// HACKER NEWS — CYBERSECURITY
DuckDB V2 PEG-based SQL parser
TL;DR: DuckDB v2.0 replaces its PostgreSQL-derived SQL parser with a PEG-based parser that is easier to evolve and can be extended at runtime.
At DuckDB, one of our goals is to make working with a database system as easy as possible. Users interact with the system through the widely understood Structured Query Language (SQL). Previous blog posts have covered DuckDB’s friendly SQL, including GROUP BY ALL and column selection using SELECT * EXCLUDE (...). Before DuckDB can execute a query using these features, however, it first has to determine whether its syntax is valid. That is the job of the parser, and in DuckDB v2.0 we are completely replacing it without you noticing.
At a high level, DuckDB processes a SQL query through the following stages:
In this blog, we focus on the tokenizer, parser, and transformer:
The parser determines whether a query is syntactically valid, while the binder determines whether the tables, columns, and functions it refers to actually exist.
Every individual token in this query is valid, but the clauses occur in an order that DuckDB’s grammar does not accept. Friendly SQL allows both SELECT-first and FROM-first syntax, but it does not allow the clauses to appear in an arbitrary order.
By comparison, the following query is syntactically valid, so it passes the parser and transformer. However, it fails later in the binder because the table missing_table does not exist.
Although a SQL standard exists, every database system supports different parts of the standard and adds its own syntax and behavior. The resulting variants are commonly referred to as SQL dialects. Examples include the dialects supported by PostgreSQL, Oracle, GoogleSQL for BigQuery, MySQL, MariaDB, SQLite, Spark SQL, and, of course, DuckDB.
DuckDB’s SQL closely follows PostgreSQL conventions, but it has evolved considerably over the years. We have added features of our own, such as GROUP BY ALL, as well as features inspired by other database systems. At the same time, DuckDB does not implement every aspect of PostgreSQL’s behavior. DuckDB therefore speaks its own SQL dialect, which we will refer to as DuckSQL in this post, even though it remains strongly influenced by PostgreSQL.
This distinction is important when talking about the parser. The SQL dialect that DuckDB accepts and the implementation used to parse that SQL are two separate things. For DuckDB v2.0, we are replacing the parser implementation and rewriting its grammar. What we are not replacing is DuckSQL itself.