Wrong result with constant quals
The following query returns a wrong result, in my opinion.
postgres=# select 1 where false having true;
?column?
----------
1
(1 row)
The correct result should be zero rows.
According to the General Rules of 7.13 <group by clause>, the lack of a
GROUP BY clause means the result of the WHERE clause is the sole group.
Because of the WHERE FALSE, we should have either a single group with no
rows, or no groups, depending on how you look at it.
The General Rules of 7.14 <having clause> dictate that all groups where
the HAVING clause evaluates to TRUE are to be output. That will always
be the case for this query, so regardless of if we have no groups or one
group with no rows, the result should be empty.
I cannot find any justification anywhere for why this query should emit
any values at all.
Hat tip to Lukas Eder:
https://twitter.com/lukaseder/status/1588150810466205697
--
Vik Fearing
Vik Fearing <vik@postgresfriends.org> writes:
The following query returns a wrong result, in my opinion.
postgres=# select 1 where false having true;
?column?
----------
1
(1 row)
The correct result should be zero rows.
No, I don't think so. The presence of HAVING without GROUP BY makes
this act like a query with an aggregate function and no GROUP BY: you
get a single grouped row, regardless of what the input is. There's a
reasonably clear specification of that in SQL92 7.8 <having clause>:
1) Let T be the result of the preceding <from clause>, <where
clause>, or <group by clause>. If that clause is not a <group
by clause>, then T consists of a single group and does not have
a grouping column.
"A single group" is not "no groups".
Later SQL versions define this by reference to "GROUP BY ()", but
I think the effect is the same.
regards, tom lane
On 11/4/22 00:36, Tom Lane wrote:
Vik Fearing <vik@postgresfriends.org> writes:
The following query returns a wrong result, in my opinion.
postgres=# select 1 where false having true;
?column?
----------
1
(1 row)The correct result should be zero rows.
No, I don't think so. The presence of HAVING without GROUP BY makes
this act like a query with an aggregate function and no GROUP BY: you
get a single grouped row, regardless of what the input is. There's a
reasonably clear specification of that in SQL92 7.8 <having clause>:
SQL92? wut?
1) Let T be the result of the preceding <from clause>, <where
clause>, or <group by clause>. If that clause is not a <group
by clause>, then T consists of a single group and does not have
a grouping column."A single group" is not "no groups".
Later SQL versions define this by reference to "GROUP BY ()", but
I think the effect is the same.
I allowed for this by saying it could be a single group with no rows if
you preferred to look at it that way.
This does not explain why the WHERE FALSE is being ignored and producing
rows.
--
Vik Fearing
On Thu, Nov 3, 2022 at 11:57 PM Vik Fearing <vik@postgresfriends.org> wrote:
On 11/4/22 00:36, Tom Lane wrote:
Vik Fearing <vik@postgresfriends.org> writes:
The following query returns a wrong result, in my opinion.
postgres=# select 1 where false having true;
?column?
----------
1
(1 row)The correct result should be zero rows.
No, I don't think so. The presence of HAVING without GROUP BY makes
this act like a query with an aggregate function and no GROUP BY: you
get a single grouped row, regardless of what the input is. There's a
reasonably clear specification of that in SQL92 7.8 <having clause>:SQL92? wut?
1) Let T be the result of the preceding <from clause>, <where
clause>, or <group by clause>. If that clause is not a <group
by clause>, then T consists of a single group and does not have
a grouping column."A single group" is not "no groups".
Later SQL versions define this by reference to "GROUP BY ()", but
I think the effect is the same.I allowed for this by saying it could be a single group with no rows if
you preferred to look at it that way.This does not explain why the WHERE FALSE is being ignored and producing
rows.
--
Vik Fearing
The WHERE FALSE is not ignored. It does produce 0 rows before the
HAVING is applied. The output of the query until then (or if HAVING
was not included) would be 0 rows indeed. So when HAVING is applied
the result so far is a single group of 0 rows. The grouping (from
applying HAVING) produces the one row in the result you see.
Vik Fearing <vik@postgresfriends.org> writes:
I allowed for this by saying it could be a single group with no rows if
you preferred to look at it that way.
That is exactly what the standard says.
This does not explain why the WHERE FALSE is being ignored and producing
rows.
It's not ignored, but it applies to the pre-grouping rows, of which
there aren't any to remove. But there's still a group.
regards, tom lane
On 11/4/22 01:14, Tom Lane wrote:
Vik Fearing <vik@postgresfriends.org> writes:
I allowed for this by saying it could be a single group with no rows if
you preferred to look at it that way.That is exactly what the standard says.
This does not explain why the WHERE FALSE is being ignored and producing
rows.It's not ignored, but it applies to the pre-grouping rows, of which
there aren't any to remove. But there's still a group.
Okay, I can see better what is happening with this query:
select 42
from (values (1), (2), (3)) as _
having true;
?column?
----------
42
(1 row)
--
Vik Fearing