common_sql/
convert.rs

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

common_sql/
convert.rs
