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

sql_cli/data/
recursive_where_evaluator.rs
