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

common_sql/
convert.rs
