citusdata / citusdata/citus

Group by on primary key with other columns in target list errors out with WHERE false

Open
#1,720 0 comments 0 reactions 0 assignees View on GitHub
bug warm-up
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.