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

common_sql/
convert.rs
