Parser fails on CAST expressions (CAST(x AS type))

[ajitpratap0]

Summary

The parser fails to parse standard SQL CAST expressions, returning a syntax error.

Steps to Reproduce

./gosqlx validate "SELECT CAST(id AS VARCHAR(50)) FROM users"

Expected Behavior

The query should be parsed successfully as CAST is standard SQL syntax.

Actual Behavior

Error E2002 at line 1, column X: expected , or ), got AS

Test Cases That Fail

-- Basic CAST
SELECT CAST(id AS VARCHAR) FROM users;

-- CAST with precision
SELECT CAST(price AS DECIMAL(10,2)) FROM products;

-- CAST with length
SELECT CAST(name AS VARCHAR(100)) FROM users;

-- Multiple CASTs
SELECT CAST(id AS TEXT), CAST(amount AS INTEGER) FROM orders;

-- CAST in expression
SELECT CAST(created_at AS DATE) + INTERVAL '1 day' FROM events;

-- CAST in WHERE clause
SELECT * FROM users WHERE CAST(age AS INTEGER) > 18;

Analysis

The parser appears to treat CAST(...) as a regular function call and doesn't handle the special AS type syntax inside the parentheses. The error "expected , or )" suggests it's expecting either another argument or the closing parenthesis after the first expression, not the AS keyword.

Root Cause Investigation

The issue is likely in:

• parseFunctionCall() - doesn't have special handling for CAST
• Need a dedicated parseCastExpression() function
• Or modify expression parsing to handle AS within function-like syntax

Impact

• HIGH - CAST is fundamental SQL functionality used for:
  • Type conversion
  • Data formatting
  • Compatibility between columns
  • Report generation

Acceptance Criteria

• CAST(expr AS type) parses successfully
• All standard SQL types supported (VARCHAR, INTEGER, DECIMAL, etc.)
• Type parameters work (VARCHAR(50), DECIMAL(10,2))
• CAST works in SELECT, WHERE, ORDER BY, etc.
• Tests added for CAST patterns
• AST node created for CAST expressions

Workaround

Some databases support :: syntax as an alternative:

-- PostgreSQL style (may or may not work)
SELECT id::varchar FROM users;

References

• SQL Standard: CAST specification
• PostgreSQL: https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS

[ajitpratap0]

✅ Fixed - CAST Expression Parsing

Root cause: The CAST keyword was missing from the tokenizer's keywordTokenTypes map, causing it to be recognized as an identifier instead of a keyword.

Fix: Added "CAST": models.TokenTypeCast to pkg/sql/tokenizer/tokenizer.go

All CAST expressions now parse correctly:

SELECT CAST(id AS VARCHAR) FROM users
SELECT CAST(price AS DECIMAL(10,2)) FROM products
SELECT * FROM users WHERE CAST(age AS INTEGER) > 18