Interesting paper from Google on pipe syntax on SQL (2024)

SQL Has Problems. We Can Fix Them: Pipe Syntax in SQL

I also don't like the pipe syntax very much, but I think there is some value in an optional re-order of SELECT and FROM clauses. Like having the SELECT clause come after FROM, like:

FROM usersSELECT first_name, last_nameWHERE id = 123;

Or even have the SELECT be entirely optional, like FROM users WHERE id = 123 (where SELECT * becomes the default).

I think this paper and other "compile-to-SQL" languages like PRQL and Logica and Malloy offer some syntax sugar that really don't have sense in pre-existing SQL databases like SQLite. But all of them have a "FROM-first" mentality, which fits a lot of developer's mental models of how SQL queries are ran.

DuckDB offers FROM-first in their SQL, though not sure if other SQL databases also do this.

For a limited time only, there is a bootleg https://sqlite.org/fiddleon-line that supports some of the proposed "pipe" syntax outlined inthe Google paper. This is but a quick prototype. You can expect to find bugs.

Differences from the syntax described in the paper:

• The "|>" operator is optional, except before the AS pipe operator. Before AS, you have to include |> to avoid a syntax ambiguity. If (like me) you dislike the |> operator, you can say "VIEW <name>" instead of "AS <name>" to accomplish the same thing and the |> operator is not required before VIEW.

• The following pipe operators are not implemented: EXTEND, SET, DROP, CALL, TABLESAMPLE, PIVOT, UNPIVOT.

Thanks for the fiddle.

notes while experimenting:

1. While exploring the "pipe" syntax it might be useful to have some sort less verbose CAST() so that when enforcing affinity/datatypes (i.e. when expressions are in the pipes) the CAST() doesn't make reading the query more difficult. Because other SQL implementations don't use SQLite's flexible datatypes/affinity the Google folks won't necessarily raise the awkward CAST() in their paper.

2. For EXTEND, "select *, cast([expr] as [type]) as [column]" was used.

3. For SET, all columns are individually specified (except for the "SET" column), and the EXTEND substitution was used.

4. For DROP, again, all columns individually specified except for the "DROP" column.

5. There may be some push through optimisations (as opposed to the push down optimisations) such as dropping columns that end up not being used.

6. operational note (operator error): When writing ad hoc SQL, missing a ; between statements will take some getting used to. For example several statements in the fiddle, perhaps meant to run individually will now pipe to each other rather than give an error. For example:

FROM (select 2 as id) /* without ; */select 1 as id

All in all, IMAO (In My Anonymous Opinion) piping seems to worth a look.

Worth a look, maybe, but not worth landing on trunk.

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 goingto change any between now and then, I'd go ahead and make non-standardextensions that allowed for FROM-clause-first queries, as that seemslike a useful extension. The problem is that standard SQL will notremain static. Probably some future version of "standard SQL" will supportsome kind of FROM-clause-first query format. I need to ensure that whateverSQLite supports will be compatible with the standard, whenever it drops.And the only way to do that is to support nothing until after the standardappears.

When will that happen? A month? A year? Ten years? Who knows.

I'll probably take my cue from PostgreSQL.If PostgreSQL adds support for FROM-clause-first queries, then I'll dothe same with SQLite, copying the PostgreSQL syntax. Until then, I'mafraid you are stuck with only traditional SELECT-first queries in SQLite.

I'm not overly impressed with the Google pipe syntax. They talk a goodgame in the paper. They sound really convincing. But once I startedactually looking for use cases and messing with it, I, for one, lostinterest in their design. I have hundreds, perhaps thousands, of queriesin the Fossil implementation whichI went trolling through, and I didn't find any that would be improvedby Google pipe syntax over just having FROM-first queries. Maybe Googleis more focused on analytical queries against big data. Maybe the pipe syntaxworks better for really gnarly analytical queries. It doesn't seem to helpfor the kinds of queries I do in Fossil using SQLite, though, at least notthat I've seen.

Doing the prototype implementation and working through the issues was auseful exercise. I now know that SQLite can be easily converted tosupport Google pipe syntax and/or FORM-first queries, should the needarise, simply by adding a few new production rules to the LALR(1) grammar,and with no changes to the query planner/optimize. But the present needis insufficient to overcome the future incompatibility risk.