Re: Requesting advanced Group By support

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

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services