16 ms·

Parsing SQL

158 points by teleforce 4y ago

TkTech 4y ago
I'm surprised this doesn't mention the class of parsers I believe to be the most useful - the database in question itself. For example I make extensive use of https://pglast.readthedocs.io/en/v3/ https://pglast.readthedocs.io/en/v3/, which is the postgres SQL parser extracted into a standalone library (pg_query). No 3rd party library attempting to parse a particular flavour of SQL will be more accurate than the database that defines that flavour, since you're also getting all of the edge cases and quirks of it's impementation.
- mayoi 4y ago
  Yeah this also seems very weird to me. Like, okay, that's cool, but did you ever hear of this thing called integration testing?
- valenterry 4y ago
  I think the article focuses on SQL parsing that builds an AST that's usable in the programming language of your choice to analyze/transform the AST. so you'd have to write a mapping at least. Otherwise I agree.
  - thejosh 4y ago
    Yep, it depends on what you want to do. Validating the SQL is correct is handy -- of course, but sometimes you need the AST.
    - sanxiyn 4y ago
      pg_query does return the AST.
      - valenterry 4y ago
        How do I pass my programming language as parameter to it?
        emaginniss 4y ago
        Why would you need to pass in your language? The AST is data. You can then take that AST, which is already validated and disambiguated and act upon it in any way that you'd like.
        valenterry 4y ago
        Because I want to analyze and transform the AST with the tools and libraries that my programming language offers. To be more precise, I want the data to be datatypes of my programming language and not JSON or some custom structure that I have to first parse myself.
  - deleted 4y ago
    [deleted]
- jiggawatts 4y ago
  In the same vein, I found this official library for parsing Microsoft T-SQL: https://devblogs.microsoft.com/azure-sql/programmatically-parsing-transact-sql-t-sql-with-the-scriptdom-parser/ https://devblogs.microsoft.com/azure-sql/programmatically-pa... I used it for a nasty global search & replace where a statement with quoted strings was sometimes embedded in quoted strings.
  - hobs 4y ago
    It's nice but it has a buuuuuuuunch of bugs - the published railroad diagrams/bnf grammar dont even match behavior in the wild so I feel bad for them, but you can see intellisense fail for similar reasons.
- pbowyer 4y ago
  Anyone know of the same for MySQL/MariaDB? I've found their grammar in the repository, but having a library would be better. I've found unofficial attempts but they have all mishandled parts of SQL compared to the built-in MySQL parser.
  - password4321 4y ago
    https://github.com/dolthub/go-mysql-server/blob/main/SUPPORTED.md#notable-limitations https://github.com/dolthub/go-mysql-server/blob/main/SUPPORT... is popular but not 100%
    - pbowyer 4y ago
      Been reporting many ~bugs~ spec inconsistencies there :) Apart from parser differences there's no support for SQL_MODE options yet.
pcblues 4y ago
"you cannot do everything related to data in SQL. In some cases, you need a traditional programming language to work with the data" Them's fighting words :) But point taken regarding the benefit of modern language tools applied to query-based work.
- edgyquant 4y ago
  I inherited a graphql project that was a work of art in that respect. All access control etc was done in the database and triggers were effectively used the works. 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.
- NoToP 4y ago
  Especially since its literally untrue. SQL, with the addition of recursive queries (supported by all since the late 90s AND used in an example within this very article), is Turing complete. There's literally no operation on data that can't in principle be expressed as an SQL query (albeit the construction might be painful and tedious).
  - egeozcan 4y ago
    > There's literally no operation on data that can't in principle be expressed as an SQL query 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.
  - branko_d 4y ago
    > There's literally no operation on data that can't in principle be expressed as an SQL query (albeit the construction might be painful and tedious). 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".
bfung 4y ago
Yes, parsing sql is not straightforward as there are many dialects. 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…
- zasdffaa 4y ago
  > 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. That's generating SQL. Parsing is going from text to the AST; the reverse of what you're saying. Maybe I'm confused.
  - bfung 4y ago
    yes, apologies, I skipped ahead. 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.
    - zasdffaa 4y ago
      He actually lists at the end as an alternatives to his consulting: 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.
