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
