diff options
Diffstat (limited to 'weed/query/engine/comprehensive_sql_test.go')
| -rw-r--r-- | weed/query/engine/comprehensive_sql_test.go | 349 |
1 files changed, 349 insertions, 0 deletions
diff --git a/weed/query/engine/comprehensive_sql_test.go b/weed/query/engine/comprehensive_sql_test.go new file mode 100644 index 000000000..5878bfba4 --- /dev/null +++ b/weed/query/engine/comprehensive_sql_test.go @@ -0,0 +1,349 @@ +package engine + +import ( + "context" + "strings" + "testing" +) + +// TestComprehensiveSQLSuite tests all kinds of SQL patterns to ensure robustness +func TestComprehensiveSQLSuite(t *testing.T) { + engine := NewTestSQLEngine() + + testCases := []struct { + name string + sql string + shouldPanic bool + shouldError bool + desc string + }{ + // =========== BASIC QUERIES =========== + { + name: "Basic_Select_All", + sql: "SELECT * FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "Basic select all columns", + }, + { + name: "Basic_Select_Column", + sql: "SELECT id FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "Basic select single column", + }, + { + name: "Basic_Select_Multiple_Columns", + sql: "SELECT id, status FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "Basic select multiple columns", + }, + + // =========== ARITHMETIC EXPRESSIONS (FIXED) =========== + { + name: "Arithmetic_Multiply_FIXED", + sql: "SELECT id*2 FROM user_events", + shouldPanic: false, // Fixed: no longer panics + shouldError: false, + desc: "FIXED: Arithmetic multiplication works", + }, + { + name: "Arithmetic_Add", + sql: "SELECT id+10 FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "Arithmetic addition works", + }, + { + name: "Arithmetic_Subtract", + sql: "SELECT id-5 FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "Arithmetic subtraction works", + }, + { + name: "Arithmetic_Divide", + sql: "SELECT id/3 FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "Arithmetic division works", + }, + { + name: "Arithmetic_Complex", + sql: "SELECT id*2+10 FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "Complex arithmetic expression works", + }, + + // =========== STRING OPERATIONS =========== + { + name: "String_Concatenation", + sql: "SELECT 'hello' || 'world' FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "String concatenation", + }, + { + name: "String_Column_Concat", + sql: "SELECT status || '_suffix' FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "Column string concatenation", + }, + + // =========== FUNCTIONS =========== + { + name: "Function_LENGTH", + sql: "SELECT LENGTH('hello') FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "LENGTH function with literal", + }, + { + name: "Function_LENGTH_Column", + sql: "SELECT LENGTH(status) FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "LENGTH function with column", + }, + { + name: "Function_UPPER", + sql: "SELECT UPPER('hello') FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "UPPER function", + }, + { + name: "Function_Nested", + sql: "SELECT LENGTH(UPPER('hello')) FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "Nested functions", + }, + + // =========== FUNCTIONS WITH ARITHMETIC =========== + { + name: "Function_Arithmetic", + sql: "SELECT LENGTH('hello') + 10 FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "Function with arithmetic", + }, + { + name: "Function_Arithmetic_Complex", + sql: "SELECT LENGTH(status) * 2 + 5 FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "Function with complex arithmetic", + }, + + // =========== TABLE REFERENCES =========== + { + name: "Table_Simple", + sql: "SELECT * FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "Simple table reference", + }, + { + name: "Table_With_Database", + sql: "SELECT * FROM ecommerce.user_events", + shouldPanic: false, + shouldError: false, + desc: "Table with database qualifier", + }, + { + name: "Table_Quoted", + sql: `SELECT * FROM "user_events"`, + shouldPanic: false, + shouldError: false, + desc: "Quoted table name", + }, + + // =========== WHERE CLAUSES =========== + { + name: "Where_Simple", + sql: "SELECT * FROM user_events WHERE id = 1", + shouldPanic: false, + shouldError: false, + desc: "Simple WHERE clause", + }, + { + name: "Where_String", + sql: "SELECT * FROM user_events WHERE status = 'active'", + shouldPanic: false, + shouldError: false, + desc: "WHERE clause with string", + }, + + // =========== LIMIT/OFFSET =========== + { + name: "Limit_Only", + sql: "SELECT * FROM user_events LIMIT 10", + shouldPanic: false, + shouldError: false, + desc: "LIMIT clause only", + }, + { + name: "Limit_Offset", + sql: "SELECT * FROM user_events LIMIT 10 OFFSET 5", + shouldPanic: false, + shouldError: false, + desc: "LIMIT with OFFSET", + }, + + // =========== DATETIME FUNCTIONS =========== + { + name: "DateTime_CURRENT_DATE", + sql: "SELECT CURRENT_DATE FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "CURRENT_DATE function", + }, + { + name: "DateTime_NOW", + sql: "SELECT NOW() FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "NOW() function", + }, + { + name: "DateTime_EXTRACT", + sql: "SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "EXTRACT function", + }, + + // =========== EDGE CASES =========== + { + name: "Empty_String", + sql: "SELECT '' FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "Empty string literal", + }, + { + name: "Multiple_Spaces", + sql: "SELECT id FROM user_events", + shouldPanic: false, + shouldError: false, + desc: "Query with multiple spaces", + }, + { + name: "Mixed_Case", + sql: "Select ID from User_Events", + shouldPanic: false, + shouldError: false, + desc: "Mixed case SQL", + }, + + // =========== SHOW STATEMENTS =========== + { + name: "Show_Databases", + sql: "SHOW DATABASES", + shouldPanic: false, + shouldError: false, + desc: "SHOW DATABASES statement", + }, + { + name: "Show_Tables", + sql: "SHOW TABLES", + shouldPanic: false, + shouldError: false, + desc: "SHOW TABLES statement", + }, + } + + var panicTests []string + var errorTests []string + var successTests []string + + for _, tc := range testCases { + t.Run(tc.name, func(t *testing.T) { + // Capture panics + var panicValue interface{} + func() { + defer func() { + if r := recover(); r != nil { + panicValue = r + } + }() + + result, err := engine.ExecuteSQL(context.Background(), tc.sql) + + if tc.shouldPanic { + if panicValue == nil { + t.Errorf("FAIL: Expected panic for %s, but query completed normally", tc.desc) + panicTests = append(panicTests, "FAIL: "+tc.desc) + return + } else { + t.Logf("PASS: EXPECTED PANIC: %s - %v", tc.desc, panicValue) + panicTests = append(panicTests, "PASS: "+tc.desc+" (reproduced)") + return + } + } + + if panicValue != nil { + t.Errorf("FAIL: Unexpected panic for %s: %v", tc.desc, panicValue) + panicTests = append(panicTests, "FAIL: "+tc.desc+" (unexpected panic)") + return + } + + if tc.shouldError { + if err == nil && (result == nil || result.Error == nil) { + t.Errorf("FAIL: Expected error for %s, but query succeeded", tc.desc) + errorTests = append(errorTests, "FAIL: "+tc.desc) + return + } else { + t.Logf("PASS: Expected error: %s", tc.desc) + errorTests = append(errorTests, "PASS: "+tc.desc) + return + } + } + + if err != nil { + t.Errorf("FAIL: Unexpected error for %s: %v", tc.desc, err) + errorTests = append(errorTests, "FAIL: "+tc.desc+" (unexpected error)") + return + } + + if result != nil && result.Error != nil { + t.Errorf("FAIL: Unexpected result error for %s: %v", tc.desc, result.Error) + errorTests = append(errorTests, "FAIL: "+tc.desc+" (unexpected result error)") + return + } + + t.Logf("PASS: Success: %s", tc.desc) + successTests = append(successTests, "PASS: "+tc.desc) + }() + }) + } + + // Summary report + separator := strings.Repeat("=", 80) + t.Log("\n" + separator) + t.Log("COMPREHENSIVE SQL TEST SUITE SUMMARY") + t.Log(separator) + t.Logf("Total Tests: %d", len(testCases)) + t.Logf("Successful: %d", len(successTests)) + t.Logf("Panics: %d", len(panicTests)) + t.Logf("Errors: %d", len(errorTests)) + t.Log(separator) + + if len(panicTests) > 0 { + t.Log("\nPANICS TO FIX:") + for _, test := range panicTests { + t.Log(" " + test) + } + } + + if len(errorTests) > 0 { + t.Log("\nERRORS TO INVESTIGATE:") + for _, test := range errorTests { + t.Log(" " + test) + } + } +} |