8 ms·

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

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/select/#select-modifiers 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.

go_prodev 4y ago
EXCEPT columns would get my vote for ansi standard SQL adoption. So much time is spent selecting all but a few columns.
1egg0myegg0 4y ago
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... :-)
nicoburns 4y ago
> 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 4y ago
  I agree, though you can always use a dummy value as a workaround: SELECT first_column, second_column, third_column, null
- _dark_matter_ 4y ago
  BigQuery also supports trailing commas!
snidane 4y ago
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;
- 1egg0myegg0 4y ago
  Yes, good thought! That is listed at the bottom of the article as something we are looking at for the future.
- flakiness 4y ago
  There is a bug for that and it looks someone is even working on it. https://github.com/duckdb/duckdb/issues/1547 https://github.com/duckdb/duckdb/issues/1547
  - karmakaze 4y ago
    There's also no need to make it left to right usage, as long as it's acyclic: select y-2 as x, 3 as y, y/x as z;
    - sonthonax 4y ago
      Would this be compiled into a graph of subqueries and window statements?
      - karmakaze 4y ago
        I'm not following, the original could be written as select x + 2 as y, 1 as x, y/x as z; with the same column values in a different order. Order of arguments shouldn't matter is all I was saying.
- gigatexal 4y ago
  Yep! Agreed!
- zasdffaa 4y ago
gregmac 4y ago
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 4y ago
  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.
  - yunohn 4y ago
    > there were only a couple old barely-maintained libraries for parsing. Do they work, though? If it’s a mostly stable standard, doesn’t seem like you’d need a frequently updated parser.
  - nicoburns 4y ago
    You can often find better maintained libraries for json5, which is a superset of jsonc
skeeter2020 4y ago
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.
IshKebab 4y ago
Matching columns by regular expression sounds like a terrible feature. Talk about bug-prone!
franga2000 4y ago
> 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 4y ago
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 4y ago
  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.

go_prodev 4y ago

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

1egg0myegg0 4y ago

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... :-)

nicoburns 4y ago

> 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 4y ago
I agree, though you can always use a dummy value as a workaround: SELECT first_column, second_column, third_column, null
_dark_matter_ 4y ago
BigQuery also supports trailing commas!

snidane 4y ago

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;

1egg0myegg0 4y ago
Yes, good thought! That is listed at the bottom of the article as something we are looking at for the future.
flakiness 4y ago
There is a bug for that and it looks someone is even working on it. https://github.com/duckdb/duckdb/issues/1547 https://github.com/duckdb/duckdb/issues/1547
- karmakaze 4y ago
  There's also no need to make it left to right usage, as long as it's acyclic: select y-2 as x, 3 as y, y/x as z;
  - sonthonax 4y ago
    Would this be compiled into a graph of subqueries and window statements?
    - karmakaze 4y ago
      I'm not following, the original could be written as select x + 2 as y, 1 as x, y/x as z; with the same column values in a different order. Order of arguments shouldn't matter is all I was saying.
gigatexal 4y ago
Yep! Agreed!
zasdffaa 4y ago

gregmac 4y ago

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 4y ago
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.
- yunohn 4y ago
  > there were only a couple old barely-maintained libraries for parsing. Do they work, though? If it’s a mostly stable standard, doesn’t seem like you’d need a frequently updated parser.
- nicoburns 4y ago
  You can often find better maintained libraries for json5, which is a superset of jsonc

skeeter2020 4y ago

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.

IshKebab 4y ago

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

franga2000 4y ago

> 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 4y ago

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 4y ago
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.