Group by on primary key with other columns in target list errors out with WHERE false
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
When grouping by a primary key, a query is allowed to refer to other columns in the target list without an aggregation, since there can only be one value for a given primary key. Grouping on a primary key is not useful, but such queries do occur in the wild because they are generated by frameworks.
In Citus, such queries may error out when there is a `WHERE false` (or equivalent) clause.
```
CREATE TABLE test (x int primary key, y int);
INSERT INTO test VALUES (1,2);
-- Allowed in postgres because x is unique
SELECT x, y FROM test GROUP BY x;
SELECT x, y FROM test WHERE false GROUP BY x;
SELECT create_distributed_table('test','x');
-- Also works in Citus:
SELECT x, y FROM test GROUP BY x;
-- Errors out in Citus because of WHERE false
SELECT x, y FROM test WHERE false GROUP BY x;
WARNING: column "test.y" must appear in the GROUP BY clause or be used in an aggregate function
ERROR: could not receive query results
```
This happens because in the query that is sent to the worker, the table is replaced by a subquery that returns 0 rows:
```
SELECT x, y FROM (SELECT NULL::integer AS x, NULL::integer AS y WHERE false) test(x, y) WHERE false GROUP BY x
ERROR: column "test.y" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT x, y FROM (SELECT NULL::integer AS x, NULL::integer A...
```
Since column x is no longer a primary key, but merely the output of a subquery, referring to column `y` in the target list is not allowed.
Contributor guide
Assessment
This issue has not been assessed yet.