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