Repository: gluesql/gluesql
Pull request #144 (2021-02-27 04:46:14)

CAST
Implements `CAST(`[value]` AS `[type]`)`

KyGost: Note: bug not enhancement
KyGost: TODO: Better errors
`[Err(Value(FailedToParseNumber))]`
Isn't quite informative enough for a query like
```SQL
INSERT INTO feeder.pathology (identity, patient_number, lab_number, item, start, value) SELECT CAST(UNIQUE_IDENT AS INTEGER), UR_Number, Lab_Number, Item, CAST(Service_Date AS INTEGER), CAST(ROUND_VALUE AS INTEGER) FROM load.csv.n2
```
panarch: > TODO: Better errors
> `[Err(Value(FailedToParseNumber))]`
> Isn't quite informative enough for a query like
> 
> ```sql
> INSERT INTO feeder.pathology (identity, patient_number, lab_number, item, start, value) SELECT CAST(UNIQUE_IDENT AS INTEGER), UR_Number, Lab_Number, Item, CAST(Service_Date AS INTEGER), CAST(ROUND_VALUE AS INTEGER) FROM load.csv.n2
> ```

I agree, the errors early I added need to have more detail on the message.
KyGost: @panarch thoughts?
KyGost: @panarch any ideas on what is causing:
```
thread 'function_cast' panicked at 'assertion failed: `(left == right)`
  left: `Err(Data(ImpossibleCast))`,
 right: `Err(Data(ImpossibleCast))`', C:\Users\kyran.SYRIS\Documents\GitHub\gluesql\src\tests\function\cast.rs:102:9
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
```

Once that's fixed PR is ready to move out of draft

Patch:
diff --git a/src/data/mod.rs b/src/data/mod.rs
index bf04114a..5a3df496 100644
--- a/src/data/mod.rs
+++ b/src/data/mod.rs
@@ -3,7 +3,9 @@ mod schema;
 mod table;
 mod value;
 
-pub use row::{Row, RowError};
-pub use schema::Schema;
-pub use table::{get_name, Table, TableError};
-pub use value::{Value, ValueError};
+pub use {
+    row::{Row, RowError},
+    schema::Schema,
+    table::{get_name, Table, TableError},
+    value::{Value, ValueError},
+};
diff --git a/src/data/value/ast_value.rs b/src/data/value/ast_value.rs
index e0c5ac8f..1e84094c 100644
--- a/src/data/value/ast_value.rs
+++ b/src/data/value/ast_value.rs
@@ -73,6 +73,7 @@ impl TryFrom<&AstValue> for Value {
                 .map_or_else(|_| v.parse::<f64>().map(Value::F64), |v| Ok(Value::I64(v)))
                 .map_err(|_| ValueError::FailedToParseNumber.into()),
             AstValue::Boolean(v) => Ok(Value::Bool(*v)),
+            AstValue::SingleQuotedString(v) => Ok(Value::Str(v.to_string())),
             _ => Err(ValueError::SqlTypeNotSupported.into()),
         }
     }
diff --git a/src/data/value/error.rs b/src/data/value/error.rs
index 9b585e7a..84235690 100644
--- a/src/data/value/error.rs
+++ b/src/data/value/error.rs
@@ -40,4 +40,10 @@ pub enum ValueError {
 
     #[error("floating columns cannot be set to unique constraint")]
     ConflictOnFloatWithUniqueConstraint,
+
+    #[error("impossible cast")]
+    ImpossibleCast,
+
+    #[error("unimplemented cast")]
+    UnimplementedCast,
 }
