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