SeaweedFS is a distributed storage system for object storage (S3), file systems, Iceberg table buckets, and Lance vector buckets, designed to handle billions of files with O(1) disk access and effortless horizontal scaling.
Go blob-storagecloud-drivedistributed-file-systemdistributed-storageerasure-codingfusehadoop-hdfshdfskuberneteslancelancedbobject-storageposixs3s3-storageseaweedfstiered-file-systemvector-search
package engine
import (
"testing"
"github.com/seaweedfs/seaweedfs/weed/pb/schema_pb"
"github.com/stretchr/testify/assert"
)
// TestSQLAliasResolution tests the complete SQL alias resolution functionality
func TestSQLAliasResolution(t *testing.T) {
engine := NewTestSQLEngine()
t.Run("ResolveColumnAlias", func(t *testing.T) {
// Test the helper function for resolving aliases
// Create SELECT expressions with aliases
selectExprs := []SelectExpr{
&AliasedExpr{
Expr: &ColName{Name: stringValue("_ts_ns")},
As: aliasValue("ts"),
},
&AliasedExpr{
Expr: &ColName{Name: stringValue("id")},
As: aliasValue("record_id"),
},
}
// Test alias resolution
resolved := engine.resolveColumnAlias("ts", selectExprs)
assert.Equal(t, "_ts_ns", resolved, "Should resolve 'ts' alias to '_ts_ns'")
resolved = engine.resolveColumnAlias("record_id", selectExprs)
assert.Equal(t, "id", resolved, "Should resolve 'record_id' alias to 'id'")
// Test non-aliased column (should return as-is)
resolved = engine.resolveColumnAlias("some_other_column", selectExprs)
assert.Equal(t, "some_other_column", resolved, "Non-aliased columns should return unchanged")
})
t.Run("SingleAliasInWhere", func(t *testing.T) {
// Test using a single alias in WHERE clause
testRecord := &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: 12345}},
},
}
// Parse SQL with alias in WHERE
sql := "SELECT _ts_ns AS ts, id FROM test WHERE ts = 1756947416566456262"
stmt, err := ParseSQL(sql)
assert.NoError(t, err, "Should parse SQL with alias in WHERE")
selectStmt := stmt.(*SelectStatement)
// Build predicate with context (for alias resolution)
predicate, err := engine.buildPredicateWithContext(selectStmt.Where.Expr, selectStmt.SelectExprs)
assert.NoError(t, err, "Should build predicate with alias resolution")
// Test the predicate
result := predicate(testRecord)
assert.True(t, result, "Predicate should match using alias 'ts' for '_ts_ns'")
// Test with non-matching value
sql2 := "SELECT _ts_ns AS ts, id FROM test WHERE ts = 999999"
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(testRecord)
assert.False(t, result2, "Predicate should not match different value")
})
t.Run("MultipleAliasesInWhere", func(t *testing.T) {
// Test using multiple aliases in WHERE clause
testRecord := &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: 82460}},
},
}
// Parse SQL with multiple aliases in WHERE
sql := "SELECT _ts_ns AS ts, id AS record_id FROM test WHERE ts = 1756947416566456262 AND record_id = 82460"
stmt, err := ParseSQL(sql)
assert.NoError(t, err, "Should parse SQL with multiple aliases")
selectStmt := stmt.(*SelectStatement)
// Build predicate with context
predicate, err := engine.buildPredicateWithContext(selectStmt.Where.Expr, selectStmt.SelectExprs)
assert.NoError(t, err, "Should build predicate with multiple alias resolution")
// Test the predicate - should match both conditions
result := predicate(testRecord)
assert.True(t, result, "Should match both aliased conditions")
// Test with one condition not matching
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: 99999}}, // Different ID
},
}
result2 := predicate(testRecord2)
assert.False(t, result2, "Should not match when one alias condition fails")
})
t.Run("RangeQueryWithAliases", func(t *testing.T) {
// Test range queries using aliases
testRecords := []*schema_pb.RecordValue{
{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: 1756947416566456260}}, // Below range
},
},
{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: 1756947416566456262}}, // In range
},
},
{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: 1756947416566456265}}, // Above range
},
},
}
// Test range query with alias
sql := "SELECT _ts_ns AS ts FROM test WHERE ts > 1756947416566456261 AND ts < 1756947416566456264"
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 below range")
assert.True(t, predicate(testRecords[1]), "Should match record in range")
assert.False(t, predicate(testRecords[2]), "Should not match record above range")
})
t.Run("MixedAliasAndDirectColumn", func(t *testing.T) {
// Test mixing aliased and non-aliased columns in WHERE
testRecord := &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: 82460}},
"status": {Kind: &schema_pb.Value_StringValue{StringValue: "active"}},
},
}
// Use alias for one column, direct name for another
sql := "SELECT _ts_ns AS ts, id, status FROM test WHERE ts = 1756947416566456262 AND status = 'active'"
stmt, err := ParseSQL(sql)
assert.NoError(t, err, "Should parse mixed alias/direct query")
selectStmt := stmt.(*SelectStatement)
predicate, err := engine.buildPredicateWithContext(selectStmt.Where.Expr, selectStmt.SelectExprs)
assert.NoError(t, err, "Should build mixed predicate")
result := predicate(testRecord)
assert.True(t, result, "Should match with mixed alias and direct column usage")
})
t.Run("AliasCompatibilityWithTimestampFixes", func(t *testing.T) {
// Test that alias resolution works with the timestamp precision fixes
largeTimestamp := int64(1756947416566456262) // Large nanosecond timestamp
testRecord := &schema_pb.RecordValue{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: largeTimestamp}},
"id": {Kind: &schema_pb.Value_Int64Value{Int64Value: 897795}},
},
}
// Test that large timestamp precision is maintained with aliases
sql := "SELECT _ts_ns AS ts, id FROM test WHERE ts = 1756947416566456262"
stmt, err := ParseSQL(sql)
assert.NoError(t, err)
selectStmt := stmt.(*SelectStatement)
predicate, err := engine.buildPredicateWithContext(selectStmt.Where.Expr, selectStmt.SelectExprs)
assert.NoError(t, err)
result := predicate(testRecord)
assert.True(t, result, "Large timestamp precision should be maintained with aliases")
// Test precision with off-by-one (should not match)
sql2 := "SELECT _ts_ns AS ts, id FROM test WHERE ts = 1756947416566456263" // +1
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(testRecord)
assert.False(t, result2, "Should not match timestamp differing by 1 nanosecond")
})
t.Run("EdgeCasesAndErrorHandling", func(t *testing.T) {
// Test edge cases and error conditions
// Test with nil SelectExprs
predicate, err := engine.buildPredicateWithContext(&ComparisonExpr{
Left: &ColName{Name: stringValue("test_col")},
Operator: "=",
Right: &SQLVal{Type: IntVal, Val: []byte("123")},
}, nil)
assert.NoError(t, err, "Should handle nil SelectExprs gracefully")
assert.NotNil(t, predicate, "Should return valid predicate even without aliases")
// Test alias resolution with empty SelectExprs
resolved := engine.resolveColumnAlias("test_col", []SelectExpr{})
assert.Equal(t, "test_col", resolved, "Should return original name with empty SelectExprs")
// Test alias resolution with nil SelectExprs
resolved = engine.resolveColumnAlias("test_col", nil)
assert.Equal(t, "test_col", resolved, "Should return original name with nil SelectExprs")
})
t.Run("ComparisonOperators", func(t *testing.T) {
// Test all comparison operators work with aliases
testRecord := &schema_pb.RecordValue{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: 1000}},
},
}
operators := []struct {
op string
value string
expected bool
}{
{"=", "1000", true},
{"=", "999", false},
{">", "999", true},
{">", "1000", false},
{">=", "1000", true},
{">=", "1001", false},
{"<", "1001", true},
{"<", "1000", false},
{"<=", "1000", true},
{"<=", "999", false},
}
for _, test := range operators {
t.Run(test.op+"_"+test.value, func(t *testing.T) {
sql := "SELECT _ts_ns AS ts FROM test WHERE ts " + test.op + " " + test.value
stmt, err := ParseSQL(sql)
assert.NoError(t, err, "Should parse operator: %s", test.op)
selectStmt := stmt.(*SelectStatement)
predicate, err := engine.buildPredicateWithContext(selectStmt.Where.Expr, selectStmt.SelectExprs)
assert.NoError(t, err, "Should build predicate for operator: %s", test.op)
result := predicate(testRecord)
assert.Equal(t, test.expected, result, "Operator %s with value %s should return %v", test.op, test.value, test.expected)
})
}
})
t.Run("BackwardCompatibility", func(t *testing.T) {
// Ensure non-alias queries still work exactly as before
testRecord := &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: 12345}},
},
}
// Test traditional query (no aliases)
sql := "SELECT _ts_ns, id FROM test WHERE _ts_ns = 1756947416566456262"
stmt, err := ParseSQL(sql)
assert.NoError(t, err)
selectStmt := stmt.(*SelectStatement)
// Should work with both old and new predicate building methods
predicateOld, err := engine.buildPredicate(selectStmt.Where.Expr)
assert.NoError(t, err, "Old buildPredicate method should still work")
predicateNew, err := engine.buildPredicateWithContext(selectStmt.Where.Expr, selectStmt.SelectExprs)
assert.NoError(t, err, "New buildPredicateWithContext should work for non-alias queries")
// Both should produce the same result
resultOld := predicateOld(testRecord)
resultNew := predicateNew(testRecord)
assert.True(t, resultOld, "Old method should match")
assert.True(t, resultNew, "New method should match")
assert.Equal(t, resultOld, resultNew, "Both methods should produce identical results")
})
}
// TestAliasIntegrationWithProductionScenarios tests real-world usage patterns
func TestAliasIntegrationWithProductionScenarios(t *testing.T) {
engine := NewTestSQLEngine()
t.Run("OriginalFailingQuery", func(t *testing.T) {
// Test the exact query pattern that was originally failing
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}},
},
}
// This was the original failing pattern
sql := "SELECT id, _ts_ns AS ts FROM ecommerce.user_events WHERE ts = 1756913789829292386"
stmt, err := ParseSQL(sql)
assert.NoError(t, err, "Should parse the originally failing query pattern")
selectStmt := stmt.(*SelectStatement)
predicate, err := engine.buildPredicateWithContext(selectStmt.Where.Expr, selectStmt.SelectExprs)
assert.NoError(t, err, "Should build predicate for originally failing pattern")
result := predicate(testRecord)
assert.True(t, result, "Should now work for the originally failing query pattern")
})
t.Run("ComplexProductionQuery", func(t *testing.T) {
// Test a more complex production-like query
testRecord := &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}},
"user_id": {Kind: &schema_pb.Value_StringValue{StringValue: "user123"}},
"event_type": {Kind: &schema_pb.Value_StringValue{StringValue: "click"}},
},
}
sql := `SELECT
id AS event_id,
_ts_ns AS event_time,
user_id AS uid,
event_type AS action
FROM ecommerce.user_events
WHERE event_time = 1756947416566456262
AND uid = 'user123'
AND action = 'click'`
stmt, err := ParseSQL(sql)
assert.NoError(t, err, "Should parse complex production query")
selectStmt := stmt.(*SelectStatement)
predicate, err := engine.buildPredicateWithContext(selectStmt.Where.Expr, selectStmt.SelectExprs)
assert.NoError(t, err, "Should build predicate for complex query")
result := predicate(testRecord)
assert.True(t, result, "Should match complex production query with multiple aliases")
// Test partial match failure
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}},
"user_id": {Kind: &schema_pb.Value_StringValue{StringValue: "user999"}}, // Different user
"event_type": {Kind: &schema_pb.Value_StringValue{StringValue: "click"}},
},
}
result2 := predicate(testRecord2)
assert.False(t, result2, "Should not match when one aliased condition fails")
})
t.Run("PerformanceRegression", func(t *testing.T) {
// Ensure alias resolution doesn't significantly impact performance
testRecord := &schema_pb.RecordValue{
Fields: map[string]*schema_pb.Value{
"_ts_ns": {Kind: &schema_pb.Value_Int64Value{Int64Value: 1756947416566456262}},
},
}
// Build predicates for comparison
sqlWithAlias := "SELECT _ts_ns AS ts FROM test WHERE ts = 1756947416566456262"
sqlWithoutAlias := "SELECT _ts_ns FROM test WHERE _ts_ns = 1756947416566456262"
stmtWithAlias, err := ParseSQL(sqlWithAlias)
assert.NoError(t, err)
stmtWithoutAlias, err := ParseSQL(sqlWithoutAlias)
assert.NoError(t, err)
selectStmtWithAlias := stmtWithAlias.(*SelectStatement)
selectStmtWithoutAlias := stmtWithoutAlias.(*SelectStatement)
// Both should build successfully
predicateWithAlias, err := engine.buildPredicateWithContext(selectStmtWithAlias.Where.Expr, selectStmtWithAlias.SelectExprs)
assert.NoError(t, err)
predicateWithoutAlias, err := engine.buildPredicateWithContext(selectStmtWithoutAlias.Where.Expr, selectStmtWithoutAlias.SelectExprs)
assert.NoError(t, err)
// Both should produce the same logical result
resultWithAlias := predicateWithAlias(testRecord)
resultWithoutAlias := predicateWithoutAlias(testRecord)
assert.True(t, resultWithAlias, "Alias query should work")
assert.True(t, resultWithoutAlias, "Non-alias query should work")
assert.Equal(t, resultWithAlias, resultWithoutAlias, "Both should produce same result")
})
}