common_sql/
convert.rs

1// Copyright 2023 Greptime Team
2//
3// Licensed under the Apache License, Version 2.0 (the "License");
4// you may not use this file except in compliance with the License.
5// You may obtain a copy of the License at
6//
7//     http://www.apache.org/licenses/LICENSE-2.0
8//
9// Unless required by applicable law or agreed to in writing, software
10// distributed under the License is distributed on an "AS IS" BASIS,
11// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12// See the License for the specific language governing permissions and
13// limitations under the License.
14
15use std::str::FromStr;
16
17use arrow_schema::extension::ExtensionType;
18use common_time::Timestamp;
19use common_time::timezone::Timezone;
20use datatypes::extension::json::{Json2ExtensionType, parse_legacy_json2_settings};
21use datatypes::prelude::ConcreteDataType;
22use datatypes::schema::{ColumnDefaultConstraint, ColumnSchema};
23use datatypes::types::{JsonFormat, parse_string_to_jsonb, parse_string_to_vector_type_value};
24use datatypes::value::{OrderedF32, OrderedF64, Value};
25use snafu::{OptionExt, ResultExt, ensure};
26pub use sqlparser::ast::{
27    BinaryOperator, ColumnDef, ColumnOption, ColumnOptionDef, DataType, Expr, Function,
28    FunctionArg, FunctionArgExpr, FunctionArguments, Ident, ObjectName, SqlOption, TableConstraint,
29    TimezoneInfo, UnaryOperator, Value as SqlValue, Visit, VisitMut, Visitor, VisitorMut,
30    visit_expressions_mut, visit_statements_mut,
31};
32
33use crate::error::{
34    ColumnTypeMismatchSnafu, ConvertSqlValueSnafu, ConvertStrSnafu, DatatypeSnafu,
35    DeserializeSnafu, InvalidCastSnafu, InvalidSqlValueSnafu, InvalidUnaryOpSnafu,
36    ParseSqlValueSnafu, Result, TimestampOverflowSnafu, UnsupportedUnaryOpSnafu,
37};
38
39fn parse_sql_number<R: FromStr + std::fmt::Debug>(n: &str) -> Result<R>
40where
41    <R as FromStr>::Err: std::fmt::Debug,
42{
43    match n.parse::<R>() {
44        Ok(n) => Ok(n),
45        Err(e) => ParseSqlValueSnafu {
46            msg: format!("Fail to parse number {n}, {e:?}"),
47        }
48        .fail(),
49    }
50}
51
52macro_rules! parse_number_to_value {
53    ($data_type: expr, $n: ident,  $(($Type: ident, $PrimitiveType: ident, $Target: ident)), +) => {
54        match $data_type {
55            $(
56                ConcreteDataType::$Type(_) => {
57                    let n  = parse_sql_number::<$PrimitiveType>($n)?;
58                    Ok(Value::$Type($Target::from(n)))
59                },
60            )+
61            ConcreteDataType::Timestamp(t) => {
62                let n = parse_sql_number::<i64>($n)?;
63                let timestamp = Timestamp::new(n, t.unit());
64
65                // Check if the value is within the valid range for the target unit
66                if Timestamp::is_overflow(n, t.unit()) {
67                    return TimestampOverflowSnafu {
68                        timestamp,
69                        target_unit: t.unit(),
70                    }.fail();
71                }
72
73                Ok(Value::Timestamp(timestamp))
74            },
75            // TODO(QuenKar): This could need to be optimized
76            // if this from_str function is slow,
77            // we can implement parse decimal string with precision and scale manually.
78            ConcreteDataType::Decimal128(_) => {
79                if let Ok(val) = common_decimal::Decimal128::from_str($n) {
80                    Ok(Value::Decimal128(val))
81                } else {
82                    ParseSqlValueSnafu {
83                        msg: format!("Fail to parse number {}, invalid column type: {:?}",
84                                        $n, $data_type)
85                    }.fail()
86                }
87            }
88            // It's valid for MySQL JDBC to send "0" and "1" for boolean types, so adapt to that.
89            ConcreteDataType::Boolean(_) => {
90                match $n {
91                    "0" => Ok(Value::Boolean(false)),
92                    "1" => Ok(Value::Boolean(true)),
93                    _ => ParseSqlValueSnafu {
94                        msg: format!("Failed to parse number '{}' to boolean column type", $n)}.fail(),
95                }
96            }
97            _ => ParseSqlValueSnafu {
98                msg: format!("Fail to parse number {}, invalid column type: {:?}",
99                                $n, $data_type
100                )}.fail(),
101        }
102    }
103}
104
105/// Convert a sql value into datatype's value
106pub(crate) fn sql_number_to_value(data_type: &ConcreteDataType, n: &str) -> Result<Value> {
107    parse_number_to_value!(
108        data_type,
109        n,
110        (UInt8, u8, u8),
111        (UInt16, u16, u16),
112        (UInt32, u32, u32),
113        (UInt64, u64, u64),
114        (Int8, i8, i8),
115        (Int16, i16, i16),
116        (Int32, i32, i32),
117        (Int64, i64, i64),
118        (Float64, f64, OrderedF64),
119        (Float32, f32, OrderedF32)
120    )
121    // TODO(hl): also Date/DateTime
122}
123
124/// Converts SQL value to value according to the data type.
125/// If `auto_string_to_numeric` is true, tries to cast the string value to numeric values,
126/// and returns error if the cast fails.
127pub fn sql_value_to_value(
128    column_schema: &ColumnSchema,
129    sql_val: &SqlValue,
130    timezone: Option<&Timezone>,
131    unary_op: Option<UnaryOperator>,
132    auto_string_to_numeric: bool,
133) -> Result<Value> {
134    let column_name = &column_schema.name;
135    let data_type = &column_schema.data_type;
136    let mut value = match sql_val {
137        SqlValue::Number(n, _) => sql_number_to_value(data_type, n)?,
138        SqlValue::Null => Value::Null,
139        SqlValue::Boolean(b) => {
140            ensure!(
141                data_type.is_boolean(),
142                ColumnTypeMismatchSnafu {
143                    column_name,
144                    expect: data_type.clone(),
145                    actual: ConcreteDataType::boolean_datatype(),
146                }
147            );
148
149            (*b).into()
150        }
151        SqlValue::DoubleQuotedString(s) | SqlValue::SingleQuotedString(s) => {
152            parse_string_to_value(column_schema, s.clone(), timezone, auto_string_to_numeric)?
153        }
154        SqlValue::HexStringLiteral(s) => {
155            // Should not directly write binary into json column
156            ensure!(
157                !matches!(data_type, ConcreteDataType::Json(_)),
158                ColumnTypeMismatchSnafu {
159                    column_name,
160                    expect: ConcreteDataType::binary_datatype(),
161                    actual: ConcreteDataType::json_datatype(),
162                }
163            );
164
165            parse_hex_string(s)?
166        }
167        SqlValue::Placeholder(s) => return InvalidSqlValueSnafu { value: s }.fail(),
168
169        // TODO(dennis): supports binary string
170        _ => {
171            return ConvertSqlValueSnafu {
172                value: sql_val.clone(),
173                datatype: data_type.clone(),
174            }
175            .fail();
176        }
177    };
178
179    if let Some(unary_op) = unary_op {
180        match unary_op {
181            UnaryOperator::Plus | UnaryOperator::Minus | UnaryOperator::Not => {}
182            _ => {
183                return UnsupportedUnaryOpSnafu { unary_op }.fail();
184            }
185        }
186
187        match value {
188            Value::Null => {}
189            Value::Boolean(bool) => match unary_op {
190                UnaryOperator::Not => value = Value::Boolean(!bool),
191                _ => {
192                    return InvalidUnaryOpSnafu { unary_op, value }.fail();
193                }
194            },
195            Value::UInt8(_)
196            | Value::UInt16(_)
197            | Value::UInt32(_)
198            | Value::UInt64(_)
199            | Value::Int8(_)
200            | Value::Int16(_)
201            | Value::Int32(_)
202            | Value::Int64(_)
203            | Value::Float32(_)
204            | Value::Float64(_)
205            | Value::Decimal128(_)
206            | Value::Date(_)
207            | Value::Timestamp(_)
208            | Value::Time(_)
209            | Value::Duration(_)
210            | Value::IntervalYearMonth(_)
211            | Value::IntervalDayTime(_)
212            | Value::IntervalMonthDayNano(_) => match unary_op {
213                UnaryOperator::Plus => {}
214                UnaryOperator::Minus => {
215                    value = value
216                        .try_negative()
217                        .with_context(|| InvalidUnaryOpSnafu { unary_op, value })?;
218                }
219                _ => return InvalidUnaryOpSnafu { unary_op, value }.fail(),
220            },
221
222            Value::String(_)
223            | Value::Binary(_)
224            | Value::List(_)
225            | Value::Struct(_)
226            | Value::Json(_) => {
227                return InvalidUnaryOpSnafu { unary_op, value }.fail();
228            }
229        }
230    }
231
232    let value_datatype = value.data_type();
233    // The datatype of json value is determined by its actual data, so we can't simply "cast" it here.
234    if value_datatype.is_json() || value_datatype == *data_type {
235        Ok(value)
236    } else {
237        datatypes::types::cast(value, data_type).with_context(|_| InvalidCastSnafu {
238            sql_value: sql_val.clone(),
239            datatype: data_type,
240        })
241    }
242}
243
244pub(crate) fn parse_string_to_value(
245    column_schema: &ColumnSchema,
246    s: String,
247    timezone: Option<&Timezone>,
248    auto_string_to_numeric: bool,
249) -> Result<Value> {
250    let data_type = &column_schema.data_type;
251    if auto_string_to_numeric && let Some(value) = auto_cast_to_numeric(&s, data_type)? {
252        return Ok(value);
253    }
254
255    ensure!(
256        data_type.is_stringifiable(),
257        ColumnTypeMismatchSnafu {
258            column_name: column_schema.name.clone(),
259            expect: data_type.clone(),
260            actual: ConcreteDataType::string_datatype(),
261        }
262    );
263
264    match data_type {
265        ConcreteDataType::String(_) => Ok(Value::String(s.into())),
266        ConcreteDataType::Date(_) => {
267            if let Ok(date) = common_time::date::Date::from_str(&s, timezone) {
268                Ok(Value::Date(date))
269            } else {
270                ParseSqlValueSnafu {
271                    msg: format!("Failed to parse {s} to Date value"),
272                }
273                .fail()
274            }
275        }
276        ConcreteDataType::Timestamp(t) => {
277            if let Ok(ts) = Timestamp::from_str(&s, timezone) {
278                Ok(Value::Timestamp(ts.convert_to(t.unit()).context(
279                    TimestampOverflowSnafu {
280                        timestamp: ts,
281                        target_unit: t.unit(),
282                    },
283                )?))
284            } else if let Ok(ts) = i64::from_str(s.as_str()) {
285                Ok(Value::Timestamp(Timestamp::new(ts, t.unit())))
286            } else {
287                ParseSqlValueSnafu {
288                    msg: format!("Failed to parse {s} to Timestamp value"),
289                }
290                .fail()
291            }
292        }
293        ConcreteDataType::Decimal128(_) => {
294            if let Ok(val) = common_decimal::Decimal128::from_str(&s) {
295                Ok(Value::Decimal128(val))
296            } else {
297                ParseSqlValueSnafu {
298                    msg: format!("Fail to parse number {s} to Decimal128 value"),
299                }
300                .fail()
301            }
302        }
303        ConcreteDataType::Binary(_) => Ok(Value::Binary(s.as_bytes().into())),
304        ConcreteDataType::Json(j) => match &j.format {
305            JsonFormat::Jsonb => {
306                let v = parse_string_to_jsonb(&s).context(DatatypeSnafu)?;
307                Ok(Value::Binary(v.into()))
308            }
309            JsonFormat::Json2(_) => {
310                let v = serde_json::from_str(&s).context(DeserializeSnafu { json: s })?;
311
312                if let Some(extension) = column_schema
313                    .extension_type::<Json2ExtensionType>()
314                    .context(DatatypeSnafu)?
315                {
316                    extension.metadata().json_settings().encode(v)
317                } else {
318                    parse_legacy_json2_settings(column_schema.metadata())
319                        .context(DatatypeSnafu)?
320                        .unwrap_or_default()
321                        .encode(v)
322                }
323                .context(DatatypeSnafu)
324            }
325        },
326        ConcreteDataType::Vector(d) => {
327            let v = parse_string_to_vector_type_value(&s, Some(d.dim)).context(DatatypeSnafu)?;
328            Ok(Value::Binary(v.into()))
329        }
330        _ => ParseSqlValueSnafu {
331            msg: format!("Failed to parse {s} to {data_type} value"),
332        }
333        .fail(),
334    }
335}
336
337/// Casts string to value of specified numeric data type.
338/// If the string cannot be parsed, returns an error.
339///
340/// Returns None if the data type doesn't support auto casting.
341pub(crate) fn auto_cast_to_numeric(s: &str, data_type: &ConcreteDataType) -> Result<Option<Value>> {
342    let value = match data_type {
343        ConcreteDataType::Boolean(_) => s.parse::<bool>().map(Value::Boolean).ok(),
344        ConcreteDataType::Int8(_) => s.parse::<i8>().map(Value::Int8).ok(),
345        ConcreteDataType::Int16(_) => s.parse::<i16>().map(Value::Int16).ok(),
346        ConcreteDataType::Int32(_) => s.parse::<i32>().map(Value::Int32).ok(),
347        ConcreteDataType::Int64(_) => s.parse::<i64>().map(Value::Int64).ok(),
348        ConcreteDataType::UInt8(_) => s.parse::<u8>().map(Value::UInt8).ok(),
349        ConcreteDataType::UInt16(_) => s.parse::<u16>().map(Value::UInt16).ok(),
350        ConcreteDataType::UInt32(_) => s.parse::<u32>().map(Value::UInt32).ok(),
351        ConcreteDataType::UInt64(_) => s.parse::<u64>().map(Value::UInt64).ok(),
352        ConcreteDataType::Float32(_) => s
353            .parse::<f32>()
354            .map(|v| Value::Float32(OrderedF32::from(v)))
355            .ok(),
356        ConcreteDataType::Float64(_) => s
357            .parse::<f64>()
358            .map(|v| Value::Float64(OrderedF64::from(v)))
359            .ok(),
360        _ => return Ok(None),
361    };
362
363    match value {
364        Some(value) => Ok(Some(value)),
365        None => ConvertStrSnafu {
366            value: s,
367            datatype: data_type.clone(),
368        }
369        .fail(),
370    }
371}
372
373pub(crate) fn parse_hex_string(s: &str) -> Result<Value> {
374    match hex::decode(s) {
375        Ok(b) => Ok(Value::Binary(common_base::bytes::Bytes::from(b))),
376        Err(hex::FromHexError::InvalidHexCharacter { c, index }) => ParseSqlValueSnafu {
377            msg: format!(
378                "Fail to parse hex string to Byte: invalid character {c:?} at position {index}"
379            ),
380        }
381        .fail(),
382        Err(hex::FromHexError::OddLength) => ParseSqlValueSnafu {
383            msg: "Fail to parse hex string to Byte: odd number of digits".to_string(),
384        }
385        .fail(),
386        Err(e) => ParseSqlValueSnafu {
387            msg: format!("Fail to parse hex string to Byte {s}, {e:?}"),
388        }
389        .fail(),
390    }
391}
392
393/// Deserialize default constraint from json bytes
394pub fn deserialize_default_constraint(
395    bytes: &[u8],
396    column_name: &str,
397    data_type: &ConcreteDataType,
398) -> Result<Option<ColumnDefaultConstraint>> {
399    let json = String::from_utf8_lossy(bytes);
400    let default_constraint = serde_json::from_str(&json).context(DeserializeSnafu { json })?;
401    let column_def = sqlparser::ast::ColumnOptionDef {
402        name: None,
403        option: sqlparser::ast::ColumnOption::Default(default_constraint),
404    };
405
406    crate::default_constraint::parse_column_default_constraint(
407        column_name,
408        data_type,
409        &[column_def],
410        None,
411    )
412}
413
414#[cfg(test)]
415mod test {
416    use common_base::bytes::Bytes;
417    use common_time::timestamp::TimeUnit;
418    use datatypes::types::TimestampType;
419    use datatypes::value::OrderedFloat;
420
421    use super::*;
422
423    macro_rules! call_parse_string_to_value {
424        ($column_name: expr, $input: expr, $data_type: expr) => {
425            call_parse_string_to_value!($column_name, $input, $data_type, None)
426        };
427        ($column_name: expr, $input: expr, $data_type: expr, timezone = $timezone: expr) => {
428            call_parse_string_to_value!($column_name, $input, $data_type, Some($timezone))
429        };
430        ($column_name: expr, $input: expr, $data_type: expr, $timezone: expr) => {{
431            let column_schema = ColumnSchema::new($column_name, $data_type, true);
432            parse_string_to_value(&column_schema, $input, $timezone, true)
433        }};
434    }
435
436    #[test]
437    fn test_string_to_value_auto_numeric() -> Result<()> {
438        // Test string to boolean with auto cast
439        let result = call_parse_string_to_value!(
440            "col",
441            "true".to_string(),
442            ConcreteDataType::boolean_datatype()
443        )?;
444        assert_eq!(Value::Boolean(true), result);
445
446        // Test invalid string to boolean with auto cast
447        let result = call_parse_string_to_value!(
448            "col",
449            "not_a_boolean".to_string(),
450            ConcreteDataType::boolean_datatype()
451        );
452        assert!(result.is_err());
453
454        // Test string to int8
455        let result = call_parse_string_to_value!(
456            "col",
457            "42".to_string(),
458            ConcreteDataType::int8_datatype()
459        )?;
460        assert_eq!(Value::Int8(42), result);
461
462        // Test invalid string to int8 with auto cast
463        let result = call_parse_string_to_value!(
464            "col",
465            "not_an_int8".to_string(),
466            ConcreteDataType::int8_datatype()
467        );
468        assert!(result.is_err());
469
470        // Test string to int16
471        let result = call_parse_string_to_value!(
472            "col",
473            "1000".to_string(),
474            ConcreteDataType::int16_datatype()
475        )?;
476        assert_eq!(Value::Int16(1000), result);
477
478        // Test invalid string to int16 with auto cast
479        let result = call_parse_string_to_value!(
480            "col",
481            "not_an_int16".to_string(),
482            ConcreteDataType::int16_datatype()
483        );
484        assert!(result.is_err());
485
486        // Test string to int32
487        let result = call_parse_string_to_value!(
488            "col",
489            "100000".to_string(),
490            ConcreteDataType::int32_datatype()
491        )?;
492        assert_eq!(Value::Int32(100000), result);
493
494        // Test invalid string to int32 with auto cast
495        let result = call_parse_string_to_value!(
496            "col",
497            "not_an_int32".to_string(),
498            ConcreteDataType::int32_datatype()
499        );
500        assert!(result.is_err());
501
502        // Test string to int64
503        let result = call_parse_string_to_value!(
504            "col",
505            "1000000".to_string(),
506            ConcreteDataType::int64_datatype()
507        )?;
508        assert_eq!(Value::Int64(1000000), result);
509
510        // Test invalid string to int64 with auto cast
511        let result = call_parse_string_to_value!(
512            "col",
513            "not_an_int64".to_string(),
514            ConcreteDataType::int64_datatype()
515        );
516        assert!(result.is_err());
517
518        // Test string to uint8
519        let result = call_parse_string_to_value!(
520            "col",
521            "200".to_string(),
522            ConcreteDataType::uint8_datatype()
523        )?;
524        assert_eq!(Value::UInt8(200), result);
525
526        // Test invalid string to uint8 with auto cast
527        let result = call_parse_string_to_value!(
528            "col",
529            "not_a_uint8".to_string(),
530            ConcreteDataType::uint8_datatype()
531        );
532        assert!(result.is_err());
533
534        // Test string to uint16
535        let result = call_parse_string_to_value!(
536            "col",
537            "60000".to_string(),
538            ConcreteDataType::uint16_datatype()
539        )?;
540        assert_eq!(Value::UInt16(60000), result);
541
542        // Test invalid string to uint16 with auto cast
543        let result = call_parse_string_to_value!(
544            "col",
545            "not_a_uint16".to_string(),
546            ConcreteDataType::uint16_datatype()
547        );
548        assert!(result.is_err());
549
550        // Test string to uint32
551        let result = call_parse_string_to_value!(
552            "col",
553            "4000000000".to_string(),
554            ConcreteDataType::uint32_datatype()
555        )?;
556        assert_eq!(Value::UInt32(4000000000), result);
557
558        // Test invalid string to uint32 with auto cast
559        let result = call_parse_string_to_value!(
560            "col",
561            "not_a_uint32".to_string(),
562            ConcreteDataType::uint32_datatype()
563        );
564        assert!(result.is_err());
565
566        // Test string to uint64
567        let result = call_parse_string_to_value!(
568            "col",
569            "18446744073709551615".to_string(),
570            ConcreteDataType::uint64_datatype()
571        )?;
572        assert_eq!(Value::UInt64(18446744073709551615), result);
573
574        // Test invalid string to uint64 with auto cast
575        let result = call_parse_string_to_value!(
576            "col",
577            "not_a_uint64".to_string(),
578            ConcreteDataType::uint64_datatype()
579        );
580        assert!(result.is_err());
581
582        // Test string to float32
583        let result = call_parse_string_to_value!(
584            "col",
585            "3.5".to_string(),
586            ConcreteDataType::float32_datatype()
587        )?;
588        assert_eq!(Value::Float32(OrderedF32::from(3.5)), result);
589
590        // Test invalid string to float32 with auto cast
591        let result = call_parse_string_to_value!(
592            "col",
593            "not_a_float32".to_string(),
594            ConcreteDataType::float32_datatype()
595        );
596        assert!(result.is_err());
597
598        // Test string to float64
599        let result = call_parse_string_to_value!(
600            "col",
601            "3.5".to_string(),
602            ConcreteDataType::float64_datatype()
603        )?;
604        assert_eq!(Value::Float64(OrderedF64::from(3.5)), result);
605
606        // Test invalid string to float64 with auto cast
607        let result = call_parse_string_to_value!(
608            "col",
609            "not_a_float64".to_string(),
610            ConcreteDataType::float64_datatype()
611        );
612        assert!(result.is_err());
613        Ok(())
614    }
615
616    macro_rules! call_sql_value_to_value {
617        ($column_name: expr, $data_type: expr, $sql_value: expr) => {
618            call_sql_value_to_value!($column_name, $data_type, $sql_value, None, None, false)
619        };
620        ($column_name: expr, $data_type: expr, $sql_value: expr, timezone = $timezone: expr) => {
621            call_sql_value_to_value!(
622                $column_name,
623                $data_type,
624                $sql_value,
625                Some($timezone),
626                None,
627                false
628            )
629        };
630        ($column_name: expr, $data_type: expr, $sql_value: expr, unary_op = $unary_op: expr) => {
631            call_sql_value_to_value!(
632                $column_name,
633                $data_type,
634                $sql_value,
635                None,
636                Some($unary_op),
637                false
638            )
639        };
640        ($column_name: expr, $data_type: expr, $sql_value: expr, auto_string_to_numeric) => {
641            call_sql_value_to_value!($column_name, $data_type, $sql_value, None, None, true)
642        };
643        ($column_name: expr, $data_type: expr, $sql_value: expr, $timezone: expr, $unary_op: expr, $auto_string_to_numeric: expr) => {{
644            let column_schema = ColumnSchema::new($column_name, $data_type, true);
645            sql_value_to_value(
646                &column_schema,
647                $sql_value,
648                $timezone,
649                $unary_op,
650                $auto_string_to_numeric,
651            )
652        }};
653    }
654
655    #[test]
656    fn test_sql_value_to_value() -> Result<()> {
657        let sql_val = SqlValue::Null;
658        assert_eq!(
659            Value::Null,
660            call_sql_value_to_value!("a", ConcreteDataType::float64_datatype(), &sql_val)?
661        );
662
663        let sql_val = SqlValue::Boolean(true);
664        assert_eq!(
665            Value::Boolean(true),
666            call_sql_value_to_value!("a", ConcreteDataType::boolean_datatype(), &sql_val)?
667        );
668
669        let sql_val = SqlValue::Number("3.0".to_string(), false);
670        assert_eq!(
671            Value::Float64(OrderedFloat(3.0)),
672            call_sql_value_to_value!("a", ConcreteDataType::float64_datatype(), &sql_val)?
673        );
674
675        let sql_val = SqlValue::Number("3.0".to_string(), false);
676        let v = call_sql_value_to_value!("a", ConcreteDataType::boolean_datatype(), &sql_val);
677        assert!(v.is_err());
678        assert!(format!("{v:?}").contains("Failed to parse number '3.0' to boolean column type"));
679
680        let sql_val = SqlValue::Boolean(true);
681        let v = call_sql_value_to_value!("a", ConcreteDataType::float64_datatype(), &sql_val);
682        assert!(v.is_err());
683        assert!(
684            format!("{v:?}").contains(
685                "Column a expect type: Float64(Float64Type), actual: Boolean(BooleanType)"
686            ),
687            "v is {v:?}",
688        );
689
690        let sql_val = SqlValue::HexStringLiteral("48656c6c6f20776f726c6421".to_string());
691        let v = call_sql_value_to_value!("a", ConcreteDataType::binary_datatype(), &sql_val)?;
692        assert_eq!(Value::Binary(Bytes::from(b"Hello world!".as_slice())), v);
693
694        let sql_val = SqlValue::DoubleQuotedString("MorningMyFriends".to_string());
695        let v = call_sql_value_to_value!("a", ConcreteDataType::binary_datatype(), &sql_val)?;
696        assert_eq!(
697            Value::Binary(Bytes::from(b"MorningMyFriends".as_slice())),
698            v
699        );
700
701        let sql_val = SqlValue::HexStringLiteral("9AF".to_string());
702        let v = call_sql_value_to_value!("a", ConcreteDataType::binary_datatype(), &sql_val);
703        assert!(v.is_err());
704        assert!(
705            format!("{v:?}").contains("odd number of digits"),
706            "v is {v:?}"
707        );
708
709        let sql_val = SqlValue::HexStringLiteral("AG".to_string());
710        let v = call_sql_value_to_value!("a", ConcreteDataType::binary_datatype(), &sql_val);
711        assert!(v.is_err());
712        assert!(format!("{v:?}").contains("invalid character"), "v is {v:?}",);
713
714        let sql_val = SqlValue::DoubleQuotedString("MorningMyFriends".to_string());
715        let v = call_sql_value_to_value!("a", ConcreteDataType::json_datatype(), &sql_val);
716        assert!(v.is_err());
717
718        let sql_val = SqlValue::DoubleQuotedString(r#"{"a":"b"}"#.to_string());
719        let v = call_sql_value_to_value!("a", ConcreteDataType::json_datatype(), &sql_val)?;
720        assert_eq!(
721            Value::Binary(Bytes::from(
722                jsonb::parse_value(r#"{"a":"b"}"#.as_bytes())
723                    .unwrap()
724                    .to_vec()
725                    .as_slice()
726            )),
727            v
728        );
729        Ok(())
730    }
731
732    #[test]
733    fn test_parse_json_to_jsonb() {
734        match call_parse_string_to_value!(
735            "json_col",
736            r#"{"a": "b"}"#.to_string(),
737            ConcreteDataType::json_datatype()
738        ) {
739            Ok(Value::Binary(b)) => {
740                assert_eq!(
741                    b,
742                    jsonb::parse_value(r#"{"a": "b"}"#.as_bytes())
743                        .unwrap()
744                        .to_vec()
745                );
746            }
747            _ => {
748                unreachable!()
749            }
750        }
751
752        assert!(
753            call_parse_string_to_value!(
754                "json_col",
755                r#"Nicola Kovac is the best rifler in the world"#.to_string(),
756                ConcreteDataType::json_datatype()
757            )
758            .is_err()
759        )
760    }
761
762    #[test]
763    fn test_sql_number_to_value() {
764        let v = sql_number_to_value(&ConcreteDataType::float64_datatype(), "3.0").unwrap();
765        assert_eq!(Value::Float64(OrderedFloat(3.0)), v);
766
767        let v = sql_number_to_value(&ConcreteDataType::int32_datatype(), "999").unwrap();
768        assert_eq!(Value::Int32(999), v);
769
770        let v = sql_number_to_value(
771            &ConcreteDataType::timestamp_nanosecond_datatype(),
772            "1073741821",
773        )
774        .unwrap();
775        assert_eq!(Value::Timestamp(Timestamp::new_nanosecond(1073741821)), v);
776
777        let v = sql_number_to_value(
778            &ConcreteDataType::timestamp_millisecond_datatype(),
779            "999999",
780        )
781        .unwrap();
782        assert_eq!(Value::Timestamp(Timestamp::new_millisecond(999999)), v);
783
784        let v = sql_number_to_value(&ConcreteDataType::string_datatype(), "999");
785        assert!(v.is_err(), "parse value error is: {v:?}");
786
787        let v = sql_number_to_value(&ConcreteDataType::boolean_datatype(), "0").unwrap();
788        assert_eq!(v, Value::Boolean(false));
789        let v = sql_number_to_value(&ConcreteDataType::boolean_datatype(), "1").unwrap();
790        assert_eq!(v, Value::Boolean(true));
791        assert!(sql_number_to_value(&ConcreteDataType::boolean_datatype(), "2").is_err());
792    }
793
794    #[test]
795    fn test_parse_date_literal() {
796        let value = call_sql_value_to_value!(
797            "date",
798            ConcreteDataType::date_datatype(),
799            &SqlValue::DoubleQuotedString("2022-02-22".to_string())
800        )
801        .unwrap();
802        assert_eq!(ConcreteDataType::date_datatype(), value.data_type());
803        if let Value::Date(d) = value {
804            assert_eq!("2022-02-22", d.to_string());
805        } else {
806            unreachable!()
807        }
808
809        // with timezone
810        let value = call_sql_value_to_value!(
811            "date",
812            ConcreteDataType::date_datatype(),
813            &SqlValue::DoubleQuotedString("2022-02-22".to_string()),
814            timezone = &Timezone::from_tz_string("+07:00").unwrap()
815        )
816        .unwrap();
817        assert_eq!(ConcreteDataType::date_datatype(), value.data_type());
818        if let Value::Date(d) = value {
819            assert_eq!("2022-02-21", d.to_string());
820        } else {
821            unreachable!()
822        }
823    }
824
825    #[test]
826    fn test_parse_timestamp_literal() -> Result<()> {
827        match call_parse_string_to_value!(
828            "timestamp_col",
829            "2022-02-22T00:01:01+08:00".to_string(),
830            ConcreteDataType::timestamp_millisecond_datatype()
831        )? {
832            Value::Timestamp(ts) => {
833                assert_eq!(1645459261000, ts.value());
834                assert_eq!(TimeUnit::Millisecond, ts.unit());
835            }
836            _ => {
837                unreachable!()
838            }
839        }
840
841        match call_parse_string_to_value!(
842            "timestamp_col",
843            "2022-02-22T00:01:01+08:00".to_string(),
844            ConcreteDataType::timestamp_datatype(TimeUnit::Second)
845        )? {
846            Value::Timestamp(ts) => {
847                assert_eq!(1645459261, ts.value());
848                assert_eq!(TimeUnit::Second, ts.unit());
849            }
850            _ => {
851                unreachable!()
852            }
853        }
854
855        match call_parse_string_to_value!(
856            "timestamp_col",
857            "2022-02-22T00:01:01+08:00".to_string(),
858            ConcreteDataType::timestamp_datatype(TimeUnit::Microsecond)
859        )? {
860            Value::Timestamp(ts) => {
861                assert_eq!(1645459261000000, ts.value());
862                assert_eq!(TimeUnit::Microsecond, ts.unit());
863            }
864            _ => {
865                unreachable!()
866            }
867        }
868
869        match call_parse_string_to_value!(
870            "timestamp_col",
871            "2022-02-22T00:01:01+08:00".to_string(),
872            ConcreteDataType::timestamp_datatype(TimeUnit::Nanosecond)
873        )? {
874            Value::Timestamp(ts) => {
875                assert_eq!(1645459261000000000, ts.value());
876                assert_eq!(TimeUnit::Nanosecond, ts.unit());
877            }
878            _ => {
879                unreachable!()
880            }
881        }
882
883        assert!(
884            call_parse_string_to_value!(
885                "timestamp_col",
886                "2022-02-22T00:01:01+08".to_string(),
887                ConcreteDataType::timestamp_datatype(TimeUnit::Nanosecond)
888            )
889            .is_err()
890        );
891
892        // with timezone
893        match call_parse_string_to_value!(
894            "timestamp_col",
895            "2022-02-22T00:01:01".to_string(),
896            ConcreteDataType::timestamp_datatype(TimeUnit::Nanosecond),
897            timezone = &Timezone::from_tz_string("Asia/Shanghai").unwrap()
898        )? {
899            Value::Timestamp(ts) => {
900                assert_eq!(1645459261000000000, ts.value());
901                assert_eq!("2022-02-21 16:01:01+0000", ts.to_iso8601_string());
902                assert_eq!(TimeUnit::Nanosecond, ts.unit());
903            }
904            _ => {
905                unreachable!()
906            }
907        }
908        Ok(())
909    }
910
911    #[test]
912    fn test_parse_placeholder_value() {
913        assert!(
914            call_sql_value_to_value!(
915                "test",
916                ConcreteDataType::string_datatype(),
917                &SqlValue::Placeholder("default".into())
918            )
919            .is_err()
920        );
921        assert!(
922            call_sql_value_to_value!(
923                "test",
924                ConcreteDataType::string_datatype(),
925                &SqlValue::Placeholder("default".into()),
926                unary_op = UnaryOperator::Minus
927            )
928            .is_err()
929        );
930        assert!(
931            call_sql_value_to_value!(
932                "test",
933                ConcreteDataType::uint16_datatype(),
934                &SqlValue::Number("3".into(), false),
935                unary_op = UnaryOperator::Minus
936            )
937            .is_err()
938        );
939        assert!(
940            call_sql_value_to_value!(
941                "test",
942                ConcreteDataType::uint16_datatype(),
943                &SqlValue::Number("3".into(), false)
944            )
945            .is_ok()
946        );
947    }
948
949    #[test]
950    fn test_auto_string_to_numeric() {
951        // Test with auto_string_to_numeric=true
952        let sql_val = SqlValue::SingleQuotedString("123".to_string());
953        let v = call_sql_value_to_value!(
954            "a",
955            ConcreteDataType::int32_datatype(),
956            &sql_val,
957            auto_string_to_numeric
958        )
959        .unwrap();
960        assert_eq!(Value::Int32(123), v);
961
962        // Test with a float string
963        let sql_val = SqlValue::SingleQuotedString("3.5".to_string());
964        let v = call_sql_value_to_value!(
965            "a",
966            ConcreteDataType::float64_datatype(),
967            &sql_val,
968            auto_string_to_numeric
969        )
970        .unwrap();
971        assert_eq!(Value::Float64(OrderedFloat(3.5)), v);
972
973        // Test with auto_string_to_numeric=false
974        let sql_val = SqlValue::SingleQuotedString("123".to_string());
975        let v = call_sql_value_to_value!("a", ConcreteDataType::int32_datatype(), &sql_val);
976        assert!(v.is_err());
977
978        // Test with an invalid numeric string but auto_string_to_numeric=true
979        // Should return an error now with the new auto_cast_to_numeric behavior
980        let sql_val = SqlValue::SingleQuotedString("not_a_number".to_string());
981        let v = call_sql_value_to_value!(
982            "a",
983            ConcreteDataType::int32_datatype(),
984            &sql_val,
985            auto_string_to_numeric
986        );
987        assert!(v.is_err());
988
989        // Test with boolean type
990        let sql_val = SqlValue::SingleQuotedString("true".to_string());
991        let v = call_sql_value_to_value!(
992            "a",
993            ConcreteDataType::boolean_datatype(),
994            &sql_val,
995            auto_string_to_numeric
996        )
997        .unwrap();
998        assert_eq!(Value::Boolean(true), v);
999
1000        // Non-numeric types should still be handled normally
1001        let sql_val = SqlValue::SingleQuotedString("hello".to_string());
1002        let v = call_sql_value_to_value!(
1003            "a",
1004            ConcreteDataType::string_datatype(),
1005            &sql_val,
1006            auto_string_to_numeric
1007        );
1008        assert!(v.is_ok());
1009    }
1010
1011    #[test]
1012    fn test_sql_number_to_value_timestamp_strict_typing() {
1013        // Test that values are interpreted according to the target column type
1014        let timestamp_type = TimestampType::Millisecond(datatypes::types::TimestampMillisecondType);
1015        let data_type = ConcreteDataType::Timestamp(timestamp_type);
1016
1017        // Valid millisecond timestamp
1018        let millisecond_str = "1747814093865";
1019        let result = sql_number_to_value(&data_type, millisecond_str).unwrap();
1020        if let Value::Timestamp(ts) = result {
1021            assert_eq!(ts.unit(), TimeUnit::Millisecond);
1022            assert_eq!(ts.value(), 1747814093865);
1023        } else {
1024            panic!("Expected timestamp value");
1025        }
1026
1027        // Large value that would overflow when treated as milliseconds should be rejected
1028        let nanosecond_str = "1747814093865000000"; // This is too large for millisecond precision
1029        let result = sql_number_to_value(&data_type, nanosecond_str);
1030        assert!(
1031            result.is_err(),
1032            "Should reject overly large timestamp values"
1033        );
1034    }
1035
1036    #[test]
1037    fn test_sql_number_to_value_timestamp_different_units() {
1038        // Test second precision
1039        let second_type = TimestampType::Second(datatypes::types::TimestampSecondType);
1040        let second_data_type = ConcreteDataType::Timestamp(second_type);
1041
1042        let second_str = "1747814093";
1043        let result = sql_number_to_value(&second_data_type, second_str).unwrap();
1044        if let Value::Timestamp(ts) = result {
1045            assert_eq!(ts.unit(), TimeUnit::Second);
1046            assert_eq!(ts.value(), 1747814093);
1047        } else {
1048            panic!("Expected timestamp value");
1049        }
1050
1051        // Test nanosecond precision
1052        let nanosecond_type = TimestampType::Nanosecond(datatypes::types::TimestampNanosecondType);
1053        let nanosecond_data_type = ConcreteDataType::Timestamp(nanosecond_type);
1054
1055        let nanosecond_str = "1747814093865000000";
1056        let result = sql_number_to_value(&nanosecond_data_type, nanosecond_str).unwrap();
1057        if let Value::Timestamp(ts) = result {
1058            assert_eq!(ts.unit(), TimeUnit::Nanosecond);
1059            assert_eq!(ts.value(), 1747814093865000000);
1060        } else {
1061            panic!("Expected timestamp value");
1062        }
1063    }
1064
1065    #[test]
1066    fn test_timestamp_range_validation() {
1067        // Test that our range checking works correctly
1068        let nanosecond_value = 1747814093865000000i64; // This should be too large for millisecond
1069
1070        // This should work for nanosecond precision
1071        let nanosecond_type = TimestampType::Nanosecond(datatypes::types::TimestampNanosecondType);
1072        let nanosecond_data_type = ConcreteDataType::Timestamp(nanosecond_type);
1073        let result = sql_number_to_value(&nanosecond_data_type, "1747814093865000000");
1074        assert!(
1075            result.is_ok(),
1076            "Nanosecond value should be valid for nanosecond column"
1077        );
1078
1079        // This should fail for millisecond precision (value too large)
1080        let millisecond_type =
1081            TimestampType::Millisecond(datatypes::types::TimestampMillisecondType);
1082        let millisecond_data_type = ConcreteDataType::Timestamp(millisecond_type);
1083        let result = sql_number_to_value(&millisecond_data_type, "1747814093865000000");
1084        assert!(
1085            result.is_err(),
1086            "Nanosecond value should be rejected for millisecond column"
1087        );
1088
1089        // Verify the ranges work as expected
1090        assert!(
1091            nanosecond_value > Timestamp::MAX_MILLISECOND.value(),
1092            "Test value should exceed millisecond range"
1093        );
1094    }
1095}
common_sql/convert.rs

common_sql/
convert.rs
