GoSQLX SQL Feature Compatibility Matrix

Version: v1.10.0 | Last Updated: 2026-03-13

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.

Recent Additions (v1.9.0)

• ✅ 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)

Previous Additions (v1.8.0)

• ✅ 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

Previous Additions (v1.7.0)

• ✅ 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
  • 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
• ✅ FOR UPDATE/SHARE - Row-level locking clauses
• ✅ Multi-row INSERT - INSERT INTO t VALUES (1), (2), (3) batch inserts

Previous Additions (v1.6.0)

• ✅ PostgreSQL Extensions:
  • LATERAL JOIN - Correlated subqueries in FROM clause
  • 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

Previous Additions (v1.4+)

• ✅ 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)

Data Definition Language (DDL)

JOIN Operations

Subqueries

Aggregate Functions

Advanced SQL Features

Common Table Expressions (CTEs)

Window Functions

Set Operations

Grouping Operations (SQL-99 T431)

ORDER BY Extensions (SQL-99 F851)

Expression Operators

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

MySQL-Specific Features (Enhanced in v1.8.0)

SQL Server-Specific Features

Oracle-Specific Features

SQLite-Specific Features

SQL Standards Compliance

SQL-92 (Entry Level)

SQL-99 (Core Features)

SQL-2003 (XML Features)

SQL-2006 (Enhancement)

SQL-2008 (Enhancements)

SQL-2011 (Temporal Data)

SQL-2016 (JSON Support)

v1.6.0 PostgreSQL Extension Summary

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

Complete PostgreSQL Feature Set

JSON/JSONB Operator Examples

-- 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

-- 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

-- 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

-- 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

-- 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 (v1.8.0)

GoSQLX v1.8.0 introduces 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

Usage

// 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 keywords reserved but no parsing logic
• CROSS/OUTER APPLY keywords reserved but no parsing logic
• TRY/CATCH blocks not supported
• OPENROWSET / OPENQUERY not supported

Oracle

• CONNECT BY / START WITH / PRIOR not parsed (keywords reserved)
• PL/SQL blocks not supported
• DECODE recognized as generic function only
• Pipelined table functions not supported

SQLite

• PRAGMA statements not parsed (keyword reserved)
• ATTACH/DETACH not parsed (keywords reserved)
• VACUUM not supported
• Virtual tables (FTS5, rtree) not supported

Snowflake

• Keyword detection and dialect scoring only
• No Snowflake-specific parsing (stages, COPY INTO, VARIANT operations)
• QUALIFY clause not supported

SQL Standards Compliance Summary

Overall Compliance (v1.10.0)

Feature Category Compliance

Dialect-Specific Compliance

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

Extreme Query Patterns

Error Recovery

Production Readiness Summary

Ready for Production (v1.7.0)

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 (v1.6.0-v1.7.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.25M ops/sec peak)

Suitable with Considerations

• Advanced dialect-specific features (keyword recognition only for: PIVOT/UNPIVOT, CONNECT BY, PRAGMA, 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

• PIVOT/UNPIVOT parsing logic (keywords reserved)
• CONNECT BY hierarchical queries (keywords reserved)
• Full XML function support
• Row pattern recognition (MATCH_RECOGNIZE)
• Complete temporal table support
• SQLite PRAGMA statements (keywords reserved)
• 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)

Last Updated: 2026-03-13 GoSQLX Version: 1.10.0 Test Suite Version: 1.10.0 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)

Quick Reference: What’s New in v1.9.0

SQLite Enhancements

1. PRAGMA - Fully parsed: bare (PRAGMA x), arg (PRAGMA x(n)), assignment (PRAGMA x=v)
2. WITHOUT ROWID - CREATE TABLE ... WITHOUT ROWID fully supported
3. Reserved keywords as DDL column names - No longer cause parse errors

Security Scanner

1. Tautology detection - 1=1, 'a'='a', col=col, OR TRUE → CRITICAL (PatternTautology)
2. UNION split - PatternUnionInjection (CRITICAL) vs PatternUnionGeneric (HIGH)

Parser Fixes

1. KEY/INDEX/VIEW in qualified identifiers (e.g., a.key)
2. NATURAL JOIN - type stored as "NATURAL" not "NATURAL INNER"
3. OVER window_name - bare window name reference (SQL:2003 §7.11)
4. MySQL backtick + SQL Server bracket reserved-word identifiers

Error Codes

1. E1009 - ErrCodeUnterminatedBlockComment for unterminated /* ... */

Quick Reference: What’s New in v1.8.0

Dialect Engine

1. ParseWithDialect() - Parse SQL with dialect-specific syntax
2. ValidateWithDialect() - Validate with dialect awareness
3. —dialect CLI flag - Specify dialect for CLI commands
4. 6 Supported Dialects - PostgreSQL, MySQL, SQL Server, Oracle, SQLite, Snowflake

MySQL Syntax (11 Features)

1. SHOW statements - SHOW TABLES, DATABASES, CREATE TABLE
2. DESCRIBE/EXPLAIN - Table description
3. REPLACE INTO - Insert-or-replace
4. ON DUPLICATE KEY UPDATE - MySQL upsert
5. LIMIT offset, count - MySQL-style pagination
6. UPDATE/DELETE with LIMIT - Row limiting extension
7. GROUP_CONCAT - With ORDER BY and SEPARATOR
8. MATCH AGAINST - Full-text search
9. REGEXP/RLIKE - Regular expression operators
10. INTERVAL number unit - MySQL-style intervals
11. IF()/REPLACE() as functions - Keywords as function names

New Capabilities

1. Query Transform API - pkg/transform/ for programmatic SQL rewriting
2. Comment Preservation - Comments survive parse-format round-trips
3. AST-to-SQL - SQL() methods on all nodes for roundtrip serialization
4. WASM Playground - Browser-based SQL parsing and formatting
5. Error Recovery - ParseWithRecovery() for multi-error diagnostics
6. Dollar-Quoted Strings - PostgreSQL $$body$$ support
7. ~50% Faster Parsing - O(1) integer token comparison

Migration Notes

• From v1.7.0: High-level API (gosqlx.Parse(), gosqlx.Validate()) is fully backward compatible
• Breaking: token.Token.ModelType renamed to Type; string-based token.Type removed. See MIGRATION.md
• MySQL Users: Use parser.ParseWithDialect(sql, "mysql") for MySQL-specific syntax
• Performance: ~50% faster parsing from token type overhaul