password4321 4y ago
Open Source SQL Parsers https://news.ycombinator.com/item?id=31107231 https://news.ycombinator.com/item?id=31107231 2022-04-21 125 points, 102 comments
asdfasgasdgasdg 4y ago
If you don't want to do it yourself, there's this: https://github.com/google/zetasql 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.
busymom0 4y ago
On a slightly related note, does anyone have any good recommendations for a parsing library in nodejs or rust for parsing advanced search queries? Similar to twitter’s advanced search?
rav 4y ago
Parsing SQL can solve many problems, but before writing/choosing a parser you need to know what your goal is. For example, at work we wanted to have statically-typed SQL statements in Python/mypy to statically identify syntax errors, misspelled column names and bad type mixes. We came up with a home-grown SQL parser [1] and type checker [2] in Rust and a mypy plugin [3]. If someone knows of other solutions for obtaining SQL/mypy integration I'd love to know about it, since parsing SQL is not exactly supposed to be our field of work. [1] https://github.com/antialize/sql-parse/blob/7869595aa92aed0bb5a45b1a5716eca9af8f11d7/src/select.rs#L479 https://github.com/antialize/sql-parse/blob/7869595aa92aed0b... [2] https://github.com/antialize/sql-type https://github.com/antialize/sql-type [3] https://github.com/antialize/py-mysql-type-plugin https://github.com/antialize/py-mysql-type-plugin
trollied 4y ago
This article is all over the place. 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.
- gmac 4y ago
  Yes. Parsing SQL solves some rather specific problems, which the article doesn’t seem terribly clear about. Interrogating the running database to establish schemas and types is exactly what my Postgres/TypeScript library[1] does, for example. [1] https://jawj.github.io/zapatos https://jawj.github.io/zapatos
- zasdffaa 4y ago
  You've completely missed the point. Author's talking about parsing SQL. That's utterly different from using infoschema to get an existing table structure.
  - trollied 4y ago
    No, I have not. The article has no clear direction, hence your comment. The article talks about parsing SQL DDL with the goal of creating classes/structures that represent the tables. You do not need to parse SQL to achieve this. The premise at the start of the article is "parsing SQL so that we can achieve things in code that is not possible in SQL". If that were indeed the case, there would be no such SQL to parse in the first place!
    - zasdffaa 4y ago
      > creating classes/structures that represent the tables 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.
irrational 4y ago
>SQL is an old language and not designed for large scale programming, it is not a language that developers will love. Even worse, they will not be very productive with it. What the hell. I stopped reading at this point.
- hardware2win 4y ago
  Why? I'd say its true
  - ansgri 4y ago
    Case in point, the generally-loved C# feature, LINQ, is modeled after SQL. Except for a nonexistent (or at least not standardized) type system, SQL's just fine for data modeling and often analysis.
    - balfirevic 4y ago
      As a data point from a LINQ fan - I love it precisely because it improves on SQL, in terms of composability, regularity and allowing some amount of meta-programming.
- tkot 4y ago
  It's true though. Relational algebra is a very nice abstraction but SQL as a language is subpar to say the least. SQL is really unergonomic, it's difficult to reuse SQL code which is why there are no SQL libraries worth talking about and it's the reason why people go out of their way to create tools to replace SQL like ORMs or LINQ. 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.
  - qsort 4y ago
    As much as SQL could be better, it's a very strong local optimum. There is no credible replacement whatsoever for what it does. 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?
michaelmior 4y ago
One parser I think deserves a mention is the one from Apache Calcite[0]. Calcite does more than parsing, there are a number of users who pick up Calcite just for the parser. While the default parser attempts to adhere strictly to the SQL standard, of interest is also the Babel parser, which aims to be as permissive as possible in accepting different dialects of SQL. Disclaimer: I am on the PMC of Apache Calcite, but not particularly active at the moment. [0] https://calcite.apache.org/ https://calcite.apache.org/
- chrisjc 4y ago
  Although I truly appreciate and admire Calcite, I've had so much trouble trying to get it to do what I want. Although in all fairness it could be me and being able to find enough time to deep-think on it. 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.
  - michaelmior 4y ago
    I agree that customization can be a complicated. Adding more tutorials and documentation is something that the community is definitely interested in.
