Обсуждение: Trailing comma support in SELECT statements

Hi,
I have created a small patch to postgres source (in particular the
psql part of it) that accepts trailing comma at the end of list in
SELECT statement.

The idea is to be able to say both (with the same result):
SELECT a, b, c from t;
SELECT a, b, c, from t;

Attached you can find a patch containing regression test (incorporated
into the serial_schedule).
My patch is relative to origin/REL9_4_STABLE branch as that is the one
I started from.

My plea is to have this change merged into the main stream so that it
becomes available in upcoming releases.

This modification does not require any interaction with user.
It does not create any backward compatibility issues.
Not does it have any performance impact.

regards
bogdan

Bogdan Pilch <bogdan@matfyz.cz> writes:
> I have created a small patch to postgres source (in particular the
> psql part of it) that accepts trailing comma at the end of list in
> SELECT statement.

This doesn't seem to me to be a remarkably good idea.  What's the
difference between this and accepting random misspellings of SELECT,
allowing mismatched parentheses in expressions, etc etc?  It's important
in a computer language to be able to catch typos.

If we were going to be lax about trailing commas, the SELECT list
would hardly be the only candidate, or even the first candidate,
for being lax that way.  But I don't want to go there.
        regards, tom lane

On Sun, Sep 28, 2014 at 01:42:46PM +0200, Bogdan Pilch wrote:
> Hi,
> I have created a small patch to postgres source (in particular the
> psql part of it) that accepts trailing comma at the end of list in
> SELECT statement.
> 
> The idea is to be able to say both (with the same result):
> SELECT a, b, c from t;
> SELECT a, b, c, from t;
> 
> Attached you can find a patch containing regression test (incorporated
> into the serial_schedule).
> My patch is relative to origin/REL9_4_STABLE branch as that is the one
> I started from.
> 
> My plea is to have this change merged into the main stream so that it
> becomes available in upcoming releases.
> 
> This modification does not require any interaction with user.
> It does not create any backward compatibility issues.

Interesting --- I know some languages allow trailing delimiters, like
Perl and Javascript.  Could this mask query errors?  Does any other
database accept this?  Seems this would need to be done in many other
places, like UPDATE, but let's first decide if we want this.

FYI, it is usually better to discuss a feature before showing a patch.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

On 10/03/2014 12:20 PM, Bruce Momjian wrote:
> On Sun, Sep 28, 2014 at 01:42:46PM +0200, Bogdan Pilch wrote:
>> Hi,
>> I have created a small patch to postgres source (in particular the
>> psql part of it) that accepts trailing comma at the end of list in
>> SELECT statement.
>>
>> The idea is to be able to say both (with the same result):
>> SELECT a, b, c from t;
>> SELECT a, b, c, from t;
>>
>> Attached you can find a patch containing regression test (incorporated
>> into the serial_schedule).
>> My patch is relative to origin/REL9_4_STABLE branch as that is the one
>> I started from.
>>
>> My plea is to have this change merged into the main stream so that it
>> becomes available in upcoming releases.
>>
>> This modification does not require any interaction with user.
>> It does not create any backward compatibility issues.
> Interesting --- I know some languages allow trailing delimiters, like
> Perl and Javascript.  Could this mask query errors?  Does any other
> database accept this?  Seems this would need to be done in many other
> places, like UPDATE, but let's first decide if we want this.
>
> FYI, it is usually better to discuss a feature before showing a patch.
>

Javascript might accept it, but it's not valid JSON.

The case for doing it is that then you can easily comment out any entry 
at all in a select list:
    select        foo as f1,        bar as f2,        -- baz as f3,    from blurfl


cheers

andrew

