sql_cli/data/
recursive_where_evaluator.rs

1use crate::data::arithmetic_evaluator::ArithmeticEvaluator;
2use crate::data::datatable::{DataTable, DataValue};
3use crate::sql::recursive_parser::{Condition, LogicalOp, SqlExpression, WhereClause};
4use anyhow::{anyhow, Result};
5use chrono::{DateTime, Local, NaiveDate, NaiveDateTime, NaiveTime, TimeZone, Utc};
6use tracing::debug;
7
8/// Evaluates WHERE clauses from `recursive_parser` directly against `DataTable`
9pub struct RecursiveWhereEvaluator<'a> {
10    table: &'a DataTable,
11    case_insensitive: bool,
12    date_notation: String,
13}
14
15impl<'a> RecursiveWhereEvaluator<'a> {
16    #[must_use]
17    pub fn new(table: &'a DataTable) -> Self {
18        Self {
19            table,
20            case_insensitive: false,
21            date_notation: "us".to_string(),
22        }
23    }
24
25    #[must_use]
26    pub fn with_date_notation(table: &'a DataTable, date_notation: String) -> Self {
27        Self {
28            table,
29            case_insensitive: false,
30            date_notation,
31        }
32    }
33
34    /// Central function to parse date/datetime strings respecting `date_notation` preference
35    fn parse_datetime_with_notation(&self, s: &str) -> Result<DateTime<Utc>> {
36        // Try ISO 8601 with timezone first (unambiguous)
37        if let Ok(parsed_dt) = s.parse::<DateTime<Utc>>() {
38            return Ok(parsed_dt);
39        }
40
41        // ISO formats without timezone (unambiguous)
42        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%dT%H:%M:%S") {
43            return Ok(Utc.from_utc_datetime(&dt));
44        }
45        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S") {
46            return Ok(Utc.from_utc_datetime(&dt));
47        }
48        if let Ok(dt) = NaiveDate::parse_from_str(s, "%Y-%m-%d") {
49            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
50        }
51
52        // Date notation preference for ambiguous formats
53        if self.date_notation == "european" {
54            // European formats (DD/MM/YYYY) - try first
55            // Date only formats
56            if let Ok(dt) = NaiveDate::parse_from_str(s, "%d/%m/%Y") {
57                return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
58            }
59            if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%m-%Y") {
60                return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
61            }
62            // With time formats
63            if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S") {
64                return Ok(Utc.from_utc_datetime(&dt));
65            }
66            if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d-%m-%Y %H:%M:%S") {
67                return Ok(Utc.from_utc_datetime(&dt));
68            }
69
70            // US formats (MM/DD/YYYY) - fallback
71            // Date only formats
72            if let Ok(dt) = NaiveDate::parse_from_str(s, "%m/%d/%Y") {
73                return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
74            }
75            if let Ok(dt) = NaiveDate::parse_from_str(s, "%m-%d-%Y") {
76                return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
77            }
78            // With time formats
79            if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S") {
80                return Ok(Utc.from_utc_datetime(&dt));
81            }
82            if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m-%d-%Y %H:%M:%S") {
83                return Ok(Utc.from_utc_datetime(&dt));
84            }
85        } else {
86            // US formats (MM/DD/YYYY) - default, try first
87            // Date only formats
88            if let Ok(dt) = NaiveDate::parse_from_str(s, "%m/%d/%Y") {
89                return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
90            }
91            if let Ok(dt) = NaiveDate::parse_from_str(s, "%m-%d-%Y") {
92                return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
93            }
94            // With time formats
95            if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S") {
96                return Ok(Utc.from_utc_datetime(&dt));
97            }
98            if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m-%d-%Y %H:%M:%S") {
99                return Ok(Utc.from_utc_datetime(&dt));
100            }
101
102            // European formats (DD/MM/YYYY) - fallback
103            // Date only formats
104            if let Ok(dt) = NaiveDate::parse_from_str(s, "%d/%m/%Y") {
105                return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
106            }
107            if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%m-%Y") {
108                return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
109            }
110            // With time formats
111            if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S") {
112                return Ok(Utc.from_utc_datetime(&dt));
113            }
114            if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d-%m-%Y %H:%M:%S") {
115                return Ok(Utc.from_utc_datetime(&dt));
116            }
117        }
118
119        // Excel/Windows format: DD-MMM-YYYY (e.g., 15-Jan-2024)
120        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%b-%Y") {
121            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
122        }
123
124        // Full month names
125        if let Ok(dt) = NaiveDate::parse_from_str(s, "%B %d, %Y") {
126            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
127        }
128        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d %B %Y") {
129            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
130        }
131
132        // Additional formats with time variations
133        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y/%m/%d %H:%M:%S") {
134            return Ok(Utc.from_utc_datetime(&dt));
135        }
136
137        Err(anyhow!(
138            "Could not parse date/datetime: '{}'. Supported formats include: YYYY-MM-DD, MM/DD/YYYY, DD/MM/YYYY (based on config), with optional HH:MM:SS",
139            s
140        ))
141    }
142
143    /// Find a column name similar to the given name using edit distance
144    fn find_similar_column(&self, name: &str) -> Option<String> {
145        let columns = self.table.column_names();
146        let mut best_match: Option<(String, usize)> = None;
147
148        for col in columns {
149            let distance = self.edit_distance(&col.to_lowercase(), &name.to_lowercase());
150            // Only suggest if distance is small (likely a typo)
151            // Allow up to 3 edits for longer names
152            let max_distance = if name.len() > 10 { 3 } else { 2 };
153            if distance <= max_distance {
154                match &best_match {
155                    None => best_match = Some((col, distance)),
156                    Some((_, best_dist)) if distance < *best_dist => {
157                        best_match = Some((col, distance));
158                    }
159                    _ => {}
160                }
161            }
162        }
163
164        best_match.map(|(name, _)| name)
165    }
166
167    /// Calculate Levenshtein edit distance between two strings
168    fn edit_distance(&self, s1: &str, s2: &str) -> usize {
169        let len1 = s1.len();
170        let len2 = s2.len();
171        let mut matrix = vec![vec![0; len2 + 1]; len1 + 1];
172
173        for i in 0..=len1 {
174            matrix[i][0] = i;
175        }
176        for j in 0..=len2 {
177            matrix[0][j] = j;
178        }
179
180        for (i, c1) in s1.chars().enumerate() {
181            for (j, c2) in s2.chars().enumerate() {
182                let cost = usize::from(c1 != c2);
183                matrix[i + 1][j + 1] = std::cmp::min(
184                    matrix[i][j + 1] + 1, // deletion
185                    std::cmp::min(
186                        matrix[i + 1][j] + 1, // insertion
187                        matrix[i][j] + cost,  // substitution
188                    ),
189                );
190            }
191        }
192
193        matrix[len1][len2]
194    }
195
196    #[must_use]
197    pub fn with_case_insensitive(table: &'a DataTable, case_insensitive: bool) -> Self {
198        Self {
199            table,
200            case_insensitive,
201            date_notation: "us".to_string(),
202        }
203    }
204
205    #[must_use]
206    pub fn with_config(
207        table: &'a DataTable,
208        case_insensitive: bool,
209        date_notation: String,
210    ) -> Self {
211        Self {
212            table,
213            case_insensitive,
214            date_notation,
215        }
216    }
217
218    /// Helper function to compare two datetime values based on the operator
219    fn compare_datetime(op: &str, left: &DateTime<Utc>, right: &DateTime<Utc>) -> bool {
220        match op {
221            "=" => left == right,
222            "!=" | "<>" => left != right,
223            ">" => left > right,
224            ">=" => left >= right,
225            "<" => left < right,
226            "<=" => left <= right,
227            _ => false,
228        }
229    }
230
231    /// Evaluate the `Length()` method on a column value
232    fn evaluate_length(
233        &self,
234        object: &str,
235        row_index: usize,
236    ) -> Result<(Option<DataValue>, String)> {
237        let col_index = self.table.get_column_index(object).ok_or_else(|| {
238            let suggestion = self.find_similar_column(object);
239            match suggestion {
240                Some(similar) => {
241                    anyhow!("Column '{}' not found. Did you mean '{}'?", object, similar)
242                }
243                None => anyhow!("Column '{}' not found", object),
244            }
245        })?;
246
247        let value = self.table.get_value(row_index, col_index);
248        let length_value = match value {
249            Some(DataValue::String(s)) => Some(DataValue::Integer(s.len() as i64)),
250            Some(DataValue::InternedString(s)) => Some(DataValue::Integer(s.len() as i64)),
251            Some(DataValue::Integer(n)) => Some(DataValue::Integer(n.to_string().len() as i64)),
252            Some(DataValue::Float(f)) => Some(DataValue::Integer(f.to_string().len() as i64)),
253            _ => Some(DataValue::Integer(0)),
254        };
255        Ok((length_value, format!("{object}.Length()")))
256    }
257
258    /// Evaluate the `IndexOf()` method on a column value
259    fn evaluate_indexof(
260        &self,
261        object: &str,
262        search_str: &str,
263        row_index: usize,
264    ) -> Result<(Option<DataValue>, String)> {
265        let col_index = self.table.get_column_index(object).ok_or_else(|| {
266            let suggestion = self.find_similar_column(object);
267            match suggestion {
268                Some(similar) => {
269                    anyhow!("Column '{}' not found. Did you mean '{}'?", object, similar)
270                }
271                None => anyhow!("Column '{}' not found", object),
272            }
273        })?;
274
275        let value = self.table.get_value(row_index, col_index);
276        let index_value = match value {
277            Some(DataValue::String(s)) => {
278                // Case-insensitive search by default, following Contains behavior
279                let pos = s
280                    .to_lowercase()
281                    .find(&search_str.to_lowercase())
282                    .map_or(-1, |idx| idx as i64);
283                Some(DataValue::Integer(pos))
284            }
285            Some(DataValue::InternedString(s)) => {
286                let pos = s
287                    .to_lowercase()
288                    .find(&search_str.to_lowercase())
289                    .map_or(-1, |idx| idx as i64);
290                Some(DataValue::Integer(pos))
291            }
292            Some(DataValue::Integer(n)) => {
293                let str_val = n.to_string();
294                let pos = str_val.find(search_str).map_or(-1, |idx| idx as i64);
295                Some(DataValue::Integer(pos))
296            }
297            Some(DataValue::Float(f)) => {
298                let str_val = f.to_string();
299                let pos = str_val.find(search_str).map_or(-1, |idx| idx as i64);
300                Some(DataValue::Integer(pos))
301            }
302            _ => Some(DataValue::Integer(-1)), // Return -1 for not found
303        };
304
305        if row_index < 3 {
306            debug!(
307                "RecursiveWhereEvaluator: Row {} IndexOf('{}') = {:?}",
308                row_index, search_str, index_value
309            );
310        }
311        Ok((index_value, format!("{object}.IndexOf('{search_str}')")))
312    }
313
314    /// Apply the appropriate trim operation based on `trim_type`
315    fn apply_trim<'b>(s: &'b str, trim_type: &str) -> &'b str {
316        match trim_type {
317            "trim" => s.trim(),
318            "trimstart" => s.trim_start(),
319            "trimend" => s.trim_end(),
320            _ => s,
321        }
322    }
323
324    /// Evaluate trim methods (Trim, `TrimStart`, `TrimEnd`) on a column value
325    fn evaluate_trim(
326        &self,
327        object: &str,
328        row_index: usize,
329        trim_type: &str,
330    ) -> Result<(Option<DataValue>, String)> {
331        let col_index = self.table.get_column_index(object).ok_or_else(|| {
332            let suggestion = self.find_similar_column(object);
333            match suggestion {
334                Some(similar) => {
335                    anyhow!("Column '{}' not found. Did you mean '{}'?", object, similar)
336                }
337                None => anyhow!("Column '{}' not found", object),
338            }
339        })?;
340
341        let value = self.table.get_value(row_index, col_index);
342        let trimmed_value = match value {
343            Some(DataValue::String(s)) => Some(DataValue::String(
344                Self::apply_trim(s, trim_type).to_string(),
345            )),
346            Some(DataValue::InternedString(s)) => Some(DataValue::String(
347                Self::apply_trim(s, trim_type).to_string(),
348            )),
349            Some(DataValue::Integer(n)) => {
350                let str_val = n.to_string();
351                Some(DataValue::String(
352                    Self::apply_trim(&str_val, trim_type).to_string(),
353                ))
354            }
355            Some(DataValue::Float(f)) => {
356                let str_val = f.to_string();
357                Some(DataValue::String(
358                    Self::apply_trim(&str_val, trim_type).to_string(),
359                ))
360            }
361            _ => Some(DataValue::String(String::new())),
362        };
363
364        let method_name = match trim_type {
365            "trim" => "Trim",
366            "trimstart" => "TrimStart",
367            "trimend" => "TrimEnd",
368            _ => "Trim",
369        };
370        Ok((trimmed_value, format!("{object}.{method_name}()")))
371    }
372
373    /// Evaluate a WHERE clause for a specific row
374    pub fn evaluate(&self, where_clause: &WhereClause, row_index: usize) -> Result<bool> {
375        // Only log for first few rows to avoid performance impact
376        if row_index < 3 {
377            debug!(
378                "RecursiveWhereEvaluator: evaluate() ENTRY - row {}, {} conditions, case_insensitive={}",
379                row_index,
380                where_clause.conditions.len(),
381                self.case_insensitive
382            );
383        }
384
385        if where_clause.conditions.is_empty() {
386            if row_index < 3 {
387                debug!("RecursiveWhereEvaluator: evaluate() EXIT - no conditions, returning true");
388            }
389            return Ok(true);
390        }
391
392        // Evaluate first condition
393        if row_index < 3 {
394            debug!(
395                "RecursiveWhereEvaluator: evaluate() - evaluating first condition for row {}",
396                row_index
397            );
398        }
399        let mut result = self.evaluate_condition(&where_clause.conditions[0], row_index)?;
400
401        // Apply connectors (AND/OR) with subsequent conditions
402        for i in 1..where_clause.conditions.len() {
403            let next_result = self.evaluate_condition(&where_clause.conditions[i], row_index)?;
404
405            // Use the connector from the previous condition
406            if let Some(connector) = &where_clause.conditions[i - 1].connector {
407                result = match connector {
408                    LogicalOp::And => result && next_result,
409                    LogicalOp::Or => result || next_result,
410                };
411            }
412        }
413
414        Ok(result)
415    }
416
417    fn evaluate_condition(&self, condition: &Condition, row_index: usize) -> Result<bool> {
418        // Only log first few rows to avoid performance impact
419        if row_index < 3 {
420            debug!(
421                "RecursiveWhereEvaluator: evaluate_condition() ENTRY - row {}",
422                row_index
423            );
424        }
425        let result = self.evaluate_expression(&condition.expr, row_index);
426        if row_index < 3 {
427            debug!(
428                "RecursiveWhereEvaluator: evaluate_condition() EXIT - row {}, result = {:?}",
429                row_index, result
430            );
431        }
432        result
433    }
434
435    fn evaluate_expression(&self, expr: &SqlExpression, row_index: usize) -> Result<bool> {
436        // Only log first few rows to avoid performance impact
437        if row_index < 3 {
438            debug!(
439                "RecursiveWhereEvaluator: evaluate_expression() ENTRY - row {}, expr = {:?}",
440                row_index, expr
441            );
442        }
443
444        let result = match expr {
445            SqlExpression::BinaryOp { left, op, right } => {
446                self.evaluate_binary_op(left, op, right, row_index)
447            }
448            SqlExpression::InList { expr, values } => {
449                self.evaluate_in_list(expr, values, row_index, false)
450            }
451            SqlExpression::NotInList { expr, values } => {
452                let in_result = self.evaluate_in_list(expr, values, row_index, false)?;
453                Ok(!in_result)
454            }
455            SqlExpression::Between { expr, lower, upper } => {
456                self.evaluate_between(expr, lower, upper, row_index)
457            }
458            SqlExpression::Not { expr } => {
459                let inner_result = self.evaluate_expression(expr, row_index)?;
460                Ok(!inner_result)
461            }
462            SqlExpression::MethodCall {
463                object,
464                method,
465                args,
466            } => {
467                if row_index < 3 {
468                    debug!("RecursiveWhereEvaluator: evaluate_expression() - found MethodCall, delegating to evaluate_method_call");
469                }
470                self.evaluate_method_call(object, method, args, row_index)
471            }
472            SqlExpression::CaseExpression {
473                when_branches,
474                else_branch,
475            } => {
476                if row_index < 3 {
477                    debug!("RecursiveWhereEvaluator: evaluate_expression() - found CaseExpression, evaluating");
478                }
479                self.evaluate_case_expression_as_bool(when_branches, else_branch, row_index)
480            }
481            _ => {
482                if row_index < 3 {
483                    debug!("RecursiveWhereEvaluator: evaluate_expression() - unsupported expression type, returning false");
484                }
485                Ok(false) // Default to false for unsupported expressions
486            }
487        };
488
489        if row_index < 3 {
490            debug!(
491                "RecursiveWhereEvaluator: evaluate_expression() EXIT - row {}, result = {:?}",
492                row_index, result
493            );
494        }
495        result
496    }
497
498    fn evaluate_binary_op(
499        &self,
500        left: &SqlExpression,
501        op: &str,
502        right: &SqlExpression,
503        row_index: usize,
504    ) -> Result<bool> {
505        // Only log first few rows to avoid performance impact
506        if row_index < 3 {
507            debug!(
508                "RecursiveWhereEvaluator: evaluate_binary_op() ENTRY - row {}, op = '{}'",
509                row_index, op
510            );
511        }
512
513        // Handle left side - could be a column or a method call
514        let (cell_value, column_name) = match left {
515            SqlExpression::MethodCall {
516                object,
517                method,
518                args,
519            } => {
520                // Handle method calls that return values (like Length(), IndexOf())
521                match method.to_lowercase().as_str() {
522                    "length" => {
523                        if !args.is_empty() {
524                            return Err(anyhow::anyhow!("Length() takes no arguments"));
525                        }
526                        self.evaluate_length(object, row_index)?
527                    }
528                    "indexof" => {
529                        if args.len() != 1 {
530                            return Err(anyhow::anyhow!("IndexOf() requires exactly 1 argument"));
531                        }
532                        let search_str = self.extract_string_value(&args[0])?;
533                        self.evaluate_indexof(object, &search_str, row_index)?
534                    }
535                    "trim" => {
536                        if !args.is_empty() {
537                            return Err(anyhow::anyhow!("Trim() takes no arguments"));
538                        }
539                        self.evaluate_trim(object, row_index, "trim")?
540                    }
541                    "trimstart" => {
542                        if !args.is_empty() {
543                            return Err(anyhow::anyhow!("TrimStart() takes no arguments"));
544                        }
545                        self.evaluate_trim(object, row_index, "trimstart")?
546                    }
547                    "trimend" => {
548                        if !args.is_empty() {
549                            return Err(anyhow::anyhow!("TrimEnd() takes no arguments"));
550                        }
551                        self.evaluate_trim(object, row_index, "trimend")?
552                    }
553                    _ => {
554                        return Err(anyhow::anyhow!(
555                            "Method '{}' cannot be used in comparisons",
556                            method
557                        ));
558                    }
559                }
560            }
561            SqlExpression::BinaryOp {
562                left: _expr_left,
563                op: arith_op,
564                right: _expr_right,
565            } if matches!(arith_op.as_str(), "+" | "-" | "*" | "/") => {
566                // Handle arithmetic expressions using ArithmeticEvaluator
567                let mut evaluator =
568                    ArithmeticEvaluator::with_date_notation(self.table, self.date_notation.clone());
569                let computed_value = evaluator.evaluate(left, row_index)?;
570                if row_index < 3 {
571                    debug!(
572                        "RecursiveWhereEvaluator: evaluate_binary_op() - computed arithmetic expression = {:?}",
573                        computed_value
574                    );
575                }
576                (Some(computed_value), "computed_expression".to_string())
577            }
578            SqlExpression::FunctionCall { name, .. } => {
579                // Handle function calls using ArithmeticEvaluator
580                let mut evaluator =
581                    ArithmeticEvaluator::with_date_notation(self.table, self.date_notation.clone());
582                let computed_value = evaluator.evaluate(left, row_index)?;
583                if row_index < 3 {
584                    debug!(
585                        "RecursiveWhereEvaluator: evaluate_binary_op() - computed function {} = {:?}",
586                        name, computed_value
587                    );
588                }
589                (Some(computed_value), format!("{name}()"))
590            }
591            _ => {
592                // Regular column reference
593                let column_name = self.extract_column_name(left)?;
594                if row_index < 3 {
595                    debug!(
596                        "RecursiveWhereEvaluator: evaluate_binary_op() - column_name = '{}'",
597                        column_name
598                    );
599                }
600
601                let col_index = self.table.get_column_index(&column_name).ok_or_else(|| {
602                    let suggestion = self.find_similar_column(&column_name);
603                    match suggestion {
604                        Some(similar) => anyhow!(
605                            "Column '{}' not found. Did you mean '{}'?",
606                            column_name,
607                            similar
608                        ),
609                        None => anyhow!("Column '{}' not found", column_name),
610                    }
611                })?;
612
613                let cell_value = self.table.get_value(row_index, col_index).cloned();
614                (cell_value, column_name)
615            }
616        };
617
618        if row_index < 3 {
619            debug!(
620                "RecursiveWhereEvaluator: evaluate_binary_op() - row {} column '{}' value = {:?}",
621                row_index, column_name, cell_value
622            );
623        }
624
625        // Get comparison value from right side
626        let compare_value = self.extract_value(right)?;
627
628        // Perform comparison
629        match (cell_value, op.to_uppercase().as_str(), &compare_value) {
630            (Some(DataValue::String(ref a)), "=", ExprValue::String(b)) => {
631                // Try to parse both as dates if they look like dates
632                if let (Ok(date_a), Ok(date_b)) = (
633                    self.parse_datetime_with_notation(a),
634                    self.parse_datetime_with_notation(b),
635                ) {
636                    if row_index < 3 {
637                        debug!(
638                            "RecursiveWhereEvaluator: Date comparison (from strings) '{}' = '{}' = {}",
639                            date_a.format("%Y-%m-%d %H:%M:%S"),
640                            date_b.format("%Y-%m-%d %H:%M:%S"),
641                            date_a == date_b
642                        );
643                    }
644                    Ok(date_a == date_b)
645                } else {
646                    if row_index < 3 {
647                        debug!(
648                            "RecursiveWhereEvaluator: String comparison '{}' = '{}' (case_insensitive={})",
649                            a, b, self.case_insensitive
650                        );
651                    }
652                    if self.case_insensitive {
653                        Ok(a.to_lowercase() == b.to_lowercase())
654                    } else {
655                        Ok(a == b)
656                    }
657                }
658            }
659            (Some(DataValue::InternedString(ref a)), "=", ExprValue::String(b)) => {
660                // Try to parse both as dates if they look like dates
661                if let (Ok(date_a), Ok(date_b)) = (
662                    self.parse_datetime_with_notation(a.as_ref()),
663                    self.parse_datetime_with_notation(b),
664                ) {
665                    if row_index < 3 {
666                        debug!(
667                            "RecursiveWhereEvaluator: Date comparison (from interned strings) '{}' = '{}' = {}",
668                            date_a.format("%Y-%m-%d %H:%M:%S"),
669                            date_b.format("%Y-%m-%d %H:%M:%S"),
670                            date_a == date_b
671                        );
672                    }
673                    Ok(date_a == date_b)
674                } else {
675                    if row_index < 3 {
676                        debug!(
677                            "RecursiveWhereEvaluator: InternedString comparison '{}' = '{}' (case_insensitive={})",
678                            a, b, self.case_insensitive
679                        );
680                    }
681                    if self.case_insensitive {
682                        Ok(a.to_lowercase() == b.to_lowercase())
683                    } else {
684                        Ok(a.as_ref() == b)
685                    }
686                }
687            }
688            (Some(DataValue::String(ref a)), "!=" | "<>", ExprValue::String(b)) => {
689                // Try to parse both as dates if they look like dates
690                if let (Ok(date_a), Ok(date_b)) = (
691                    self.parse_datetime_with_notation(a),
692                    self.parse_datetime_with_notation(b),
693                ) {
694                    if row_index < 3 {
695                        debug!(
696                            "RecursiveWhereEvaluator: Date comparison (from strings) '{}' != '{}' = {}",
697                            date_a.format("%Y-%m-%d %H:%M:%S"),
698                            date_b.format("%Y-%m-%d %H:%M:%S"),
699                            date_a != date_b
700                        );
701                    }
702                    Ok(date_a != date_b)
703                } else {
704                    if row_index < 3 {
705                        debug!(
706                            "RecursiveWhereEvaluator: String comparison '{}' != '{}' (case_insensitive={})",
707                            a, b, self.case_insensitive
708                        );
709                    }
710                    if self.case_insensitive {
711                        Ok(a.to_lowercase() != b.to_lowercase())
712                    } else {
713                        Ok(a != b)
714                    }
715                }
716            }
717            (Some(DataValue::InternedString(ref a)), "!=" | "<>", ExprValue::String(b)) => {
718                // Try to parse both as dates if they look like dates
719                if let (Ok(date_a), Ok(date_b)) = (
720                    self.parse_datetime_with_notation(a.as_ref()),
721                    self.parse_datetime_with_notation(b),
722                ) {
723                    if row_index < 3 {
724                        debug!(
725                            "RecursiveWhereEvaluator: Date comparison (from interned strings) '{}' != '{}' = {}",
726                            date_a.format("%Y-%m-%d %H:%M:%S"),
727                            date_b.format("%Y-%m-%d %H:%M:%S"),
728                            date_a != date_b
729                        );
730                    }
731                    Ok(date_a != date_b)
732                } else {
733                    if row_index < 3 {
734                        debug!(
735                            "RecursiveWhereEvaluator: InternedString comparison '{}' != '{}' (case_insensitive={})",
736                            a, b, self.case_insensitive
737                        );
738                    }
739                    if self.case_insensitive {
740                        Ok(a.to_lowercase() != b.to_lowercase())
741                    } else {
742                        Ok(a.as_ref() != b)
743                    }
744                }
745            }
746            (Some(DataValue::String(ref a)), ">", ExprValue::String(b)) => {
747                // Try to parse both as dates if they look like dates
748                if let (Ok(date_a), Ok(date_b)) = (
749                    self.parse_datetime_with_notation(a),
750                    self.parse_datetime_with_notation(b),
751                ) {
752                    if row_index < 3 {
753                        debug!(
754                            "RecursiveWhereEvaluator: Date comparison (from strings) '{}' > '{}' = {}",
755                            date_a.format("%Y-%m-%d %H:%M:%S"),
756                            date_b.format("%Y-%m-%d %H:%M:%S"),
757                            date_a > date_b
758                        );
759                    }
760                    Ok(date_a > date_b)
761                } else if self.case_insensitive {
762                    Ok(a.to_lowercase() > b.to_lowercase())
763                } else {
764                    Ok(a > b)
765                }
766            }
767            (Some(DataValue::InternedString(ref a)), ">", ExprValue::String(b)) => {
768                // Try to parse both as dates if they look like dates
769                if let (Ok(date_a), Ok(date_b)) = (
770                    self.parse_datetime_with_notation(a.as_ref()),
771                    self.parse_datetime_with_notation(b),
772                ) {
773                    if row_index < 3 {
774                        debug!(
775                            "RecursiveWhereEvaluator: Date comparison (from interned strings) '{}' > '{}' = {}",
776                            date_a.format("%Y-%m-%d %H:%M:%S"),
777                            date_b.format("%Y-%m-%d %H:%M:%S"),
778                            date_a > date_b
779                        );
780                    }
781                    Ok(date_a > date_b)
782                } else if self.case_insensitive {
783                    Ok(a.to_lowercase() > b.to_lowercase())
784                } else {
785                    Ok(a.as_ref() > b)
786                }
787            }
788            (Some(DataValue::String(ref a)), ">=", ExprValue::String(b)) => {
789                // Try to parse both as dates if they look like dates
790                if let (Ok(date_a), Ok(date_b)) = (
791                    self.parse_datetime_with_notation(a),
792                    self.parse_datetime_with_notation(b),
793                ) {
794                    if row_index < 3 {
795                        debug!(
796                            "RecursiveWhereEvaluator: Date comparison (from strings) '{}' >= '{}' = {}",
797                            date_a.format("%Y-%m-%d %H:%M:%S"),
798                            date_b.format("%Y-%m-%d %H:%M:%S"),
799                            date_a >= date_b
800                        );
801                    }
802                    Ok(date_a >= date_b)
803                } else if self.case_insensitive {
804                    Ok(a.to_lowercase() >= b.to_lowercase())
805                } else {
806                    Ok(a >= b)
807                }
808            }
809            (Some(DataValue::InternedString(ref a)), ">=", ExprValue::String(b)) => {
810                // Try to parse both as dates if they look like dates
811                if let (Ok(date_a), Ok(date_b)) = (
812                    self.parse_datetime_with_notation(a.as_ref()),
813                    self.parse_datetime_with_notation(b),
814                ) {
815                    if row_index < 3 {
816                        debug!(
817                            "RecursiveWhereEvaluator: Date comparison (from interned strings) '{}' >= '{}' = {}",
818                            date_a.format("%Y-%m-%d %H:%M:%S"),
819                            date_b.format("%Y-%m-%d %H:%M:%S"),
820                            date_a >= date_b
821                        );
822                    }
823                    Ok(date_a >= date_b)
824                } else if self.case_insensitive {
825                    Ok(a.to_lowercase() >= b.to_lowercase())
826                } else {
827                    Ok(a.as_ref() >= b)
828                }
829            }
830            (Some(DataValue::String(ref a)), "<", ExprValue::String(b)) => {
831                // Try to parse both as dates if they look like dates
832                if let (Ok(date_a), Ok(date_b)) = (
833                    self.parse_datetime_with_notation(a),
834                    self.parse_datetime_with_notation(b),
835                ) {
836                    if row_index < 3 {
837                        debug!(
838                            "RecursiveWhereEvaluator: Date comparison (from strings) '{}' < '{}' = {}",
839                            date_a.format("%Y-%m-%d %H:%M:%S"),
840                            date_b.format("%Y-%m-%d %H:%M:%S"),
841                            date_a < date_b
842                        );
843                    }
844                    Ok(date_a < date_b)
845                } else if self.case_insensitive {
846                    Ok(a.to_lowercase() < b.to_lowercase())
847                } else {
848                    Ok(a < b)
849                }
850            }
851            (Some(DataValue::InternedString(ref a)), "<", ExprValue::String(b)) => {
852                // Try to parse both as dates if they look like dates
853                if let (Ok(date_a), Ok(date_b)) = (
854                    self.parse_datetime_with_notation(a.as_ref()),
855                    self.parse_datetime_with_notation(b),
856                ) {
857                    if row_index < 3 {
858                        debug!(
859                            "RecursiveWhereEvaluator: Date comparison (from interned strings) '{}' < '{}' = {}",
860                            date_a.format("%Y-%m-%d %H:%M:%S"),
861                            date_b.format("%Y-%m-%d %H:%M:%S"),
862                            date_a < date_b
863                        );
864                    }
865                    Ok(date_a < date_b)
866                } else if self.case_insensitive {
867                    Ok(a.to_lowercase() < b.to_lowercase())
868                } else {
869                    Ok(a.as_ref() < b)
870                }
871            }
872            (Some(DataValue::String(ref a)), "<=", ExprValue::String(b)) => {
873                // Try to parse both as dates if they look like dates
874                if let (Ok(date_a), Ok(date_b)) = (
875                    self.parse_datetime_with_notation(a),
876                    self.parse_datetime_with_notation(b),
877                ) {
878                    if row_index < 3 {
879                        debug!(
880                            "RecursiveWhereEvaluator: Date comparison (from strings) '{}' <= '{}' = {}",
881                            date_a.format("%Y-%m-%d %H:%M:%S"),
882                            date_b.format("%Y-%m-%d %H:%M:%S"),
883                            date_a <= date_b
884                        );
885                    }
886                    Ok(date_a <= date_b)
887                } else if self.case_insensitive {
888                    Ok(a.to_lowercase() <= b.to_lowercase())
889                } else {
890                    Ok(a <= b)
891                }
892            }
893            (Some(DataValue::InternedString(ref a)), "<=", ExprValue::String(b)) => {
894                // Try to parse both as dates if they look like dates
895                if let (Ok(date_a), Ok(date_b)) = (
896                    self.parse_datetime_with_notation(a.as_ref()),
897                    self.parse_datetime_with_notation(b),
898                ) {
899                    if row_index < 3 {
900                        debug!(
901                            "RecursiveWhereEvaluator: Date comparison (from interned strings) '{}' <= '{}' = {}",
902                            date_a.format("%Y-%m-%d %H:%M:%S"),
903                            date_b.format("%Y-%m-%d %H:%M:%S"),
904                            date_a <= date_b
905                        );
906                    }
907                    Ok(date_a <= date_b)
908                } else if self.case_insensitive {
909                    Ok(a.to_lowercase() <= b.to_lowercase())
910                } else {
911                    Ok(a.as_ref() <= b)
912                }
913            }
914
915            (Some(DataValue::Integer(a)), "=", ExprValue::Number(b)) => Ok(a as f64 == *b),
916            (Some(DataValue::Integer(a)), "!=" | "<>", ExprValue::Number(b)) => Ok(a as f64 != *b),
917            (Some(DataValue::Integer(a)), ">", ExprValue::Number(b)) => Ok(a as f64 > *b),
918            (Some(DataValue::Integer(a)), ">=", ExprValue::Number(b)) => Ok(a as f64 >= *b),
919            (Some(DataValue::Integer(a)), "<", ExprValue::Number(b)) => Ok((a as f64) < *b),
920            (Some(DataValue::Integer(a)), "<=", ExprValue::Number(b)) => Ok(a as f64 <= *b),
921
922            (Some(DataValue::Float(a)), "=", ExprValue::Number(b)) => {
923                Ok((a - b).abs() < f64::EPSILON)
924            }
925            (Some(DataValue::Float(a)), "!=" | "<>", ExprValue::Number(b)) => {
926                Ok((a - b).abs() >= f64::EPSILON)
927            }
928            (Some(DataValue::Float(a)), ">", ExprValue::Number(b)) => Ok(a > *b),
929            (Some(DataValue::Float(a)), ">=", ExprValue::Number(b)) => Ok(a >= *b),
930            (Some(DataValue::Float(a)), "<", ExprValue::Number(b)) => Ok(a < *b),
931            (Some(DataValue::Float(a)), "<=", ExprValue::Number(b)) => Ok(a <= *b),
932
933            // Boolean comparisons
934            (Some(DataValue::Boolean(a)), "=", ExprValue::Boolean(b)) => Ok(a == *b),
935            (Some(DataValue::Boolean(a)), "!=" | "<>", ExprValue::Boolean(b)) => Ok(a != *b),
936
937            // Boolean to string comparisons (for backward compatibility)
938            (Some(DataValue::Boolean(a)), "=", ExprValue::String(b)) => {
939                let bool_str = a.to_string();
940                if self.case_insensitive {
941                    Ok(bool_str.to_lowercase() == b.to_lowercase())
942                } else {
943                    Ok(bool_str == *b)
944                }
945            }
946            (Some(DataValue::Boolean(a)), "!=" | "<>", ExprValue::String(b)) => {
947                let bool_str = a.to_string();
948                if self.case_insensitive {
949                    Ok(bool_str.to_lowercase() != b.to_lowercase())
950                } else {
951                    Ok(bool_str != *b)
952                }
953            }
954
955            // LIKE operator
956            (Some(DataValue::String(ref text)), "LIKE", ExprValue::String(pattern)) => {
957                let regex_pattern = pattern.replace('%', ".*").replace('_', ".");
958                let regex = regex::RegexBuilder::new(&format!("^{regex_pattern}$"))
959                    .case_insensitive(true)
960                    .build()
961                    .map_err(|e| anyhow::anyhow!("Invalid LIKE pattern: {}", e))?;
962                Ok(regex.is_match(text))
963            }
964            (Some(DataValue::InternedString(ref text)), "LIKE", ExprValue::String(pattern)) => {
965                let regex_pattern = pattern.replace('%', ".*").replace('_', ".");
966                let regex = regex::RegexBuilder::new(&format!("^{regex_pattern}$"))
967                    .case_insensitive(true)
968                    .build()
969                    .map_err(|e| anyhow::anyhow!("Invalid LIKE pattern: {}", e))?;
970                Ok(regex.is_match(text.as_ref()))
971            }
972
973            // IS NULL / IS NOT NULL (old style)
974            (None | Some(DataValue::Null), "IS", ExprValue::Null) => Ok(true),
975            (Some(_), "IS", ExprValue::Null) => Ok(false),
976            (None | Some(DataValue::Null), "IS NOT", ExprValue::Null) => Ok(false),
977            (Some(_), "IS NOT", ExprValue::Null) => Ok(true),
978
979            // IS NULL / IS NOT NULL (new style with combined operator)
980            (None | Some(DataValue::Null), "IS NULL", _) => Ok(true),
981            (Some(_), "IS NULL", _) => Ok(false),
982            (None | Some(DataValue::Null), "IS NOT NULL", _) => Ok(false),
983            (Some(_), "IS NOT NULL", _) => Ok(true),
984
985            // DateTime comparisons
986            (Some(DataValue::String(ref date_str)), op_str, ExprValue::DateTime(dt)) => {
987                if row_index < 3 {
988                    debug!(
989                        "RecursiveWhereEvaluator: DateTime comparison '{}' {} '{}' - attempting parse",
990                        date_str,
991                        op_str,
992                        dt.format("%Y-%m-%d %H:%M:%S")
993                    );
994                }
995
996                // Use the central parsing function that respects date_notation
997                if let Ok(parsed_dt) = self.parse_datetime_with_notation(date_str) {
998                    let result = Self::compare_datetime(op_str, &parsed_dt, dt);
999                    if row_index < 3 {
1000                        debug!(
1001                            "RecursiveWhereEvaluator: DateTime parsed successfully: '{}' {} '{}' = {}",
1002                            parsed_dt.format("%Y-%m-%d %H:%M:%S"),
1003                            op_str,
1004                            dt.format("%Y-%m-%d %H:%M:%S"),
1005                            result
1006                        );
1007                    }
1008                    Ok(result)
1009                } else {
1010                    if row_index < 3 {
1011                        debug!(
1012                            "RecursiveWhereEvaluator: DateTime parse FAILED for '{}' - no matching format",
1013                            date_str
1014                        );
1015                    }
1016                    Ok(false)
1017                }
1018            }
1019            (Some(DataValue::InternedString(ref date_str)), op_str, ExprValue::DateTime(dt)) => {
1020                if row_index < 3 {
1021                    debug!(
1022                        "RecursiveWhereEvaluator: DateTime comparison (interned) '{}' {} '{}' - attempting parse",
1023                        date_str,
1024                        op_str,
1025                        dt.format("%Y-%m-%d %H:%M:%S")
1026                    );
1027                }
1028
1029                // Use the central parsing function that respects date_notation
1030                if let Ok(parsed_dt) = self.parse_datetime_with_notation(date_str.as_ref()) {
1031                    let result = Self::compare_datetime(op_str, &parsed_dt, dt);
1032                    if row_index < 3 {
1033                        debug!(
1034                            "RecursiveWhereEvaluator: DateTime parsed successfully: '{}' {} '{}' = {}",
1035                            parsed_dt.format("%Y-%m-%d %H:%M:%S"),
1036                            op_str,
1037                            dt.format("%Y-%m-%d %H:%M:%S"),
1038                            result
1039                        );
1040                    }
1041                    Ok(result)
1042                } else {
1043                    if row_index < 3 {
1044                        debug!(
1045                            "RecursiveWhereEvaluator: DateTime parse FAILED for '{}' - no matching format",
1046                            date_str
1047                        );
1048                    }
1049                    Ok(false)
1050                }
1051            }
1052
1053            // DateTime vs DateTime comparisons (when column is already parsed as DateTime)
1054            (Some(DataValue::DateTime(ref date_str)), op_str, ExprValue::DateTime(dt)) => {
1055                if row_index < 3 {
1056                    debug!(
1057                        "RecursiveWhereEvaluator: DateTime vs DateTime comparison '{}' {} '{}' - direct comparison",
1058                        date_str, op_str, dt.format("%Y-%m-%d %H:%M:%S")
1059                    );
1060                }
1061
1062                // Use the central parsing function that respects date_notation
1063                if let Ok(parsed_dt) = self.parse_datetime_with_notation(date_str) {
1064                    let result = Self::compare_datetime(op_str, &parsed_dt, dt);
1065                    if row_index < 3 {
1066                        debug!(
1067                            "RecursiveWhereEvaluator: DateTime vs DateTime parsed successfully: '{}' {} '{}' = {}",
1068                            parsed_dt.format("%Y-%m-%d %H:%M:%S"), op_str, dt.format("%Y-%m-%d %H:%M:%S"), result
1069                        );
1070                    }
1071                    Ok(result)
1072                } else {
1073                    if row_index < 3 {
1074                        debug!(
1075                            "RecursiveWhereEvaluator: DateTime vs DateTime parse FAILED for '{}' - no matching format",
1076                            date_str
1077                        );
1078                    }
1079                    Ok(false)
1080                }
1081            }
1082
1083            _ => Ok(false),
1084        }
1085    }
1086
1087    fn evaluate_in_list(
1088        &self,
1089        expr: &SqlExpression,
1090        values: &[SqlExpression],
1091        row_index: usize,
1092        _ignore_case: bool,
1093    ) -> Result<bool> {
1094        let column_name = self.extract_column_name(expr)?;
1095        let col_index = self
1096            .table
1097            .get_column_index(&column_name)
1098            .ok_or_else(|| anyhow::anyhow!("Column '{}' not found", column_name))?;
1099
1100        let cell_value = self.table.get_value(row_index, col_index).cloned();
1101
1102        for value_expr in values {
1103            let compare_value = self.extract_value(value_expr)?;
1104            let matches = match (cell_value.as_ref(), &compare_value) {
1105                (Some(DataValue::String(a)), ExprValue::String(b)) => {
1106                    if self.case_insensitive {
1107                        if row_index < 3 {
1108                            debug!("RecursiveWhereEvaluator: IN list string comparison '{}' in '{}' (case_insensitive={})", a, b, self.case_insensitive);
1109                        }
1110                        a.to_lowercase() == b.to_lowercase()
1111                    } else {
1112                        a == b
1113                    }
1114                }
1115                (Some(DataValue::InternedString(a)), ExprValue::String(b)) => {
1116                    if self.case_insensitive {
1117                        if row_index < 3 {
1118                            debug!("RecursiveWhereEvaluator: IN list interned string comparison '{}' in '{}' (case_insensitive={})", a, b, self.case_insensitive);
1119                        }
1120                        a.to_lowercase() == b.to_lowercase()
1121                    } else {
1122                        a.as_ref() == b
1123                    }
1124                }
1125                (Some(DataValue::Integer(a)), ExprValue::Number(b)) => *a as f64 == *b,
1126                (Some(DataValue::Float(a)), ExprValue::Number(b)) => (*a - b).abs() < f64::EPSILON,
1127                _ => false,
1128            };
1129
1130            if matches {
1131                return Ok(true);
1132            }
1133        }
1134
1135        Ok(false)
1136    }
1137
1138    fn evaluate_between(
1139        &self,
1140        expr: &SqlExpression,
1141        lower: &SqlExpression,
1142        upper: &SqlExpression,
1143        row_index: usize,
1144    ) -> Result<bool> {
1145        let column_name = self.extract_column_name(expr)?;
1146        let col_index = self
1147            .table
1148            .get_column_index(&column_name)
1149            .ok_or_else(|| anyhow::anyhow!("Column '{}' not found", column_name))?;
1150
1151        let cell_value = self.table.get_value(row_index, col_index).cloned();
1152        let lower_value = self.extract_value(lower)?;
1153        let upper_value = self.extract_value(upper)?;
1154
1155        match (cell_value, &lower_value, &upper_value) {
1156            (Some(DataValue::Integer(n)), ExprValue::Number(l), ExprValue::Number(u)) => {
1157                Ok(n as f64 >= *l && n as f64 <= *u)
1158            }
1159            (Some(DataValue::Float(n)), ExprValue::Number(l), ExprValue::Number(u)) => {
1160                Ok(n >= *l && n <= *u)
1161            }
1162            (Some(DataValue::String(ref s)), ExprValue::String(l), ExprValue::String(u)) => {
1163                Ok(s >= l && s <= u)
1164            }
1165            (
1166                Some(DataValue::InternedString(ref s)),
1167                ExprValue::String(l),
1168                ExprValue::String(u),
1169            ) => Ok(s.as_ref() >= l && s.as_ref() <= u),
1170            _ => Ok(false),
1171        }
1172    }
1173
1174    fn evaluate_method_call(
1175        &self,
1176        object: &str,
1177        method: &str,
1178        args: &[SqlExpression],
1179        row_index: usize,
1180    ) -> Result<bool> {
1181        if row_index < 3 {
1182            debug!(
1183                "RecursiveWhereEvaluator: evaluate_method_call - object='{}', method='{}', row={}",
1184                object, method, row_index
1185            );
1186        }
1187
1188        // Get column value
1189        let col_index = self.table.get_column_index(object).ok_or_else(|| {
1190            let suggestion = self.find_similar_column(object);
1191            match suggestion {
1192                Some(similar) => {
1193                    anyhow!("Column '{}' not found. Did you mean '{}'?", object, similar)
1194                }
1195                None => anyhow!("Column '{}' not found", object),
1196            }
1197        })?;
1198
1199        let cell_value = self.table.get_value(row_index, col_index).cloned();
1200        if row_index < 3 {
1201            debug!(
1202                "RecursiveWhereEvaluator: Row {} column '{}' value = {:?}",
1203                row_index, object, cell_value
1204            );
1205        }
1206
1207        match method.to_lowercase().as_str() {
1208            "contains" => {
1209                if args.len() != 1 {
1210                    return Err(anyhow::anyhow!("Contains requires exactly 1 argument"));
1211                }
1212                let search_str = self.extract_string_value(&args[0])?;
1213                // Pre-compute lowercase once instead of for every row
1214                let search_lower = search_str.to_lowercase();
1215
1216                // Type coercion: convert numeric values to strings for string methods
1217                match cell_value {
1218                    Some(DataValue::String(ref s)) => {
1219                        let result = s.to_lowercase().contains(&search_lower);
1220                        // Only log first few rows to avoid performance impact
1221                        if row_index < 3 {
1222                            debug!("RecursiveWhereEvaluator: Row {} contains('{}') on '{}' = {} (case-insensitive)", row_index, search_str, s, result);
1223                        }
1224                        Ok(result)
1225                    }
1226                    Some(DataValue::InternedString(ref s)) => {
1227                        let result = s.to_lowercase().contains(&search_lower);
1228                        // Only log first few rows to avoid performance impact
1229                        if row_index < 3 {
1230                            debug!("RecursiveWhereEvaluator: Row {} contains('{}') on interned '{}' = {} (case-insensitive)", row_index, search_str, s, result);
1231                        }
1232                        Ok(result)
1233                    }
1234                    Some(DataValue::Integer(n)) => {
1235                        let str_val = n.to_string();
1236                        let result = str_val.contains(&search_str);
1237                        if row_index < 3 {
1238                            debug!("RecursiveWhereEvaluator: Row {} contains('{}') on integer '{}' = {}", row_index, search_str, str_val, result);
1239                        }
1240                        Ok(result)
1241                    }
1242                    Some(DataValue::Float(f)) => {
1243                        let str_val = f.to_string();
1244                        let result = str_val.contains(&search_str);
1245                        if row_index < 3 {
1246                            debug!(
1247                                "RecursiveWhereEvaluator: Row {} contains('{}') on float '{}' = {}",
1248                                row_index, search_str, str_val, result
1249                            );
1250                        }
1251                        Ok(result)
1252                    }
1253                    Some(DataValue::Boolean(b)) => {
1254                        let str_val = b.to_string();
1255                        let result = str_val.contains(&search_str);
1256                        if row_index < 3 {
1257                            debug!("RecursiveWhereEvaluator: Row {} contains('{}') on boolean '{}' = {}", row_index, search_str, str_val, result);
1258                        }
1259                        Ok(result)
1260                    }
1261                    Some(DataValue::DateTime(dt)) => {
1262                        // DateTime columns can use string methods via coercion
1263                        let result = dt.contains(&search_str);
1264                        if row_index < 3 {
1265                            debug!("RecursiveWhereEvaluator: Row {} contains('{}') on datetime '{}' = {}", row_index, search_str, dt, result);
1266                        }
1267                        Ok(result)
1268                    }
1269                    _ => {
1270                        if row_index < 3 {
1271                            debug!("RecursiveWhereEvaluator: Row {} contains('{}') on null/empty value = false", row_index, search_str);
1272                        }
1273                        Ok(false)
1274                    }
1275                }
1276            }
1277            "startswith" => {
1278                if args.len() != 1 {
1279                    return Err(anyhow::anyhow!("StartsWith requires exactly 1 argument"));
1280                }
1281                let prefix = self.extract_string_value(&args[0])?;
1282
1283                // Type coercion: convert numeric values to strings for string methods
1284                match cell_value {
1285                    Some(DataValue::String(ref s)) => {
1286                        Ok(s.to_lowercase().starts_with(&prefix.to_lowercase()))
1287                    }
1288                    Some(DataValue::InternedString(ref s)) => {
1289                        Ok(s.to_lowercase().starts_with(&prefix.to_lowercase()))
1290                    }
1291                    Some(DataValue::Integer(n)) => Ok(n.to_string().starts_with(&prefix)),
1292                    Some(DataValue::Float(f)) => Ok(f.to_string().starts_with(&prefix)),
1293                    Some(DataValue::Boolean(b)) => Ok(b.to_string().starts_with(&prefix)),
1294                    Some(DataValue::DateTime(dt)) => Ok(dt.starts_with(&prefix)),
1295                    _ => Ok(false),
1296                }
1297            }
1298            "endswith" => {
1299                if args.len() != 1 {
1300                    return Err(anyhow::anyhow!("EndsWith requires exactly 1 argument"));
1301                }
1302                let suffix = self.extract_string_value(&args[0])?;
1303
1304                // Type coercion: convert numeric values to strings for string methods
1305                match cell_value {
1306                    Some(DataValue::String(ref s)) => {
1307                        Ok(s.to_lowercase().ends_with(&suffix.to_lowercase()))
1308                    }
1309                    Some(DataValue::InternedString(ref s)) => {
1310                        Ok(s.to_lowercase().ends_with(&suffix.to_lowercase()))
1311                    }
1312                    Some(DataValue::Integer(n)) => Ok(n.to_string().ends_with(&suffix)),
1313                    Some(DataValue::Float(f)) => Ok(f.to_string().ends_with(&suffix)),
1314                    Some(DataValue::Boolean(b)) => Ok(b.to_string().ends_with(&suffix)),
1315                    Some(DataValue::DateTime(dt)) => Ok(dt.ends_with(&suffix)),
1316                    _ => Ok(false),
1317                }
1318            }
1319            _ => Err(anyhow::anyhow!("Unsupported method: {}", method)),
1320        }
1321    }
1322
1323    fn extract_column_name(&self, expr: &SqlExpression) -> Result<String> {
1324        match expr {
1325            SqlExpression::Column(name) => Ok(name.clone()),
1326            _ => Err(anyhow::anyhow!("Expected column name, got: {:?}", expr)),
1327        }
1328    }
1329
1330    fn extract_string_value(&self, expr: &SqlExpression) -> Result<String> {
1331        match expr {
1332            SqlExpression::StringLiteral(s) => Ok(s.clone()),
1333            _ => Err(anyhow::anyhow!("Expected string literal, got: {:?}", expr)),
1334        }
1335    }
1336
1337    fn extract_value(&self, expr: &SqlExpression) -> Result<ExprValue> {
1338        match expr {
1339            SqlExpression::StringLiteral(s) => Ok(ExprValue::String(s.clone())),
1340            SqlExpression::BooleanLiteral(b) => Ok(ExprValue::Boolean(*b)),
1341            SqlExpression::NumberLiteral(n) => {
1342                if let Ok(num) = n.parse::<f64>() {
1343                    Ok(ExprValue::Number(num))
1344                } else {
1345                    Ok(ExprValue::String(n.clone()))
1346                }
1347            }
1348            SqlExpression::DateTimeConstructor {
1349                year,
1350                month,
1351                day,
1352                hour,
1353                minute,
1354                second,
1355            } => {
1356                // Create a DateTime from the constructor
1357                let naive_date = NaiveDate::from_ymd_opt(*year, *month, *day)
1358                    .ok_or_else(|| anyhow::anyhow!("Invalid date: {}-{}-{}", year, month, day))?;
1359                let naive_time = NaiveTime::from_hms_opt(
1360                    hour.unwrap_or(0),
1361                    minute.unwrap_or(0),
1362                    second.unwrap_or(0),
1363                )
1364                .ok_or_else(|| anyhow::anyhow!("Invalid time"))?;
1365                let naive_datetime = NaiveDateTime::new(naive_date, naive_time);
1366                let datetime = Utc.from_utc_datetime(&naive_datetime);
1367                Ok(ExprValue::DateTime(datetime))
1368            }
1369            SqlExpression::DateTimeToday {
1370                hour,
1371                minute,
1372                second,
1373            } => {
1374                // Get today's date with optional time
1375                let today = Local::now().date_naive();
1376                let time = NaiveTime::from_hms_opt(
1377                    hour.unwrap_or(0),
1378                    minute.unwrap_or(0),
1379                    second.unwrap_or(0),
1380                )
1381                .ok_or_else(|| anyhow::anyhow!("Invalid time"))?;
1382                let naive_datetime = NaiveDateTime::new(today, time);
1383                let datetime = Utc.from_utc_datetime(&naive_datetime);
1384                Ok(ExprValue::DateTime(datetime))
1385            }
1386            _ => Ok(ExprValue::Null),
1387        }
1388    }
1389
1390    /// Evaluate a CASE expression as a boolean (for WHERE clauses)
1391    fn evaluate_case_expression_as_bool(
1392        &self,
1393        when_branches: &[crate::sql::recursive_parser::WhenBranch],
1394        else_branch: &Option<Box<SqlExpression>>,
1395        row_index: usize,
1396    ) -> Result<bool> {
1397        debug!(
1398            "RecursiveWhereEvaluator: evaluating CASE expression as bool for row {}",
1399            row_index
1400        );
1401
1402        // Evaluate each WHEN condition in order
1403        for branch in when_branches {
1404            // Evaluate the condition as a boolean
1405            let condition_result = self.evaluate_expression(&branch.condition, row_index)?;
1406
1407            if condition_result {
1408                debug!("CASE: WHEN condition matched, evaluating result expression as bool");
1409                // Evaluate the result and convert to boolean
1410                return self.evaluate_expression_as_bool(&branch.result, row_index);
1411            }
1412        }
1413
1414        // If no WHEN condition matched, evaluate ELSE clause (or return false)
1415        if let Some(else_expr) = else_branch {
1416            debug!("CASE: No WHEN matched, evaluating ELSE expression as bool");
1417            self.evaluate_expression_as_bool(else_expr, row_index)
1418        } else {
1419            debug!("CASE: No WHEN matched and no ELSE, returning false");
1420            Ok(false)
1421        }
1422    }
1423
1424    /// Helper method to evaluate any expression as a boolean
1425    fn evaluate_expression_as_bool(&self, expr: &SqlExpression, row_index: usize) -> Result<bool> {
1426        match expr {
1427            // For expressions that naturally return booleans, use the existing evaluator
1428            SqlExpression::BinaryOp { .. }
1429            | SqlExpression::InList { .. }
1430            | SqlExpression::NotInList { .. }
1431            | SqlExpression::Between { .. }
1432            | SqlExpression::Not { .. }
1433            | SqlExpression::MethodCall { .. } => self.evaluate_expression(expr, row_index),
1434            // For CASE expressions, recurse
1435            SqlExpression::CaseExpression {
1436                when_branches,
1437                else_branch,
1438            } => self.evaluate_case_expression_as_bool(when_branches, else_branch, row_index),
1439            // For other expressions (columns, literals), use ArithmeticEvaluator and convert
1440            _ => {
1441                // Use ArithmeticEvaluator to get the value, then convert to boolean
1442                let mut evaluator =
1443                    crate::data::arithmetic_evaluator::ArithmeticEvaluator::with_date_notation(
1444                        self.table,
1445                        self.date_notation.clone(),
1446                    );
1447                let value = evaluator.evaluate(expr, row_index)?;
1448
1449                match value {
1450                    crate::data::datatable::DataValue::Boolean(b) => Ok(b),
1451                    crate::data::datatable::DataValue::Integer(i) => Ok(i != 0),
1452                    crate::data::datatable::DataValue::Float(f) => Ok(f != 0.0),
1453                    crate::data::datatable::DataValue::Null => Ok(false),
1454                    crate::data::datatable::DataValue::String(s) => Ok(!s.is_empty()),
1455                    crate::data::datatable::DataValue::InternedString(s) => Ok(!s.is_empty()),
1456                    _ => Ok(true), // Other types are considered truthy
1457                }
1458            }
1459        }
1460    }
1461}
1462
1463enum ExprValue {
1464    String(String),
1465    Number(f64),
1466    Boolean(bool),
1467    DateTime(DateTime<Utc>),
1468    Null,
1469}
sql_cli/data/recursive_where_evaluator.rs

sql_cli/data/
recursive_where_evaluator.rs
