feat(dialect): add MariaDB SQL dialect with SEQUENCE, temporal tables, and CONNECT BY

[ajitpratap0]

Summary

Adds MariaDB as a fully-supported SQL dialect (--dialect mariadb) extending MySQL with three MariaDB-specific feature areas: SEQUENCE DDL, temporal tables, and CONNECT BY hierarchical queries.

What's New

MariaDB Dialect (DialectMariaDB)

• Inherits all MySQL keywords and parsing rules
• Auto-detected from SQL content (NEXTVAL, VERSIONING, FOR SYSTEM_TIME, CONNECT BY hints)
• Registered in validDialects, supported in CLI and high-level API

SEQUENCE DDL

CREATE SEQUENCE s START WITH 1000 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999 CACHE 20 CYCLE;
CREATE OR REPLACE SEQUENCE s NOCACHE NOCYCLE;
CREATE SEQUENCE IF NOT EXISTS s START WITH 1;
DROP SEQUENCE IF EXISTS s;
ALTER SEQUENCE s RESTART WITH 5000;
ALTER SEQUENCE s RESTART;

Full option set: START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CACHE, CYCLE, NOCACHE, NOCYCLE, RESTART, RESTART WITH.

Temporal Tables

-- Versioned table definition
CREATE TABLE t (
    start_time DATETIME(6) GENERATED ALWAYS AS ROW START,
    end_time   DATETIME(6) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(start_time, end_time)
) WITH SYSTEM VERSIONING;

-- Time travel queries
SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP '2023-01-01 00:00:00';
SELECT * FROM t FOR SYSTEM_TIME ALL;
SELECT * FROM t FOR SYSTEM_TIME BETWEEN TIMESTAMP '2023-01-01' AND TIMESTAMP '2023-12-31';
SELECT * FROM t FOR SYSTEM_TIME FROM TIMESTAMP '2023-01-01' TO TIMESTAMP '2023-12-31';

CONNECT BY Hierarchical Queries

SELECT id, name FROM employees
START WITH parent_id IS NULL
CONNECT BY NOCYCLE PRIOR id = parent_id;

Supports PRIOR prefix operator, START WITH, and NOCYCLE.

Changes

