sql: make GROUP BY recognize projection aliases

[knz]

Reported by @clanstyles on Gitter.

This is a subtle extension to the SQL standard recognized by PostgreSQL which currently causes CockroachDB to silently return different results from pg in common cases. So the severity is high.

To understand the problem, let's start with the "easy" and innocuous form of the bug:

> create table t(x int);
> select x+1 as z from t group by z;

This is recognized by PostgreSQL (groups by the projected expression x+1), but not by CockroachDB currently (reports an error z must be grouped by).

The difference between pg and crdb here is that GROUP BY will recognize simple/naked projection aliases before resolving the names using the regular algorithm (like ORDER BY already does.) This is a behavior specific to "simple identifiers" (like for ORDER BY).

Why this matters is that CockroachDB fails to report an error in pretty common cases, and instead returns, silently, very different results from PostgreSQL:

>  select (x % 10) as x
     from t
 group by x;

This currently groups by t.x in CockroachDB, whereas it should group by (x%10) like in PostgreSQL!

Both the heuristic planner and the opt code must be extended to support this!

[knz]

cc @RaduBerinde

[knz]

Started to investigate.

The problem is worse than that said above, actually the resolution may currently be incorrect also for ORDER BY.

[knz]

See the code in pg's src/parser/parse_clause.c, findTargetlistEntrySQL92

The following is also valid and needs to be checked:

select x+1 from foo group by "?column?"

[sploiselle]

@knz Can I get a quick blurb describing this known limitation w/r/t the impact to user experience? Ideally, we need it by Friday 10/26 for the 2.1 Known Limitations page. Posting it on this issue and/or pinging me would be great.

[knz]

"""
title: GROUP BY referring to SELECT aliases

Applications developed for PostgreSQL that use GROUP BY to refer to column aliases produced in the same SELECT clause must be changed to use the full underlying expression instead. For example, SELECT x+y AS z ... GROUP BY z must be changed to SELECT x+y AS z ... GROUP BY x+y. Otherwise, CockroachDB will produce either a planning error or in some cases invalid results.
"""

[RaduBerinde]

We are running tight on 19.1, I am pushing this out given that the fix is not trivial and the "surface area" is not very high.

[rafiss]

I think aliases and GROUP BY do not play well in most circumstances:

This came up when testing with Django: cockroachdb/django-cockroachdb#82

> create table t (a int primary key);
CREATE TABLE

> insert into t values(1);
INSERT 1

> select exists (select a from t) as e1, count(*) from t group by e1;
pq: column "e1" does not exist

> select (select a from t) as s1, count(*) from t group by s1;
pq: column "s1" does not exist

> select 1 as s1 group by s1;
pq: column "s1" does not exist

[RaduBerinde]

We should prioritize fixing this and backport to 19.2.x. I can take a look this week.

[RaduBerinde]

Trying to understand the postgres semantics.. Given the above examples, I would have expected that the aliases take precedence over the input columns, but that doesn't seem to be the case:

radu=> select a as x from ab group by x;
ok
radu=> select a as b from ab group by b;
ERROR:  column "ab.a" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select a as b from ab group by b;

They do seem to take precedence over columns from higher scopes:

radu=> select c, d, (select a as c from ab group by c) from cd;
ERROR:  more than one row returned by a subquery used as an expression

Here c refers to the alias and not the outer column (otherwise it couldn't have returned more than one row).

[knz]

The comment in the pg source file I pointed to at the top explains the special cases I think. -- Sent from my Android device with K-9 Mail. Please excuse my brevity.

[RaduBerinde]

Thanks @knz, that definitely helped. I will try to harvest some test cases from postgres as well.

[RaduBerinde]

I would like to correct something in the original description. The aliases are used only if they don't conflict with the FROM column names, which have precedence. So in the (x%10) as x example, we do the right thing. This is postgres:

radu=> insert into ab values (1, 1), (11, 11);
INSERT 0 2
radu=> select (a % 10) as a from ab group by a;
 a 
---
 1
 1

[jseldess]

@RaduBerinde, since this issue is closed, does that mean we can remove this limitation from the 20.1 known limitations? https://www.cockroachlabs.com/docs/dev/known-limitations.html#group-by-referring-to-select-aliases

[RaduBerinde]

@jseldess yes, thanks.