Обсуждение: Using non-grouping-keys at HAVING clause

Hello,

I got a trouble report here:
https://github.com/heterodb/pg-strom/issues/636

It says that PG-Strom raised an error when the HAVING clause used
non-grouping-keys,
even though the vanilla PostgreSQL successfully processed the query.

SELECT MAX(c0) FROM t0 GROUP BY t0.c1 HAVING t0.c0<MIN(t0.c0);

However, I'm not certain what is the right behavior here.
The "c0" column does not appear in the GROUP BY clause, thus we cannot
know its individual
values after the group-by stage, right?
So, what does the "HAVING t0.c0<MIN(t0.c0)" evaluate here?

Best regards,
-- 
HeteroDB, Inc / The PG-Strom Project
KaiGai Kohei <kaigai@heterodb.com>

On 9/8/23 09:42, Kohei KaiGai wrote:
> Hello,
> 
> I got a trouble report here:
> https://github.com/heterodb/pg-strom/issues/636
> 
> It says that PG-Strom raised an error when the HAVING clause used
> non-grouping-keys,
> even though the vanilla PostgreSQL successfully processed the query.
> 
> SELECT MAX(c0) FROM t0 GROUP BY t0.c1 HAVING t0.c0<MIN(t0.c0);
> 
> However, I'm not certain what is the right behavior here.
> The "c0" column does not appear in the GROUP BY clause, thus we cannot
> know its individual
> values after the group-by stage, right?

Wrong.  c1 is the primary key and so c0 is functionally dependent on it. 
  Grouping by the PK is equivalent to grouping by all of the columns in 
the table.
-- 
Vik Fearing

2023年9月8日(金) 19:07 Vik Fearing <vik@postgresfriends.org>:
>
> On 9/8/23 09:42, Kohei KaiGai wrote:
> > Hello,
> >
> > I got a trouble report here:
> > https://github.com/heterodb/pg-strom/issues/636
> >
> > It says that PG-Strom raised an error when the HAVING clause used
> > non-grouping-keys,
> > even though the vanilla PostgreSQL successfully processed the query.
> >
> > SELECT MAX(c0) FROM t0 GROUP BY t0.c1 HAVING t0.c0<MIN(t0.c0);
> >
> > However, I'm not certain what is the right behavior here.
> > The "c0" column does not appear in the GROUP BY clause, thus we cannot
> > know its individual
> > values after the group-by stage, right?
>
> Wrong.  c1 is the primary key and so c0 is functionally dependent on it.
>   Grouping by the PK is equivalent to grouping by all of the columns in
> the table.
>
Wow! Thanks, I got the point. Indeed, it is equivalent to the grouping
by all the columns.

--
HeteroDB, Inc / The PG-Strom Project
KaiGai Kohei <kaigai@heterodb.com>