Обсуждение: 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