sql_cli/data/
query_engine.rs

1use anyhow::Result;
2use std::sync::Arc;
3use std::time::Instant;
4use tracing::{debug, info};
5
6use crate::data::arithmetic_evaluator::ArithmeticEvaluator;
7use crate::data::data_view::DataView;
8use crate::data::datatable::{DataColumn, DataRow, DataTable};
9use crate::data::recursive_where_evaluator::RecursiveWhereEvaluator;
10use crate::sql::recursive_parser::{
11    OrderByColumn, Parser, SelectItem, SelectStatement, SortDirection,
12};
13
14/// Query engine that executes SQL directly on DataTable
15pub struct QueryEngine {
16    case_insensitive: bool,
17}
18
19impl QueryEngine {
20    pub fn new() -> Self {
21        Self {
22            case_insensitive: false,
23        }
24    }
25
26    /// Find a column name similar to the given name using edit distance
27    fn find_similar_column(&self, table: &DataTable, name: &str) -> Option<String> {
28        let columns = table.column_names();
29        let mut best_match: Option<(String, usize)> = None;
30
31        for col in columns {
32            let distance = self.edit_distance(&col.to_lowercase(), &name.to_lowercase());
33            // Only suggest if distance is small (likely a typo)
34            // Allow up to 3 edits for longer names
35            let max_distance = if name.len() > 10 { 3 } else { 2 };
36            if distance <= max_distance {
37                match &best_match {
38                    None => best_match = Some((col, distance)),
39                    Some((_, best_dist)) if distance < *best_dist => {
40                        best_match = Some((col, distance));
41                    }
42                    _ => {}
43                }
44            }
45        }
46
47        best_match.map(|(name, _)| name)
48    }
49
50    /// Calculate Levenshtein edit distance between two strings
51    fn edit_distance(&self, s1: &str, s2: &str) -> usize {
52        let len1 = s1.len();
53        let len2 = s2.len();
54        let mut matrix = vec![vec![0; len2 + 1]; len1 + 1];
55
56        for i in 0..=len1 {
57            matrix[i][0] = i;
58        }
59        for j in 0..=len2 {
60            matrix[0][j] = j;
61        }
62
63        for (i, c1) in s1.chars().enumerate() {
64            for (j, c2) in s2.chars().enumerate() {
65                let cost = if c1 == c2 { 0 } else { 1 };
66                matrix[i + 1][j + 1] = std::cmp::min(
67                    matrix[i][j + 1] + 1, // deletion
68                    std::cmp::min(
69                        matrix[i + 1][j] + 1, // insertion
70                        matrix[i][j] + cost,  // substitution
71                    ),
72                );
73            }
74        }
75
76        matrix[len1][len2]
77    }
78
79    pub fn with_case_insensitive(case_insensitive: bool) -> Self {
80        Self { case_insensitive }
81    }
82
83    /// Execute a SQL query on a DataTable and return a DataView (for backward compatibility)
84    pub fn execute(&self, table: Arc<DataTable>, sql: &str) -> Result<DataView> {
85        let start_time = Instant::now();
86
87        // Parse the SQL query
88        let parse_start = Instant::now();
89        let mut parser = Parser::new(sql);
90        let statement = parser
91            .parse()
92            .map_err(|e| anyhow::anyhow!("Parse error: {}", e))?;
93        let parse_duration = parse_start.elapsed();
94
95        // Convert SelectStatement to DataView operations
96        let build_start = Instant::now();
97        let result = self.build_view(table, statement)?;
98        let build_duration = build_start.elapsed();
99
100        let total_duration = start_time.elapsed();
101        info!(
102            "Query execution complete: parse={:?}, build={:?}, total={:?}, rows={}",
103            parse_duration,
104            build_duration,
105            total_duration,
106            result.row_count()
107        );
108
109        Ok(result)
110    }
111
112    /// Build a DataView from a parsed SQL statement
113    fn build_view(&self, table: Arc<DataTable>, statement: SelectStatement) -> Result<DataView> {
114        // Start with all rows visible
115        let mut visible_rows: Vec<usize> = (0..table.row_count()).collect();
116
117        // Apply WHERE clause filtering using recursive evaluator
118        if let Some(where_clause) = &statement.where_clause {
119            let total_rows = table.row_count();
120            debug!("QueryEngine: Applying WHERE clause to {} rows", total_rows);
121            debug!("QueryEngine: WHERE clause = {:?}", where_clause);
122
123            let filter_start = Instant::now();
124            // Filter visible rows based on WHERE clause
125            let mut filtered_rows = Vec::new();
126            for row_idx in visible_rows {
127                // Only log for first few rows to avoid performance impact
128                if row_idx < 3 {
129                    debug!("QueryEngine: Evaluating WHERE clause for row {}", row_idx);
130                }
131                let evaluator =
132                    RecursiveWhereEvaluator::with_case_insensitive(&*table, self.case_insensitive);
133                match evaluator.evaluate(where_clause, row_idx) {
134                    Ok(result) => {
135                        if row_idx < 3 {
136                            debug!("QueryEngine: Row {} WHERE result: {}", row_idx, result);
137                        }
138                        if result {
139                            filtered_rows.push(row_idx);
140                        }
141                    }
142                    Err(e) => {
143                        if row_idx < 3 {
144                            debug!(
145                                "QueryEngine: WHERE evaluation error for row {}: {}",
146                                row_idx, e
147                            );
148                        }
149                        // Propagate WHERE clause errors instead of silently ignoring them
150                        return Err(e);
151                    }
152                }
153            }
154            visible_rows = filtered_rows;
155            let filter_duration = filter_start.elapsed();
156            info!(
157                "WHERE clause filtering: {} rows -> {} rows in {:?}",
158                total_rows,
159                visible_rows.len(),
160                filter_duration
161            );
162
163            // Debug log moved to info level above with timing
164        }
165
166        // Create initial DataView with filtered rows
167        let mut view = DataView::new(table.clone());
168        view = view.with_rows(visible_rows);
169
170        // Apply column projection or computed expressions (SELECT clause) - do this AFTER filtering
171        if !statement.select_items.is_empty() {
172            // Check if we have ANY non-star items (not just the first one)
173            let has_non_star_items = statement
174                .select_items
175                .iter()
176                .any(|item| !matches!(item, SelectItem::Star));
177
178            // Apply select items if:
179            // 1. We have computed expressions or explicit columns
180            // 2. OR we have a mix of star and other items (e.g., SELECT *, computed_col)
181            if has_non_star_items || statement.select_items.len() > 1 {
182                view = self.apply_select_items(view, &statement.select_items)?;
183            }
184            // If it's just a single star, no projection needed
185        } else if !statement.columns.is_empty() && statement.columns[0] != "*" {
186            // Fallback to legacy column projection for backward compatibility
187            let column_indices = self.resolve_column_indices(&*table, &statement.columns)?;
188            view = view.with_columns(column_indices);
189        }
190
191        // Apply ORDER BY sorting
192        if let Some(order_by_columns) = &statement.order_by {
193            if !order_by_columns.is_empty() {
194                view = self.apply_multi_order_by(view, order_by_columns)?;
195            }
196        }
197
198        // Apply LIMIT/OFFSET
199        if let Some(limit) = statement.limit {
200            let offset = statement.offset.unwrap_or(0);
201            view = view.with_limit(limit, offset);
202        }
203
204        Ok(view)
205    }
206
207    /// Resolve column names to indices
208    fn resolve_column_indices(&self, table: &DataTable, columns: &[String]) -> Result<Vec<usize>> {
209        let mut indices = Vec::new();
210        let table_columns = table.column_names();
211
212        for col_name in columns {
213            let index = table_columns
214                .iter()
215                .position(|c| c.eq_ignore_ascii_case(col_name))
216                .ok_or_else(|| {
217                    let suggestion = self.find_similar_column(table, col_name);
218                    match suggestion {
219                        Some(similar) => anyhow::anyhow!(
220                            "Column '{}' not found. Did you mean '{}'?",
221                            col_name,
222                            similar
223                        ),
224                        None => anyhow::anyhow!("Column '{}' not found", col_name),
225                    }
226                })?;
227            indices.push(index);
228        }
229
230        Ok(indices)
231    }
232
233    /// Apply SELECT items (columns and computed expressions) to create new view
234    fn apply_select_items(&self, view: DataView, select_items: &[SelectItem]) -> Result<DataView> {
235        // Check if we need to create computed columns
236        let has_computed_expressions = select_items
237            .iter()
238            .any(|item| matches!(item, SelectItem::Expression { .. }));
239
240        if !has_computed_expressions {
241            // Simple case: only columns, use existing projection logic
242            let column_indices = self.resolve_select_columns(view.source(), select_items)?;
243            return Ok(view.with_columns(column_indices));
244        }
245
246        // Complex case: we have computed expressions
247        // IMPORTANT: We create a PROJECTED view, not a new table
248        // This preserves the original DataTable reference
249
250        let source_table = view.source();
251        let visible_rows = view.visible_row_indices();
252
253        // Create a temporary table just for the computed result view
254        // But this table is only used for the current query result
255        let mut computed_table = DataTable::new("query_result");
256
257        // First, expand any Star selectors to actual columns
258        let mut expanded_items = Vec::new();
259        for item in select_items {
260            match item {
261                SelectItem::Star => {
262                    // Expand * to all columns from source table
263                    for col_name in source_table.column_names() {
264                        expanded_items.push(SelectItem::Column(col_name));
265                    }
266                }
267                _ => expanded_items.push(item.clone()),
268            }
269        }
270
271        // Add columns based on expanded SelectItems, handling duplicates
272        let mut column_name_counts: std::collections::HashMap<String, usize> =
273            std::collections::HashMap::new();
274
275        for item in &expanded_items {
276            let base_name = match item {
277                SelectItem::Column(name) => name.clone(),
278                SelectItem::Expression { alias, .. } => alias.clone(),
279                SelectItem::Star => unreachable!("Star should have been expanded"),
280            };
281
282            // Check if this column name has been used before
283            let count = column_name_counts.entry(base_name.clone()).or_insert(0);
284            let column_name = if *count == 0 {
285                // First occurrence, use the name as-is
286                base_name.clone()
287            } else {
288                // Duplicate, append a suffix
289                format!("{}_{}", base_name, count)
290            };
291            *count += 1;
292
293            computed_table.add_column(DataColumn::new(&column_name));
294        }
295
296        // Calculate values for each row
297        let evaluator = ArithmeticEvaluator::new(source_table);
298
299        for &row_idx in visible_rows {
300            let mut row_values = Vec::new();
301
302            for item in &expanded_items {
303                let value = match item {
304                    SelectItem::Column(col_name) => {
305                        // Simple column reference
306                        let col_idx = source_table.get_column_index(col_name).ok_or_else(|| {
307                            let suggestion = self.find_similar_column(source_table, col_name);
308                            match suggestion {
309                                Some(similar) => anyhow::anyhow!(
310                                    "Column '{}' not found. Did you mean '{}'?",
311                                    col_name,
312                                    similar
313                                ),
314                                None => anyhow::anyhow!("Column '{}' not found", col_name),
315                            }
316                        })?;
317                        let row = source_table
318                            .get_row(row_idx)
319                            .ok_or_else(|| anyhow::anyhow!("Row {} not found", row_idx))?;
320                        row.get(col_idx)
321                            .ok_or_else(|| anyhow::anyhow!("Column {} not found in row", col_idx))?
322                            .clone()
323                    }
324                    SelectItem::Expression { expr, .. } => {
325                        // Computed expression
326                        evaluator.evaluate(expr, row_idx)?
327                    }
328                    SelectItem::Star => unreachable!("Star should have been expanded"),
329                };
330                row_values.push(value);
331            }
332
333            computed_table
334                .add_row(DataRow::new(row_values))
335                .map_err(|e| anyhow::anyhow!("Failed to add row: {}", e))?;
336        }
337
338        // Return a view of the computed result
339        // This is a temporary view for this query only
340        Ok(DataView::new(Arc::new(computed_table)))
341    }
342
343    /// Resolve SelectItem columns to indices (for simple column projections only)
344    fn resolve_select_columns(
345        &self,
346        table: &DataTable,
347        select_items: &[SelectItem],
348    ) -> Result<Vec<usize>> {
349        let mut indices = Vec::new();
350        let table_columns = table.column_names();
351
352        for item in select_items {
353            match item {
354                SelectItem::Column(col_name) => {
355                    let index = table_columns
356                        .iter()
357                        .position(|c| c.eq_ignore_ascii_case(col_name))
358                        .ok_or_else(|| {
359                            let suggestion = self.find_similar_column(table, col_name);
360                            match suggestion {
361                                Some(similar) => anyhow::anyhow!(
362                                    "Column '{}' not found. Did you mean '{}'?",
363                                    col_name,
364                                    similar
365                                ),
366                                None => anyhow::anyhow!("Column '{}' not found", col_name),
367                            }
368                        })?;
369                    indices.push(index);
370                }
371                SelectItem::Star => {
372                    // Expand * to all column indices
373                    for i in 0..table_columns.len() {
374                        indices.push(i);
375                    }
376                }
377                SelectItem::Expression { .. } => {
378                    return Err(anyhow::anyhow!(
379                        "Computed expressions require new table creation"
380                    ));
381                }
382            }
383        }
384
385        Ok(indices)
386    }
387
388    /// Apply multi-column ORDER BY sorting to the view
389    fn apply_multi_order_by(
390        &self,
391        mut view: DataView,
392        order_by_columns: &[OrderByColumn],
393    ) -> Result<DataView> {
394        // Build list of (source_column_index, ascending) tuples
395        let mut sort_columns = Vec::new();
396
397        for order_col in order_by_columns {
398            // Get column index
399            let col_index = view
400                .source()
401                .get_column_index(&order_col.column)
402                .ok_or_else(|| {
403                    let suggestion = self.find_similar_column(view.source(), &order_col.column);
404                    match suggestion {
405                        Some(similar) => anyhow::anyhow!(
406                            "Column '{}' not found. Did you mean '{}'?",
407                            order_col.column,
408                            similar
409                        ),
410                        None => anyhow::anyhow!("Column '{}' not found", order_col.column),
411                    }
412                })?;
413
414            let ascending = matches!(order_col.direction, SortDirection::Asc);
415            sort_columns.push((col_index, ascending));
416        }
417
418        // Apply multi-column sorting
419        view.apply_multi_sort(&sort_columns)?;
420        Ok(view)
421    }
422}
423
424#[cfg(test)]
425mod tests {
426    use super::*;
427    use crate::data::datatable::{DataColumn, DataRow, DataValue};
428
429    fn create_test_table() -> Arc<DataTable> {
430        let mut table = DataTable::new("test");
431
432        // Add columns
433        table.add_column(DataColumn::new("id"));
434        table.add_column(DataColumn::new("name"));
435        table.add_column(DataColumn::new("age"));
436
437        // Add rows
438        table
439            .add_row(DataRow::new(vec![
440                DataValue::Integer(1),
441                DataValue::String("Alice".to_string()),
442                DataValue::Integer(30),
443            ]))
444            .unwrap();
445
446        table
447            .add_row(DataRow::new(vec![
448                DataValue::Integer(2),
449                DataValue::String("Bob".to_string()),
450                DataValue::Integer(25),
451            ]))
452            .unwrap();
453
454        table
455            .add_row(DataRow::new(vec![
456                DataValue::Integer(3),
457                DataValue::String("Charlie".to_string()),
458                DataValue::Integer(35),
459            ]))
460            .unwrap();
461
462        Arc::new(table)
463    }
464
465    #[test]
466    fn test_select_all() {
467        let table = create_test_table();
468        let engine = QueryEngine::new();
469
470        let view = engine
471            .execute(table.clone(), "SELECT * FROM users")
472            .unwrap();
473        assert_eq!(view.row_count(), 3);
474        assert_eq!(view.column_count(), 3);
475    }
476
477    #[test]
478    fn test_select_columns() {
479        let table = create_test_table();
480        let engine = QueryEngine::new();
481
482        let view = engine
483            .execute(table.clone(), "SELECT name, age FROM users")
484            .unwrap();
485        assert_eq!(view.row_count(), 3);
486        assert_eq!(view.column_count(), 2);
487    }
488
489    #[test]
490    fn test_select_with_limit() {
491        let table = create_test_table();
492        let engine = QueryEngine::new();
493
494        let view = engine
495            .execute(table.clone(), "SELECT * FROM users LIMIT 2")
496            .unwrap();
497        assert_eq!(view.row_count(), 2);
498    }
499
500    #[test]
501    fn test_type_coercion_contains() {
502        // Initialize tracing for debug output
503        let _ = tracing_subscriber::fmt()
504            .with_max_level(tracing::Level::DEBUG)
505            .try_init();
506
507        let mut table = DataTable::new("test");
508        table.add_column(DataColumn::new("id"));
509        table.add_column(DataColumn::new("status"));
510        table.add_column(DataColumn::new("price"));
511
512        // Add test data with mixed types
513        table
514            .add_row(DataRow::new(vec![
515                DataValue::Integer(1),
516                DataValue::String("Pending".to_string()),
517                DataValue::Float(99.99),
518            ]))
519            .unwrap();
520
521        table
522            .add_row(DataRow::new(vec![
523                DataValue::Integer(2),
524                DataValue::String("Confirmed".to_string()),
525                DataValue::Float(150.50),
526            ]))
527            .unwrap();
528
529        table
530            .add_row(DataRow::new(vec![
531                DataValue::Integer(3),
532                DataValue::String("Pending".to_string()),
533                DataValue::Float(75.00),
534            ]))
535            .unwrap();
536
537        let table = Arc::new(table);
538        let engine = QueryEngine::new();
539
540        println!("\n=== Testing WHERE clause with Contains ===");
541        println!("Table has {} rows", table.row_count());
542        for i in 0..table.row_count() {
543            let status = table.get_value(i, 1);
544            println!("Row {}: status = {:?}", i, status);
545        }
546
547        // Test 1: Basic string contains (should work)
548        println!("\n--- Test 1: status.Contains('pend') ---");
549        let result = engine.execute(
550            table.clone(),
551            "SELECT * FROM test WHERE status.Contains('pend')",
552        );
553        match result {
554            Ok(view) => {
555                println!("SUCCESS: Found {} matching rows", view.row_count());
556                assert_eq!(view.row_count(), 2); // Should find both Pending rows
557            }
558            Err(e) => {
559                panic!("Query failed: {}", e);
560            }
561        }
562
563        // Test 2: Numeric contains (should work with type coercion)
564        println!("\n--- Test 2: price.Contains('9') ---");
565        let result = engine.execute(
566            table.clone(),
567            "SELECT * FROM test WHERE price.Contains('9')",
568        );
569        match result {
570            Ok(view) => {
571                println!(
572                    "SUCCESS: Found {} matching rows with price containing '9'",
573                    view.row_count()
574                );
575                // Should find 99.99 row
576                assert!(view.row_count() >= 1);
577            }
578            Err(e) => {
579                panic!("Numeric coercion query failed: {}", e);
580            }
581        }
582
583        println!("\n=== All tests passed! ===");
584    }
585
586    #[test]
587    fn test_not_in_clause() {
588        // Initialize tracing for debug output
589        let _ = tracing_subscriber::fmt()
590            .with_max_level(tracing::Level::DEBUG)
591            .try_init();
592
593        let mut table = DataTable::new("test");
594        table.add_column(DataColumn::new("id"));
595        table.add_column(DataColumn::new("country"));
596
597        // Add test data
598        table
599            .add_row(DataRow::new(vec![
600                DataValue::Integer(1),
601                DataValue::String("CA".to_string()),
602            ]))
603            .unwrap();
604
605        table
606            .add_row(DataRow::new(vec![
607                DataValue::Integer(2),
608                DataValue::String("US".to_string()),
609            ]))
610            .unwrap();
611
612        table
613            .add_row(DataRow::new(vec![
614                DataValue::Integer(3),
615                DataValue::String("UK".to_string()),
616            ]))
617            .unwrap();
618
619        let table = Arc::new(table);
620        let engine = QueryEngine::new();
621
622        println!("\n=== Testing NOT IN clause ===");
623        println!("Table has {} rows", table.row_count());
624        for i in 0..table.row_count() {
625            let country = table.get_value(i, 1);
626            println!("Row {}: country = {:?}", i, country);
627        }
628
629        // Test NOT IN clause - should exclude CA, return US and UK (2 rows)
630        println!("\n--- Test: country NOT IN ('CA') ---");
631        let result = engine.execute(
632            table.clone(),
633            "SELECT * FROM test WHERE country NOT IN ('CA')",
634        );
635        match result {
636            Ok(view) => {
637                println!("SUCCESS: Found {} rows not in ('CA')", view.row_count());
638                assert_eq!(view.row_count(), 2); // Should find US and UK
639            }
640            Err(e) => {
641                panic!("NOT IN query failed: {}", e);
642            }
643        }
644
645        println!("\n=== NOT IN test complete! ===");
646    }
647
648    #[test]
649    fn test_case_insensitive_in_and_not_in() {
650        // Initialize tracing for debug output
651        let _ = tracing_subscriber::fmt()
652            .with_max_level(tracing::Level::DEBUG)
653            .try_init();
654
655        let mut table = DataTable::new("test");
656        table.add_column(DataColumn::new("id"));
657        table.add_column(DataColumn::new("country"));
658
659        // Add test data with mixed case
660        table
661            .add_row(DataRow::new(vec![
662                DataValue::Integer(1),
663                DataValue::String("CA".to_string()), // uppercase
664            ]))
665            .unwrap();
666
667        table
668            .add_row(DataRow::new(vec![
669                DataValue::Integer(2),
670                DataValue::String("us".to_string()), // lowercase
671            ]))
672            .unwrap();
673
674        table
675            .add_row(DataRow::new(vec![
676                DataValue::Integer(3),
677                DataValue::String("UK".to_string()), // uppercase
678            ]))
679            .unwrap();
680
681        let table = Arc::new(table);
682
683        println!("\n=== Testing Case-Insensitive IN clause ===");
684        println!("Table has {} rows", table.row_count());
685        for i in 0..table.row_count() {
686            let country = table.get_value(i, 1);
687            println!("Row {}: country = {:?}", i, country);
688        }
689
690        // Test case-insensitive IN - should match 'CA' with 'ca'
691        println!("\n--- Test: country IN ('ca') with case_insensitive=true ---");
692        let engine = QueryEngine::with_case_insensitive(true);
693        let result = engine.execute(table.clone(), "SELECT * FROM test WHERE country IN ('ca')");
694        match result {
695            Ok(view) => {
696                println!(
697                    "SUCCESS: Found {} rows matching 'ca' (case-insensitive)",
698                    view.row_count()
699                );
700                assert_eq!(view.row_count(), 1); // Should find CA row
701            }
702            Err(e) => {
703                panic!("Case-insensitive IN query failed: {}", e);
704            }
705        }
706
707        // Test case-insensitive NOT IN - should exclude 'CA' when searching for 'ca'
708        println!("\n--- Test: country NOT IN ('ca') with case_insensitive=true ---");
709        let result = engine.execute(
710            table.clone(),
711            "SELECT * FROM test WHERE country NOT IN ('ca')",
712        );
713        match result {
714            Ok(view) => {
715                println!(
716                    "SUCCESS: Found {} rows not matching 'ca' (case-insensitive)",
717                    view.row_count()
718                );
719                assert_eq!(view.row_count(), 2); // Should find us and UK rows
720            }
721            Err(e) => {
722                panic!("Case-insensitive NOT IN query failed: {}", e);
723            }
724        }
725
726        // Test case-sensitive (default) - should NOT match 'CA' with 'ca'
727        println!("\n--- Test: country IN ('ca') with case_insensitive=false ---");
728        let engine_case_sensitive = QueryEngine::new(); // defaults to case_insensitive=false
729        let result = engine_case_sensitive
730            .execute(table.clone(), "SELECT * FROM test WHERE country IN ('ca')");
731        match result {
732            Ok(view) => {
733                println!(
734                    "SUCCESS: Found {} rows matching 'ca' (case-sensitive)",
735                    view.row_count()
736                );
737                assert_eq!(view.row_count(), 0); // Should find no rows (CA != ca)
738            }
739            Err(e) => {
740                panic!("Case-sensitive IN query failed: {}", e);
741            }
742        }
743
744        println!("\n=== Case-insensitive IN/NOT IN test complete! ===");
745    }
746
747    #[test]
748    #[ignore = "Parentheses in WHERE clause not yet implemented"]
749    fn test_parentheses_in_where_clause() {
750        // Initialize tracing for debug output
751        let _ = tracing_subscriber::fmt()
752            .with_max_level(tracing::Level::DEBUG)
753            .try_init();
754
755        let mut table = DataTable::new("test");
756        table.add_column(DataColumn::new("id"));
757        table.add_column(DataColumn::new("status"));
758        table.add_column(DataColumn::new("priority"));
759
760        // Add test data
761        table
762            .add_row(DataRow::new(vec![
763                DataValue::Integer(1),
764                DataValue::String("Pending".to_string()),
765                DataValue::String("High".to_string()),
766            ]))
767            .unwrap();
768
769        table
770            .add_row(DataRow::new(vec![
771                DataValue::Integer(2),
772                DataValue::String("Complete".to_string()),
773                DataValue::String("High".to_string()),
774            ]))
775            .unwrap();
776
777        table
778            .add_row(DataRow::new(vec![
779                DataValue::Integer(3),
780                DataValue::String("Pending".to_string()),
781                DataValue::String("Low".to_string()),
782            ]))
783            .unwrap();
784
785        table
786            .add_row(DataRow::new(vec![
787                DataValue::Integer(4),
788                DataValue::String("Complete".to_string()),
789                DataValue::String("Low".to_string()),
790            ]))
791            .unwrap();
792
793        let table = Arc::new(table);
794        let engine = QueryEngine::new();
795
796        println!("\n=== Testing Parentheses in WHERE clause ===");
797        println!("Table has {} rows", table.row_count());
798        for i in 0..table.row_count() {
799            let status = table.get_value(i, 1);
800            let priority = table.get_value(i, 2);
801            println!(
802                "Row {}: status = {:?}, priority = {:?}",
803                i, status, priority
804            );
805        }
806
807        // Test OR with parentheses - should get (Pending AND High) OR (Complete AND Low)
808        println!("\n--- Test: (status = 'Pending' AND priority = 'High') OR (status = 'Complete' AND priority = 'Low') ---");
809        let result = engine.execute(
810            table.clone(),
811            "SELECT * FROM test WHERE (status = 'Pending' AND priority = 'High') OR (status = 'Complete' AND priority = 'Low')",
812        );
813        match result {
814            Ok(view) => {
815                println!(
816                    "SUCCESS: Found {} rows with parenthetical logic",
817                    view.row_count()
818                );
819                assert_eq!(view.row_count(), 2); // Should find rows 1 and 4
820            }
821            Err(e) => {
822                panic!("Parentheses query failed: {}", e);
823            }
824        }
825
826        println!("\n=== Parentheses test complete! ===");
827    }
828
829    #[test]
830    #[ignore = "Numeric type coercion needs fixing"]
831    fn test_numeric_type_coercion() {
832        // Initialize tracing for debug output
833        let _ = tracing_subscriber::fmt()
834            .with_max_level(tracing::Level::DEBUG)
835            .try_init();
836
837        let mut table = DataTable::new("test");
838        table.add_column(DataColumn::new("id"));
839        table.add_column(DataColumn::new("price"));
840        table.add_column(DataColumn::new("quantity"));
841
842        // Add test data with different numeric types
843        table
844            .add_row(DataRow::new(vec![
845                DataValue::Integer(1),
846                DataValue::Float(99.50), // Contains '.'
847                DataValue::Integer(100),
848            ]))
849            .unwrap();
850
851        table
852            .add_row(DataRow::new(vec![
853                DataValue::Integer(2),
854                DataValue::Float(150.0), // Contains '.' and '0'
855                DataValue::Integer(200),
856            ]))
857            .unwrap();
858
859        table
860            .add_row(DataRow::new(vec![
861                DataValue::Integer(3),
862                DataValue::Integer(75), // No decimal point
863                DataValue::Integer(50),
864            ]))
865            .unwrap();
866
867        let table = Arc::new(table);
868        let engine = QueryEngine::new();
869
870        println!("\n=== Testing Numeric Type Coercion ===");
871        println!("Table has {} rows", table.row_count());
872        for i in 0..table.row_count() {
873            let price = table.get_value(i, 1);
874            let quantity = table.get_value(i, 2);
875            println!("Row {}: price = {:?}, quantity = {:?}", i, price, quantity);
876        }
877
878        // Test Contains on float values - should find rows with decimal points
879        println!("\n--- Test: price.Contains('.') ---");
880        let result = engine.execute(
881            table.clone(),
882            "SELECT * FROM test WHERE price.Contains('.')",
883        );
884        match result {
885            Ok(view) => {
886                println!(
887                    "SUCCESS: Found {} rows with decimal points in price",
888                    view.row_count()
889                );
890                assert_eq!(view.row_count(), 2); // Should find 99.50 and 150.0
891            }
892            Err(e) => {
893                panic!("Numeric Contains query failed: {}", e);
894            }
895        }
896
897        // Test Contains on integer values converted to string
898        println!("\n--- Test: quantity.Contains('0') ---");
899        let result = engine.execute(
900            table.clone(),
901            "SELECT * FROM test WHERE quantity.Contains('0')",
902        );
903        match result {
904            Ok(view) => {
905                println!(
906                    "SUCCESS: Found {} rows with '0' in quantity",
907                    view.row_count()
908                );
909                assert_eq!(view.row_count(), 2); // Should find 100 and 200
910            }
911            Err(e) => {
912                panic!("Integer Contains query failed: {}", e);
913            }
914        }
915
916        println!("\n=== Numeric type coercion test complete! ===");
917    }
918
919    #[test]
920    fn test_datetime_comparisons() {
921        // Initialize tracing for debug output
922        let _ = tracing_subscriber::fmt()
923            .with_max_level(tracing::Level::DEBUG)
924            .try_init();
925
926        let mut table = DataTable::new("test");
927        table.add_column(DataColumn::new("id"));
928        table.add_column(DataColumn::new("created_date"));
929
930        // Add test data with date strings (as they would come from CSV)
931        table
932            .add_row(DataRow::new(vec![
933                DataValue::Integer(1),
934                DataValue::String("2024-12-15".to_string()),
935            ]))
936            .unwrap();
937
938        table
939            .add_row(DataRow::new(vec![
940                DataValue::Integer(2),
941                DataValue::String("2025-01-15".to_string()),
942            ]))
943            .unwrap();
944
945        table
946            .add_row(DataRow::new(vec![
947                DataValue::Integer(3),
948                DataValue::String("2025-02-15".to_string()),
949            ]))
950            .unwrap();
951
952        let table = Arc::new(table);
953        let engine = QueryEngine::new();
954
955        println!("\n=== Testing DateTime Comparisons ===");
956        println!("Table has {} rows", table.row_count());
957        for i in 0..table.row_count() {
958            let date = table.get_value(i, 1);
959            println!("Row {}: created_date = {:?}", i, date);
960        }
961
962        // Test DateTime constructor comparison - should find dates after 2025-01-01
963        println!("\n--- Test: created_date > DateTime(2025,1,1) ---");
964        let result = engine.execute(
965            table.clone(),
966            "SELECT * FROM test WHERE created_date > DateTime(2025,1,1)",
967        );
968        match result {
969            Ok(view) => {
970                println!("SUCCESS: Found {} rows after 2025-01-01", view.row_count());
971                assert_eq!(view.row_count(), 2); // Should find 2025-01-15 and 2025-02-15
972            }
973            Err(e) => {
974                panic!("DateTime comparison query failed: {}", e);
975            }
976        }
977
978        println!("\n=== DateTime comparison test complete! ===");
979    }
980
981    #[test]
982    fn test_not_with_method_calls() {
983        // Initialize tracing for debug output
984        let _ = tracing_subscriber::fmt()
985            .with_max_level(tracing::Level::DEBUG)
986            .try_init();
987
988        let mut table = DataTable::new("test");
989        table.add_column(DataColumn::new("id"));
990        table.add_column(DataColumn::new("status"));
991
992        // Add test data
993        table
994            .add_row(DataRow::new(vec![
995                DataValue::Integer(1),
996                DataValue::String("Pending Review".to_string()),
997            ]))
998            .unwrap();
999
1000        table
1001            .add_row(DataRow::new(vec![
1002                DataValue::Integer(2),
1003                DataValue::String("Complete".to_string()),
1004            ]))
1005            .unwrap();
1006
1007        table
1008            .add_row(DataRow::new(vec![
1009                DataValue::Integer(3),
1010                DataValue::String("Pending Approval".to_string()),
1011            ]))
1012            .unwrap();
1013
1014        let table = Arc::new(table);
1015        let engine = QueryEngine::with_case_insensitive(true);
1016
1017        println!("\n=== Testing NOT with Method Calls ===");
1018        println!("Table has {} rows", table.row_count());
1019        for i in 0..table.row_count() {
1020            let status = table.get_value(i, 1);
1021            println!("Row {}: status = {:?}", i, status);
1022        }
1023
1024        // Test NOT with Contains - should exclude rows containing "pend"
1025        println!("\n--- Test: NOT status.Contains('pend') ---");
1026        let result = engine.execute(
1027            table.clone(),
1028            "SELECT * FROM test WHERE NOT status.Contains('pend')",
1029        );
1030        match result {
1031            Ok(view) => {
1032                println!(
1033                    "SUCCESS: Found {} rows NOT containing 'pend'",
1034                    view.row_count()
1035                );
1036                assert_eq!(view.row_count(), 1); // Should find only "Complete"
1037            }
1038            Err(e) => {
1039                panic!("NOT Contains query failed: {}", e);
1040            }
1041        }
1042
1043        // Test NOT with StartsWith
1044        println!("\n--- Test: NOT status.StartsWith('Pending') ---");
1045        let result = engine.execute(
1046            table.clone(),
1047            "SELECT * FROM test WHERE NOT status.StartsWith('Pending')",
1048        );
1049        match result {
1050            Ok(view) => {
1051                println!(
1052                    "SUCCESS: Found {} rows NOT starting with 'Pending'",
1053                    view.row_count()
1054                );
1055                assert_eq!(view.row_count(), 1); // Should find only "Complete"
1056            }
1057            Err(e) => {
1058                panic!("NOT StartsWith query failed: {}", e);
1059            }
1060        }
1061
1062        println!("\n=== NOT with method calls test complete! ===");
1063    }
1064
1065    #[test]
1066    #[ignore = "Complex logical expressions with parentheses not yet implemented"]
1067    fn test_complex_logical_expressions() {
1068        // Initialize tracing for debug output
1069        let _ = tracing_subscriber::fmt()
1070            .with_max_level(tracing::Level::DEBUG)
1071            .try_init();
1072
1073        let mut table = DataTable::new("test");
1074        table.add_column(DataColumn::new("id"));
1075        table.add_column(DataColumn::new("status"));
1076        table.add_column(DataColumn::new("priority"));
1077        table.add_column(DataColumn::new("assigned"));
1078
1079        // Add comprehensive test data
1080        table
1081            .add_row(DataRow::new(vec![
1082                DataValue::Integer(1),
1083                DataValue::String("Pending".to_string()),
1084                DataValue::String("High".to_string()),
1085                DataValue::String("John".to_string()),
1086            ]))
1087            .unwrap();
1088
1089        table
1090            .add_row(DataRow::new(vec![
1091                DataValue::Integer(2),
1092                DataValue::String("Complete".to_string()),
1093                DataValue::String("High".to_string()),
1094                DataValue::String("Jane".to_string()),
1095            ]))
1096            .unwrap();
1097
1098        table
1099            .add_row(DataRow::new(vec![
1100                DataValue::Integer(3),
1101                DataValue::String("Pending".to_string()),
1102                DataValue::String("Low".to_string()),
1103                DataValue::String("John".to_string()),
1104            ]))
1105            .unwrap();
1106
1107        table
1108            .add_row(DataRow::new(vec![
1109                DataValue::Integer(4),
1110                DataValue::String("In Progress".to_string()),
1111                DataValue::String("Medium".to_string()),
1112                DataValue::String("Jane".to_string()),
1113            ]))
1114            .unwrap();
1115
1116        let table = Arc::new(table);
1117        let engine = QueryEngine::new();
1118
1119        println!("\n=== Testing Complex Logical Expressions ===");
1120        println!("Table has {} rows", table.row_count());
1121        for i in 0..table.row_count() {
1122            let status = table.get_value(i, 1);
1123            let priority = table.get_value(i, 2);
1124            let assigned = table.get_value(i, 3);
1125            println!(
1126                "Row {}: status = {:?}, priority = {:?}, assigned = {:?}",
1127                i, status, priority, assigned
1128            );
1129        }
1130
1131        // Test complex AND/OR logic
1132        println!("\n--- Test: status = 'Pending' AND (priority = 'High' OR assigned = 'John') ---");
1133        let result = engine.execute(
1134            table.clone(),
1135            "SELECT * FROM test WHERE status = 'Pending' AND (priority = 'High' OR assigned = 'John')",
1136        );
1137        match result {
1138            Ok(view) => {
1139                println!(
1140                    "SUCCESS: Found {} rows with complex logic",
1141                    view.row_count()
1142                );
1143                assert_eq!(view.row_count(), 2); // Should find rows 1 and 3 (both Pending, one High priority, both assigned to John)
1144            }
1145            Err(e) => {
1146                panic!("Complex logic query failed: {}", e);
1147            }
1148        }
1149
1150        // Test NOT with complex expressions
1151        println!("\n--- Test: NOT (status.Contains('Complete') OR priority = 'Low') ---");
1152        let result = engine.execute(
1153            table.clone(),
1154            "SELECT * FROM test WHERE NOT (status.Contains('Complete') OR priority = 'Low')",
1155        );
1156        match result {
1157            Ok(view) => {
1158                println!(
1159                    "SUCCESS: Found {} rows with NOT complex logic",
1160                    view.row_count()
1161                );
1162                assert_eq!(view.row_count(), 2); // Should find rows 1 (Pending+High) and 4 (In Progress+Medium)
1163            }
1164            Err(e) => {
1165                panic!("NOT complex logic query failed: {}", e);
1166            }
1167        }
1168
1169        println!("\n=== Complex logical expressions test complete! ===");
1170    }
1171
1172    #[test]
1173    fn test_mixed_data_types_and_edge_cases() {
1174        // Initialize tracing for debug output
1175        let _ = tracing_subscriber::fmt()
1176            .with_max_level(tracing::Level::DEBUG)
1177            .try_init();
1178
1179        let mut table = DataTable::new("test");
1180        table.add_column(DataColumn::new("id"));
1181        table.add_column(DataColumn::new("value"));
1182        table.add_column(DataColumn::new("nullable_field"));
1183
1184        // Add test data with mixed types and edge cases
1185        table
1186            .add_row(DataRow::new(vec![
1187                DataValue::Integer(1),
1188                DataValue::String("123.45".to_string()),
1189                DataValue::String("present".to_string()),
1190            ]))
1191            .unwrap();
1192
1193        table
1194            .add_row(DataRow::new(vec![
1195                DataValue::Integer(2),
1196                DataValue::Float(678.90),
1197                DataValue::Null,
1198            ]))
1199            .unwrap();
1200
1201        table
1202            .add_row(DataRow::new(vec![
1203                DataValue::Integer(3),
1204                DataValue::Boolean(true),
1205                DataValue::String("also present".to_string()),
1206            ]))
1207            .unwrap();
1208
1209        table
1210            .add_row(DataRow::new(vec![
1211                DataValue::Integer(4),
1212                DataValue::String("false".to_string()),
1213                DataValue::Null,
1214            ]))
1215            .unwrap();
1216
1217        let table = Arc::new(table);
1218        let engine = QueryEngine::new();
1219
1220        println!("\n=== Testing Mixed Data Types and Edge Cases ===");
1221        println!("Table has {} rows", table.row_count());
1222        for i in 0..table.row_count() {
1223            let value = table.get_value(i, 1);
1224            let nullable = table.get_value(i, 2);
1225            println!(
1226                "Row {}: value = {:?}, nullable_field = {:?}",
1227                i, value, nullable
1228            );
1229        }
1230
1231        // Test type coercion with boolean Contains
1232        println!("\n--- Test: value.Contains('true') (boolean to string coercion) ---");
1233        let result = engine.execute(
1234            table.clone(),
1235            "SELECT * FROM test WHERE value.Contains('true')",
1236        );
1237        match result {
1238            Ok(view) => {
1239                println!(
1240                    "SUCCESS: Found {} rows with boolean coercion",
1241                    view.row_count()
1242                );
1243                assert_eq!(view.row_count(), 1); // Should find the boolean true row
1244            }
1245            Err(e) => {
1246                panic!("Boolean coercion query failed: {}", e);
1247            }
1248        }
1249
1250        // Test multiple IN values with mixed types
1251        println!("\n--- Test: id IN (1, 3) ---");
1252        let result = engine.execute(table.clone(), "SELECT * FROM test WHERE id IN (1, 3)");
1253        match result {
1254            Ok(view) => {
1255                println!("SUCCESS: Found {} rows with IN clause", view.row_count());
1256                assert_eq!(view.row_count(), 2); // Should find rows with id 1 and 3
1257            }
1258            Err(e) => {
1259                panic!("Multiple IN values query failed: {}", e);
1260            }
1261        }
1262
1263        println!("\n=== Mixed data types test complete! ===");
1264    }
1265
1266    #[test]
1267    fn test_not_in_parsing() {
1268        use crate::sql::recursive_parser::Parser;
1269
1270        let query = "SELECT * FROM test WHERE country NOT IN ('CA')";
1271        println!("\n=== Testing NOT IN parsing ===");
1272        println!("Parsing query: {}", query);
1273
1274        let mut parser = Parser::new(query);
1275        match parser.parse() {
1276            Ok(statement) => {
1277                println!("Parsed statement: {:#?}", statement);
1278                if let Some(where_clause) = statement.where_clause {
1279                    println!("WHERE conditions: {:#?}", where_clause.conditions);
1280                    if let Some(first_condition) = where_clause.conditions.first() {
1281                        println!("First condition expression: {:#?}", first_condition.expr);
1282                    }
1283                }
1284            }
1285            Err(e) => {
1286                panic!("Parse error: {}", e);
1287            }
1288        }
1289    }
1290}
1291
1292#[cfg(test)]
1293#[path = "query_engine_tests.rs"]
1294mod query_engine_tests;
sql_cli/data/query_engine.rs

sql_cli/data/
query_engine.rs
