Repository: gluesql/gluesql
Pull request #295 (2021-08-18 06:19:05)

Add mathematical functions: DIV(), MOD().
## Add DIV() and MOD() function #273 
I have added two math functions to derive quotient and remainder from two numeric values(currently supported for Integer and Float type).
For people who are unfamiliar with terms 'quotient' and 'remainder', here's a nice explanation for them.
![image](https://user-images.githubusercontent.com/41038636/129846154-085d8f2a-a22f-4062-983f-a2e2884bd348.png)

## Usage
### DIV(dividend, divisor)
DIV() receives two numeric inputs and returns an integer typed number which is a quotient.
If there's at least one null input, the result will be null as well.
```sql
SELECT DIV(12, 2);
# Result: 6
SELECT DIV(-12.3, 3);
# Result: -4
SELECT DIV(12, Null);
# Result: Null
SELECT DIV(12, 0);
# Result: divisor should not be zero
```
### MOD(dividend, divisor)
MOD() receives two numeric values and returns a float typed number which is a remainder.
If there's at least one null input, the result will be null as well.
```sql
SELECT MOD(12, 2);
# Result: 0
SELECT MOD(-12.3, 3);
# Result: -0.3
SELECT MOD(12, Null);
# Result: Null
SELECT MOD(12, 0);
# Result: divisor should not be zero
```

## Reference
- Well-explained image about division: https://maths.forkids.education/explanation-of-divisor-remainder-dividend-quotient/
- PostgreSQL doc page about div and mod: https://www.postgresql.org/docs/9.5/functions-math.html


Patch:
diff --git a/src/ast/function.rs b/src/ast/function.rs
index e99c1b5d..1334f9f3 100644
--- a/src/ast/function.rs
+++ b/src/ast/function.rs
@@ -13,6 +13,8 @@ pub enum Function {
     Round(Expr),
     Floor(Expr),
     Trim(Expr),
+    Div { dividend: Expr, divisor: Expr },
+    Mod { dividend: Expr, divisor: Expr },
 }
 
 #[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]
diff --git a/src/executor/evaluate/error.rs b/src/executor/evaluate/error.rs
index e17293c5..50b6a704 100644
--- a/src/executor/evaluate/error.rs
+++ b/src/executor/evaluate/error.rs
@@ -16,9 +16,12 @@ pub enum EvaluateError {
     #[error("function requires string value: {0}")]
     FunctionRequiresStringValue(String),
 
-    #[error("function requires string value: {0}")]
+    #[error("function requires integer value: {0}")]
     FunctionRequiresIntegerValue(String),
 
+    #[error("function requires float or integer value: {0}")]
+    FunctionRequiresFloatOrIntegerValue(String),
+
     #[error("function requires usize value: {0}")]
     FunctionRequiresUSizeValue(String),
 
@@ -45,4 +48,7 @@ pub enum EvaluateError {
 
     #[error("unreachable empty aggregate value: {0:?}")]
     UnreachableEmptyAggregateValue(Aggregate),
+
+    #[error("divisor should not be zero")]
+    InvalidDivisorZero,
 }
diff --git a/src/executor/evaluate/mod.rs b/src/executor/evaluate/mod.rs
index 27868578..cb2e63b5 100644
--- a/src/executor/evaluate/mod.rs
+++ b/src/executor/evaluate/mod.rs
@@ -310,5 +310,51 @@ async fn evaluate_function<'a, T: 'static + Debug>(
             Nullable::Null => Ok(Value::Null),
         }
         .map(Evaluated::from),
+        Function::Div { dividend, divisor } | Function::Mod { dividend, divisor } => {
+            let name = if matches!(func, Function::Div { .. }) {
+                "DIV"
+            } else {
+                "MOD"
+            };
+
+            let dividend = match eval(dividend).await?.try_into()? {
+                Value::F64(number) => number,
+                Value::I64(number) => number as f64,
+                Value::Null => {
+                    return Ok(Evaluated::from(Value::Null));
+                }
+                _ => {
+                    return Err(EvaluateError::FunctionRequiresFloatOrIntegerValue(
+                        name.to_owned(),
+                    )
+                    .into());
+                }
+            };
+
+            let divisor = match eval(divisor).await?.try_into()? {
+                Value::F64(number) => match number {
+                    x if x == 0.0 => return Err(EvaluateError::InvalidDivisorZero.into()),
+                    _ => number,
+                },
+                Value::I64(number) => match number {
+                    0 => return Err(EvaluateError::InvalidDivisorZero.into()),
+                    _ => number as f64,
+                },
+                Value::Null => {
+                    return Ok(Evaluated::from(Value::Null));
+                }
+                _ => {
+                    return Err(EvaluateError::FunctionRequiresFloatOrIntegerValue(
+                        name.to_owned(),
+                    )
+                    .into());
+                }
+            };
+
+            match name {
+                "DIV" => Ok(Evaluated::from(Value::I64((dividend / divisor) as i64))),
+                _ => Ok(Evaluated::from(Value::F64(dividend % divisor))),
+            }
+        }
     }
 }
