# GoSQLX SQL Feature Compatibility Matrix

Source: https://gosqlx.dev/docs/reference/sql-compatibility/

## Overview

This matrix documents the comprehensive SQL feature support in GoSQLX across different SQL dialects and standards. The testing was conducted using the comprehensive integration test suite with 700+ test cases covering real-world SQL patterns.

### Real-world dialect audit (October 2026)

The real-world corpus contains 2,813 production-style statements: SQL written
by hand to imitate what ORMs, migration tools and applications send, not SQL
collected from production systems. Every statement passes
split → dialect-aware parse → format → reparse → format idempotency, plus a
check for dropped SQL words. `testdata/realworld/known_failures.txt` lists no
failures.
These results describe this corpus; they do not establish complete dialect
coverage or database execution equivalence. Some routine and utility statements
retain their text rather than receiving a complete semantic AST.

| Dialect | Passing statements | Known failures | Pass rate |
| --- | ---: | ---: | ---: |
| PostgreSQL | 305 / 305 | 0 | 100% |
| MySQL | 414 / 414 | 0 | 100% |
| MariaDB | 380 / 380 | 0 | 100% |
| SQL Server | 277 / 277 | 0 | 100% |
| Oracle | 368 / 368 | 0 | 100% |
| SQLite | 348 / 348 | 0 | 100% |
| Snowflake | 327 / 327 | 0 | 100% |
| ClickHouse | 394 / 394 | 0 | 100% |

The table totals 2,813 statements and was checked with `scripts/dialect-audit`
at commit `fda9a5ef`. Reproduce one dialect's row from the repository root
(replace `<d>` with a dialect string such as `mysql`); the last line of output
reads, for example:

```text
dialect=mysql total=405 passed=405 failed=0 stages=map[ok:405]
```

The command is:

```sh
go run ./scripts/dialect-audit -dialect <d> testdata/realworld/<d>/*.sql
```

The same command audits your own SQL files. To reproduce the ratchet and every
per-dialect summary at once:

```sh
go test ./pkg/gosqlx -run '^TestRealWorldCorpus$' -count=1
for dir in testdata/realworld/*/; do
  go run ./scripts/dialect-audit -dialect "$(basename "$dir")" "$dir"*.sql
done
```

`TestRealWorldCorpus` is a ratchet against `known_failures.txt`: a statement
that fails and is not listed fails the test, and so does a listed statement
that now passes, so the list can only shrink. Regenerate it with
`go test ./pkg/gosqlx -run TestRealWorldCorpus -update-known-failures`.
`BenchmarkRealWorldParse` and `BenchmarkRealWorldParseFormat` measure the corpus.

SQL Server batch boundaries also have whole-script round-trip tests: the AST
retains each `GO` as a `BatchSeparator`, including its optional repeat count,
and both library and CLI rendering keep it on a separate line. The splitter
removes client directives when returning individual SQL statements, so the
statement-by-statement corpus totals above do not count GO directives.

### Feature Highlights

