From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: John Naylor <john.naylor@enterprisedb.com>
Cc: David Micallef <david.j.micallef@gmail.com>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: group by true now errors with non-integer constant in GROUP BY
Date: Mon, 28 Aug 2023 21:02:37 -0400
Message-ID: <894521.1693270957@sss.pgh.pa.us> (raw)
In-Reply-To: <CAApHDvrSUvb1ODvNcYGHz4O6WEsPChhwmSkJcs_3y5pniN2p+A@mail.gmail.com>
References: <CAJBCwCS7OuEUmGkrnu5Q8SmBP10njnEiX7vJiaxJURZXcCORyw@mail.gmail.com>
<CAFBsxsE_zy1Y=u2ZM6xZHFY2Cwfr_Uii=-irsHtCxrh6GnfBNA@mail.gmail.com>
<837886.1693252560@sss.pgh.pa.us>
<CAApHDvrSUvb1ODvNcYGHz4O6WEsPChhwmSkJcs_3y5pniN2p+A@mail.gmail.com>
David Rowley <dgrowleyml@gmail.com> writes:
> On Tue, 29 Aug 2023 at 07:56, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
Sure, you can write any constant expression, for instance NULL::bool
would work. The question is where do we draw the line between SQL92
and SQL99 behaviors. I think "an undecorated constant is SQL92, and
therefore it must be an integer matching an output column number" is
a nice simple rule. The fact that TRUE and FALSE were not previously
treated as undecorated constants is an unintentional wart of the old
implementation, not something we ought to preserve.
> I had a look on dbfiddle and I see that MySQL 8.0 and SQLlite all
> allow GROUP BY true.
What do they do with GROUP BY 1, or GROUP BY 10000, or GROUP BY 1.0 ?
> 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.
Sure, changing the behavior is trivial. Whether we *should* change
the behavior, and if so to what, is less so. I'm not really on
board with "we need to maintain bug-compatibility with an old
implementation artifact".
BTW, I poked around and couldn't find anything explaining this
fine point in the SGML docs, although the comments in
findTargetlistEntrySQL92 are clear about it. If we do anything
at all here, I think that ought to include documenting the behavior
more clearly (and I'm curious to see how you'd propose to explain
the behavior you want to users).
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, dgrowleyml@gmail.com, john.naylor@enterprisedb.com, david.j.micallef@gmail.com, peter.eisentraut@enterprisedb.com, pgsql-bugs@lists.postgresql.org
Subject: Re: group by true now errors with non-integer constant in GROUP BY
In-Reply-To: <894521.1693270957@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox