På onsdag 10. oktober 2018 kl. 18:46:15, skrev Tomas Vondra < tomas.von...@2ndquadrant.com <mailto:tomas.von...@2ndquadrant.com>>: Hi,
On 10/09/2018 03:10 PM, Arun Kumar wrote: > Hi, > From MySQL 5.7, It supports SQL standard 99 and implements the feature > such functional dependent on the GROUP By columns, i.e., it detects the > non-aggregate columns which are functionally dependent on the GROUP BY > columns (not included in GROUP BY) and then executes the query without > error. > For example, > > *SELECT a.sno,b.sno,a.name,b.location FROM Name AS a JOIN Location AS b > ON a.sno=b.sno GROUP BY a.sno,b.location * > > In this case, a.sno is a primary key so no need to include a.name in > GROUP By as it would be identified by the primary key and then for b.sno > which is again equated with a.sno (primary key) so no need to add this > as well but for b.location, we need to add it in GROUP BY or we should > use any aggregate function over this column to avoid error. For more > info, please check on the below link > https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html > Is there any plans on supporting this in Postgres in future versions ? > So, which part of this supposedly does not work in PostgreSQL? Consider this: test2=# create table t (id int primary key, b int, c int, d int); CREATE TABLE test2=# explain select * from t group by id, b, c; QUERY PLAN ------------------------------------------------------------ HashAggregate (cost=33.12..51.62 rows=1850 width=16) Group Key: id -> Seq Scan on t (cost=0.00..28.50 rows=1850 width=16) (3 rows) test2=# explain select id, count(*) from t group by id, b, c; QUERY PLAN ------------------------------------------------------------ HashAggregate (cost=37.75..56.25 rows=1850 width=20) Group Key: id -> Seq Scan on t (cost=0.00..28.50 rows=1850 width=12) (3 rows) So clearly we've already eliminated the functionally-dependent columns from the aggregation. regards Too bad this doesn't: create table t (id int NOT NULL UNIQUE, b int, c int, d int); explain select * from t group by id, b, c; ERROR: column "t.d" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: explain select * from t group by id, b, c; -- Andreas Joseph Krogh