Andrew Dunstan wrote
> On 10/03/2014 12:20 PM, Bruce Momjian wrote:
>> On Sun, Sep 28, 2014 at 01:42:46PM +0200, Bogdan Pilch wrote:
>>> Hi,
>>> I have created a small patch to postgres source (in particular the
>>> psql part of it) that accepts trailing comma at the end of list in
>>> SELECT statement.
>>>
>>> The idea is to be able to say both (with the same result):
>>> SELECT a, b, c from t;
>>> SELECT a, b, c, from t;
>>>
>>> Attached you can find a patch containing regression test (incorporated
>>> into the serial_schedule).
>>> My patch is relative to origin/REL9_4_STABLE branch as that is the one
>>> I started from.
>>>
>>> My plea is to have this change merged into the main stream so that it
>>> becomes available in upcoming releases.
>>>
>>> This modification does not require any interaction with user.
>>> It does not create any backward compatibility issues.
>> Interesting --- I know some languages allow trailing delimiters, like
>> Perl and Javascript.  Could this mask query errors?  Does any other
>> database accept this?  Seems this would need to be done in many other
>> places, like UPDATE, but let's first decide if we want this.
>>
>> FYI, it is usually better to discuss a feature before showing a patch.
>>
> 
> Javascript might accept it, but it's not valid JSON.
> 
> The case for doing it is that then you can easily comment out any entry 
> at all in a select list:
> 
>      select
>          foo as f1,
>          bar as f2,
>          -- baz as f3,
>      from blurfl

Should we also allow:

SELECT
, col1
, col2
, col3
FROM ...

?

The other reason for this would be to build dynamic SQL more easily via a
loop.

Barring arguments showing danger allowing I don't see a reason to reject
this; let people decide whether they want to utilize it on stylistic or
compatibility grounds.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Trailing-comma-support-in-SELECT-statements-tp5821613p5821694.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On 10/3/14, 4:02 PM, David G Johnston wrote:
> Should we also allow:
>
> SELECT
> , col1
> , col2
> , col3
> FROM ...
>
> ?
I would say yes, if we're going to do this. I don't see it being any worse than trailing commas.

If we are going to do this, we need to do it EVERYWHERE.

FWIW, the way I normally "work around" this problem is:

SELECT    blah    , foo    , bar    , baz

In my experience, it's quite uncommon to mess with the first item in the list, which mostly eliminates the issue. A
missingleading comma is also MUCH easier to spot than a missing trailing comma.
 

