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

On 10/19/23 15:18, Laurenz Albe wrote:
> On Thu, 2023-10-19 at 14:07 +0200, Dennis Brouwer wrote:
>> 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!
>
> I understand your pain.
>
> But according to my reading of the SQL standard, it only allows for
> regular column references in GROUP BY. Unless I got something wrong,
> that would mean the the ball is in Hibernate's court. It ought to
> produce correct SQL.

The answer is not as easy as that. It is true that the standard
requires a <column reference> for each element of the GROUP BY clause,
but it is also true that PostgreSQL allows arbitrary expressions.

Why is this non-standard query allowed:

vik=# select true group by true or true;
?column?
----------
t
(1 row)

but not this one?

vik=# select true group by true;
ERROR: non-integer constant in GROUP BY
LINE 1: select true group by true;
^

I may have oversimplified this example, but as long as the value is
present in the SELECT, logic would dictate that we can group by it.

The correct thing to do would be to *at least* get rid of the horrible
monstrosity that is "GROUP BY 1", but that is probably never going to
happen. This syntax was never part of the standard, and the "ORDER BY
1" syntax it was calqued upon was ripped out in SQL-99.
--
Vik Fearing