sql_cli/sql/functions/
cast.rs

1use anyhow::{anyhow, Result};
2
3use crate::data::datatable::DataValue;
4use crate::sql::functions::{ArgCount, FunctionCategory, FunctionSignature, SqlFunction};
5
6/// The set of target types CAST supports. We deliberately collapse SQL's large
7/// "zoo" of character/numeric types down to the handful of types our engine
8/// actually stores in `DataValue`. This keeps CAST close to DuckDB's observable
9/// behaviour without dragging in fixed-width CHAR, DECIMAL precision, etc.
10#[derive(Debug, Clone, Copy, PartialEq)]
11pub enum CastTarget {
12    Integer,
13    Float,
14    Boolean,
15    /// Any character type (VARCHAR/CHAR/TEXT/STRING/…) maps here.
16    Varchar,
17    /// Any temporal type (DATE/DATETIME/TIMESTAMP/…) maps here.
18    DateTime,
19}
20
21impl CastTarget {
22    /// Map a SQL type name (already stripped of any precision/scale) onto one of
23    /// our supported target types. Returns `None` for types we cannot represent.
24    #[must_use]
25    pub fn from_name(name: &str) -> Option<Self> {
26        let upper = name.trim().to_uppercase();
27        // Collapse "DOUBLE PRECISION" and similar multi-word spellings.
28        let key = upper.split_whitespace().next().unwrap_or("");
29        match key {
30            "INT" | "INTEGER" | "INT1" | "INT2" | "INT4" | "INT8" | "TINYINT" | "SMALLINT"
31            | "BIGINT" | "HUGEINT" | "LONG" | "SHORT" | "SIGNED" | "UINTEGER" | "UBIGINT"
32            | "USMALLINT" | "UTINYINT" => Some(CastTarget::Integer),
33            "DOUBLE" | "FLOAT" | "FLOAT4" | "FLOAT8" | "REAL" | "DECIMAL" | "NUMERIC" | "DEC"
34            | "NUMBER" => Some(CastTarget::Float),
35            "BOOL" | "BOOLEAN" | "LOGICAL" => Some(CastTarget::Boolean),
36            "VARCHAR" | "CHAR" | "CHARACTER" | "TEXT" | "STRING" | "NVARCHAR" | "NCHAR"
37            | "BPCHAR" | "CLOB" => Some(CastTarget::Varchar),
38            "DATE" | "DATETIME" | "TIMESTAMP" | "TIME" => Some(CastTarget::DateTime),
39            _ => None,
40        }
41    }
42}
43
44/// Coerce a value to the requested target type. Errors describe why a value
45/// could not be represented; callers decide whether that error is fatal (CAST)
46/// or should become NULL (TRY_CAST). NULL always casts to NULL.
47pub fn cast_value(value: &DataValue, target: CastTarget) -> Result<DataValue> {
48    if matches!(value, DataValue::Null) {
49        return Ok(DataValue::Null);
50    }
51
52    match target {
53        CastTarget::Integer => cast_to_integer(value),
54        CastTarget::Float => cast_to_float(value),
55        CastTarget::Boolean => cast_to_boolean(value),
56        CastTarget::Varchar => Ok(DataValue::String(value.to_string_optimized())),
57        CastTarget::DateTime => cast_to_datetime(value),
58    }
59}
60
61fn cast_to_integer(value: &DataValue) -> Result<DataValue> {
62    let n = match value {
63        DataValue::Integer(i) => *i,
64        // Numeric-to-int rounds to nearest (matching DuckDB, which rounds rather
65        // than truncates) using round-half-to-even so exact `.5` ties agree with
66        // DuckDB, e.g. CAST(2.5 AS INT) = 2, CAST(3.5 AS INT) = 4.
67        DataValue::Float(f) => f.round_ties_even() as i64,
68        DataValue::Boolean(b) => i64::from(*b),
69        DataValue::String(s) | DataValue::DateTime(s) => parse_integer(s)?,
70        DataValue::InternedString(s) => parse_integer(s)?,
71        other => return Err(anyhow!("cannot cast {:?} to INTEGER", other)),
72    };
73    Ok(DataValue::Integer(n))
74}
75
76fn cast_to_float(value: &DataValue) -> Result<DataValue> {
77    let f = match value {
78        DataValue::Integer(i) => *i as f64,
79        DataValue::Float(f) => *f,
80        DataValue::Boolean(b) => {
81            if *b {
82                1.0
83            } else {
84                0.0
85            }
86        }
87        DataValue::String(s) | DataValue::DateTime(s) => parse_float(s)?,
88        DataValue::InternedString(s) => parse_float(s)?,
89        other => return Err(anyhow!("cannot cast {:?} to DOUBLE", other)),
90    };
91    Ok(DataValue::Float(f))
92}
93
94fn cast_to_boolean(value: &DataValue) -> Result<DataValue> {
95    let b = match value {
96        DataValue::Boolean(b) => *b,
97        DataValue::Integer(i) => *i != 0,
98        DataValue::Float(f) => *f != 0.0,
99        DataValue::String(s) => parse_bool(s)?,
100        DataValue::InternedString(s) => parse_bool(s)?,
101        other => return Err(anyhow!("cannot cast {:?} to BOOLEAN", other)),
102    };
103    Ok(DataValue::Boolean(b))
104}
105
106fn cast_to_datetime(value: &DataValue) -> Result<DataValue> {
107    match value {
108        DataValue::DateTime(s) => Ok(DataValue::DateTime(s.clone())),
109        // We store datetimes as their ISO-8601 string form; a string is taken
110        // at face value rather than re-parsed, to stay within our type confines.
111        DataValue::String(s) => Ok(DataValue::DateTime(s.clone())),
112        DataValue::InternedString(s) => Ok(DataValue::DateTime(s.as_ref().clone())),
113        other => Err(anyhow!("cannot cast {:?} to DATE/TIMESTAMP", other)),
114    }
115}
116
117fn parse_integer(s: &str) -> Result<i64> {
118    let trimmed = s.trim();
119    trimmed
120        .parse::<i64>()
121        .map_err(|_| anyhow!("could not convert string '{}' to INTEGER", trimmed))
122}
123
124fn parse_float(s: &str) -> Result<f64> {
125    let trimmed = s.trim();
126    trimmed
127        .parse::<f64>()
128        .map_err(|_| anyhow!("could not convert string '{}' to DOUBLE", trimmed))
129}
130
131fn parse_bool(s: &str) -> Result<bool> {
132    match s.trim().to_ascii_lowercase().as_str() {
133        "true" | "t" | "yes" | "y" | "1" | "on" => Ok(true),
134        "false" | "f" | "no" | "n" | "0" | "off" => Ok(false),
135        other => Err(anyhow!("could not convert string '{}' to BOOLEAN", other)),
136    }
137}
138
139/// SQL `CAST(expr AS type)`.
140///
141/// Parsed into a two-argument function call: `CAST(value, 'TYPE_NAME')`, where
142/// the type name is carried as a string literal by the parser. `try_cast`
143/// distinguishes `CAST` (errors on failure) from `TRY_CAST` (yields NULL).
144pub struct CastFunction {
145    pub try_cast: bool,
146}
147
148impl SqlFunction for CastFunction {
149    fn signature(&self) -> FunctionSignature {
150        if self.try_cast {
151            FunctionSignature {
152                name: "TRY_CAST",
153                category: FunctionCategory::Conversion,
154                arg_count: ArgCount::Fixed(2),
155                description: "Cast a value to a target type, yielding NULL if the cast fails",
156                returns: "Target type",
157                examples: vec![
158                    "SELECT TRY_CAST('abc' AS INTEGER)",
159                    "SELECT TRY_CAST('42' AS INTEGER)",
160                ],
161            }
162        } else {
163            FunctionSignature {
164                name: "CAST",
165                category: FunctionCategory::Conversion,
166                arg_count: ArgCount::Fixed(2),
167                description: "Cast a value to a target type: CAST(expr AS type)",
168                returns: "Target type",
169                examples: vec![
170                    "SELECT CAST('42' AS INTEGER)",
171                    "SELECT CAST(price AS INTEGER) FROM trades",
172                    "SELECT CAST(quantity AS DOUBLE) / 2 FROM trades",
173                ],
174            }
175        }
176    }
177
178    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
179        self.validate_args(args)?;
180
181        let type_name = match &args[1] {
182            DataValue::String(s) => s.as_str(),
183            DataValue::InternedString(s) => s.as_str(),
184            other => {
185                return Err(anyhow!(
186                    "CAST target type must be a type name, got {:?}",
187                    other
188                ))
189            }
190        };
191
192        // An unknown target type is a query error, not a data error — so it
193        // fails even under TRY_CAST.
194        let target = CastTarget::from_name(type_name)
195            .ok_or_else(|| anyhow!("unsupported CAST target type: {}", type_name))?;
196
197        match cast_value(&args[0], target) {
198            Ok(v) => Ok(v),
199            Err(e) if self.try_cast => {
200                let _ = e; // swallowed: TRY_CAST turns cast failures into NULL
201                Ok(DataValue::Null)
202            }
203            Err(e) => Err(e),
204        }
205    }
206}
207
208#[cfg(test)]
209mod tests {
210    use super::*;
211
212    fn cast(value: DataValue, ty: &str) -> Result<DataValue> {
213        let func = CastFunction { try_cast: false };
214        func.evaluate(&[value, DataValue::String(ty.to_string())])
215    }
216
217    fn try_cast(value: DataValue, ty: &str) -> DataValue {
218        let func = CastFunction { try_cast: true };
219        func.evaluate(&[value, DataValue::String(ty.to_string())])
220            .unwrap()
221    }
222
223    #[test]
224    fn string_to_integer() {
225        assert_eq!(
226            cast(DataValue::String("42".into()), "INTEGER").unwrap(),
227            DataValue::Integer(42)
228        );
229    }
230
231    #[test]
232    fn float_to_integer_rounds() {
233        assert_eq!(
234            cast(DataValue::Float(2.9), "INT").unwrap(),
235            DataValue::Integer(3)
236        );
237        assert_eq!(
238            cast(DataValue::Float(-2.9), "BIGINT").unwrap(),
239            DataValue::Integer(-3)
240        );
241    }
242
243    #[test]
244    fn float_to_integer_uses_banker_rounding_like_duckdb() {
245        // Round-half-to-even, matching DuckDB's float->int cast.
246        assert_eq!(
247            cast(DataValue::Float(2.5), "INT").unwrap(),
248            DataValue::Integer(2)
249        );
250        assert_eq!(
251            cast(DataValue::Float(3.5), "INT").unwrap(),
252            DataValue::Integer(4)
253        );
254        assert_eq!(
255            cast(DataValue::Float(-2.5), "INT").unwrap(),
256            DataValue::Integer(-2)
257        );
258    }
259
260    #[test]
261    fn integer_to_double() {
262        assert_eq!(
263            cast(DataValue::Integer(5), "DOUBLE").unwrap(),
264            DataValue::Float(5.0)
265        );
266    }
267
268    #[test]
269    fn char_type_zoo_collapses_to_string() {
270        for ty in [
271            "VARCHAR",
272            "CHAR",
273            "TEXT",
274            "STRING",
275            "VARCHAR(50)".trim_end(),
276        ] {
277            let ty = ty.split('(').next().unwrap();
278            assert_eq!(
279                cast(DataValue::Integer(7), ty).unwrap(),
280                DataValue::String("7".into())
281            );
282        }
283    }
284
285    #[test]
286    fn to_boolean_variants() {
287        assert_eq!(
288            cast(DataValue::Integer(0), "BOOLEAN").unwrap(),
289            DataValue::Boolean(false)
290        );
291        assert_eq!(
292            cast(DataValue::Integer(3), "BOOL").unwrap(),
293            DataValue::Boolean(true)
294        );
295        assert_eq!(
296            cast(DataValue::String("true".into()), "BOOLEAN").unwrap(),
297            DataValue::Boolean(true)
298        );
299    }
300
301    #[test]
302    fn null_casts_to_null() {
303        assert_eq!(cast(DataValue::Null, "INTEGER").unwrap(), DataValue::Null);
304    }
305
306    #[test]
307    fn invalid_cast_errors_but_try_cast_nulls() {
308        assert!(cast(DataValue::String("abc".into()), "INTEGER").is_err());
309        assert_eq!(
310            try_cast(DataValue::String("abc".into()), "INTEGER"),
311            DataValue::Null
312        );
313    }
314
315    #[test]
316    fn unknown_target_type_errors_even_for_try_cast() {
317        let func = CastFunction { try_cast: true };
318        let r = func.evaluate(&[DataValue::Integer(1), DataValue::String("BLOB".to_string())]);
319        assert!(r.is_err());
320    }
321}
sql_cli/sql/functions/cast.rs

sql_cli/sql/functions/
cast.rs
