DuckDB SQL

github.com/ornew/pego/parsers/duckdb parses the SQL dialect of DuckDB, as DuckDB 1.5.6 parses it: the statements of its grammar (a fork of PostgreSQL's) with the syntax that DuckDB adds (FROM-first queries, PIVOT, QUALIFY, ASOF joins, lambdas, list comprehensions, struct and map literals, COLUMNS(...), GROUP BY ALL, SELECT * EXCLUDE ..., COPY, ATTACH, secrets and the rest), and the scanner that goes with it. The parser is generated by PEGO from duckdb.pego. It depends only on the standard library and the PEGO runtime, and not on DuckDB.

# In your module, with a checkout of the PEGO repository at ~/src/pego:
go work init .
go work use ~/src/pego ~/src/pego/parsers/duckdb

Until PEGO publishes a release, a parser module depends on the github.com/ornew/pego module through a local replace directive, so go get cannot fetch it from outside a checkout of the repository. The install notes show the replace form too.

#Use

import "github.com/ornew/pego/parsers/duckdb/types"

// The script as typed values, each node with its Span in the input.
script, err := duckdb.ParseAST(`SELECT region, sum(amount) AS total FROM sales WHERE year = 2024 GROUP BY region`)
sel := script.Statements[0].(*types.Select)
core := sel.Body.(*types.SelectCore)
for _, item := range core.Items {
	fmt.Println(item.Span.Start, item.Span.End) // 7 13, then 15 35
}

// The script as DuckDB cuts it: the statements, each with the text DuckDB keeps for it, and its type.
statements, err := duckdb.Split("CREATE TABLE t (a INT); INSERT INTO t VALUES (1), (2); FROM t")
for _, s := range statements {
	fmt.Println(duckdb.Kind(s.Statement), s.Text) // CREATE, INSERT, SELECT
}

// Visit the nodes in the order of the input.
duckdb.Inspect(script, func(n any) bool {
	if t, ok := n.(*types.BaseTable); ok {
		fmt.Println(strings.Join(t.Name.Names(), "."))
	}
	return true
})

// A syntax error.
_, err = duckdb.ParseAST("SELECT 1 UNION")    // a *duckdb.SyntaxError with Line, Col, Pos

Positions are in code points by default; duckdb.ParseAST(src, duckdb.WithUnit(duckdb.Bytes)) counts bytes (Split uses bytes).

#The Syntax Tree

The types follow the grammar of DuckDB, without the details of how the Bison grammar is organized. They are declared in the package types (types.Select, ...); the list names them without the qualifier:

• Statements: Select (the query: With, Body, OrderBy, Limit, Offset, Locking; the body is a SelectCore, ValuesClause, TableQuery, PivotQuery, UnpivotQuery, SetOp or ParenQuery), InsertStmt, UpdateStmt, DeleteStmt, MergeStmt, CreateTableStmt, CreateTableAsStmt, CreateViewStmt, CreateIndexStmt, CreateSchemaStmt, CreateSequenceStmt, CreateTypeStmt, CreateMacroStmt, CreateSecretStmt, AlterTableStmt and the other ALTERs, DropStmt, CopyStmt, CopyDatabaseStmt, ExportStmt, ImportStmt, AttachStmt, DetachStmt, UseStmt, LoadStmt, PragmaStmt, SetStmt, ResetStmt, ShowStmt, ExplainStmt, PrepareStmt, ExecuteStmt, DeallocateStmt, TransactionStmt, VacuumStmt, AnalyzeStmt, CheckpointStmt, CallStmt, CommentStmt and UpdateExtensionsStmt.
• Expressions (the Expr union): literals, ColumnRef, Star with its EXCLUDE, REPLACE and RENAME, Binary, Unary, Not, IsExpr, Between, In, Like, AnyAll, Cast, Case, FuncCall (with FILTER, ORDER BY, WITHIN GROUP and OVER), SpecialCall (EXTRACT, SUBSTRING, TRIM, POSITION, ...), Lambda, ListLit, StructLit, MapLit, ListComp, Indirection (field access, subscripts and slices), Subquery, Exists, IntervalLit, TypedLit and the rest. Operators are Binary nodes whose Op is the text of the operator as the scanner cut it; the precedence is in the shape of the tree.
• Tables (TableRef): BaseTable, SubqueryRef, FuncTable, ValuesRef, Join, ParenTable, PivotRef, UnpivotRef, with their aliases, sampling and AT clauses.
• Types (Type): the name as written, the modifiers, the fields of STRUCT and UNION, the key and value of MAP and the array dimensions.

A rule that matches a keyword or a punctuation sign without building a node gives a Match, with the matched text; fields of a node that hold such text (Op, Kind, Quantifier) are strings.

#Names and Literals

DuckDB keeps the case of an identifier and compares names without regard to the case of ASCII letters: compare Fold() of two identifiers, or use EqualFold. A name is an Ident whose Text is as written: a word, a quoted identifier, a U&"..." identifier, or a string where DuckDB accepts one as a name ('t' after AS, in COPY, ...). Which words are names where is decided by the category of the keyword, as DuckDB does: year and type name columns and functions, left and join name functions but not columns, select and from name nothing without quotes except after AS and after a dot.

The scanner is part of the grammar. It reads nested /* */ comments, -- comments, $$...$$ and $tag$...$tag$ strings, E'...', U&'...', B'...' and X'...' literals, N'...', numbers with _ between digits (1_000), the operators as PostgreSQL's scanner cuts them (the trailing + and - of an operator without a special character are not part of it), the parameters ?, ?1, $1 and $name, and the Unicode white space characters that DuckDB replaces by spaces.

#Conformance

The tests compare the parser with DuckDB 1.5.6 on 158,605 texts, whose results are in testdata/reference. The texts of the first and the fifth corpus are DuckDB's: the tests read them from test/sql of the corpus duckdb, which go run ./hack/corpora fetch duckdb puts in the cache from the root of the repository (those two are skipped without it):

go test checks, for each text, what DuckDB's extract_statements says, and for each accepted text how it splits the script; for the SELECT statements of tests and exprs it also compares the AST (below). The numbers (go test -v -run 'TestReference'):

The parser and DuckDB agree on every text where DuckDB's parser itself finds a syntax error or none, but the seven known deviations (below). The third row counts the texts that DuckDB's parser rejects in the step after the Bison grammar, when it transforms the syntax tree into its own: the grammar cannot know. They are 1,575 texts, of which about 1,150 name a window that does not exist, 133 are an empty select list in a context the grammar accepts (cte2 AS (SELECT )), and the others are checks such as VALUES lists of different lengths (19), U&'...' and \u escapes, which DuckDB reads but does not implement (28), a type modifier that is not a constant (13), LIMIT or ORDER BY in a recursive query (16), PIVOT ON NULL (11), if() with another number of arguments than three (11), EXCLUDE or FILTER with a window function that does not allow them (19), a foreign key with CASCADE (6), generated columns added to a table (6), SELECT INTO (4), FOR UPDATE (4), duplicate names in a macro, a CTE or an EXCLUDE list, and about fifty more that occur once or twice.

For the statements of the scripts that DuckDB accepts, the number of statements, their types and their texts agree (Split and Kind): 44,949 scripts of tests (44,678 statements with the same text, and 523 scripts not compared because DuckDB expands a statement into several: PIVOT makes a CREATE TYPE first, COPY FROM DATABASE is four statements, and ALTER TABLE ... ADD COLUMN ... DEFAULT is five), 852 of lexical and 3,842 of mutants. The text DuckDB keeps for a statement is what lies between the semicolons that surround it, comments and white space included, and with the Unicode white space of the text replaced by spaces.

DuckDB allows BETWEEN as a column name as well as a frame keyword. In all three frame units, forms such as ROWS BETWEEN . PRECEDING AND 1 PRECEDING, RANGE BETWEEN IN PRECEDING AND -1 PRECEDING and GROUPS BETWEEN AND PRECEDING AND 1 PRECEDING are single-bound frames: their offset expressions contain the column name BETWEEN, and the end defaults to CURRENT ROW. They are not special dot, IN or AND bound literals. This follows the conflict resolution of DuckDB 1.5.6's Bison grammar. An unparenthesized NOT cannot start the first bound of a two-bound frame; (NOT 1) can, and NOT 1 can start a single or second bound. The same conflict state rejects LIKE, ILIKE, GLOB, SIMILAR, BETWEEN and the scanner's NOT_LA token immediately after the frame keyword BETWEEN, preserving the grammar's nonassociativity. Tokens that select the two-bound parse do not fall back to a single bound on failure: ROWS BETWEEN - PRECEDING AND 1 FOLLOWING remains a syntax error.

#The AST

For a SELECT statement, json_serialize_sql returns the AST that DuckDB's transformer builds. The tests contain a mapping (shape_*_test.go) that builds the same JSON from the syntax tree of this module, reduced to a canonical form (no locations; no key whose value is null, empty or false), and compare the two. It reproduces what the transformer does to the syntax: function names in lower case, count(*) as count_star, x IN (list) and x IN y, NOT turned into the opposite comparison, BETWEEN, LIKE as ~~, a ~ b as regexp_full_match, IF as CASE, windows with their frames and the named windows they refer to, the lambdas and list comprehensions as calls of list_apply and list_filter, intervals as to_days(CAST(trunc(CAST('1' AS DOUBLE)) AS INTEGER)), ANY and ALL as subqueries, GROUPING SETS, ROLLUP and CUBE as lists of sets, chains of UNION as a tree whose operands are paired from the left, the type of every number literal from its digits, and so on.

A statement is not compared when it has a construct that the mapping does not cover (the most common are PIVOT and UNPIVOT, which DuckDB expands, SAMPLE clauses, LATERAL, the VARIANT and GEOMETRY types, WITHIN GROUP, ARRAY(...) with an ORDER BY, and DESCRIBE). The two statements that differ are WITH RECURSIVE t(b) AS MATERIALIZED ((WITH helper(c) AS (SELECT 5) SELECT ... (the aliases that DuckDB puts in a recursive CTE written with a parenthesized WITH) and a NOT applied to NOT BETWEEN in a fuzzer-made expression. The mapping is test code: it is not an API of the module. Expanded frame controls also expose three mapping-only disagreements for unary OPERATOR(+): the source AST retains the written operator, but the test mapping does not yet unwrap that spelling to the canonical + function name. This limitation is separate from syntax acceptance and the two corpus disagreements above.

The precedence and the associativity of the operators were checked further with 250,000 random expressions made with other seeds than the corpus (REFERENCE_FILE, see testdata/README.md): the same acceptance, and the same AST in all of the 114,000 SELECT statements that they make.

#Known Deviations and Limits

• Seven texts (testdata/deviations.jsonl, each with its reason) on which the parser and DuckDB differ in acceptance. Six come from the pass with which DuckDB replaces Unicode white space before it parses: it does not close a dollar-quoted string when a letter follows the closing tag (select $a$b$a$x<U+3000>x), so it leaves the white space inside the name that follows, where the parser reads a space. The remaining grammar difference is a percent sign after a postfix operator (LIMIT -1| %), which DuckDB accepts. The other differences that mutating the tests found, in 1.4 million texts, were fixed and are in the corpus found.
• The checks after parsing (above) are not made: a text such as SELECT sum(x) OVER w where the window w is not defined is accepted. A function that checks the most common of them is not part of this module.
• Split and statements that DuckDB expands: PIVOT, COPY FROM DATABASE and ALTER TABLE ... ADD COLUMN ... DEFAULT are one statement here and several for DuckDB, which also gives them other texts.
• Input that is not UTF-8: DuckDB takes text, and the parser reads each invalid byte as U+FFFD in the positions of code points (the default), where two different bytes are the same character (a dollar-quoted string can open with $\xa5$ and close with $\xb1$); with Bytes they are different, and Split counts bytes.
• PRAGMA statements are rewritten by DuckDB into others (PRAGMA name = value is a SET, a call is a SELECT); Kind of a PragmaStmt is PRAGMA and its text is the text as written.
• Nesting is limited by the depth limit of the generated parser, 100,000 rule calls: 16,600 levels of parentheses, 14,200 of lists, 12,500 of CASE, 11,100 of derived tables, 10,000 of function calls, 7,100 of subqueries and 99,980 prefix operators in a chain (NOT NOT ... a, - - ... a). Deeper input fails with an error.
• Compile time and size: the generated parser.go is 15.6 MB (780,000 lines). The generator writes separate rules for tree parsing, recognition and typed AST construction, including the case-insensitive keyword tries. Its first compilation is expensive; subsequent builds reuse the Go build cache. Reducing generated code size remains open work.
• DuckDB's extensions (the parsers that extensions add, such as PRQL) and the settings that change how DuckDB parses (SET of an option of the parser) are not covered.

#Grammar

SETOF accepts simple type names, including modifiers and array suffixes. Qualified names after SETOF require explicit ARRAY or ARRAY[n]; bare qualified names and bracket suffixes are rejected. Constructed STRUCT, ROW, UNION and MAP types cannot directly follow SETOF, but their member types may use it. Ordinary qualified and constructed types retain their array forms. The AST omits SETOF and keeps the underlying type name, modifiers, dimensions and span.

duckdb.pego follows the grammar of DuckDB (third_party/libpg_query/grammar, the Bison files) and its scanner (scan.l). The ways in which it departs from the structure of the Bison grammar, where a PEG needs it or where it measurably pays, are commented in the grammar:

• The scanner is in the grammar. A rule for a token matches the token only; white space and comments are skipped (ws) where a rule needs them. The operators are cut as scan.l cuts them, with the lookahead that decides where a run of operator characters ends. The keywords are rules of their own, generated by internal/refgen/keywords.py from DuckDB's lists, which also write the tries that tell, for each kind of name, whether a word is a keyword that the kind forbids.
• Precedence and associativity are those of the %left, %right and %nonassoc declarations, in an Operators Rule block of levels: comparisons do not associate (a < b < c is an error, also on the right of AND), the operators of the LIKE, IN and BETWEEN level do not either, ->> has the precedence of a token that completes the expression on its left but takes an arithmetic expression on its right, and a postfix operator (a !) is read where the Bison grammar would shift it. NOT before BETWEEN, IN, LIKE, ILIKE and SIMILAR, NULLS before FIRST and LAST, and WITH before TIME and ORDINALITY are tokens of their own in the Bison grammar (NOT_LA, NULLS_LA, WITH_LA): the rules look at the word that follows.
• Where a keyword may be a name is decided by its category (unreserved, column_name, type_function, reserved), with the exceptions of the grammar: CUBE, ROLLUP, ENUM, BETWEEN and OPERATOR that the parser shifts as keywords in some positions.
• Every keyword is matched without regard to ASCII case. A word that is not a keyword at all is required in a few places, including an alias without AS and the dotted name of a type: SELECT 1 tExT is rejected, while SELECT 1 AS tExT and SELECT tExT FROM tExT remain valid. Keyword tries check the entire word, so tExT_, tExTé and tExT$ remain ordinary identifiers. Quoted names and the original spelling of identifier text are preserved.
• A rule has one action: where the Bison grammar gives alternatives different actions, they are rules of their own, so that every node has its type. Optional parts are *T fields, repeated parts []T.
• Alternatives are tried only where they can match: an identifier that cannot begin a call, a typed literal or a E'...' string is read by a rule that needs no lookahead (plain_column), the operators of an expression look at the character they begin with, and a function call is tried before the typed literal that looks like one (DECIMAL(10, 2) '1.5').
• Joins associate to the left, and INTERSECT binds tighter than UNION and EXCEPT, which associate to the left.

#Performance

Parsing a script of 256 KB (1,271 statements: tables, CTEs with joins, aggregates, window functions, subqueries, CASE, casts, lists and structs, set operations and DML; the benchmark writes it with BENCH_SCRIPT=file go test -run TestWriteBenchScript), go test -bench . on an Apple M3 Max:

DuckDB's time is the best of ten runs of extract_statements (the parser of DuckDB, and the step that makes its statement objects) on the script that the test wrote, with the Python module of DuckDB 1.5.6:

BENCH_SCRIPT=/tmp/bench.sql go test -run TestWriteBenchScript
python3 -c "
import duckdb, time
sql = open('/tmp/bench.sql').read()
con = duckdb.connect()
best = 1e9
for i in range(10):
    t = time.perf_counter()
    statements = con.extract_statements(sql)
    best = min(best, time.perf_counter() - t)
print(len(statements), 'statements', round(best * 1000, 1), 'ms', round(len(sql) / best / 1e6), 'MB/s')"

DuckDB's parser is C++ and parses the same script about four times as fast. The time here is mostly the cost of a grammar that has to decide, at each token, among the alternatives that the Bison automaton chooses by a table: the ordered choice of a PEG tries them one after the other. In the CPU profile of ParseAST, about a tenth of the time is spent recording what each failed alternative expected (for the error message), another tenth in the memo table, and another in the allocator and the garbage collector. Tuning the grammar took ParseAST from 4.1 MB/s to 5.6 MB/s: the operators of the expressions are tried only when the character that follows could begin them, a function call is not parsed twice to find that no string literal follows it, IN, LIKE, IS and the rest are one operator each, and the benchmarked version compared a word with unreserved keywords beginning with its letter, rather than all 330 (that alone made a CREATE TABLE with a TEXT column 5 times as fast). The current grammar uses case-insensitive tries for complete keyword recognition; the historical measurements above predate that correctness change. BenchmarkStatements now covers 36 kinds of statement, including explicit window frames. The historical 35-case measurements ranged from 2.5 MB/s (SELECT [1, 2, 3], {'a': 1, 'b': 2}, x[1], x.y) to 19 MB/s (ATTACH 'f.db' AS db).

#Development

python3 parsers/duckdb/internal/refgen/keywords.py parsers/duckdb/duckdb.pego --check
# Check generated keyword rules and keywords.go from the repository root (Python 3 and gofmt required).
# After changing a keyword list, run the same command without --check, then regenerate parser.go below.
go generate ./parsers                 # regenerate parser.go after changing duckdb.pego (from the repository root)
go test ./parsers                     # parser.go up to date; golden files on every backend of the engine
cd parsers/duckdb && go test ./...    # conformance against the reference data, the examples, the other tests
go test -short                        # a fraction of the texts
go test -bench .                      # benchmarks
go test -fuzz FuzzParse               # fuzz the parser (ParseAST agrees between position units, nothing panics)
NESTING_LIMITS=1 go test -v -run TestNesting   # the depth at which each construct fails

The reference data is made by internal/refgen and is described in testdata/README.md, with the command that makes it again from a checkout of DuckDB v1.5.6 and its Python module. Only the results are part of the module, not DuckDB's text; DuckDB is not a dependency of anything that is built or tested here.