1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
|
package engine
import (
"context"
"strconv"
"testing"
)
// TestWhereClauseValidation tests WHERE clause functionality with various conditions
func TestWhereClauseValidation(t *testing.T) {
engine := NewTestSQLEngine()
t.Log("WHERE CLAUSE VALIDATION TESTS")
t.Log("==============================")
// Test 1: Baseline - get all rows to understand the data
baselineResult, err := engine.ExecuteSQL(context.Background(), "SELECT id FROM user_events")
if err != nil {
t.Fatalf("Baseline query failed: %v", err)
}
t.Logf("Baseline data - Total rows: %d", len(baselineResult.Rows))
if len(baselineResult.Rows) > 0 {
t.Logf("Sample IDs: %s, %s, %s",
baselineResult.Rows[0][0].ToString(),
baselineResult.Rows[1][0].ToString(),
baselineResult.Rows[2][0].ToString())
}
// Test 2: Specific ID match (should return 1 row)
firstId := baselineResult.Rows[0][0].ToString()
specificResult, err := engine.ExecuteSQL(context.Background(),
"SELECT id FROM user_events WHERE id = "+firstId)
if err != nil {
t.Fatalf("Specific ID query failed: %v", err)
}
t.Logf("WHERE id = %s: %d rows", firstId, len(specificResult.Rows))
if len(specificResult.Rows) == 1 {
t.Logf("Specific ID filtering works correctly")
} else {
t.Errorf("Expected 1 row, got %d rows", len(specificResult.Rows))
}
// Test 3: Range filtering (find actual data ranges)
// First, find the min and max IDs in our data
var minId, maxId int64 = 999999999, 0
for _, row := range baselineResult.Rows {
if idVal, err := strconv.ParseInt(row[0].ToString(), 10, 64); err == nil {
if idVal < minId {
minId = idVal
}
if idVal > maxId {
maxId = idVal
}
}
}
t.Logf("Data range: min ID = %d, max ID = %d", minId, maxId)
// Test with a threshold between min and max
threshold := (minId + maxId) / 2
rangeResult, err := engine.ExecuteSQL(context.Background(),
"SELECT id FROM user_events WHERE id > "+strconv.FormatInt(threshold, 10))
if err != nil {
t.Fatalf("Range query failed: %v", err)
}
t.Logf("WHERE id > %d: %d rows", threshold, len(rangeResult.Rows))
// Verify all returned IDs are > threshold
allCorrect := true
for _, row := range rangeResult.Rows {
if idVal, err := strconv.ParseInt(row[0].ToString(), 10, 64); err == nil {
if idVal <= threshold {
t.Errorf("Found ID %d which should be filtered out (<= %d)", idVal, threshold)
allCorrect = false
}
}
}
if allCorrect && len(rangeResult.Rows) > 0 {
t.Logf("Range filtering works correctly - all returned IDs > %d", threshold)
} else if len(rangeResult.Rows) == 0 {
t.Logf("Range filtering works correctly - no IDs > %d in data", threshold)
}
// Test 4: String filtering
statusResult, err := engine.ExecuteSQL(context.Background(),
"SELECT id, status FROM user_events WHERE status = 'active'")
if err != nil {
t.Fatalf("Status query failed: %v", err)
}
t.Logf("WHERE status = 'active': %d rows", len(statusResult.Rows))
// Verify all returned rows have status = 'active'
statusCorrect := true
for _, row := range statusResult.Rows {
if len(row) > 1 && row[1].ToString() != "active" {
t.Errorf("Found status '%s' which should be filtered out", row[1].ToString())
statusCorrect = false
}
}
if statusCorrect {
t.Logf("String filtering works correctly")
}
// Test 5: Comparison with actual real-world case
t.Log("\nTESTING REAL-WORLD CASE:")
realWorldResult, err := engine.ExecuteSQL(context.Background(),
"SELECT id FROM user_events WHERE id > 10000000 LIMIT 10 OFFSET 5")
if err != nil {
t.Fatalf("Real-world query failed: %v", err)
}
t.Logf("Real-world query returned: %d rows", len(realWorldResult.Rows))
// Check if any IDs are <= 10,000,000 (should be 0)
violationCount := 0
for _, row := range realWorldResult.Rows {
if idVal, err := strconv.ParseInt(row[0].ToString(), 10, 64); err == nil {
if idVal <= 10000000 {
violationCount++
}
}
}
if violationCount == 0 {
t.Logf("Real-world case FIXED: No violations found")
} else {
t.Errorf("Real-world case FAILED: %d violations found", violationCount)
}
}
// TestWhereClauseComparisonOperators tests all comparison operators
func TestWhereClauseComparisonOperators(t *testing.T) {
engine := NewTestSQLEngine()
// Get baseline data
baselineResult, _ := engine.ExecuteSQL(context.Background(), "SELECT id FROM user_events")
if len(baselineResult.Rows) == 0 {
t.Skip("No test data available")
return
}
// Use the second ID as our test value
testId := baselineResult.Rows[1][0].ToString()
operators := []struct {
op string
desc string
expectRows bool
}{
{"=", "equals", true},
{"!=", "not equals", true},
{">", "greater than", false}, // Depends on data
{"<", "less than", true}, // Should have some results
{">=", "greater or equal", true},
{"<=", "less or equal", true},
}
t.Logf("Testing comparison operators with ID = %s", testId)
for _, op := range operators {
sql := "SELECT id FROM user_events WHERE id " + op.op + " " + testId
result, err := engine.ExecuteSQL(context.Background(), sql)
if err != nil {
t.Errorf("Operator %s failed: %v", op.op, err)
continue
}
t.Logf("WHERE id %s %s: %d rows (%s)", op.op, testId, len(result.Rows), op.desc)
// Basic validation - should not return more rows than baseline
if len(result.Rows) > len(baselineResult.Rows) {
t.Errorf("Operator %s returned more rows than baseline", op.op)
}
}
}
|