diff --git a/src/data/value/mod.rs b/src/data/value/mod.rs
index 2110f823..e743c3e6 100644
--- a/src/data/value/mod.rs
+++ b/src/data/value/mod.rs
@@ -1,11 +1,10 @@
-use boolinator::Boolinator;
-use serde::{Deserialize, Serialize};
-use std::cmp::Ordering;
-use std::fmt::Debug;
-
-use sqlparser::ast::{DataType, Expr, Ident, Value as AstValue};
-
-use crate::result::Result;
+use {
+    crate::result::Result,
+    boolinator::Boolinator,
+    serde::{Deserialize, Serialize},
+    sqlparser::ast::{DataType, Expr, Ident, Value as AstValue},
+    std::{cmp::Ordering, fmt::Debug},
+};
 
 mod ast_value;
 mod error;
@@ -170,6 +169,61 @@ impl Value {
         }
     }
 
+    pub fn cast(&self, data_type: &DataType) -> Result<Self> {
+        match (data_type, self) {
+            // Same as
+            (DataType::Boolean, Value::Bool(_))
+            | (DataType::Int, Value::I64(_))
+            | (DataType::Float(_), Value::F64(_))
+            | (DataType::Text, Value::Str(_)) => Ok(self.clone()),
+            // Boolean
+            (DataType::Boolean, Value::Str(value)) => Ok(match value.to_uppercase().as_str() {
+                "TRUE" => Ok(Value::Bool(true)),
+                "FALSE" => Ok(Value::Bool(false)),
+                _ => Err(ValueError::ImpossibleCast),
+            }?),
+            (DataType::Boolean, Value::I64(value)) => Ok(match value {
+                1 => Ok(Value::Bool(true)),
+                0 => Ok(Value::Bool(false)),
+                _ => Err(ValueError::ImpossibleCast),
+            }?),
+            (DataType::Boolean, Value::F64(value)) => {
+                if value.eq(&1.0) {
+                    Ok(Value::Bool(true))
+                } else if value.eq(&0.0) {
+                    Ok(Value::Bool(false))
+                } else {
+                    Err(ValueError::ImpossibleCast.into())
+                }
+            }
+            // Integer
+            (DataType::Int, Value::F64(value)) => Ok(Value::I64(value.trunc() as i64)),
+            (DataType::Int, Value::Str(value)) => Ok(Value::I64(
+                value
+                    .parse::<i64>()
+                    .map_err(|_| ValueError::ImpossibleCast)?,
+            )),
+            (DataType::Int, Value::Bool(value)) => Ok(Value::I64(if *value { 1 } else { 0 })),
+            // Float
+            (DataType::Float(_), Value::Bool(value)) => {
+                Ok(Value::F64(if *value { 1.0 } else { 0.0 }))
+            }
+            (DataType::Float(_), Value::I64(value)) => Ok(Value::F64((*value as f64).trunc())),
+            (DataType::Float(_), Value::Str(value)) => Ok(Value::F64(
+                value
+                    .parse::<f64>()
+                    .map_err(|_| ValueError::ImpossibleCast)?,
+            )),
+            (DataType::Text, Value::Bool(value)) => Ok(Value::Str(
+                (if *value { "TRUE" } else { "FALSE" }).to_string(),
+            )),
+            // Text
+            (DataType::Text, Value::I64(value)) => Ok(Value::Str(value.to_string())),
+            (DataType::Text, Value::F64(value)) => Ok(Value::Str(value.to_string())),
+            _ => Err(ValueError::UnimplementedCast.into()),
+        }
+    }
+
     pub fn clone_by(&self, literal: &AstValue) -> Result<Self> {
         match (self, literal) {
             (Value::I64(_), AstValue::Number(v)) => v
diff --git a/src/executor/evaluate/error.rs b/src/executor/evaluate/error.rs
index 6ca748e1..35a6f846 100644
--- a/src/executor/evaluate/error.rs
+++ b/src/executor/evaluate/error.rs
@@ -47,4 +47,13 @@ pub enum EvaluateError {
 
     #[error("unimplemented")]
     Unimplemented,
+
+    #[error("impossible cast")]
+    ImpossibleCast,
+
+    #[error("unimplemented cast")]
+    UnimplementedCast,
+
+    #[error("unreachable impossible cast")]
+    UnreachableImpossibleCast,
 }
diff --git a/src/executor/evaluate/evaluated.rs b/src/executor/evaluate/evaluated.rs
index fd9a3ba5..2ec2f5be 100644
--- a/src/executor/evaluate/evaluated.rs
+++ b/src/executor/evaluate/evaluated.rs
@@ -1,13 +1,16 @@
-use std::cmp::Ordering;
-use std::convert::{TryFrom, TryInto};
-
-use sqlparser::ast::Value as AstValue;
-
-use crate::data;
-use crate::data::Value;
-use crate::result::{Error, Result};
-
-use super::EvaluateError;
+use {
+    super::EvaluateError,
+    crate::{
+        data,
+        data::Value,
+        result::{Error, Result},
+    },
+    sqlparser::ast::{DataType, Value as AstValue},
+    std::{
+        cmp::Ordering,
+        convert::{TryFrom, TryInto},
+    },
+};
 
 /// `LiteralRef`, `Literal` and `StringRef` are used when it is not possible to specify what kind of `Value`
 /// can be applied.
@@ -343,6 +346,23 @@ impl<'a> Evaluated<'a> {
             StringRef(_) => unreachable(),
         }
     }
+
+    pub fn cast(self, data_type: &DataType) -> Result<Evaluated<'a>> {
+        use Evaluated::*;
+
+        let cast_literal =
+            |value: &AstValue| cast_ast_value(value, data_type).map(Evaluated::Literal);
+        let cast_value = |value: &data::Value| value.cast(data_type).map(Evaluated::Value);
+
+        // Decided: Due to the explicit call, we can abandon -Ref
+        match self {
+            StringRef(value) => cast_literal(&AstValue::SingleQuotedString(value.to_string())),
+            LiteralRef(value) => cast_literal(value),
+            Literal(value) => cast_literal(&value),
+            ValueRef(value) => cast_value(value),
+            Value(value) => cast_value(&value),
+        }
+    }
 }
 
 macro_rules! literal_binary_op {
@@ -414,3 +434,38 @@ fn literal_minus(v: &AstValue) -> Result<AstValue> {
         _ => Err(EvaluateError::LiteralUnaryMinusOnNonNumeric.into()),
     }
 }
