4 ms·

Richard Hipp, creator of SQLite, has implemented this in an experimental branch: https://sqlite.org/forum/forumpost/5f218012b6e1a9db https://sqlite.org/forum/fo

Richard Hipp, creator of SQLite, has implemented this in an experimental branch: https://sqlite.org/forum/forumpost/5f218012b6e1a9db https://sqlite.org/forum/forumpost/5f218012b6e1a9db Worth reading the thread, there are some good insights. It looks like he will be waiting on Postgres to take the initiative on implementing this before it makes it into a release.

Blackthorn 2y ago
FROM first would be nothing short of incredible. I can only hope that Postgres and others can find it within themselves to get together and standardize on such an extension!
- willvarfar 2y ago
  Yeap I didn't know DuckDB supported it already! Being able to do SELECT FROM WHERE in any order and allowing multiple WHEREs and AGGREGATE etc, combined with supporting trailing commas, makes copy pasting templating and reusing and code-generating SQL so much easier. FROM table <-- at this point there is an implicit SELECT * SELECT whatever WHERE some_filter WHERE another_filter <-- this is like AND AGGREGATE something WHERE a_filter_that_is_after_grouping <-- is like HAVING ORDER BY ALL <-- group-by-all is great in engines that support it; want it for ordering too ...
  - aidos 2y ago
    What’s group-by-all? Sounds like distinct?
    - willvarfar 2y ago
      Normally the SELECT has a bunch of columns to group by and a bunch of columns that are aggregates. Then, in the GROUP BY clause, you have to list all the columns to group by. The query compiler knows which they are, and polices you, making sure you got it right. All the GROUP BY ALL does is say 'the compiler knows, there's no need to list them all'. Very convenient. BigQuery supports GROUP BY ALL and it really cleans up lots of queries. E.g. SELECT foo, bar, SUM(baz) FROM x GROUP BY ALL <-- equiv to GROUP BY foo, bar (eh, except MySQL; my memory of MySQL is it will silently do ANY_VALUE() on any columns that aren't an explicit aggregate function but are not grouped; argh it was a long time ago)
      - Sesse__ 2y ago
        MySQL doesn't do this anymore; the ONLY_FULL_GROUP_BY mode became default in 5.7 (I think). You can still turn it off and get the old behavior, though.
    - genezeta 2y ago
      It's different from distinct. Distinct just eliminates duplicates but does not group entries. Suppose... SELECT brand, model, revision, SUM(quantity) FROM stock GROUP BY brand, model, revision This is not solved by using distinct as you would not get the correct count. Group By All allows you to write it a bit more compact... SELECT brand, model, revision, SUM(quantity) FROM stock GROUP BY ALL
      - aidos 2y ago
        Gotcha. Thanks. That’s actually super useful! Looks like Postgres doesn’t implement it unfortunately. I revert to “group by 1, 2, 3… “ when I’m just hacking about. Group by all would definitely be an improvement.
  - croes 2y ago
    A special keyword like HAVING prevents erros by typing in the wrong line. How is OR done with this WHERES?
- pradeepchhetri 2y ago
  This syntax looks a lot like PRQL. ClickHouse supports writing queries in PRQL dialect. Moreover, ClickHouse also supports Kusto dialect too. https://clickhouse.com/docs/en/guides/developer/alternative-query-languages https://clickhouse.com/docs/en/guides/developer/alternative-...
- quartesixte 2y ago
  What exactly is the history of having FROM be the second item, and not the first? Because FROM first seems more intuitive and actually the way you write out queries. Really hope this takes off and gets more widespread adoption because I really want to stop doing: SELECT * FROM all_the_joins into SELECT {my statements here} FROM all_the_joins
simonw 2y ago
That comment where he explains why he's not rushing to add new unproven SQL syntax to SQLite is fascinating: > My goal is to keep SQLite relevant and viable through the year 2050. That's a long time from now. If I knew that standard SQL was not going to change any between now and then, I'd go ahead and make non-standard extensions that allowed for FROM-clause-first queries, as that seems like a useful extension. The problem is that standard SQL will not remain static. Probably some future version of "standard SQL" will support some kind of FROM-clause-first query format. I need to ensure that whatever SQLite supports will be compatible with the standard, whenever it drops. And the only way to do that is to support nothing until after the standard appears.
- anitil 2y ago
  It's so ambitious in an almost boring way, exactly the right steward for a project like this
  - maxbond 2y ago
    Dr. Hipp is one of my heroes. He seems to labor quietly in semi obscurity for decades, and at the end of it he's produced some amazing software. I was tickled by the curfuffle over his use of a set of guidelines for living in a Christian monastery as SQLite's code of ethics for the purpose of checking a box on an RFQ (part of the fallout of the libsql fork), because he does seem like a sort of programmer monk. (For what it's worth, as an agnostic, I've read them several times and found them unobjectionable. While I think the drama was unnecessary, the libsql people are doing interesting work.) I choose never to meet this man and be disabused of this notion. Shine on, doctor.
    - foldr 2y ago
      In fairness, I think the complaint over the tongue-in-cheek 'code of conduct' was that it was transparently unsuitable if considered as an actual code of conduct (i.e. a list of rules that SQLite contributors must obey in order to participate in the project). For example, it seems unlikely that Dr. Hipp would wish to exclude contributors who have committed adultery, or who do not pray with sufficient frequency. (The erstwhile code of conduct is now labeled a 'code of ethics', and AFAIK SQLite has no official CoC currently.)
