sql: group by does not conform with postgres

[BramGruneir]

org.hibernate.userguide.hql.HQLTest test_hql_group_by_example_3 fails

create table Phone (
   id int8 not null,
    phone_number varchar(255),
    phone_type varchar(255),
    person_id int8,
    order_id int4,
    primary key (id)
);
create table phone_call (
   id int8 not null,
    duration int4 not null,
    call_timestamp timestamp,
    phone_id int8,
    primary key (id)
);
create table Person (
   id int8 not null,
    address varchar(255),
    createdOn timestamp,
    name varchar(255),
    nickName varchar(255),
    version int4 not null,
    primary key (id)
);
select
    person2_.id as col_0_0_,
    sum(call0_.duration) as col_1_0_,
    person2_.id as id1_2_,
    person2_.address as address2_2_,
    person2_.createdOn as createdO3_2_,
    person2_.name as name4_2_,
    person2_.nickName as nickName5_2_,
    person2_.version as version6_2_ 
from
    phone_call call0_ 
inner join
    Phone phone1_ 
        on call0_.phone_id=phone1_.id 
inner join
    Person person2_ 
        on phone1_.person_id=person2_.id 
group by
    person2_.id;

This should parse and execute, but instead it produces
ERROR: column "address" must appear in the GROUP BY clause or be used in an aggregate function

From the postgres docs:

When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

[knz]

cc @RaduBerinde @andy-kimball

Andy says:

doing something as simple as:
select x, y FROM (select x, y from t) AS t group by x;
causes Postgres to raise an error

OTOH, the query above uses a join in the FROM clause and yet the syntax is valid.

This means that:

• during semantic analysis we must propagate the key properties of columns in the FROM relational expression up to the analysis of SELECT target exprs.
• however, we must not propagate these key properties if a relational sub-expression was using the (...) (subquery) syntax

Probably a good idea to peek at pg to determine how this is detected.

[sploiselle]

@knz Can we get a docs description this issue as a Known Limitation for 2.1?

[knz]

"""
title: Using columns in SELECT not listed in GROUP BY

Applications developed for PostgreSQL may exploit the fact that PostgreSQL allows a SELECT clause to name a column that is not also listed in GROUP BY in some cases, for example SELECT a GROUP BY b. This is not yet supported by CockroachDB. To work around this limitation, and depending on expected results, the rendered columns should be either added at the end of the GROUP BY list, for example SELECT a GROUP BY b, a, or DISTINCT should also be used, for example SELECT DISTINCT a GROUP BY b.
"""

[RaduBerinde]

Another example from @jordanlewis:

root@:26257/activerecord_unittest> SELECT "developers"."id", "developers"."name", "developers"."salary", "developers"."firm_id", "developers"."mentor_id", "developers"."created_at", "developers"."updated_at", "developers"."created_on", "developers"."updated_on" FROM "developers" GROUP BY developers.name, developers.salary, developers.id, developers.created_at, developers.updated_at, developers.created_on, developers.updated_on;
pq: column "firm_id" must appear in the GROUP BY clause or be used in an aggregate function

[RaduBerinde]

PG docs:

When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

So the column is accepted if the PK of the base table it is coming from is in the grouping list. In our case this would be the ColumnMeta.Table.
https://github.com/postgres/postgres/blob/master/src/backend/parser/parse_agg.c
https://github.com/postgres/postgres/blob/master/src/backend/catalog/pg_constraint.c

An alternative would be to use our internal FDs. These are a superset of this simple rule and we would accept more cases; however it is fragile - changing some rules could in principle change the detected FDs, so it's possible that some queries would work in a release but then stop working in a future release.

[rohany]

A note -- this feature seems to be used heavily by django (alot of the django annotate/group by tools end up creating queries like this).

[jseldess]

@RaduBerinde, can we resolve/remove this known limitation from the 20.1 docs? https://www.cockroachlabs.com/docs/dev/known-limitations.html#truncate-does-not-behave-like-delete

[RaduBerinde]

@jseldess yes, we should remove it.

[jseldess]

Sorry, was linking to the wrong limitation. I meant: https://www.cockroachlabs.com/docs/dev/known-limitations.html#using-columns-in-select-not-listed-in-group-by

I'll remove that one. Please let me know if that's not right, @RaduBerinde.

[RaduBerinde]

Yes, I figured you were referring to that one.