[roncohen]

Lots of great additions. I will just highlight two:

Column selection:

When you have tons of columns these become useful. Clickhouse takes it to the next level and supports APPLY and COLUMN in addition to EXCEPT, REPLACE which DuckDB supports:

    - APPLY: apply a function to a set of columns
    - COLUMN: select columns by matching a regular expression (!)

Details here: https://clickhouse.com/docs/en/sql-reference/statements/sele...

Allow trailing commas:

I can't count how many times I've run into a problem with a trailing comma. There's a whole convention developed to overcome this: the prefix comma convention where you'd write:

    SELECT
      first_column
      ,second_column
      ,third_column

which lets you easily comment out a line without worrying about trailing comma errors. That's no longer necessary in DuckDB. Allowing for trailing commas should get included in the SQL spec.

[nicoburns]

> Allowing for trailing commas should get included in the SQL spec.

Yep! That would be my #1 request for SQL. Seems ridiculous that it's not supported already.

[layer8]

I agree, though you can always use a dummy value as a workaround:

  SELECT
      first_column,
      second_column,
      third_column,
      null

[_dark_matter_]

BigQuery also supports trailing commas!

[snidane]

Allow referencing columns defined previously in the same query would make duckdb competitive for data analytics. Without that one has to chain With statements for just the tiniest operations.

  select 1 as x, x + 2 as y, y/x as z;

[flakiness]

There is a bug for that and it looks someone is even working on it. https://github.com/duckdb/duckdb/issues/1547

[1egg0myegg0]

Yes, good thought! That is listed at the bottom of the article as something we are looking at for the future.

[gigatexal]

Yep! Agreed!

[zasdffaa]

(nothing to do with DuckDB but..) SQL is complex enough, and allowing this (and acyclically as mentioned below) would do my $%^& nut implementing it.

But I know a user requirement when I hear one, so can you give me an large, real example of where allowing this would make things easier? That would be mega helpful, ta

[gregmac]

Yes, trailing commas should work everywhere!

JSON is the other one where it annoys me, but luckily I rarely hand-write any JSON anymore (and there are semi-solutions for this like json5).

In code I always add trailing commas to anything comma-separated. It makes editing simpler (you can shuffle lines without thinking about commas). In a diff or blame it doesn't show adding a comma as a change.

SQL is the one spot where this doesn't work, and it's a constant foot-gun as I often don't remember until I run and get a syntax error.

[skrtskrt]

JSONC allows comments and trailing commas, but adoption seems to be low.

VSCode uses it for configuration, but when I wanted to use it in Python (to add context to source-controlled Elasticsearch schemas) there were only a couple old barely-maintained libraries for parsing.

[go_prodev]

EXCEPT columns would get my vote for ansi standard SQL adoption. So much time is spent selecting all but a few columns.

[skeeter2020]

You can do the same thing with your WHERE clause and ANDs by always starting them WHERE 1=1 as well.

>> Allowing for trailing commas should get included in the SQL spec.

So there is no "SQL spec" per se, there's an ANSI specification with decades of convention and provider-specific customizations piled on top. This support for trailing commas is the best you're going to get.

[1egg0myegg0]

Thank you for the feedback! I will check those Clickhouse features out. I totally agree on the trailing commas, and I use commas first syntax for that same reason! But maybe not anymore... :-)

[franga2000]

> Allowing for trailing commas should get included in the SQL spec

Not just SQL, trailing commas are stupidly useful and convenient, so as far as I'm concerned every language should have them. To be fair, a decent amount of them have implemented them (I was pleasantly surprised by GCC C), but there are still notable holdouts (JSON!).

[throw_away]

Are leading commas allowed? Because otherwise, you've just traded out the inability to comment out the last element for the inability to comment out the first. I never understood this convention.

[sagarm]

I agree that it's ugly and don't use it myself, but I find that I modify the last item in a list far more frequently than the first. Probably because the grouping columns tend to go first by convention, and these change less.

[IshKebab]

Matching columns by regular expression sounds like a terrible feature. Talk about bug-prone!