test: Add comprehensive tests for tuple expressions in IN clause (#181)

[ajitpratap0]

Summary

Documents and verifies existing support for tuple/row expressions in IN clause as requested in Issue #181.

Discovery

Upon investigation, the tuple IN clause functionality was already fully implemented:

• TupleExpression AST node exists and is properly pooled
• Parser correctly handles (col1, col2) IN ((val1, val2), ...) syntax
• Subquery variant works: (a, b) IN (SELECT x, y FROM ...)

Tests Added

Parser Tests (pkg/sql/parser/tuple_in_test.go) - 11 test functions:

Test	Description
TestParser_TupleIn_Basic	Basic tuple IN with value list
TestParser_TupleIn_NotIn	NOT IN with tuples
TestParser_TupleIn_ThreeElements	3-element tuples
TestParser_TupleIn_WithExpressions	Arithmetic in tuples
TestParser_TupleIn_WithFunctionCalls	Function calls in tuples
TestParser_TupleIn_WithSubquery	Tuple IN with subquery
TestParser_TupleIn_SingleElementTuple	Edge case: (x) IN (...)
TestParser_TupleIn_QualifiedColumns	Table-qualified columns
TestParser_TupleIn_ComplexConditions	Combined with AND/OR
TestParser_TupleIn_MixedTypes	Mixed literal types

High-Level API Tests (pkg/gosqlx/tuple_in_test.go) - 5 test functions:

Test	Scenarios
TestParse_TupleIn_Basic	Basic parsing verification
TestParse_TupleIn_Scenarios	15 comprehensive scenarios
TestValidate_TupleIn	Validation function
TestExtractMetadata_TupleIn	Metadata extraction
TestParse_TupleIn_WithSubquery	Subquery detail verification

SQL Examples Verified

-- Basic tuple IN
SELECT * FROM orders WHERE (user_id, status) IN ((1, 'active'), (2, 'pending'));

-- NOT IN
SELECT * FROM orders WHERE (user_id, status) NOT IN ((1, 'active'));

-- With subquery
SELECT * FROM t WHERE (a, b) IN (SELECT x, y FROM other);

-- With expressions
SELECT * FROM t WHERE (a + 1, LOWER(name)) IN ((10, 'john'));

-- Combined conditions
SELECT * FROM t WHERE (a, b) IN ((1, 2)) AND c > 100;

Test plan

• All 26 new test scenarios pass
• Race detection clean
• Pre-commit hooks pass

Closes

Closes #181

🤖 Generated with Claude Code

[claude]

Claude finished @ajitpratap0's task —— View job

---

PR Review: Tuple IN Clause Tests

Todo List:

• Read and analyze new test files
• Check adherence to GoSQLX coding standards
• Review test coverage completeness
• Look for potential bugs or edge cases
• Assess performance considerations
• Check security aspects
• Provide comprehensive feedback

---

🔍 Code Quality Review

✅ Strengths

1. Excellent adherence to project patterns:

   • Proper object pooling usage with defer statements (tokenizer.PutTokenizer(), ast.ReleaseAST(), parser.Release())
   • Follows existing test structure and naming conventions
   • Consistent error handling patterns matching project standards

2. Comprehensive test coverage:

   • 11 parser tests covering diverse scenarios from basic to complex
   • 5 high-level API tests including 15 scenarios in table-driven tests
   • Tests both value lists and subquery variants properly
   • Good edge case coverage (single element, qualified columns, mixed types)

3. Well-structured test scenarios:

   • Clear separation between parser-level and high-level API tests
   • Descriptive test names following TestParser_TupleIn_Feature pattern
   • Good mix of positive test cases with validation

4. Proper AST validation:

   • Correctly validates TupleExpression structures
   • Verifies InExpression fields (Expr, List, Subquery, Not)
   • Checks element counts and types appropriately

⚠️ Areas for Improvement

1. Missing Error Cases (Priority: Medium)

// Missing tests for these invalid cases:
"SELECT * FROM t WHERE (a, b) IN ((1))"          // Mismatched tuple sizes
"SELECT * FROM t WHERE (a) IN ((1, 2))"          // Mismatched tuple sizes  
"SELECT * FROM t WHERE (a, b) IN ()"             // Empty IN list
"SELECT * FROM t WHERE (a, b) IN ((,))"          // Malformed tuple

2. Test Isolation Issues (Priority: Low)

Several tests make unsafe type assertions without checks:

// pkg/sql/parser/tuple_in_test.go:110, 151, etc.
stmt := tree.Statements[0].(*ast.SelectStatement)  // Could panic
inExpr := stmt.Where.(*ast.InExpression)           // Could panic

Recommended fix:

stmt, ok := tree.Statements[0].(*ast.SelectStatement)
if !ok {
    t.Fatalf("expected SelectStatement, got %T", tree.Statements[0])
}

3. Resource Management (Priority: Low)

The parser tests create parsers for each test instead of using table-driven tests, leading to more allocations. Consider consolidating similar tests.

4. Limited Negative Testing (Priority: Medium)

Only one test checks the Not field for NOT IN. Consider adding more negative test scenarios.

🚀 Performance Considerations

• Good: Proper object pooling prevents memory leaks
• Good: Tests release resources appropriately
• Minor: Some redundant parsing setup could be consolidated
• Assessment: No performance impact on production code

🔒 Security Assessment

• No security issues identified - tests only validate parsing logic
• All SQL strings are hardcoded literals (no injection vectors)
• Tests follow defensive programming practices

📊 Test Coverage Analysis

Covered scenarios:

• ✅ Basic tuple IN/NOT IN
• ✅ Multi-element tuples (2, 3, 4 elements)
• ✅ Complex expressions (arithmetic, functions)
• ✅ Qualified column names (table.column)
• ✅ Subquery variants
• ✅ Combined conditions (AND/OR)
• ✅ Mixed data types
• ✅ High-level API integration

Missing scenarios:

• ❌ Error cases (malformed tuples, mismatched sizes)
• ❌ Very large tuples (performance edge cases)
• ❌ Nested tuple expressions
• ❌ Empty tuple validation

📋 Recommendations

1. Add Error Test Cases

func TestParser_TupleIn_ErrorCases(t *testing.T) {
    errorCases := []string{
        "SELECT * FROM t WHERE (a, b) IN ((1))",      // Size mismatch
        "SELECT * FROM t WHERE (a) IN ((1, 2))",      // Size mismatch  
        "SELECT * FROM t WHERE (a, b) IN ()",         // Empty list
    }
    // Test that these return appropriate parsing errors
}

2. Improve Type Safety

Replace unsafe type assertions with safe checks in critical sections.

3. Consider Table-Driven Consolidation

Some parser tests could be consolidated into table-driven tests for better maintainability.

✅ Overall Assessment

This is a high-quality PR that:

• ✅ Properly documents existing functionality
• ✅ Follows project conventions meticulously
• ✅ Provides excellent test coverage for the happy path
• ✅ Uses object pooling correctly
• ✅ No security or performance concerns

Recommendation: APPROVE with minor suggestions for enhanced error testing.

The tests are well-written, comprehensive, and properly validate that the tuple IN clause functionality works as expected. The missing error cases are nice-to-have improvements rather than blocking issues.

---

[claude]

Claude finished @ajitpratap0's task —— View job

---

PR Review: Tuple IN Clause Tests

Todo List:

• Read project guidelines from CLAUDE.md
• Analyze new test files structure and patterns
• Check adherence to GoSQLX coding standards
• Review test coverage completeness
• Look for potential bugs or edge cases
• Assess performance considerations
• Check security aspects
• Provide comprehensive feedback

---

🔍 Code Quality Review

✅ Strengths

1. Excellent adherence to project patterns:

   • Proper object pooling usage with defer statements (tokenizer.PutTokenizer(), ast.ReleaseAST(), parser.Release())
   • Follows existing test structure and naming conventions
   • Consistent error handling patterns matching project standards

2. Comprehensive test coverage:

   • 11 parser tests covering diverse scenarios from basic to complex
   • 5 high-level API tests including 15 scenarios in table-driven tests
   • Tests both value lists and subquery variants properly
   • Good edge case coverage (single element, qualified columns, mixed types)

3. Well-structured test scenarios:

   • Clear separation between parser-level and high-level API tests
   • Descriptive test names following TestParser_TupleIn_Feature pattern
   • Good mix of positive test cases with validation

4. Proper AST validation:

   • Correctly validates TupleExpression structures
   • Verifies InExpression fields (Expr, List, Subquery, Not)
   • Checks element counts and types appropriately

⚠️ Areas for Improvement

1. Missing Error Cases (Priority: Medium)

// Missing tests for these invalid cases:
"SELECT * FROM t WHERE (a, b) IN ((1))"          // Mismatched tuple sizes
"SELECT * FROM t WHERE (a) IN ((1, 2))"          // Mismatched tuple sizes  
"SELECT * FROM t WHERE (a, b) IN ()"             // Empty IN list
"SELECT * FROM t WHERE (a, b) IN ((,))"          // Malformed tuple

2. Test Isolation Issues (Priority: Low)

Several tests make unsafe type assertions without checks:

// pkg/sql/parser/tuple_in_test.go:110, 151, etc.
stmt := tree.Statements[0].(*ast.SelectStatement)  // Could panic
inExpr := stmt.Where.(*ast.InExpression)           // Could panic

Recommended fix:

stmt, ok := tree.Statements[0].(*ast.SelectStatement)
if !ok {
    t.Fatalf("expected SelectStatement, got %T", tree.Statements[0])
}

3. Resource Management (Priority: Low)

The parser tests create parsers for each test instead of using table-driven tests, leading to more allocations. Consider consolidating similar tests.

4. Limited Negative Testing (Priority: Medium)

Only one test checks the Not field for NOT IN. Consider adding more negative test scenarios.

🚀 Performance Considerations

• Good: Proper object pooling prevents memory leaks
• Good: Tests release resources appropriately
• Minor: Some redundant parsing setup could be consolidated
• Assessment: No performance impact on production code

🔒 Security Assessment

• No security issues identified - tests only validate parsing logic
• All SQL strings are hardcoded literals (no injection vectors)
• Tests follow defensive programming practices

📊 Test Coverage Analysis

Covered scenarios:

• ✅ Basic tuple IN/NOT IN
• ✅ Multi-element tuples (2, 3, 4 elements)
• ✅ Complex expressions (arithmetic, functions)
• ✅ Qualified column names (table.column)
• ✅ Subquery variants
• ✅ Combined conditions (AND/OR)
• ✅ Mixed data types
• ✅ High-level API integration

Missing scenarios:

• ❌ Error cases (malformed tuples, mismatched sizes)
• ❌ Very large tuples (performance edge cases)
• ❌ Nested tuple expressions
• ❌ Empty tuple validation

📋 Recommendations

1. Add Error Test Cases

func TestParser_TupleIn_ErrorCases(t *testing.T) {
    errorCases := []string{
        "SELECT * FROM t WHERE (a, b) IN ((1))",      // Size mismatch
        "SELECT * FROM t WHERE (a) IN ((1, 2))",      // Size mismatch  
        "SELECT * FROM t WHERE (a, b) IN ()",         // Empty list
    }
    // Test that these return appropriate parsing errors
}

2. Improve Type Safety

Replace unsafe type assertions with safe checks in critical sections.

3. Consider Table-Driven Consolidation

Some parser tests could be consolidated into table-driven tests for better maintainability.

✅ Overall Assessment

This is a high-quality PR that:

• ✅ Properly documents existing functionality
• ✅ Follows project conventions meticulously
• ✅ Provides excellent test coverage for the happy path
• ✅ Uses object pooling correctly
• ✅ No security or performance concerns

Recommendation: APPROVE with minor suggestions for enhanced error testing.

The tests are well-written, comprehensive, and properly validate that the tuple IN clause functionality works as expected. The missing error cases are nice-to-have improvements rather than blocking issues.

---

[claude]

Claude finished @ajitpratap0's task —— View job

---

PR Review: Tuple IN Clause Tests

Todo List:

• Read project guidelines from CLAUDE.md
• Analyze new test files structure and patterns
• Check adherence to GoSQLX coding standards
• Review test coverage completeness
• Look for potential bugs or edge cases
• Assess performance considerations
• Check security aspects
• Provide comprehensive feedback

---

🔍 Code Quality Review

✅ Strengths

1. Excellent adherence to project patterns:

   • Proper object pooling usage with defer statements (tokenizer.PutTokenizer(), ast.ReleaseAST(), parser.Release())
   • Follows existing test structure and naming conventions
   • Consistent error handling patterns matching project standards

2. Comprehensive test coverage:

   • 11 parser tests covering diverse scenarios from basic to complex
   • 5 high-level API tests including 15 scenarios in table-driven tests
   • Tests both value lists and subquery variants properly
   • Good edge case coverage (single element, qualified columns, mixed types)

3. Well-structured test scenarios:

   • Clear separation between parser-level and high-level API tests
   • Descriptive test names following TestParser_TupleIn_Feature pattern
   • Good mix of positive test cases with validation

4. Proper AST validation:

   • Correctly validates TupleExpression structures
   • Verifies InExpression fields (Expr, List, Subquery, Not)
   • Checks element counts and types appropriately

⚠️ Areas for Improvement

1. Missing Error Cases (Priority: Medium)

// Missing tests for these invalid cases:
"SELECT * FROM t WHERE (a, b) IN ((1))"          // Mismatched tuple sizes
"SELECT * FROM t WHERE (a) IN ((1, 2))"          // Mismatched tuple sizes  
"SELECT * FROM t WHERE (a, b) IN ()"             // Empty IN list
"SELECT * FROM t WHERE (a, b) IN ((,))"          // Malformed tuple

2. Test Isolation Issues (Priority: Low)

Several tests make unsafe type assertions without checks:

// pkg/sql/parser/tuple_in_test.go:110, 151, etc.
stmt := tree.Statements[0].(*ast.SelectStatement)  // Could panic
inExpr := stmt.Where.(*ast.InExpression)           // Could panic

Recommended fix:

stmt, ok := tree.Statements[0].(*ast.SelectStatement)
if !ok {
    t.Fatalf("expected SelectStatement, got %T", tree.Statements[0])
}

3. Resource Management (Priority: Low)

The parser tests create parsers for each test instead of using table-driven tests, leading to more allocations. Consider consolidating similar tests.

4. Limited Negative Testing (Priority: Medium)

Only one test checks the Not field for NOT IN. Consider adding more negative test scenarios.

🚀 Performance Considerations

• Good: Proper object pooling prevents memory leaks
• Good: Tests release resources appropriately
• Minor: Some redundant parsing setup could be consolidated
• Assessment: No performance impact on production code

🔒 Security Assessment

• No security issues identified - tests only validate parsing logic
• All SQL strings are hardcoded literals (no injection vectors)
• Tests follow defensive programming practices

📊 Test Coverage Analysis

Covered scenarios:

• ✅ Basic tuple IN/NOT IN
• ✅ Multi-element tuples (2, 3, 4 elements)
• ✅ Complex expressions (arithmetic, functions)
• ✅ Qualified column names (table.column)
• ✅ Subquery variants
• ✅ Combined conditions (AND/OR)
• ✅ Mixed data types
• ✅ High-level API integration

Missing scenarios:

• ❌ Error cases (malformed tuples, mismatched sizes)
• ❌ Very large tuples (performance edge cases)
• ❌ Nested tuple expressions
• ❌ Empty tuple validation

📋 Recommendations

1. Add Error Test Cases

func TestParser_TupleIn_ErrorCases(t *testing.T) {
    errorCases := []string{
        "SELECT * FROM t WHERE (a, b) IN ((1))",      // Size mismatch
        "SELECT * FROM t WHERE (a) IN ((1, 2))",      // Size mismatch  
        "SELECT * FROM t WHERE (a, b) IN ()",         // Empty list
    }
    // Test that these return appropriate parsing errors
}

2. Improve Type Safety

Replace unsafe type assertions with safe checks in critical sections.

3. Consider Table-Driven Consolidation

Some parser tests could be consolidated into table-driven tests for better maintainability.

✅ Overall Assessment

This is a high-quality PR that:

• ✅ Properly documents existing functionality
• ✅ Follows project conventions meticulously
• ✅ Provides excellent test coverage for the happy path
• ✅ Uses object pooling correctly
• ✅ No security or performance concerns

Recommendation: APPROVE with minor suggestions for enhanced error testing.

The tests are well-written, comprehensive, and properly validate that the tuple IN clause functionality works as expected. The missing error cases are nice-to-have improvements rather than blocking issues.

---

[claude]

Claude finished @ajitpratap0's task —— View job

---

PR Review: Tuple IN Clause Tests

Todo List:

• Read project guidelines from CLAUDE.md
• Analyze new test files structure and patterns
• Check adherence to GoSQLX coding standards
• Review test coverage completeness
• Look for potential bugs or edge cases
• Assess performance considerations
• Check security aspects
• Provide comprehensive feedback

---

🔍 Code Quality Review

✅ Strengths

1. Excellent adherence to project patterns:

   • Proper object pooling usage with defer statements (tokenizer.PutTokenizer(), ast.ReleaseAST(), parser.Release())
   • Follows existing test structure and naming conventions
   • Consistent error handling patterns matching project standards

2. Comprehensive test coverage:

   • 11 parser tests covering diverse scenarios from basic to complex
   • 5 high-level API tests including 15 scenarios in table-driven tests
   • Tests both value lists and subquery variants properly
   • Good edge case coverage (single element, qualified columns, mixed types)

3. Well-structured test scenarios:

   • Clear separation between parser-level and high-level API tests
   • Descriptive test names following TestParser_TupleIn_Feature pattern
   • Good mix of positive test cases with validation

4. Proper AST validation:

   • Correctly validates TupleExpression structures
   • Verifies InExpression fields (Expr, List, Subquery, Not)
   • Checks element counts and types appropriately

⚠️ Areas for Improvement

1. Missing Error Cases (Priority: Medium)

// Missing tests for these invalid cases:
"SELECT * FROM t WHERE (a, b) IN ((1))"          // Mismatched tuple sizes
"SELECT * FROM t WHERE (a) IN ((1, 2))"          // Mismatched tuple sizes  
"SELECT * FROM t WHERE (a, b) IN ()"             // Empty IN list
"SELECT * FROM t WHERE (a, b) IN ((,))"          // Malformed tuple

2. Test Isolation Issues (Priority: Low)

Several tests make unsafe type assertions without checks:

// pkg/sql/parser/tuple_in_test.go:110, 151, etc.
stmt := tree.Statements[0].(*ast.SelectStatement)  // Could panic
inExpr := stmt.Where.(*ast.InExpression)           // Could panic

Recommended fix:

stmt, ok := tree.Statements[0].(*ast.SelectStatement)
if !ok {
    t.Fatalf("expected SelectStatement, got %T", tree.Statements[0])
}

3. Resource Management (Priority: Low)

The parser tests create parsers for each test instead of using table-driven tests, leading to more allocations. Consider consolidating similar tests.

4. Limited Negative Testing (Priority: Medium)

Only one test checks the Not field for NOT IN. Consider adding more negative test scenarios.

🚀 Performance Considerations

• Good: Proper object pooling prevents memory leaks
• Good: Tests release resources appropriately
• Minor: Some redundant parsing setup could be consolidated
• Assessment: No performance impact on production code

🔒 Security Assessment

• No security issues identified - tests only validate parsing logic
• All SQL strings are hardcoded literals (no injection vectors)
• Tests follow defensive programming practices

📊 Test Coverage Analysis

Covered scenarios:

• ✅ Basic tuple IN/NOT IN
• ✅ Multi-element tuples (2, 3, 4 elements)
• ✅ Complex expressions (arithmetic, functions)
• ✅ Qualified column names (table.column)
• ✅ Subquery variants
• ✅ Combined conditions (AND/OR)
• ✅ Mixed data types
• ✅ High-level API integration

Missing scenarios:

• ❌ Error cases (malformed tuples, mismatched sizes)
• ❌ Very large tuples (performance edge cases)
• ❌ Nested tuple expressions
• ❌ Empty tuple validation

📋 Recommendations

1. Add Error Test Cases

func TestParser_TupleIn_ErrorCases(t *testing.T) {
    errorCases := []string{
        "SELECT * FROM t WHERE (a, b) IN ((1))",      // Size mismatch
        "SELECT * FROM t WHERE (a) IN ((1, 2))",      // Size mismatch  
        "SELECT * FROM t WHERE (a, b) IN ()",         // Empty list
    }
    // Test that these return appropriate parsing errors
}

2. Improve Type Safety

Replace unsafe type assertions with safe checks in critical sections.

3. Consider Table-Driven Consolidation

Some parser tests could be consolidated into table-driven tests for better maintainability.

✅ Overall Assessment

This is a high-quality PR that:

• ✅ Properly documents existing functionality
• ✅ Follows project conventions meticulously
• ✅ Provides excellent test coverage for the happy path
• ✅ Uses object pooling correctly
• ✅ No security or performance concerns

Recommendation: APPROVE with minor suggestions for enhanced error testing.

The tests are well-written, comprehensive, and properly validate that the tuple IN clause functionality works as expected. The missing error cases are nice-to-have improvements rather than blocking issues.

---