opt: when grouping RBR table by primary key, non-grouping columns must be in aggregate functions

[michae2]

When grouping a table by its primary key, we usually allow non-grouping columns in the SELECT clause without aggregation functions (because there is only a single row per group).

(The following uses cockroach demo --global --nodes 9 --multitenant=false --insecure.)

CREATE TABLE a (a INT PRIMARY KEY, b INT);
INSERT INTO a VALUES (0, 0), (1, 1), (2, 2);
-- we allow b without an aggregation function
SELECT a, b FROM a GROUP BY a;

This is still true when the table has LOCALITY REGIONAL BY TABLE:

SHOW REGIONS;
ALTER DATABASE defaultdb PRIMARY REGION "us-east1";
ALTER DATABASE defaultdb ADD REGION "europe-west1";
ALTER DATABASE defaultdb ADD REGION "us-west1";
SHOW CREATE DATABASE defaultdb;
-- should now have LOCALITY REGIONAL BY TABLE
SHOW CREATE TABLE a;
-- still works
SELECT a, b FROM a GROUP BY a;

But if we change the table to LOCALITY REGIONAL BY ROW then b is no longer allowed outside of an aggregation function, even if we constrain the query to a single region:

ALTER TABLE a ADD COLUMN region crdb_internal_region AS (
  CASE WHEN a % 3 = 0 THEN 'europe-west1'
       WHEN a % 3 = 1 THEN 'us-east1'
       WHEN a % 3 = 2 THEN 'us-west1'
  END
) STORED;
ALTER TABLE a ALTER COLUMN region SET NOT NULL;
ALTER TABLE a SET LOCALITY REGIONAL BY ROW AS region;
-- should now have LOCALITY REGIONAL BY ROW
SHOW CREATE TABLE a;
-- these all fail
SELECT a, b FROM a GROUP BY a;
SELECT a, b FROM a WHERE a = 0 GROUP BY a;
SELECT a, b FROM a WHERE a % 3 = 0 GROUP BY a;

The failures:

[email protected]:26257/defaultdb> SELECT a, b FROM a GROUP BY a;
ERROR: column "b" must appear in the GROUP BY clause or be used in an aggregate function
SQLSTATE: 42803
[email protected]:26257/defaultdb> SELECT a, b FROM a WHERE a = 0 GROUP BY a;
ERROR: column "b" must appear in the GROUP BY clause or be used in an aggregate function
SQLSTATE: 42803
[email protected]:26257/defaultdb> SELECT a, b FROM a WHERE a % 3 = 0 GROUP BY a;
ERROR: column "b" must appear in the GROUP BY clause or be used in an aggregate function
SQLSTATE: 42803

Jira issue: CRDB-25879