Skip to content

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.

6.0 KB
Raw Blame
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")
}