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
John Naylor <john.naylor@enterprisedb.com> writes:
> On Mon, Aug 28, 2023 at 1:28 PM David Micallef <david.j.micallef@gmail.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