sql_cli/data/
query_engine.rs

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

sql_cli/data/
query_engine.rs
