Extend a Multi-Dialect SQL Compiler Without Changing What Queries Mean

## Find Which Compiler Layer Owns the Behavior Before Editing Treat the system as a compiler whose layers have distinct responsibilities: - the tokenizer converts SQL text into typed tokens and lets dialects override lexical mappings; - the parser uses recursive descent to build an abstract syntax tree (AST), with dedicated matching, cursor, list, and wrapper helpers; - expression classes represent semantic SQL concepts and support traversal and transformation; - the generator recursively turns expressions back into SQL, using simple transform mappings or dedicated generation methods; - dialect modules extend a common base with feature flags, token sets, parsing mappings, and generation mappings; - optimization rules canonicalize and rewrite the AST in a deliberate order; - schema and type information support optimization, transpilation, and lineage; - lineage connects output columns to upstream expressions and tables. Locate the owning layer before changing behavior. Keep tokenization, parsing, semantic representation, optimization, and generation separate rather than fixing one layer by leaking logic into another. Preserve parser and generator symmetry: parsing maps tokens to expressions, while generation maps expressions to SQL. If the parser intentionally falls back to a generic command node for unrecognized text, do not silently turn that preservation mechanism into partial parsing. ## Use the Repository's Own Test and Check Commands for Pure and Native Code Read the repository's contribution and architecture guidance before editing. Use its documented targets for basic installation, development installation, optional native-extension installation, pre-commit setup, full tests, unit tests, style checks, type checks, and benchmarks. Distinguish the pure-language implementation from any optional compiled extension. Run the directly relevant mode first, then the broader checks required by the change. When native acceleration is affected, test both the pure implementation and the compiled path rather than assuming they behave identically. Target failures precisely during iteration by running a specific test file, class, or method. Before submission, run the repository's complete required check target, including formatting, static checks, pure-runtime tests, and native-extension tests where applicable. Use parsing and optimization benchmarks for performance-sensitive work. Compare like-for-like configurations and do not trade semantic correctness for a favorable microbenchmark. ## Keep the Same Meaning When Turning SQL into Another Dialect Treat the AST as the semantic source of truth. Parse source SQL into semantic expressions and generate the target dialect from those expressions; preserve meaning rather than source spelling. Comments may be preserved on a best-effort basis, but semantic equivalence takes priority. Add features shared by multiple dialects to the common base when their semantics genuinely match. Add dialect-specific syntax or behavior only to the relevant dialect. Use explicit feature flags, reserved-token sets, function or statement mappings, and expression-to-SQL mappings instead of scattered name checks. Keep optimizer rules in their intentional sequence. Qualification, type annotation, predicate or projection pushdown, and simplification may depend on earlier steps and schema information. Treat the optimizer as logical rewriting, not a physical query planner. When a feature depends on database structure, supply schema information explicitly. Do not claim reliable qualification, type inference, optimization, or column lineage when required table and column metadata is absent. ## Build SQL with AST Helpers and Templates, Not by Pasting Strings Before creating a generic function expression, search the expression catalog for an existing dedicated class. Use a generic anonymous expression only when no dedicated semantic class exists. Prefer the simplest supported generation mechanism: 1. For a single complex expression type, implement the automatically discovered `<lowercase_expression_name>_sql` generator method. 2. Reserve transform mappings for simple one-line renames, lambdas, or functions that intentionally have multiple entry points. 3. Inside generator methods, use the generator's function helper rather than constructing a function node only to serialize it. 4. Use expression builders and operators for arrays, boolean expressions, cases, casts, columns, nulls, arithmetic, indexing, and comparisons. 5. For complex output, parse an SQL template and replace placeholders in a copied AST. Do not assemble SQL fragments with string interpolation. AST builders and templates preserve quoting, escaping, nesting, and dialect behavior that hand-built strings commonly break. ## Check Types After Type Annotation and Handle NULL at Run Time Distinguish syntactic and semantic type checks. Use a literal predicate such as `is_string` only when checking whether a node is literally a string and extracting its literal value. Use `is_type()` when checking the inferred semantic type of a column, function result, or other expression. If semantic behavior depends on types, run type annotation before calling `is_type()`. Tests for type-dependent transpilation must annotate the AST; otherwise a correct semantic check can appear to fail because the type was never populated. Use the expression's conversion method, such as `to_py()`, to extract a numeric literal value after verifying that the node is a numeric literal. Do not manually strip quoting and cast raw internal text. Do not resolve SQL `NULL` behavior at transpilation time by checking only for a literal null node. Generate SQL with runtime `IS NULL` logic so columns, parameters, expressions, and literal nulls all follow the same semantics. When searching ancestors, include query-scope boundaries so the search cannot escape into a parent query. Use the framework's unsupported-argument declaration mechanism when a target dialect cannot represent specific arguments. ## Test Each Dialect and Run the Tests Against Real Databases Add tests to the suite that owns the affected dialect or compiler layer. Use round-trip assertions for syntax that should remain identical and cross-dialect assertions when one semantic expression must generate different target SQL. Do not duplicate coverage that already proves the same behavior. Add focused cases for the new semantic distinction, including nullability, types, quoting, scope, unsupported arguments, or dialect differences when relevant. Ensure that tests placed in database-specific suites actually execute against the corresponding databases or compatible engines. A generated string alone is not proof when the rule depends on runtime behavior. For type-sensitive output, test both literals and typed non-literal expressions. For native acceleration, confirm parity with the pure implementation. For optimizer changes, include schema-aware cases and verify that rule ordering does not alter meaning. ## Keep Each Change to One Purpose and Easy to Review Keep each change limited to one well-defined purpose. Add tests for non-trivial behavior, update public docstrings when APIs change, follow the repository's pull-request and commit conventions, and run the complete required checks before submission. Remove unused imports, variables, and dead code. Avoid compatibility shims for code that has intentionally been removed. Prefer a readable inline expression over an unnecessary temporary, but do not compress logic until its semantic phases become unclear. Use comments only for non-obvious logic and add docstrings only for complex behavior or public APIs. Do not modify license files or licensing configuration unless the task explicitly concerns licensing. Review the change for accidental dialect coupling, duplicated expression types, hand-built SQL, missing type annotation, compile-time-only null handling, scope leakage, and untested native or database-specific behavior. ## Keep the Native Speed-Up Path and the Parser Design When Optimizing Treat performance as a product feature, even when the primary implementation uses a high-level language. Preserve optional native acceleration paths and benchmark changes that affect tokenization, parsing, generation, or optimization. Do not replace an intentional recursive-descent parser with Pratt parsing as a presumed optimization. The parsing strategy is an architectural choice, not an interchangeable speed setting. Avoid abstractions known to interfere with the compiled path when the codebase documents a preferred alternative, such as choosing simple union types and structural duck typing over an unsupported protocol abstraction. Optimize only after preserving correctness across parsing, AST construction, generation, dialect behavior, and tests. A faster implementation that changes semantics, loses quoting, mishandles `NULL`, or diverges between pure and compiled modes is a regression.