Re: Works in MySQL but not in PG - why?

=?UTF-8?B?UMOzbCBVYSBMYW/DrW5lY2jDoWlu?= <linehanp@tcd.ie> writes:
> ... I have the following
> query (which works for MySQL 5.7 and 8.0.17 (but not 5.6 strangely).

> SELECT
> ...
> GROUP BY LEAST(t1key, t2key), GREATEST(t1key, t2key)
> HAVING COUNT(LEAST(t1key, t2key))/2 = (SELECT COUNT(tab.t_key) FROM
> tab WHERE t_key = t1key)

> but in PG, I get the following error
> ERROR: subquery uses ungrouped column "t1.t1key" from outer query
> LINE 20: ...)/2 = (SELECT COUNT(tab.t_key) FROM tab WHERE t_key = t1key)

Well, it's right: the sub-select refers directly to t1key from the
outer query, and t1key does not have a well-defined value in the
HAVING clause.  As an example, if you had a row with t1key=1 and
t2key=2, and another row with t1key=2 and t2key=1, those would fall
into the same group, because the LEAST and GREATEST values will be
1 and 2 respectively for both rows.  So which value of t1key would
you expect the HAVING clause to use?

MySQL is rather infamous for not worrying too much about whether
queries like this have any well-defined result, so the fact that
it fails to throw an error is sad but not very surprising.  You
got back some answer, but who knows which value of t1key they used?

It's not very clear to me what you're really trying to do here,
and in particular I don't follow why grouping by the LEAST and
GREATEST values is appropriate, so I don't have any solid advice
on what you ought to do to fix the query.  Maybe the GROUP BY
clause should just be "GROUP BY t1key, t2key"?

            regards, tom lane