Allowing additional commas between columns, and at the end of the SELECT clause
Hello!
I have created a patch to allow additional commas between columns, and
at the end of the SELECT clause.
Motivation:
Commas of this type are allowed in many programming languages, in some
it is even recommended to use them at the ends of lists or objects. A
new generation of programmers expects a more forgiving language just as
our generation enjoyed LIMIT and the ability to write `select` in lowercase.
Accepted:
SELECT 1,;
SELECT 1,,,,,;
SELECT *, from information_schema.sql_features;
(...) RETURNING a,,b,c,;
Not accepted:
SELECT ,;
SELECT ,1;
SELECT ,,,;
Advantages:
- simplifies the creation and debugging of queries by reducing the most
common syntax error,
- eliminates the need to use the popular `1::int as dummy` at the end of
a SELECT list,
- simplifies query generators,
- the query is still deterministic,
Disadvantages:
- counting of returned columns can be difficult,
- syntax checkers will still report errors,
- probably not SQL standard compliant,
- functionality can be controversial,
I attach the patch along with the tests.
What do you think?
Your opinions are very much welcome!
Attachments:
0001-additional_commas.patchtext/plain; charset=UTF-8; name=0001-additional_commas.patchDownload
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e8b619926e..8bf0a2690e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -17174,6 +17174,7 @@ opt_target_list: target_list { $$ = $1; }
target_list:
target_el { $$ = list_make1($1); }
| target_list ',' target_el { $$ = lappend($1, $3); }
+ | target_list ',' /* Handling trailing comma */
;
target_el: a_expr AS ColLabel
diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out
index cb51bb8687..8604e50e18 100644
--- a/src/test/regress/expected/returning.out
+++ b/src/test/regress/expected/returning.out
@@ -238,7 +238,7 @@ CREATE OR REPLACE RULE voo_u AS ON UPDATE TO voo DO INSTEAD
UPDATE foo SET f1 = new.f1, f2 = new.f2 WHERE f1 = old.f1
RETURNING f1, f2;
update voo set f1 = f1 + 1 where f2 = 'zoo2';
-update voo set f1 = f1 + 1 where f2 = 'zoo2' RETURNING *, f1*2;
+update voo set f1 = f1 + 1 where f2 = 'zoo2' RETURNING *, f1*2,;
f1 | f2 | ?column?
----+------+----------
16 | zoo2 | 32
@@ -264,7 +264,7 @@ SELECT * FROM voo;
CREATE OR REPLACE RULE voo_d AS ON DELETE TO voo DO INSTEAD
DELETE FROM foo WHERE f1 = old.f1
- RETURNING f1, f2;
+ RETURNING f1, , , , f2;
DELETE FROM foo WHERE f1 = 13;
DELETE FROM foo WHERE f2 = 'zit' RETURNING *;
f1 | f2 | f3 | f4
diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out
index 33a6dceb0e..e4b9e916da 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -968,3 +968,50 @@ explain (costs off) select * from list_parted_tbl;
(2 rows)
drop table list_parted_tbl;
+-- Handling trailing comma
+SELECT *, FROM onek
+ WHERE onek.unique1 < 10
+ ORDER BY onek.unique1;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+ 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx
+ 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx
+ 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx
+ 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx
+ 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx
+ 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx
+ 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx
+ 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx
+ 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx
+ 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx
+(10 rows)
+
+SELECT 1,,2,,,3 as dummy,;
+ ?column? | ?column? | dummy
+----------+----------+-------
+ 1 | 2 | 3
+(1 row)
+
+SELECT count(*),;
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*), FROM onek;
+ count
+-------
+ 1000
+(1 row)
+
+SELECT v.k as x, FROM (VALUES(1)) v(k);
+ x
+---
+ 1
+(1 row)
+
+-- Error is expected
+SELECT ,;
+ERROR: syntax error at or near ","
+LINE 1: SELECT ,;
+ ^
diff --git a/src/test/regress/sql/returning.sql b/src/test/regress/sql/returning.sql
index a460f82fb7..a3cb0586af 100644
--- a/src/test/regress/sql/returning.sql
+++ b/src/test/regress/sql/returning.sql
@@ -117,14 +117,14 @@ CREATE OR REPLACE RULE voo_u AS ON UPDATE TO voo DO INSTEAD
RETURNING f1, f2;
update voo set f1 = f1 + 1 where f2 = 'zoo2';
-update voo set f1 = f1 + 1 where f2 = 'zoo2' RETURNING *, f1*2;
+update voo set f1 = f1 + 1 where f2 = 'zoo2' RETURNING *, f1*2,;
SELECT * FROM foo;
SELECT * FROM voo;
CREATE OR REPLACE RULE voo_d AS ON DELETE TO voo DO INSTEAD
DELETE FROM foo WHERE f1 = old.f1
- RETURNING f1, f2;
+ RETURNING f1, , , , f2;
DELETE FROM foo WHERE f1 = 13;
DELETE FROM foo WHERE f2 = 'zit' RETURNING *;
diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql
index 019f1e7673..e98794c135 100644
--- a/src/test/regress/sql/select.sql
+++ b/src/test/regress/sql/select.sql
@@ -262,3 +262,20 @@ create table list_parted_tbl1 partition of list_parted_tbl
for values in (1) partition by list(b);
explain (costs off) select * from list_parted_tbl;
drop table list_parted_tbl;
+
+
+-- Handling trailing comma
+SELECT *, FROM onek
+ WHERE onek.unique1 < 10
+ ORDER BY onek.unique1;
+
+SELECT 1,,2,,,3 as dummy,;
+
+SELECT count(*),;
+
+SELECT count(*), FROM onek;
+
+SELECT v.k as x, FROM (VALUES(1)) v(k);
+
+-- Error is expected
+SELECT ,;
On Mon, 13 May 2024 at 10:42, Artur Formella <artur.formella3@gmail.com> wrote:
Motivation:
Commas of this type are allowed in many programming languages, in some
it is even recommended to use them at the ends of lists or objects.
Single trailing commas are a feature that's more and more common in
languages, yes, but arbitrary excess commas is new to me. Could you
provide some examples of popular languages which have that, as I can't
think of any.
Accepted:
SELECT 1,;
SELECT 1,,,,,;
SELECT *, from information_schema.sql_features;
(...) RETURNING a,,b,c,;Not accepted:
SELECT ,;
SELECT ,1;
SELECT ,,,;Advantages:
- simplifies the creation and debugging of queries by reducing the most
common syntax error,
- eliminates the need to use the popular `1::int as dummy` at the end of
a SELECT list,
This is the first time I've heard of this `1 as dummy`.
- simplifies query generators,
- the query is still deterministic,
What part of a query would (or would not) be deterministic? I don't
think I understand the potential concern here. Is it about whether the
statement can be parsed deterministically?
Disadvantages:
- counting of returned columns can be difficult,
- syntax checkers will still report errors,
- probably not SQL standard compliant,
I'd argue you better raise this with the standard committee if this
isn't compliant. I don't see enough added value to break standard
compliance here, especially when the standard may at some point allow
only a single trailing comma (and not arbitrarily many).
What do you think?
Do you expect `SELECT 1,,,,,,,` to have an equivalent query identifier
to `SELECT 1;` in pg_stat_statements? Why, or why not?
Overall, I don't think unlimited commas is a good feature. A trailing
comma in the select list would be less problematic, but I'd still want
to follow the standard first and foremost.
Kind regards,
Matthias van de Meent
Neon (https://neon.tech)
Hi,
As a developer, I love this feature.
But as a developer of an universal TDOP SQL parser[1]https://gitlab.com/dalibo/transqlate, this can be a
pain. Please request it to the standard.
Regards,
Étienne
Matthias van de Meent <boekewurm+postgres@gmail.com> writes:
On Mon, 13 May 2024 at 10:42, Artur Formella <artur.formella3@gmail.com> wrote:
Motivation:
Commas of this type are allowed in many programming languages, in some
it is even recommended to use them at the ends of lists or objects.Single trailing commas are a feature that's more and more common in
languages, yes, but arbitrary excess commas is new to me. Could you
provide some examples of popular languages which have that, as I can't
think of any.
The only one I can think of is Perl, which I'm not sure counts as
popular any more. JavaScript allows consecutive commas in array
literals, but they're not no-ops, they create empty array slots:
❯ js
Welcome to Node.js v18.19.0.
Type ".help" for more information.
[1,,2,,]
[ 1, <1 empty item>, 2, <1 empty item> ]
- ilmari
=?utf-8?Q?Dagfinn_Ilmari_Manns=C3=A5ker?= <ilmari@ilmari.org> writes:
Matthias van de Meent <boekewurm+postgres@gmail.com> writes:
Single trailing commas are a feature that's more and more common in
languages, yes, but arbitrary excess commas is new to me. Could you
provide some examples of popular languages which have that, as I can't
think of any.
The only one I can think of is Perl, which I'm not sure counts as
popular any more. JavaScript allows consecutive commas in array
literals, but they're not no-ops, they create empty array slots:
I'm fairly down on this idea for SQL, because I think it creates
ambiguity for the ROW() constructor syntax. That is:
(x,y) is understood to be shorthand for ROW(x,y)
(x) is not ROW(x), it's just x
(x,) means what?
I realize the original proposal intended to restrict the legality of
excess commas to only a couple of places, but to me that just flags
it as a kluge. ROW(...) ought to work pretty much the same as a
SELECT list.
As already mentioned, if you can get some variant of this through the
SQL standards process, we'll probably adopt it. But I doubt that we
want to get out front of the committee in this area.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
=?utf-8?Q?Dagfinn_Ilmari_Manns=C3=A5ker?= <ilmari@ilmari.org> writes:
Matthias van de Meent <boekewurm+postgres@gmail.com> writes:
Single trailing commas are a feature that's more and more common in
languages, yes, but arbitrary excess commas is new to me. Could you
provide some examples of popular languages which have that, as I can't
think of any.The only one I can think of is Perl, which I'm not sure counts as
popular any more. JavaScript allows consecutive commas in array
literals, but they're not no-ops, they create empty array slots:I'm fairly down on this idea for SQL, because I think it creates
ambiguity for the ROW() constructor syntax. That is:(x,y) is understood to be shorthand for ROW(x,y)
(x) is not ROW(x), it's just x
(x,) means what?
Python has a similar issue: (x, y) is a tuple, but (x) is just x, and
they use the trailing comma to disambiguate, so (x,) creates a
single-item tuple. AFAIK it's the only place where the trailing comma
is significant.
I realize the original proposal intended to restrict the legality of
excess commas to only a couple of places, but to me that just flags
it as a kluge. ROW(...) ought to work pretty much the same as a
SELECT list.
Yeah, a more principled approach would be to not special-case target
lists, but to allow one (and only one) trailing comma everywhere:
select, order by, group by, array constructors, row constructors,
everything that looks like a function call, etc.
As already mentioned, if you can get some variant of this through the
SQL standards process, we'll probably adopt it. But I doubt that we
want to get out front of the committee in this area.
Agreed.
regards, tom lane
- ilmari
=?utf-8?Q?Dagfinn_Ilmari_Manns=C3=A5ker?= <ilmari@ilmari.org> writes:
Tom Lane <tgl@sss.pgh.pa.us> writes:
I'm fairly down on this idea for SQL, because I think it creates
ambiguity for the ROW() constructor syntax. That is:
(x,y) is understood to be shorthand for ROW(x,y)
(x) is not ROW(x), it's just x
(x,) means what?
Python has a similar issue: (x, y) is a tuple, but (x) is just x, and
they use the trailing comma to disambiguate, so (x,) creates a
single-item tuple. AFAIK it's the only place where the trailing comma
is significant.
Ugh :-(. The semantic principle I'd prefer to have here is "a trailing
comma is ignored", but what they did breaks that. But then again,
I'm not particularly a fan of anything about Python's syntax.
Yeah, a more principled approach would be to not special-case target
lists, but to allow one (and only one) trailing comma everywhere:
select, order by, group by, array constructors, row constructors,
everything that looks like a function call, etc.
If it can be made to work everywhere, that would get my vote.
I'm not sure if any other ambiguities arise, though. SQL has
a lot of weird syntax corners (and the committee keeps adding
more :-().
regards, tom lane
On 13.05.2024 11:24, Matthias van de Meent wrote:
On Mon, 13 May 2024 at 10:42, Artur Formella<artur.formella3@gmail.com> wrote:
Motivation:
Commas of this type are allowed in many programming languages, in some
it is even recommended to use them at the ends of lists or objects.Single trailing commas are a feature that's more and more common in
languages, yes, but arbitrary excess commas is new to me. Could you
provide some examples of popular languages which have that, as I can't
think of any.
Thank for your comment.
I meant commas are recommended at the end of the list. Sorry for the
lack of precision.
Typescript has a popular directive "rules": { "trailing-comma": false }
in the tslint.json file, which forces trailing commas. Popular Airbnb
coding style require trailing commas by eslint
(https://github.com/airbnb/javascript?tab=readme-ov-file#functions--signature-invocation-indentation).
This is the first time I've heard of this `1 as dummy`.
dummy column is a popular way to end SELECT list on R&D phase to avoid
the most common syntax error. This way you don't have to pay attention
to commas.
SELECT <hacking /> , 1::int AS ignoreme FROM <hacking />
- simplifies query generators,
- the query is still deterministic,What part of a query would (or would not) be deterministic? I don't
think I understand the potential concern here. Is it about whether the
statement can be parsed deterministically?
Bison doesn't report error or conflict.
I'd argue you better raise this with the standard committee if this
isn't compliant. I don't see enough added value to break standard
compliance here, especially when the standard may at some point allow
only a single trailing comma (and not arbitrarily many).Do you expect `SELECT 1,,,,,,,` to have an equivalent query identifier
to `SELECT 1;` in pg_stat_statements? Why, or why not?
I don't know, I have a feeling that the queries are equivalent, but I
don't know the mechanism.
Overall, I don't think unlimited commas is a good feature. A trailing
comma in the select list would be less problematic, but I'd still want
to follow the standard first and foremost.
I will prepare a patch with trailing comma only tomorrow.
Thank you.
Artur