Re: Wrong result with constant quals

On Thu, Nov 3, 2022 at 11:57 PM Vik Fearing <vik(at)postgresfriends(dot)org> wrote:
>
> On 11/4/22 00:36, Tom Lane wrote:
> > Vik Fearing <vik(at)postgresfriends(dot)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.