Repository: gluesql/gluesql
Pull request #291 (2021-08-16 11:07:31)

Add Floor, Ceil, Round Function
I'm done to add the functions below.

Ceil Func : Nearest integer greater than or equal to argument
- example ) 11.2 -> 12
- example ) -11.3 -> 11

Floor Func : Nearest integer less than or equal to argument

example ) 11.3 -> 11
example ) -11.3 -> -12
Rounds Func : v to s decimal places. Ties are broken by rounding away from zero.

example ) 11.6 -> 12
exmaple ) 11.2 -> 11


Patch:
diff --git a/src/ast/function.rs b/src/ast/function.rs
index ad9e5099..e99c1b5d 100644
--- a/src/ast/function.rs
+++ b/src/ast/function.rs
@@ -9,6 +9,9 @@ pub enum Function {
     Upper(Expr),
     Left { expr: Expr, size: Expr },
     Right { expr: Expr, size: Expr },
+    Ceil(Expr),
+    Round(Expr),
+    Floor(Expr),
     Trim(Expr),
 }
 
diff --git a/src/executor/evaluate/error.rs b/src/executor/evaluate/error.rs
index 4fdef3c7..e17293c5 100644
--- a/src/executor/evaluate/error.rs
+++ b/src/executor/evaluate/error.rs
@@ -22,6 +22,9 @@ pub enum EvaluateError {
     #[error("function requires usize value: {0}")]
     FunctionRequiresUSizeValue(String),
 
+    #[error("function requires float value: {0}")]
+    FunctionRequiresFloatValue(String),
+
     #[error("value not found: {0}")]
     ValueNotFound(String),
 
diff --git a/src/executor/evaluate/mod.rs b/src/executor/evaluate/mod.rs
index 37f8d687..27868578 100644
--- a/src/executor/evaluate/mod.rs
+++ b/src/executor/evaluate/mod.rs
@@ -228,6 +228,15 @@ async fn evaluate_function<'a, T: 'static + Debug>(
         }
     };
 