lukaseder 4y ago
It's probably not advertised enough, but jOOQ also contains a parser that can translate between dialects, and let users work with the expression tree to achieve custom SQL translations. A demo here: https://www.jooq.org/translate https://www.jooq.org/translate
anonymousDan 4y ago
On a related note, does any one know of a parser + planner library that allows you to spit out a query plan (either logical or physical) in a serializable form (e.g. JSON)? For example, say I want to parse a query on the client side into a basic plan (that might be further optimised on the server side).
- cfors 4y ago
  It would always be nonsensical unless you were running on the database you were targeting (or one that contained similar data distributions). 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 1
  - anonymousDan 4y ago
    Great, thanks. How modular is the postgres codebase? Would it be relatively easy to factor out the part of the code that does the above from the rest of the database (e.g. from the execution or storage engines)?
eatonphil 4y ago
If you're looking for a good sql library to use I did a review/survey of parsers in a bunch of major languages. Most of them are full postgres or mysql parsers. https://datastation.multiprocess.io/blog/2022-04-11-sql-parsers.html https://datastation.multiprocess.io/blog/2022-04-11-sql-pars...
- lukaseder 4y ago
  Fancy adding the jOOQ parser to your list of Java parsers? https://www.jooq.org/doc/latest/manual/sql-building/sql-parser/ https://www.jooq.org/doc/latest/manual/sql-building/sql-pars...
  - eatonphil 4y ago
    Neat! I'll give it a shot.
ParetoOptimal 4y ago
The hasql-th[0] package implements an sql parser to give better error messages, here's an example: Consider 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: | 2 | select 1 from a where b >= 3 && b < 4; | ^ unexpected '&' 0: https://github.com/nikita-volkov/hasql-th#error-example-1 https://github.com/nikita-volkov/hasql-th#error-example-1
jjice 4y ago
I took a DB implementation class my senior year of college (I still giggle at our team name: "YourSQL"). I wrote our parsers. First was the DDL, and I wrote the lexer and parser by hand (this is all in C). It's not necessarily a hard task, but dealing with where the hell that segfault is coming from takes a lot more time. I convinced the professor (after a short essay) to let us use lex and yacc since even PostgreSQL uses them. Made the job so much easier to write and modify. 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.
wener 4y ago
Recently build a simple SQL Parser for sequelize https://github.com/wenerme/js-miniquery https://github.com/wenerme/js-miniquery
tomerbd 4y ago
In py spark parser = self.spark._jsparkSession.sessionState().sqlParser() parser.parseExpression(some sql)

158 points by teleforce 4y ago

I'm surprised this doesn't mention the class of parsers I believe to be the most useful - the database in question itself. For example I make extensive use of https://pglast.readthedocs.io/en/v3/ https://pglast.readthedocs.io/en/v3/, which is the postgres SQL parser extracted into a standalone library (pg_query). No 3rd party library attempting to parse a particular flavour of SQL will be more accurate than the database that defines that flavour, since you're also getting all of the edge cases and quirks of it's impementation.

mayoi 4y ago
Yeah this also seems very weird to me. Like, okay, that's cool, but did you ever hear of this thing called integration testing?
valenterry 4y ago
I think the article focuses on SQL parsing that builds an AST that's usable in the programming language of your choice to analyze/transform the AST. so you'd have to write a mapping at least. Otherwise I agree.
- thejosh 4y ago
  Yep, it depends on what you want to do. Validating the SQL is correct is handy -- of course, but sometimes you need the AST.
  - sanxiyn 4y ago
    pg_query does return the AST.
    - valenterry 4y ago
      How do I pass my programming language as parameter to it?
      - emaginniss 4y ago
        Why would you need to pass in your language? The AST is data. You can then take that AST, which is already validated and disambiguated and act upon it in any way that you'd like.
        valenterry 4y ago
        Because I want to analyze and transform the AST with the tools and libraries that my programming language offers. To be more precise, I want the data to be datatypes of my programming language and not JSON or some custom structure that I have to first parse myself.
- deleted 4y ago
  [deleted]
jiggawatts 4y ago
In the same vein, I found this official library for parsing Microsoft T-SQL: https://devblogs.microsoft.com/azure-sql/programmatically-parsing-transact-sql-t-sql-with-the-scriptdom-parser/ https://devblogs.microsoft.com/azure-sql/programmatically-pa... I used it for a nasty global search & replace where a statement with quoted strings was sometimes embedded in quoted strings.
- hobs 4y ago
  It's nice but it has a buuuuuuuunch of bugs - the published railroad diagrams/bnf grammar dont even match behavior in the wild so I feel bad for them, but you can see intellisense fail for similar reasons.