bvrmn 2y ago
It's funny how he addresses the new syntax as "from-clause-first". Like a very minor change with a low value.
- Cthulhu_ 2y ago
  I think that's important, because a lot of concepts are presented as prohibitively complicated; for example, functional programming makes sense in my head, but if you present it as lambda calculus and write it in concise form with new operators, you lost me.

Blackthorn 2y ago

FROM first would be nothing short of incredible. I can only hope that Postgres and others can find it within themselves to get together and standardize on such an extension!

willvarfar 2y ago
Yeap I didn't know DuckDB supported it already! Being able to do SELECT FROM WHERE in any order and allowing multiple WHEREs and AGGREGATE etc, combined with supporting trailing commas, makes copy pasting templating and reusing and code-generating SQL so much easier. FROM table <-- at this point there is an implicit SELECT * SELECT whatever WHERE some_filter WHERE another_filter <-- this is like AND AGGREGATE something WHERE a_filter_that_is_after_grouping <-- is like HAVING ORDER BY ALL <-- group-by-all is great in engines that support it; want it for ordering too ...
- aidos 2y ago
  What’s group-by-all? Sounds like distinct?
  - willvarfar 2y ago
    Normally the SELECT has a bunch of columns to group by and a bunch of columns that are aggregates. Then, in the GROUP BY clause, you have to list all the columns to group by. The query compiler knows which they are, and polices you, making sure you got it right. All the GROUP BY ALL does is say 'the compiler knows, there's no need to list them all'. Very convenient. BigQuery supports GROUP BY ALL and it really cleans up lots of queries. E.g. SELECT foo, bar, SUM(baz) FROM x GROUP BY ALL <-- equiv to GROUP BY foo, bar (eh, except MySQL; my memory of MySQL is it will silently do ANY_VALUE() on any columns that aren't an explicit aggregate function but are not grouped; argh it was a long time ago)
    - Sesse__ 2y ago
      MySQL doesn't do this anymore; the ONLY_FULL_GROUP_BY mode became default in 5.7 (I think). You can still turn it off and get the old behavior, though.
  - genezeta 2y ago
    It's different from distinct. Distinct just eliminates duplicates but does not group entries. Suppose... SELECT brand, model, revision, SUM(quantity) FROM stock GROUP BY brand, model, revision This is not solved by using distinct as you would not get the correct count. Group By All allows you to write it a bit more compact... SELECT brand, model, revision, SUM(quantity) FROM stock GROUP BY ALL
    - aidos 2y ago
      Gotcha. Thanks. That’s actually super useful! Looks like Postgres doesn’t implement it unfortunately. I revert to “group by 1, 2, 3… “ when I’m just hacking about. Group by all would definitely be an improvement.
- croes 2y ago
  A special keyword like HAVING prevents erros by typing in the wrong line. How is OR done with this WHERES?
pradeepchhetri 2y ago
This syntax looks a lot like PRQL. ClickHouse supports writing queries in PRQL dialect. Moreover, ClickHouse also supports Kusto dialect too. https://clickhouse.com/docs/en/guides/developer/alternative-query-languages https://clickhouse.com/docs/en/guides/developer/alternative-...
quartesixte 2y ago
What exactly is the history of having FROM be the second item, and not the first? Because FROM first seems more intuitive and actually the way you write out queries. Really hope this takes off and gets more widespread adoption because I really want to stop doing: SELECT * FROM all_the_joins into SELECT {my statements here} FROM all_the_joins

simonw 2y ago

That comment where he explains why he's not rushing to add new unproven SQL syntax to SQLite is fascinating: > My goal is to keep SQLite relevant and viable through the year 2050. That's a long time from now. If I knew that standard SQL was not going to change any between now and then, I'd go ahead and make non-standard extensions that allowed for FROM-clause-first queries, as that seems like a useful extension. The problem is that standard SQL will not remain static. Probably some future version of "standard SQL" will support some kind of FROM-clause-first query format. I need to ensure that whatever SQLite supports will be compatible with the standard, whenever it drops. And the only way to do that is to support nothing until after the standard appears.

anitil 2y ago
It's so ambitious in an almost boring way, exactly the right steward for a project like this
- maxbond 2y ago
  Dr. Hipp is one of my heroes. He seems to labor quietly in semi obscurity for decades, and at the end of it he's produced some amazing software. I was tickled by the curfuffle over his use of a set of guidelines for living in a Christian monastery as SQLite's code of ethics for the purpose of checking a box on an RFQ (part of the fallout of the libsql fork), because he does seem like a sort of programmer monk. (For what it's worth, as an agnostic, I've read them several times and found them unobjectionable. While I think the drama was unnecessary, the libsql people are doing interesting work.) I choose never to meet this man and be disabused of this notion. Shine on, doctor.
  - foldr 2y ago
    In fairness, I think the complaint over the tongue-in-cheek 'code of conduct' was that it was transparently unsuitable if considered as an actual code of conduct (i.e. a list of rules that SQLite contributors must obey in order to participate in the project). For example, it seems unlikely that Dr. Hipp would wish to exclude contributors who have committed adultery, or who do not pray with sufficient frequency. (The erstwhile code of conduct is now labeled a 'code of ethics', and AFAIK SQLite has no official CoC currently.)

bvrmn 2y ago

It's funny how he addresses the new syntax as "from-clause-first". Like a very minor change with a low value.

Cthulhu_ 2y ago
I think that's important, because a lot of concepts are presented as prohibitively complicated; for example, functional programming makes sense in my head, but if you present it as lambda calculus and write it in concise form with new operators, you lost me.