Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qtiKF-00DcYJ-6o for pgsql-bugs@arkaria.postgresql.org; Fri, 20 Oct 2023 05:45:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qtiJE-00DcCO-Lm for pgsql-bugs@arkaria.postgresql.org; Fri, 20 Oct 2023 05:44:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qtiJE-00DcCG-Dc for pgsql-bugs@lists.postgresql.org; Fri, 20 Oct 2023 05:44:33 +0000 Received: from relay1-d.mail.gandi.net ([2001:4b98:dc4:8::221]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qtiJC-001NH9-0x for pgsql-bugs@lists.postgresql.org; Fri, 20 Oct 2023 05:44:31 +0000 Received: by mail.gandi.net (Postfix) with ESMTPSA id 0BB21240007; Fri, 20 Oct 2023 05:44:23 +0000 (UTC) Message-ID: <18ba0d29-0637-48c5-a11d-19f8672a8b0f@postgresfriends.org> Date: Fri, 20 Oct 2023 07:44:23 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: group by true now errors with non-integer constant in GROUP BY Content-Language: en-US To: Laurenz Albe , Dennis Brouwer , Tom Lane Cc: John Naylor , David Micallef , Peter Eisentraut , pgsql-bugs@lists.postgresql.org References: <837886.1693252560@sss.pgh.pa.us> <3af446dd6fbfdb6e08de95fc24e06081f78a6f15.camel@cybertec.at> From: Vik Fearing In-Reply-To: <3af446dd6fbfdb6e08de95fc24e06081f78a6f15.camel@cybertec.at> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-GND-Sasl: vik@postgresfriends.org List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 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