pbowyer 4y ago
Anyone know of the same for MySQL/MariaDB? I've found their grammar in the repository, but having a library would be better. I've found unofficial attempts but they have all mishandled parts of SQL compared to the built-in MySQL parser.
- password4321 4y ago
  https://github.com/dolthub/go-mysql-server/blob/main/SUPPORTED.md#notable-limitations https://github.com/dolthub/go-mysql-server/blob/main/SUPPORT... is popular but not 100%
  - pbowyer 4y ago
    Been reporting many ~bugs~ spec inconsistencies there :) Apart from parser differences there's no support for SQL_MODE options yet.

pcblues 4y ago

"you cannot do everything related to data in SQL. In some cases, you need a traditional programming language to work with the data" Them's fighting words :) But point taken regarding the benefit of modern language tools applied to query-based work.

edgyquant 4y ago
I inherited a graphql project that was a work of art in that respect. All access control etc was done in the database and triggers were effectively used the works. 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.
NoToP 4y ago
Especially since its literally untrue. SQL, with the addition of recursive queries (supported by all since the late 90s AND used in an example within this very article), is Turing complete. There's literally no operation on data that can't in principle be expressed as an SQL query (albeit the construction might be painful and tedious).
- egeozcan 4y ago
  > There's literally no operation on data that can't in principle be expressed as an SQL query 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.
- branko_d 4y ago
  > There's literally no operation on data that can't in principle be expressed as an SQL query (albeit the construction might be painful and tedious). 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".

bfung 4y ago

Yes, parsing sql is not straightforward as there are many dialects. 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…

zasdffaa 4y ago
> 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. That's generating SQL. Parsing is going from text to the AST; the reverse of what you're saying. Maybe I'm confused.
- bfung 4y ago
  yes, apologies, I skipped ahead. 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.
  - zasdffaa 4y ago
    He actually lists at the end as an alternatives to his consulting: 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.

password4321 4y ago

Open Source SQL Parsers https://news.ycombinator.com/item?id=31107231 https://news.ycombinator.com/item?id=31107231 2022-04-21 125 points, 102 comments

asdfasgasdgasdg 4y ago

If you don't want to do it yourself, there's this: https://github.com/google/zetasql 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.

busymom0 4y ago

On a slightly related note, does anyone have any good recommendations for a parsing library in nodejs or rust for parsing advanced search queries? Similar to twitter’s advanced search?

rav 4y ago

Parsing SQL can solve many problems, but before writing/choosing a parser you need to know what your goal is. For example, at work we wanted to have statically-typed SQL statements in Python/mypy to statically identify syntax errors, misspelled column names and bad type mixes. We came up with a home-grown SQL parser [1] and type checker [2] in Rust and a mypy plugin [3]. If someone knows of other solutions for obtaining SQL/mypy integration I'd love to know about it, since parsing SQL is not exactly supposed to be our field of work. [1] https://github.com/antialize/sql-parse/blob/7869595aa92aed0bb5a45b1a5716eca9af8f11d7/src/select.rs#L479 https://github.com/antialize/sql-parse/blob/7869595aa92aed0b... [2] https://github.com/antialize/sql-type https://github.com/antialize/sql-type [3] https://github.com/antialize/py-mysql-type-plugin https://github.com/antialize/py-mysql-type-plugin

trollied 4y ago

This article is all over the place. 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.

gmac 4y ago
Yes. Parsing SQL solves some rather specific problems, which the article doesn’t seem terribly clear about. Interrogating the running database to establish schemas and types is exactly what my Postgres/TypeScript library[1] does, for example. [1] https://jawj.github.io/zapatos https://jawj.github.io/zapatos
zasdffaa 4y ago
You've completely missed the point. Author's talking about parsing SQL. That's utterly different from using infoschema to get an existing table structure.
- trollied 4y ago
  No, I have not. The article has no clear direction, hence your comment. The article talks about parsing SQL DDL with the goal of creating classes/structures that represent the tables. You do not need to parse SQL to achieve this. The premise at the start of the article is "parsing SQL so that we can achieve things in code that is not possible in SQL". If that were indeed the case, there would be no such SQL to parse in the first place!
  - zasdffaa 4y ago
    > creating classes/structures that represent the tables 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.

irrational 4y ago

