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

Dear all,

I would like to add something to the question: "why grouping on a constant
would be necessary", even if it is plain wrong.

Background info: I stumbled upon this thread while upgrading our database
from postgresql-14 to postgresql-16 making use of a Java application using
of JPA and Hibernate. Hibernate is a widely used framework and this library
will compose queries (under certain conditions (still unknown to me))
with *GROUP
BY coulmn1, column2, true* *<-- *

Hibernate has been doing this quircky thing for many many years and even in
the latest release does so. So, potentionally many Java Enterprise
applications will be tied to postgresql-14 if there is no compatibility
switch possible.

In our case a tiny compatability switch would be a livesaver. Of courcse, I
will try to convince the Hibernate community to have this unnecessary
constant removed but that still leaves all legacy code to not work with
postgresql-15+ databases which would be pitiful!

Kind regards,

Dennis Brouwer

On Thu, 19 Oct 2023 at 13:44, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> John Naylor <john(dot)naylor(at)enterprisedb(dot)com> writes:
> > On Mon, Aug 28, 2023 at 1:28 PM David Micallef <
> david(dot)j(dot)micallef(at)gmail(dot)com>
> > wrote:
> >> ERROR: non-integer constant in GROUP BY
>
> > I can confirm the reproducer script fails with commit 941460fcf731a ("Add
> > Boolean node"). (So actually all 15.x were affected.)
>
> Yeah, same result from bisecting here. I had had the idea that this
> was an intentional semantics change to reduce the probability of error,
> but that commit didn't mention any such thing, so that's a tough claim
> to make as far as the historical intent goes.
>
> 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?)
>
> 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.
>
> 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.)
>
> regards, tom lane
>
>
>
>
>