1 files changed, 182 insertions, 0 deletions
diff --git a/weed/query/engine/where_validation_test.go b/weed/query/engine/where_validation_test.go
new file mode 100644
index 000000000..4c2d8b903
--- /dev/null
+++ b/weed/query/engine/where_validation_test.go
@@ -0,0 +1,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("\n🎯 TESTING 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)
+		}
+	}
+}