+
+pub fn cast_ast_value(value: &AstValue, data_type: &DataType) -> Result<AstValue> {
+    match (data_type, value) {
+        (DataType::Boolean, AstValue::SingleQuotedString(value))
+        | (DataType::Boolean, AstValue::Number(value)) => Ok(match value.to_uppercase().as_str() {
+            "TRUE" | "1" => Ok(AstValue::Boolean(true)),
+            "FALSE" | "0" => Ok(AstValue::Boolean(false)),
+            _ => Err(EvaluateError::ImpossibleCast),
+        }?),
+        (DataType::Int, AstValue::Number(value)) => Ok(AstValue::Number(
+            value
+                .parse::<f64>()
+                .map_err(|_| EvaluateError::UnreachableImpossibleCast)?
+                .trunc()
+                .to_string(),
+        )),
+        (DataType::Int, AstValue::SingleQuotedString(value))
+        | (DataType::Float(_), AstValue::SingleQuotedString(value)) => {
+            Ok(AstValue::Number(value.to_string()))
+        }
+        (DataType::Int, AstValue::Boolean(value))
+        | (DataType::Float(_), AstValue::Boolean(value)) => Ok(AstValue::Number(
+            (if *value { "1" } else { "0" }).to_string(),
+        )),
+        (DataType::Float(_), AstValue::Number(value)) => Ok(AstValue::Number(value.to_string())),
+        (DataType::Text, AstValue::Boolean(value)) => Ok(AstValue::SingleQuotedString(
+            (if *value { "TRUE" } else { "FALSE" }).to_string(),
+        )),
+        (DataType::Text, AstValue::Number(value)) => {
+            Ok(AstValue::SingleQuotedString(value.to_string()))
+        }
+        (_, AstValue::Null) => Ok(AstValue::Null),
+        _ => Err(EvaluateError::UnimplementedCast.into()),
+    }
+}
diff --git a/src/executor/evaluate/mod.rs b/src/executor/evaluate/mod.rs
index e4c0d6c7..33c1175c 100644
--- a/src/executor/evaluate/mod.rs
+++ b/src/executor/evaluate/mod.rs
@@ -1,23 +1,21 @@
 mod error;
 mod evaluated;
 