Layer	Files	What
Keywords	keywords/dialect.go, keywords/mariadb.go, keywords/keywords.go	DialectMariaDB constant, 17 MariaDB-specific keywords, MySQL inheritance
Detection	keywords/detect.go	10 MariaDB auto-detection hints (NEXTVAL weight 5, CONNECT BY weight 3)
AST	ast/ast.go, ast/sql.go, ast/pool.go	CreateSequenceStatement, DropSequenceStatement, AlterSequenceStatement, ForSystemTimeClause, PeriodDefinition, ConnectByClause; extended TableReference and SelectStatement
Parser	parser/mariadb.go (new, ~390 lines)	All MariaDB-specific parse functions, dialect-gated
Parser hooks	parser/parser.go, parser/ddl.go, parser/select.go, parser/select_subquery.go	Dispatch points for MariaDB features, guarded by p.isMariaDB()
Config	config/config.go, cmd/validate.go	"mariadb" in validDialects
Tests	keywords/mariadb_test.go, ast/ast_sequence_test.go, parser/mariadb_test.go	35+ test cases
Test data	parser/testdata/mariadb/*.sql	4 SQL files (sequences, temporal, connect_by, mixed)
Docs	docs/SQL_COMPATIBILITY.md, CHANGELOG.md	MariaDB dialect section and changelog entry

Test Coverage

• 35+ unit tests across keywords, AST, and parser layers
• File-based integration test (TestMariaDB_SQLFiles) parses every statement in testdata/mariadb/
• Zero race conditions — passes go test -race ./...
• Performance unchanged — 1.38M+ ops/sec sustained

Design Decisions

• MySQL superset: loads MYSQL_SPECIFIC keywords first, then MARIADB_SPECIFIC — ensures full MySQL compatibility
• Dialect-gated parsing: all MariaDB features are guarded by p.isMariaDB() — zero impact on other dialects
• Prior as UnaryOperator: PRIOR col modeled as UnaryExpression{Operator: ast.Prior} — dedicated iota constant, proper AST type
• Oracle disambiguation: both get CONNECT BY weight 3; unambiguous MariaDB features (NEXTVAL, VERSIONING) at weight 5 ensure correct accumulation-based detection

Test Plan

• go test -race ./... — zero races, all packages pass
• go test -v -run TestMariaDB ./pkg/sql/parser/ ./pkg/sql/keywords/
• CLI: gosqlx validate "CREATE SEQUENCE s START WITH 1" --dialect mariadb
• CLI: gosqlx validate "SELECT * FROM t FOR SYSTEM_TIME ALL" --dialect mariadb
• CLI: gosqlx validate "SELECT id FROM emp START WITH id=1 CONNECT BY PRIOR id = parent_id" --dialect mariadb
• go test -bench=. -benchmem ./pkg/sql/parser/ — performance healthy

🤖 Generated with Claude Code

[vercel]

The latest updates on your projects. Learn more about Vercel for GitHub.

Project	Deployment	Actions	Updated (UTC)
gosqlx-website	Ready	Preview, Comment	Mar 25, 2026 7:21am

[github-actions]

Claude finished @ajitpratap0's task —— View job

---

Code Review: MariaDB SQL Dialect Implementation

Review Checklist

• Analyze core architecture and design patterns
• Review AST implementation for new MariaDB features
• Examine parser logic and dialect handling
• Check keyword management and detection
• Assess test coverage and quality
• Review memory management and performance
• Analyze security considerations
• Check adherence to project conventions
• Provide final recommendations

Architecture & Design Patterns ✅

Strengths:

• Excellent architectural coherence: MariaDB properly inherits from MySQL as a superset
• Clean dialect gating: All MariaDB features guarded by p.isMariaDB() checks - zero impact on other dialects
• Proper separation of concerns: Keywords → AST → Parser layers cleanly separated
• Smart disambiguation: MariaDB/Oracle both support CONNECT BY but MariaDB gets higher-weight hints

AST Implementation ✅

Well-structured new types:

• SequenceOptions: Smart pointer-based design distinguishes unset vs zero values
• SystemTimeClauseType: Clean enum for temporal query variants
• ConnectByClause: Proper hierarchical query representation

Parser & Keywords ✅

Strong implementation:

• Robust option parsing: Handles all sequence option combinations properly
• Smart lookahead: Prevents table alias conflicts with isMariaDBClauseStart()
• Excellent dialect detection: High-weight hints (NEXTVAL=5) ensure correct detection

Test Coverage ✅

Comprehensive testing:

• 35+ unit tests covering all feature areas with good edge cases
• File-based integration tests parsing real SQL files
• Safe type assertions using proper two-value form

Security Analysis ✅

No new attack vectors introduced:

• MariaDB features follow same security patterns as existing codebase
• Existing SQL injection detection in pkg/sql/security/ still applies
• SEQUENCE operations properly validate numeric literals
• No dynamic SQL generation in new code

Performance & Memory ✅

Production-ready optimizations:

• Proper object pooling for all new AST types
• Zero-copy design maintained
• No memory leaks - all pools follow established patterns

---

🚨 Issues Found

1. Logic Contradiction in SequenceOptions (Medium Priority)

// pkg/sql/ast/ast.go:1852-1854
Cycle       bool          // CYCLE
NoCycle     bool          // NO CYCLE / NOCYCLE (explicit; default is NO CYCLE)

Problem: Both fields can be true simultaneously, creating invalid state.
Solution: Use enum instead:

type CycleOption int
const (
    CycleUnspecified CycleOption = iota
    CycleBehavior
    NoCycleBehavior
)

2. Missing Validation in Parser (Medium Priority)

The parseSequenceOptions() function doesn't validate contradictory options:

• Both CYCLE and NOCYCLE specified
• Both CACHE n and NOCACHE specified
• Negative INCREMENT BY values

Recommendation: Add validation in parseSequenceOptions() after parsing all options.

3. Inconsistent Error Handling (Low Priority)

In mariadb.go:207, generic fmt.Errorf used instead of structured errors from pkg/errors/:

return opts, fmt.Errorf("unexpected token after NO in SEQUENCE options: %s", sub)

Should use p.expectedError() for consistency.

---

✅ Overall Assessment

This is high-quality implementation that follows GoSQLX patterns excellently:

Strengths:

• Clean architecture with proper dialect isolation
• Comprehensive test coverage (35+ tests)
• Memory-efficient with object pooling
• Zero race conditions (properly gates features)
• Excellent documentation

Ready for production with minor fixes to the validation logic.

📋 Recommendations

1. Fix the SequenceOptions contradiction - use enum for CYCLE behavior
2. Add option validation in parseSequenceOptions()
3. Use structured errors instead of fmt.Errorf
4. Consider adding benchmark tests for MariaDB-specific parsing

[github-actions]

Claude finished @ajitpratap0's task —— View job

---

Code Review: MariaDB SQL Dialect Implementation

Review Checklist

• Analyze core architecture and design patterns
• Review AST implementation for new MariaDB features
• Examine parser logic and dialect handling
• Check keyword management and detection
• Assess test coverage and quality
• Review memory management and performance
• Analyze security considerations
• Check adherence to project conventions
• Provide final recommendations

Architecture & Design Patterns ✅

Strengths:

• Excellent architectural coherence: MariaDB properly inherits from MySQL as a superset
• Clean dialect gating: All MariaDB features guarded by p.isMariaDB() checks - zero impact on other dialects
• Proper separation of concerns: Keywords → AST → Parser layers cleanly separated
• Smart disambiguation: MariaDB/Oracle both support CONNECT BY but MariaDB gets higher-weight hints

AST Implementation ✅

Well-structured new types:

• SequenceOptions: Smart pointer-based design distinguishes unset vs zero values
• SystemTimeClauseType: Clean enum for temporal query variants
• ConnectByClause: Proper hierarchical query representation

Parser & Keywords ✅

Strong implementation:

• Robust option parsing: Handles all sequence option combinations properly
• Smart lookahead: Prevents table alias conflicts with isMariaDBClauseStart()
• Excellent dialect detection: High-weight hints (NEXTVAL=5) ensure correct detection

Test Coverage ✅

Comprehensive testing:

• 35+ unit tests covering all feature areas with good edge cases
• File-based integration tests parsing real SQL files
• Safe type assertions using proper two-value form

Security Analysis ✅

No new attack vectors introduced:

• MariaDB features follow same security patterns as existing codebase
• Existing SQL injection detection in pkg/sql/security/ still applies
• SEQUENCE operations properly validate numeric literals
• No dynamic SQL generation in new code

Performance & Memory ✅

Production-ready optimizations:

• Proper object pooling for all new AST types
• Zero-copy design maintained
• No memory leaks - all pools follow established patterns

---

🚨 Issues Found

1. Logic Contradiction in SequenceOptions (Medium Priority)

// pkg/sql/ast/ast.go:1852-1854
Cycle       bool          // CYCLE
NoCycle     bool          // NO CYCLE / NOCYCLE (explicit; default is NO CYCLE)

Problem: Both fields can be true simultaneously, creating invalid state.
Solution: Use enum instead:

type CycleOption int
const (
    CycleUnspecified CycleOption = iota
    CycleBehavior
    NoCycleBehavior
)

2. Missing Validation in Parser (Medium Priority)

The parseSequenceOptions() function doesn't validate contradictory options:

• Both CYCLE and NOCYCLE specified
• Both CACHE n and NOCACHE specified
• Negative INCREMENT BY values

Recommendation: Add validation in parseSequenceOptions() after parsing all options.

3. Inconsistent Error Handling (Low Priority)

In mariadb.go:207, generic fmt.Errorf used instead of structured errors from pkg/errors/:

return opts, fmt.Errorf("unexpected token after NO in SEQUENCE options: %s", sub)

Should use p.expectedError() for consistency.

---

✅ Overall Assessment

This is high-quality implementation that follows GoSQLX patterns excellently:

Strengths:

• Clean architecture with proper dialect isolation
• Comprehensive test coverage (35+ tests)
• Memory-efficient with object pooling
• Zero race conditions (properly gates features)
• Excellent documentation

Ready for production with minor fixes to the validation logic.

📋 Recommendations

1. Fix the SequenceOptions contradiction - use enum for CYCLE behavior
2. Add option validation in parseSequenceOptions()
3. Use structured errors instead of fmt.Errorf
4. Consider adding benchmark tests for MariaDB-specific parsing

[github-actions]

Claude finished @ajitpratap0's task —— View job

---

Code Review: MariaDB SQL Dialect Implementation

Review Checklist

• Analyze core architecture and design patterns
• Review AST implementation for new MariaDB features
• Examine parser logic and dialect handling
• Check keyword management and detection
• Assess test coverage and quality
• Review memory management and performance
• Analyze security considerations
• Check adherence to project conventions
• Provide final recommendations

Architecture & Design Patterns ✅

Strengths:

• Excellent architectural coherence: MariaDB properly inherits from MySQL as a superset
• Clean dialect gating: All MariaDB features guarded by p.isMariaDB() checks - zero impact on other dialects
• Proper separation of concerns: Keywords → AST → Parser layers cleanly separated
• Smart disambiguation: MariaDB/Oracle both support CONNECT BY but MariaDB gets higher-weight hints

AST Implementation ✅

Well-structured new types:

• SequenceOptions: Smart pointer-based design distinguishes unset vs zero values
• SystemTimeClauseType: Clean enum for temporal query variants
• ConnectByClause: Proper hierarchical query representation

Parser & Keywords ✅

Strong implementation:

• Robust option parsing: Handles all sequence option combinations properly
• Smart lookahead: Prevents table alias conflicts with isMariaDBClauseStart()
• Excellent dialect detection: High-weight hints (NEXTVAL=5) ensure correct detection

Test Coverage ✅

Comprehensive testing:

• 35+ unit tests covering all feature areas with good edge cases
• File-based integration tests parsing real SQL files
• Safe type assertions using proper two-value form

Security Analysis ✅

No new attack vectors introduced:

• MariaDB features follow same security patterns as existing codebase
• Existing SQL injection detection in pkg/sql/security/ still applies
• SEQUENCE operations properly validate numeric literals
• No dynamic SQL generation in new code

Performance & Memory ✅

Production-ready optimizations:

• Proper object pooling for all new AST types
• Zero-copy design maintained
• No memory leaks - all pools follow established patterns

---

🚨 Issues Found

1. Logic Contradiction in SequenceOptions (Medium Priority)

// pkg/sql/ast/ast.go:1852-1854
Cycle       bool          // CYCLE
NoCycle     bool          // NO CYCLE / NOCYCLE (explicit; default is NO CYCLE)

Problem: Both fields can be true simultaneously, creating invalid state.
Solution: Use enum instead:

type CycleOption int
const (
    CycleUnspecified CycleOption = iota
    CycleBehavior
    NoCycleBehavior
)

2. Missing Validation in Parser (Medium Priority)

The parseSequenceOptions() function doesn't validate contradictory options:

• Both CYCLE and NOCYCLE specified
• Both CACHE n and NOCACHE specified
• Negative INCREMENT BY values

Recommendation: Add validation in parseSequenceOptions() after parsing all options.

3. Inconsistent Error Handling (Low Priority)

In mariadb.go:207, generic fmt.Errorf used instead of structured errors from pkg/errors/:

return opts, fmt.Errorf("unexpected token after NO in SEQUENCE options: %s", sub)

Should use p.expectedError() for consistency.

---

✅ Overall Assessment

This is high-quality implementation that follows GoSQLX patterns excellently:

Strengths:

• Clean architecture with proper dialect isolation
• Comprehensive test coverage (35+ tests)
• Memory-efficient with object pooling
• Zero race conditions (properly gates features)
• Excellent documentation

Ready for production with minor fixes to the validation logic.

📋 Recommendations

1. Fix the SequenceOptions contradiction - use enum for CYCLE behavior
2. Add option validation in parseSequenceOptions()
3. Use structured errors instead of fmt.Errorf
4. Consider adding benchmark tests for MariaDB-specific parsing

[github-actions]

Claude finished @ajitpratap0's task —— View job

---

Code Review: MariaDB SQL Dialect Implementation

Review Checklist

• Analyze core architecture and design patterns
• Review AST implementation for new MariaDB features
• Examine parser logic and dialect handling
• Check keyword management and detection
• Assess test coverage and quality
• Review memory management and performance
• Analyze security considerations
• Check adherence to project conventions
• Provide final recommendations

Architecture & Design Patterns ✅

Strengths:

• Excellent architectural coherence: MariaDB properly inherits from MySQL as a superset
• Clean dialect gating: All MariaDB features guarded by p.isMariaDB() checks - zero impact on other dialects
• Proper separation of concerns: Keywords → AST → Parser layers cleanly separated
• Smart disambiguation: MariaDB/Oracle both support CONNECT BY but MariaDB gets higher-weight hints

AST Implementation ✅

Well-structured new types:

• SequenceOptions: Smart pointer-based design distinguishes unset vs zero values
• SystemTimeClauseType: Clean enum for temporal query variants
• ConnectByClause: Proper hierarchical query representation

Parser & Keywords ✅

Strong implementation:

• Robust option parsing: Handles all sequence option combinations properly
• Smart lookahead: Prevents table alias conflicts with isMariaDBClauseStart()
• Excellent dialect detection: High-weight hints (NEXTVAL=5) ensure correct detection

Test Coverage ✅

Comprehensive testing:

• 35+ unit tests covering all feature areas with good edge cases
• File-based integration tests parsing real SQL files
• Safe type assertions using proper two-value form

Security Analysis ✅

No new attack vectors introduced:

• MariaDB features follow same security patterns as existing codebase
• Existing SQL injection detection in pkg/sql/security/ still applies
• SEQUENCE operations properly validate numeric literals
• No dynamic SQL generation in new code

Performance & Memory ✅

Production-ready optimizations:

• Proper object pooling for all new AST types
• Zero-copy design maintained
• No memory leaks - all pools follow established patterns

---

🚨 Issues Found

1. Logic Contradiction in SequenceOptions (Medium Priority)

// pkg/sql/ast/ast.go:1852-1854
Cycle       bool          // CYCLE
NoCycle     bool          // NO CYCLE / NOCYCLE (explicit; default is NO CYCLE)

Problem: Both fields can be true simultaneously, creating invalid state.
Solution: Use enum instead:

type CycleOption int
const (
    CycleUnspecified CycleOption = iota
    CycleBehavior
    NoCycleBehavior
)

2. Missing Validation in Parser (Medium Priority)

The parseSequenceOptions() function doesn't validate contradictory options:

• Both CYCLE and NOCYCLE specified
• Both CACHE n and NOCACHE specified
• Negative INCREMENT BY values

Recommendation: Add validation in parseSequenceOptions() after parsing all options.

3. Inconsistent Error Handling (Low Priority)

In mariadb.go:207, generic fmt.Errorf used instead of structured errors from pkg/errors/:

return opts, fmt.Errorf("unexpected token after NO in SEQUENCE options: %s", sub)

Should use p.expectedError() for consistency.

---

✅ Overall Assessment

This is high-quality implementation that follows GoSQLX patterns excellently:

Strengths:

• Clean architecture with proper dialect isolation
• Comprehensive test coverage (35+ tests)
• Memory-efficient with object pooling
• Zero race conditions (properly gates features)
• Excellent documentation

Ready for production with minor fixes to the validation logic.

📋 Recommendations

1. Fix the SequenceOptions contradiction - use enum for CYCLE behavior
2. Add option validation in parseSequenceOptions()
3. Use structured errors instead of fmt.Errorf
4. Consider adding benchmark tests for MariaDB-specific parsing

[github-actions]

Claude finished @ajitpratap0's task —— View job

---

Code Review: MariaDB SQL Dialect Implementation

Review Checklist

• Analyze core architecture and design patterns
• Review AST implementation for new MariaDB features
• Examine parser logic and dialect handling
• Check keyword management and detection
• Assess test coverage and quality
• Review memory management and performance
• Analyze security considerations
• Check adherence to project conventions
• Provide final recommendations

---

✅ Overall Assessment

This is high-quality implementation that follows GoSQLX patterns excellently and addresses previous review feedback.

Architecture & Design Patterns ✅

Strengths:

• Excellent architectural coherence: MariaDB properly inherits from MySQL as a superset
• Clean dialect gating: All MariaDB features guarded by p.isMariaDB() checks - zero impact on other dialects
• Proper separation of concerns: Keywords → AST → Parser layers cleanly separated
• Smart disambiguation: MariaDB/Oracle both support CONNECT BY but MariaDB gets higher-weight hints

AST Implementation ✅

Previous issue resolved: The SequenceOptions boolean contradiction has been fixed! Now uses proper CycleOption enum (pkg/sql/ast/ast.go:1844-1854) instead of conflicting boolean fields.

Well-structured new types:

• SequenceOptions: Smart pointer-based design distinguishes unset vs zero values
• SystemTimeClauseType: Clean enum for temporal query variants
• ConnectByClause: Proper hierarchical query representation

Memory Management & Performance ✅

Production-ready optimizations:

• Proper object pooling for all new AST types (pkg/sql/ast/pool.go:356-364)
• Zero-copy design maintained
• No memory leaks - all pools follow established patterns
• Benchmark tests included for performance validation

Parser Logic & Dialect Handling ✅

Strong implementation:

• Option validation added: parseSequenceOptions() now validates contradictory options (pkg/sql/parser/mariadb.go:242-244)
• Robust option parsing: Handles all sequence option combinations properly
• Smart lookahead: Prevents table alias conflicts with isMariaDBClauseStart()

Keywords & Detection ✅

Excellent dialect detection: High-weight hints (NEXTVAL=5) ensure correct MariaDB detection over Oracle (pkg/sql/keywords/detect.go:80-90). All 17 MariaDB-specific keywords properly defined.

Test Coverage ✅

Comprehensive testing:

• 35+ unit tests across keywords (6), AST (11), and parser (19) layers
• File-based integration tests parsing real SQL files from testdata/mariadb/
• Benchmark tests for performance validation
• Safe type assertions using proper two-value form
• 719 total lines of test code across 3 test files

Security Analysis ✅

No new attack vectors introduced:

• MariaDB features follow same security patterns as existing codebase
• Existing SQL injection detection in pkg/sql/security/ still applies
• SEQUENCE operations properly validate numeric literals
• No dynamic SQL generation in new code

Adherence to Project Conventions ✅

Follows all GoSQLX patterns:

• Proper error handling (mostly uses p.expectedError())
• Memory pooling for all new types
• Zero race conditions (properly gates features)
• Documentation follows project style
• Changelog updated appropriately

---

🚨 Minor Issues Found

1. Inconsistent Error Handling (Low Priority)

Some functions use fmt.Errorf instead of structured errors from p.expectedError():

• Lines 243, 266, 279, 296, 313, 327, 342 in mariadb.go
• Recommendation: Replace with p.expectedError() for consistency

---

📋 Final Recommendations

1. Ready for production - this implementation is well-architected and comprehensive
2. Consider standardizing error handling - replace remaining fmt.Errorf calls with p.expectedError()
3. Excellent test coverage - no additional testing needed
4. Performance benchmarks included - good practice for new features

Approval recommendation: ✅ APPROVE - High-quality implementation that enhances GoSQLX with solid MariaDB dialect support.

---