FEAT-001: SQL-99 Compliance to 95%

[ajitpratap0]

Summary

Increase SQL-99 standard compliance from current 80-85% to 95%+ by implementing missing advanced SQL features.

Current State

• Current: 80-85% SQL-99 compliance
• Target: 95%+ compliance

Missing Features

• GROUPING SETS, ROLLUP, CUBE (advanced aggregation)
• Enhanced MERGE statement
• PIVOT/UNPIVOT operations
• Advanced window functions (PERCENT_RANK, CUME_DIST)
• TABLE function
• Recursive UNION with cycle detection

Action Items

Phase 1 (Q1 2025): GROUPING SETS, ROLLUP, CUBE

1. Add AST nodes for grouping operations
2. Update parser to handle GROUPING SETS syntax
3. Add 100+ test cases covering edge cases
4. Document new features

Phase 2 (Q2 2025): Enhanced MERGE, PIVOT/UNPIVOT

1. Implement MERGE statement with all clauses
2. Add PIVOT/UNPIVOT support
3. Add comprehensive tests

Phase 3 (Q3 2025): Advanced Analytics Functions

1. PERCENT_RANK(), CUME_DIST()
2. Enhanced window frame specifications
3. Advanced aggregation functions

Acceptance Criteria

• SQL-99 compliance ≥ 95%
• Test coverage for new features ≥ 80%
• Documentation updated with examples
• Benchmark performance impact (< 5% overhead)
• Backward compatible with existing queries

Technical Details

Priority: High
Effort: Large (240h / 3 months phased)
Phase: Phase 4 - Feature Development
Dependencies: None

Testing Strategy

• Unit tests for each new feature
• Integration tests with real-world queries
• Dialect-specific tests
• Performance benchmarks

[ajitpratap0]

✅ SQL-99 Feature F851 Implemented - NULLS FIRST/LAST Support

Implementation: PR #115 (merged November 20, 2025)

Achievement

Implemented SQL-99 Feature F851 for deterministic NULL ordering in ORDER BY clauses, increasing SQL-99 compliance from ~80-85% to ~80-85%+.

Features Implemented

1. NULLS FIRST/LAST in ORDER BY:

SELECT name, salary 
FROM employees 
ORDER BY salary DESC NULLS FIRST

2. Window Function ORDER BY Support:

SELECT dept, name, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC NULLS LAST) as dept_rank
FROM employees

3. Multi-Column Mixed NULL Handling:

SELECT dept, name, salary
FROM employees
ORDER BY dept NULLS FIRST, salary DESC NULLS LAST

Technical Implementation

AST Changes:

• New OrderByExpression struct with:
  • Expression field (the column/expression to order by)
  • Ascending bool (ASC/DESC direction)
  • NullsFirst *bool (tri-state: nil=database default, true=FIRST, false=LAST)

Parser Enhancements:

• Enhanced parseSelectStatement() for NULLS FIRST/LAST in ORDER BY
• Enhanced parseWindowSpec() for window function ORDER BY
• Added helper method parseNullsClause() for code reusability

Formatter Updates:

• Enhanced SQL formatter to output NULLS FIRST/LAST clauses
• Support for both SELECT ORDER BY and window function ORDER BY

Test Coverage:

• 15 comprehensive tests covering all scenarios
• Edge cases: multiple columns, window functions, mixed NULL handling
• All tests pass with race detection

Database Portability

This feature addresses different database NULL ordering defaults:

• PostgreSQL: NULLs are largest (DESC NULLS FIRST by default)
• MySQL: NULLs are smallest (ASC NULLS FIRST by default)
• Oracle: NULLs are largest

Users can now write portable SQL with deterministic NULL ordering.

Remaining Work for 95% Compliance

Still Missing:

• GROUPING SETS, ROLLUP, CUBE (advanced aggregation)
• Enhanced MERGE statement
• PIVOT/UNPIVOT operations
• Advanced window functions (PERCENT_RANK, CUME_DIST)
• TABLE function
• Recursive UNION with cycle detection

References

• SQL-99 Standard: ISO/IEC 9075-2:1999 Section 7.13 (ORDER BY clause)
• Feature F851: NULLS FIRST/LAST in ORDER BY

Status: Incremental progress toward 95% SQL-99 compliance target.

[ajitpratap0]

Progress Update: Significant SQL-99 Compliance Improvements ✅

Recent Achievements (PRs #117 & #118 merged)

Compatibility: ~24% → 94.6% of real-world SQL queries now parse successfully

Implemented Features:

1. Comparison Predicates (SQL-99 F041, F051, F052)

   • ✅ BETWEEN operator with NOT support
   • ✅ IN operator with value lists
   • ✅ LIKE/ILIKE pattern matching
   • ✅ IS NULL/IS NOT NULL postfix operators

2. Subquery Support (SQL-99 F591)

   • ✅ EXISTS / NOT EXISTS subquery expressions
   • ✅ IN with subquery support
   • ✅ Scalar subqueries in SELECT list
   • ✅ ANY / ALL subquery operators

3. Advanced Expressions

   • ✅ CASE expressions (simple and searched forms)
   • ✅ Parenthesized expressions
   • ✅ NULL literal support
   • ✅ Proper NOT token handling with look-ahead parsing

Updated Compliance Estimate

• Before: 80-85% SQL-99 compliance
• After: ~88-90% SQL-99 compliance (estimated)

Remaining for 95% Target

Still missing (from original issue):

• GROUPING SETS, ROLLUP, CUBE (advanced aggregation)
• Enhanced MERGE statement
• PIVOT/UNPIVOT operations
• Advanced window functions (PERCENT_RANK, CUME_DIST) - partially done, need completion
• TABLE function
• Recursive UNION with cycle detection improvements

Next Steps

Recommend prioritizing:

1. GROUPING SETS, ROLLUP, CUBE - High value for analytics use cases
2. Advanced window functions - Build on existing window function support
3. Enhanced MERGE statement - Frequently requested feature