googlesql

I'm as big a SQL stan as the next person and I'm also very skeptical anytime anyone says that SQL needs to be replaced. At the same time, it's challenging that SQL cannot be iteratively improved and experimented upon. IMHO, PRQL is a reasonable approach to extending SQL without replacing SQL. But what I'd love to see is projects like Google's zeta-sql [1] and Substrait [2] get more traction. It would provide a more stable, standardized foundation upon which SQL could be improved, which would make the case for "SQL forever" even more strong. I've blogged about this before [3]. [1]: https://github.com/google/googlesql [2]: https://substrait.io/ [3]: https://roundup.getdbt.com/p/problem-exists-between-database...

SQL with pipe syntax can also do functions like that. An sequence of pipe operators can be saved as a table-valued function, and then reused by invoking in other queries with CALL. Example: CREATE TEMP TABLE FUNCTION ExtendDates(input ANY TABLE, num_days INT64) AS FROM input |> EXTEND date AS original_date |> EXTEND max(date) OVER () AS max_date |> JOIN UNNEST(generate_array(0, num_days - 1)) diff_days |> SET date = date_add(date, INTERVAL diff_days DAY) |> WHERE date <= max_date |> SELECT * EXCEPT (max_date, diff_days); FROM Orders |> RENAME o_orderdate AS date, o_custkey AS user_id |> CALL ExtendDates(7) |> LIMIT 10; from the script here: https://github.com/google/zetasql/blob/master/zetasql/exampl...

It is, using table-valued functions (TVFs). There's an example at the bottom of this file: https://github.com/google/zetasql/blob/master/zetasql/exampl...

For examples of larger queries, see here for all TPC-H queries in standard syntax and converted to pipe syntax: https://github.com/google/zetasql/blob/master/zetasql/exampl... And several more examples with pipe syntax here: https://github.com/google/zetasql/blob/master/zetasql/exampl...

> this pipeline syntax to be mixed with regular compositional forms like CTEs and subqueries? Yes, it's an extension (available by default), which means you can freely mix with regular SQL and use pipes for just parts of your query. https://github.com/google/zetasql/blob/2024.08.2/docs/pipe-s... > Pipe syntax can be mixed with standard syntax in the same query. For example, subqueries can use different syntax from the parent query.

ZetaSQL’s docs for the ‘OVER’ keyword you mention: https://github.com/google/zetasql/blob/master/docs/window-fu... I disagree that the paper not mentioning ‘OVER’ implies that the paper authors secretly think pipe syntax is a bad idea. They probably just wanted to keep the paper concise, or forgot about that one less-used bit of syntax. Do you think that ‘OVER’ keyword implies something fundamentally wrong about pipe syntax? If so, how?

Google's open source ZetaSQL project added documentation covering pipe syntax last week, which I'm finding easier to follow than this paper: https://github.com/google/zetasql/blob/2024.08.2/docs/pipe-s...

I've just update the EBNF for rarilroad diagram here https://github.com/google/zetasql/issues/134#issuecomment-14... with the full grammar.

Yes, there are CTE and recursive queries, and various DBMS also offer views. There are even table-valued functions. These things are not widespread, and differ by implementation, and the way these are used by clients are copy-and-paste. Something as thoughtful as ZetaSQL https://github.com/google/zetasql does not have mechanisms for structuring (modules, packages, interfaces). SQL will not, cannot evolve into such a direction (or, anything that evolves, will not be recognizable as SQL).

If you don't want to do it yourself, there's this: https://github.com/google/zetasql Parsing is huge but it's amazing how small a part of the job it is. This library isn't even the half of it.

The parser.py [1] has only 1.6k lines. And it is hand-written parser. This size is amazing if it's really capable, but I intuitively doubt it. For example, duckdb's select.y [2] has 3700 lines, and this is only for SELECT. ZetaSQL's grammar file [3] is almost 10k lines. The SQL is a monstrous language. Is there any trick that keeps the code simple? [1] https://github.com/tobymao/sqlglot/blob/main/sqlglot/parser.... [2] https://github.com/duckdb/duckdb/blob/master/third_party/lib... [3] https://github.com/google/zetasql/blob/master/zetasql/parser...

I mean the postgres parser (and semantic changes) for ZetaSQL. The zetasql parser is in a file called zetasql/parser/bison_parser.y, I strongly suspect they now have a file called something like zetasql/pgparser/bison_parser.y as well (and much more pervasive changes to support the deeper differences in the dialects). This is the lexical structure and syntax docs for the new postgres inteface to cloud spanner: https://cloud.google.com/spanner/docs/postgresql/lexical And this is the zetasql lexical structure and syntax docs: https://github.com/google/zetasql/blob/master/docs/lexical.m... Notice that the new PG docs are an edit of the Zeta ones - evidence that my hypothesis is correct.