Re: group by true now errors with non-integer constant in GROUP BY

On Tue, 29 Aug 2023 at 07:56, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Having said that ... it seems to me that it was pure coincidence that
> "group by true" worked before, and I'm not sure we should install a
> grotty hack to make it work again. In particular, why should we allow
> Boolean Consts but not other non-integer Consts? (And if we do, don't
> we need to change that error message?)

Is it really a grotty hack? Isn't it just as simple as the attached?

> The bigger picture here is: what is the use-case for grouping by a
> constant at all? Assuming that it is an error seems like a good
> foolproofing restriction. The reason we felt we could keep the
> "group by N" SQL92-ism after SQL99 redefined GROUP BY arguments is
> exactly that there's no obvious use-case for grouping by a constant.
> As soon as you allow it, "group by N" becomes hopelessly ambiguous.

The new behaviour feels a bit inconsistent to me as it stands today.

I can't write GROUP BY true, but I can write GROUP BY 1=1, which gets
it beyond the parser and allows constant folding to turn it into GROUP
BY true, which I couldn't specify because the parser would complain.

> So my druthers would be to reject this as a non-bug. But if we accept
> it as something to fix, we need to revisit exactly which conditions
> are errors here. Perhaps rather than "reject all non-integer cases",
> we should only reject the Float case, and let others fall through to
> the SQL99 code. (I would not be happy allowing Float, because that'd
> mean that "group by 4" and "group by 4.0" mean fundamentally different
> things.)

I had a look on dbfiddle and I see that MySQL 8.0 and SQLlite all
allow GROUP BY true. I think if we used to, and those other databases
do, then we might want to reconsider supporting it again, especially
so now that someone has complained. I'm assuming it's just as simple
as the attached patch, but I'm happy to listen if I've underestimated
the complexity.

David