Jim Nasby-5 wrote
> On 10/3/14, 4:02 PM, David G Johnston wrote:
>> Should we also allow:
>>
>> SELECT
>> , col1
>> , col2
>> , col3
>> FROM ...
>>
>> ?
> I would say yes, if we're going to do this. I don't see it being any worse
> than trailing commas.
> 
> If we are going to do this, we need to do it EVERYWHERE.
> 
> FWIW, the way I normally "work around" this problem is:
> 
> SELECT
>      blah
>      , foo
>      , bar
>      , baz
> 
> In my experience, it's quite uncommon to mess with the first item in the
> list, which mostly eliminates the issue. A missing leading comma is also
> MUCH easier to spot than a missing trailing comma.
> 
> 
> -- 
> Sent via pgsql-hackers mailing list (

> pgsql-hackers@

> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers


Jim Nasby-5 wrote
> On 10/3/14, 4:02 PM, David G Johnston wrote:
>> Should we also allow:
>>
>> SELECT
>> , col1
>> , col2
>> , col3
>> FROM ...
>>
>> ?
> I would say yes, if we're going to do this. I don't see it being any worse
> than trailing commas.
> 
> If we are going to do this, we need to do it EVERYWHERE.
> 
> FWIW, the way I normally "work around" this problem is:
> 
> SELECT
>      blah
>      , foo
>      , bar
>      , baz
> 
> In my experience, it's quite uncommon to mess with the first item in the
> list, which mostly eliminates the issue. A missing leading comma is also
> MUCH easier to spot than a missing trailing comma.

We might as well allow a final trailing (or initial leading) comma on a
values list at the same time:

VALUES
(...),
(...),
(...),
;


David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Trailing-comma-support-in-SELECT-statements-tp5821613p5823365.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Pavel Stehule <pavel.stehule@gmail.com> wrote:

> do you know, so this feature is a proprietary and it is not based
> on ANSI/SQL? Any user, that use this feature and will to port to
> other database will hate it.

I remember that Sybase ASE allowed a trailing comma within the
parentheses of a table definition, which was handy.  I checked on
SQL Fiddle and found that MS SQL Server and MySQL both allow that,
too; although Oracle does not.  I'm not taking a position on
whether we should allow this in PostgreSQL, but not having it is
likely to annoy some users moving *to* PostgreSQL, while having it
is likely to annoy some users moving *away* from PostgreSQL.

None of the products I tried allowed a leading comma.

I didn't test, and have no knowledge regarding, how other products
treat extra commas elsewhere.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 10/16/14, 11:48 PM, David Johnston wrote:
>         We might as well allow a final trailing (or initial leading) comma on a
>         values list at the same time:
<snip>

>     do you know, so this feature is a proprietary and it is not based on ANSI/SQL? Any user, that use this feature
andwill to port to other database will hate it.
 
>
> ​I've got no complaint if "at the same time" means that neither behavior is ever implemented...

As I originally posted, if we're going to do this I think we should do it *EVERYWHERE* commas are used as delimiters,
saveCOPY input and output. Or we should at least get close to doing it everywhere. I think the only way things could
getmore annoying is if we accepted extra commas in SELECT but not in CREATE TABLE (as one example).
 

To me completeness is more important than whether we do it or not; that said, I like the idea (as well as supporting
leadingextra commas).
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> As I originally posted, if we're going to do this I think we should do it *EVERYWHERE* commas are used as delimiters,
saveCOPY input and output. Or we should at least get close to doing it everywhere. I think the only way things could
getmore annoying is if we accepted extra commas in SELECT but not in CREATE TABLE (as one example).
 

> To me completeness is more important than whether we do it or not; that said, I like the idea (as well as supporting
leadingextra commas).
 

Yeah, exactly.  Personally I'm *not* for this, but if we do it we should
do it consistently: every comma-separated list in the SQL syntax should
work the same.
        regards, tom lane

On Oct 17, 2014, at 3:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Yeah, exactly.  Personally I'm *not* for this, but if we do it we should
> do it consistently: every comma-separated list in the SQL syntax should
> work the same.

PL/pgSQL, too, I presume.

D

On 10/17/14, 11:19 PM, David E. Wheeler wrote:
> On Oct 17, 2014, at 3:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Yeah, exactly.  Personally I'm *not* for this, but if we do it we should
>> do it consistently: every comma-separated list in the SQL syntax should
>> work the same.
>
> PL/pgSQL, too, I presume.

Yes.

The only case I can think of where we wouldn't want this is COPY.

BTW, this should also apply to delimiters other than commas; for example, some geometry types use ; as a delimiter
betweenpoints.
 

I do think such a change should be made in stages, and maybe not every last one makes it into 9.5, but the intention
shouldcertainly be that we support extra delimiters *everywhere*.
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On Oct 18, 2014, at 7:06 PM, Jim Nasby <Jim.Nasby@BlueTreble.com> wrote:

> Yes.
>
> The only case I can think of where we wouldn't want this is COPY.
>
> BTW, this should also apply to delimiters other than commas; for example, some geometry types use ; as a delimiter
betweenpoints. 

I don’t think it should apply to the internals of types, necessarily. JSON, for example, always dies on an trailing
comma,so should probably stay that way. Well, maybe allow it on JSONB input, but not JSON. Though we perhaps don’t want
theirbehaviors to diverge. 

D

On 10/20/2014 11:59 AM, David E. Wheeler wrote:
> On Oct 18, 2014, at 7:06 PM, Jim Nasby <Jim.Nasby@BlueTreble.com> wrote:
>
>> Yes.
>>
>> The only case I can think of where we wouldn't want this is COPY.
>>
>> BTW, this should also apply to delimiters other than commas; for example, some geometry types use ; as a delimiter
betweenpoints. 
> I don’t think it should apply to the internals of types, necessarily. JSON, for example, always dies on an trailing
comma,so should probably stay that way. Well, maybe allow it on JSONB input, but not JSON. Though we perhaps don’t want
theirbehaviors to diverge. 
>


The JSON spec is quite clear on this. Leading and trailing commas are
not allowed. I would fight tooth and nail not to allow it for json (and
by implication jsonb, since they use literally the same parser - in fact
we do that precisely so their input grammars can't diverge).

cheers

andrew

On 10/20/14, 11:16 AM, Andrew Dunstan wrote:
> On 10/20/2014 11:59 AM, David E. Wheeler wrote:
>> On Oct 18, 2014, at 7:06 PM, Jim Nasby <Jim.Nasby@BlueTreble.com> wrote:
>>
>>> Yes.
>>>
>>> The only case I can think of where we wouldn't want this is COPY.
>>>
>>> BTW, this should also apply to delimiters other than commas; for example, some geometry types use ; as a delimiter
betweenpoints.
 
>> I don’t think it should apply to the internals of types, necessarily. JSON, for example, always dies on an trailing
comma,so should probably stay that way. Well, maybe allow it on JSONB input, but not JSON. Though we perhaps don’t want
theirbehaviors to diverge.
 
>>
>
>
> The JSON spec is quite clear on this. Leading and trailing commas are not allowed. I would fight tooth and nail not
toallow it for json (and by implication jsonb, since they use literally the same parser - in fact we do that precisely
sotheir input grammars can't diverge).
 

+1. Data types that implement specs should follow the spec.

I was more concerned about things like polygon, but the real point (ha!) is that we need to think about the data types
too.(I will say I don't think things that mandate an exact number of elements (like point, box, etc) should support
extradelimiters).
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> On 10/20/14, 11:16 AM, Andrew Dunstan wrote:
>> The JSON spec is quite clear on this. Leading and trailing commas are not allowed. I would fight tooth and nail not
toallow it for json (and by implication jsonb, since they use literally the same parser - in fact we do that precisely
sotheir input grammars can't diverge).
 

> +1. Data types that implement specs should follow the spec.

> I was more concerned about things like polygon, but the real point (ha!) is that we need to think about the data
typestoo. (I will say I don't think things that mandate an exact number of elements (like point, box, etc) should
supportextra delimiters).
 

I'm pretty strongly against this, as it would create cross-version hazards
for data.  Having queries that depend on newer-version SQL features is
something that people are used to coping with ... but data that loads into
some versions and not others seems like a hassle we do not need to invent.

(Of course, I'm not for the feature w.r.t. SQL either.  But breaking data
compatibility is just adding an entire new dimension of trouble.)
        regards, tom lane

Alex Goncharov wrote:

> This is a misfeature for the benefit of edit-lazy users only.

+1

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/28/2014 05:20 AM, Alvaro Herrera wrote:
> Alex Goncharov wrote:
> 
>> This is a misfeature for the benefit of edit-lazy users only.
> 
> +1

+1

Joe

- -- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBAgAGBQJUT+MWAAoJEDfy90M199hllO8QAICsG86NK9hQE4QPLppIMkbv
E5qGAv0Hml/g+dKtsDpxo1f2L5dguQnAQD7ERZeEMWv0X28PixN+PDxfUVUjFKm2
5uI+OiJ60xM4oN+/eNgt4gaJWgk8osVmS9lx4jcGRTY+z9loxLQVUvWJ04nEdbV4
CJjcC6QZB0bpIDSkWYPZIB/YYrBLZ+/gI2GMWSIye8QutshHXd0Bca6gqWcgrBcK
sRVTVYd9hTNjnDLYPxSipuMJWHsyObMsSOnA0G9NuT+itj817uhOJhLOSq/Ctu02
C43CT4tM8Y0PX+EHhnkg2m0FMLeVYrSU8dCMI6MgjbSaghqjPRYHYjHoXyE9zqEk
M2Vw3qsqax0hx1AAJr//PIrCTf2Kc7T5K2soZkUXaIwrilrk7GTW1wtl+OCizn5D
Che5XCdwivh3m0Q6Wx/jtvdMqm1aJVA137kvHFLgR4SxkH2af/jhsP26ol5ieiAx
lno00w3JSjjeVl+EFzDTVBDsFD0FRffdlUxB1V+gUUQ+XvOdpfZEbyppQfP01+Bd
2WHnc2tYr0QJKWwnInFMeN+OHP7cOvE5C1I48DIZUSvI76astP3QWPtFXa30xFiA
CZUjzAKOqDQFvTpnHNMHoKRGCv28WZ525b5TPICWSO7EBaG2Uz/kMBvGXq4h6fOW
g9Rrm4UuMcMxoHhWoz2T
=JZC8
-----END PGP SIGNATURE-----

On Oct 24, 2014, at 6:36 AM, Alex Goncharov <alex.goncharov.usa@gmail.com> wrote:

> Another dimension of the trouble is breaking the operation of the
> tools that parse SQL statements for various purposes, e.g. for
> dependency analysis.

That’s a valid point.

> This is a misfeature for the benefit of edit-lazy users only.

This one, however, is more a judgment of people and their practices rather than the feature itself. Color me
unimpressed.

Best,

David

On 10/28/14, 4:25 PM, David E. Wheeler wrote:
>> This is a misfeature for the benefit of edit-lazy users only.
> This one, however, is more a judgment of people and their practices rather than the feature itself. Color me
unimpressed.

+1.

Having users sweat of comma placement in this day and age is pretty stupid. I can understand why we wouldn't want to
breakbackwards compatibility, but I think it does us and our users a disservice to dismiss the issue.
 

(BTW, I use a "comma first" formatting standard, so this doesn't actually effect me much, but I still find the original
complaintvery valid.)
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> On 10/28/14, 4:25 PM, David E. Wheeler wrote:
>> This one, however, is more a judgment of people and their practices rather than the feature itself. Color me
unimpressed.

> +1.

> Having users sweat of comma placement in this day and age is pretty stupid. I can understand why we wouldn't want to
breakbackwards compatibility, but I think it does us and our users a disservice to dismiss the issue.
 

I don't think anyone is just dismissing the issue.  But it is certainly a
judgment call as to whether the pros outweigh the cons, and I'm not seeing
a clear majority of us thinking they do.
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
>> On 10/28/14, 4:25 PM, David E. Wheeler wrote:
>>> This one, however, is more a judgment of people and their
>>> practices rather than the feature itself. Color me unimpressed.
>>
>> +1.
>>
>> Having users sweat of comma placement in this day and age is
>> pretty stupid. I can understand why we wouldn't want to break
>> backwards compatibility, but I think it does us and our users a
>> disservice to dismiss the issue.
>
> I don't think anyone is just dismissing the issue.  But it is
> certainly a judgment call as to whether the pros outweigh the
> cons, and I'm not seeing a clear majority of us thinking they do.

+1

My personal experience with products which allowed this (at least
in some circumstances) was that it occasionally saved me from an
"oops, ROLLBACK, retry" cycle.  I feel there is some value to that.

It also seems probable that some people on such products maintain
their DDL scripts in this format to minimize such cycles in their
current environments, and for them the lack of such behavior in
PostgreSQL will cause some extra bumps on the road to conversion.
Smoothing the road for conversion to PostgreSQL also seems to have
some value.

I feel that these are both fairly small benefits, so in the absence
of any information on the costs of developing and maintaining this,
I don't have any opinion on whether it is worth it.  I don't like
the scope of the initial proposal because that is not the place
where I noticed the feature in other products, and thus doubt that
it is the most important place to make the change, and think that
if we do it we had better have more consistency with it.  If
someone offered a more comprehensive patch, I could look at it and
develop an opinion on whether the cost/benefit ratio looked like it
was worth it.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Oct 28, 2014 at 7:59 PM, David Johnston
<david.g.johnston@gmail.com> wrote:
> I'd be much more inclined to favor this if the user is provided a capability
> to have warnings emitted whenever extraneous commas are present - either via
> some form of strict mode or linting configuration.

My experience with this kind of thing has not been positive.  You
write all your code in strict mode and have it turned on server-wide,
and then you want to install an extension which doesn't use strict
mode and you have to turn it off, but then you lose your own checks.
Uggh.

I think the discussion here is an example of taking a questionable
idea to its illogical conclusion.  I don't believe that a decision to
allow a trailing comma in a SELECT list means that we also have to
allow trailing commas in every single kind of comma-separated list we
have anywhere in PostgreSQL, right down to some obscure type's input
functions.  That's taking a simple idea that might possibly be worth
considering and expanding it into a giant project that nobody's ever
going to do, and certainly not correctly.

My personal vote is for not changing anything here at all.  But I
don't think it's got to be all-or-nothing.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company