zlacker

Richard Hipp, creator of SQLite, has implemented this in an experimental branch: 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.

>>samwil+(OP)
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!

>>samwil+(OP)
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.

>>simonw+z3
It's so ambitious in an almost boring way, exactly the right steward for a project like this

>>anitil+Zh
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.

>>Blackt+I2
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

...

>>Blackt+I2
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-...

>>willva+SG
What’s group-by-all? Sounds like distinct?

>>aidos+dK
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)

>>aidos+dK
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

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

>>bvrmn+XV
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.

>>maxbon+7n
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.)

>>foldr+011
To me it seemed like they had incompatible visions (SQLite wants to work in 2050 in the contexts it's been traditionally used in, libsql wants to modernize and lean into the more recent use cases) and so a fork was the appropriate and inevitable course of action.

Given that SQLite isn't really open to contribution (one of libsql's frustrations) it doesn't really worry me that they didn't & don't have a clear code of conduct. To me, digging through the repository [ETA: the website, rather] for what amounts to a cringey Easter egg and then linking to it as if it were a serious issue is uncalled for. To be honest, I think the complaints shouldn't stayed out of their announcement entirely - they have a legitimately cool vision for what their fork could be, and the complaints were only a distraction.

>>maxbon+y21
Yes, it's an important point that SQLite is not a project with an open contribution model. However, they do presumably accept external contributions in the form of bug reports, suggested patches, etc. etc.

You didn't have to dig through the repository to find the CoC. It was right there on the website at /codeofconduct.html: https://web.archive.org/web/20180315125217/https://www.sqlit...

>>foldr+S41
Another cool project from Dr. Hipp is the fossil SCM, which SQLite is developed in, and one of it's features is that it ships with a web view similar to GitHub. The website is actually the web view of the repo. (Apologies for expressing that in a confusing way, I knew it was on the website, I was referring to the website as the repository.)

>>willva+wM
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.

>>willva+SG
A special keyword like HAVING prevents erros by typing in the wrong line.

How is OR done with this WHERES?

>>maxbon+y21
The blatant religious discrimination in the document is both not a problem at all if the author is the only contributor (I suppose thetr must be some form of arms-length way of consuming external support from less beholden entities; I don't know the details of Critical Code of Conduct Theory), and totally unacceptable otherwise.

Following the document itself, it should be rewritten if it ever intends to include other people, and should be explicitly clarified that the current form only applies to the author himself.

>>Blackt+I2
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

>>geneze+JN
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.