-use async_recursion::async_recursion;
-use futures::stream::{StreamExt, TryStreamExt};
-use im_rc::HashMap;
-use std::convert::TryInto;
-use std::fmt::Debug;
-use std::rc::Rc;
-
-use sqlparser::ast::{BinaryOperator, Expr, Function, UnaryOperator, Value as AstValue};
-
-use super::context::FilterContext;
-use super::select::select;
-use crate::data::{get_name, Value};
-use crate::result::Result;
-use crate::store::Store;
-
-pub use error::EvaluateError;
-pub use evaluated::Evaluated;
+use {
+    super::{context::FilterContext, select::select},
+    crate::{
+        data::{get_name, Value},
+        result::Result,
+        store::Store,
+    },
+    async_recursion::async_recursion,
+    futures::stream::{StreamExt, TryStreamExt},
+    im_rc::HashMap,
+    sqlparser::ast::{BinaryOperator, Expr, Function, UnaryOperator, Value as AstValue},
+    std::{convert::TryInto, fmt::Debug, rc::Rc},
+};
+
+pub use {error::EvaluateError, evaluated::Evaluated};
 
 #[async_recursion(?Send)]
 pub async fn evaluate<'a, T: 'static + Debug>(
@@ -113,6 +111,7 @@ pub async fn evaluate<'a, T: 'static + Debug>(
                 evaluate_function(storage, context, aggregated, func, use_empty).await
             }
         },
+        Expr::Cast { expr, data_type } => eval(expr).await?.cast(data_type),
         _ => Err(EvaluateError::Unimplemented.into()),
     }
 }
