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
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
|
package engine
import (
"context"
"strings"
"testing"
)
// TestSQLEngine_StringFunctionsAndLiterals tests the fixes for string functions and string literals
// This covers the user's reported issues:
// 1. String functions like UPPER(), LENGTH() being treated as aggregation functions
// 2. String literals like 'good' returning empty values
func TestSQLEngine_StringFunctionsAndLiterals(t *testing.T) {
engine := NewTestSQLEngine()
tests := []struct {
name string
query string
expectedCols []string
expectNonEmpty bool
validateFirstRow func(t *testing.T, row []string)
}{
{
name: "String functions - UPPER and LENGTH",
query: "SELECT status, UPPER(status), LENGTH(status) FROM user_events LIMIT 3",
expectedCols: []string{"status", "UPPER(status)", "LENGTH(status)"},
expectNonEmpty: true,
validateFirstRow: func(t *testing.T, row []string) {
if len(row) != 3 {
t.Errorf("Expected 3 columns, got %d", len(row))
return
}
// Status should exist, UPPER should be uppercase version, LENGTH should be numeric
status := row[0]
upperStatus := row[1]
lengthStr := row[2]
if status == "" {
t.Error("Status column should not be empty")
}
if upperStatus == "" {
t.Error("UPPER(status) should not be empty")
}
if lengthStr == "" {
t.Error("LENGTH(status) should not be empty")
}
t.Logf("Status: '%s', UPPER: '%s', LENGTH: '%s'", status, upperStatus, lengthStr)
},
},
{
name: "String literal in SELECT",
query: "SELECT id, user_id, 'good' FROM user_events LIMIT 2",
expectedCols: []string{"id", "user_id", "'good'"},
expectNonEmpty: true,
validateFirstRow: func(t *testing.T, row []string) {
if len(row) != 3 {
t.Errorf("Expected 3 columns, got %d", len(row))
return
}
literal := row[2]
if literal != "good" {
t.Errorf("Expected string literal to be 'good', got '%s'", literal)
}
},
},
{
name: "Mixed: columns, functions, arithmetic, and literals",
query: "SELECT id, UPPER(status), id*2, 'test' FROM user_events LIMIT 2",
expectedCols: []string{"id", "UPPER(status)", "id*2", "'test'"},
expectNonEmpty: true,
validateFirstRow: func(t *testing.T, row []string) {
if len(row) != 4 {
t.Errorf("Expected 4 columns, got %d", len(row))
return
}
// Verify the literal value
if row[3] != "test" {
t.Errorf("Expected literal 'test', got '%s'", row[3])
}
// Verify other values are not empty
for i, val := range row {
if val == "" {
t.Errorf("Column %d should not be empty", i)
}
}
},
},
{
name: "User's original failing query - fixed",
query: "SELECT status, action, user_type, UPPER(action), LENGTH(action) FROM user_events LIMIT 2",
expectedCols: []string{"status", "action", "user_type", "UPPER(action)", "LENGTH(action)"},
expectNonEmpty: true,
validateFirstRow: func(t *testing.T, row []string) {
if len(row) != 5 {
t.Errorf("Expected 5 columns, got %d", len(row))
return
}
// All values should be non-empty
for i, val := range row {
if val == "" {
t.Errorf("Column %d (%s) should not be empty", i, []string{"status", "action", "user_type", "UPPER(action)", "LENGTH(action)"}[i])
}
}
// UPPER should be uppercase
action := row[1]
upperAction := row[3]
if action != "" && upperAction != "" {
if upperAction != action && upperAction != strings.ToUpper(action) {
t.Logf("Note: UPPER(%s) = %s (may be expected)", action, upperAction)
}
}
},
},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
result, err := engine.ExecuteSQL(context.Background(), tt.query)
if err != nil {
t.Fatalf("Query failed: %v", err)
}
if result.Error != nil {
t.Fatalf("Query returned error: %v", result.Error)
}
// Verify we got results
if tt.expectNonEmpty && len(result.Rows) == 0 {
t.Fatal("Query returned no rows")
}
// Verify column count
if len(result.Columns) != len(tt.expectedCols) {
t.Errorf("Expected %d columns, got %d", len(tt.expectedCols), len(result.Columns))
}
// Check column names
for i, expectedCol := range tt.expectedCols {
if i < len(result.Columns) && result.Columns[i] != expectedCol {
t.Errorf("Expected column %d to be '%s', got '%s'", i, expectedCol, result.Columns[i])
}
}
// Validate first row if provided
if len(result.Rows) > 0 && tt.validateFirstRow != nil {
firstRow := result.Rows[0]
stringRow := make([]string, len(firstRow))
for i, val := range firstRow {
stringRow[i] = val.ToString()
}
tt.validateFirstRow(t, stringRow)
}
// Log results for debugging
t.Logf("Query: %s", tt.query)
t.Logf("Columns: %v", result.Columns)
for i, row := range result.Rows {
values := make([]string, len(row))
for j, val := range row {
values[j] = val.ToString()
}
t.Logf("Row %d: %v", i, values)
}
})
}
}
// TestSQLEngine_StringFunctionErrorHandling tests error cases for string functions
func TestSQLEngine_StringFunctionErrorHandling(t *testing.T) {
engine := NewTestSQLEngine()
// This should now work (previously would error as "unsupported aggregation function")
result, err := engine.ExecuteSQL(context.Background(), "SELECT UPPER(status) FROM user_events LIMIT 1")
if err != nil {
t.Fatalf("UPPER function should work, got error: %v", err)
}
if result.Error != nil {
t.Fatalf("UPPER function should work, got query error: %v", result.Error)
}
t.Logf("UPPER function works correctly")
// This should now work (previously would error as "unsupported aggregation function")
result2, err2 := engine.ExecuteSQL(context.Background(), "SELECT LENGTH(action) FROM user_events LIMIT 1")
if err2 != nil {
t.Fatalf("LENGTH function should work, got error: %v", err2)
}
if result2.Error != nil {
t.Fatalf("LENGTH function should work, got query error: %v", result2.Error)
}
t.Logf("LENGTH function works correctly")
}
|
