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
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
|
package engine
import (
"strconv"
"testing"
"github.com/seaweedfs/seaweedfs/weed/pb/schema_pb"
"github.com/stretchr/testify/assert"
)
// TestAliasTimestampIntegration tests that SQL aliases work correctly with timestamp query fixes
func TestAliasTimestampIntegration(t *testing.T) {
engine := NewTestSQLEngine()
// Use the exact timestamps from the original failing production queries
originalFailingTimestamps := []int64{
1756947416566456262, // Original failing query 1
1756947416566439304, // Original failing query 2
1756913789829292386, // Current data timestamp
}
t.Run("AliasWithLargeTimestamps", func(t *testing.T) {
for i, timestamp := range originalFailingTimestamps {
t.Run("Timestamp_"+strconv.Itoa(i+1), func(t *testing.T) {
// Create test record
testRecord := &schema_pb.RecordValue{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: timestamp}},
"id": {Kind: &schema_pb.Value_Int64Value{Int64Value: int64(1000 + i)}},
},
}
// Test equality with alias (this was the originally failing pattern)
sql := "SELECT _ts_ns AS ts, id FROM test WHERE ts = " + strconv.FormatInt(timestamp, 10)
stmt, err := ParseSQL(sql)
assert.NoError(t, err, "Should parse alias equality query for timestamp %d", timestamp)
selectStmt := stmt.(*SelectStatement)
predicate, err := engine.buildPredicateWithContext(selectStmt.Where.Expr, selectStmt.SelectExprs)
assert.NoError(t, err, "Should build predicate for large timestamp with alias")
result := predicate(testRecord)
assert.True(t, result, "Should match exact large timestamp using alias")
// Test precision - off by 1 nanosecond should not match
sqlOffBy1 := "SELECT _ts_ns AS ts, id FROM test WHERE ts = " + strconv.FormatInt(timestamp+1, 10)
stmt2, err := ParseSQL(sqlOffBy1)
assert.NoError(t, err)
selectStmt2 := stmt2.(*SelectStatement)
predicate2, err := engine.buildPredicateWithContext(selectStmt2.Where.Expr, selectStmt2.SelectExprs)
assert.NoError(t, err)
result2 := predicate2(testRecord)
assert.False(t, result2, "Should not match timestamp off by 1 nanosecond with alias")
})
}
})
t.Run("AliasWithTimestampRangeQueries", func(t *testing.T) {
timestamp := int64(1756947416566456262)
testRecords := []*schema_pb.RecordValue{
{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: timestamp - 2}}, // Before range
},
},
{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: timestamp}}, // In range
},
},
{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: timestamp + 2}}, // After range
},
},
}
// Test range query with alias
sql := "SELECT _ts_ns AS ts FROM test WHERE ts >= " +
strconv.FormatInt(timestamp-1, 10) + " AND ts <= " +
strconv.FormatInt(timestamp+1, 10)
stmt, err := ParseSQL(sql)
assert.NoError(t, err, "Should parse range query with alias")
selectStmt := stmt.(*SelectStatement)
predicate, err := engine.buildPredicateWithContext(selectStmt.Where.Expr, selectStmt.SelectExprs)
assert.NoError(t, err, "Should build range predicate with alias")
// Test each record
assert.False(t, predicate(testRecords[0]), "Should not match record before range")
assert.True(t, predicate(testRecords[1]), "Should match record in range")
assert.False(t, predicate(testRecords[2]), "Should not match record after range")
})
t.Run("AliasWithTimestampPrecisionEdgeCases", func(t *testing.T) {
// Test maximum int64 value
maxInt64 := int64(9223372036854775807)
testRecord := &schema_pb.RecordValue{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: maxInt64}},
},
}
// Test with alias
sql := "SELECT _ts_ns AS ts FROM test WHERE ts = " + strconv.FormatInt(maxInt64, 10)
stmt, err := ParseSQL(sql)
assert.NoError(t, err, "Should parse max int64 with alias")
selectStmt := stmt.(*SelectStatement)
predicate, err := engine.buildPredicateWithContext(selectStmt.Where.Expr, selectStmt.SelectExprs)
assert.NoError(t, err, "Should build predicate for max int64 with alias")
result := predicate(testRecord)
assert.True(t, result, "Should handle max int64 value correctly with alias")
// Test minimum value
minInt64 := int64(-9223372036854775808)
testRecord2 := &schema_pb.RecordValue{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: minInt64}},
},
}
sql2 := "SELECT _ts_ns AS ts FROM test WHERE ts = " + strconv.FormatInt(minInt64, 10)
stmt2, err := ParseSQL(sql2)
assert.NoError(t, err)
selectStmt2 := stmt2.(*SelectStatement)
predicate2, err := engine.buildPredicateWithContext(selectStmt2.Where.Expr, selectStmt2.SelectExprs)
assert.NoError(t, err)
result2 := predicate2(testRecord2)
assert.True(t, result2, "Should handle min int64 value correctly with alias")
})
t.Run("MultipleAliasesWithTimestamps", func(t *testing.T) {
// Test multiple aliases including timestamps
timestamp1 := int64(1756947416566456262)
timestamp2 := int64(1756913789829292386)
testRecord := &schema_pb.RecordValue{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: timestamp1}},
"created_at": {Kind: &schema_pb.Value_Int64Value{Int64Value: timestamp2}},
"id": {Kind: &schema_pb.Value_Int64Value{Int64Value: 12345}},
},
}
// Use multiple timestamp aliases in WHERE
sql := "SELECT _ts_ns AS event_time, created_at AS created_time, id AS record_id FROM test " +
"WHERE event_time = " + strconv.FormatInt(timestamp1, 10) +
" AND created_time = " + strconv.FormatInt(timestamp2, 10) +
" AND record_id = 12345"
stmt, err := ParseSQL(sql)
assert.NoError(t, err, "Should parse complex query with multiple timestamp aliases")
selectStmt := stmt.(*SelectStatement)
predicate, err := engine.buildPredicateWithContext(selectStmt.Where.Expr, selectStmt.SelectExprs)
assert.NoError(t, err, "Should build predicate for multiple timestamp aliases")
result := predicate(testRecord)
assert.True(t, result, "Should match complex query with multiple timestamp aliases")
})
t.Run("CompatibilityWithExistingTimestampFixes", func(t *testing.T) {
// Verify that all the timestamp fixes (precision, scan boundaries, etc.) still work with aliases
largeTimestamp := int64(1756947416566456262)
// Test all comparison operators with aliases
operators := []struct {
sql string
value int64
expected bool
}{
{"ts = " + strconv.FormatInt(largeTimestamp, 10), largeTimestamp, true},
{"ts = " + strconv.FormatInt(largeTimestamp+1, 10), largeTimestamp, false},
{"ts > " + strconv.FormatInt(largeTimestamp-1, 10), largeTimestamp, true},
{"ts > " + strconv.FormatInt(largeTimestamp, 10), largeTimestamp, false},
{"ts >= " + strconv.FormatInt(largeTimestamp, 10), largeTimestamp, true},
{"ts >= " + strconv.FormatInt(largeTimestamp+1, 10), largeTimestamp, false},
{"ts < " + strconv.FormatInt(largeTimestamp+1, 10), largeTimestamp, true},
{"ts < " + strconv.FormatInt(largeTimestamp, 10), largeTimestamp, false},
{"ts <= " + strconv.FormatInt(largeTimestamp, 10), largeTimestamp, true},
{"ts <= " + strconv.FormatInt(largeTimestamp-1, 10), largeTimestamp, false},
}
for _, op := range operators {
t.Run(op.sql, func(t *testing.T) {
testRecord := &schema_pb.RecordValue{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: op.value}},
},
}
sql := "SELECT _ts_ns AS ts FROM test WHERE " + op.sql
stmt, err := ParseSQL(sql)
assert.NoError(t, err, "Should parse: %s", op.sql)
selectStmt := stmt.(*SelectStatement)
predicate, err := engine.buildPredicateWithContext(selectStmt.Where.Expr, selectStmt.SelectExprs)
assert.NoError(t, err, "Should build predicate for: %s", op.sql)
result := predicate(testRecord)
assert.Equal(t, op.expected, result, "Alias operator test failed for: %s", op.sql)
})
}
})
t.Run("ProductionScenarioReproduction", func(t *testing.T) {
// Reproduce the exact production scenario that was originally failing
// This was the original failing pattern from the user
originalFailingSQL := "select id, _ts_ns as ts from ecommerce.user_events where ts = 1756913789829292386"
testRecord := &schema_pb.RecordValue{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: 1756913789829292386}},
"id": {Kind: &schema_pb.Value_Int64Value{Int64Value: 82460}},
},
}
stmt, err := ParseSQL(originalFailingSQL)
assert.NoError(t, err, "Should parse the exact originally failing production query")
selectStmt := stmt.(*SelectStatement)
predicate, err := engine.buildPredicateWithContext(selectStmt.Where.Expr, selectStmt.SelectExprs)
assert.NoError(t, err, "Should build predicate for original failing query")
result := predicate(testRecord)
assert.True(t, result, "The originally failing production query should now work perfectly")
// Also test the other originally failing timestamp
originalFailingSQL2 := "select id, _ts_ns as ts from ecommerce.user_events where ts = 1756947416566456262"
testRecord2 := &schema_pb.RecordValue{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: 1756947416566456262}},
"id": {Kind: &schema_pb.Value_Int64Value{Int64Value: 897795}},
},
}
stmt2, err := ParseSQL(originalFailingSQL2)
assert.NoError(t, err)
selectStmt2 := stmt2.(*SelectStatement)
predicate2, err := engine.buildPredicateWithContext(selectStmt2.Where.Expr, selectStmt2.SelectExprs)
assert.NoError(t, err)
result2 := predicate2(testRecord2)
assert.True(t, result2, "The second originally failing production query should now work perfectly")
})
}
|
