No SQL Validation in Tests - Generated SQL May Be Invalid

[richardwooding]

Summary

Tests verify SQL string output matches expectations but don't validate that generated SQL is syntactically valid, executable, or semantically correct.

Location

All test files (cel2sql_test.go, comprehensions_test.go, etc.)

Issue

Tests use string comparisons:

func TestExample(t *testing.T) {
    ast, _ := env.Compile(`expr`)
    sql, _ := cel2sql.Convert(ast)
    require.Equal(t, "expected SQL", sql)
}

This doesn't validate:

• SQL is syntactically valid PostgreSQL
• Query executes without errors
• Query returns expected results
• Type conversions are correct

Impact

High - Invalid SQL could be generated and tests would still pass. Only fails when users try to execute queries.

Current State

Integration tests exist in provider_testcontainer_test.go but only cover the pg package, not conversion logic.

Recommendation

Add SQL validation tests using testcontainers:

func TestGeneratedSQLIsValid(t *testing.T) {
    ctx := context.Background()
    
    // Setup test database with schema
    pool := setupTestDB(t, ctx)
    defer pool.Close()
    
    testCases := []struct{
        name string
        expr string
    }{
        {"simple comparison", `user.age > 18`},
        {"JSON access", `user.metadata.key == "value"`},
        // ... more cases
    }
    
    for _, tc := range testCases {
        t.Run(tc.name, func(t *testing.T) {
            ast, err := env.Compile(tc.expr)
            require.NoError(t, err)
            
            sql, err := cel2sql.Convert(ast)
            require.NoError(t, err)
            
            // Validate SQL is executable
            query := "SELECT * FROM users WHERE " + sql
            rows, err := pool.Query(ctx, query)
            require.NoError(t, err, "Generated SQL should be valid")
            rows.Close()
        })
    }
}

Additional Validation

Consider using PostgreSQL EXPLAIN to validate query plans:

_, err := pool.Query(ctx, "EXPLAIN " + query)
require.NoError(t, err, "Query should have valid execution plan")