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

common_sql/
convert.rs
