Parsing SQL
tomassetti.me
tomassetti.me
I used it for a nasty global search & replace where a statement with quoted strings was sometimes embedded in quoted strings.
I've found unofficial attempts but they have all mishandled parts of SQL compared to the built-in MySQL parser.
Apart from parser differences there's no support for SQL_MODE options yet.
What the hell. I stopped reading at this point.
SQL is basically a DSL for data processing just like XSLT for example. Would you call XSLT "designed for large scale programming"?
The "they will not be very productive with it" comment is a bit of a stretch - it can be very productive in the domain it is designed for - but you can often replace SQL at its job (like for example use Dataframe API in Spark instead of SQL API) while there are many tasks that SQL is a very poor fit for.
It's true people try to replace it, but most solutions are of rather questionable quality. I personally argue against ORMs in pretty much any situation.
> SQL is basically a DSL for data processing
Yeah and what's wrong with that? SQL databases power everything, including data sources with billions of records that serve millions of customers, with hundreds of developers/DBAs working on them. Does that not count as programming in the large?
I'm compelled to agree with this under protest. There was a language called Dataphor based on D and the Third Manifesto, it's unfortunately hard to find even sample code any longer, but it would be a strict improvement on SQL.
Did I mention you can't even find a corpus? Good luck running any implementation on a modern system.
I'm reasonably content writing SQL, but I know a syntax with the same power but lacking several disadvantages is possible, and I'd rather use a mature implementation of that instead, if I were able.
The reason why I'm skeptical of most proposed alternatives is that I'm fundamentally in your exact same position: I know SQL isn't perfect but I'm reasonably content with it; and they invariably all end up throwing away the "good parts" of SQL (relational model, declarative, easy stuff is easy).
The first step of an hypothetical solution that replaces SQL isn't "SQL sucks", it's "SQL is extremely good at what it does but has problems that are only fixable with a new language".
There's other "D" type projects. Rel is one. Don't have URL handy but it's fairly easy to find. They publish their grammar and example code.
None of these tools have ever matured or become popular. I have theories why, we could discuss forever.
I think the issue has more to do with two things: a) most people don't understand the relational model fully, so they have no idea what they're missing b) new databases (and existing) simply cannot afford to rock the boat here because they need customers. And in terms of the architecture of the DB system, the SQL parser is one of the lower effort items (when compared to query planner, optimization, storage impl and storage optimization, replication, etc.) So why invest there for little value? Customers aren't asking for it.
The company I'm contracting for right now has some interest in working in this space.
Date & Darwin did good work with "The Third Manifesto" but it went almost completely ignored, and the tone and target of it may have been off. Frustratingly we went through a phase where SQL went out of style and then back into style ("NewSQL") and so there may have been a window missed there where alternative query languages (but still based on the relational model) could have risen. But instead "NoSQL" was too interested in jettisoning the relational model along with SQL (mostly I would argue because they don't understand it).
There has been some recent rise in Datalog implementations in the Clojure community. That is interesting, though not strictly as an alternative to SQL.
I mostly agree, though I think that SQL's popularity comes mostly from network effects.
> Yeah and what's wrong with that?
Nothing is wrong wrong with that. I just think that it's reasonable to interpret the words "programming in the large" as "being usable as general purpose programming language" (which I admit is not a very strict definition) whereas SQL (or XSLT or awk or bash...) seem well suited for certain niches (important niches nonetheless).
On the DML side I've found SQL to be very reusable at any company I've worked for - grep/search repos for the tables of interest, throw the queries in a CTE and you're off to the races. If the data infrastructure is robust you can probably just query from ([un]materialized) views - quite literally SQL code reuse. And even across vastly different domains, even if not directly resuable, SQL queries are still highly transferable. I can see it being more true on the DDL side but even there, at least anecdotally most of the DBAs/devops/infrastructure engineers etc I've met seemed to have favorable impressions of SQL when needed in between the tooling.
>while there are many tasks that SQL is a very poor fit for.
You probably don't want to try and perform graphical rendering tasks, and it's true certain use cases like dynamically generating SQL statements from table/column names _and then executing_ them requires some manual input. But which data/processing related tasks is SQL a very poor fit for?
In my experience it's much more common to actually see the opposite - inefficient usage of ORMs, exporting datasets only to then perform pre/postprocessing, often requiring building programming "jigs" to circumvent bottlenecks, etc when it could've been done in a more streamlined manner in the DB/warehouse.
> And even across vastly different domains, even if not directly reusable, SQL queries are still highly transferable.
That's the point, they are probably transferable in the "copy, paste and tweak" sense which would be highly frowned upon in other popular languages.
> But which data/processing related tasks is SQL a very poor fit for?
I specifically called SQL "a DSL for data processing", I probably should have written "there are many domains that SQL is a very poor fit for" to be more clear.
>In my experience it's much more common to actually see the opposite - inefficient usage of ORMs, exporting datasets only to then perform pre/postprocessing, often requiring building programming "jigs" to circumvent bottlenecks, etc when it could've been done in a more streamlined manner in the DB/warehouse.
Totally agree. Lukas Eder has some nice presentations about it.
>Totally agree. Lukas Eder has some nice presentations about it.
Thanks - I googled him and instantly recognized the JOOQ blog, quality stuff.
Thanks for the shout out! For the record, that's probably the referenced talk: https://www.youtube.com/watch?v=wTPGW1PNy_Y
I have seen people go crazy in stored procedures or via ORMs with super complex queries.
Avoid complex queries (with more foresight about how to structure, cache and query data) and you might not need the SQL code reuse.
There might be some less than ideal bits you would live to DRY up and would if it were OO or Functional Programming but perhaps let it be in SQL.
SQL is performance programming anyway so you are allowed!
I assume you mean libraries of SQL code (e.g. query fragments/templates) as opposed to libraries for working with SQL?
I'm criticizing SQL as a language which is lacking in composability (as opposed to criticizing relational algebra or relational data model).
Libraries for working with SQL (full-fledged ORMs or simpler query builders) are themselves written in a different language so they don't necessarily prove anything about SQL itself, though one could argue that if people want to use them then they might not be satisfied with raw SQL.
Or alternatively, if it's for things that really are mutating state and involving biz logic... a stored procedure.
Date & Darwin proposed the addition of "operators" to the relational algebra in their "Tutorial D" description of alternatives to SQL. That is, instead of "stored procedure", the addition of a type system matching on relations ("tables") and their tuples ("rows") and then the ability to create user-defined programmatic "operators" (a bit like OO methods) for those types.
I feel that it isn't SQL that is actually difficult to re-use. It's what SQL was designed to describe that is difficult to re-use: business entities.
Unlike "true" programming languages (not to get into a debate about SQL's completeness, we all know it isn't practically a general-purpose language) this is usually best approached differently than debugging a program. Whenever I encountered a mess of a query, I'd ask myself two questions:
1. What is the purpose of the query/what is the desired output?
2. What are the source tables used in the query?
At this point I'd typically rewrite the query, taking inspiration from the non-broken parts of the original if possible, and otherwise rewriting from scratch. In most cases this was faster (and the rewritten result often more efficient) than trying to debug a truly busted statement.
It is tho, SQL is a declarative language, so the execution model is largely opaque, by language design.
But even if it were solely an implementation detail, Closi would still be wrong: they literally stated that “it’s not any better” to try debugging a 100 lines python script than a 100 lines query.
> As it is, you often have to take the whole damn thing apart from the inside to see what's going on, then put it all back together again. That's... suboptimal, to say the least.
Which is the point? Sounds like you agree that it is significantly easier to debug a 100 lines python script than a 100 lines SQL query.
I don't mind writing a query, or even a complex query at all. Sometimes it can be draining if you need to nest more queries than you'd like rather than have some joins, but I personally think SQL is a fine language.
Could just be Stockholm Syndrome.
That's not implying that large, successful projects cannot be built upon SQL. We see them in the real world all the time.
We have awesome implementations of the language, but any productivity is in spite of a quirky language designed in the early 1970s, not because of it.
This says nothing about the quality of SQL as a language.
What I think is more interesting is: if you were to greenfield a language for a relational database, how much would it look like SQL?
> if you were to greenfield a language for a relational database, how much would it look like SQL?
Save for secondary concerns like syntax, it would be exactly the same in all respects except easier composability.
There is no low-level API to SQL servers to create competition, so this is like saying CSS has no competition in browsers whatsoever. Yes, it does not, because it was never allowed. Let's go make a browser competition as part of our weekend, or at least RDBMS, sure /s. ORMs with their "relations" etc are basically better DSLs over SQL databases which try to hide rough edges from an innocent user.
concisely, effectively, efficiently expressing ideas, it's unquestionably successful
Yeah, "group by case when cond1 then v1 when cond2 then v3 else v3 end" instead of "group by alias1". Because, you know, it's declarative, but not really: https://stackoverflow.com/a/3841804/3125367
Or fetching thousands of rows out of explosion of "select t.a, t.b, p.propname, p.propvalue from items t left join props p on t.id = p.item_id". Or inability to join two different subtables like 'props' and 'per_city_prices' simultaneously without exploding into infinity. Of course you can always make 3 queries and join at your place, which is neither effective nor efficient nor concise. A business wants simple [{a, b, props:[{name, value}, ...], per_city_prices:[{city, price}, ...], ...]. Can SQL do that? It can not.
Or getting corresponding non-aggregate values along with min/max results, which requires very funny self-joins if it's already e.g. a windowed aggregation.
SQL is cool for these relational... matrices(?), but sucks as a programming language in both syntactic and semantic parts. Some servers fixed few dumb parts of it, but not generally.
Yes... but there is only one Web, and many databases. Some of them don't use SQL. They are, in my opinion, and in the opinion of many other developers, inferior. They lost in the market in a fair fight.
Also if SQL sucks so much you can compile to SQL, like TS->JS. Many such solutions exist. All worse than SQL.
> ORMs with their "relations" etc are basically better DSLs over SQL databases which try to hide rough edges from an innocent user.
ORMs are developed by software engineers for software engineers. It's kind of silly to picture them as "innocent" or incapable to understand the relational model.
> https://stackoverflow.com/a/3841804/3125367
Does having an operational semantics make a language not declarative? That's not how I use words. Is Haskell not functional because it runs on a GRS?
For the rest of your post you're arguing about either syntax, stuff you solve with CTEs, or stuff you would just hand off to the programming language, so I'll just leave it at that.
Perhaps SQL is so good that it couldn't be replaced all these years. But yes, debugging could have been better.
It is as though we lived in a world where, like our own, Lisp invented garbage collection. But unlike our own world, in 2022, Lisp is the only garbage collected language anyone uses. Others were invented but that mostly stopped by the 80s.
Many people would like to replace Lisp, and keep in mind this is Lisp so lexical scope is only available in some implementations and people who want cross-platform compatibility don't use it. But without it, you have to manage your own memory.
That's SQL and relational databases. Relational databases with ACID guarantees aren't optional, they aren't a nice-to-have, they're foundational.
And we talk to them in SQL because... we talk to them in SQL.
This grunt work of sql =>spark translation is easily the most boring part of my job.
Other than that, I do kind of agree that sql isn't really good at large scale programming. There is no ide support while you are working, hard to extract common logic , hard to reuse , hard to compose, hard to unit test. All these things have been mainstays of conventional programming.
I often see people saying parser generators are a waste of time for various reasons. There are a good few cases I'd agree in:
- Performance is critical
- You want lots of power for error reporting
- You really want to learn about recursive descent parsers by writing on by hand
Personally, the least interesting part of a compiler after the first time is lexing and parsing for me. Code gen, type checking, optimization, etc are all the real meat and potatoes for me, and I find parser generators to be a great time saver.
In general, I see generated code becoming less common (could just be me), which I don't think is a bad thing. I see a lot less CFG parsing tools these days too, aside from parser combinators. Could just be me not paying attention, but it's in interesting way we've progressed.
If I was working on a new language - hobby or professional - I'd start with parsing tools and swap them out when it became worthwhile.
[1] https://github.com/antialize/sql-parse/blob/7869595aa92aed0b... [2] https://github.com/antialize/sql-type [3] https://github.com/antialize/py-mysql-type-plugin
But the rest of the article is confusing. There’s not very many instances where someone needs to parse sql - there are ORM libraries that generate the relevant sql dialect. This has been the case since the early 2000s; see Hibernate. And for C#, there’s LINQ to SQL.
The last sentence then goes into the “sell”: if you need help parsing sql, contact us at xyz…
That's generating SQL. Parsing is going from text to the AST; the reverse of what you're saying. Maybe I'm confused.
Parsing: "There’s not very many instances where someone needs to parse sql" The only use cases would be building your own database/query optimizer and having SQL be the API.
There are plenty of other tools already to help programming languages interface w/SQL, give compile time checks, test, generate SQL, etc.
Build the parser for fun, sure, but don't then offer consulting services on an article that didn't really specify a business problem or outcome and just talked about tech stuff.
Consider if you can use existing tools or libraries to process SQL code. Some of them even support multiple SQL-dialects or multiple programming languages. If you can use any of them for your needs they should be your first option
If you want to build a solution in-house you may consider starting from the few ANTLR grammars available for the major SQL databases. They can really help you get started in parsing SQL
> There are plenty of other tools already to help ... give compile time checks
Which are these?
> ..that didn't really specify a business problem or outcome and just talked about tech stuff.
The business problem is to build an sql parser. the outcome being an SQL parser. It's clear enough and it's something I had to do. Your criticisms are unreasonable.
To do that you have to parse the schema to get the table types and work backward. That lets the analyzer decide what sorts of data may be legally passed to a given prepared statement, and produce type failures at compile time.
There are more, that's the first which came to mind. Disclosure: I'm writing an SQLite PEG for a number of reasons which might hypothetically include that one.
Disclaimer: I am on the PMC of Apache Calcite, but not particularly active at the moment.
Specifically, customizing the DDL grammar was really hard to understand and play with. It's been a while and maybe it's better, but I remember there was some kind of freemarker compiler templating language that had to be modified and documentation and posts on different forums was lacking to say the least.
I was trying to do some very unorthodox things at the time, but Calcite is probably a good fit for 98% of the things one might need to do with an SQL compiler/parser.
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.
Them's fighting words :)
But point taken regarding the benefit of modern language tools applied to query-based work.
The resulting application was a complete performance failure. Databases are slow and generally involve latency. They are a source of truth and an engine for querying.
They are not meant to be your applications working data model.
But in all seriousness, DataFrame centric operations are a superset of SQL (you can always do a df.sql("...") if you want to ) and have a lot more efficient implementation of both OLTP/ORM requirements and OLAP/DS/BI requirements.
They also encourage composability, modularization, reuse, unit and data testing ...
So it's ironic I feel like SQL's replacement is another declarative language - its original inspiration - plain English.
Just a natural language transpiler (like Palantir's Ontology plus Looker's Malloy (reverse disclaimer: I do not work for or enjoy either of these products but these underlying concepts are correct)) with some fancy domain heuristics and light AI (I suspect a Pareto like model that supports 80% of use cases only needs a semantic graph with a few thousand nodes and vertices)
There are some problems though:
- Single query can't write to multiple tables.
- Single query can't return multiple resultsets.
You can write a stored procedure, but this is no longer "an SQL query", strictly speaking. And once you start writing stored procedures, you are no longer using just SQL, but whatever Ada-inspired procedural extension to SQL was implemented by the database vendor. In other words, you are using "a traditional programming language to work with the data".
You could compose a SQL query that allows you to map multiple resultsets to 1 resultset, although that feels a bit awkward.
WITH a AS (
insert into a (k, v) values ('a', 1.0) returning *
), b AS (
insert into b (k, v) values ('b', 2.0) returning *
)
SELECT
row_to_json(a)
FROM
a
UNION ALL
SELECT
row_to_json(b)
FROM
b;
row_to_json
--------------------------
{"a_id":1,"k":"a","v":1}
{"b_id":1,"k":"b","v":2}
(2 rows)ah, the insert is a CTE because it produces a value ('returning' I guess). Hmm. This is very odd. Doesn't seem to work in mssql.
Well thanks for the can of worms...
with x as (...)
update x set ...
ok but you can linearise them
with x as (...), y as (...)
utter, reeking garbage.
> you can't use distinct or group
more garbage. I have. Show me an example of it not working.
(Edited for less rudeness)
WITH t AS (
DELETE FROM foo
)
DELETE FROM bar;
Yes on the final query of the CTE you can do all sorts of things, but that's way less useful if you can't do them in all the component queries.
> Or can you only use SELECT for WITH queries
you can only use a select inside a cte (or should be able to) because the 'e' stands for 'expression'. It seems postgres does allow an insert with and output which sort of makes sense but I doubt it's in the standard.
Your last sentence makes no sense to me. Give an example.
Also you failed to give an example that group by/distiinct weren't allowed in ctes.
I'm currently on SQL Server and it doesn't support INSERT as a CTE (and I think most DBMSes out there still don't). It would definitely make my life easier if it did...
https://docs.snowflake.com/en/sql-reference/sql/insert-multi...
That may be technically true (or not, I don't know), but in many cases some complex data manipulation (especially when it's done in multiple passes) practically needs a lot of ram and a programming language to be more time efficient.
Sql can also have the same issue with feature creep (snowflakes array columns often invites people to write pathetic sql trying to join on this col for example) but with the right design you can force your users to interact with your data more efficiently.
Anyway, the author would have been better off writing a query to generate the class definitions directly from the data dictionary (if available). INFORMATION_SCHEMA (and other proprietary implementations, like the DBA_ views in Oracle or sys views in SQL Server/Sybase) is just perfect for this. An order of magnitude easier than writing a parser.
An example: in case of Debezium's MySQL connector (a change data capture platform), we need to know the table schema in order to interpret incoming change events from the MySQL binlog. Due to the async nature of this process, such event may adhere to an earlier schema version, which is different from how the table looks like in the data dictionary now. For instance, a column's type may have changed between the point in time when that change event was created and now. That's why indeed we also implemented a full MySQL DDL parser for Debezium, as DDL events show up in-stream and thus allow us to react to schema changes at the right moment and update our model of the table structures accordingly.
Now, ideally, data dictionaries were revisioned, so that indeed we could query history dictionary entries (with actual change events containing a schema revision id), but I'm not aware of any database which supports that. Touched on this briefly in a talk I did earlier this year for Andy Pavlo's database group at CMU [1].
[1] https://speakerdeck.com/gunnarmorling/open-source-change-dat...
Interrogating the running database to establish schemas and types is exactly what my Postgres/TypeScript library[1] does, for example.
From there an implementation can be defined to create, alter, drop, etc the respective objects and then probably update some kind of manifest that represents the objects and potentially its relationship to others. In most databases that manifest is the INFORMATION_SCHEMA.
AFAICS he's talking about creating AST objects.
If the tables don't exist, you can't query the infoschema because the tables don't exist. Someone has to parse the table-definition source code to work out what the tables must eventually look like when turned into structures in the database.
However, with Postgres its pretty trivial to get a JSON format of the output if you needed a machine parseable answer. I don't know of a library that does this for you, but it wouldn't surprise me if one existed.
explain (format json, analyze, buffers) select 1Consider the following broken statement:
select 1 from a where b >= 3 && b < 4
It is incorrect, because it uses && instead of and. But here's what Postgres' original parser says about it:
ERROR: syntax error at or near "<" LINE 1: select 1 from a where b >= 3 && b < 4; ^
Here's what "hasql-th" says:
|0: https://github.com/nikita-volkov/hasql-th#error-example-1
https://news.ycombinator.com/item?id=31107231
2022-04-21 125 points, 102 comments
https://datastation.multiprocess.io/blog/2022-04-11-sql-pars...
parser = self.spark._jsparkSession.sessionState().sqlParser()
parser.parseExpression(some sql)