>SQL is an old language and not designed for large scale programming, it is not a language that developers will love. Even worse, they will not be very productive with it. What the hell. I stopped reading at this point.

hardware2win 4y ago
Why? I'd say its true
- ansgri 4y ago
  Case in point, the generally-loved C# feature, LINQ, is modeled after SQL. Except for a nonexistent (or at least not standardized) type system, SQL's just fine for data modeling and often analysis.
  - balfirevic 4y ago
    As a data point from a LINQ fan - I love it precisely because it improves on SQL, in terms of composability, regularity and allowing some amount of meta-programming.
tkot 4y ago
It's true though. Relational algebra is a very nice abstraction but SQL as a language is subpar to say the least. SQL is really unergonomic, it's difficult to reuse SQL code which is why there are no SQL libraries worth talking about and it's the reason why people go out of their way to create tools to replace SQL like ORMs or LINQ. 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.
- qsort 4y ago
  As much as SQL could be better, it's a very strong local optimum. There is no credible replacement whatsoever for what it does. 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?

michaelmior 4y ago

One parser I think deserves a mention is the one from Apache Calcite[0]. Calcite does more than parsing, there are a number of users who pick up Calcite just for the parser. While the default parser attempts to adhere strictly to the SQL standard, of interest is also the Babel parser, which aims to be as permissive as possible in accepting different dialects of SQL. Disclaimer: I am on the PMC of Apache Calcite, but not particularly active at the moment. [0] https://calcite.apache.org/ https://calcite.apache.org/

chrisjc 4y ago
Although I truly appreciate and admire Calcite, I've had so much trouble trying to get it to do what I want. Although in all fairness it could be me and being able to find enough time to deep-think on it. 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.
- michaelmior 4y ago
  I agree that customization can be a complicated. Adding more tutorials and documentation is something that the community is definitely interested in.

lukaseder 4y ago

It's probably not advertised enough, but jOOQ also contains a parser that can translate between dialects, and let users work with the expression tree to achieve custom SQL translations. A demo here: https://www.jooq.org/translate https://www.jooq.org/translate

anonymousDan 4y ago

On a related note, does any one know of a parser + planner library that allows you to spit out a query plan (either logical or physical) in a serializable form (e.g. JSON)? For example, say I want to parse a query on the client side into a basic plan (that might be further optimised on the server side).

cfors 4y ago
It would always be nonsensical unless you were running on the database you were targeting (or one that contained similar data distributions). 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 1
- anonymousDan 4y ago
  Great, thanks. How modular is the postgres codebase? Would it be relatively easy to factor out the part of the code that does the above from the rest of the database (e.g. from the execution or storage engines)?

eatonphil 4y ago

If you're looking for a good sql library to use I did a review/survey of parsers in a bunch of major languages. Most of them are full postgres or mysql parsers. https://datastation.multiprocess.io/blog/2022-04-11-sql-parsers.html https://datastation.multiprocess.io/blog/2022-04-11-sql-pars...

lukaseder 4y ago
Fancy adding the jOOQ parser to your list of Java parsers? https://www.jooq.org/doc/latest/manual/sql-building/sql-parser/ https://www.jooq.org/doc/latest/manual/sql-building/sql-pars...
- eatonphil 4y ago
  Neat! I'll give it a shot.

ParetoOptimal 4y ago

The hasql-th[0] package implements an sql parser to give better error messages, here's an example: Consider 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: | 2 | select 1 from a where b >= 3 && b < 4; | ^ unexpected '&' 0: https://github.com/nikita-volkov/hasql-th#error-example-1 https://github.com/nikita-volkov/hasql-th#error-example-1

jjice 4y ago

I took a DB implementation class my senior year of college (I still giggle at our team name: "YourSQL"). I wrote our parsers. First was the DDL, and I wrote the lexer and parser by hand (this is all in C). It's not necessarily a hard task, but dealing with where the hell that segfault is coming from takes a lot more time. I convinced the professor (after a short essay) to let us use lex and yacc since even PostgreSQL uses them. Made the job so much easier to write and modify. 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.

wener 4y ago

Recently build a simple SQL Parser for sequelize https://github.com/wenerme/js-miniquery https://github.com/wenerme/js-miniquery

tomerbd 4y ago

In py spark parser = self.spark._jsparkSession.sessionState().sqlParser() parser.parseExpression(some sql)