DuckDB: remaining syntax to support #2804

Tracking what JSqlParser still needs to parse DuckDB 1.5 SQL. Each item below is accepted by DuckDB 1.5.6's parser and fails on master.

How it was measured: every statement ok / query block of DuckDB's own test suite (test/sql, 63,832 statements once the test-harness templates are excluded): master parses 93.4% after the merged PRs below (92.4% before). The remaining failures are mostly CHECKPOINT (949), BEGIN (758), DDL and EXPLAIN.

Merged: #2794 slices, #2795 == ** ~~ and chained signs, #2797 digit separators (opt-in), #2798 list comprehensions, #2799 FROM-first and TABLE t as query operands, #2801 star modifiers and trailing comma, #2802 LIMIT 10% and TABLESAMPLE, #2803 QUALIFY after WINDOW, OVER (w ...), FILTER without WHERE.

Queries

• EXPORT_STATE with an alias: SELECT sum(x) EXPORT_STATE s FROM t (308 test statements)
• Nested types in casts: NULL::MAP(INT[], VARCHAR[]), CAST(x AS UNION(a INT, b VARCHAR)), (42,)::TUPLE(INTEGER) (169)
• Integer division //: SELECT 7 // 2 (43); // currently starts a line comment (Snowflake), so this likely needs a dialect switch
• Recursive CTE with USING KEY: WITH RECURSIVE r(n) USING KEY (n) AS (...) (41)
• Keywords as names: SELECT a AS current, t.when, (v.outer).a, CREATE SCHEMA parent (~50)
• DESCRIBE / SUMMARIZE as a subquery: SELECT * FROM (DESCRIBE SELECT 1)
• Prefix alias: SELECT j : 42, SELECT "a b" : 42
• Unpacked *COLUMNS(...): SELECT *COLUMNS(*), [*COLUMNS(*)], x IN (*COLUMNS(*)) (11)
• GROUP BY mixing GROUPING SETS with other elements: GROUP BY a, GROUPING SETS (b, ()), GROUPING SETS (a), GROUPING SETS (b) (20); GroupByElement keeps expressions and grouping sets in separate lists, so the order cannot be represented
• Comparison chaining and postfix null tests: a <> b IS NULL, CAST(1=1 AS VARCHAR), 1 IS NOT NULL = TRUE, 1 = 1 IS DISTINCT FROM FALSE, a NOT NULL
• Operators and literals: s ^@ 'x' (starts with), BITSTRING'0101', xor(1, 2)
• EXCLUDE with string literals: * EXCLUDE ('i'), * EXCLUDE 'i'
• PIVOT_WIDER / PIVOT_LONGER

DDL

• Nested types in column definitions: UNION(i INT, s VARCHAR), MAP(VARCHAR, STRUCT(z INTEGER)), STRUCT(i INTEGER), ROW(i INTEGER)
• CREATE TYPE name AS <type> (AS STRUCT(...), AS BIGINT; AS ENUM already works) and DROP TYPE [IF EXISTS]
• Generated column without a type: total AS (price * 2)
• DROP MACRO [TABLE] name, COMMENT ON MACRO [TABLE] name IS '...'
• ALTER TABLE on nested fields: ADD COLUMN s.element.k INTEGER, RENAME s.i TO v1; ALTER a SET TYPE BIGINT USING a + 1
• CREATE [PERSISTENT] SECRET [IF NOT EXISTS] name (TYPE s3, ...), DROP SECRET

DML

• INSERT OR IGNORE INTO ...
• MERGE INTO t USING s USING (id) WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT (USING (cols) and actions without SET / VALUES; 42)

Utility statements

• CHECKPOINT [db], FORCE CHECKPOINT (976)
• BEGIN [TRANSACTION] [READ ONLY], ABORT (764)
• EXPLAIN of non-SELECT statements: EXPLAIN DROP TABLE t, EXPLAIN EXECUTE q
• VACUUM [ANALYZE] [t], ANALYZE [t]
• USE db.schema, SET GLOBAL threads TO 4, UPDATE EXTENSIONS
• EXPORT DATABASE 'dir' (FORMAT parquet), COPY FROM DATABASE a TO b

Newer than DuckDB 1.5

DuckDB's development branch also tests syntax that 1.5 rejects, e.g. JOIN BY (TYPE MARK), NEAREST k BY DISTANCE, CREATE TRIGGER, method calls on literals ('abc'.upper()). These can wait for a DuckDB release.