+    let eval_to_float = |name: &'static str, expr| async move {
+        match eval(expr).await?.try_into()? {
+            Value::I64(v) => Ok(Nullable::Value(v as f64)),
+            Value::F64(v) => Ok(Nullable::Value(v)),
+            Value::Null => Ok(Nullable::Null),
+            _ => Err::<_, Error>(EvaluateError::FunctionRequiresFloatValue(name.to_owned()).into()),
+        }
+    };
+
     match func {
         Function::Lower(expr) => match eval_to_str("LOWER", expr).await? {
             Nullable::Value(v) => Ok(Value::Str(v.to_lowercase())),
@@ -281,6 +290,21 @@ async fn evaluate_function<'a, T: 'static + Debug>(
 
             Ok(Evaluated::from(Value::Str(converted)))
         }
+        Function::Ceil(expr) => match eval_to_float("CEIL", expr).await? {
+            Nullable::Value(v) => Ok(Value::F64(v.ceil())),
+            Nullable::Null => Ok(Value::Null),
+        }
+        .map(Evaluated::from),
+        Function::Round(expr) => match eval_to_float("ROUND", expr).await? {
+            Nullable::Value(v) => Ok(Value::F64(v.round())),
+            Nullable::Null => Ok(Value::Null),
+        }
+        .map(Evaluated::from),
+        Function::Floor(expr) => match eval_to_float("FLOOR", expr).await? {
+            Nullable::Value(v) => Ok(Value::F64(v.floor())),
+            Nullable::Null => Ok(Value::Null),
+        }
+        .map(Evaluated::from),
         Function::Trim(expr) => match eval_to_str("TRIM", expr).await? {
             Nullable::Value(string) => Ok(Value::Str(string.trim().to_owned())),
             Nullable::Null => Ok(Value::Null),
diff --git a/src/translate/function.rs b/src/translate/function.rs
index 13801413..a5e54f45 100644
--- a/src/translate/function.rs
+++ b/src/translate/function.rs
@@ -78,6 +78,9 @@ pub fn translate_function(sql_function: &SqlFunction) -> Result<Expr> {
 
             Ok(Expr::Function(Box::new(Function::Right { expr, size })))
         }
+        "CEIL" => func_with_one_arg!(Function::Ceil),
+        "ROUND" => func_with_one_arg!(Function::Round),
+        "FLOOR" => func_with_one_arg!(Function::Floor),
         "COUNT" => aggr!(Aggregate::Count),
         "SUM" => aggr!(Aggregate::Sum),
         "MIN" => aggr!(Aggregate::Min),


Test patch:
diff --git a/src/tests/function/ceil.rs b/src/tests/function/ceil.rs
new file mode 100644
index 00000000..305dce2d
--- /dev/null
+++ b/src/tests/function/ceil.rs
@@ -0,0 +1,57 @@
+use crate::*;
+
+test_case!(ceil, async move {
+    use Value::{Null, F64};
+
+    let test_cases = vec![
+        (
+            "CREATE TABLE SingleItem (id INTEGER PRIMARY KEY)",
+            Ok(Payload::Create),
+        ),
+        (
+            r#"INSERT INTO SingleItem VALUES (0)"#,
+            Ok(Payload::Insert(1)),
+        ),
+        (
+            "SELECT CEIL(0.3) AS ceil1, 
+            CEIL(-0.8) AS ceil2, 
+            CEIL(10) AS ceil3, 
+            CEIL(6.87421) AS ceil4 
+            FROM SingleItem",
+            Ok(select!(
+                "ceil1"        | "ceil2"                   | "ceil3"             | "ceil4";
+                F64            | F64                       | F64                 | F64 ;
+                0.3_f64.ceil()   f64::ceil(-0.8_f64)         f64::from(10).ceil()  6.87421_f64.ceil()
+            )),
+        ),
+        (
+            "SELECT CEIL('string') AS ceil FROM SingleItem",
+            Err(EvaluateError::FunctionRequiresFloatValue(String::from("CEIL")).into()),
+        ),
+        (
+            "SELECT CEIL(NULL) AS ceil FROM SingleItem",
+            Ok(select_with_null!(ceil; Null)),
+        ),
+        (
+            "SELECT CEIL(TRUE) AS ceil FROM SingleItem",
+            Err(EvaluateError::FunctionRequiresFloatValue(String::from("CEIL")).into()),
+        ),
+        (
+            "SELECT CEIL(FALSE) AS ceil FROM SingleItem",
+            Err(EvaluateError::FunctionRequiresFloatValue(String::from("CEIL")).into()),
+        ),
+        (
+            "SELECT CEIL('string', 'string2') AS ceil FROM SingleItem",
+            Err(TranslateError::FunctionArgsLengthNotMatching {
+                name: "CEIL".to_owned(),
+                expected: 1,
+                found: 2,
+            }
+            .into()),
+        ),
+    ];
+
+    for (sql, expected) in test_cases.into_iter() {
+        test!(expected, sql);
+    }
+});
diff --git a/src/tests/function/floor.rs b/src/tests/function/floor.rs
new file mode 100644
index 00000000..b6dd2cfc
--- /dev/null
+++ b/src/tests/function/floor.rs
@@ -0,0 +1,59 @@
+use crate::*;
+
+test_case!(floor, async move {
+    use Value::{Null, F64};
+
+    let test_cases = vec![
+        (
+            "CREATE TABLE SingleItem (id INTEGER PRIMARY KEY)",
+            Ok(Payload::Create),
+        ),
+        (
+            r#"INSERT INTO SingleItem VALUES (0)"#,
+            Ok(Payload::Insert(1)),
+        ),
+        (
+            r#"
+            SELECT 
+            FLOOR(0.3) as floor1, 
+            FLOOR(-0.8) as floor2, 
+            FLOOR(10) as floor3, 
+            FLOOR(6.87421) as floor4 
+            FROM SingleItem"#,
+            Ok(select!(
+                floor1          | floor2                 | floor3               | floor4
+                F64             | F64                    | F64                  | F64;
+                0.3_f64.floor()   f64::floor(-0.8_f64)     f64::from(10).floor()  6.87421_f64.floor()
+            )),
+        ),
+        (
+            "SELECT FLOOR('string') AS floor FROM SingleItem",
+            Err(EvaluateError::FunctionRequiresFloatValue(String::from("FLOOR")).into()),
+        ),
+        (
+            "SELECT FLOOR(NULL) AS floor FROM SingleItem",
+            Ok(select_with_null!(floor; Null)),
+        ),
+        (
+            "SELECT FLOOR(TRUE) AS floor FROM SingleItem",
+            Err(EvaluateError::FunctionRequiresFloatValue(String::from("FLOOR")).into()),
+        ),
+        (
+            "SELECT FLOOR(FALSE) AS floor FROM SingleItem",
+            Err(EvaluateError::FunctionRequiresFloatValue(String::from("FLOOR")).into()),
+        ),
+        (
+            "SELECT FLOOR('string', 'string2') AS floor FROM SingleItem",
+            Err(TranslateError::FunctionArgsLengthNotMatching {
+                name: "FLOOR".to_owned(),
+                expected: 1,
+                found: 2,
+            }
+            .into()),
+        ),
+    ];
+
+    for (sql, expected) in test_cases.into_iter() {
+        test!(expected, sql);
+    }
+});
diff --git a/src/tests/function/mod.rs b/src/tests/function/mod.rs
index c34f5e47..67ed43b8 100644
--- a/src/tests/function/mod.rs
+++ b/src/tests/function/mod.rs
@@ -1,4 +1,7 @@
 pub mod cast;
+pub mod ceil;
+pub mod floor;
 pub mod left_right;
+pub mod round;
 pub mod trim;
 pub mod upper_lower;
diff --git a/src/tests/function/round.rs b/src/tests/function/round.rs
new file mode 100644
index 00000000..4eb45647
--- /dev/null
+++ b/src/tests/function/round.rs
@@ -0,0 +1,53 @@
+use crate::*;
+
+test_case!(round, async move {
+    use Value::{Null, F64};
+
+    let test_cases = vec![
+        (
+            "CREATE TABLE SingleItem (id INTEGER PRIMARY KEY)",
+            Ok(Payload::Create),
+        ),
+        (
+            r#"INSERT INTO SingleItem VALUES (0)"#,
+            Ok(Payload::Insert(1)),
+        ),
+        (
+            "SELECT ROUND(0.3) AS round1, ROUND(-0.8) AS round2, ROUND(10) AS round3, ROUND(6.87421) AS round4 FROM SingleItem",
+            Ok(select!(
+                round1          | round2                       | round3               | round4
+                F64             | F64                          | F64                  | F64;
+                0.3_f64.round()   f64::round(-0.8_f64)           f64::from(10).round()  6.87421_f64.round()
+            )),
+        ),
+        (
+            "SELECT ROUND('string') AS round FROM SingleItem",
+            Err(EvaluateError::FunctionRequiresFloatValue(String::from("ROUND")).into()),
+        ),
+        (
+            "SELECT ROUND(NULL) AS round FROM SingleItem",
+            Ok(select_with_null!(round; Null)),
+        ),
+        (
+            "SELECT ROUND(TRUE) AS round FROM SingleItem",
+            Err(EvaluateError::FunctionRequiresFloatValue(String::from("ROUND")).into()),
+        ),
+        (
+            "SELECT ROUND(FALSE) AS round FROM SingleItem",
+            Err(EvaluateError::FunctionRequiresFloatValue(String::from("ROUND")).into()),
+        ),
+        (
+            "SELECT ROUND('string', 'string2') AS round FROM SingleItem",
+            Err(TranslateError::FunctionArgsLengthNotMatching {
+                name: "ROUND".to_owned(),
+                expected: 1,
+                found: 2,
+            }
+            .into()),
+        ),
+    ];
+
+    for (sql, expected) in test_cases.into_iter() {
+        test!(expected, sql);
+    }
+});
diff --git a/src/tests/mod.rs b/src/tests/mod.rs
index 890db14e..ac9b690c 100644
--- a/src/tests/mod.rs
+++ b/src/tests/mod.rs
@@ -60,6 +60,9 @@ macro_rules! generate_tests {
         glue!(function_trim, function::trim::trim);
         glue!(function_cast_literal, function::cast::cast_literal);
         glue!(function_cast_value, function::cast::cast_value);
+        glue!(function_ceil, function::ceil::ceil);
+        glue!(function_round, function::round::round);
+        glue!(function_floor, function::floor::floor);
         glue!(join, join::join);
         glue!(join_blend, join::blend);
         glue!(migrate, migrate::migrate);