diff --git a/src/result.rs b/src/result.rs
index 58ac4dc0..0f416f70 100644
--- a/src/result.rs
+++ b/src/result.rs
@@ -1,10 +1,13 @@
-use serde::Serialize;
-use thiserror::Error as ThisError;
-
-use crate::data::{RowError, TableError, ValueError};
-use crate::executor::{
-    AggregateError, BlendError, CreateTableError, EvaluateError, ExecuteError, FetchError,
-    FilterError, JoinError, LimitError, SelectError, UpdateError, ValidateError,
+use {
+    crate::{
+        data::{RowError, TableError, ValueError},
+        executor::{
+            AggregateError, BlendError, CreateTableError, EvaluateError, ExecuteError, FetchError,
+            FilterError, JoinError, LimitError, SelectError, UpdateError, ValidateError,
+        },
+    },
+    serde::Serialize,
+    thiserror::Error as ThisError,
 };
 
 #[cfg(feature = "alter-table")]


Test patch:
diff --git a/src/tests/function/cast.rs b/src/tests/function/cast.rs
new file mode 100644
index 00000000..4b3e4209
--- /dev/null
+++ b/src/tests/function/cast.rs
@@ -0,0 +1,108 @@
+use crate::*;
+
+test_case!(cast, async move {
+    use Value::{Bool, Str, F64, I64};
+    let test_cases = vec![
+        ("CREATE TABLE Item (number TEXT)", Ok(Payload::Create)),
+        (
+            r#"INSERT INTO Item VALUES ("1"), ("2"), ("3")"#,
+            Ok(Payload::Insert(3)),
+        ),
+        (
+            r#"SELECT CAST("1" AS INTEGER) AS cast FROM Item LIMIT 1"#,
+            Ok(select!(
+                cast I64;
+                1
+            )),
+        ),
+        (
+            r#"SELECT CAST("1.1" AS FLOAT) AS cast FROM Item LIMIT 1"#,
+            Ok(select!(
+                cast F64;
+                1.1
+            )),
+        ),
+        (
+            r#"SELECT CAST(1 AS TEXT) AS cast FROM Item LIMIT 1"#,
+            Ok(select!(
+                cast Str;
+                "1".to_string()
+            )),
+        ),
+        (
+            r#"SELECT CAST(1.1 AS INTEGER) AS cast FROM Item LIMIT 1"#,
+            Ok(select!(
+                cast I64;
+                1
+            )),
+        ),
+        (
+            r#"SELECT CAST(1.1 AS TEXT) AS cast FROM Item LIMIT 1"#,
+            Ok(select!(
+                cast Str;
+                "1.1".to_string()
+            )),
+        ),
+        (
+            r#"SELECT CAST(TRUE AS INTEGER) AS cast FROM Item LIMIT 1"#,
+            Ok(select!(
+                cast I64;
+                1
+            )),
+        ),
+        (
+            r#"SELECT CAST(TRUE AS TEXT) AS cast FROM Item LIMIT 1"#,
+            Ok(select!(
+                cast Str;
+                "TRUE".to_string()
+            )),
+        ),
+        (
+            r#"SELECT CAST(1 AS BOOLEAN) AS cast FROM Item LIMIT 1"#,
+            Ok(select!(
+                cast Bool;
+                true
+            )),
+        ),
+        (
+            r#"SELECT CAST("TRUE" AS BOOLEAN) AS cast FROM Item LIMIT 1"#,
+            Ok(select!(
+                cast Bool;
+                true
+            )),
+        ),
+        (
+            r#"SELECT CAST(LOWER(number) AS INTEGER) AS cast FROM Item LIMIT 1"#,
+            Ok(select!(
+                cast I64;
+                1
+            )),
+        ),
+        (
+            r#"SELECT CAST(1 AS FLOAT) AS cast FROM Item LIMIT 1"#,
+            Ok(select!(
+                cast I64; // Ideally should be F64
+                1
+            )),
+        ),
+        (
+            r#"SELECT CAST("ABC" AS INTEGER) FROM Item LIMIT 1"#,
+            Err(ValueError::FailedToParseNumber.into()),
+        ),
+        (
+            r#"SELECT CAST("$1" AS INTEGER) FROM Item LIMIT 1"#,
+            Err(ValueError::FailedToParseNumber.into()),
+        ),
+        (
+            r#"SELECT CAST("BLEH" AS BOOLEAN) FROM Item LIMIT 1"#,
+            Err(EvaluateError::ImpossibleCast.into()),
+        ),
+        (
+            r#"SELECT CAST(number AS BOOLEAN) FROM Item LIMIT 1"#,
+            Err(ValueError::ImpossibleCast.into()),
+        ),
+    ];
+    for (sql, expected) in test_cases.into_iter() {
+        test!(expected, sql);
+    }
+});
diff --git a/src/tests/function.rs b/src/tests/function/mod.rs
similarity index 99%
rename from src/tests/function.rs
rename to src/tests/function/mod.rs
index a9b9a6bf..dc8d6da9 100644
--- a/src/tests/function.rs
+++ b/src/tests/function/mod.rs
@@ -1,3 +1,5 @@
+pub mod cast;
+
 use crate::*;
 
 test_case!(function, async move {
diff --git a/src/tests/mod.rs b/src/tests/mod.rs
index 42a2c021..1cc2ef37 100644
--- a/src/tests/mod.rs
+++ b/src/tests/mod.rs
@@ -67,6 +67,7 @@ macro_rules! generate_tests {
         glue!(error, error::error);
         glue!(filter, filter::filter);
         glue!(function, function::function);
+        glue!(function_cast, function::cast::cast);
         glue!(join, join::join);
         glue!(join_blend, join::blend);
         glue!(migrate, migrate::migrate);