sql_cli/data/
arithmetic_evaluator.rs

1use crate::data::datatable::{DataTable, DataValue};
2use crate::sql::recursive_parser::SqlExpression;
3use anyhow::{anyhow, Result};
4use chrono::{DateTime, Datelike, NaiveDate, NaiveDateTime, TimeZone, Utc};
5use tracing::debug;
6
7/// Evaluates SQL expressions to compute DataValues (for SELECT clauses)
8/// This is different from RecursiveWhereEvaluator which returns boolean
9pub struct ArithmeticEvaluator<'a> {
10    table: &'a DataTable,
11}
12
13impl<'a> ArithmeticEvaluator<'a> {
14    pub fn new(table: &'a DataTable) -> Self {
15        Self { table }
16    }
17
18    /// Find a column name similar to the given name using edit distance
19    fn find_similar_column(&self, name: &str) -> Option<String> {
20        let columns = self.table.column_names();
21        let mut best_match: Option<(String, usize)> = None;
22
23        for col in columns {
24            let distance = self.edit_distance(&col.to_lowercase(), &name.to_lowercase());
25            // Only suggest if distance is small (likely a typo)
26            // Allow up to 3 edits for longer names
27            let max_distance = if name.len() > 10 { 3 } else { 2 };
28            if distance <= max_distance {
29                match &best_match {
30                    None => best_match = Some((col, distance)),
31                    Some((_, best_dist)) if distance < *best_dist => {
32                        best_match = Some((col, distance));
33                    }
34                    _ => {}
35                }
36            }
37        }
38
39        best_match.map(|(name, _)| name)
40    }
41
42    /// Calculate Levenshtein edit distance between two strings
43    fn edit_distance(&self, s1: &str, s2: &str) -> usize {
44        let len1 = s1.len();
45        let len2 = s2.len();
46        let mut matrix = vec![vec![0; len2 + 1]; len1 + 1];
47
48        for i in 0..=len1 {
49            matrix[i][0] = i;
50        }
51        for j in 0..=len2 {
52            matrix[0][j] = j;
53        }
54
55        for (i, c1) in s1.chars().enumerate() {
56            for (j, c2) in s2.chars().enumerate() {
57                let cost = if c1 == c2 { 0 } else { 1 };
58                matrix[i + 1][j + 1] = std::cmp::min(
59                    matrix[i][j + 1] + 1, // deletion
60                    std::cmp::min(
61                        matrix[i + 1][j] + 1, // insertion
62                        matrix[i][j] + cost,  // substitution
63                    ),
64                );
65            }
66        }
67
68        matrix[len1][len2]
69    }
70
71    /// Evaluate an SQL expression to produce a DataValue
72    pub fn evaluate(&self, expr: &SqlExpression, row_index: usize) -> Result<DataValue> {
73        debug!(
74            "ArithmeticEvaluator: evaluating {:?} for row {}",
75            expr, row_index
76        );
77
78        match expr {
79            SqlExpression::Column(column_name) => self.evaluate_column(column_name, row_index),
80            SqlExpression::StringLiteral(s) => Ok(DataValue::String(s.clone())),
81            SqlExpression::NumberLiteral(n) => self.evaluate_number_literal(n),
82            SqlExpression::BinaryOp { left, op, right } => {
83                self.evaluate_binary_op(left, op, right, row_index)
84            }
85            SqlExpression::FunctionCall { name, args } => {
86                self.evaluate_function(name, args, row_index)
87            }
88            SqlExpression::MethodCall {
89                object,
90                method,
91                args,
92            } => self.evaluate_method_call(object, method, args, row_index),
93            SqlExpression::ChainedMethodCall { base, method, args } => {
94                // Evaluate the base expression first, then apply the method
95                let base_value = self.evaluate(base, row_index)?;
96                self.evaluate_method_on_value(&base_value, method, args, row_index)
97            }
98            SqlExpression::CaseExpression {
99                when_branches,
100                else_branch,
101            } => self.evaluate_case_expression(when_branches, else_branch, row_index),
102            _ => Err(anyhow!(
103                "Unsupported expression type for arithmetic evaluation: {:?}",
104                expr
105            )),
106        }
107    }
108
109    /// Evaluate a column reference
110    fn evaluate_column(&self, column_name: &str, row_index: usize) -> Result<DataValue> {
111        let col_index = self.table.get_column_index(column_name).ok_or_else(|| {
112            let suggestion = self.find_similar_column(column_name);
113            match suggestion {
114                Some(similar) => anyhow!(
115                    "Column '{}' not found. Did you mean '{}'?",
116                    column_name,
117                    similar
118                ),
119                None => anyhow!("Column '{}' not found", column_name),
120            }
121        })?;
122
123        if row_index >= self.table.row_count() {
124            return Err(anyhow!("Row index {} out of bounds", row_index));
125        }
126
127        let row = self
128            .table
129            .get_row(row_index)
130            .ok_or_else(|| anyhow!("Row {} not found", row_index))?;
131
132        let value = row
133            .get(col_index)
134            .ok_or_else(|| anyhow!("Column index {} out of bounds for row", col_index))?;
135
136        Ok(value.clone())
137    }
138
139    /// Evaluate a number literal (handles both integers and floats)
140    fn evaluate_number_literal(&self, number_str: &str) -> Result<DataValue> {
141        // Try to parse as integer first
142        if let Ok(int_val) = number_str.parse::<i64>() {
143            return Ok(DataValue::Integer(int_val));
144        }
145
146        // If that fails, try as float
147        if let Ok(float_val) = number_str.parse::<f64>() {
148            return Ok(DataValue::Float(float_val));
149        }
150
151        Err(anyhow!("Invalid number literal: {}", number_str))
152    }
153
154    /// Evaluate a binary operation (arithmetic)
155    fn evaluate_binary_op(
156        &self,
157        left: &SqlExpression,
158        op: &str,
159        right: &SqlExpression,
160        row_index: usize,
161    ) -> Result<DataValue> {
162        let left_val = self.evaluate(left, row_index)?;
163        let right_val = self.evaluate(right, row_index)?;
164
165        debug!(
166            "ArithmeticEvaluator: {} {} {}",
167            self.format_value(&left_val),
168            op,
169            self.format_value(&right_val)
170        );
171
172        match op {
173            "+" => self.add_values(&left_val, &right_val),
174            "-" => self.subtract_values(&left_val, &right_val),
175            "*" => self.multiply_values(&left_val, &right_val),
176            "/" => self.divide_values(&left_val, &right_val),
177            // Comparison operators (return boolean results)
178            ">" => self.compare_values(&left_val, &right_val, |a, b| a > b),
179            "<" => self.compare_values(&left_val, &right_val, |a, b| a < b),
180            ">=" => self.compare_values(&left_val, &right_val, |a, b| a >= b),
181            "<=" => self.compare_values(&left_val, &right_val, |a, b| a <= b),
182            "=" => self.compare_values(&left_val, &right_val, |a, b| a == b),
183            "!=" | "<>" => self.compare_values(&left_val, &right_val, |a, b| a != b),
184            _ => Err(anyhow!("Unsupported arithmetic operator: {}", op)),
185        }
186    }
187
188    /// Add two DataValues with type coercion
189    fn add_values(&self, left: &DataValue, right: &DataValue) -> Result<DataValue> {
190        match (left, right) {
191            (DataValue::Integer(a), DataValue::Integer(b)) => Ok(DataValue::Integer(a + b)),
192            (DataValue::Integer(a), DataValue::Float(b)) => Ok(DataValue::Float(*a as f64 + b)),
193            (DataValue::Float(a), DataValue::Integer(b)) => Ok(DataValue::Float(a + *b as f64)),
194            (DataValue::Float(a), DataValue::Float(b)) => Ok(DataValue::Float(a + b)),
195            _ => Err(anyhow!("Cannot add {:?} and {:?}", left, right)),
196        }
197    }
198
199    /// Subtract two DataValues with type coercion
200    fn subtract_values(&self, left: &DataValue, right: &DataValue) -> Result<DataValue> {
201        match (left, right) {
202            (DataValue::Integer(a), DataValue::Integer(b)) => Ok(DataValue::Integer(a - b)),
203            (DataValue::Integer(a), DataValue::Float(b)) => Ok(DataValue::Float(*a as f64 - b)),
204            (DataValue::Float(a), DataValue::Integer(b)) => Ok(DataValue::Float(a - *b as f64)),
205            (DataValue::Float(a), DataValue::Float(b)) => Ok(DataValue::Float(a - b)),
206            _ => Err(anyhow!("Cannot subtract {:?} and {:?}", left, right)),
207        }
208    }
209
210    /// Multiply two DataValues with type coercion
211    fn multiply_values(&self, left: &DataValue, right: &DataValue) -> Result<DataValue> {
212        match (left, right) {
213            (DataValue::Integer(a), DataValue::Integer(b)) => Ok(DataValue::Integer(a * b)),
214            (DataValue::Integer(a), DataValue::Float(b)) => Ok(DataValue::Float(*a as f64 * b)),
215            (DataValue::Float(a), DataValue::Integer(b)) => Ok(DataValue::Float(a * *b as f64)),
216            (DataValue::Float(a), DataValue::Float(b)) => Ok(DataValue::Float(a * b)),
217            _ => Err(anyhow!("Cannot multiply {:?} and {:?}", left, right)),
218        }
219    }
220
221    /// Divide two DataValues with type coercion
222    fn divide_values(&self, left: &DataValue, right: &DataValue) -> Result<DataValue> {
223        // Check for division by zero first
224        let is_zero = match right {
225            DataValue::Integer(0) => true,
226            DataValue::Float(f) if *f == 0.0 => true, // Only check for exact zero, not epsilon
227            _ => false,
228        };
229
230        if is_zero {
231            return Err(anyhow!("Division by zero"));
232        }
233
234        match (left, right) {
235            (DataValue::Integer(a), DataValue::Integer(b)) => {
236                // Integer division - if result is exact, keep as int, otherwise promote to float
237                if a % b == 0 {
238                    Ok(DataValue::Integer(a / b))
239                } else {
240                    Ok(DataValue::Float(*a as f64 / *b as f64))
241                }
242            }
243            (DataValue::Integer(a), DataValue::Float(b)) => Ok(DataValue::Float(*a as f64 / b)),
244            (DataValue::Float(a), DataValue::Integer(b)) => Ok(DataValue::Float(a / *b as f64)),
245            (DataValue::Float(a), DataValue::Float(b)) => Ok(DataValue::Float(a / b)),
246            _ => Err(anyhow!("Cannot divide {:?} and {:?}", left, right)),
247        }
248    }
249
250    /// Format a DataValue for debug output
251    fn format_value(&self, value: &DataValue) -> String {
252        match value {
253            DataValue::Integer(i) => i.to_string(),
254            DataValue::Float(f) => f.to_string(),
255            DataValue::String(s) => format!("'{}'", s),
256            _ => format!("{:?}", value),
257        }
258    }
259
260    /// Compare two DataValues using the provided comparison function
261    fn compare_values<F>(&self, left: &DataValue, right: &DataValue, op: F) -> Result<DataValue>
262    where
263        F: Fn(f64, f64) -> bool,
264    {
265        debug!(
266            "ArithmeticEvaluator: comparing values {:?} and {:?}",
267            left, right
268        );
269
270        let result = match (left, right) {
271            // Integer comparisons
272            (DataValue::Integer(a), DataValue::Integer(b)) => op(*a as f64, *b as f64),
273            (DataValue::Integer(a), DataValue::Float(b)) => op(*a as f64, *b),
274            (DataValue::Float(a), DataValue::Integer(b)) => op(*a, *b as f64),
275            (DataValue::Float(a), DataValue::Float(b)) => op(*a, *b),
276
277            // String comparisons (lexicographic)
278            (DataValue::String(a), DataValue::String(b)) => {
279                let a_num = a.parse::<f64>();
280                let b_num = b.parse::<f64>();
281                match (a_num, b_num) {
282                    (Ok(a_val), Ok(b_val)) => op(a_val, b_val), // Both are numbers
283                    _ => op(a.len() as f64, b.len() as f64),    // Fallback to length comparison
284                }
285            }
286            (DataValue::InternedString(a), DataValue::InternedString(b)) => {
287                let a_num = a.parse::<f64>();
288                let b_num = b.parse::<f64>();
289                match (a_num, b_num) {
290                    (Ok(a_val), Ok(b_val)) => op(a_val, b_val), // Both are numbers
291                    _ => op(a.len() as f64, b.len() as f64),    // Fallback to length comparison
292                }
293            }
294            (DataValue::String(a), DataValue::InternedString(b)) => {
295                let a_num = a.parse::<f64>();
296                let b_num = b.parse::<f64>();
297                match (a_num, b_num) {
298                    (Ok(a_val), Ok(b_val)) => op(a_val, b_val), // Both are numbers
299                    _ => op(a.len() as f64, b.len() as f64),    // Fallback to length comparison
300                }
301            }
302            (DataValue::InternedString(a), DataValue::String(b)) => {
303                let a_num = a.parse::<f64>();
304                let b_num = b.parse::<f64>();
305                match (a_num, b_num) {
306                    (Ok(a_val), Ok(b_val)) => op(a_val, b_val), // Both are numbers
307                    _ => op(a.len() as f64, b.len() as f64),    // Fallback to length comparison
308                }
309            }
310
311            // Mixed type comparisons (try to convert to numbers)
312            (DataValue::String(a), DataValue::Integer(b)) => {
313                match a.parse::<f64>() {
314                    Ok(a_val) => op(a_val, *b as f64),
315                    Err(_) => false, // String can't be compared with number
316                }
317            }
318            (DataValue::Integer(a), DataValue::String(b)) => {
319                match b.parse::<f64>() {
320                    Ok(b_val) => op(*a as f64, b_val),
321                    Err(_) => false, // String can't be compared with number
322                }
323            }
324            (DataValue::String(a), DataValue::Float(b)) => match a.parse::<f64>() {
325                Ok(a_val) => op(a_val, *b),
326                Err(_) => false,
327            },
328            (DataValue::Float(a), DataValue::String(b)) => match b.parse::<f64>() {
329                Ok(b_val) => op(*a, b_val),
330                Err(_) => false,
331            },
332
333            // NULL comparisons
334            (DataValue::Null, _) | (_, DataValue::Null) => false,
335
336            // Boolean comparisons
337            (DataValue::Boolean(a), DataValue::Boolean(b)) => {
338                op(if *a { 1.0 } else { 0.0 }, if *b { 1.0 } else { 0.0 })
339            }
340
341            _ => {
342                debug!(
343                    "ArithmeticEvaluator: unsupported comparison between {:?} and {:?}",
344                    left, right
345                );
346                false
347            }
348        };
349
350        debug!("ArithmeticEvaluator: comparison result: {}", result);
351        Ok(DataValue::Boolean(result))
352    }
353
354    /// Evaluate a function call
355    fn evaluate_function(
356        &self,
357        name: &str,
358        args: &[SqlExpression],
359        row_index: usize,
360    ) -> Result<DataValue> {
361        // Convert function name to uppercase for case-insensitive matching
362        match name.to_uppercase().as_str() {
363            "ROUND" => {
364                if args.is_empty() || args.len() > 2 {
365                    return Err(anyhow!("ROUND requires 1 or 2 arguments"));
366                }
367
368                // Evaluate the value to round
369                let value = self.evaluate(&args[0], row_index)?;
370
371                // Get decimal places (default to 0 if not specified)
372                let decimals = if args.len() == 2 {
373                    match self.evaluate(&args[1], row_index)? {
374                        DataValue::Integer(n) => n as i32,
375                        DataValue::Float(f) => f as i32,
376                        _ => return Err(anyhow!("ROUND precision must be a number")),
377                    }
378                } else {
379                    0
380                };
381
382                // Perform rounding
383                match value {
384                    DataValue::Integer(n) => Ok(DataValue::Integer(n)), // Already an integer
385                    DataValue::Float(f) => {
386                        if decimals >= 0 {
387                            let multiplier = 10_f64.powi(decimals);
388                            let rounded = (f * multiplier).round() / multiplier;
389                            if decimals == 0 {
390                                // Return as integer if rounding to 0 decimals
391                                Ok(DataValue::Integer(rounded as i64))
392                            } else {
393                                Ok(DataValue::Float(rounded))
394                            }
395                        } else {
396                            // Negative decimals round to left of decimal point
397                            let divisor = 10_f64.powi(-decimals);
398                            let rounded = (f / divisor).round() * divisor;
399                            Ok(DataValue::Float(rounded))
400                        }
401                    }
402                    _ => Err(anyhow!("ROUND can only be applied to numeric values")),
403                }
404            }
405            "ABS" => {
406                if args.len() != 1 {
407                    return Err(anyhow!("ABS requires exactly 1 argument"));
408                }
409
410                let value = self.evaluate(&args[0], row_index)?;
411                match value {
412                    DataValue::Integer(n) => Ok(DataValue::Integer(n.abs())),
413                    DataValue::Float(f) => Ok(DataValue::Float(f.abs())),
414                    _ => Err(anyhow!("ABS can only be applied to numeric values")),
415                }
416            }
417            "FLOOR" => {
418                if args.len() != 1 {
419                    return Err(anyhow!("FLOOR requires exactly 1 argument"));
420                }
421
422                let value = self.evaluate(&args[0], row_index)?;
423                match value {
424                    DataValue::Integer(n) => Ok(DataValue::Integer(n)),
425                    DataValue::Float(f) => Ok(DataValue::Integer(f.floor() as i64)),
426                    _ => Err(anyhow!("FLOOR can only be applied to numeric values")),
427                }
428            }
429            "CEILING" | "CEIL" => {
430                if args.len() != 1 {
431                    return Err(anyhow!("CEILING requires exactly 1 argument"));
432                }
433
434                let value = self.evaluate(&args[0], row_index)?;
435                match value {
436                    DataValue::Integer(n) => Ok(DataValue::Integer(n)),
437                    DataValue::Float(f) => Ok(DataValue::Integer(f.ceil() as i64)),
438                    _ => Err(anyhow!("CEILING can only be applied to numeric values")),
439                }
440            }
441            "MOD" => {
442                if args.len() != 2 {
443                    return Err(anyhow!("MOD requires exactly 2 arguments"));
444                }
445
446                let dividend = self.evaluate(&args[0], row_index)?;
447                let divisor = self.evaluate(&args[1], row_index)?;
448
449                match (&dividend, &divisor) {
450                    (DataValue::Integer(n), DataValue::Integer(d)) => {
451                        if *d == 0 {
452                            return Err(anyhow!("Division by zero in MOD"));
453                        }
454                        Ok(DataValue::Integer(n % d))
455                    }
456                    _ => {
457                        // Convert to float for mixed types
458                        let n = match dividend {
459                            DataValue::Integer(i) => i as f64,
460                            DataValue::Float(f) => f,
461                            _ => return Err(anyhow!("MOD requires numeric arguments")),
462                        };
463                        let d = match divisor {
464                            DataValue::Integer(i) => i as f64,
465                            DataValue::Float(f) => f,
466                            _ => return Err(anyhow!("MOD requires numeric arguments")),
467                        };
468                        if d == 0.0 {
469                            return Err(anyhow!("Division by zero in MOD"));
470                        }
471                        Ok(DataValue::Float(n % d))
472                    }
473                }
474            }
475            "QUOTIENT" => {
476                if args.len() != 2 {
477                    return Err(anyhow!("QUOTIENT requires exactly 2 arguments"));
478                }
479
480                let numerator = self.evaluate(&args[0], row_index)?;
481                let denominator = self.evaluate(&args[1], row_index)?;
482
483                match (&numerator, &denominator) {
484                    (DataValue::Integer(n), DataValue::Integer(d)) => {
485                        if *d == 0 {
486                            return Err(anyhow!("Division by zero in QUOTIENT"));
487                        }
488                        Ok(DataValue::Integer(n / d))
489                    }
490                    _ => {
491                        // Convert to float for mixed types
492                        let n = match numerator {
493                            DataValue::Integer(i) => i as f64,
494                            DataValue::Float(f) => f,
495                            _ => return Err(anyhow!("QUOTIENT requires numeric arguments")),
496                        };
497                        let d = match denominator {
498                            DataValue::Integer(i) => i as f64,
499                            DataValue::Float(f) => f,
500                            _ => return Err(anyhow!("QUOTIENT requires numeric arguments")),
501                        };
502                        if d == 0.0 {
503                            return Err(anyhow!("Division by zero in QUOTIENT"));
504                        }
505                        Ok(DataValue::Integer((n / d).trunc() as i64))
506                    }
507                }
508            }
509            "POWER" | "POW" => {
510                if args.len() != 2 {
511                    return Err(anyhow!("POWER requires exactly 2 arguments"));
512                }
513
514                let base = self.evaluate(&args[0], row_index)?;
515                let exponent = self.evaluate(&args[1], row_index)?;
516
517                match (&base, &exponent) {
518                    (DataValue::Integer(b), DataValue::Integer(e)) => {
519                        if *e >= 0 && *e <= i32::MAX as i64 {
520                            Ok(DataValue::Float((*b as f64).powi(*e as i32)))
521                        } else {
522                            Ok(DataValue::Float((*b as f64).powf(*e as f64)))
523                        }
524                    }
525                    _ => {
526                        // Convert to float for mixed types or floats
527                        let b = match base {
528                            DataValue::Integer(i) => i as f64,
529                            DataValue::Float(f) => f,
530                            _ => return Err(anyhow!("POWER requires numeric arguments")),
531                        };
532                        let e = match exponent {
533                            DataValue::Integer(i) => i as f64,
534                            DataValue::Float(f) => f,
535                            _ => return Err(anyhow!("POWER requires numeric arguments")),
536                        };
537                        Ok(DataValue::Float(b.powf(e)))
538                    }
539                }
540            }
541            "SQRT" => {
542                if args.len() != 1 {
543                    return Err(anyhow!("SQRT requires exactly 1 argument"));
544                }
545
546                let value = self.evaluate(&args[0], row_index)?;
547                match value {
548                    DataValue::Integer(n) => {
549                        if n < 0 {
550                            return Err(anyhow!("SQRT of negative number"));
551                        }
552                        Ok(DataValue::Float((n as f64).sqrt()))
553                    }
554                    DataValue::Float(f) => {
555                        if f < 0.0 {
556                            return Err(anyhow!("SQRT of negative number"));
557                        }
558                        Ok(DataValue::Float(f.sqrt()))
559                    }
560                    _ => Err(anyhow!("SQRT can only be applied to numeric values")),
561                }
562            }
563            "EXP" => {
564                if args.len() != 1 {
565                    return Err(anyhow!("EXP requires exactly 1 argument"));
566                }
567
568                let value = self.evaluate(&args[0], row_index)?;
569                match value {
570                    DataValue::Integer(n) => Ok(DataValue::Float((n as f64).exp())),
571                    DataValue::Float(f) => Ok(DataValue::Float(f.exp())),
572                    _ => Err(anyhow!("EXP can only be applied to numeric values")),
573                }
574            }
575            "LN" => {
576                if args.len() != 1 {
577                    return Err(anyhow!("LN requires exactly 1 argument"));
578                }
579
580                let value = self.evaluate(&args[0], row_index)?;
581                match value {
582                    DataValue::Integer(n) => {
583                        if n <= 0 {
584                            return Err(anyhow!("LN of non-positive number"));
585                        }
586                        Ok(DataValue::Float((n as f64).ln()))
587                    }
588                    DataValue::Float(f) => {
589                        if f <= 0.0 {
590                            return Err(anyhow!("LN of non-positive number"));
591                        }
592                        Ok(DataValue::Float(f.ln()))
593                    }
594                    _ => Err(anyhow!("LN can only be applied to numeric values")),
595                }
596            }
597            "LOG" | "LOG10" => {
598                if name == "LOG" && args.len() == 2 {
599                    // LOG with custom base
600                    let value = self.evaluate(&args[0], row_index)?;
601                    let base = self.evaluate(&args[1], row_index)?;
602
603                    let n = match value {
604                        DataValue::Integer(i) => i as f64,
605                        DataValue::Float(f) => f,
606                        _ => return Err(anyhow!("LOG requires numeric arguments")),
607                    };
608                    let b = match base {
609                        DataValue::Integer(i) => i as f64,
610                        DataValue::Float(f) => f,
611                        _ => return Err(anyhow!("LOG requires numeric arguments")),
612                    };
613
614                    if n <= 0.0 {
615                        return Err(anyhow!("LOG of non-positive number"));
616                    }
617                    if b <= 0.0 || b == 1.0 {
618                        return Err(anyhow!("Invalid LOG base"));
619                    }
620                    Ok(DataValue::Float(n.log(b)))
621                } else if (name == "LOG" && args.len() == 1) || name == "LOG10" {
622                    // LOG10 or LOG with default base 10
623                    if args.len() != 1 {
624                        return Err(anyhow!("{} requires exactly 1 argument", name));
625                    }
626
627                    let value = self.evaluate(&args[0], row_index)?;
628                    match value {
629                        DataValue::Integer(n) => {
630                            if n <= 0 {
631                                return Err(anyhow!("LOG10 of non-positive number"));
632                            }
633                            Ok(DataValue::Float((n as f64).log10()))
634                        }
635                        DataValue::Float(f) => {
636                            if f <= 0.0 {
637                                return Err(anyhow!("LOG10 of non-positive number"));
638                            }
639                            Ok(DataValue::Float(f.log10()))
640                        }
641                        _ => Err(anyhow!("LOG10 can only be applied to numeric values")),
642                    }
643                } else {
644                    Err(anyhow!("LOG requires 1 or 2 arguments"))
645                }
646            }
647            "PI" => {
648                if !args.is_empty() {
649                    return Err(anyhow!("PI takes no arguments"));
650                }
651                Ok(DataValue::Float(std::f64::consts::PI))
652            }
653            "DATEDIFF" => {
654                if args.len() != 3 {
655                    return Err(anyhow!(
656                        "DATEDIFF requires exactly 3 arguments: unit, date1, date2"
657                    ));
658                }
659
660                // First argument: unit (day, month, year, hour, minute, second)
661                let unit = match self.evaluate(&args[0], row_index)? {
662                    DataValue::String(s) => s.to_lowercase(),
663                    DataValue::InternedString(s) => s.to_lowercase(),
664                    _ => return Err(anyhow!("DATEDIFF unit must be a string")),
665                };
666
667                // Helper function to parse date/datetime strings
668                let parse_datetime = |value: DataValue| -> Result<DateTime<Utc>> {
669                    let parse_string = |s: &str| -> Result<DateTime<Utc>> {
670                        // Try various date/datetime formats
671
672                        // ISO formats (most common)
673                        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S") {
674                            return Ok(Utc.from_utc_datetime(&dt));
675                        }
676                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%Y-%m-%d") {
677                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
678                        }
679
680                        // US format: MM/DD/YYYY or MM-DD-YYYY
681                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m/%d/%Y") {
682                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
683                        }
684                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m-%d-%Y") {
685                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
686                        }
687
688                        // European format: DD/MM/YYYY or DD-MM-YYYY
689                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d/%m/%Y") {
690                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
691                        }
692                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%m-%Y") {
693                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
694                        }
695
696                        // Excel/Windows format: DD-MMM-YYYY (e.g., 15-Jan-2024)
697                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%b-%Y") {
698                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
699                        }
700
701                        // Full month names: January 15, 2024 or 15 January 2024
702                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%B %d, %Y") {
703                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
704                        }
705                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d %B %Y") {
706                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
707                        }
708
709                        // With time: MM/DD/YYYY HH:MM:SS
710                        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S") {
711                            return Ok(Utc.from_utc_datetime(&dt));
712                        }
713                        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S") {
714                            return Ok(Utc.from_utc_datetime(&dt));
715                        }
716
717                        // ISO 8601 / RFC3339
718                        if let Ok(dt) = s.parse::<DateTime<Utc>>() {
719                            return Ok(dt);
720                        }
721
722                        Err(anyhow!("Could not parse date: {}. Supported formats: YYYY-MM-DD, MM/DD/YYYY, DD/MM/YYYY, DD-MMM-YYYY", s))
723                    };
724
725                    match value {
726                        DataValue::String(s) | DataValue::DateTime(s) => parse_string(&s),
727                        DataValue::InternedString(s) => parse_string(s.as_str()),
728                        _ => Err(anyhow!("DATEDIFF requires date/datetime values")),
729                    }
730                };
731
732                // Parse both dates
733                let date1 = parse_datetime(self.evaluate(&args[1], row_index)?)?;
734                let date2 = parse_datetime(self.evaluate(&args[2], row_index)?)?;
735
736                // Calculate difference based on unit
737                let diff = match unit.as_str() {
738                    "day" | "days" => {
739                        let duration = date2.signed_duration_since(date1);
740                        duration.num_days()
741                    }
742                    "month" | "months" => {
743                        // Approximate months as 30.44 days
744                        let duration = date2.signed_duration_since(date1);
745                        duration.num_days() / 30
746                    }
747                    "year" | "years" => {
748                        // Approximate years as 365.25 days
749                        let duration = date2.signed_duration_since(date1);
750                        duration.num_days() / 365
751                    }
752                    "hour" | "hours" => {
753                        let duration = date2.signed_duration_since(date1);
754                        duration.num_hours()
755                    }
756                    "minute" | "minutes" => {
757                        let duration = date2.signed_duration_since(date1);
758                        duration.num_minutes()
759                    }
760                    "second" | "seconds" => {
761                        let duration = date2.signed_duration_since(date1);
762                        duration.num_seconds()
763                    }
764                    _ => {
765                        return Err(anyhow!(
766                        "Unknown DATEDIFF unit: {}. Use: day, month, year, hour, minute, second",
767                        unit
768                    ))
769                    }
770                };
771
772                Ok(DataValue::Integer(diff))
773            }
774            "NOW" => {
775                if !args.is_empty() {
776                    return Err(anyhow!("NOW takes no arguments"));
777                }
778                let now = Utc::now();
779                Ok(DataValue::DateTime(
780                    now.format("%Y-%m-%d %H:%M:%S").to_string(),
781                ))
782            }
783            "TODAY" => {
784                if !args.is_empty() {
785                    return Err(anyhow!("TODAY takes no arguments"));
786                }
787                let today = Utc::now().date_naive();
788                Ok(DataValue::String(today.format("%Y-%m-%d").to_string()))
789            }
790            "DATEADD" => {
791                if args.len() != 3 {
792                    return Err(anyhow!(
793                        "DATEADD requires exactly 3 arguments: unit, number, date"
794                    ));
795                }
796
797                // First argument: unit (day, month, year, hour, minute, second)
798                let unit = match self.evaluate(&args[0], row_index)? {
799                    DataValue::String(s) => s.to_lowercase(),
800                    DataValue::InternedString(s) => s.to_lowercase(),
801                    _ => return Err(anyhow!("DATEADD unit must be a string")),
802                };
803
804                // Second argument: number to add (can be negative for subtraction)
805                let amount = match self.evaluate(&args[1], row_index)? {
806                    DataValue::Integer(i) => i,
807                    DataValue::Float(f) => f as i64,
808                    _ => return Err(anyhow!("DATEADD amount must be a number")),
809                };
810
811                // Third argument: base date
812                let base_date_value = self.evaluate(&args[2], row_index)?;
813
814                // Reuse the parse_datetime function from DATEDIFF
815                let parse_datetime = |value: DataValue| -> Result<DateTime<Utc>> {
816                    let parse_string = |s: &str| -> Result<DateTime<Utc>> {
817                        // Try various date/datetime formats (same as DATEDIFF)
818
819                        // ISO formats (most common)
820                        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S") {
821                            return Ok(Utc.from_utc_datetime(&dt));
822                        }
823                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%Y-%m-%d") {
824                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
825                        }
826
827                        // US format: MM/DD/YYYY or MM-DD-YYYY
828                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m/%d/%Y") {
829                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
830                        }
831                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m-%d-%Y") {
832                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
833                        }
834
835                        // European format: DD/MM/YYYY or DD-MM-YYYY
836                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d/%m/%Y") {
837                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
838                        }
839                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%m-%Y") {
840                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
841                        }
842
843                        // Excel/Windows format: DD-MMM-YYYY (e.g., 15-Jan-2024)
844                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%b-%Y") {
845                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
846                        }
847
848                        // Full month names: January 15, 2024 or 15 January 2024
849                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%B %d, %Y") {
850                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
851                        }
852                        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d %B %Y") {
853                            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
854                        }
855
856                        // With time: MM/DD/YYYY HH:MM:SS
857                        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S") {
858                            return Ok(Utc.from_utc_datetime(&dt));
859                        }
860                        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S") {
861                            return Ok(Utc.from_utc_datetime(&dt));
862                        }
863
864                        // ISO 8601 / RFC3339
865                        if let Ok(dt) = s.parse::<DateTime<Utc>>() {
866                            return Ok(dt);
867                        }
868
869                        Err(anyhow!("Could not parse date: {}. Supported formats: YYYY-MM-DD, MM/DD/YYYY, DD/MM/YYYY, DD-MMM-YYYY", s))
870                    };
871
872                    match value {
873                        DataValue::String(s) | DataValue::DateTime(s) => parse_string(&s),
874                        DataValue::InternedString(s) => parse_string(s.as_str()),
875                        _ => Err(anyhow!("DATEADD requires date/datetime values")),
876                    }
877                };
878
879                // Parse the base date
880                let base_date = parse_datetime(base_date_value)?;
881
882                // Add the specified amount based on unit
883                let result_date = match unit.as_str() {
884                    "day" | "days" => base_date + chrono::Duration::days(amount),
885                    "month" | "months" => {
886                        // For months, we need to be careful about month boundaries
887                        let mut year = base_date.year();
888                        let mut month = base_date.month() as i32;
889                        let day = base_date.day();
890
891                        month += amount as i32;
892
893                        // Handle month overflow/underflow
894                        while month > 12 {
895                            month -= 12;
896                            year += 1;
897                        }
898                        while month < 1 {
899                            month += 12;
900                            year -= 1;
901                        }
902
903                        // Create new date, handling day overflow (e.g., Jan 31 + 1 month = Feb 28/29)
904                        let target_date = NaiveDate::from_ymd_opt(year, month as u32, day)
905                            .unwrap_or_else(|| {
906                                // If day doesn't exist in target month, use the last day of that month
907                                // Try decreasing days until we find a valid one
908                                for test_day in (1..=day).rev() {
909                                    if let Some(date) =
910                                        NaiveDate::from_ymd_opt(year, month as u32, test_day)
911                                    {
912                                        return date;
913                                    }
914                                }
915                                // This should never happen, but fallback to day 28 as safety
916                                NaiveDate::from_ymd_opt(year, month as u32, 28).unwrap()
917                            });
918
919                        Utc.from_utc_datetime(&target_date.and_time(base_date.time()))
920                    }
921                    "year" | "years" => {
922                        let new_year = base_date.year() + amount as i32;
923                        let target_date =
924                            NaiveDate::from_ymd_opt(new_year, base_date.month(), base_date.day())
925                                .unwrap_or_else(|| {
926                                    // Handle Feb 29 in non-leap years
927                                    NaiveDate::from_ymd_opt(new_year, base_date.month(), 28)
928                                        .unwrap()
929                                });
930                        Utc.from_utc_datetime(&target_date.and_time(base_date.time()))
931                    }
932                    "hour" | "hours" => base_date + chrono::Duration::hours(amount),
933                    "minute" | "minutes" => base_date + chrono::Duration::minutes(amount),
934                    "second" | "seconds" => base_date + chrono::Duration::seconds(amount),
935                    _ => {
936                        return Err(anyhow!(
937                            "Unknown DATEADD unit: {}. Use: day, month, year, hour, minute, second",
938                            unit
939                        ))
940                    }
941                };
942
943                // Return as datetime string
944                Ok(DataValue::DateTime(
945                    result_date.format("%Y-%m-%d %H:%M:%S").to_string(),
946                ))
947            }
948            "TEXTJOIN" => {
949                if args.len() < 3 {
950                    return Err(anyhow!("TEXTJOIN requires at least 3 arguments: delimiter, ignore_empty, text1, [text2, ...]"));
951                }
952
953                // First argument: delimiter
954                let delimiter = match self.evaluate(&args[0], row_index)? {
955                    DataValue::String(s) => s,
956                    DataValue::InternedString(s) => s.to_string(),
957                    DataValue::Integer(n) => n.to_string(),
958                    DataValue::Float(f) => f.to_string(),
959                    DataValue::Boolean(b) => b.to_string(),
960                    DataValue::Null => String::new(),
961                    _ => String::new(),
962                };
963
964                // Second argument: ignore_empty (treat as boolean - 0 is false, anything else is true)
965                let ignore_empty = match self.evaluate(&args[1], row_index)? {
966                    DataValue::Integer(n) => n != 0,
967                    DataValue::Float(f) => f != 0.0,
968                    DataValue::Boolean(b) => b,
969                    DataValue::String(s) => {
970                        !s.is_empty() && s != "0" && s.to_lowercase() != "false"
971                    }
972                    DataValue::InternedString(s) => {
973                        !s.is_empty() && s.as_str() != "0" && s.to_lowercase() != "false"
974                    }
975                    DataValue::Null => false,
976                    _ => true,
977                };
978
979                // Remaining arguments: values to join
980                let mut values = Vec::new();
981                for i in 2..args.len() {
982                    let value = self.evaluate(&args[i], row_index)?;
983                    let string_value = match value {
984                        DataValue::String(s) => Some(s),
985                        DataValue::InternedString(s) => Some(s.to_string()),
986                        DataValue::Integer(n) => Some(n.to_string()),
987                        DataValue::Float(f) => Some(f.to_string()),
988                        DataValue::Boolean(b) => Some(b.to_string()),
989                        DataValue::DateTime(dt) => Some(dt),
990                        DataValue::Null => {
991                            if ignore_empty {
992                                None
993                            } else {
994                                Some(String::new())
995                            }
996                        }
997                        _ => {
998                            if ignore_empty {
999                                None
1000                            } else {
1001                                Some(String::new())
1002                            }
1003                        }
1004                    };
1005
1006                    if let Some(s) = string_value {
1007                        if !ignore_empty || !s.is_empty() {
1008                            values.push(s);
1009                        }
1010                    }
1011                }
1012
1013                Ok(DataValue::String(values.join(&delimiter)))
1014            }
1015            "PI" => {
1016                // PI constant - no arguments
1017                if !args.is_empty() {
1018                    return Err(anyhow!("PI() takes no arguments"));
1019                }
1020                Ok(DataValue::Float(std::f64::consts::PI))
1021            }
1022            "EULER" => {
1023                // Euler's number constant - no arguments
1024                if !args.is_empty() {
1025                    return Err(anyhow!("EULER() takes no arguments"));
1026                }
1027                Ok(DataValue::Float(std::f64::consts::E))
1028            }
1029            "TAU" => {
1030                // Tau constant (2*PI) - no arguments
1031                if !args.is_empty() {
1032                    return Err(anyhow!("TAU() takes no arguments"));
1033                }
1034                Ok(DataValue::Float(std::f64::consts::TAU))
1035            }
1036            "PHI" => {
1037                // Golden ratio constant - no arguments
1038                if !args.is_empty() {
1039                    return Err(anyhow!("PHI() takes no arguments"));
1040                }
1041                Ok(DataValue::Float(1.618033988749895))
1042            }
1043            "SQRT2" => {
1044                // Square root of 2 constant - no arguments
1045                if !args.is_empty() {
1046                    return Err(anyhow!("SQRT2() takes no arguments"));
1047                }
1048                Ok(DataValue::Float(std::f64::consts::SQRT_2))
1049            }
1050            "LN2" => {
1051                // Natural logarithm of 2 - no arguments
1052                if !args.is_empty() {
1053                    return Err(anyhow!("LN2() takes no arguments"));
1054                }
1055                Ok(DataValue::Float(std::f64::consts::LN_2))
1056            }
1057            "LN10" => {
1058                // Natural logarithm of 10 - no arguments
1059                if !args.is_empty() {
1060                    return Err(anyhow!("LN10() takes no arguments"));
1061                }
1062                Ok(DataValue::Float(std::f64::consts::LN_10))
1063            }
1064            // Physics Constants - Fundamental Constants
1065            "C" | "SPEED_OF_LIGHT" => {
1066                // Speed of light in vacuum (m/s)
1067                if !args.is_empty() {
1068                    return Err(anyhow!("C() takes no arguments"));
1069                }
1070                Ok(DataValue::Float(299792458.0))
1071            }
1072            "G" | "GRAVITATIONAL_CONSTANT" => {
1073                // Gravitational constant (m^3 kg^-1 s^-2)
1074                if !args.is_empty() {
1075                    return Err(anyhow!("G() takes no arguments"));
1076                }
1077                Ok(DataValue::Float(6.67430e-11))
1078            }
1079            "H" | "PLANCK" => {
1080                // Planck constant (J⋅s)
1081                if !args.is_empty() {
1082                    return Err(anyhow!("PLANCK() takes no arguments"));
1083                }
1084                Ok(DataValue::Float(6.62607015e-34))
1085            }
1086            "HBAR" => {
1087                // Reduced Planck constant ℏ = h/(2π) (J⋅s)
1088                if !args.is_empty() {
1089                    return Err(anyhow!("HBAR() takes no arguments"));
1090                }
1091                Ok(DataValue::Float(1.054571817e-34))
1092            }
1093            "K" | "BOLTZMANN" => {
1094                // Boltzmann constant (J/K)
1095                if !args.is_empty() {
1096                    return Err(anyhow!("BOLTZMANN() takes no arguments"));
1097                }
1098                Ok(DataValue::Float(1.380649e-23))
1099            }
1100            "NA" | "AVOGADRO" => {
1101                // Avogadro's number (mol^-1)
1102                if !args.is_empty() {
1103                    return Err(anyhow!("AVOGADRO() takes no arguments"));
1104                }
1105                Ok(DataValue::Float(6.02214076e23))
1106            }
1107            "R" | "GAS_CONSTANT" => {
1108                // Universal gas constant (J mol^-1 K^-1)
1109                if !args.is_empty() {
1110                    return Err(anyhow!("R() takes no arguments"));
1111                }
1112                Ok(DataValue::Float(8.314462618))
1113            }
1114            // Physics Constants - Electromagnetic
1115            "E0" | "EPSILON0" | "PERMITTIVITY" => {
1116                // Electric permittivity of vacuum (F/m)
1117                if !args.is_empty() {
1118                    return Err(anyhow!("E0() takes no arguments"));
1119                }
1120                Ok(DataValue::Float(8.8541878128e-12))
1121            }
1122            "MU0" | "PERMEABILITY" => {
1123                // Magnetic permeability of vacuum (N/A^2)
1124                if !args.is_empty() {
1125                    return Err(anyhow!("MU0() takes no arguments"));
1126                }
1127                Ok(DataValue::Float(1.25663706212e-6))
1128            }
1129            "QE" | "ELEMENTARY_CHARGE" => {
1130                // Elementary charge (C)
1131                if !args.is_empty() {
1132                    return Err(anyhow!("QE() takes no arguments"));
1133                }
1134                Ok(DataValue::Float(1.602176634e-19))
1135            }
1136            // Physics Constants - Particle Masses
1137            "ME" | "MASS_ELECTRON" => {
1138                // Electron mass (kg)
1139                if !args.is_empty() {
1140                    return Err(anyhow!("ME() takes no arguments"));
1141                }
1142                Ok(DataValue::Float(9.1093837015e-31))
1143            }
1144            "MP" | "MASS_PROTON" => {
1145                // Proton mass (kg)
1146                if !args.is_empty() {
1147                    return Err(anyhow!("MP() takes no arguments"));
1148                }
1149                Ok(DataValue::Float(1.67262192369e-27))
1150            }
1151            "MN" | "MASS_NEUTRON" => {
1152                // Neutron mass (kg)
1153                if !args.is_empty() {
1154                    return Err(anyhow!("MN() takes no arguments"));
1155                }
1156                Ok(DataValue::Float(1.67492749804e-27))
1157            }
1158            "AMU" | "ATOMIC_MASS_UNIT" => {
1159                // Atomic mass unit (kg)
1160                if !args.is_empty() {
1161                    return Err(anyhow!("AMU() takes no arguments"));
1162                }
1163                Ok(DataValue::Float(1.66053906660e-27))
1164            }
1165            // Physics Constants - Other
1166            "ALPHA" | "FINE_STRUCTURE" => {
1167                // Fine structure constant (dimensionless)
1168                if !args.is_empty() {
1169                    return Err(anyhow!("ALPHA() takes no arguments"));
1170                }
1171                Ok(DataValue::Float(7.2973525693e-3))
1172            }
1173            "RY" | "RYDBERG" => {
1174                // Rydberg constant (m^-1)
1175                if !args.is_empty() {
1176                    return Err(anyhow!("RYDBERG() takes no arguments"));
1177                }
1178                Ok(DataValue::Float(10973731.568160))
1179            }
1180            "SIGMA" | "STEFAN_BOLTZMANN" => {
1181                // Stefan-Boltzmann constant (W m^-2 K^-4)
1182                if !args.is_empty() {
1183                    return Err(anyhow!("SIGMA() takes no arguments"));
1184                }
1185                Ok(DataValue::Float(5.670374419e-8))
1186            }
1187            // Angle conversion functions (demonstrating easy extensibility)
1188            "DEGREES" => {
1189                // Convert radians to degrees
1190                if args.len() != 1 {
1191                    return Err(anyhow!("DEGREES requires exactly 1 argument"));
1192                }
1193                let radians = match self.evaluate(&args[0], row_index)? {
1194                    DataValue::Integer(n) => n as f64,
1195                    DataValue::Float(f) => f,
1196                    _ => return Err(anyhow!("DEGREES requires a numeric argument")),
1197                };
1198                Ok(DataValue::Float(radians * 180.0 / std::f64::consts::PI))
1199            }
1200            "RADIANS" => {
1201                // Convert degrees to radians
1202                if args.len() != 1 {
1203                    return Err(anyhow!("RADIANS requires exactly 1 argument"));
1204                }
1205                let degrees = match self.evaluate(&args[0], row_index)? {
1206                    DataValue::Integer(n) => n as f64,
1207                    DataValue::Float(f) => f,
1208                    _ => return Err(anyhow!("RADIANS requires a numeric argument")),
1209                };
1210                Ok(DataValue::Float(degrees * std::f64::consts::PI / 180.0))
1211            }
1212            "MID" => {
1213                // MID(text, start_position, length) - Excel-compatible, 1-based indexing
1214                if args.len() != 3 {
1215                    return Err(anyhow!(
1216                        "MID requires exactly 3 arguments: text, start_position, length"
1217                    ));
1218                }
1219
1220                let text = match self.evaluate(&args[0], row_index)? {
1221                    DataValue::String(s) => s,
1222                    DataValue::InternedString(s) => s.to_string(),
1223                    DataValue::Integer(n) => n.to_string(),
1224                    DataValue::Float(f) => f.to_string(),
1225                    DataValue::Null => String::new(),
1226                    _ => return Err(anyhow!("MID first argument must be convertible to text")),
1227                };
1228
1229                let start_pos = match self.evaluate(&args[1], row_index)? {
1230                    DataValue::Integer(n) => n,
1231                    DataValue::Float(f) => f as i64,
1232                    _ => return Err(anyhow!("MID start_position must be a number")),
1233                };
1234
1235                let length = match self.evaluate(&args[2], row_index)? {
1236                    DataValue::Integer(n) => n,
1237                    DataValue::Float(f) => f as i64,
1238                    _ => return Err(anyhow!("MID length must be a number")),
1239                };
1240
1241                // Excel MID uses 1-based indexing
1242                if start_pos < 1 {
1243                    return Err(anyhow!("MID start_position must be >= 1"));
1244                }
1245                if length < 0 {
1246                    return Err(anyhow!("MID length must be >= 0"));
1247                }
1248
1249                // Convert to 0-based index
1250                let start_idx = (start_pos - 1) as usize;
1251                let chars: Vec<char> = text.chars().collect();
1252
1253                // If start position is beyond string length, return empty string
1254                if start_idx >= chars.len() {
1255                    return Ok(DataValue::String(String::new()));
1256                }
1257
1258                // Extract substring
1259                let end_idx = std::cmp::min(start_idx + length as usize, chars.len());
1260                let result: String = chars[start_idx..end_idx].iter().collect();
1261
1262                Ok(DataValue::String(result))
1263            }
1264            "UPPER" => {
1265                if args.len() != 1 {
1266                    return Err(anyhow!("UPPER requires exactly 1 argument"));
1267                }
1268
1269                let text = match self.evaluate(&args[0], row_index)? {
1270                    DataValue::String(s) => s,
1271                    DataValue::InternedString(s) => s.to_string(),
1272                    DataValue::Integer(n) => n.to_string(),
1273                    DataValue::Float(f) => f.to_string(),
1274                    DataValue::Null => String::new(),
1275                    _ => return Err(anyhow!("UPPER argument must be convertible to text")),
1276                };
1277
1278                Ok(DataValue::String(text.to_uppercase()))
1279            }
1280            "LOWER" => {
1281                if args.len() != 1 {
1282                    return Err(anyhow!("LOWER requires exactly 1 argument"));
1283                }
1284
1285                let text = match self.evaluate(&args[0], row_index)? {
1286                    DataValue::String(s) => s,
1287                    DataValue::InternedString(s) => s.to_string(),
1288                    DataValue::Integer(n) => n.to_string(),
1289                    DataValue::Float(f) => f.to_string(),
1290                    DataValue::Null => String::new(),
1291                    _ => return Err(anyhow!("LOWER argument must be convertible to text")),
1292                };
1293
1294                Ok(DataValue::String(text.to_lowercase()))
1295            }
1296            "TRIM" => {
1297                if args.len() != 1 {
1298                    return Err(anyhow!("TRIM requires exactly 1 argument"));
1299                }
1300
1301                let text = match self.evaluate(&args[0], row_index)? {
1302                    DataValue::String(s) => s,
1303                    DataValue::InternedString(s) => s.to_string(),
1304                    DataValue::Integer(n) => n.to_string(),
1305                    DataValue::Float(f) => f.to_string(),
1306                    DataValue::Null => String::new(),
1307                    _ => return Err(anyhow!("TRIM argument must be convertible to text")),
1308                };
1309
1310                Ok(DataValue::String(text.trim().to_string()))
1311            }
1312            _ => Err(anyhow!("Unknown function: {}", name)),
1313        }
1314    }
1315
1316    /// Evaluate a method call on a column (e.g., column.Trim())
1317    fn evaluate_method_call(
1318        &self,
1319        object: &str,
1320        method: &str,
1321        args: &[SqlExpression],
1322        row_index: usize,
1323    ) -> Result<DataValue> {
1324        // Get column value
1325        let col_index = self.table.get_column_index(object).ok_or_else(|| {
1326            let suggestion = self.find_similar_column(object);
1327            match suggestion {
1328                Some(similar) => {
1329                    anyhow!("Column '{}' not found. Did you mean '{}'?", object, similar)
1330                }
1331                None => anyhow!("Column '{}' not found", object),
1332            }
1333        })?;
1334
1335        let cell_value = self.table.get_value(row_index, col_index).cloned();
1336
1337        self.evaluate_method_on_value(
1338            &cell_value.unwrap_or(DataValue::Null),
1339            method,
1340            args,
1341            row_index,
1342        )
1343    }
1344
1345    /// Evaluate a method on a value
1346    fn evaluate_method_on_value(
1347        &self,
1348        value: &DataValue,
1349        method: &str,
1350        args: &[SqlExpression],
1351        row_index: usize,
1352    ) -> Result<DataValue> {
1353        match method.to_lowercase().as_str() {
1354            "trim" | "trimstart" | "trimend" => {
1355                if !args.is_empty() {
1356                    return Err(anyhow!("{} takes no arguments", method));
1357                }
1358
1359                // Convert value to string and apply trim
1360                let str_val = match value {
1361                    DataValue::String(s) => s.clone(),
1362                    DataValue::InternedString(s) => s.to_string(),
1363                    DataValue::Integer(n) => n.to_string(),
1364                    DataValue::Float(f) => f.to_string(),
1365                    DataValue::Boolean(b) => b.to_string(),
1366                    DataValue::DateTime(dt) => dt.clone(),
1367                    DataValue::Null => return Ok(DataValue::Null),
1368                };
1369
1370                let result = match method.to_lowercase().as_str() {
1371                    "trim" => str_val.trim().to_string(),
1372                    "trimstart" => str_val.trim_start().to_string(),
1373                    "trimend" => str_val.trim_end().to_string(),
1374                    _ => unreachable!(),
1375                };
1376
1377                Ok(DataValue::String(result))
1378            }
1379            "length" => {
1380                if !args.is_empty() {
1381                    return Err(anyhow!("Length takes no arguments"));
1382                }
1383
1384                // Get string length
1385                let len = match value {
1386                    DataValue::String(s) => s.len(),
1387                    DataValue::InternedString(s) => s.len(),
1388                    DataValue::Integer(n) => n.to_string().len(),
1389                    DataValue::Float(f) => f.to_string().len(),
1390                    DataValue::Boolean(b) => b.to_string().len(),
1391                    DataValue::DateTime(dt) => dt.len(),
1392                    DataValue::Null => return Ok(DataValue::Integer(0)),
1393                };
1394
1395                Ok(DataValue::Integer(len as i64))
1396            }
1397            "indexof" => {
1398                if args.len() != 1 {
1399                    return Err(anyhow!("IndexOf requires exactly 1 argument"));
1400                }
1401
1402                // Get the search string from args
1403                let search_str = match self.evaluate(&args[0], row_index)? {
1404                    DataValue::String(s) => s,
1405                    DataValue::InternedString(s) => s.to_string(),
1406                    DataValue::Integer(n) => n.to_string(),
1407                    DataValue::Float(f) => f.to_string(),
1408                    _ => return Err(anyhow!("IndexOf argument must be a string")),
1409                };
1410
1411                // Convert value to string and find index
1412                let str_val = match value {
1413                    DataValue::String(s) => s.clone(),
1414                    DataValue::InternedString(s) => s.to_string(),
1415                    DataValue::Integer(n) => n.to_string(),
1416                    DataValue::Float(f) => f.to_string(),
1417                    DataValue::Boolean(b) => b.to_string(),
1418                    DataValue::DateTime(dt) => dt.clone(),
1419                    DataValue::Null => return Ok(DataValue::Integer(-1)),
1420                };
1421
1422                let index = str_val.find(&search_str).map(|i| i as i64).unwrap_or(-1);
1423
1424                Ok(DataValue::Integer(index))
1425            }
1426            "contains" => {
1427                if args.len() != 1 {
1428                    return Err(anyhow!("Contains requires exactly 1 argument"));
1429                }
1430
1431                // Get the search string from args
1432                let search_str = match self.evaluate(&args[0], row_index)? {
1433                    DataValue::String(s) => s,
1434                    DataValue::InternedString(s) => s.to_string(),
1435                    DataValue::Integer(n) => n.to_string(),
1436                    DataValue::Float(f) => f.to_string(),
1437                    _ => return Err(anyhow!("Contains argument must be a string")),
1438                };
1439
1440                // Convert value to string and check contains
1441                let str_val = match value {
1442                    DataValue::String(s) => s.clone(),
1443                    DataValue::InternedString(s) => s.to_string(),
1444                    DataValue::Integer(n) => n.to_string(),
1445                    DataValue::Float(f) => f.to_string(),
1446                    DataValue::Boolean(b) => b.to_string(),
1447                    DataValue::DateTime(dt) => dt.clone(),
1448                    DataValue::Null => return Ok(DataValue::Boolean(false)),
1449                };
1450
1451                // Case-insensitive search
1452                let result = str_val.to_lowercase().contains(&search_str.to_lowercase());
1453                Ok(DataValue::Boolean(result))
1454            }
1455            "startswith" => {
1456                if args.len() != 1 {
1457                    return Err(anyhow!("StartsWith requires exactly 1 argument"));
1458                }
1459
1460                // Get the prefix from args
1461                let prefix = match self.evaluate(&args[0], row_index)? {
1462                    DataValue::String(s) => s,
1463                    DataValue::InternedString(s) => s.to_string(),
1464                    DataValue::Integer(n) => n.to_string(),
1465                    DataValue::Float(f) => f.to_string(),
1466                    _ => return Err(anyhow!("StartsWith argument must be a string")),
1467                };
1468
1469                // Convert value to string and check starts_with
1470                let str_val = match value {
1471                    DataValue::String(s) => s.clone(),
1472                    DataValue::InternedString(s) => s.to_string(),
1473                    DataValue::Integer(n) => n.to_string(),
1474                    DataValue::Float(f) => f.to_string(),
1475                    DataValue::Boolean(b) => b.to_string(),
1476                    DataValue::DateTime(dt) => dt.clone(),
1477                    DataValue::Null => return Ok(DataValue::Boolean(false)),
1478                };
1479
1480                // Case-insensitive check
1481                let result = str_val.to_lowercase().starts_with(&prefix.to_lowercase());
1482                Ok(DataValue::Boolean(result))
1483            }
1484            "endswith" => {
1485                if args.len() != 1 {
1486                    return Err(anyhow!("EndsWith requires exactly 1 argument"));
1487                }
1488
1489                // Get the suffix from args
1490                let suffix = match self.evaluate(&args[0], row_index)? {
1491                    DataValue::String(s) => s,
1492                    DataValue::InternedString(s) => s.to_string(),
1493                    DataValue::Integer(n) => n.to_string(),
1494                    DataValue::Float(f) => f.to_string(),
1495                    _ => return Err(anyhow!("EndsWith argument must be a string")),
1496                };
1497
1498                // Convert value to string and check ends_with
1499                let str_val = match value {
1500                    DataValue::String(s) => s.clone(),
1501                    DataValue::InternedString(s) => s.to_string(),
1502                    DataValue::Integer(n) => n.to_string(),
1503                    DataValue::Float(f) => f.to_string(),
1504                    DataValue::Boolean(b) => b.to_string(),
1505                    DataValue::DateTime(dt) => dt.clone(),
1506                    DataValue::Null => return Ok(DataValue::Boolean(false)),
1507                };
1508
1509                // Case-insensitive check
1510                let result = str_val.to_lowercase().ends_with(&suffix.to_lowercase());
1511                Ok(DataValue::Boolean(result))
1512            }
1513            _ => Err(anyhow!("Unsupported method: {}", method)),
1514        }
1515    }
1516
1517    /// Evaluate a CASE expression
1518    fn evaluate_case_expression(
1519        &self,
1520        when_branches: &[crate::sql::recursive_parser::WhenBranch],
1521        else_branch: &Option<Box<SqlExpression>>,
1522        row_index: usize,
1523    ) -> Result<DataValue> {
1524        debug!(
1525            "ArithmeticEvaluator: evaluating CASE expression for row {}",
1526            row_index
1527        );
1528
1529        // Evaluate each WHEN condition in order
1530        for branch in when_branches {
1531            // Evaluate the condition as a boolean
1532            let condition_result = self.evaluate_condition_as_bool(&branch.condition, row_index)?;
1533
1534            if condition_result {
1535                debug!("CASE: WHEN condition matched, evaluating result expression");
1536                return self.evaluate(&branch.result, row_index);
1537            }
1538        }
1539
1540        // If no WHEN condition matched, evaluate ELSE clause (or return NULL)
1541        match else_branch {
1542            Some(else_expr) => {
1543                debug!("CASE: No WHEN matched, evaluating ELSE expression");
1544                self.evaluate(else_expr, row_index)
1545            }
1546            None => {
1547                debug!("CASE: No WHEN matched and no ELSE, returning NULL");
1548                Ok(DataValue::Null)
1549            }
1550        }
1551    }
1552
1553    /// Helper method to evaluate an expression as a boolean (for CASE WHEN conditions)
1554    fn evaluate_condition_as_bool(&self, expr: &SqlExpression, row_index: usize) -> Result<bool> {
1555        let value = self.evaluate(expr, row_index)?;
1556
1557        match value {
1558            DataValue::Boolean(b) => Ok(b),
1559            DataValue::Integer(i) => Ok(i != 0),
1560            DataValue::Float(f) => Ok(f != 0.0),
1561            DataValue::Null => Ok(false),
1562            DataValue::String(s) => Ok(!s.is_empty()),
1563            DataValue::InternedString(s) => Ok(!s.is_empty()),
1564            _ => Ok(true), // Other types are considered truthy
1565        }
1566    }
1567}
1568
1569#[cfg(test)]
1570mod tests {
1571    use super::*;
1572    use crate::data::datatable::{DataColumn, DataRow};
1573
1574    fn create_test_table() -> DataTable {
1575        let mut table = DataTable::new("test");
1576        table.add_column(DataColumn::new("a"));
1577        table.add_column(DataColumn::new("b"));
1578        table.add_column(DataColumn::new("c"));
1579
1580        table
1581            .add_row(DataRow::new(vec![
1582                DataValue::Integer(10),
1583                DataValue::Float(2.5),
1584                DataValue::Integer(4),
1585            ]))
1586            .unwrap();
1587
1588        table
1589    }
1590
1591    #[test]
1592    fn test_evaluate_column() {
1593        let table = create_test_table();
1594        let evaluator = ArithmeticEvaluator::new(&table);
1595
1596        let expr = SqlExpression::Column("a".to_string());
1597        let result = evaluator.evaluate(&expr, 0).unwrap();
1598        assert_eq!(result, DataValue::Integer(10));
1599    }
1600
1601    #[test]
1602    fn test_evaluate_number_literal() {
1603        let table = create_test_table();
1604        let evaluator = ArithmeticEvaluator::new(&table);
1605
1606        let expr = SqlExpression::NumberLiteral("42".to_string());
1607        let result = evaluator.evaluate(&expr, 0).unwrap();
1608        assert_eq!(result, DataValue::Integer(42));
1609
1610        let expr = SqlExpression::NumberLiteral("3.14".to_string());
1611        let result = evaluator.evaluate(&expr, 0).unwrap();
1612        assert_eq!(result, DataValue::Float(3.14));
1613    }
1614
1615    #[test]
1616    fn test_add_values() {
1617        let table = create_test_table();
1618        let evaluator = ArithmeticEvaluator::new(&table);
1619
1620        // Integer + Integer
1621        let result = evaluator
1622            .add_values(&DataValue::Integer(5), &DataValue::Integer(3))
1623            .unwrap();
1624        assert_eq!(result, DataValue::Integer(8));
1625
1626        // Integer + Float
1627        let result = evaluator
1628            .add_values(&DataValue::Integer(5), &DataValue::Float(2.5))
1629            .unwrap();
1630        assert_eq!(result, DataValue::Float(7.5));
1631    }
1632
1633    #[test]
1634    fn test_multiply_values() {
1635        let table = create_test_table();
1636        let evaluator = ArithmeticEvaluator::new(&table);
1637
1638        // Integer * Float
1639        let result = evaluator
1640            .multiply_values(&DataValue::Integer(4), &DataValue::Float(2.5))
1641            .unwrap();
1642        assert_eq!(result, DataValue::Float(10.0));
1643    }
1644
1645    #[test]
1646    fn test_divide_values() {
1647        let table = create_test_table();
1648        let evaluator = ArithmeticEvaluator::new(&table);
1649
1650        // Exact division
1651        let result = evaluator
1652            .divide_values(&DataValue::Integer(10), &DataValue::Integer(2))
1653            .unwrap();
1654        assert_eq!(result, DataValue::Integer(5));
1655
1656        // Non-exact division
1657        let result = evaluator
1658            .divide_values(&DataValue::Integer(10), &DataValue::Integer(3))
1659            .unwrap();
1660        assert_eq!(result, DataValue::Float(10.0 / 3.0));
1661    }
1662
1663    #[test]
1664    fn test_division_by_zero() {
1665        let table = create_test_table();
1666        let evaluator = ArithmeticEvaluator::new(&table);
1667
1668        let result = evaluator.divide_values(&DataValue::Integer(10), &DataValue::Integer(0));
1669        assert!(result.is_err());
1670        assert!(result.unwrap_err().to_string().contains("Division by zero"));
1671    }
1672
1673    #[test]
1674    fn test_binary_op_expression() {
1675        let table = create_test_table();
1676        let evaluator = ArithmeticEvaluator::new(&table);
1677
1678        // a * b where a=10, b=2.5
1679        let expr = SqlExpression::BinaryOp {
1680            left: Box::new(SqlExpression::Column("a".to_string())),
1681            op: "*".to_string(),
1682            right: Box::new(SqlExpression::Column("b".to_string())),
1683        };
1684
1685        let result = evaluator.evaluate(&expr, 0).unwrap();
1686        assert_eq!(result, DataValue::Float(25.0));
1687    }
1688}
sql_cli/data/arithmetic_evaluator.rs

sql_cli/data/
arithmetic_evaluator.rs
