sql_cli/sql/functions/
math.rs

1use anyhow::{anyhow, Result};
2
3use crate::data::datatable::DataValue;
4use crate::sql::functions::{ArgCount, FunctionCategory, FunctionSignature, SqlFunction};
5
6/// ROUND function - Round to specified decimal places
7pub struct RoundFunction;
8
9impl SqlFunction for RoundFunction {
10    fn signature(&self) -> FunctionSignature {
11        FunctionSignature {
12            name: "ROUND",
13            category: FunctionCategory::Mathematical,
14            arg_count: ArgCount::Range(1, 2),
15            description: "Round a number to specified decimal places",
16            returns: "NUMBER",
17            examples: vec![
18                "SELECT ROUND(3.14159, 2)", // Returns 3.14
19                "SELECT ROUND(123.456)",    // Returns 123
20                "SELECT ROUND(1234.5, -2)", // Returns 1200
21            ],
22        }
23    }
24
25    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
26        self.validate_args(args)?;
27
28        let value = match &args[0] {
29            DataValue::Integer(n) => *n as f64,
30            DataValue::Float(f) => *f,
31            DataValue::Null => return Ok(DataValue::Null),
32            _ => return Err(anyhow!("ROUND requires a numeric argument")),
33        };
34
35        let decimals = if args.len() == 2 {
36            match &args[1] {
37                DataValue::Integer(n) => *n as i32,
38                DataValue::Float(f) => *f as i32,
39                _ => return Err(anyhow!("ROUND precision must be a number")),
40            }
41        } else {
42            0
43        };
44
45        if decimals >= 0 {
46            let multiplier = 10_f64.powi(decimals);
47            let rounded = (value * multiplier).round() / multiplier;
48
49            // Return integer if input was integer and result is unchanged
50            if decimals == 0 {
51                Ok(DataValue::Integer(rounded as i64))
52            } else if matches!(&args[0], DataValue::Integer(_)) && rounded == value {
53                // Input was integer and rounding didn't change it
54                Ok(DataValue::Integer(value as i64))
55            } else {
56                Ok(DataValue::Float(rounded))
57            }
58        } else {
59            // Negative decimals round to left of decimal point
60            let divisor = 10_f64.powi(-decimals);
61            let rounded = (value / divisor).round() * divisor;
62            Ok(DataValue::Float(rounded))
63        }
64    }
65}
66
67/// ABS function - Absolute value
68pub struct AbsFunction;
69
70impl SqlFunction for AbsFunction {
71    fn signature(&self) -> FunctionSignature {
72        FunctionSignature {
73            name: "ABS",
74            category: FunctionCategory::Mathematical,
75            arg_count: ArgCount::Fixed(1),
76            description: "Returns the absolute value of a number",
77            returns: "NUMBER",
78            examples: vec![
79                "SELECT ABS(-5)",   // Returns 5
80                "SELECT ABS(3.14)", // Returns 3.14
81                "SELECT ABS(price - cost) FROM products",
82            ],
83        }
84    }
85
86    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
87        self.validate_args(args)?;
88
89        match &args[0] {
90            DataValue::Integer(n) => Ok(DataValue::Integer(n.abs())),
91            DataValue::Float(f) => Ok(DataValue::Float(f.abs())),
92            DataValue::Null => Ok(DataValue::Null),
93            _ => Err(anyhow!("ABS requires a numeric argument")),
94        }
95    }
96}
97
98/// FLOOR function - Round down to nearest integer
99pub struct FloorFunction;
100
101impl SqlFunction for FloorFunction {
102    fn signature(&self) -> FunctionSignature {
103        FunctionSignature {
104            name: "FLOOR",
105            category: FunctionCategory::Mathematical,
106            arg_count: ArgCount::Fixed(1),
107            description: "Returns the largest integer less than or equal to the value",
108            returns: "INTEGER",
109            examples: vec![
110                "SELECT FLOOR(3.7)",  // Returns 3
111                "SELECT FLOOR(-2.3)", // Returns -3
112                "SELECT FLOOR(5)",    // Returns 5
113            ],
114        }
115    }
116
117    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
118        self.validate_args(args)?;
119
120        match &args[0] {
121            DataValue::Integer(n) => Ok(DataValue::Integer(*n)),
122            DataValue::Float(f) => Ok(DataValue::Integer(f.floor() as i64)),
123            DataValue::Null => Ok(DataValue::Null),
124            _ => Err(anyhow!("FLOOR requires a numeric argument")),
125        }
126    }
127}
128
129/// CEILING function - Round up to nearest integer
130pub struct CeilingFunction;
131
132impl SqlFunction for CeilingFunction {
133    fn signature(&self) -> FunctionSignature {
134        FunctionSignature {
135            name: "CEILING",
136            category: FunctionCategory::Mathematical,
137            arg_count: ArgCount::Fixed(1),
138            description: "Returns the smallest integer greater than or equal to the value",
139            returns: "INTEGER",
140            examples: vec![
141                "SELECT CEILING(3.2)",  // Returns 4
142                "SELECT CEILING(-2.7)", // Returns -2
143                "SELECT CEILING(5)",    // Returns 5
144            ],
145        }
146    }
147
148    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
149        self.validate_args(args)?;
150
151        match &args[0] {
152            DataValue::Integer(n) => Ok(DataValue::Integer(*n)),
153            DataValue::Float(f) => Ok(DataValue::Integer(f.ceil() as i64)),
154            DataValue::Null => Ok(DataValue::Null),
155            _ => Err(anyhow!("CEILING requires a numeric argument")),
156        }
157    }
158}
159
160/// CEIL function - Alias for CEILING function
161pub struct CeilFunction;
162
163impl SqlFunction for CeilFunction {
164    fn signature(&self) -> FunctionSignature {
165        FunctionSignature {
166            name: "CEIL",
167            category: FunctionCategory::Mathematical,
168            arg_count: ArgCount::Fixed(1),
169            description: "Alias for CEILING - Returns the smallest integer greater than or equal to the value",
170            returns: "INTEGER",
171            examples: vec![
172                "SELECT CEIL(3.2)",  // Returns 4
173                "SELECT CEIL(-2.7)", // Returns -2
174                "SELECT CEIL(5)",    // Returns 5
175            ],
176        }
177    }
178
179    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
180        // Delegate to CEILING function
181        let ceiling_func = CeilingFunction;
182        ceiling_func.evaluate(args)
183    }
184}
185
186/// MOD function - Modulo operation
187pub struct ModFunction;
188
189impl SqlFunction for ModFunction {
190    fn signature(&self) -> FunctionSignature {
191        FunctionSignature {
192            name: "MOD",
193            category: FunctionCategory::Mathematical,
194            arg_count: ArgCount::Fixed(2),
195            description: "Returns the remainder of division",
196            returns: "NUMBER",
197            examples: vec![
198                "SELECT MOD(10, 3)", // Returns 1
199                "SELECT MOD(15, 4)", // Returns 3
200                "SELECT MOD(id, 100) FROM table",
201            ],
202        }
203    }
204
205    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
206        self.validate_args(args)?;
207
208        let dividend = match &args[0] {
209            DataValue::Integer(n) => *n as f64,
210            DataValue::Float(f) => *f,
211            DataValue::Null => return Ok(DataValue::Null),
212            _ => return Err(anyhow!("MOD requires numeric arguments")),
213        };
214
215        let divisor = match &args[1] {
216            DataValue::Integer(n) => *n as f64,
217            DataValue::Float(f) => *f,
218            DataValue::Null => return Ok(DataValue::Null),
219            _ => return Err(anyhow!("MOD requires numeric arguments")),
220        };
221
222        if divisor == 0.0 {
223            return Err(anyhow!("Division by zero in MOD"));
224        }
225
226        // Check if both inputs were integers
227        let both_integers =
228            matches!(&args[0], DataValue::Integer(_)) && matches!(&args[1], DataValue::Integer(_));
229
230        let result = dividend % divisor;
231
232        if both_integers && result.fract() == 0.0 {
233            Ok(DataValue::Integer(result as i64))
234        } else {
235            Ok(DataValue::Float(result))
236        }
237    }
238}
239
240/// QUOTIENT function - Integer division
241pub struct QuotientFunction;
242
243impl SqlFunction for QuotientFunction {
244    fn signature(&self) -> FunctionSignature {
245        FunctionSignature {
246            name: "QUOTIENT",
247            category: FunctionCategory::Mathematical,
248            arg_count: ArgCount::Fixed(2),
249            description: "Returns the integer portion of division",
250            returns: "INTEGER",
251            examples: vec![
252                "SELECT QUOTIENT(10, 3)",  // Returns 3
253                "SELECT QUOTIENT(15, 4)",  // Returns 3
254                "SELECT QUOTIENT(100, 7)", // Returns 14
255            ],
256        }
257    }
258
259    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
260        self.validate_args(args)?;
261
262        let numerator = match &args[0] {
263            DataValue::Integer(n) => *n as f64,
264            DataValue::Float(f) => *f,
265            DataValue::Null => return Ok(DataValue::Null),
266            _ => return Err(anyhow!("QUOTIENT requires numeric arguments")),
267        };
268
269        let denominator = match &args[1] {
270            DataValue::Integer(n) => *n as f64,
271            DataValue::Float(f) => *f,
272            DataValue::Null => return Ok(DataValue::Null),
273            _ => return Err(anyhow!("QUOTIENT requires numeric arguments")),
274        };
275
276        if denominator == 0.0 {
277            return Err(anyhow!("Division by zero in QUOTIENT"));
278        }
279
280        Ok(DataValue::Integer((numerator / denominator).trunc() as i64))
281    }
282}
283
284/// SQRT function - Square root
285pub struct SqrtFunction;
286
287impl SqlFunction for SqrtFunction {
288    fn signature(&self) -> FunctionSignature {
289        FunctionSignature {
290            name: "SQRT",
291            category: FunctionCategory::Mathematical,
292            arg_count: ArgCount::Fixed(1),
293            description: "Returns the square root of a number",
294            returns: "FLOAT",
295            examples: vec![
296                "SELECT SQRT(16)", // Returns 4.0
297                "SELECT SQRT(2)",  // Returns 1.414...
298                "SELECT SQRT(area) FROM squares",
299            ],
300        }
301    }
302
303    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
304        self.validate_args(args)?;
305
306        let value = match &args[0] {
307            DataValue::Integer(n) => *n as f64,
308            DataValue::Float(f) => *f,
309            DataValue::Null => return Ok(DataValue::Null),
310            _ => return Err(anyhow!("SQRT requires a numeric argument")),
311        };
312
313        if value < 0.0 {
314            return Err(anyhow!("SQRT of negative number"));
315        }
316
317        Ok(DataValue::Float(value.sqrt()))
318    }
319}
320
321/// EXP function - e raised to the power
322pub struct ExpFunction;
323
324impl SqlFunction for ExpFunction {
325    fn signature(&self) -> FunctionSignature {
326        FunctionSignature {
327            name: "EXP",
328            category: FunctionCategory::Mathematical,
329            arg_count: ArgCount::Fixed(1),
330            description: "Returns e raised to the power of the given number",
331            returns: "FLOAT",
332            examples: vec![
333                "SELECT EXP(1)",      // Returns e (2.718...)
334                "SELECT EXP(0)",      // Returns 1
335                "SELECT EXP(LN(10))", // Returns 10
336            ],
337        }
338    }
339
340    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
341        self.validate_args(args)?;
342
343        let value = match &args[0] {
344            DataValue::Integer(n) => *n as f64,
345            DataValue::Float(f) => *f,
346            DataValue::Null => return Ok(DataValue::Null),
347            _ => return Err(anyhow!("EXP requires a numeric argument")),
348        };
349
350        Ok(DataValue::Float(value.exp()))
351    }
352}
353
354/// LN function - Natural logarithm
355pub struct LnFunction;
356
357impl SqlFunction for LnFunction {
358    fn signature(&self) -> FunctionSignature {
359        FunctionSignature {
360            name: "LN",
361            category: FunctionCategory::Mathematical,
362            arg_count: ArgCount::Fixed(1),
363            description: "Returns the natural logarithm (base e) of a number",
364            returns: "FLOAT",
365            examples: vec![
366                "SELECT LN(2.718282)", // Returns ~1
367                "SELECT LN(10)",       // Returns 2.302...
368                "SELECT LN(1)",        // Returns 0
369            ],
370        }
371    }
372
373    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
374        self.validate_args(args)?;
375
376        let value = match &args[0] {
377            DataValue::Integer(n) => *n as f64,
378            DataValue::Float(f) => *f,
379            DataValue::Null => return Ok(DataValue::Null),
380            _ => return Err(anyhow!("LN requires a numeric argument")),
381        };
382
383        if value <= 0.0 {
384            return Err(anyhow!("LN of non-positive number"));
385        }
386
387        Ok(DataValue::Float(value.ln()))
388    }
389}
390
391/// LOG function - Logarithm with specified base
392pub struct LogFunction;
393
394impl SqlFunction for LogFunction {
395    fn signature(&self) -> FunctionSignature {
396        FunctionSignature {
397            name: "LOG",
398            category: FunctionCategory::Mathematical,
399            arg_count: ArgCount::Range(1, 2),
400            description:
401                "Returns the logarithm of a number (base 10 by default, or specified base)",
402            returns: "FLOAT",
403            examples: vec![
404                "SELECT LOG(100)",      // Returns 2 (base 10)
405                "SELECT LOG(8, 2)",     // Returns 3 (log base 2 of 8)
406                "SELECT LOG(1000, 10)", // Returns 3
407            ],
408        }
409    }
410
411    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
412        self.validate_args(args)?;
413
414        let value = match &args[0] {
415            DataValue::Integer(n) => *n as f64,
416            DataValue::Float(f) => *f,
417            DataValue::Null => return Ok(DataValue::Null),
418            _ => return Err(anyhow!("LOG requires numeric arguments")),
419        };
420
421        if value <= 0.0 {
422            return Err(anyhow!("LOG of non-positive number"));
423        }
424
425        let base = if args.len() == 2 {
426            match &args[1] {
427                DataValue::Integer(n) => *n as f64,
428                DataValue::Float(f) => *f,
429                _ => return Err(anyhow!("LOG base must be a number")),
430            }
431        } else {
432            10.0
433        };
434
435        if base <= 0.0 || base == 1.0 {
436            return Err(anyhow!("Invalid logarithm base"));
437        }
438
439        Ok(DataValue::Float(value.log(base)))
440    }
441}
442
443/// LOG10 function - Base-10 logarithm
444pub struct Log10Function;
445
446impl SqlFunction for Log10Function {
447    fn signature(&self) -> FunctionSignature {
448        FunctionSignature {
449            name: "LOG10",
450            category: FunctionCategory::Mathematical,
451            arg_count: ArgCount::Fixed(1),
452            description: "Returns the base-10 logarithm of a number",
453            returns: "FLOAT",
454            examples: vec![
455                "SELECT LOG10(100)",  // Returns 2
456                "SELECT LOG10(1000)", // Returns 3
457                "SELECT LOG10(0.1)",  // Returns -1
458            ],
459        }
460    }
461
462    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
463        self.validate_args(args)?;
464
465        let value = match &args[0] {
466            DataValue::Integer(n) => *n as f64,
467            DataValue::Float(f) => *f,
468            DataValue::Null => return Ok(DataValue::Null),
469            _ => return Err(anyhow!("LOG10 requires a numeric argument")),
470        };
471
472        if value <= 0.0 {
473            return Err(anyhow!("LOG10 of non-positive number"));
474        }
475
476        Ok(DataValue::Float(value.log10()))
477    }
478}
479
480/// POWER function - Raise to power
481pub struct PowerFunction;
482
483impl SqlFunction for PowerFunction {
484    fn signature(&self) -> FunctionSignature {
485        FunctionSignature {
486            name: "POWER",
487            category: FunctionCategory::Mathematical,
488            arg_count: ArgCount::Fixed(2),
489            description: "Returns a number raised to a power",
490            returns: "NUMBER",
491            examples: vec![
492                "SELECT POWER(2, 3)",   // Returns 8
493                "SELECT POWER(10, -2)", // Returns 0.01
494                "SELECT POWER(9, 0.5)", // Returns 3
495            ],
496        }
497    }
498
499    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
500        self.validate_args(args)?;
501
502        let base = match &args[0] {
503            DataValue::Integer(n) => *n as f64,
504            DataValue::Float(f) => *f,
505            DataValue::Null => return Ok(DataValue::Null),
506            _ => return Err(anyhow!("POWER requires numeric arguments")),
507        };
508
509        let exponent = match &args[1] {
510            DataValue::Integer(n) => *n as f64,
511            DataValue::Float(f) => *f,
512            DataValue::Null => return Ok(DataValue::Null),
513            _ => return Err(anyhow!("POWER requires numeric arguments")),
514        };
515
516        let result = base.powf(exponent);
517
518        // Always return float for POWER function to match SQL standards
519        Ok(DataValue::Float(result))
520    }
521}
522
523/// POW function - Alias for POWER function
524pub struct PowFunction;
525
526impl SqlFunction for PowFunction {
527    fn signature(&self) -> FunctionSignature {
528        FunctionSignature {
529            name: "POW",
530            category: FunctionCategory::Mathematical,
531            arg_count: ArgCount::Fixed(2),
532            description: "Returns a number raised to a power (alias for POWER)",
533            returns: "FLOAT",
534            examples: vec![
535                "SELECT POW(2, 3)",   // Returns 8.0
536                "SELECT POW(10, -2)", // Returns 0.01
537                "SELECT POW(9, 0.5)", // Returns 3.0
538            ],
539        }
540    }
541
542    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
543        // Delegate to POWER function
544        let power_func = PowerFunction;
545        power_func.evaluate(args)
546    }
547}
548
549/// DEGREES function - Convert radians to degrees
550pub struct DegreesFunction;
551
552impl SqlFunction for DegreesFunction {
553    fn signature(&self) -> FunctionSignature {
554        FunctionSignature {
555            name: "DEGREES",
556            category: FunctionCategory::Mathematical,
557            arg_count: ArgCount::Fixed(1),
558            description: "Convert radians to degrees",
559            returns: "FLOAT",
560            examples: vec![
561                "SELECT DEGREES(PI())",   // Returns 180
562                "SELECT DEGREES(PI()/2)", // Returns 90
563                "SELECT DEGREES(1)",      // Returns 57.2958...
564            ],
565        }
566    }
567
568    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
569        self.validate_args(args)?;
570
571        let radians = match &args[0] {
572            DataValue::Integer(n) => *n as f64,
573            DataValue::Float(f) => *f,
574            DataValue::Null => return Ok(DataValue::Null),
575            _ => return Err(anyhow!("DEGREES requires a numeric argument")),
576        };
577
578        Ok(DataValue::Float(radians * 180.0 / std::f64::consts::PI))
579    }
580}
581
582/// RADIANS function - Convert degrees to radians
583pub struct RadiansFunction;
584
585impl SqlFunction for RadiansFunction {
586    fn signature(&self) -> FunctionSignature {
587        FunctionSignature {
588            name: "RADIANS",
589            category: FunctionCategory::Mathematical,
590            arg_count: ArgCount::Fixed(1),
591            description: "Convert degrees to radians",
592            returns: "FLOAT",
593            examples: vec![
594                "SELECT RADIANS(180)", // Returns PI
595                "SELECT RADIANS(90)",  // Returns PI/2
596                "SELECT RADIANS(45)",  // Returns PI/4
597            ],
598        }
599    }
600
601    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
602        self.validate_args(args)?;
603
604        let degrees = match &args[0] {
605            DataValue::Integer(n) => *n as f64,
606            DataValue::Float(f) => *f,
607            DataValue::Null => return Ok(DataValue::Null),
608            _ => return Err(anyhow!("RADIANS requires a numeric argument")),
609        };
610
611        Ok(DataValue::Float(degrees * std::f64::consts::PI / 180.0))
612    }
613}
614
615/// FACTORIAL function - Calculate factorial of a number
616pub struct FactorialFunction;
617
618impl FactorialFunction {
619    /// Precomputed factorials for 0! through 20!
620    /// Beyond 20!, the values exceed u64 max
621    const FACTORIAL_TABLE: [u64; 21] = [
622        1,                         // 0!
623        1,                         // 1!
624        2,                         // 2!
625        6,                         // 3!
626        24,                        // 4!
627        120,                       // 5!
628        720,                       // 6!
629        5_040,                     // 7!
630        40_320,                    // 8!
631        362_880,                   // 9!
632        3_628_800,                 // 10!
633        39_916_800,                // 11!
634        479_001_600,               // 12!
635        6_227_020_800,             // 13!
636        87_178_291_200,            // 14!
637        1_307_674_368_000,         // 15!
638        20_922_789_888_000,        // 16!
639        355_687_428_096_000,       // 17!
640        6_402_373_705_728_000,     // 18!
641        121_645_100_408_832_000,   // 19!
642        2_432_902_008_176_640_000, // 20!
643    ];
644
645    fn factorial(n: u64) -> Result<u64> {
646        if n <= 20 {
647            Ok(Self::FACTORIAL_TABLE[n as usize])
648        } else {
649            Err(anyhow!("FACTORIAL: argument {} too large (max is 20)", n))
650        }
651    }
652}
653
654impl SqlFunction for FactorialFunction {
655    fn signature(&self) -> FunctionSignature {
656        FunctionSignature {
657            name: "FACTORIAL",
658            category: FunctionCategory::Mathematical,
659            arg_count: ArgCount::Fixed(1),
660            description: "Returns the factorial of a non-negative integer (n!)",
661            returns: "INTEGER",
662            examples: vec![
663                "SELECT FACTORIAL(5)",  // Returns 120
664                "SELECT FACTORIAL(10)", // Returns 3628800
665                "SELECT FACTORIAL(0)",  // Returns 1
666            ],
667        }
668    }
669
670    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
671        self.validate_args(args)?;
672
673        let n = match &args[0] {
674            DataValue::Integer(i) if *i >= 0 && *i <= 20 => *i as u64,
675            DataValue::Integer(i) if *i < 0 => {
676                return Err(anyhow!("FACTORIAL requires a non-negative integer"))
677            }
678            DataValue::Integer(i) => {
679                return Err(anyhow!("FACTORIAL: argument {} too large (max is 20)", i))
680            }
681            DataValue::Float(f) if f.is_finite() && *f >= 0.0 && f.floor() == *f => {
682                let n = *f as i64;
683                if n > 20 {
684                    return Err(anyhow!("FACTORIAL: argument {} too large (max is 20)", n));
685                }
686                n as u64
687            }
688            DataValue::Null => return Ok(DataValue::Null),
689            _ => return Err(anyhow!("FACTORIAL requires a non-negative integer")),
690        };
691
692        Ok(DataValue::Integer(Self::factorial(n)? as i64))
693    }
694}
695
696/// SUM_N function - Sum of first n natural numbers (triangular number)
697pub struct SumNFunction;
698
699impl SqlFunction for SumNFunction {
700    fn signature(&self) -> FunctionSignature {
701        FunctionSignature {
702            name: "SUM_N",
703            category: FunctionCategory::Mathematical,
704            arg_count: ArgCount::Fixed(1),
705            description: "Calculate sum of first n natural numbers (triangular number)",
706            returns: "INTEGER",
707            examples: vec![
708                "SELECT SUM_N(10)",  // Returns 55 (1+2+3+...+10)
709                "SELECT SUM_N(100)", // Returns 5050
710                "SELECT SUM_N(5)",   // Returns 15 (1+2+3+4+5)
711            ],
712        }
713    }
714
715    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
716        self.validate_args(args)?;
717
718        let n = match &args[0] {
719            DataValue::Integer(n) if *n >= 0 => *n,
720            DataValue::Float(f) if f.is_finite() && *f >= 0.0 && f.floor() == *f => *f as i64,
721            DataValue::Null => return Ok(DataValue::Null),
722            _ => return Err(anyhow!("SUM_N requires a non-negative integer")),
723        };
724
725        // Calculate n * (n + 1) / 2
726        // Check for overflow
727        if n > 3037000499 {
728            // sqrt(i64::MAX * 2) approximately
729            return Err(anyhow!("SUM_N: argument {} too large (would overflow)", n));
730        }
731
732        let result = n * (n + 1) / 2;
733        Ok(DataValue::Integer(result))
734    }
735}
736
737/// SUM_N_SQR function - sum of squares from 1 to n
738pub struct SumNSqrFunction;
739
740impl SqlFunction for SumNSqrFunction {
741    fn signature(&self) -> FunctionSignature {
742        FunctionSignature {
743            name: "SUM_N_SQR",
744            category: FunctionCategory::Mathematical,
745            arg_count: ArgCount::Fixed(1),
746            description: "Sum of squares from 1 to n: 1² + 2² + ... + n²",
747            returns: "Integer - sum of squares (n(n+1)(2n+1)/6)",
748            examples: vec![
749                "SELECT SUM_N_SQR(5) -- Returns 55 (1+4+9+16+25)",
750                "SELECT SUM_N_SQR(10) -- Returns 385",
751            ],
752        }
753    }
754
755    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
756        if args.len() != 1 {
757            return Err(anyhow!("SUM_N_SQR requires exactly 1 argument"));
758        }
759
760        let n = match &args[0] {
761            DataValue::Integer(i) => *i,
762            DataValue::Float(f) => *f as i64,
763            _ => return Err(anyhow!("SUM_N_SQR requires a numeric argument")),
764        };
765
766        if n < 0 {
767            return Err(anyhow!("SUM_N_SQR requires a non-negative number"));
768        }
769
770        // Formula: n(n+1)(2n+1)/6
771        let result = n * (n + 1) * (2 * n + 1) / 6;
772        Ok(DataValue::Integer(result))
773    }
774}
775
776/// SUM_N_CUBE function - sum of cubes from 1 to n
777pub struct SumNCubeFunction;
778
779impl SqlFunction for SumNCubeFunction {
780    fn signature(&self) -> FunctionSignature {
781        FunctionSignature {
782            name: "SUM_N_CUBE",
783            category: FunctionCategory::Mathematical,
784            arg_count: ArgCount::Fixed(1),
785            description: "Sum of cubes from 1 to n: 1³ + 2³ + ... + n³",
786            returns: "Integer - sum of cubes ([n(n+1)/2]²)",
787            examples: vec![
788                "SELECT SUM_N_CUBE(4) -- Returns 100 (1+8+27+64)",
789                "SELECT SUM_N_CUBE(5) -- Returns 225",
790            ],
791        }
792    }
793
794    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
795        if args.len() != 1 {
796            return Err(anyhow!("SUM_N_CUBE requires exactly 1 argument"));
797        }
798
799        let n = match &args[0] {
800            DataValue::Integer(i) => *i,
801            DataValue::Float(f) => *f as i64,
802            _ => return Err(anyhow!("SUM_N_CUBE requires a numeric argument")),
803        };
804
805        if n < 0 {
806            return Err(anyhow!("SUM_N_CUBE requires a non-negative number"));
807        }
808
809        // Formula: [n(n+1)/2]² - which is the square of sum_n!
810        let sum_n = n * (n + 1) / 2;
811        let result = sum_n * sum_n;
812        Ok(DataValue::Integer(result))
813    }
814}
815
816/// HARMONIC function - harmonic series sum 1 + 1/2 + 1/3 + ... + 1/n
817pub struct HarmonicFunction;
818
819impl SqlFunction for HarmonicFunction {
820    fn signature(&self) -> FunctionSignature {
821        FunctionSignature {
822            name: "HARMONIC",
823            category: FunctionCategory::Mathematical,
824            arg_count: ArgCount::Fixed(1),
825            description: "Harmonic series: 1 + 1/2 + 1/3 + ... + 1/n",
826            returns: "Float - sum of harmonic series",
827            examples: vec![
828                "SELECT HARMONIC(4) -- Returns 2.0833... (1 + 0.5 + 0.333... + 0.25)",
829                "SELECT HARMONIC(10) -- Returns 2.9290...",
830            ],
831        }
832    }
833
834    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
835        if args.len() != 1 {
836            return Err(anyhow!("HARMONIC requires exactly 1 argument"));
837        }
838
839        let n = match &args[0] {
840            DataValue::Integer(i) => *i,
841            DataValue::Float(f) => *f as i64,
842            _ => return Err(anyhow!("HARMONIC requires a numeric argument")),
843        };
844
845        if n <= 0 {
846            return Err(anyhow!("HARMONIC requires a positive number"));
847        }
848
849        let mut sum = 0.0;
850        for i in 1..=n {
851            sum += 1.0 / i as f64;
852        }
853
854        Ok(DataValue::Float(sum))
855    }
856}
857
858/// FIBONACCI function - returns the nth Fibonacci number
859pub struct FibonacciFunction;
860
861impl SqlFunction for FibonacciFunction {
862    fn signature(&self) -> FunctionSignature {
863        FunctionSignature {
864            name: "FIBONACCI",
865            category: FunctionCategory::Mathematical,
866            arg_count: ArgCount::Fixed(1),
867            description: "Returns the nth Fibonacci number (0, 1, 1, 2, 3, 5, 8, ...)",
868            returns: "Integer - nth Fibonacci number",
869            examples: vec![
870                "SELECT FIBONACCI(7) -- Returns 13",
871                "SELECT FIBONACCI(10) -- Returns 55",
872            ],
873        }
874    }
875
876    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
877        if args.len() != 1 {
878            return Err(anyhow!("FIBONACCI requires exactly 1 argument"));
879        }
880
881        let n = match &args[0] {
882            DataValue::Integer(i) => *i,
883            DataValue::Float(f) => *f as i64,
884            _ => return Err(anyhow!("FIBONACCI requires a numeric argument")),
885        };
886
887        if n < 0 {
888            return Err(anyhow!("FIBONACCI requires a non-negative number"));
889        }
890
891        if n == 0 {
892            return Ok(DataValue::Integer(0));
893        }
894        if n == 1 {
895            return Ok(DataValue::Integer(1));
896        }
897
898        let mut a = 0i64;
899        let mut b = 1i64;
900
901        for _ in 2..=n {
902            let temp = a + b;
903            a = b;
904            b = temp;
905        }
906
907        Ok(DataValue::Integer(b))
908    }
909}
910
911/// GEOMETRIC function - geometric series sum: a + ar + ar² + ... + ar^(n-1)
912pub struct GeometricFunction;
913
914impl SqlFunction for GeometricFunction {
915    fn signature(&self) -> FunctionSignature {
916        FunctionSignature {
917            name: "GEOMETRIC",
918            category: FunctionCategory::Mathematical,
919            arg_count: ArgCount::Fixed(3),
920            description: "Geometric series sum: a + ar + ar² + ... + ar^(n-1)",
921            returns: "Float - sum of geometric series",
922            examples: vec![
923                "SELECT GEOMETRIC(1, 2, 5) -- Returns 31 (1 + 2 + 4 + 8 + 16)",
924                "SELECT GEOMETRIC(1, 0.5, 10) -- Returns 1.998... (converging series)",
925            ],
926        }
927    }
928
929    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
930        if args.len() != 3 {
931            return Err(anyhow!("GEOMETRIC requires exactly 3 arguments (a, r, n)"));
932        }
933
934        let a = match &args[0] {
935            DataValue::Integer(i) => *i as f64,
936            DataValue::Float(f) => *f,
937            _ => return Err(anyhow!("GEOMETRIC first argument (a) must be numeric")),
938        };
939
940        let r = match &args[1] {
941            DataValue::Integer(i) => *i as f64,
942            DataValue::Float(f) => *f,
943            _ => return Err(anyhow!("GEOMETRIC second argument (r) must be numeric")),
944        };
945
946        let n = match &args[2] {
947            DataValue::Integer(i) => *i,
948            DataValue::Float(f) => *f as i64,
949            _ => return Err(anyhow!("GEOMETRIC third argument (n) must be numeric")),
950        };
951
952        if n < 0 {
953            return Err(anyhow!("GEOMETRIC requires non-negative n"));
954        }
955
956        if n == 0 {
957            return Ok(DataValue::Float(0.0));
958        }
959
960        // Formula: a * (1 - r^n) / (1 - r) for r != 1
961        // If r = 1, sum = a * n
962        let sum = if (r - 1.0).abs() < 1e-10 {
963            a * n as f64
964        } else {
965            a * (1.0 - r.powi(n as i32)) / (1.0 - r)
966        };
967
968        Ok(DataValue::Float(sum))
969    }
970}
971
972/// Register all math functions
973pub fn register_math_functions(registry: &mut super::FunctionRegistry) {
974    registry.register(Box::new(RoundFunction));
975    registry.register(Box::new(AbsFunction));
976    registry.register(Box::new(FloorFunction));
977    registry.register(Box::new(CeilingFunction));
978    registry.register(Box::new(CeilFunction)); // Add CEIL alias
979    registry.register(Box::new(ModFunction));
980    registry.register(Box::new(QuotientFunction));
981    registry.register(Box::new(SqrtFunction));
982    registry.register(Box::new(ExpFunction));
983    registry.register(Box::new(LnFunction));
984    registry.register(Box::new(LogFunction));
985    registry.register(Box::new(Log10Function));
986    registry.register(Box::new(PowerFunction));
987    registry.register(Box::new(PowFunction)); // Add POW alias
988    registry.register(Box::new(DegreesFunction));
989    registry.register(Box::new(RadiansFunction));
990    registry.register(Box::new(FactorialFunction));
991    registry.register(Box::new(SumNFunction));
992    registry.register(Box::new(SumNSqrFunction));
993    registry.register(Box::new(SumNCubeFunction));
994    registry.register(Box::new(HarmonicFunction));
995    registry.register(Box::new(FibonacciFunction));
996    registry.register(Box::new(GeometricFunction));
997}
sql_cli/sql/functions/math.rs

sql_cli/sql/functions/
math.rs