#### Snowflake and ClickHouse
- ✅ **Snowflake**: 87/87 QA corpus pass (MATCH_RECOGNIZE, QUALIFY, VARIANT paths, time-travel, LATERAL FLATTEN, stage/stream/task/pipe stubs)
- ✅ **ClickHouse**: 83/83 QA corpus pass: `ARRAY JOIN`, scalar CTEs, `CREATE MATERIALIZED VIEW`, numeric `SAMPLE ... OFFSET`, `LIMIT offset, count`
- See [Snowflake](#snowflake) and [ClickHouse](#clickhouse) below for per-dialect details

#### SQLite, Security Scanner and Error Codes
- ✅ **SQLite PRAGMA**: Fully parsed - bare (`PRAGMA x`), arg (`PRAGMA x(n)`), and assignment (`PRAGMA x=v`) forms; `PragmaStatement` AST node
- ✅ **WITHOUT ROWID**: `CREATE TABLE ... WITHOUT ROWID`; reserved keywords now valid as DDL column names
- ✅ **Tautology Detection**: `ScanSQL()` detects `1=1`, `'a'='a'`, `col=col`, `OR TRUE` → CRITICAL severity (`PatternTautology`)
- ✅ **UNION Detection Split**: `PatternUnionInjection` (CRITICAL, system tables + NULL padding) / `PatternUnionGeneric` (HIGH) - eliminates false-positive CRITICAL on legitimate queries
- ✅ **E1009**: `ErrCodeUnterminatedBlockComment` for unterminated `/* ... */` block comments
- ✅ **Dialect Fixes**: MySQL backtick + SQL Server bracket reserved-word identifiers now parsed correctly
- ✅ **Parser Fixes**: KEY/INDEX in qualified names, NATURAL JOIN type, OVER window_name (bare name per SQL:2003 §7.11)

#### Dialect Engine and MySQL Syntax
- ✅ **Dialect Mode Engine**: First-class dialect support with `ParseWithDialect()` - thread dialect through tokenizer and parser
- ✅ **MySQL Syntax Support**:
  - **LIMIT offset, count** - MySQL-style `LIMIT 10, 20`
  - **ON DUPLICATE KEY UPDATE** - MySQL upsert syntax
  - **SHOW statements** - `SHOW TABLES`, `SHOW DATABASES`, `SHOW CREATE TABLE`
  - **DESCRIBE/EXPLAIN** - Table description commands
  - **REPLACE INTO** - MySQL insert-or-replace
  - **UPDATE/DELETE with LIMIT** - MySQL extension
  - **GROUP_CONCAT** - With ORDER BY and SEPARATOR clause
  - **MATCH AGAINST** - Full-text search expressions
  - **REGEXP/RLIKE** - Regular expression operators
  - **INTERVAL number unit** - MySQL-style `INTERVAL 30 DAY`
- ✅ **Query Transform API**: Programmatic SQL rewriting via `pkg/transform/`
- ✅ **Comment Preservation**: Comments survive parse-format round-trips
- ✅ **AST-to-SQL Serialization**: `SQL()` methods on all AST nodes
- ✅ **Dollar-Quoted Strings**: PostgreSQL `$$body$$` and `$tag$body$tag$`
- ✅ **Error Recovery**: Multi-error parsing with `ParseWithRecovery()`
- ✅ **~50% Faster Parsing**: Token type overhaul with O(1) integer comparison
- ✅ **Snowflake Dialect**: Keyword detection and weighted dialect scoring

#### Schema-Qualified Names and PostgreSQL Enhancements
- ✅ **Schema-Qualified Names**: Full support for `schema.table`, `db.schema.table` in all DML/DDL statements
- ✅ **PostgreSQL Enhancements**:
  - **Type Casting** - `::` operator for PostgreSQL-style casts (`SELECT 1::int`)
  - **UPSERT** - `INSERT ... ON CONFLICT DO UPDATE/NOTHING`
  - **Positional Parameters** - `$1`, `$2` style parameter placeholders (PostgreSQL); `?` placeholders for MySQL/MariaDB/SQLite (dialect-gated); `:1`/`:name` bind variables for Oracle (dialect-gated)
  - **JSONB Operators** - Additional `@?` and `@@` operators
  - **Regex Operators** - `~`, `~*`, `!~`, `!~*` for pattern matching
- ✅ **ARRAY Constructors**: `ARRAY[1, 2, 3]` expressions with subscript/slice operations
- ✅ **WITHIN GROUP** - Ordered-set aggregate functions
- ✅ **INTERVAL Expressions** - `INTERVAL '1 day'` temporal literals, plus the ANSI qualifier form `INTERVAL '1' DAY` / `INTERVAL '1-2' YEAR TO MONTH`
- ✅ **FOR UPDATE/SHARE** - Row-level locking clauses
- ✅ **Multi-row INSERT** - `INSERT INTO t VALUES (1), (2), (3)` batch inserts

#### PostgreSQL Extensions and SQL Standards
- ✅ **PostgreSQL Extensions**:
  - **LATERAL JOIN** - Correlated subqueries and table functions in FROM/JOIN clauses; select the PostgreSQL dialect for table functions
  - **Table Function Aliases** - Column alias lists and `WITH ORDINALITY` on FROM/JOIN table functions: `unnest(arr) AS g(elem)`, `generate_series(1, 3) WITH ORDINALITY AS s(n, idx)`. `WITH ORDINALITY` is PostgreSQL-only.
  - **Composite Field Access** - `(get_item(payload)).name`, `(payload).*`, quoted fields and chained field/subscript access such as `(payload).items[1].name` and `(items)[1].name`. Any keyword, reserved ones included, is accepted as a field name, e.g. `(payload).order`. Field access parses for PostgreSQL, BigQuery, the generic dialect and the default (no dialect) parser; MySQL, MariaDB, SQL Server, SQLite, Oracle, Snowflake, ClickHouse and Redshift reject it with E4001.
  - **JSON/JSONB Operators** - Complete operator set (`->`, `->>`, `#>`, `#>>`, `@>`, `<@`, `?`, `?|`, `?&`, `#-`)
  - **DISTINCT ON** - PostgreSQL-specific row selection
  - **FILTER Clause** - Conditional aggregation (SQL:2003)
  - **Aggregate ORDER BY** - Ordering within aggregate functions
  - **RETURNING Clause** - Return modified rows from INSERT/UPDATE/DELETE
- ✅ **SQL Standards**:
  - **FETCH FIRST n ROWS** - Standard row limiting (SQL-99 F861)
  - **FETCH WITH TIES** - Include tied rows (SQL-99 F862)
  - **OFFSET-FETCH** - Standard pagination
  - **TRUNCATE TABLE** - SQL:2008 table truncation
  - **Materialized CTE Hints** - CTE optimization

#### Core SQL Features
- ✅ **MERGE Statements** (SQL:2003 F312)
- ✅ **GROUPING SETS, ROLLUP, CUBE** (SQL-99 T431)
- ✅ **Materialized Views** (CREATE, DROP, REFRESH)
- ✅ **Table Partitioning** (RANGE, LIST, HASH)
- ✅ **NULLS FIRST/LAST** (SQL-99 F851)
- ✅ **Advanced Operators** (BETWEEN, IN, LIKE, IS NULL)
- ✅ **Comprehensive Subqueries** (Scalar, Table, Correlated, EXISTS)
- ✅ **Window Functions** - Complete SQL-99 support (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, etc.)
- ✅ **SQL Injection Detection** (`pkg/sql/security` package)

## Legend

- ✅ **Full Support**: Complete parsing and AST generation with validation
- ⚠️ **Partial Support**: Basic syntax recognition, limited semantic validation
- ❌ **Not Supported**: Feature not recognized or causes parsing errors
- 🔧 **Syntax Only**: Recognizes syntax but no semantic validation

## Core SQL Features Support Matrix

### Data Manipulation Language (DML)

| Feature | PostgreSQL | MySQL | SQL Server | Oracle | SQLite | GoSQLX Parser | Test Coverage |
|---------|------------|-------|------------|--------|--------|---------------|---------------|
| **SELECT** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| Basic WHERE clauses | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| Complex WHERE (nested) | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| ORDER BY | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| GROUP BY | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| HAVING | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| DISTINCT | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| LIMIT/TOP | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 95% |
| FETCH FIRST (SQL-99 F861) | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 95% |
| FETCH WITH TIES (SQL-99 F862) | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 90% |
| OFFSET-FETCH pagination | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 95% |
| **INSERT** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| INSERT VALUES | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| INSERT SELECT | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| Multi-row INSERT | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **UPDATE** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| UPDATE with JOIN | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 90% |
| Multi-table UPDATE | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 80% |
| **DELETE** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| DELETE with JOIN | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 90% |
| **TRUNCATE** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 95% |
| TRUNCATE with CASCADE | ✅ | ❌ | ❌ | ✅ | ❌ | ✅ Full | 90% |
| **MERGE** (SQL:2003 F312) | ✅ | ❌ | ✅ | ✅ | ❌ | ✅ Full | 95% |
| MERGE WHEN MATCHED | ✅ | ❌ | ✅ | ✅ | ❌ | ✅ Full | 95% |
| MERGE WHEN NOT MATCHED | ✅ | ❌ | ✅ | ✅ | ❌ | ✅ Full | 95% |
| **RETURNING Clause** (PostgreSQL) | ✅ | ❌ | ✅ | ✅ | ❌ | ✅ Full | 95% |
| INSERT...RETURNING | ✅ | ❌ | ✅ | ✅ | ❌ | ✅ Full | 95% |
| UPDATE...RETURNING | ✅ | ❌ | ✅ | ✅ | ❌ | ✅ Full | 95% |
| DELETE...RETURNING | ✅ | ❌ | ✅ | ✅ | ❌ | ✅ Full | 95% |

### Data Definition Language (DDL)

| Feature | PostgreSQL | MySQL | SQL Server | Oracle | SQLite | GoSQLX Parser | Test Coverage |
|---------|------------|-------|------------|--------|--------|---------------|---------------|
| **CREATE TABLE** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| Column definitions | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| Primary keys | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| Foreign keys | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| Unique constraints | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| Check constraints | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 95% |
| **ALTER TABLE** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 95% |
| ADD COLUMN | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| DROP COLUMN | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| MODIFY COLUMN | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 95% |
| **DROP TABLE** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **CREATE INDEX** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| Unique indexes | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| Partial indexes | ✅ | ❌ | ⚠️ | ❌ | ✅ | ⚠️ Partial | 40% |
| **CREATE VIEW** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **CREATE MATERIALIZED VIEW** | ✅ | ❌ | ✅ | ✅ | ❌ | ✅ Full | 95% |
| REFRESH MATERIALIZED VIEW | ✅ | ❌ | ❌ | ✅ | ❌ | ✅ Full | 95% |
| **TABLE PARTITIONING** | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 90% |
| PARTITION BY RANGE | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 90% |
| PARTITION BY LIST | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 90% |
| PARTITION BY HASH | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 90% |

### JOIN Operations

| Feature | PostgreSQL | MySQL | SQL Server | Oracle | SQLite | GoSQLX Parser | Test Coverage |
|---------|------------|-------|------------|--------|--------|---------------|---------------|
| **INNER JOIN** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **LEFT JOIN** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **RIGHT JOIN** | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 100% |
| **FULL OUTER JOIN** | ✅ | ❌ | ✅ | ✅ | ❌ | ✅ Full | 85% |
| **CROSS JOIN** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **NATURAL JOIN** | ✅ | ✅ | ❌ | ✅ | ✅ | ✅ Full | 95% |
| Multiple table JOINs | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| Self JOINs | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| Parenthesized JOIN groups (left-nested, e.g. MySQL view output) | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 90% |
| **LATERAL JOIN** (PostgreSQL) | ✅ | ❌ | ❌ | ❌ | ❌ | ✅ Full | 95% |
| LATERAL with LEFT JOIN | ✅ | ❌ | ❌ | ❌ | ❌ | ✅ Full | 95% |
| LATERAL with INNER JOIN | ✅ | ❌ | ❌ | ❌ | ❌ | ✅ Full | 95% |
| LATERAL with CROSS JOIN | ✅ | ❌ | ❌ | ❌ | ❌ | ✅ Full | 95% |
| JOIN with USING clause | ✅ | ✅ | ❌ | ✅ | ✅ | ✅ Full | 95% |

### Subqueries

| Feature | PostgreSQL | MySQL | SQL Server | Oracle | SQLite | GoSQLX Parser | Test Coverage |
|---------|------------|-------|------------|--------|--------|---------------|---------------|
| **Scalar subqueries** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **Table subqueries** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **Correlated subqueries** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **EXISTS** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **NOT EXISTS** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **IN (subquery)** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **ANY/SOME** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 95% |
| **ALL** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 95% |

### Aggregate Functions

| Feature | PostgreSQL | MySQL | SQL Server | Oracle | SQLite | GoSQLX Parser | Test Coverage |
|---------|------------|-------|------------|--------|--------|---------------|---------------|
| **COUNT** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **SUM** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **AVG** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **MIN/MAX** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **FILTER Clause** (SQL:2003) | ✅ | ❌ | ❌ | ✅ | ❌ | ✅ Full | 95% |
| COUNT(*) FILTER (WHERE...) | ✅ | ❌ | ❌ | ✅ | ❌ | ✅ Full | 95% |
| Aggregate ORDER BY (PostgreSQL) | ✅ | ❌ | ❌ | ❌ | ❌ | ✅ Full | 95% |
| **GROUP_CONCAT** | ❌ | ✅ | ❌ | ❌ | ✅ | ✅ Full | 95% |
| **STRING_AGG** | ✅ | ❌ | ✅ | ✅ | ❌ | ✅ Full | 90% |
| **ARRAY_AGG** | ✅ | ❌ | ❌ | ✅ | ❌ | ✅ Full | 90% |

## Advanced SQL Features

### Common Table Expressions (CTEs)

| Feature | PostgreSQL | MySQL | SQL Server | Oracle | SQLite | GoSQLX Parser | Test Coverage |
|---------|------------|-------|------------|--------|--------|---------------|---------------|
| **Basic CTE** (WITH clause) | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **Multiple CTEs** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **Recursive CTE** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **CTE in INSERT ... SELECT** (`INSERT INTO t WITH c AS (...) SELECT ...`) | ✅ | ✅ | ❌ | ✅ | ✅ | ✅ Full | 95% |
| **Nested CTEs** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 95% |
| **Materialized CTE Hints** | ✅ | ❌ | ❌ | ❌ | ❌ | ✅ Full | 90% |
| WITH...AS MATERIALIZED | ✅ | ❌ | ❌ | ❌ | ❌ | ✅ Full | 90% |
| WITH...AS NOT MATERIALIZED | ✅ | ❌ | ❌ | ❌ | ❌ | ✅ Full | 90% |

### Window Functions

| Feature | PostgreSQL | MySQL | SQL Server | Oracle | SQLite | GoSQLX Parser | Test Coverage |
|---------|------------|-------|------------|--------|--------|---------------|---------------|
| **ROW_NUMBER()** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **RANK()** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **DENSE_RANK()** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **NTILE()** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **LAG/LEAD** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **FIRST_VALUE/LAST_VALUE** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **NTH_VALUE** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 95% |
| **PARTITION BY** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **ORDER BY in window** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **ROWS frame** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 95% |
| **RANGE frame** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 90% |
| Frame UNBOUNDED PRECEDING | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 95% |
| Frame UNBOUNDED FOLLOWING | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 95% |
| Frame CURRENT ROW | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 95% |
| Frame N PRECEDING/FOLLOWING | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 90% |
| **GROUPS frame** | ✅ | ❌ | ❌ | ✅ | ✅ | ✅ Full | 90% |
| Frame EXCLUDE (CURRENT ROW/GROUP/TIES/NO OTHERS) | ✅ | ❌ | ❌ | ✅ | ✅ | ✅ Full | 90% |

### Set Operations

| Feature | PostgreSQL | MySQL | SQL Server | Oracle | SQLite | GoSQLX Parser | Test Coverage |
|---------|------------|-------|------------|--------|--------|---------------|---------------|
| **UNION** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **UNION ALL** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **INTERSECT** | ✅ | ❌ | ✅ | ✅ | ✅ | ✅ Full | 90% |
| **EXCEPT/MINUS** | ✅ | ❌ | ✅ | ✅ | ❌ | ✅ Full | 90% |
| Parenthesized set-operation operands | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 90% |
| Set operation / WITH / double-parenthesized query as derived table | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 90% |

Parenthesized operands such as `(SELECT ... ORDER BY a LIMIT 5) UNION ALL (SELECT ...)` and
`SELECT ... EXCEPT (SELECT ... UNION SELECT ...)` keep their parentheses when rendered. An
`ORDER BY`/`LIMIT` written after a parenthesized query is accepted when it can be represented
without changing meaning (for example `(SELECT 1) ORDER BY 1`); a second `ORDER BY`/`LIMIT`
level over a parenthesized query that already has one, such as
`(SELECT a FROM t ORDER BY a LIMIT 10) ORDER BY b`, returns `E4001` (unsupported feature).

### Grouping Operations (SQL-99 T431)

| Feature | PostgreSQL | MySQL | SQL Server | Oracle | SQLite | GoSQLX Parser | Test Coverage |
|---------|------------|-------|------------|--------|--------|---------------|---------------|
| **GROUPING SETS** | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 95% |
| **ROLLUP** | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 95% |
| **CUBE** | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 95% |
| Combined GROUPING SETS | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 90% |
| GROUPING() function | ✅ | ✅ | ✅ | ✅ | ❌ | ✅ Full | 90% |

### ORDER BY Extensions (SQL-99 F851)

| Feature | PostgreSQL | MySQL | SQL Server | Oracle | SQLite | GoSQLX Parser | Test Coverage |
|---------|------------|-------|------------|--------|--------|---------------|---------------|
| **NULLS FIRST** | ✅ | ❌ | ❌ | ✅ | ❌ | ✅ Full | 95% |
| **NULLS LAST** | ✅ | ❌ | ❌ | ✅ | ❌ | ✅ Full | 95% |
| Multiple columns with NULLS | ✅ | ❌ | ❌ | ✅ | ❌ | ✅ Full | 90% |

### Expression Operators

| Feature | PostgreSQL | MySQL | SQL Server | Oracle | SQLite | GoSQLX Parser | Test Coverage |
|---------|------------|-------|------------|--------|--------|---------------|---------------|
| **BETWEEN...AND** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **NOT BETWEEN** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **IN (list)** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **IN (subquery)** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **NOT IN** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **LIKE** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **NOT LIKE** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **IS NULL** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **IS NOT NULL** | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ Full | 100% |
| **IS TRUE/FALSE** | ✅ | ✅ | ❌ | ❌ | ✅ | ✅ Full | 90% |

## Dialect-Specific Features

**Note**: This section documents dialect-specific features where "Support Level" refers to the native database's support, while "GoSQLX Parser" and "Test Coverage" indicate GoSQLX implementation status. Many features listed have keyword recognition only (🔧 Syntax) without full parsing logic.

### PostgreSQL-Specific Features

| Feature | Support Level | GoSQLX Parser | Test Coverage | Notes |
|---------|---------------|---------------|---------------|-------|
| **Arrays** | ✅ Full | 🔧 Syntax | 40% | Keyword recognition only |
| **JSON/JSONB Types** | ✅ Full | ✅ Full | 95% | Full type support |
| **JSON -> Operator** | ✅ Full | ✅ Full | 95% | Extract JSON field as JSON |
| **JSON ->> Operator** | ✅ Full | ✅ Full | 95% | Extract JSON field as text |
| **JSON #> Operator** | ✅ Full | ✅ Full | 95% | Extract nested JSON path as JSON |
| **JSON #>> Operator** | ✅ Full | ✅ Full | 95% | Extract nested JSON path as text |
| **JSON @> Operator** | ✅ Full | ✅ Full | 95% | Contains (left contains right) |
| **JSON <@ Operator** | ✅ Full | ✅ Full | 95% | Contained by (left contained by right) |
| **JSON ? Operator** | ✅ Full | ✅ Full | 95% | Key exists |
| **JSON ?| Operator** | ✅ Full | ✅ Full | 95% | Any key exists |
| **JSON ?& Operator** | ✅ Full | ✅ Full | 95% | All keys exist |
| **JSON #- Operator** | ✅ Full | ✅ Full | 95% | Delete path |
| **DISTINCT ON** | ✅ Full | ✅ Full | 95% | SELECT DISTINCT ON (columns) ORDER BY... |
| **FILTER Clause** | ✅ Full | ✅ Full | 95% | Aggregate FILTER (WHERE condition) |
| **Aggregate ORDER BY** | ✅ Full | ✅ Full | 95% | string_agg(col, ',' ORDER BY col) |
| **RETURNING Clause** | ✅ Full | ✅ Full | 95% | INSERT/UPDATE/DELETE RETURNING |
| **Full-text search** | ✅ Full | 🔧 Syntax | 30% | tsvector, tsquery types |
| **LATERAL Joins** | ✅ Full | ✅ Full | 95% | Full support with LEFT/INNER/CROSS variants |
| **Custom operators** | ✅ Full | ⚠️ Partial | 30% | Basic operator recognition |
| **Dollar quoting** | ✅ Full | ✅ Full | 90% | `$$body$$` and `$tag$body$tag$` (since v1.8.0) |

### MySQL-Specific Features

| Feature | Support Level | GoSQLX Parser | Test Coverage | Notes |
|---------|---------------|---------------|---------------|-------|
| **SHOW statements** | ✅ Full | ✅ Full | 95% | SHOW TABLES, DATABASES, CREATE TABLE (since v1.8.0) |
| **DESCRIBE/EXPLAIN** | ✅ Full | ✅ Full | 95% | Table description commands (since v1.8.0) |
| **REPLACE INTO** | ✅ Full | ✅ Full | 95% | MySQL insert-or-replace (since v1.8.0) |
| **ON DUPLICATE KEY UPDATE** | ✅ Full | ✅ Full | 95% | MySQL upsert syntax (since v1.8.0) |
| **LIMIT offset, count** | ✅ Full | ✅ Full | 95% | MySQL-style `LIMIT 10, 20` (since v1.8.0) |
| **UPDATE/DELETE with LIMIT** | ✅ Full | ✅ Full | 90% | MySQL extension (since v1.8.0) |
| **GROUP_CONCAT** | ✅ Full | ✅ Full | 95% | With ORDER BY and SEPARATOR (since v1.8.0) |
| **MATCH/AGAINST** | ✅ Full | ✅ Full | 95% | Full-text search (since v1.8.0) |
| **REGEXP/RLIKE** | ✅ Full | ✅ Full | 90% | Regular expression operators (since v1.8.0) |
| **INTERVAL number unit** | ✅ Full | ✅ Full | 90% | MySQL-style `INTERVAL 30 DAY` (since v1.8.0) |
| **IF()/REPLACE() as functions** | ✅ Full | ✅ Full | 85% | Keywords usable as function names (since v1.8.0) |
| **Storage engines** | ✅ Full | 🔧 Syntax | 80% | ENGINE=InnoDB syntax |
| **Index hints** | ✅ Full | ✅ Full | 75% | USE/IGNORE/FORCE INDEX |
| **Partitioning** | ✅ Full | 🔧 Syntax | 70% | PARTITION BY syntax |
| **AUTO_INCREMENT** | ✅ Full | ✅ Full | 95% | Column property |
| **Backtick identifiers** | ✅ Full | ✅ Full | 100% | `` `table`.`column` `` syntax |
| **Multi-part column references** | ✅ Full | ✅ Full | 95% | `` `db`.`table`.`column` `` and `db.table.*` (#521) |
| **`\|\|` as logical OR** | ✅ Full | ✅ Full | 95% | MySQL/MariaDB default; see below (#781) |

#### `||` operator in MySQL and MariaDB

In MySQL and MariaDB `||` is a synonym for `OR` unless the server runs with the
`PIPES_AS_CONCAT` SQL mode. GoSQLX follows the server default:

- With the `mysql` or `mariadb` dialect, `a = 1 || b` parses as `(a = 1) OR b`,
  with `OR` precedence (looser than `AND`, `NOT`, comparisons and arithmetic).
  The AST is the same as for the `OR` keyword
  (`BinaryExpression{Operator: "OR"}`), so linter rules, the security scanner
  and other analysis see an `OR`. The node also sets `PipesOr: true`, and
  `SQL()`, the formatter, the CLI and LSP formatting write `||` back, so
  formatting never changes the meaning for a server that runs with
  `PIPES_AS_CONCAT`.
- To mirror `PIPES_AS_CONCAT`, pass `parser.WithPipesAsConcat()` (or
  `gosqlx.WithPipesAsConcat()` with `gosqlx.ParseTree`); `||` is then string
  concatenation. The CLI, LSP and linter configuration do not expose this
  option yet, so with `--dialect mysql` they analyse `||` as `OR` (for example
  OR-based lint rules may report a concatenation).
- Precedence in the opt-in mode differs from MySQL: MySQL gives `||` under
  `PIPES_AS_CONCAT` a precedence between `^` and the unary operators, tighter
  than `*`, `/`, `+` and `-`. GoSQLX places concatenation just below `+` and
  `-`, so `a + b || c` parses as `(a + b) || c`, while MySQL reads
  `a + (b || c)`. Parenthesise mixed arithmetic and concatenation.
- Every other dialect (PostgreSQL, SQL Server, Oracle, SQLite, Snowflake,
  ClickHouse, and no dialect) always parses `||` as concatenation.
- Transpiling from any of those dialects to MySQL or MariaDB rewrites `a || b`
  to `CONCAT(a, b)`. Transpiling from MySQL or MariaDB to any of them writes a
  `||` OR as `OR`; between MySQL and MariaDB it stays `||`.

```go
// MySQL default: || is logical OR
p := parser.NewParser(parser.WithDialect("mysql"))

// MySQL with PIPES_AS_CONCAT: || is concatenation
p2 := parser.NewParser(parser.WithDialect("mysql"), parser.WithPipesAsConcat())

tree, err := gosqlx.ParseTree(ctx, sql,
    gosqlx.WithDialect("mysql"), gosqlx.WithPipesAsConcat())
```

Before v1.16.0 (#781), `||` was concatenation in every dialect, including MySQL and MariaDB.

### MariaDB-Specific Features

MariaDB inherits all MySQL features (SHOW, DESCRIBE, REPLACE INTO, ON DUPLICATE KEY UPDATE, GROUP_CONCAT, MATCH/AGAINST, REGEXP/RLIKE, backtick identifiers, etc.) and adds the following extensions:

| Feature | Support Level | GoSQLX Parser | Test Coverage | Notes |
|---------|---------------|---------------|---------------|-------|
| **CREATE SEQUENCE** | ✅ Full | ✅ Full | 95% | Full DDL with all sequence options |
| **DROP SEQUENCE** | ✅ Full | ✅ Full | 95% | DROP SEQUENCE [IF EXISTS] |
| **ALTER SEQUENCE** | ✅ Full | ✅ Full | 90% | RESTART, RESTART WITH, and all options |
| **Sequence options** | ✅ Full | ✅ Full | 95% | START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CACHE, CYCLE, NOCACHE, NOCYCLE, RESTART, RESTART WITH |
| **FOR SYSTEM_TIME AS OF** | ✅ Full | ✅ Full | 95% | Point-in-time query on system-versioned tables |
| **FOR SYSTEM_TIME BETWEEN** | ✅ Full | ✅ Full | 95% | Range query on system-versioned tables |
| **FOR SYSTEM_TIME FROM/TO** | ✅ Full | ✅ Full | 95% | Range query (inclusive/exclusive) |
| **FOR SYSTEM_TIME ALL** | ✅ Full | ✅ Full | 95% | All rows including historical |
| **WITH SYSTEM VERSIONING** | ✅ Full | ✅ Full | 90% | CREATE TABLE ... WITH SYSTEM VERSIONING |
| **PERIOD FOR** | ✅ Full | ✅ Full | 85% | Application-time period definitions |
| **CONNECT BY** | ✅ Full | ✅ Full | 90% | Hierarchical queries with PRIOR and NOCYCLE |
| **START WITH (CONNECT BY)** | ✅ Full | ✅ Full | 90% | Root condition for hierarchical traversal |
| **PRIOR operator** | ✅ Full | ✅ Full | 90% | Reference parent row in CONNECT BY |
| **NOCYCLE** | ✅ Full | ✅ Full | 85% | Prevent infinite loops in cyclic graphs |

### SQL Server-Specific Features

| Feature | Support Level | GoSQLX Parser | Test Coverage | Notes |
|---------|---------------|---------------|---------------|-------|
| **MERGE** | ✅ Full | ✅ Full | 95% | MERGE statements with WHEN clauses |
| **PIVOT/UNPIVOT** | ✅ Full | ✅ Full | — | Parsed and rendered since v1.14.0 (`FROM s PIVOT (...) AS p`). Also parsed for Oracle, Snowflake and BigQuery: PIVOT/UNPIVOT is gated on the dialect capability `SupportsPivotUnpivot` |
| **CROSS/OUTER APPLY** | ✅ Full | 🔧 Syntax | 10% | Keywords reserved, no parsing logic |
| **HierarchyID** | ✅ Full | 🔧 Syntax | 20% | Data type recognition |
| **T-SQL functions** | ✅ Full | ⚠️ Partial | 40% | Subset of T-SQL functions |

### Oracle-Specific Features

| Feature | Support Level | GoSQLX Parser | Test Coverage | Notes |
|---------|---------------|---------------|---------------|-------|
| **CONNECT BY** | ✅ Full | ✅ Full | — | `START WITH`, `CONNECT BY [NOCYCLE]` with general expressions |
| **PRIOR operator** | ✅ Full | ✅ Full | — | Prefix operator (`CONNECT BY PRIOR id = parent_id + 1`); a column named `prior` stays an identifier |
| **DECODE function** | ✅ Full | ⚠️ Partial | 40% | Recognized as function, no special handling |
| **NVL/NVL2** | ✅ Full | ⚠️ Partial | 40% | Recognized as function, no special handling |
| **Dual table** | ✅ Full | ✅ Full | 100% | Standard table reference |
| **Bind variables** (`:1`, `:name`) | ✅ Full | ✅ Full | 90% | Positional and named binds tokenized as placeholders (dialect-gated). In Oracle mode `:` followed by a digit or identifier is always a bind, so array slices such as `arr[1:2]` and `arr[1:n]` do not parse (`expected ], got PLACEHOLDER`); Oracle has no array slices, and `arr[:2]` still parses |
| **Analytic functions** | ✅ Full | ⚠️ Partial | 60% | Subset via window functions |

### SQLite-Specific Features

| Feature | Support Level | GoSQLX Parser | Test Coverage | Notes |
|---------|---------------|---------------|---------------|-------|
| **PRAGMA** | ✅ Full | ✅ Full | 90% | Bare, arg, and assignment forms fully parsed |
| **ATTACH/DETACH** | ✅ Full | 🔧 Syntax | 10% | Keywords reserved, no parsing logic |
| **Type affinity** | ✅ Full | ⚠️ Partial | 30% | Flexible typing |
| **WITHOUT ROWID** | ✅ Full | ✅ Full | 85% | CREATE TABLE option fully supported |
| **Simplified syntax** | ✅ Full | ✅ Full | 85% | SQLite variations |

### Statement syntax

These forms are available in v1.17.0. Each example was parsed with
`gosqlx.ParseWithDialect` and formatted with `gosqlx.FormatWithDialect` in the
named dialect.

| Syntax | Dialects | GoSQLX Parser | Notes |
|--------|----------|---------------|-------|
| **Multi-table UPDATE** | MySQL, MariaDB | ✅ Full | `UPDATE a JOIN b ON a.id = b.id SET a.x = b.x`; joins and extra targets live in `UpdateStatement.Extras` |
| **Multi-table DELETE** | MySQL, MariaDB | ✅ Full | `DELETE a, b FROM a JOIN b ON a.id = b.id WHERE ...`; `DeleteStatement.Extras` |
| **UPDATE / DELETE TOP** | SQL Server | ✅ Full | `UPDATE TOP (10) t SET ...`, `DELETE TOP (5) FROM t ...` |
| **OUTPUT clause** | SQL Server | ✅ Full | `UPDATE ... OUTPUT inserted.id WHERE ...`, `DELETE ... OUTPUT deleted.id` |
| **GO [n] batch separator** | SQL Server | ✅ Full | Kept as `ast.BatchSeparator` with its optional `RepeatCount`; repeated batches are not expanded. `SQL()` and `FormatAST` keep GO on its own line. Statement counts reported by the CLI, the MCP server and the C binding exclude GO, but `AST.Statements` contains a `BatchSeparator` node for each GO. A `GO` line inside a statement is an error. Transpiling a GO script to another dialect returns an error |
| **RETURNING ... INTO** | Oracle | ✅ Full | `UPDATE t SET x = 1 WHERE id = 2 RETURNING x INTO :v` |
| **ARRAY JOIN / LIMIT BY** | ClickHouse | ✅ Full | `SELECT a FROM t ARRAY JOIN arr AS x LIMIT 1 BY a`. Parsed since earlier releases; AST walkers (`ast.Inspect`, `Walk`, security scanner, linter, table extraction) now reach `ARRAY JOIN` |
| **MERGE with a VALUES source** | PostgreSQL, SQL Server | ✅ Full | `MERGE INTO t USING (VALUES (1, 'a')) AS s (id, val) ON ...`; the source's column alias list is kept when rendering |
| **CREATE TABLE ... CLONE ... AT** | Snowflake | ✅ Full | `CREATE TABLE t2 CLONE t1 AT (TIMESTAMP => ...)`; walkers now reach the clone clause |
| **CREATE / ALTER / DROP SEQUENCE** | PostgreSQL, Oracle, Snowflake | ⚠️ Partial | Parsed and kept as statement text (`UtilityStatement`); `AST.SQL()` no longer drops them. MariaDB has a full `CreateSequenceStatement` (see MariaDB-Specific Features) |
| **EXPLAIN** | PostgreSQL, MySQL, SQLite | ✅ Full | `ExplainStatement` now retains the explained statement as an AST (`EXPLAIN ANALYZE ...`, SQLite `EXPLAIN QUERY PLAN ...`) |
| **SQL\*Plus `/` line** | Oracle | ✅ Full | A line holding only `/` ends a statement in the parser and in `SplitStatements`; `/` with other SQL on the line is still division. Known gap: formatting drops the `/` lines |
| **`<=>` operator** | MySQL, MariaDB, PostgreSQL | ✅ Full | Null-safe equality (MySQL family) and pgvector distance (PostgreSQL). Accepted only in these dialects and rejected in the others; v1.16.0 accepted `<=>` in no dialect |
| **`==` operator** | SQLite, ClickHouse | ✅ Full | Accepted only in these dialects and rejected in the others; v1.16.0 accepted `==` in no dialect |

Related grammar checks: outside SQL Server, a utility statement
(`SET`, `USE`, `GRANT`, `COPY`, `CALL`, ...) followed on the next line by
another statement without `;` is an error (E2002); transaction statements are
checked against each dialect's grammar (`BEGIN banana` is rejected); an
unbalanced SQL Server `BEGIN ... END` block is an error.

## SQL Standards Compliance

### SQL-92 (Entry Level)

| Feature Category | Support Level | Test Coverage | Notes |
|------------------|---------------|---------------|-------|
| **Basic data types** | ✅ Full | 100% | CHAR, VARCHAR, INTEGER, etc. |
| **Basic predicates** | ✅ Full | 100% | =, <>, <, >, <=, >= |
| **Basic expressions** | ✅ Full | 100% | Arithmetic, string operations |
| **Subqueries** | ✅ Full | 100% | WHERE and HAVING subqueries |
| **Aggregate functions** | ✅ Full | 100% | COUNT, SUM, AVG, MIN, MAX |
| **Joins** | ✅ Full | 95% | Inner and outer joins |
| **UNION** | ✅ Full | 100% | Set operations |

### SQL-99 (Core Features)

| Feature Category | Support Level | Test Coverage | Notes |
|------------------|---------------|---------------|-------|
| **Regular expressions** | ⚠️ Partial | 60% | SIMILAR TO operator |
| **Array types** | ⚠️ Partial | 40% | Limited array support |
| **Common Table Expressions** | ✅ Full | 100% | WITH clause |
| **Window functions** | ✅ Full | 95% | OVER clause |
| **CASE expressions** | ✅ Full | 100% | Simple and searched CASE |
| **Recursive queries** | ✅ Full | 100% | Recursive CTEs |

### SQL-2003 (XML Features)

| Feature Category | Support Level | Test Coverage | Notes |
|------------------|---------------|---------------|-------|
| **Window functions** | ✅ Full | 100% | Enhanced window support |
| **MERGE statement** | ✅ Full | 80% | UPSERT operations |
| **Object identifiers** | ✅ Full | 90% | Standardized identifiers |
| **XML data type** | 🔧 Syntax | 30% | Basic syntax recognition |
| **XML functions** | ❌ Not Supported | 0% | XMLQuery, XMLTable, etc. |

### SQL-2006 (Enhancement)

| Feature Category | Support Level | Test Coverage | Notes |
|------------------|---------------|---------------|-------|
| **Enhanced window functions** | ✅ Full | 95% | Additional frame options |
| **More built-in functions** | ⚠️ Partial | 70% | Subset of new functions |
| **IMPORT/EXPORT** | ❌ Not Applicable | 0% | Not relevant for parser |

### SQL-2008 (Enhancements)

| Feature Category | Support Level | Test Coverage | Notes |
|------------------|---------------|---------------|-------|
| **INSTEAD OF triggers** | 🔧 Syntax | 50% | Syntax recognition only |
| **Enhanced MERGE** | ✅ Full | 80% | Extended MERGE capabilities |
| **TRUNCATE statement** | ✅ Full | 95% | Full TRUNCATE support with CASCADE |
| **FETCH FIRST/NEXT** | ✅ Full | 95% | Standard row limiting (F861/F862) |

### SQL-2011 (Temporal Data)

| Feature Category | Support Level | Test Coverage | Notes |
|------------------|---------------|---------------|-------|
| **Temporal tables** | 🔧 Syntax | 30% | FOR SYSTEM_TIME syntax |
| **Window function enhancements** | ⚠️ Partial | 60% | Some new functions |

### SQL-2016 (JSON Support)

| Feature Category | Support Level | Test Coverage | Notes |
|------------------|---------------|---------------|-------|
| **JSON data type** | 🔧 Syntax | 40% | Type recognition |
| **JSON functions** | ⚠️ Partial | 20% | Limited function support |
| **Row pattern recognition** | ❌ Not Supported | 0% | MATCH_RECOGNIZE clause |

## PostgreSQL Extension Summary

GoSQLX provides comprehensive PostgreSQL-specific feature support, making it one of the most PostgreSQL-compatible SQL parsers available.

### Complete PostgreSQL Feature Set

| Feature Category | Features Included | Support Level | Use Cases |
|------------------|-------------------|---------------|-----------|
| **JSON/JSONB** | All 10 operators (`->`, `->>`, `#>`, `#>>`, `@>`, `<@`, `?`, `?|`, `?&`, `#-`) | ✅ Full | Modern web apps, document stores, API backends |
| **LATERAL Joins** | LEFT LATERAL, INNER LATERAL, CROSS LATERAL | ✅ Full | Correlated subqueries, row-level computations |
| **DISTINCT ON** | SELECT DISTINCT ON with ORDER BY | ✅ Full | Deduplication, first/last row selection |
| **FILTER Clause** | Conditional aggregation on all aggregates | ✅ Full | Multi-condition analytics in single query |
| **Aggregate ORDER BY** | Ordering within aggregate functions | ✅ Full | String concatenation, array aggregation |
| **RETURNING** | INSERT/UPDATE/DELETE RETURNING | ✅ Full | Audit trails, single-trip operations |

### JSON/JSONB Operator Examples

```sql
-- Extract field as JSON (->)
SELECT data->'user' FROM events;

-- Extract field as text (->>)
SELECT data->>'email' FROM users;

-- Extract nested path as JSON (#>)
SELECT data#>'{user,address,city}' FROM profiles;

-- Extract nested path as text (#>>)
SELECT data#>>'{user,name}' FROM profiles;

-- Contains (@>)
SELECT * FROM products WHERE attributes @> '{"color": "red"}';

-- Contained by (<@)
SELECT * FROM users WHERE tags <@ '["admin", "user"]';

-- Key exists (?)
SELECT * FROM profiles WHERE data ? 'email';

-- Any key exists (?|)
SELECT * FROM users WHERE profile ?| array['phone', 'mobile'];

-- All keys exist (?&)
SELECT * FROM users WHERE profile ?& array['name', 'email'];

-- Delete path (#-)
SELECT data #- '{user,temp}' FROM cache;
```

### LATERAL JOIN Examples

```sql
-- Correlated subquery in FROM clause
SELECT u.name, recent.order_date
FROM users u,
LATERAL (
    SELECT order_date FROM orders
    WHERE user_id = u.id
    ORDER BY order_date DESC
    LIMIT 3
) recent;

-- LEFT LATERAL JOIN
SELECT u.name, stats.total
FROM users u
LEFT JOIN LATERAL (
    SELECT SUM(amount) as total
    FROM transactions
    WHERE user_id = u.id
) stats ON true;
```

### DISTINCT ON Examples

```sql
-- Get first row per department
SELECT DISTINCT ON (dept_id) dept_id, name, salary
FROM employees
ORDER BY dept_id, salary DESC;

-- Latest status per user
SELECT DISTINCT ON (user_id) user_id, status, updated_at
FROM user_status_log
ORDER BY user_id, updated_at DESC;
```

### FILTER Clause Examples

```sql
-- Multi-condition aggregation
SELECT
    dept_id,
    COUNT(*) FILTER (WHERE status = 'active') AS active_count,
    COUNT(*) FILTER (WHERE status = 'inactive') AS inactive_count,
    SUM(salary) FILTER (WHERE bonus_eligible = true) AS bonus_pool
FROM employees
GROUP BY dept_id;
```

### RETURNING Clause Examples

```sql
-- INSERT with RETURNING
INSERT INTO users (name, email)
VALUES ('John Doe', 'john@example.com')
RETURNING id, created_at;

-- UPDATE with RETURNING
UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics'
RETURNING id, name, price;

-- DELETE with RETURNING
DELETE FROM sessions
WHERE expired_at < NOW()
RETURNING user_id, session_id;
```

## Dialect Mode Engine

GoSQLX provides a first-class dialect mode engine that threads the SQL dialect through the tokenizer and parser. This enables dialect-specific keyword recognition, syntax parsing, and validation.

### Supported Dialects

| Dialect | Dialect String | Keyword Set | Dialect-Specific Parsing | Status |
|---------|---------------|-------------|--------------------------|--------|
| **PostgreSQL** | `"postgresql"` | Full PG keywords | `::`, `ON CONFLICT`, `$$`, JSONB ops, LATERAL, DISTINCT ON | ✅ Default dialect |
| **MySQL** | `"mysql"` | MySQL keywords | SHOW, DESCRIBE, REPLACE INTO, ON DUPLICATE KEY, LIMIT n,m, GROUP_CONCAT, MATCH AGAINST, REGEXP, `\|\|` as logical OR | ✅ Full support |
| **SQL Server** | `"sqlserver"` | T-SQL keywords | MERGE, bracket identifiers `[col]`, PIVOT/UNPIVOT | ✅ PIVOT/UNPIVOT (since v1.14.0) |
| **Oracle** | `"oracle"` | Oracle keywords | DUAL table, basic PL/SQL keywords, MINUS as EXCEPT, `:1`/`:name` binds, CONNECT BY / START WITH / PRIOR | ⚠️ Keywords + basic parsing |
| **SQLite** | `"sqlite"` | SQLite keywords | Flexible typing, simplified syntax | ⚠️ Keywords + basic parsing |
| **Snowflake** | `"snowflake"` | Snowflake keywords | MATCH_RECOGNIZE, @stage, SAMPLE, QUALIFY, VARIANT paths, time-travel, MINUS, LATERAL FLATTEN, TRY_CAST, IGNORE/RESPECT NULLS, LIKE ANY/ALL, CREATE STAGE/STREAM/TASK/PIPE stubs | ✅ 87/87 QA pass |
| **ClickHouse** | `"clickhouse"` | ClickHouse keywords | PREWHERE, FINAL, GLOBAL IN/NOT IN, nested types, parametric aggregates, bare-bracket arrays, ORDER BY WITH FILL, CODEC, WITH TOTALS, LIMIT BY, ANY/ALL JOIN, SETTINGS, TTL, INSERT FORMAT | ✅ 83/83 QA pass (was 69/83 in v1.14.0) |
| **MariaDB** | `"mariadb"` | MariaDB keywords (superset of MySQL) | All MySQL features + SEQUENCE DDL, FOR SYSTEM_TIME, WITH SYSTEM VERSIONING, PERIOD FOR, CONNECT BY | ✅ |
| **BigQuery** | `"bigquery"` | BigQuery keywords | Composite field access `(expr).field`, table functions, QUALIFY, PIVOT/UNPIVOT | ⚠️ Keywords + basic parsing |
| **Redshift** | `"redshift"` | Redshift keywords | Keyword recognition | ⚠️ Keywords + basic parsing |
| **Generic** | `"generic"` | Standard SQL keywords | No dialect-specific syntax; composite field access allowed | ✅ |

### Usage

```go
// API
ast, err := parser.ParseWithDialect("SHOW TABLES", "mysql")
err = parser.ValidateWithDialect("DESCRIBE users", "mysql")

// CLI
gosqlx validate --dialect mysql "SHOW TABLES"
gosqlx format --dialect mysql query.sql
```

### Known Gaps by Dialect

#### PostgreSQL (default, best supported)
- PL/pgSQL procedural blocks not parsed
- Some advanced array operations limited
- Full-text search `tsvector`/`tsquery` syntax-only recognition

#### MySQL
- Stored procedures / functions not parsed
- HANDLER statements not supported
- XA transactions not supported
- CREATE EVENT not supported

#### SQL Server (T-SQL)
- PIVOT/UNPIVOT parsed (since v1.14.0)
- CROSS/OUTER APPLY keywords reserved but no parsing logic
- TRY/CATCH blocks not supported
- OPENROWSET / OPENQUERY not supported

#### Oracle
- CONNECT BY / START WITH / PRIOR parsed (PRIOR is a prefix operator, since v1.16.0)
- PL/SQL blocks not supported
- DECODE recognized as generic function only
- Pipelined table functions not supported

#### SQLite
- ATTACH/DETACH not parsed (keywords reserved)
- VACUUM not supported
- Virtual tables (FTS5, rtree) not supported

#### Snowflake
- 87/87 QA corpus pass (100%)
- MATCH_RECOGNIZE (SQL:2016 R010), @stage references, SAMPLE/TABLESAMPLE, QUALIFY
- VARIANT colon-path expressions (`expr:field.sub[0]`)
- Time-travel: AT/BEFORE/CHANGES
- MINUS as EXCEPT synonym, LATERAL FLATTEN, named arguments (`name => expr`)
- TRY_CAST, IGNORE NULLS / RESPECT NULLS
- LIKE ANY/ALL and ILIKE ANY/ALL
- CREATE STAGE/STREAM/TASK/PIPE stubs (parsed but not semantically validated)
- USE [WAREHOUSE|DATABASE|SCHEMA|ROLE], DESCRIBE with object-kind prefixes
- COPY INTO / PUT / GET / LIST / REMOVE stubs

#### MariaDB
- Inherits all MySQL known gaps (stored procedures, HANDLER, XA transactions, CREATE EVENT)
- JSON_TABLE not supported
- Spider storage engine syntax not parsed
- ColumnStore-specific syntax not supported

#### ClickHouse
- 83/83 QA corpus pass (100%). It closed the remaining QA gaps: `ARRAY JOIN` / `LEFT ARRAY JOIN`, scalar CTEs (`WITH <expr> AS <name>`), `CREATE MATERIALIZED VIEW` with `ENGINE`/`TO`, `CREATE TABLE ... AS <source> ENGINE = ...`, numeric `SAMPLE` arguments with `OFFSET`, and `LIMIT offset, count`
- PREWHERE clause for pre-filter optimization before primary key scan
- FINAL modifier on table references (forces MergeTree part merge)
- GLOBAL IN / GLOBAL NOT IN for distributed query execution
- ClickHouse data types: FixedString(N), LowCardinality(T), Nullable(T), Array(T), Map(K,V), DateTime64, IPv4, IPv6
- MergeTree engine family keywords: MERGETREE, REPLACINGMERGETREE, AGGREGATINGMERGETREE, SUMMINGMERGETREE, COLLAPSINGMERGETREE, VERSIONEDCOLLAPSINGMERGETREE
- Nested column types, parametric aggregates (`quantile(0.95)(duration)`)
- Bare-bracket array literals `[1,2,3]`, ORDER BY WITH FILL, CODEC(...)
- WITH TOTALS in GROUP BY, LIMIT BY clause, ANY/ALL JOIN strictness
- SETTINGS, TTL, INSERT FORMAT (JSONEachRow, CSV, etc.)
- `table`, `partition`, `tables`, `databases` as identifiers
- Table functions in FROM: `numbers(N)`, `remote(...)`, `file(...)`, `url(...)`, `merge(...)`, `s3(...)`, `values(...)`, etc.
- 30+ ClickHouse-specific keywords: TTL, CODEC, FORMAT, SETTINGS, DISTRIBUTED, etc.

#### BigQuery
- PIVOT / UNPIVOT (#854): aggregate aliases and multiple aggregates (`PIVOT (SUM(s) AS total, COUNT(*) AS n FOR q IN ('Q1' AS q1, 'Q2' q2))`), `UNPIVOT INCLUDE NULLS` / `EXCLUDE NULLS`, row-value aliases with or without `AS` (string or INT64), and multi-column UNPIVOT (`UNPIVOT ((a, b) FOR k IN ((c1, c2) AS 'x', (c3, c4) AS 'y'))`)
- PIVOT `IN` values must be literals or names; constant expressions such as `IN (1 + 1)` or `IN (DATE '2024-01-01')` are not parsed
- QUALIFY, `UNNEST` in FROM and `(struct).field` access are capability-gated; BigQuery has no dedicated keyword set
- Quoted PIVOT aggregate aliases and UNPIVOT value-column names retain their source delimiters, including BigQuery backticks and Oracle double quotes

Oracle also accepts aggregate aliases, multiple aggregates, `INCLUDE`/`EXCLUDE NULLS` and multi-column UNPIVOT; Snowflake accepts `INCLUDE`/`EXCLUDE NULLS`. SQL Server keeps the single-aggregate form without aliases or a NULLS modifier. Oracle's tuple row-value aliases (`(c1, c2) AS ('a', 'b')`) are not parsed.

## SQL Standards Compliance Summary

### Overall Compliance

| Standard | Compliance % | Status | Notes |
|----------|--------------|--------|-------|
| **SQL-92 Entry** | ~95% | ✅ Excellent | All core features supported |
| **SQL-92 Intermediate** | ~85% | ✅ Strong | Most features supported |
| **SQL-99 Core** | ~85% | ✅ Strong | Window functions, CTEs, recursive queries |
| **SQL:2003** | ~75% | ✅ Good | MERGE, FILTER, enhanced window functions |
| **SQL:2008** | ~65% | ✅ Good | TRUNCATE, FETCH FIRST/NEXT |
| **SQL:2011** | ~40% | ⚠️ Partial | Some temporal features, limited support |
| **SQL:2016** | ~50% | ⚠️ Partial | JSON support via PostgreSQL extensions |

### Feature Category Compliance

| Category | Features Supported | Total Features | Compliance % |
|----------|-------------------|----------------|--------------|
| **Basic DML** | 18/18 | 18 | 100% |
| **Advanced DML** | 12/15 | 15 | 80% |
| **DDL Operations** | 22/25 | 25 | 88% |
| **JOIN Operations** | 10/10 | 10 | 100% |
| **Subqueries** | 8/8 | 8 | 100% |
| **Aggregate Functions** | 10/13 | 13 | 77% |
| **Window Functions** | 15/16 | 16 | 94% |
| **CTEs** | 7/7 | 7 | 100% |
| **Set Operations** | 4/4 | 4 | 100% |
| **Expression Operators** | 9/9 | 9 | 100% |
| **PostgreSQL Extensions** | 20/25 | 25 | 80% |

### Dialect-Specific Compliance

| Database | Core Features | Extensions | Overall Rating | Notes |
|----------|---------------|------------|----------------|-------|
| **PostgreSQL** | 95% | 85% | ⭐⭐⭐⭐⭐ Excellent | Default dialect, best supported |
| **MySQL** | 95% | 85% | ⭐⭐⭐⭐⭐ Excellent | Full dialect parsing (since v1.8.0) |
| **SQL Server** | 85% | 65% | ⭐⭐⭐⭐ Very Good | Keywords + MERGE |
| **Oracle** | 80% | 60% | ⭐⭐⭐⭐ Good | Keywords + basic features |
| **SQLite** | 85% | 50% | ⭐⭐⭐⭐ Good | Keywords + basic features |
| **MariaDB** | 95% | 90% | ⭐⭐⭐⭐⭐ Excellent | MySQL superset + SEQUENCE DDL, temporal tables, CONNECT BY  |
| **Snowflake** | n/a | n/a | ⭐⭐⭐⭐ Good | 87/87 QA corpus pass; the QA pass rate is not a core/extension percentage |
| **ClickHouse** | n/a | n/a | ⭐⭐⭐⭐ Good | 83/83 QA corpus pass; the QA pass rate is not a core/extension percentage |

## Performance Characteristics by Feature

### High Performance Features (>1M ops/sec)

- Basic SELECT statements
- Simple INSERT/UPDATE/DELETE
- Basic WHERE clauses
- Simple JOINs (2-3 tables)
- Standard aggregate functions
- Basic subqueries

### Good Performance Features (100K-1M ops/sec)

- Complex WHERE clauses
- Multi-table JOINs (4-6 tables)
- Window functions
- Simple CTEs
- Set operations (UNION, etc.)
- Complex subqueries

### Moderate Performance Features (10K-100K ops/sec)

- Recursive CTEs
- Very complex JOINs (7+ tables)
- Deeply nested subqueries
- Complex window functions with frames
- Large CASE expressions

### Lower Performance Features (<10K ops/sec)

- Extremely complex queries (1000+ tokens)
- Deeply nested expressions (10+ levels)
- Very large DDL statements
- Queries with 100+ columns

## Edge Case Support

### Unicode and International Support

| Feature | Support Level | Test Coverage |
|---------|---------------|---------------|
| **Unicode identifiers** | ✅ Full | 95% |
| **Unicode string literals** | ✅ Full | 100% |
| **Multi-byte characters** | ✅ Full | 90% |
| **Right-to-left text** | ✅ Full | 80% |
| **Emoji in comments** | ✅ Full | 70% |

### Extreme Query Patterns

| Feature | Support Level | Test Coverage |
|---------|---------------|---------------|
| **Very long queries (50K+ chars)** | ✅ Full | 80% |
| **Deeply nested subqueries (10+ levels)** | ✅ Full | 85% |
| **Large column lists (100+ columns)** | ✅ Full | 75% |
| **Complex WHERE clauses (50+ conditions)** | ✅ Full | 80% |
| **Large IN lists (1000+ values)** | ✅ Full | 70% |

### Error Recovery

| Scenario | Support Level | Test Coverage |
|----------|---------------|---------------|
| **Syntax errors with context** | ✅ Full | 95% |
| **Incomplete queries** | ✅ Full | 90% |
| **Invalid token sequences** | ✅ Full | 85% |
| **Unmatched parentheses** | ✅ Full | 100% |
| **Invalid string literals** | ✅ Full | 95% |

## Production Readiness Summary

### Ready for Production

**Core DML/DDL**:
- **Core SQL operations** (SELECT, INSERT, UPDATE, DELETE, TRUNCATE)
- **Standard joins and subqueries** (all types including LATERAL)
- **Window functions and CTEs** (including recursive and materialized hints)
- **MERGE statements** (SQL:2003 F312)
- **GROUPING SETS, ROLLUP, CUBE** (SQL-99 T431)
- **Materialized views**
- **Table partitioning**

**PostgreSQL Extensions** (since v1.6.0):
- **JSON/JSONB operators** - All 10 operators (`->`, `->>`, `#>`, `#>>`, `@>`, `<@`, `?`, `?|`, `?&`, `#-`)
- **LATERAL JOIN** - Full support with LEFT/INNER/CROSS variants
- **DISTINCT ON** - PostgreSQL-specific row selection
- **FILTER clause** - Conditional aggregation
- **Aggregate ORDER BY** - Ordering within aggregate functions
- **RETURNING clause** - INSERT/UPDATE/DELETE RETURNING

**Standards & Performance**:
- **FETCH FIRST/NEXT** - SQL-99 F861/F862 standard pagination
- **OFFSET-FETCH** - Standard row limiting
- **Multi-dialect basic syntax**
- **Unicode and international text**
- **High-performance scenarios** (1.38M+ ops/sec, 1.5M peak)

### Suitable with Considerations

- **Advanced dialect-specific features** (keyword recognition only for: CROSS/OUTER APPLY, ATTACH/DETACH)
- **Complex XML operations** (syntax recognition only)
- **Dialect-specific functions** (DECODE, NVL, recognized as generic functions)
- **Newest SQL standard features (SQL-2011+)**
- **Very large query processing**

### Development Needed

- **Full XML function support**
- **Row pattern recognition (MATCH_RECOGNIZE)**
- **Complete temporal table support**
- **Advanced array operations**

## Recommendations

### For Web Applications
- ✅ **Excellent support** for typical web app queries
- ✅ **High performance** for user authentication, content management
- ✅ **Multi-dialect compatibility** for different backends
- ✅ **PostgreSQL JSON/JSONB support** for modern document storage
- ✅ **RETURNING clause** for efficient single-trip operations

### For Analytics Platforms
- ✅ **Strong support** for complex analytical queries
- ✅ **Full CTE and window function support**
- ✅ **GROUPING SETS, ROLLUP, CUBE** for OLAP operations
- ✅ **FILTER clause** for conditional aggregation
- ⚠️ **Consider dialect-specific features** for advanced analytics

### For PostgreSQL Applications
- ✅ **Industry-leading PostgreSQL support** with 95% core feature coverage
- ✅ **Complete JSON/JSONB operator support** (10 operators)
- ✅ **LATERAL JOIN** for advanced correlated subqueries
- ✅ **DISTINCT ON** for PostgreSQL-specific deduplication
- ✅ **Aggregate ORDER BY** for string aggregation
- ✅ **Best-in-class PostgreSQL compatibility**

### For Database Tools
- ✅ **Comprehensive DDL support**
- ✅ **Excellent error handling and recovery**
- ✅ **Multi-dialect parsing capabilities**
- ✅ **SQL injection detection** built-in

### For Migration Tools
- ✅ **Strong cross-dialect compatibility**
- ✅ **Robust error handling**
- ✅ **PostgreSQL extension awareness**
- ⚠️ **Manual handling needed** for dialect-specific features (PIVOT, CONNECT BY)

---

**Total Test Cases**: 800+
**Coverage Percentage**: 95%+
**SQL-99 Compliance**: ~85%
**PostgreSQL Compliance**: ~95% (core features), ~85% (extensions)
**MySQL Compliance**: ~95% (core features), ~85% (extensions)