sql_verification/
sql_verification.rs

1// Comprehensive verification that Rust Query Builder produces exact SQL results
2// Tests ordering, LIKE operations, NULL handling, and result consistency
3// cargo run --example sql_verification
4
5use rust_queries_builder::Query;
6use key_paths_derive::Keypaths;
7
8#[derive(Debug, Clone, Keypaths)]
9struct Employee {
10    id: u32,
11    name: String,
12    email: String,
13    department: String,
14    salary: f64,
15}
16
17fn create_test_data() -> Vec<Employee> {
18    vec![
19        Employee { id: 1, name: "Alice Johnson".to_string(), email: "alice@example.com".to_string(), department: "Engineering".to_string(), salary: 95000.0 },
20        Employee { id: 2, name: "Bob Smith".to_string(), email: "bob@example.com".to_string(), department: "Engineering".to_string(), salary: 87000.0 },
21        Employee { id: 3, name: "Carol White".to_string(), email: "carol@example.com".to_string(), department: "Sales".to_string(), salary: 75000.0 },
22        Employee { id: 4, name: "David Brown".to_string(), email: "david@example.com".to_string(), department: "Sales".to_string(), salary: 82000.0 },
23        Employee { id: 5, name: "Eve Davis".to_string(), email: "eve@example.com".to_string(), department: "Marketing".to_string(), salary: 71000.0 },
24        Employee { id: 6, name: "Alice Cooper".to_string(), email: "cooper@example.com".to_string(), department: "Engineering".to_string(), salary: 105000.0 },
25    ]
26}
27
28fn print_test(title: &str, passed: bool) {
29    let status = if passed { "✅ PASS" } else { "❌ FAIL" };
30    println!("{} - {}", status, title);
31}
32
33fn main() {
34    let employees = create_test_data();
35    
36    println!("\n╔════════════════════════════════════════════════════════════════╗");
37    println!("║  SQL Equivalence Verification Tests                           ║");
38    println!("╚════════════════════════════════════════════════════════════════╝\n");
39
40    // TEST 1: ORDER BY maintains exact SQL ordering (DESC)
41    println!("Test 1: ORDER BY DESC - Exact ordering");
42    println!("SQL: SELECT * FROM employees ORDER BY salary DESC;");
43    
44    let ordered = Query::new(&employees).order_by_float_desc(Employee::salary_r());
45    let expected_order = vec![105000.0, 95000.0, 87000.0, 82000.0, 75000.0, 71000.0];
46    let actual_order: Vec<f64> = ordered.iter().map(|e| e.salary).collect();
47    
48    let test1_pass = expected_order == actual_order;
49    print_test("ORDER BY salary DESC produces correct order", test1_pass);
50    if !test1_pass {
51        println!("  Expected: {:?}", expected_order);
52        println!("  Got: {:?}", actual_order);
53    }
54
55    // TEST 2: ORDER BY maintains exact SQL ordering (ASC)
56    println!("\nTest 2: ORDER BY ASC - Exact ordering");
57    println!("SQL: SELECT * FROM employees ORDER BY salary ASC;");
58    
59    let ordered_asc = Query::new(&employees).order_by_float(Employee::salary_r());
60    let expected_asc = vec![71000.0, 75000.0, 82000.0, 87000.0, 95000.0, 105000.0];
61    let actual_asc: Vec<f64> = ordered_asc.iter().map(|e| e.salary).collect();
62    
63    let test2_pass = expected_asc == actual_asc;
64    print_test("ORDER BY salary ASC produces correct order", test2_pass);
65    if !test2_pass {
66        println!("  Expected: {:?}", expected_asc);
67        println!("  Got: {:?}", actual_asc);
68    }
69
70    // TEST 3: String ordering (alphabetical)
71    println!("\nTest 3: ORDER BY name (alphabetical)");
72    println!("SQL: SELECT name FROM employees ORDER BY name;");
73    
74    let ordered_names = Query::new(&employees).order_by(Employee::name_r());
75    let expected_names = vec!["Alice Cooper", "Alice Johnson", "Bob Smith", "Carol White", "David Brown", "Eve Davis"];
76    let actual_names: Vec<&str> = ordered_names.iter().map(|e| e.name.as_str()).collect();
77    
78    let test3_pass = expected_names == actual_names;
79    print_test("ORDER BY name produces correct alphabetical order", test3_pass);
80    if !test3_pass {
81        println!("  Expected: {:?}", expected_names);
82        println!("  Got: {:?}", actual_names);
83    }
84
85    // TEST 4: LIKE equivalent - starts with
86    println!("\nTest 4: LIKE 'Alice%' equivalent");
87    println!("SQL: SELECT * FROM employees WHERE name LIKE 'Alice%';");
88    
89    let like_alice_query = Query::new(&employees)
90        .where_(Employee::name_r(), |name| name.starts_with("Alice"));
91    let like_alice = like_alice_query.all();
92    
93    let test4_pass = like_alice.len() == 2 
94        && like_alice.iter().all(|e| e.name.starts_with("Alice"));
95    print_test("LIKE 'Alice%' (starts_with) works correctly", test4_pass);
96    if test4_pass {
97        for emp in &like_alice {
98            println!("  Found: {}", emp.name);
99        }
100    }
101
102    // TEST 5: LIKE equivalent - ends with
103    println!("\nTest 5: LIKE '%son' equivalent");
104    println!("SQL: SELECT * FROM employees WHERE name LIKE '%son';");
105    
106    let like_son_query = Query::new(&employees)
107        .where_(Employee::name_r(), |name| name.ends_with("son"));
108    let like_son = like_son_query.all();
109    
110    let test5_pass = like_son.len() == 1 && like_son[0].name == "Alice Johnson";
111    print_test("LIKE '%son' (ends_with) works correctly", test5_pass);
112    if test5_pass {
113        for emp in &like_son {
114            println!("  Found: {}", emp.name);
115        }
116    }
117
118    // TEST 6: LIKE equivalent - contains
119    println!("\nTest 6: LIKE '%mit%' equivalent");
120    println!("SQL: SELECT * FROM employees WHERE name LIKE '%mit%';");
121    
122    let like_mit_query = Query::new(&employees)
123        .where_(Employee::name_r(), |name| name.contains("mit"));
124    let like_mit = like_mit_query.all();
125    
126    let test6_pass = like_mit.len() == 1 && like_mit[0].name == "Bob Smith";
127    print_test("LIKE '%mit%' (contains) works correctly", test6_pass);
128    if test6_pass {
129        for emp in &like_mit {
130            println!("  Found: {}", emp.name);
131        }
132    }
133
134    // TEST 7: IN clause equivalent
135    println!("\nTest 7: IN clause equivalent");
136    println!("SQL: SELECT * FROM employees WHERE department IN ('Engineering', 'Sales');");
137    
138    let in_depts_query = Query::new(&employees)
139        .where_(Employee::department_r(), |dept| dept == "Engineering" || dept == "Sales");
140    let in_depts = in_depts_query.all();
141    
142    let test7_pass = in_depts.len() == 5 // 3 Engineering + 2 Sales
143        && in_depts.iter().all(|e| e.department == "Engineering" || e.department == "Sales");
144    print_test("IN clause works correctly", test7_pass);
145    println!("  Found {} employees in Engineering or Sales", in_depts.len());
146
147    // TEST 8: BETWEEN equivalent
148    println!("\nTest 8: BETWEEN clause equivalent");
149    println!("SQL: SELECT * FROM employees WHERE salary BETWEEN 75000 AND 90000;");
150    
151    let between_query = Query::new(&employees)
152        .where_(Employee::salary_r(), |&sal| sal >= 75000.0 && sal <= 90000.0);
153    let between = between_query.all();
154    
155    let test8_pass = between.len() == 3;
156    print_test("BETWEEN clause works correctly", test8_pass);
157    println!("  Found {} employees with salary between 75K-90K", between.len());
158
159    // TEST 9: COUNT with WHERE
160    println!("\nTest 9: COUNT with WHERE");
161    println!("SQL: SELECT COUNT(*) FROM employees WHERE department = 'Engineering';");
162    
163    let count_eng = Query::new(&employees)
164        .where_(Employee::department_r(), |dept| dept == "Engineering")
165        .count();
166    
167    let test9_pass = count_eng == 3;
168    print_test("COUNT with WHERE produces correct result", test9_pass);
169    println!("  Count: {} (expected 3)", count_eng);
170
171    // TEST 10: AVG produces exact result
172    println!("\nTest 10: AVG aggregation accuracy");
173    println!("SQL: SELECT AVG(salary) FROM employees WHERE department = 'Engineering';");
174    
175    let avg_sal = Query::new(&employees)
176        .where_(Employee::department_r(), |dept| dept == "Engineering")
177        .avg(Employee::salary_r())
178        .unwrap_or(0.0);
179    
180    let expected_avg = (95000.0 + 87000.0 + 105000.0) / 3.0;
181    let test10_pass = (avg_sal - expected_avg).abs() < 0.01;
182    print_test("AVG produces mathematically correct result", test10_pass);
183    println!("  Average: ${:.2} (expected ${:.2})", avg_sal, expected_avg);
184
185    // TEST 11: MIN and MAX
186    println!("\nTest 11: MIN and MAX aggregations");
187    println!("SQL: SELECT MIN(salary), MAX(salary) FROM employees;");
188    
189    let min_sal = Query::new(&employees).min_float(Employee::salary_r()).unwrap_or(0.0);
190    let max_sal = Query::new(&employees).max_float(Employee::salary_r()).unwrap_or(0.0);
191    
192    let test11_pass = min_sal == 71000.0 && max_sal == 105000.0;
193    print_test("MIN and MAX produce correct results", test11_pass);
194    println!("  MIN: ${:.2}, MAX: ${:.2}", min_sal, max_sal);
195
196    // TEST 12: Complex WHERE with AND/OR
197    println!("\nTest 12: Complex WHERE (AND + OR)");
198    println!("SQL: SELECT * FROM employees WHERE (department = 'Engineering' OR department = 'Sales') AND salary > 80000;");
199    
200    let complex_query = Query::new(&employees)
201        .where_(Employee::department_r(), |dept| dept == "Engineering" || dept == "Sales")
202        .where_(Employee::salary_r(), |&sal| sal > 80000.0);
203    let complex = complex_query.all();
204    
205    let test12_pass = complex.len() == 4; // Alice J ($95k), Bob ($87k), David ($82k), Alice C ($105k)
206    print_test("Complex WHERE clause works correctly", test12_pass);
207    println!("  Found {} employees", complex.len());
208
209    // TEST 13: Case-sensitive string comparison (SQL default)
210    println!("\nTest 13: Case-sensitive comparison (like SQL)");
211    println!("SQL: SELECT * FROM employees WHERE department = 'engineering'; -- should find 0");
212    
213    let case_sensitive = Query::new(&employees)
214        .where_(Employee::department_r(), |dept| dept == "engineering") // lowercase
215        .count();
216    
217    let test13_pass = case_sensitive == 0;
218    print_test("Case-sensitive comparison (SQL default)", test13_pass);
219    println!("  Found {} with lowercase 'engineering' (expected 0)", case_sensitive);
220
221    // TEST 14: Case-insensitive LIKE equivalent
222    println!("\nTest 14: Case-insensitive LIKE (ILIKE equivalent)");
223    println!("SQL: SELECT * FROM employees WHERE LOWER(name) LIKE '%alice%';");
224    
225    let ilike_query = Query::new(&employees)
226        .where_(Employee::name_r(), |name| name.to_lowercase().contains("alice"));
227    let ilike = ilike_query.all();
228    
229    let test14_pass = ilike.len() == 2;
230    print_test("Case-insensitive LIKE works correctly", test14_pass);
231    println!("  Found {} employees with 'alice' (case-insensitive)", ilike.len());
232
233    // TEST 15: LIMIT produces exact subset
234    println!("\nTest 15: LIMIT clause");
235    println!("SQL: SELECT * FROM employees ORDER BY salary DESC LIMIT 3;");
236    
237    let limited = Query::new(&employees)
238        .order_by_float_desc(Employee::salary_r())
239        .into_iter()
240        .take(3)
241        .collect::<Vec<_>>();
242    
243    let test15_pass = limited.len() == 3 
244        && limited[0].salary == 105000.0
245        && limited[1].salary == 95000.0
246        && limited[2].salary == 87000.0;
247    print_test("LIMIT returns correct top-N results", test15_pass);
248    println!("  Top 3 salaries: ${:.0}, ${:.0}, ${:.0}", 
249        limited[0].salary, limited[1].salary, limited[2].salary);
250
251    // TEST 16: GROUP BY produces correct groups
252    println!("\nTest 16: GROUP BY");
253    println!("SQL: SELECT department, COUNT(*) FROM employees GROUP BY department;");
254    
255    let grouped = Query::new(&employees).group_by(Employee::department_r());
256    let eng_count = grouped.get("Engineering").map(|v| v.len()).unwrap_or(0);
257    let sales_count = grouped.get("Sales").map(|v| v.len()).unwrap_or(0);
258    let marketing_count = grouped.get("Marketing").map(|v| v.len()).unwrap_or(0);
259    
260    let test16_pass = eng_count == 3 && sales_count == 2 && marketing_count == 1;
261    print_test("GROUP BY produces correct counts", test16_pass);
262    println!("  Engineering: {}, Sales: {}, Marketing: {}", eng_count, sales_count, marketing_count);
263
264    // TEST 17: Email pattern matching (regex-like)
265    println!("\nTest 17: Email domain filtering (LIKE '%@example.com')");
266    println!("SQL: SELECT * FROM employees WHERE email LIKE '%@example.com';");
267    
268    let example_emails = Query::new(&employees)
269        .where_(Employee::email_r(), |email| email.ends_with("@example.com"))
270        .count();
271    
272    let test17_pass = example_emails == 6; // All employees have @example.com emails
273    print_test("Email pattern matching works correctly", test17_pass);
274    println!("  Found {} employees with @example.com emails", example_emails);
275
276    // Summary
277    println!("\n╔════════════════════════════════════════════════════════════════╗");
278    println!("║  Test Summary                                                  ║");
279    println!("╚════════════════════════════════════════════════════════════════╝\n");
280    
281    let tests = vec![
282        ("ORDER BY DESC", test1_pass),
283        ("ORDER BY ASC", test2_pass),
284        ("ORDER BY name", test3_pass),
285        ("LIKE 'prefix%'", test4_pass),
286        ("LIKE '%suffix'", test5_pass),
287        ("LIKE '%contains%'", test6_pass),
288        ("IN clause", test7_pass),
289        ("BETWEEN clause", test8_pass),
290        ("COUNT", test9_pass),
291        ("AVG accuracy", test10_pass),
292        ("MIN/MAX", test11_pass),
293        ("Complex WHERE", test12_pass),
294        ("Case-sensitive", test13_pass),
295        ("Case-insensitive", test14_pass),
296        ("LIMIT", test15_pass),
297        ("GROUP BY", test16_pass),
298        ("Email patterns", test17_pass),
299    ];
300    
301    let passed = tests.iter().filter(|(_, p)| *p).count();
302    let total = tests.len();
303    
304    println!("Results: {}/{} tests passed", passed, total);
305    
306    if passed == total {
307        println!("\n🎉 All tests PASSED! Rust Query Builder produces exact SQL-equivalent results!");
308        println!("\n✅ Verified:");
309        println!("  • Exact ordering (ASC/DESC)");
310        println!("  • LIKE operations (starts_with, ends_with, contains)");
311        println!("  • IN clause (OR conditions)");
312        println!("  • BETWEEN clause");
313        println!("  • Aggregations (COUNT, AVG, MIN, MAX)");
314        println!("  • Complex WHERE conditions");
315        println!("  • Case sensitivity");
316        println!("  • LIMIT clause");
317        println!("  • GROUP BY");
318        println!("  • Pattern matching");
319    } else {
320        println!("\n⚠️  {} test(s) failed. See details above.", total - passed);
321    }
322}
323
sql_verification/sql_verification.rs

sql_verification/
sql_verification.rs
