Re: Wrong result with constant quals

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.