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::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::{ensure, OptionExt, ResultExt};
24pub use sqlparser::ast::{
25    visit_expressions_mut, visit_statements_mut, BinaryOperator, ColumnDef, ColumnOption,
26    ColumnOptionDef, DataType, Expr, Function, FunctionArg, FunctionArgExpr, FunctionArguments,
27    Ident, ObjectName, SqlOption, TableConstraint, TimezoneInfo, UnaryOperator, Value as SqlValue,
28    Visit, VisitMut, Visitor, VisitorMut,
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 {
247        if let Some(value) = auto_cast_to_numeric(&s, data_type)? {
248            return Ok(value);
249        }
250    }
251
252    ensure!(
253        data_type.is_stringifiable(),
254        ColumnTypeMismatchSnafu {
255            column_name,
256            expect: data_type.clone(),
257            actual: ConcreteDataType::string_datatype(),
258        }
259    );
260
261    match data_type {
262        ConcreteDataType::String(_) => Ok(Value::String(s.into())),
263        ConcreteDataType::Date(_) => {
264            if let Ok(date) = common_time::date::Date::from_str(&s, timezone) {
265                Ok(Value::Date(date))
266            } else {
267                ParseSqlValueSnafu {
268                    msg: format!("Failed to parse {s} to Date value"),
269                }
270                .fail()
271            }
272        }
273        ConcreteDataType::Timestamp(t) => {
274            if let Ok(ts) = Timestamp::from_str(&s, timezone) {
275                Ok(Value::Timestamp(ts.convert_to(t.unit()).context(
276                    TimestampOverflowSnafu {
277                        timestamp: ts,
278                        target_unit: t.unit(),
279                    },
280                )?))
281            } else if let Ok(ts) = i64::from_str(s.as_str()) {
282                Ok(Value::Timestamp(Timestamp::new(ts, t.unit())))
283            } else {
284                ParseSqlValueSnafu {
285                    msg: format!("Failed to parse {s} to Timestamp value"),
286                }
287                .fail()
288            }
289        }
290        ConcreteDataType::Decimal128(_) => {
291            if let Ok(val) = common_decimal::Decimal128::from_str(&s) {
292                Ok(Value::Decimal128(val))
293            } else {
294                ParseSqlValueSnafu {
295                    msg: format!("Fail to parse number {s} to Decimal128 value"),
296                }
297                .fail()
298            }
299        }
300        ConcreteDataType::Binary(_) => Ok(Value::Binary(s.as_bytes().into())),
301        ConcreteDataType::Json(j) => {
302            let v = parse_string_to_json_type_value(&s, &j.format).context(DatatypeSnafu)?;
303            Ok(Value::Binary(v.into()))
304        }
305        ConcreteDataType::Vector(d) => {
306            let v = parse_string_to_vector_type_value(&s, Some(d.dim)).context(DatatypeSnafu)?;
307            Ok(Value::Binary(v.into()))
308        }
309        _ => ParseSqlValueSnafu {
310            msg: format!("Failed to parse {s} to {data_type} value"),
311        }
312        .fail(),
313    }
314}
315
316/// Casts string to value of specified numeric data type.
317/// If the string cannot be parsed, returns an error.
318///
319/// Returns None if the data type doesn't support auto casting.
320pub(crate) fn auto_cast_to_numeric(s: &str, data_type: &ConcreteDataType) -> Result<Option<Value>> {
321    let value = match data_type {
322        ConcreteDataType::Boolean(_) => s.parse::<bool>().map(Value::Boolean).ok(),
323        ConcreteDataType::Int8(_) => s.parse::<i8>().map(Value::Int8).ok(),
324        ConcreteDataType::Int16(_) => s.parse::<i16>().map(Value::Int16).ok(),
325        ConcreteDataType::Int32(_) => s.parse::<i32>().map(Value::Int32).ok(),
326        ConcreteDataType::Int64(_) => s.parse::<i64>().map(Value::Int64).ok(),
327        ConcreteDataType::UInt8(_) => s.parse::<u8>().map(Value::UInt8).ok(),
328        ConcreteDataType::UInt16(_) => s.parse::<u16>().map(Value::UInt16).ok(),
329        ConcreteDataType::UInt32(_) => s.parse::<u32>().map(Value::UInt32).ok(),
330        ConcreteDataType::UInt64(_) => s.parse::<u64>().map(Value::UInt64).ok(),
331        ConcreteDataType::Float32(_) => s
332            .parse::<f32>()
333            .map(|v| Value::Float32(OrderedF32::from(v)))
334            .ok(),
335        ConcreteDataType::Float64(_) => s
336            .parse::<f64>()
337            .map(|v| Value::Float64(OrderedF64::from(v)))
338            .ok(),
339        _ => return Ok(None),
340    };
341
342    match value {
343        Some(value) => Ok(Some(value)),
344        None => ConvertStrSnafu {
345            value: s,
346            datatype: data_type.clone(),
347        }
348        .fail(),
349    }
350}
351
352pub(crate) fn parse_hex_string(s: &str) -> Result<Value> {
353    match hex::decode(s) {
354        Ok(b) => Ok(Value::Binary(common_base::bytes::Bytes::from(b))),
355        Err(hex::FromHexError::InvalidHexCharacter { c, index }) => ParseSqlValueSnafu {
356            msg: format!(
357                "Fail to parse hex string to Byte: invalid character {c:?} at position {index}"
358            ),
359        }
360        .fail(),
361        Err(hex::FromHexError::OddLength) => ParseSqlValueSnafu {
362            msg: "Fail to parse hex string to Byte: odd number of digits".to_string(),
363        }
364        .fail(),
365        Err(e) => ParseSqlValueSnafu {
366            msg: format!("Fail to parse hex string to Byte {s}, {e:?}"),
367        }
368        .fail(),
369    }
370}
371
372/// Deserialize default constraint from json bytes
373pub fn deserialize_default_constraint(
374    bytes: &[u8],
375    column_name: &str,
376    data_type: &ConcreteDataType,
377) -> Result<Option<ColumnDefaultConstraint>> {
378    let json = String::from_utf8_lossy(bytes);
379    let default_constraint = serde_json::from_str(&json).context(DeserializeSnafu { json })?;
380    let column_def = sqlparser::ast::ColumnOptionDef {
381        name: None,
382        option: sqlparser::ast::ColumnOption::Default(default_constraint),
383    };
384
385    crate::default_constraint::parse_column_default_constraint(
386        column_name,
387        data_type,
388        &[column_def],
389        None,
390    )
391}
392
393#[cfg(test)]
394mod test {
395    use common_base::bytes::Bytes;
396    use common_time::timestamp::TimeUnit;
397    use datatypes::types::TimestampType;
398    use datatypes::value::OrderedFloat;
399
400    use super::*;
401
402    #[test]
403    fn test_string_to_value_auto_numeric() {
404        // Test string to boolean with auto cast
405        let result = parse_string_to_value(
406            "col",
407            "true".to_string(),
408            &ConcreteDataType::boolean_datatype(),
409            None,
410            true,
411        )
412        .unwrap();
413        assert_eq!(Value::Boolean(true), result);
414
415        // Test invalid string to boolean with auto cast
416        let result = parse_string_to_value(
417            "col",
418            "not_a_boolean".to_string(),
419            &ConcreteDataType::boolean_datatype(),
420            None,
421            true,
422        );
423        assert!(result.is_err());
424
425        // Test string to int8
426        let result = parse_string_to_value(
427            "col",
428            "42".to_string(),
429            &ConcreteDataType::int8_datatype(),
430            None,
431            true,
432        )
433        .unwrap();
434        assert_eq!(Value::Int8(42), result);
435
436        // Test invalid string to int8 with auto cast
437        let result = parse_string_to_value(
438            "col",
439            "not_an_int8".to_string(),
440            &ConcreteDataType::int8_datatype(),
441            None,
442            true,
443        );
444        assert!(result.is_err());
445
446        // Test string to int16
447        let result = parse_string_to_value(
448            "col",
449            "1000".to_string(),
450            &ConcreteDataType::int16_datatype(),
451            None,
452            true,
453        )
454        .unwrap();
455        assert_eq!(Value::Int16(1000), result);
456
457        // Test invalid string to int16 with auto cast
458        let result = parse_string_to_value(
459            "col",
460            "not_an_int16".to_string(),
461            &ConcreteDataType::int16_datatype(),
462            None,
463            true,
464        );
465        assert!(result.is_err());
466
467        // Test string to int32
468        let result = parse_string_to_value(
469            "col",
470            "100000".to_string(),
471            &ConcreteDataType::int32_datatype(),
472            None,
473            true,
474        )
475        .unwrap();
476        assert_eq!(Value::Int32(100000), result);
477
478        // Test invalid string to int32 with auto cast
479        let result = parse_string_to_value(
480            "col",
481            "not_an_int32".to_string(),
482            &ConcreteDataType::int32_datatype(),
483            None,
484            true,
485        );
486        assert!(result.is_err());
487
488        // Test string to int64
489        let result = parse_string_to_value(
490            "col",
491            "1000000".to_string(),
492            &ConcreteDataType::int64_datatype(),
493            None,
494            true,
495        )
496        .unwrap();
497        assert_eq!(Value::Int64(1000000), result);
498
499        // Test invalid string to int64 with auto cast
500        let result = parse_string_to_value(
501            "col",
502            "not_an_int64".to_string(),
503            &ConcreteDataType::int64_datatype(),
504            None,
505            true,
506        );
507        assert!(result.is_err());
508
509        // Test string to uint8
510        let result = parse_string_to_value(
511            "col",
512            "200".to_string(),
513            &ConcreteDataType::uint8_datatype(),
514            None,
515            true,
516        )
517        .unwrap();
518        assert_eq!(Value::UInt8(200), result);
519
520        // Test invalid string to uint8 with auto cast
521        let result = parse_string_to_value(
522            "col",
523            "not_a_uint8".to_string(),
524            &ConcreteDataType::uint8_datatype(),
525            None,
526            true,
527        );
528        assert!(result.is_err());
529
530        // Test string to uint16
531        let result = parse_string_to_value(
532            "col",
533            "60000".to_string(),
534            &ConcreteDataType::uint16_datatype(),
535            None,
536            true,
537        )
538        .unwrap();
539        assert_eq!(Value::UInt16(60000), result);
540
541        // Test invalid string to uint16 with auto cast
542        let result = parse_string_to_value(
543            "col",
544            "not_a_uint16".to_string(),
545            &ConcreteDataType::uint16_datatype(),
546            None,
547            true,
548        );
549        assert!(result.is_err());
550
551        // Test string to uint32
552        let result = parse_string_to_value(
553            "col",
554            "4000000000".to_string(),
555            &ConcreteDataType::uint32_datatype(),
556            None,
557            true,
558        )
559        .unwrap();
560        assert_eq!(Value::UInt32(4000000000), result);
561
562        // Test invalid string to uint32 with auto cast
563        let result = parse_string_to_value(
564            "col",
565            "not_a_uint32".to_string(),
566            &ConcreteDataType::uint32_datatype(),
567            None,
568            true,
569        );
570        assert!(result.is_err());
571
572        // Test string to uint64
573        let result = parse_string_to_value(
574            "col",
575            "18446744073709551615".to_string(),
576            &ConcreteDataType::uint64_datatype(),
577            None,
578            true,
579        )
580        .unwrap();
581        assert_eq!(Value::UInt64(18446744073709551615), result);
582
583        // Test invalid string to uint64 with auto cast
584        let result = parse_string_to_value(
585            "col",
586            "not_a_uint64".to_string(),
587            &ConcreteDataType::uint64_datatype(),
588            None,
589            true,
590        );
591        assert!(result.is_err());
592
593        // Test string to float32
594        let result = parse_string_to_value(
595            "col",
596            "3.5".to_string(),
597            &ConcreteDataType::float32_datatype(),
598            None,
599            true,
600        )
601        .unwrap();
602        assert_eq!(Value::Float32(OrderedF32::from(3.5)), result);
603
604        // Test invalid string to float32 with auto cast
605        let result = parse_string_to_value(
606            "col",
607            "not_a_float32".to_string(),
608            &ConcreteDataType::float32_datatype(),
609            None,
610            true,
611        );
612        assert!(result.is_err());
613
614        // Test string to float64
615        let result = parse_string_to_value(
616            "col",
617            "3.5".to_string(),
618            &ConcreteDataType::float64_datatype(),
619            None,
620            true,
621        )
622        .unwrap();
623        assert_eq!(Value::Float64(OrderedF64::from(3.5)), result);
624
625        // Test invalid string to float64 with auto cast
626        let result = parse_string_to_value(
627            "col",
628            "not_a_float64".to_string(),
629            &ConcreteDataType::float64_datatype(),
630            None,
631            true,
632        );
633        assert!(result.is_err());
634    }
635
636    #[test]
637    fn test_sql_value_to_value() {
638        let sql_val = SqlValue::Null;
639        assert_eq!(
640            Value::Null,
641            sql_value_to_value(
642                "a",
643                &ConcreteDataType::float64_datatype(),
644                &sql_val,
645                None,
646                None,
647                false
648            )
649            .unwrap()
650        );
651
652        let sql_val = SqlValue::Boolean(true);
653        assert_eq!(
654            Value::Boolean(true),
655            sql_value_to_value(
656                "a",
657                &ConcreteDataType::boolean_datatype(),
658                &sql_val,
659                None,
660                None,
661                false
662            )
663            .unwrap()
664        );
665
666        let sql_val = SqlValue::Number("3.0".to_string(), false);
667        assert_eq!(
668            Value::Float64(OrderedFloat(3.0)),
669            sql_value_to_value(
670                "a",
671                &ConcreteDataType::float64_datatype(),
672                &sql_val,
673                None,
674                None,
675                false
676            )
677            .unwrap()
678        );
679
680        let sql_val = SqlValue::Number("3.0".to_string(), false);
681        let v = sql_value_to_value(
682            "a",
683            &ConcreteDataType::boolean_datatype(),
684            &sql_val,
685            None,
686            None,
687            false,
688        );
689        assert!(v.is_err());
690        assert!(format!("{v:?}").contains("Failed to parse number '3.0' to boolean column type"));
691
692        let sql_val = SqlValue::Boolean(true);
693        let v = sql_value_to_value(
694            "a",
695            &ConcreteDataType::float64_datatype(),
696            &sql_val,
697            None,
698            None,
699            false,
700        );
701        assert!(v.is_err());
702        assert!(
703            format!("{v:?}").contains(
704                "Column a expect type: Float64(Float64Type), actual: Boolean(BooleanType)"
705            ),
706            "v is {v:?}",
707        );
708
709        let sql_val = SqlValue::HexStringLiteral("48656c6c6f20776f726c6421".to_string());
710        let v = sql_value_to_value(
711            "a",
712            &ConcreteDataType::binary_datatype(),
713            &sql_val,
714            None,
715            None,
716            false,
717        )
718        .unwrap();
719        assert_eq!(Value::Binary(Bytes::from(b"Hello world!".as_slice())), v);
720
721        let sql_val = SqlValue::DoubleQuotedString("MorningMyFriends".to_string());
722        let v = sql_value_to_value(
723            "a",
724            &ConcreteDataType::binary_datatype(),
725            &sql_val,
726            None,
727            None,
728            false,
729        )
730        .unwrap();
731        assert_eq!(
732            Value::Binary(Bytes::from(b"MorningMyFriends".as_slice())),
733            v
734        );
735
736        let sql_val = SqlValue::HexStringLiteral("9AF".to_string());
737        let v = sql_value_to_value(
738            "a",
739            &ConcreteDataType::binary_datatype(),
740            &sql_val,
741            None,
742            None,
743            false,
744        );
745        assert!(v.is_err());
746        assert!(
747            format!("{v:?}").contains("odd number of digits"),
748            "v is {v:?}"
749        );
750
751        let sql_val = SqlValue::HexStringLiteral("AG".to_string());
752        let v = sql_value_to_value(
753            "a",
754            &ConcreteDataType::binary_datatype(),
755            &sql_val,
756            None,
757            None,
758            false,
759        );
760        assert!(v.is_err());
761        assert!(format!("{v:?}").contains("invalid character"), "v is {v:?}",);
762
763        let sql_val = SqlValue::DoubleQuotedString("MorningMyFriends".to_string());
764        let v = sql_value_to_value(
765            "a",
766            &ConcreteDataType::json_datatype(),
767            &sql_val,
768            None,
769            None,
770            false,
771        );
772        assert!(v.is_err());
773
774        let sql_val = SqlValue::DoubleQuotedString(r#"{"a":"b"}"#.to_string());
775        let v = sql_value_to_value(
776            "a",
777            &ConcreteDataType::json_datatype(),
778            &sql_val,
779            None,
780            None,
781            false,
782        )
783        .unwrap();
784        assert_eq!(
785            Value::Binary(Bytes::from(
786                jsonb::parse_value(r#"{"a":"b"}"#.as_bytes())
787                    .unwrap()
788                    .to_vec()
789                    .as_slice()
790            )),
791            v
792        );
793    }
794
795    #[test]
796    fn test_parse_json_to_jsonb() {
797        match parse_string_to_value(
798            "json_col",
799            r#"{"a": "b"}"#.to_string(),
800            &ConcreteDataType::json_datatype(),
801            None,
802            false,
803        ) {
804            Ok(Value::Binary(b)) => {
805                assert_eq!(
806                    b,
807                    jsonb::parse_value(r#"{"a": "b"}"#.as_bytes())
808                        .unwrap()
809                        .to_vec()
810                );
811            }
812            _ => {
813                unreachable!()
814            }
815        }
816
817        assert!(parse_string_to_value(
818            "json_col",
819            r#"Nicola Kovac is the best rifler in the world"#.to_string(),
820            &ConcreteDataType::json_datatype(),
821            None,
822            false,
823        )
824        .is_err())
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!(parse_string_to_value(
970            "timestamp_col",
971            "2022-02-22T00:01:01+08".to_string(),
972            &ConcreteDataType::timestamp_datatype(TimeUnit::Nanosecond),
973            None,
974            false,
975        )
976        .is_err());
977
978        // with timezone
979        match parse_string_to_value(
980            "timestamp_col",
981            "2022-02-22T00:01:01".to_string(),
982            &ConcreteDataType::timestamp_datatype(TimeUnit::Nanosecond),
983            Some(&Timezone::from_tz_string("Asia/Shanghai").unwrap()),
984            false,
985        )
986        .unwrap()
987        {
988            Value::Timestamp(ts) => {
989                assert_eq!(1645459261000000000, ts.value());
990                assert_eq!("2022-02-21 16:01:01+0000", ts.to_iso8601_string());
991                assert_eq!(TimeUnit::Nanosecond, ts.unit());
992            }
993            _ => {
994                unreachable!()
995            }
996        }
997    }
998
999    #[test]
1000    fn test_parse_placeholder_value() {
1001        assert!(sql_value_to_value(
1002            "test",
1003            &ConcreteDataType::string_datatype(),
1004            &SqlValue::Placeholder("default".into()),
1005            None,
1006            None,
1007            false
1008        )
1009        .is_err());
1010        assert!(sql_value_to_value(
1011            "test",
1012            &ConcreteDataType::string_datatype(),
1013            &SqlValue::Placeholder("default".into()),
1014            None,
1015            Some(UnaryOperator::Minus),
1016            false
1017        )
1018        .is_err());
1019        assert!(sql_value_to_value(
1020            "test",
1021            &ConcreteDataType::uint16_datatype(),
1022            &SqlValue::Number("3".into(), false),
1023            None,
1024            Some(UnaryOperator::Minus),
1025            false
1026        )
1027        .is_err());
1028        assert!(sql_value_to_value(
1029            "test",
1030            &ConcreteDataType::uint16_datatype(),
1031            &SqlValue::Number("3".into(), false),
1032            None,
1033            None,
1034            false
1035        )
1036        .is_ok());
1037    }
1038
1039    #[test]
1040    fn test_auto_string_to_numeric() {
1041        // Test with auto_string_to_numeric=true
1042        let sql_val = SqlValue::SingleQuotedString("123".to_string());
1043        let v = sql_value_to_value(
1044            "a",
1045            &ConcreteDataType::int32_datatype(),
1046            &sql_val,
1047            None,
1048            None,
1049            true,
1050        )
1051        .unwrap();
1052        assert_eq!(Value::Int32(123), v);
1053
1054        // Test with a float string
1055        let sql_val = SqlValue::SingleQuotedString("3.5".to_string());
1056        let v = sql_value_to_value(
1057            "a",
1058            &ConcreteDataType::float64_datatype(),
1059            &sql_val,
1060            None,
1061            None,
1062            true,
1063        )
1064        .unwrap();
1065        assert_eq!(Value::Float64(OrderedFloat(3.5)), v);
1066
1067        // Test with auto_string_to_numeric=false
1068        let sql_val = SqlValue::SingleQuotedString("123".to_string());
1069        let v = sql_value_to_value(
1070            "a",
1071            &ConcreteDataType::int32_datatype(),
1072            &sql_val,
1073            None,
1074            None,
1075            false,
1076        );
1077        assert!(v.is_err());
1078
1079        // Test with an invalid numeric string but auto_string_to_numeric=true
1080        // Should return an error now with the new auto_cast_to_numeric behavior
1081        let sql_val = SqlValue::SingleQuotedString("not_a_number".to_string());
1082        let v = sql_value_to_value(
1083            "a",
1084            &ConcreteDataType::int32_datatype(),
1085            &sql_val,
1086            None,
1087            None,
1088            true,
1089        );
1090        assert!(v.is_err());
1091
1092        // Test with boolean type
1093        let sql_val = SqlValue::SingleQuotedString("true".to_string());
1094        let v = sql_value_to_value(
1095            "a",
1096            &ConcreteDataType::boolean_datatype(),
1097            &sql_val,
1098            None,
1099            None,
1100            true,
1101        )
1102        .unwrap();
1103        assert_eq!(Value::Boolean(true), v);
1104
1105        // Non-numeric types should still be handled normally
1106        let sql_val = SqlValue::SingleQuotedString("hello".to_string());
1107        let v = sql_value_to_value(
1108            "a",
1109            &ConcreteDataType::string_datatype(),
1110            &sql_val,
1111            None,
1112            None,
1113            true,
1114        );
1115        assert!(v.is_ok());
1116    }
1117
1118    #[test]
1119    fn test_sql_number_to_value_timestamp_strict_typing() {
1120        // Test that values are interpreted according to the target column type
1121        let timestamp_type = TimestampType::Millisecond(datatypes::types::TimestampMillisecondType);
1122        let data_type = ConcreteDataType::Timestamp(timestamp_type);
1123
1124        // Valid millisecond timestamp
1125        let millisecond_str = "1747814093865";
1126        let result = sql_number_to_value(&data_type, millisecond_str).unwrap();
1127        if let Value::Timestamp(ts) = result {
1128            assert_eq!(ts.unit(), TimeUnit::Millisecond);
1129            assert_eq!(ts.value(), 1747814093865);
1130        } else {
1131            panic!("Expected timestamp value");
1132        }
1133
1134        // Large value that would overflow when treated as milliseconds should be rejected
1135        let nanosecond_str = "1747814093865000000"; // This is too large for millisecond precision
1136        let result = sql_number_to_value(&data_type, nanosecond_str);
1137        assert!(
1138            result.is_err(),
1139            "Should reject overly large timestamp values"
1140        );
1141    }
1142
1143    #[test]
1144    fn test_sql_number_to_value_timestamp_different_units() {
1145        // Test second precision
1146        let second_type = TimestampType::Second(datatypes::types::TimestampSecondType);
1147        let second_data_type = ConcreteDataType::Timestamp(second_type);
1148
1149        let second_str = "1747814093";
1150        let result = sql_number_to_value(&second_data_type, second_str).unwrap();
1151        if let Value::Timestamp(ts) = result {
1152            assert_eq!(ts.unit(), TimeUnit::Second);
1153            assert_eq!(ts.value(), 1747814093);
1154        } else {
1155            panic!("Expected timestamp value");
1156        }
1157
1158        // Test nanosecond precision
1159        let nanosecond_type = TimestampType::Nanosecond(datatypes::types::TimestampNanosecondType);
1160        let nanosecond_data_type = ConcreteDataType::Timestamp(nanosecond_type);
1161
1162        let nanosecond_str = "1747814093865000000";
1163        let result = sql_number_to_value(&nanosecond_data_type, nanosecond_str).unwrap();
1164        if let Value::Timestamp(ts) = result {
1165            assert_eq!(ts.unit(), TimeUnit::Nanosecond);
1166            assert_eq!(ts.value(), 1747814093865000000);
1167        } else {
1168            panic!("Expected timestamp value");
1169        }
1170    }
1171
1172    #[test]
1173    fn test_timestamp_range_validation() {
1174        // Test that our range checking works correctly
1175        let nanosecond_value = 1747814093865000000i64; // This should be too large for millisecond
1176
1177        // This should work for nanosecond precision
1178        let nanosecond_type = TimestampType::Nanosecond(datatypes::types::TimestampNanosecondType);
1179        let nanosecond_data_type = ConcreteDataType::Timestamp(nanosecond_type);
1180        let result = sql_number_to_value(&nanosecond_data_type, "1747814093865000000");
1181        assert!(
1182            result.is_ok(),
1183            "Nanosecond value should be valid for nanosecond column"
1184        );
1185
1186        // This should fail for millisecond precision (value too large)
1187        let millisecond_type =
1188            TimestampType::Millisecond(datatypes::types::TimestampMillisecondType);
1189        let millisecond_data_type = ConcreteDataType::Timestamp(millisecond_type);
1190        let result = sql_number_to_value(&millisecond_data_type, "1747814093865000000");
1191        assert!(
1192            result.is_err(),
1193            "Nanosecond value should be rejected for millisecond column"
1194        );
1195
1196        // Verify the ranges work as expected
1197        assert!(
1198            nanosecond_value > Timestamp::MAX_MILLISECOND.value(),
1199            "Test value should exceed millisecond range"
1200        );
1201    }
1202}
common_sql/convert.rs

common_sql/
convert.rs