diff --git a/src/translate/function.rs b/src/translate/function.rs
index a5e54f45..a0665736 100644
--- a/src/translate/function.rs
+++ b/src/translate/function.rs
@@ -86,6 +86,28 @@ pub fn translate_function(sql_function: &SqlFunction) -> Result<Expr> {
         "MIN" => aggr!(Aggregate::Min),
         "MAX" => aggr!(Aggregate::Max),
         "TRIM" => func_with_one_arg!(Function::Trim),
+        "DIV" => {
+            check_len(name, args.len(), 2)?;
+
+            let dividend = translate_expr(args[0])?;
+            let divisor = translate_expr(args[1])?;
+
+            Ok(Expr::Function(Box::new(Function::Div {
+                dividend,
+                divisor,
+            })))
+        }
+        "MOD" => {
+            check_len(name, args.len(), 2)?;
+
+            let dividend = translate_expr(args[0])?;
+            let divisor = translate_expr(args[1])?;
+
+            Ok(Expr::Function(Box::new(Function::Mod {
+                dividend,
+                divisor,
+            })))
+        }
         _ => Err(TranslateError::UnsupportedFunction(name).into()),
     }
 }


Test patch:
diff --git a/src/tests/function/div_mod.rs b/src/tests/function/div_mod.rs
new file mode 100644
index 00000000..c63a3199
--- /dev/null
+++ b/src/tests/function/div_mod.rs
@@ -0,0 +1,134 @@
+use crate::*;
+
+test_case!(div_mod, async move {
+    use Value::{Null, F64, I64};
+    let eval_div = |dividend, divisor| (dividend / divisor) as i64;
+    let eval_mod = |dividend, divisor| dividend % divisor;
+    let test_cases = vec![
+        (
+            "CREATE TABLE FloatDiv (dividend FLOAT, divisor FLOAT)",
+            Ok(Payload::Create),
+        ),
+        (
+            r#"
+            INSERT INTO 
+                FloatDiv (dividend, divisor) 
+            VALUES 
+                (12.0, 3.0), (12.34, 56.78), (-12.3, 4.0)
+            "#,
+            Ok(Payload::Insert(3)),
+        ),
+        (
+            "
+            SELECT 
+                DIV(dividend, divisor),
+                MOD(dividend, divisor) 
+            FROM FloatDiv
+            ",
+            Ok(select!(
+                "DIV(dividend, divisor)" | "MOD(dividend, divisor)"
+                I64                      | F64;
+                eval_div(12.0, 3.0)        eval_mod(12.0, 3.0);
+                eval_div(12.34, 56.78)     eval_mod(12.34, 56.78);
+                eval_div(-12.3, 4.0)       eval_mod(-12.3, 4.0)
+            )),
+        ),
+        (
+            "SELECT DIV(1.0, 0.0) AS quotient FROM FloatDiv",
+            Err(EvaluateError::InvalidDivisorZero.into()),
+        ),
+        (
+            r#"SELECT DIV(1.0, "dividend") AS quotient FROM FloatDiv"#,
+            Err(EvaluateError::FunctionRequiresFloatOrIntegerValue("DIV".to_owned()).into()),
+        ),
+        (
+            "SELECT DIV(1.0) AS quotient FROM FloatDiv",
+            Err(TranslateError::FunctionArgsLengthNotMatching {
+                name: "DIV".to_owned(),
+                expected: 2,
+                found: 1,
+            }
+            .into()),
+        ),
+        (
+            "SELECT MOD(1.0, 2, 3) AS remainder FROM FloatDiv",
+            Err(TranslateError::FunctionArgsLengthNotMatching {
+                name: "MOD".to_owned(),
+                expected: 2,
+                found: 3,
+            }
+            .into()),
+        ),
+        (
+            "CREATE TABLE IntDiv (dividend INTEGER, divisor INTEGER)",
+            Ok(Payload::Create),
+        ),
+        (
+            r#"
+            INSERT INTO 
+                IntDiv (dividend, divisor) 
+            VALUES 
+                (12, 3), (12, 7), (12, 34), (-12, 7)
+            "#,
+            Ok(Payload::Insert(4)),
+        ),
+        (
+            "INSERT INTO IntDiv (dividend, divisor) VALUES (12, 2.0)",
+            Err(ValueError::FailedToParseNumber.into()),
+        ),
+        (
+            "
+            SELECT 
+                DIV(dividend, divisor),
+                MOD(dividend, divisor) 
+            FROM IntDiv 
+            ",
+            Ok(select!(
+                "DIV(dividend, divisor)" | "MOD(dividend, divisor)"
+                I64                      | F64;
+                eval_div(12_f64, 3_f64)    eval_mod(12_f64, 3_f64);
+                eval_div(12_f64, 7_f64)    eval_mod(12_f64, 7_f64);
+                eval_div(12_f64, 34_f64)   eval_mod(12_f64, 34_f64);
+                eval_div(-12_f64, 7_f64)   eval_mod(-12_f64, 7_f64)
+            )),
+        ),
+        (
+            "SELECT DIV(1, 0) AS quotient FROM IntDiv",
+            Err(EvaluateError::InvalidDivisorZero.into()),
+        ),
+        (
+            "CREATE TABLE MixDiv (dividend INTEGER NULL, divisor FLOAT NULL)",
+            Ok(Payload::Create),
+        ),
+        (
+            r#"
+            INSERT INTO 
+                MixDiv (dividend, divisor) 
+            VALUES 
+                (12, 3.0), (12, 34.0), (12, -5.2),
+                (12, NULL), (NULL, 34.0), (NULL, NULL)
+            "#,
+            Ok(Payload::Insert(6)),
+        ),
+        (
+            "
+            SELECT 
+                DIV(dividend, divisor),
+                MOD(dividend, divisor) 
+            FROM MixDiv 
+            ",
+            Ok(select_with_null!(
+                "DIV(dividend, divisor)"    | "MOD(dividend, divisor)";
+                I64(eval_div(12_f64, 3.0))    F64(eval_mod(12_f64, 3.0));
+                I64(eval_div(12_f64, 34.0))   F64(eval_mod(12_f64, 34.0));
+                I64(eval_div(12_f64, -5.2))   F64(eval_mod(12_f64, -5.2));
+                Null                          Null;
+                Null                          Null;
+                Null                          Null
+            )),
+        ),
+    ];
+    for (sql, expected) in test_cases {
+        test!(expected, sql);
+    }
+});
diff --git a/src/tests/function/mod.rs b/src/tests/function/mod.rs
index 67ed43b8..756131ec 100644
--- a/src/tests/function/mod.rs
+++ b/src/tests/function/mod.rs
@@ -1,5 +1,6 @@
 pub mod cast;
 pub mod ceil;
+pub mod div_mod;
 pub mod floor;
 pub mod left_right;
 pub mod round;
diff --git a/src/tests/mod.rs b/src/tests/mod.rs
index ac9b690c..1b01f1b9 100644
--- a/src/tests/mod.rs
+++ b/src/tests/mod.rs
@@ -58,6 +58,7 @@ macro_rules! generate_tests {
         glue!(function_upper_lower, function::upper_lower::upper_lower);
         glue!(function_left_right, function::left_right::left_right);
         glue!(function_trim, function::trim::trim);
+        glue!(function_div_mod, function::div_mod::div_mod);
         glue!(function_cast_literal, function::cast::cast_literal);
         glue!(function_cast_value, function::cast::cast_value);
         glue!(function_ceil, function::ceil::ceil);