sql/
statements.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
15pub mod admin;
16pub mod alter;
17pub mod copy;
18pub mod create;
19pub mod cursor;
20pub mod delete;
21pub mod describe;
22pub mod drop;
23pub mod explain;
24pub mod insert;
25pub mod kill;
26mod option_map;
27pub mod query;
28pub mod set_variables;
29pub mod show;
30pub mod statement;
31pub mod tql;
32pub(crate) mod transform;
33pub mod truncate;
34
35use std::str::FromStr;
36
37use api::helper::ColumnDataTypeWrapper;
38use api::v1::SemanticType;
39use common_base::bytes::Bytes;
40use common_time::timezone::Timezone;
41use common_time::Timestamp;
42use datatypes::prelude::ConcreteDataType;
43use datatypes::schema::constraint::{CURRENT_TIMESTAMP, CURRENT_TIMESTAMP_FN};
44use datatypes::schema::{ColumnDefaultConstraint, ColumnSchema, COMMENT_KEY};
45use datatypes::types::{
46    cast, parse_string_to_json_type_value, parse_string_to_vector_type_value, TimestampType,
47};
48use datatypes::value::{OrderedF32, OrderedF64, Value};
49use snafu::{ensure, OptionExt, ResultExt};
50use sqlparser::ast::{ExactNumberInfo, Ident, ObjectName, UnaryOperator};
51
52use crate::ast::{
53    ColumnDef, ColumnOption, ColumnOptionDef, DataType as SqlDataType, Expr, TimezoneInfo,
54    Value as SqlValue,
55};
56use crate::error::{
57    self, ColumnTypeMismatchSnafu, ConvertSqlValueSnafu, ConvertStrSnafu,
58    ConvertToGrpcDataTypeSnafu, ConvertValueSnafu, DatatypeSnafu, InvalidCastSnafu,
59    InvalidSqlValueSnafu, InvalidUnaryOpSnafu, ParseSqlValueSnafu, Result,
60    SerializeColumnDefaultConstraintSnafu, SetFulltextOptionSnafu, SetSkippingIndexOptionSnafu,
61    TimestampOverflowSnafu, UnsupportedDefaultValueSnafu, UnsupportedUnaryOpSnafu,
62};
63use crate::statements::create::Column;
64pub use crate::statements::option_map::OptionMap;
65pub(crate) use crate::statements::transform::transform_statements;
66
67const VECTOR_TYPE_NAME: &str = "VECTOR";
68
69fn parse_string_to_value(
70    column_name: &str,
71    s: String,
72    data_type: &ConcreteDataType,
73    timezone: Option<&Timezone>,
74    auto_string_to_numeric: bool,
75) -> Result<Value> {
76    if auto_string_to_numeric {
77        if let Some(value) = auto_cast_to_numeric(&s, data_type)? {
78            return Ok(value);
79        }
80    }
81
82    ensure!(
83        data_type.is_stringifiable(),
84        ColumnTypeMismatchSnafu {
85            column_name,
86            expect: data_type.clone(),
87            actual: ConcreteDataType::string_datatype(),
88        }
89    );
90
91    match data_type {
92        ConcreteDataType::String(_) => Ok(Value::String(s.into())),
93        ConcreteDataType::Date(_) => {
94            if let Ok(date) = common_time::date::Date::from_str(&s, timezone) {
95                Ok(Value::Date(date))
96            } else {
97                ParseSqlValueSnafu {
98                    msg: format!("Failed to parse {s} to Date value"),
99                }
100                .fail()
101            }
102        }
103        ConcreteDataType::Timestamp(t) => {
104            if let Ok(ts) = Timestamp::from_str(&s, timezone) {
105                Ok(Value::Timestamp(ts.convert_to(t.unit()).context(
106                    TimestampOverflowSnafu {
107                        timestamp: ts,
108                        target_unit: t.unit(),
109                    },
110                )?))
111            } else if let Ok(ts) = i64::from_str(s.as_str()) {
112                Ok(Value::Timestamp(Timestamp::new(ts, t.unit())))
113            } else {
114                ParseSqlValueSnafu {
115                    msg: format!("Failed to parse {s} to Timestamp value"),
116                }
117                .fail()
118            }
119        }
120        ConcreteDataType::Decimal128(_) => {
121            if let Ok(val) = common_decimal::Decimal128::from_str(&s) {
122                Ok(Value::Decimal128(val))
123            } else {
124                ParseSqlValueSnafu {
125                    msg: format!("Fail to parse number {s} to Decimal128 value"),
126                }
127                .fail()
128            }
129        }
130        ConcreteDataType::Binary(_) => Ok(Value::Binary(s.as_bytes().into())),
131        ConcreteDataType::Json(j) => {
132            let v = parse_string_to_json_type_value(&s, &j.format).context(DatatypeSnafu)?;
133            Ok(Value::Binary(v.into()))
134        }
135        ConcreteDataType::Vector(d) => {
136            let v = parse_string_to_vector_type_value(&s, Some(d.dim)).context(DatatypeSnafu)?;
137            Ok(Value::Binary(v.into()))
138        }
139        _ => ParseSqlValueSnafu {
140            msg: format!("Failed to parse {s} to {data_type} value"),
141        }
142        .fail(),
143    }
144}
145
146/// Casts string to value of specified numeric data type.
147/// If the string cannot be parsed, returns an error.
148///
149/// Returns None if the data type doesn't support auto casting.
150fn auto_cast_to_numeric(s: &str, data_type: &ConcreteDataType) -> Result<Option<Value>> {
151    let value = match data_type {
152        ConcreteDataType::Boolean(_) => s.parse::<bool>().map(Value::Boolean).ok(),
153        ConcreteDataType::Int8(_) => s.parse::<i8>().map(Value::Int8).ok(),
154        ConcreteDataType::Int16(_) => s.parse::<i16>().map(Value::Int16).ok(),
155        ConcreteDataType::Int32(_) => s.parse::<i32>().map(Value::Int32).ok(),
156        ConcreteDataType::Int64(_) => s.parse::<i64>().map(Value::Int64).ok(),
157        ConcreteDataType::UInt8(_) => s.parse::<u8>().map(Value::UInt8).ok(),
158        ConcreteDataType::UInt16(_) => s.parse::<u16>().map(Value::UInt16).ok(),
159        ConcreteDataType::UInt32(_) => s.parse::<u32>().map(Value::UInt32).ok(),
160        ConcreteDataType::UInt64(_) => s.parse::<u64>().map(Value::UInt64).ok(),
161        ConcreteDataType::Float32(_) => s
162            .parse::<f32>()
163            .map(|v| Value::Float32(OrderedF32::from(v)))
164            .ok(),
165        ConcreteDataType::Float64(_) => s
166            .parse::<f64>()
167            .map(|v| Value::Float64(OrderedF64::from(v)))
168            .ok(),
169        _ => return Ok(None),
170    };
171
172    match value {
173        Some(value) => Ok(Some(value)),
174        None => ConvertStrSnafu {
175            value: s,
176            datatype: data_type.clone(),
177        }
178        .fail(),
179    }
180}
181
182fn parse_hex_string(s: &str) -> Result<Value> {
183    match hex::decode(s) {
184        Ok(b) => Ok(Value::Binary(Bytes::from(b))),
185        Err(hex::FromHexError::InvalidHexCharacter { c, index }) => ParseSqlValueSnafu {
186            msg: format!(
187                "Fail to parse hex string to Byte: invalid character {c:?} at position {index}"
188            ),
189        }
190        .fail(),
191        Err(hex::FromHexError::OddLength) => ParseSqlValueSnafu {
192            msg: "Fail to parse hex string to Byte: odd number of digits".to_string(),
193        }
194        .fail(),
195        Err(e) => ParseSqlValueSnafu {
196            msg: format!("Fail to parse hex string to Byte {s}, {e:?}"),
197        }
198        .fail(),
199    }
200}
201
202macro_rules! parse_number_to_value {
203    ($data_type: expr, $n: ident,  $(($Type: ident, $PrimitiveType: ident, $Target: ident)), +) => {
204        match $data_type {
205            $(
206                ConcreteDataType::$Type(_) => {
207                    let n  = parse_sql_number::<$PrimitiveType>($n)?;
208                    Ok(Value::$Type($Target::from(n)))
209                },
210            )+
211            ConcreteDataType::Timestamp(t) => {
212                let n  = parse_sql_number::<i64>($n)?;
213                Ok(Value::Timestamp(Timestamp::new(n, t.unit())))
214            },
215            // TODO(QuenKar): This could need to be optimized
216            // if this from_str function is slow,
217            // we can implement parse decimal string with precision and scale manually.
218            ConcreteDataType::Decimal128(_) => {
219                if let Ok(val) = common_decimal::Decimal128::from_str($n) {
220                    Ok(Value::Decimal128(val))
221                } else {
222                    ParseSqlValueSnafu {
223                        msg: format!("Fail to parse number {}, invalid column type: {:?}",
224                                        $n, $data_type)
225                    }.fail()
226                }
227            }
228            // It's valid for MySQL JDBC to send "0" and "1" for boolean types, so adapt to that.
229            ConcreteDataType::Boolean(_) => {
230                match $n {
231                    "0" => Ok(Value::Boolean(false)),
232                    "1" => Ok(Value::Boolean(true)),
233                    _ => ParseSqlValueSnafu {
234                        msg: format!("Failed to parse number '{}' to boolean column type", $n)}.fail(),
235                }
236            }
237            _ => ParseSqlValueSnafu {
238                msg: format!("Fail to parse number {}, invalid column type: {:?}",
239                                $n, $data_type
240                )}.fail(),
241        }
242    }
243}
244
245/// Convert a sql value into datatype's value
246pub fn sql_number_to_value(data_type: &ConcreteDataType, n: &str) -> Result<Value> {
247    parse_number_to_value!(
248        data_type,
249        n,
250        (UInt8, u8, u8),
251        (UInt16, u16, u16),
252        (UInt32, u32, u32),
253        (UInt64, u64, u64),
254        (Int8, i8, i8),
255        (Int16, i16, i16),
256        (Int32, i32, i32),
257        (Int64, i64, i64),
258        (Float64, f64, OrderedF64),
259        (Float32, f32, OrderedF32)
260    )
261    // TODO(hl): also Date/DateTime
262}
263
264pub(crate) fn parse_sql_number<R: FromStr + std::fmt::Debug>(n: &str) -> Result<R>
265where
266    <R as FromStr>::Err: std::fmt::Debug,
267{
268    match n.parse::<R>() {
269        Ok(n) => Ok(n),
270        Err(e) => ParseSqlValueSnafu {
271            msg: format!("Fail to parse number {n}, {e:?}"),
272        }
273        .fail(),
274    }
275}
276
277/// Converts SQL value to value according to the data type.
278/// If `auto_string_to_numeric` is true, tries to cast the string value to numeric values,
279/// and returns error if the cast fails.
280pub fn sql_value_to_value(
281    column_name: &str,
282    data_type: &ConcreteDataType,
283    sql_val: &SqlValue,
284    timezone: Option<&Timezone>,
285    unary_op: Option<UnaryOperator>,
286    auto_string_to_numeric: bool,
287) -> Result<Value> {
288    let mut value = match sql_val {
289        SqlValue::Number(n, _) => sql_number_to_value(data_type, n)?,
290        SqlValue::Null => Value::Null,
291        SqlValue::Boolean(b) => {
292            ensure!(
293                data_type.is_boolean(),
294                ColumnTypeMismatchSnafu {
295                    column_name,
296                    expect: data_type.clone(),
297                    actual: ConcreteDataType::boolean_datatype(),
298                }
299            );
300
301            (*b).into()
302        }
303        SqlValue::DoubleQuotedString(s) | SqlValue::SingleQuotedString(s) => parse_string_to_value(
304            column_name,
305            s.clone(),
306            data_type,
307            timezone,
308            auto_string_to_numeric,
309        )?,
310        SqlValue::HexStringLiteral(s) => {
311            // Should not directly write binary into json column
312            ensure!(
313                !matches!(data_type, ConcreteDataType::Json(_)),
314                ColumnTypeMismatchSnafu {
315                    column_name,
316                    expect: ConcreteDataType::binary_datatype(),
317                    actual: ConcreteDataType::json_datatype(),
318                }
319            );
320
321            parse_hex_string(s)?
322        }
323        SqlValue::Placeholder(s) => return InvalidSqlValueSnafu { value: s }.fail(),
324
325        // TODO(dennis): supports binary string
326        _ => {
327            return ConvertSqlValueSnafu {
328                value: sql_val.clone(),
329                datatype: data_type.clone(),
330            }
331            .fail()
332        }
333    };
334
335    if let Some(unary_op) = unary_op {
336        match unary_op {
337            UnaryOperator::Plus | UnaryOperator::Minus | UnaryOperator::Not => {}
338            _ => {
339                return UnsupportedUnaryOpSnafu { unary_op }.fail();
340            }
341        }
342
343        match value {
344            Value::Null => {}
345            Value::Boolean(bool) => match unary_op {
346                UnaryOperator::Not => value = Value::Boolean(!bool),
347                _ => {
348                    return InvalidUnaryOpSnafu { unary_op, value }.fail();
349                }
350            },
351            Value::UInt8(_)
352            | Value::UInt16(_)
353            | Value::UInt32(_)
354            | Value::UInt64(_)
355            | Value::Int8(_)
356            | Value::Int16(_)
357            | Value::Int32(_)
358            | Value::Int64(_)
359            | Value::Float32(_)
360            | Value::Float64(_)
361            | Value::Decimal128(_)
362            | Value::Date(_)
363            | Value::Timestamp(_)
364            | Value::Time(_)
365            | Value::Duration(_)
366            | Value::IntervalYearMonth(_)
367            | Value::IntervalDayTime(_)
368            | Value::IntervalMonthDayNano(_) => match unary_op {
369                UnaryOperator::Plus => {}
370                UnaryOperator::Minus => {
371                    value = value
372                        .try_negative()
373                        .with_context(|| InvalidUnaryOpSnafu { unary_op, value })?;
374                }
375                _ => return InvalidUnaryOpSnafu { unary_op, value }.fail(),
376            },
377
378            Value::String(_) | Value::Binary(_) | Value::List(_) => {
379                return InvalidUnaryOpSnafu { unary_op, value }.fail()
380            }
381        }
382    }
383
384    if value.data_type() != *data_type {
385        cast(value, data_type).with_context(|_| InvalidCastSnafu {
386            sql_value: sql_val.clone(),
387            datatype: data_type,
388        })
389    } else {
390        Ok(value)
391    }
392}
393
394pub fn value_to_sql_value(val: &Value) -> Result<SqlValue> {
395    Ok(match val {
396        Value::Int8(v) => SqlValue::Number(v.to_string(), false),
397        Value::UInt8(v) => SqlValue::Number(v.to_string(), false),
398        Value::Int16(v) => SqlValue::Number(v.to_string(), false),
399        Value::UInt16(v) => SqlValue::Number(v.to_string(), false),
400        Value::Int32(v) => SqlValue::Number(v.to_string(), false),
401        Value::UInt32(v) => SqlValue::Number(v.to_string(), false),
402        Value::Int64(v) => SqlValue::Number(v.to_string(), false),
403        Value::UInt64(v) => SqlValue::Number(v.to_string(), false),
404        Value::Float32(v) => SqlValue::Number(v.to_string(), false),
405        Value::Float64(v) => SqlValue::Number(v.to_string(), false),
406        Value::Boolean(b) => SqlValue::Boolean(*b),
407        Value::Date(d) => SqlValue::SingleQuotedString(d.to_string()),
408        Value::Timestamp(ts) => SqlValue::SingleQuotedString(ts.to_iso8601_string()),
409        Value::String(s) => SqlValue::SingleQuotedString(s.as_utf8().to_string()),
410        Value::Null => SqlValue::Null,
411        // TODO(dennis): supports binary
412        _ => return ConvertValueSnafu { value: val.clone() }.fail(),
413    })
414}
415
416fn parse_column_default_constraint(
417    column_name: &str,
418    data_type: &ConcreteDataType,
419    opts: &[ColumnOptionDef],
420    timezone: Option<&Timezone>,
421) -> Result<Option<ColumnDefaultConstraint>> {
422    if let Some(opt) = opts
423        .iter()
424        .find(|o| matches!(o.option, ColumnOption::Default(_)))
425    {
426        let default_constraint = match &opt.option {
427            ColumnOption::Default(Expr::Value(v)) => ColumnDefaultConstraint::Value(
428                sql_value_to_value(column_name, data_type, v, timezone, None, false)?,
429            ),
430            ColumnOption::Default(Expr::Function(func)) => {
431                let mut func = format!("{func}").to_lowercase();
432                // normalize CURRENT_TIMESTAMP to CURRENT_TIMESTAMP()
433                if func == CURRENT_TIMESTAMP {
434                    func = CURRENT_TIMESTAMP_FN.to_string();
435                }
436                // Always use lowercase for function expression
437                ColumnDefaultConstraint::Function(func.to_lowercase())
438            }
439
440            ColumnOption::Default(Expr::UnaryOp { op, expr }) => {
441                // Specialized process for handling numerical inputs to prevent
442                // overflow errors during the parsing of negative numbers,
443                // See https://github.com/GreptimeTeam/greptimedb/issues/4351
444                if let (UnaryOperator::Minus, Expr::Value(SqlValue::Number(n, _))) =
445                    (op, expr.as_ref())
446                {
447                    return Ok(Some(ColumnDefaultConstraint::Value(sql_number_to_value(
448                        data_type,
449                        &format!("-{n}"),
450                    )?)));
451                }
452
453                if let Expr::Value(v) = &**expr {
454                    let value =
455                        sql_value_to_value(column_name, data_type, v, timezone, Some(*op), false)?;
456                    ColumnDefaultConstraint::Value(value)
457                } else {
458                    return UnsupportedDefaultValueSnafu {
459                        column_name,
460                        expr: *expr.clone(),
461                    }
462                    .fail();
463                }
464            }
465            ColumnOption::Default(others) => {
466                return UnsupportedDefaultValueSnafu {
467                    column_name,
468                    expr: others.clone(),
469                }
470                .fail();
471            }
472            _ => {
473                return UnsupportedDefaultValueSnafu {
474                    column_name,
475                    expr: Expr::Value(SqlValue::Null),
476                }
477                .fail();
478            }
479        };
480
481        Ok(Some(default_constraint))
482    } else {
483        Ok(None)
484    }
485}
486
487/// Return true when the `ColumnDef` options contain primary key
488pub fn has_primary_key_option(column_def: &ColumnDef) -> bool {
489    column_def
490        .options
491        .iter()
492        .any(|options| match options.option {
493            ColumnOption::Unique { is_primary, .. } => is_primary,
494            _ => false,
495        })
496}
497
498/// Create a `ColumnSchema` from `Column`.
499pub fn column_to_schema(
500    column: &Column,
501    time_index: &str,
502    timezone: Option<&Timezone>,
503) -> Result<ColumnSchema> {
504    let is_time_index = column.name().value == time_index;
505
506    let is_nullable = column
507        .options()
508        .iter()
509        .all(|o| !matches!(o.option, ColumnOption::NotNull))
510        && !is_time_index;
511
512    let name = column.name().value.clone();
513    let data_type = sql_data_type_to_concrete_data_type(column.data_type())?;
514    let default_constraint =
515        parse_column_default_constraint(&name, &data_type, column.options(), timezone)?;
516
517    let mut column_schema = ColumnSchema::new(name, data_type, is_nullable)
518        .with_time_index(is_time_index)
519        .with_default_constraint(default_constraint)
520        .context(error::InvalidDefaultSnafu {
521            column: &column.name().value,
522        })?;
523
524    if let Some(ColumnOption::Comment(c)) = column.options().iter().find_map(|o| {
525        if matches!(o.option, ColumnOption::Comment(_)) {
526            Some(&o.option)
527        } else {
528            None
529        }
530    }) {
531        let _ = column_schema
532            .mut_metadata()
533            .insert(COMMENT_KEY.to_string(), c.to_string());
534    }
535
536    if let Some(options) = column.extensions.build_fulltext_options()? {
537        column_schema = column_schema
538            .with_fulltext_options(options)
539            .context(SetFulltextOptionSnafu)?;
540    }
541
542    if let Some(options) = column.extensions.build_skipping_index_options()? {
543        column_schema = column_schema
544            .with_skipping_options(options)
545            .context(SetSkippingIndexOptionSnafu)?;
546    }
547
548    column_schema.set_inverted_index(column.extensions.inverted_index_options.is_some());
549
550    Ok(column_schema)
551}
552
553/// Convert `ColumnDef` in sqlparser to `ColumnDef` in gRPC proto.
554pub fn sql_column_def_to_grpc_column_def(
555    col: &ColumnDef,
556    timezone: Option<&Timezone>,
557) -> Result<api::v1::ColumnDef> {
558    let name = col.name.value.clone();
559    let data_type = sql_data_type_to_concrete_data_type(&col.data_type)?;
560
561    let is_nullable = col
562        .options
563        .iter()
564        .all(|o| !matches!(o.option, ColumnOption::NotNull));
565
566    let default_constraint =
567        parse_column_default_constraint(&name, &data_type, &col.options, timezone)?
568            .map(ColumnDefaultConstraint::try_into) // serialize default constraint to bytes
569            .transpose()
570            .context(SerializeColumnDefaultConstraintSnafu)?;
571    // convert ConcreteDataType to grpc ColumnDataTypeWrapper
572    let (datatype, datatype_ext) = ColumnDataTypeWrapper::try_from(data_type.clone())
573        .context(ConvertToGrpcDataTypeSnafu)?
574        .to_parts();
575
576    let is_primary_key = col.options.iter().any(|o| {
577        matches!(
578            o.option,
579            ColumnOption::Unique {
580                is_primary: true,
581                ..
582            }
583        )
584    });
585
586    let semantic_type = if is_primary_key {
587        SemanticType::Tag
588    } else {
589        SemanticType::Field
590    };
591
592    Ok(api::v1::ColumnDef {
593        name,
594        data_type: datatype as i32,
595        is_nullable,
596        default_constraint: default_constraint.unwrap_or_default(),
597        semantic_type: semantic_type as _,
598        comment: String::new(),
599        datatype_extension: datatype_ext,
600        options: None,
601    })
602}
603
604pub fn sql_data_type_to_concrete_data_type(data_type: &SqlDataType) -> Result<ConcreteDataType> {
605    match data_type {
606        SqlDataType::BigInt(_) | SqlDataType::Int64 => Ok(ConcreteDataType::int64_datatype()),
607        SqlDataType::UnsignedBigInt(_) => Ok(ConcreteDataType::uint64_datatype()),
608        SqlDataType::Int(_) | SqlDataType::Integer(_) => Ok(ConcreteDataType::int32_datatype()),
609        SqlDataType::UnsignedInt(_) | SqlDataType::UnsignedInteger(_) => {
610            Ok(ConcreteDataType::uint32_datatype())
611        }
612        SqlDataType::SmallInt(_) => Ok(ConcreteDataType::int16_datatype()),
613        SqlDataType::UnsignedSmallInt(_) => Ok(ConcreteDataType::uint16_datatype()),
614        SqlDataType::TinyInt(_) | SqlDataType::Int8(_) => Ok(ConcreteDataType::int8_datatype()),
615        SqlDataType::UnsignedTinyInt(_) | SqlDataType::UnsignedInt8(_) => {
616            Ok(ConcreteDataType::uint8_datatype())
617        }
618        SqlDataType::Char(_)
619        | SqlDataType::Varchar(_)
620        | SqlDataType::Text
621        | SqlDataType::TinyText
622        | SqlDataType::MediumText
623        | SqlDataType::LongText
624        | SqlDataType::String(_) => Ok(ConcreteDataType::string_datatype()),
625        SqlDataType::Float(_) => Ok(ConcreteDataType::float32_datatype()),
626        SqlDataType::Double(_) | SqlDataType::Float64 => Ok(ConcreteDataType::float64_datatype()),
627        SqlDataType::Boolean => Ok(ConcreteDataType::boolean_datatype()),
628        SqlDataType::Date => Ok(ConcreteDataType::date_datatype()),
629        SqlDataType::Binary(_)
630        | SqlDataType::Blob(_)
631        | SqlDataType::Bytea
632        | SqlDataType::Varbinary(_) => Ok(ConcreteDataType::binary_datatype()),
633        SqlDataType::Datetime(_) => Ok(ConcreteDataType::timestamp_microsecond_datatype()),
634        SqlDataType::Timestamp(precision, _) => Ok(precision
635            .as_ref()
636            .map(|v| TimestampType::try_from(*v))
637            .transpose()
638            .map_err(|_| {
639                error::SqlTypeNotSupportedSnafu {
640                    t: data_type.clone(),
641                }
642                .build()
643            })?
644            .map(|t| ConcreteDataType::timestamp_datatype(t.unit()))
645            .unwrap_or(ConcreteDataType::timestamp_millisecond_datatype())),
646        SqlDataType::Interval => Ok(ConcreteDataType::interval_month_day_nano_datatype()),
647        SqlDataType::Decimal(exact_info) => match exact_info {
648            ExactNumberInfo::None => Ok(ConcreteDataType::decimal128_default_datatype()),
649            // refer to https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html
650            // In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0).
651            ExactNumberInfo::Precision(p) => Ok(ConcreteDataType::decimal128_datatype(*p as u8, 0)),
652            ExactNumberInfo::PrecisionAndScale(p, s) => {
653                Ok(ConcreteDataType::decimal128_datatype(*p as u8, *s as i8))
654            }
655        },
656        SqlDataType::JSON => Ok(ConcreteDataType::json_datatype()),
657        // Vector type
658        SqlDataType::Custom(name, d)
659            if name.0.as_slice().len() == 1
660                && name.0.as_slice()[0].value.to_ascii_uppercase() == VECTOR_TYPE_NAME
661                && d.len() == 1 =>
662        {
663            let dim = d[0].parse().map_err(|e| {
664                error::ParseSqlValueSnafu {
665                    msg: format!("Failed to parse vector dimension: {}", e),
666                }
667                .build()
668            })?;
669            Ok(ConcreteDataType::vector_datatype(dim))
670        }
671        _ => error::SqlTypeNotSupportedSnafu {
672            t: data_type.clone(),
673        }
674        .fail(),
675    }
676}
677
678pub fn concrete_data_type_to_sql_data_type(data_type: &ConcreteDataType) -> Result<SqlDataType> {
679    match data_type {
680        ConcreteDataType::Int64(_) => Ok(SqlDataType::BigInt(None)),
681        ConcreteDataType::UInt64(_) => Ok(SqlDataType::UnsignedBigInt(None)),
682        ConcreteDataType::Int32(_) => Ok(SqlDataType::Int(None)),
683        ConcreteDataType::UInt32(_) => Ok(SqlDataType::UnsignedInt(None)),
684        ConcreteDataType::Int16(_) => Ok(SqlDataType::SmallInt(None)),
685        ConcreteDataType::UInt16(_) => Ok(SqlDataType::UnsignedSmallInt(None)),
686        ConcreteDataType::Int8(_) => Ok(SqlDataType::TinyInt(None)),
687        ConcreteDataType::UInt8(_) => Ok(SqlDataType::UnsignedTinyInt(None)),
688        ConcreteDataType::String(_) => Ok(SqlDataType::String(None)),
689        ConcreteDataType::Float32(_) => Ok(SqlDataType::Float(None)),
690        ConcreteDataType::Float64(_) => Ok(SqlDataType::Double(ExactNumberInfo::None)),
691        ConcreteDataType::Boolean(_) => Ok(SqlDataType::Boolean),
692        ConcreteDataType::Date(_) => Ok(SqlDataType::Date),
693        ConcreteDataType::Timestamp(ts_type) => Ok(SqlDataType::Timestamp(
694            Some(ts_type.precision()),
695            TimezoneInfo::None,
696        )),
697        ConcreteDataType::Time(time_type) => Ok(SqlDataType::Time(
698            Some(time_type.precision()),
699            TimezoneInfo::None,
700        )),
701        ConcreteDataType::Interval(_) => Ok(SqlDataType::Interval),
702        ConcreteDataType::Binary(_) => Ok(SqlDataType::Varbinary(None)),
703        ConcreteDataType::Decimal128(d) => Ok(SqlDataType::Decimal(
704            ExactNumberInfo::PrecisionAndScale(d.precision() as u64, d.scale() as u64),
705        )),
706        ConcreteDataType::Json(_) => Ok(SqlDataType::JSON),
707        ConcreteDataType::Vector(v) => Ok(SqlDataType::Custom(
708            ObjectName(vec![Ident::new(VECTOR_TYPE_NAME)]),
709            vec![v.dim.to_string()],
710        )),
711        ConcreteDataType::Duration(_)
712        | ConcreteDataType::Null(_)
713        | ConcreteDataType::List(_)
714        | ConcreteDataType::Dictionary(_) => error::ConcreteTypeNotSupportedSnafu {
715            t: data_type.clone(),
716        }
717        .fail(),
718    }
719}
720
721#[cfg(test)]
722mod tests {
723    use std::assert_matches::assert_matches;
724    use std::collections::HashMap;
725
726    use api::v1::ColumnDataType;
727    use common_time::timestamp::TimeUnit;
728    use datatypes::schema::{
729        FulltextAnalyzer, COLUMN_FULLTEXT_OPT_KEY_ANALYZER, COLUMN_FULLTEXT_OPT_KEY_CASE_SENSITIVE,
730    };
731    use datatypes::types::BooleanType;
732    use datatypes::value::OrderedFloat;
733
734    use super::*;
735    use crate::ast::TimezoneInfo;
736    use crate::statements::create::ColumnExtensions;
737    use crate::statements::ColumnOption;
738
739    fn check_type(sql_type: SqlDataType, data_type: ConcreteDataType) {
740        assert_eq!(
741            data_type,
742            sql_data_type_to_concrete_data_type(&sql_type).unwrap()
743        );
744    }
745
746    #[test]
747    pub fn test_sql_data_type_to_concrete_data_type() {
748        check_type(
749            SqlDataType::BigInt(None),
750            ConcreteDataType::int64_datatype(),
751        );
752        check_type(SqlDataType::Int(None), ConcreteDataType::int32_datatype());
753        check_type(
754            SqlDataType::Integer(None),
755            ConcreteDataType::int32_datatype(),
756        );
757        check_type(
758            SqlDataType::SmallInt(None),
759            ConcreteDataType::int16_datatype(),
760        );
761        check_type(SqlDataType::Char(None), ConcreteDataType::string_datatype());
762        check_type(
763            SqlDataType::Varchar(None),
764            ConcreteDataType::string_datatype(),
765        );
766        check_type(SqlDataType::Text, ConcreteDataType::string_datatype());
767        check_type(
768            SqlDataType::String(None),
769            ConcreteDataType::string_datatype(),
770        );
771        check_type(
772            SqlDataType::Float(None),
773            ConcreteDataType::float32_datatype(),
774        );
775        check_type(
776            SqlDataType::Double(ExactNumberInfo::None),
777            ConcreteDataType::float64_datatype(),
778        );
779        check_type(SqlDataType::Boolean, ConcreteDataType::boolean_datatype());
780        check_type(SqlDataType::Date, ConcreteDataType::date_datatype());
781        check_type(
782            SqlDataType::Timestamp(None, TimezoneInfo::None),
783            ConcreteDataType::timestamp_millisecond_datatype(),
784        );
785        check_type(
786            SqlDataType::Varbinary(None),
787            ConcreteDataType::binary_datatype(),
788        );
789        check_type(
790            SqlDataType::UnsignedBigInt(None),
791            ConcreteDataType::uint64_datatype(),
792        );
793        check_type(
794            SqlDataType::UnsignedInt(None),
795            ConcreteDataType::uint32_datatype(),
796        );
797        check_type(
798            SqlDataType::UnsignedSmallInt(None),
799            ConcreteDataType::uint16_datatype(),
800        );
801        check_type(
802            SqlDataType::UnsignedTinyInt(None),
803            ConcreteDataType::uint8_datatype(),
804        );
805        check_type(
806            SqlDataType::Datetime(None),
807            ConcreteDataType::timestamp_microsecond_datatype(),
808        );
809        check_type(
810            SqlDataType::Interval,
811            ConcreteDataType::interval_month_day_nano_datatype(),
812        );
813        check_type(SqlDataType::JSON, ConcreteDataType::json_datatype());
814        check_type(
815            SqlDataType::Custom(
816                ObjectName(vec![Ident::new(VECTOR_TYPE_NAME)]),
817                vec!["3".to_string()],
818            ),
819            ConcreteDataType::vector_datatype(3),
820        );
821    }
822
823    #[test]
824    fn test_sql_number_to_value() {
825        let v = sql_number_to_value(&ConcreteDataType::float64_datatype(), "3.0").unwrap();
826        assert_eq!(Value::Float64(OrderedFloat(3.0)), v);
827
828        let v = sql_number_to_value(&ConcreteDataType::int32_datatype(), "999").unwrap();
829        assert_eq!(Value::Int32(999), v);
830
831        let v = sql_number_to_value(
832            &ConcreteDataType::timestamp_nanosecond_datatype(),
833            "1073741821",
834        )
835        .unwrap();
836        assert_eq!(Value::Timestamp(Timestamp::new_nanosecond(1073741821)), v);
837
838        let v = sql_number_to_value(
839            &ConcreteDataType::timestamp_millisecond_datatype(),
840            "999999",
841        )
842        .unwrap();
843        assert_eq!(Value::Timestamp(Timestamp::new_millisecond(999999)), v);
844
845        let v = sql_number_to_value(&ConcreteDataType::string_datatype(), "999");
846        assert!(v.is_err(), "parse value error is: {v:?}");
847
848        let v = sql_number_to_value(&ConcreteDataType::boolean_datatype(), "0").unwrap();
849        assert_eq!(v, Value::Boolean(false));
850        let v = sql_number_to_value(&ConcreteDataType::boolean_datatype(), "1").unwrap();
851        assert_eq!(v, Value::Boolean(true));
852        assert!(sql_number_to_value(&ConcreteDataType::boolean_datatype(), "2").is_err());
853    }
854
855    #[test]
856    fn test_sql_value_to_value() {
857        let sql_val = SqlValue::Null;
858        assert_eq!(
859            Value::Null,
860            sql_value_to_value(
861                "a",
862                &ConcreteDataType::float64_datatype(),
863                &sql_val,
864                None,
865                None,
866                false
867            )
868            .unwrap()
869        );
870
871        let sql_val = SqlValue::Boolean(true);
872        assert_eq!(
873            Value::Boolean(true),
874            sql_value_to_value(
875                "a",
876                &ConcreteDataType::boolean_datatype(),
877                &sql_val,
878                None,
879                None,
880                false
881            )
882            .unwrap()
883        );
884
885        let sql_val = SqlValue::Number("3.0".to_string(), false);
886        assert_eq!(
887            Value::Float64(OrderedFloat(3.0)),
888            sql_value_to_value(
889                "a",
890                &ConcreteDataType::float64_datatype(),
891                &sql_val,
892                None,
893                None,
894                false
895            )
896            .unwrap()
897        );
898
899        let sql_val = SqlValue::Number("3.0".to_string(), false);
900        let v = sql_value_to_value(
901            "a",
902            &ConcreteDataType::boolean_datatype(),
903            &sql_val,
904            None,
905            None,
906            false,
907        );
908        assert!(v.is_err());
909        assert!(format!("{v:?}").contains("Failed to parse number '3.0' to boolean column type"));
910
911        let sql_val = SqlValue::Boolean(true);
912        let v = sql_value_to_value(
913            "a",
914            &ConcreteDataType::float64_datatype(),
915            &sql_val,
916            None,
917            None,
918            false,
919        );
920        assert!(v.is_err());
921        assert!(
922            format!("{v:?}").contains(
923                "Column a expect type: Float64(Float64Type), actual: Boolean(BooleanType)"
924            ),
925            "v is {v:?}",
926        );
927
928        let sql_val = SqlValue::HexStringLiteral("48656c6c6f20776f726c6421".to_string());
929        let v = sql_value_to_value(
930            "a",
931            &ConcreteDataType::binary_datatype(),
932            &sql_val,
933            None,
934            None,
935            false,
936        )
937        .unwrap();
938        assert_eq!(Value::Binary(Bytes::from(b"Hello world!".as_slice())), v);
939
940        let sql_val = SqlValue::DoubleQuotedString("MorningMyFriends".to_string());
941        let v = sql_value_to_value(
942            "a",
943            &ConcreteDataType::binary_datatype(),
944            &sql_val,
945            None,
946            None,
947            false,
948        )
949        .unwrap();
950        assert_eq!(
951            Value::Binary(Bytes::from(b"MorningMyFriends".as_slice())),
952            v
953        );
954
955        let sql_val = SqlValue::HexStringLiteral("9AF".to_string());
956        let v = sql_value_to_value(
957            "a",
958            &ConcreteDataType::binary_datatype(),
959            &sql_val,
960            None,
961            None,
962            false,
963        );
964        assert!(v.is_err());
965        assert!(
966            format!("{v:?}").contains("odd number of digits"),
967            "v is {v:?}"
968        );
969
970        let sql_val = SqlValue::HexStringLiteral("AG".to_string());
971        let v = sql_value_to_value(
972            "a",
973            &ConcreteDataType::binary_datatype(),
974            &sql_val,
975            None,
976            None,
977            false,
978        );
979        assert!(v.is_err());
980        assert!(format!("{v:?}").contains("invalid character"), "v is {v:?}",);
981
982        let sql_val = SqlValue::DoubleQuotedString("MorningMyFriends".to_string());
983        let v = sql_value_to_value(
984            "a",
985            &ConcreteDataType::json_datatype(),
986            &sql_val,
987            None,
988            None,
989            false,
990        );
991        assert!(v.is_err());
992
993        let sql_val = SqlValue::DoubleQuotedString(r#"{"a":"b"}"#.to_string());
994        let v = sql_value_to_value(
995            "a",
996            &ConcreteDataType::json_datatype(),
997            &sql_val,
998            None,
999            None,
1000            false,
1001        )
1002        .unwrap();
1003        assert_eq!(
1004            Value::Binary(Bytes::from(
1005                jsonb::parse_value(r#"{"a":"b"}"#.as_bytes())
1006                    .unwrap()
1007                    .to_vec()
1008                    .as_slice()
1009            )),
1010            v
1011        );
1012    }
1013
1014    #[test]
1015    fn test_parse_date_literal() {
1016        let value = sql_value_to_value(
1017            "date",
1018            &ConcreteDataType::date_datatype(),
1019            &SqlValue::DoubleQuotedString("2022-02-22".to_string()),
1020            None,
1021            None,
1022            false,
1023        )
1024        .unwrap();
1025        assert_eq!(ConcreteDataType::date_datatype(), value.data_type());
1026        if let Value::Date(d) = value {
1027            assert_eq!("2022-02-22", d.to_string());
1028        } else {
1029            unreachable!()
1030        }
1031
1032        // with timezone
1033        let value = sql_value_to_value(
1034            "date",
1035            &ConcreteDataType::date_datatype(),
1036            &SqlValue::DoubleQuotedString("2022-02-22".to_string()),
1037            Some(&Timezone::from_tz_string("+07:00").unwrap()),
1038            None,
1039            false,
1040        )
1041        .unwrap();
1042        assert_eq!(ConcreteDataType::date_datatype(), value.data_type());
1043        if let Value::Date(d) = value {
1044            assert_eq!("2022-02-21", d.to_string());
1045        } else {
1046            unreachable!()
1047        }
1048    }
1049
1050    #[test]
1051    fn test_parse_timestamp_literal() {
1052        match parse_string_to_value(
1053            "timestamp_col",
1054            "2022-02-22T00:01:01+08:00".to_string(),
1055            &ConcreteDataType::timestamp_millisecond_datatype(),
1056            None,
1057            false,
1058        )
1059        .unwrap()
1060        {
1061            Value::Timestamp(ts) => {
1062                assert_eq!(1645459261000, ts.value());
1063                assert_eq!(TimeUnit::Millisecond, ts.unit());
1064            }
1065            _ => {
1066                unreachable!()
1067            }
1068        }
1069
1070        match parse_string_to_value(
1071            "timestamp_col",
1072            "2022-02-22T00:01:01+08:00".to_string(),
1073            &ConcreteDataType::timestamp_datatype(TimeUnit::Second),
1074            None,
1075            false,
1076        )
1077        .unwrap()
1078        {
1079            Value::Timestamp(ts) => {
1080                assert_eq!(1645459261, ts.value());
1081                assert_eq!(TimeUnit::Second, ts.unit());
1082            }
1083            _ => {
1084                unreachable!()
1085            }
1086        }
1087
1088        match parse_string_to_value(
1089            "timestamp_col",
1090            "2022-02-22T00:01:01+08:00".to_string(),
1091            &ConcreteDataType::timestamp_datatype(TimeUnit::Microsecond),
1092            None,
1093            false,
1094        )
1095        .unwrap()
1096        {
1097            Value::Timestamp(ts) => {
1098                assert_eq!(1645459261000000, ts.value());
1099                assert_eq!(TimeUnit::Microsecond, ts.unit());
1100            }
1101            _ => {
1102                unreachable!()
1103            }
1104        }
1105
1106        match parse_string_to_value(
1107            "timestamp_col",
1108            "2022-02-22T00:01:01+08:00".to_string(),
1109            &ConcreteDataType::timestamp_datatype(TimeUnit::Nanosecond),
1110            None,
1111            false,
1112        )
1113        .unwrap()
1114        {
1115            Value::Timestamp(ts) => {
1116                assert_eq!(1645459261000000000, ts.value());
1117                assert_eq!(TimeUnit::Nanosecond, ts.unit());
1118            }
1119            _ => {
1120                unreachable!()
1121            }
1122        }
1123
1124        assert!(parse_string_to_value(
1125            "timestamp_col",
1126            "2022-02-22T00:01:01+08".to_string(),
1127            &ConcreteDataType::timestamp_datatype(TimeUnit::Nanosecond),
1128            None,
1129            false,
1130        )
1131        .is_err());
1132
1133        // with timezone
1134        match parse_string_to_value(
1135            "timestamp_col",
1136            "2022-02-22T00:01:01".to_string(),
1137            &ConcreteDataType::timestamp_datatype(TimeUnit::Nanosecond),
1138            Some(&Timezone::from_tz_string("Asia/Shanghai").unwrap()),
1139            false,
1140        )
1141        .unwrap()
1142        {
1143            Value::Timestamp(ts) => {
1144                assert_eq!(1645459261000000000, ts.value());
1145                assert_eq!("2022-02-21 16:01:01+0000", ts.to_iso8601_string());
1146                assert_eq!(TimeUnit::Nanosecond, ts.unit());
1147            }
1148            _ => {
1149                unreachable!()
1150            }
1151        }
1152    }
1153
1154    #[test]
1155    fn test_parse_json_to_jsonb() {
1156        match parse_string_to_value(
1157            "json_col",
1158            r#"{"a": "b"}"#.to_string(),
1159            &ConcreteDataType::json_datatype(),
1160            None,
1161            false,
1162        ) {
1163            Ok(Value::Binary(b)) => {
1164                assert_eq!(
1165                    b,
1166                    jsonb::parse_value(r#"{"a": "b"}"#.as_bytes())
1167                        .unwrap()
1168                        .to_vec()
1169                );
1170            }
1171            _ => {
1172                unreachable!()
1173            }
1174        }
1175
1176        assert!(parse_string_to_value(
1177            "json_col",
1178            r#"Nicola Kovac is the best rifler in the world"#.to_string(),
1179            &ConcreteDataType::json_datatype(),
1180            None,
1181            false,
1182        )
1183        .is_err())
1184    }
1185
1186    #[test]
1187    pub fn test_parse_column_default_constraint() {
1188        let bool_value = sqlparser::ast::Value::Boolean(true);
1189
1190        let opts = vec![
1191            ColumnOptionDef {
1192                name: None,
1193                option: ColumnOption::Default(Expr::Value(bool_value)),
1194            },
1195            ColumnOptionDef {
1196                name: None,
1197                option: ColumnOption::NotNull,
1198            },
1199        ];
1200
1201        let constraint = parse_column_default_constraint(
1202            "coll",
1203            &ConcreteDataType::Boolean(BooleanType),
1204            &opts,
1205            None,
1206        )
1207        .unwrap();
1208
1209        assert_matches!(
1210            constraint,
1211            Some(ColumnDefaultConstraint::Value(Value::Boolean(true)))
1212        );
1213
1214        // Test negative number
1215        let opts = vec![ColumnOptionDef {
1216            name: None,
1217            option: ColumnOption::Default(Expr::UnaryOp {
1218                op: UnaryOperator::Minus,
1219                expr: Box::new(Expr::Value(SqlValue::Number("32768".to_string(), false))),
1220            }),
1221        }];
1222
1223        let constraint = parse_column_default_constraint(
1224            "coll",
1225            &ConcreteDataType::int16_datatype(),
1226            &opts,
1227            None,
1228        )
1229        .unwrap();
1230
1231        assert_matches!(
1232            constraint,
1233            Some(ColumnDefaultConstraint::Value(Value::Int16(-32768)))
1234        );
1235    }
1236
1237    #[test]
1238    fn test_incorrect_default_value_issue_3479() {
1239        let opts = vec![ColumnOptionDef {
1240            name: None,
1241            option: ColumnOption::Default(Expr::Value(SqlValue::Number(
1242                "0.047318541668048164".into(),
1243                false,
1244            ))),
1245        }];
1246        let constraint = parse_column_default_constraint(
1247            "coll",
1248            &ConcreteDataType::float64_datatype(),
1249            &opts,
1250            None,
1251        )
1252        .unwrap()
1253        .unwrap();
1254        assert_eq!("0.047318541668048164", constraint.to_string());
1255        let encoded: Vec<u8> = constraint.clone().try_into().unwrap();
1256        let decoded = ColumnDefaultConstraint::try_from(encoded.as_ref()).unwrap();
1257        assert_eq!(decoded, constraint);
1258    }
1259
1260    #[test]
1261    pub fn test_sql_column_def_to_grpc_column_def() {
1262        // test basic
1263        let column_def = ColumnDef {
1264            name: "col".into(),
1265            data_type: SqlDataType::Double(ExactNumberInfo::None),
1266            collation: None,
1267            options: vec![],
1268        };
1269
1270        let grpc_column_def = sql_column_def_to_grpc_column_def(&column_def, None).unwrap();
1271
1272        assert_eq!("col", grpc_column_def.name);
1273        assert!(grpc_column_def.is_nullable); // nullable when options are empty
1274        assert_eq!(ColumnDataType::Float64 as i32, grpc_column_def.data_type);
1275        assert!(grpc_column_def.default_constraint.is_empty());
1276        assert_eq!(grpc_column_def.semantic_type, SemanticType::Field as i32);
1277
1278        // test not null
1279        let column_def = ColumnDef {
1280            name: "col".into(),
1281            data_type: SqlDataType::Double(ExactNumberInfo::None),
1282            collation: None,
1283            options: vec![ColumnOptionDef {
1284                name: None,
1285                option: ColumnOption::NotNull,
1286            }],
1287        };
1288
1289        let grpc_column_def = sql_column_def_to_grpc_column_def(&column_def, None).unwrap();
1290        assert!(!grpc_column_def.is_nullable);
1291
1292        // test primary key
1293        let column_def = ColumnDef {
1294            name: "col".into(),
1295            data_type: SqlDataType::Double(ExactNumberInfo::None),
1296            collation: None,
1297            options: vec![ColumnOptionDef {
1298                name: None,
1299                option: ColumnOption::Unique {
1300                    is_primary: true,
1301                    characteristics: None,
1302                },
1303            }],
1304        };
1305
1306        let grpc_column_def = sql_column_def_to_grpc_column_def(&column_def, None).unwrap();
1307        assert_eq!(grpc_column_def.semantic_type, SemanticType::Tag as i32);
1308    }
1309
1310    #[test]
1311    pub fn test_sql_column_def_to_grpc_column_def_with_timezone() {
1312        let column_def = ColumnDef {
1313            name: "col".into(),
1314            // MILLISECOND
1315            data_type: SqlDataType::Timestamp(Some(3), TimezoneInfo::None),
1316            collation: None,
1317            options: vec![ColumnOptionDef {
1318                name: None,
1319                option: ColumnOption::Default(Expr::Value(SqlValue::SingleQuotedString(
1320                    "2024-01-30T00:01:01".to_string(),
1321                ))),
1322            }],
1323        };
1324
1325        // with timezone "Asia/Shanghai"
1326        let grpc_column_def = sql_column_def_to_grpc_column_def(
1327            &column_def,
1328            Some(&Timezone::from_tz_string("Asia/Shanghai").unwrap()),
1329        )
1330        .unwrap();
1331        assert_eq!("col", grpc_column_def.name);
1332        assert!(grpc_column_def.is_nullable); // nullable when options are empty
1333        assert_eq!(
1334            ColumnDataType::TimestampMillisecond as i32,
1335            grpc_column_def.data_type
1336        );
1337        assert!(!grpc_column_def.default_constraint.is_empty());
1338
1339        let constraint =
1340            ColumnDefaultConstraint::try_from(&grpc_column_def.default_constraint[..]).unwrap();
1341        assert!(
1342            matches!(constraint, ColumnDefaultConstraint::Value(Value::Timestamp(ts))
1343                         if ts.to_iso8601_string() == "2024-01-29 16:01:01+0000")
1344        );
1345
1346        // without timezone
1347        let grpc_column_def = sql_column_def_to_grpc_column_def(&column_def, None).unwrap();
1348        assert_eq!("col", grpc_column_def.name);
1349        assert!(grpc_column_def.is_nullable); // nullable when options are empty
1350        assert_eq!(
1351            ColumnDataType::TimestampMillisecond as i32,
1352            grpc_column_def.data_type
1353        );
1354        assert!(!grpc_column_def.default_constraint.is_empty());
1355
1356        let constraint =
1357            ColumnDefaultConstraint::try_from(&grpc_column_def.default_constraint[..]).unwrap();
1358        assert!(
1359            matches!(constraint, ColumnDefaultConstraint::Value(Value::Timestamp(ts))
1360                         if ts.to_iso8601_string() == "2024-01-30 00:01:01+0000")
1361        );
1362    }
1363
1364    #[test]
1365    pub fn test_has_primary_key_option() {
1366        let column_def = ColumnDef {
1367            name: "col".into(),
1368            data_type: SqlDataType::Double(ExactNumberInfo::None),
1369            collation: None,
1370            options: vec![],
1371        };
1372        assert!(!has_primary_key_option(&column_def));
1373
1374        let column_def = ColumnDef {
1375            name: "col".into(),
1376            data_type: SqlDataType::Double(ExactNumberInfo::None),
1377            collation: None,
1378            options: vec![ColumnOptionDef {
1379                name: None,
1380                option: ColumnOption::Unique {
1381                    is_primary: true,
1382                    characteristics: None,
1383                },
1384            }],
1385        };
1386        assert!(has_primary_key_option(&column_def));
1387    }
1388
1389    #[test]
1390    pub fn test_column_to_schema() {
1391        let column_def = Column {
1392            column_def: ColumnDef {
1393                name: "col".into(),
1394                data_type: SqlDataType::Double(ExactNumberInfo::None),
1395                collation: None,
1396                options: vec![],
1397            },
1398            extensions: ColumnExtensions::default(),
1399        };
1400
1401        let column_schema = column_to_schema(&column_def, "ts", None).unwrap();
1402
1403        assert_eq!("col", column_schema.name);
1404        assert_eq!(
1405            ConcreteDataType::float64_datatype(),
1406            column_schema.data_type
1407        );
1408        assert!(column_schema.is_nullable());
1409        assert!(!column_schema.is_time_index());
1410
1411        let column_schema = column_to_schema(&column_def, "col", None).unwrap();
1412
1413        assert_eq!("col", column_schema.name);
1414        assert_eq!(
1415            ConcreteDataType::float64_datatype(),
1416            column_schema.data_type
1417        );
1418        assert!(!column_schema.is_nullable());
1419        assert!(column_schema.is_time_index());
1420
1421        let column_def = Column {
1422            column_def: ColumnDef {
1423                name: "col2".into(),
1424                data_type: SqlDataType::String(None),
1425                collation: None,
1426                options: vec![
1427                    ColumnOptionDef {
1428                        name: None,
1429                        option: ColumnOption::NotNull,
1430                    },
1431                    ColumnOptionDef {
1432                        name: None,
1433                        option: ColumnOption::Comment("test comment".to_string()),
1434                    },
1435                ],
1436            },
1437            extensions: ColumnExtensions::default(),
1438        };
1439
1440        let column_schema = column_to_schema(&column_def, "ts", None).unwrap();
1441
1442        assert_eq!("col2", column_schema.name);
1443        assert_eq!(ConcreteDataType::string_datatype(), column_schema.data_type);
1444        assert!(!column_schema.is_nullable());
1445        assert!(!column_schema.is_time_index());
1446        assert_eq!(
1447            column_schema.metadata().get(COMMENT_KEY),
1448            Some(&"test comment".to_string())
1449        );
1450    }
1451
1452    #[test]
1453    pub fn test_column_to_schema_timestamp_with_timezone() {
1454        let column = Column {
1455            column_def: ColumnDef {
1456                name: "col".into(),
1457                // MILLISECOND
1458                data_type: SqlDataType::Timestamp(Some(3), TimezoneInfo::None),
1459                collation: None,
1460                options: vec![ColumnOptionDef {
1461                    name: None,
1462                    option: ColumnOption::Default(Expr::Value(SqlValue::SingleQuotedString(
1463                        "2024-01-30T00:01:01".to_string(),
1464                    ))),
1465                }],
1466            },
1467            extensions: ColumnExtensions::default(),
1468        };
1469
1470        // with timezone "Asia/Shanghai"
1471
1472        let column_schema = column_to_schema(
1473            &column,
1474            "ts",
1475            Some(&Timezone::from_tz_string("Asia/Shanghai").unwrap()),
1476        )
1477        .unwrap();
1478
1479        assert_eq!("col", column_schema.name);
1480        assert_eq!(
1481            ConcreteDataType::timestamp_millisecond_datatype(),
1482            column_schema.data_type
1483        );
1484        assert!(column_schema.is_nullable());
1485
1486        let constraint = column_schema.default_constraint().unwrap();
1487        assert!(
1488            matches!(constraint, ColumnDefaultConstraint::Value(Value::Timestamp(ts))
1489                         if ts.to_iso8601_string() == "2024-01-29 16:01:01+0000")
1490        );
1491
1492        // without timezone
1493        let column_schema = column_to_schema(&column, "ts", None).unwrap();
1494
1495        assert_eq!("col", column_schema.name);
1496        assert_eq!(
1497            ConcreteDataType::timestamp_millisecond_datatype(),
1498            column_schema.data_type
1499        );
1500        assert!(column_schema.is_nullable());
1501
1502        let constraint = column_schema.default_constraint().unwrap();
1503        assert!(
1504            matches!(constraint, ColumnDefaultConstraint::Value(Value::Timestamp(ts))
1505                         if ts.to_iso8601_string() == "2024-01-30 00:01:01+0000")
1506        );
1507    }
1508
1509    #[test]
1510    fn test_column_to_schema_with_fulltext() {
1511        let column = Column {
1512            column_def: ColumnDef {
1513                name: "col".into(),
1514                data_type: SqlDataType::Text,
1515                collation: None,
1516                options: vec![],
1517            },
1518            extensions: ColumnExtensions {
1519                fulltext_index_options: Some(
1520                    HashMap::from_iter([
1521                        (
1522                            COLUMN_FULLTEXT_OPT_KEY_ANALYZER.to_string(),
1523                            "English".to_string(),
1524                        ),
1525                        (
1526                            COLUMN_FULLTEXT_OPT_KEY_CASE_SENSITIVE.to_string(),
1527                            "true".to_string(),
1528                        ),
1529                    ])
1530                    .into(),
1531                ),
1532                vector_options: None,
1533                skipping_index_options: None,
1534                inverted_index_options: None,
1535            },
1536        };
1537
1538        let column_schema = column_to_schema(&column, "ts", None).unwrap();
1539        assert_eq!("col", column_schema.name);
1540        assert_eq!(ConcreteDataType::string_datatype(), column_schema.data_type);
1541        let fulltext_options = column_schema.fulltext_options().unwrap().unwrap();
1542        assert_eq!(fulltext_options.analyzer, FulltextAnalyzer::English);
1543        assert!(fulltext_options.case_sensitive);
1544    }
1545
1546    #[test]
1547    fn test_parse_placeholder_value() {
1548        assert!(sql_value_to_value(
1549            "test",
1550            &ConcreteDataType::string_datatype(),
1551            &SqlValue::Placeholder("default".into()),
1552            None,
1553            None,
1554            false
1555        )
1556        .is_err());
1557        assert!(sql_value_to_value(
1558            "test",
1559            &ConcreteDataType::string_datatype(),
1560            &SqlValue::Placeholder("default".into()),
1561            None,
1562            Some(UnaryOperator::Minus),
1563            false
1564        )
1565        .is_err());
1566        assert!(sql_value_to_value(
1567            "test",
1568            &ConcreteDataType::uint16_datatype(),
1569            &SqlValue::Number("3".into(), false),
1570            None,
1571            Some(UnaryOperator::Minus),
1572            false
1573        )
1574        .is_err());
1575        assert!(sql_value_to_value(
1576            "test",
1577            &ConcreteDataType::uint16_datatype(),
1578            &SqlValue::Number("3".into(), false),
1579            None,
1580            None,
1581            false
1582        )
1583        .is_ok());
1584    }
1585
1586    #[test]
1587    fn test_string_to_value_auto_numeric() {
1588        // Test string to boolean with auto cast
1589        let result = parse_string_to_value(
1590            "col",
1591            "true".to_string(),
1592            &ConcreteDataType::boolean_datatype(),
1593            None,
1594            true,
1595        )
1596        .unwrap();
1597        assert_eq!(Value::Boolean(true), result);
1598
1599        // Test invalid string to boolean with auto cast
1600        let result = parse_string_to_value(
1601            "col",
1602            "not_a_boolean".to_string(),
1603            &ConcreteDataType::boolean_datatype(),
1604            None,
1605            true,
1606        );
1607        assert!(result.is_err());
1608
1609        // Test string to int8
1610        let result = parse_string_to_value(
1611            "col",
1612            "42".to_string(),
1613            &ConcreteDataType::int8_datatype(),
1614            None,
1615            true,
1616        )
1617        .unwrap();
1618        assert_eq!(Value::Int8(42), result);
1619
1620        // Test invalid string to int8 with auto cast
1621        let result = parse_string_to_value(
1622            "col",
1623            "not_an_int8".to_string(),
1624            &ConcreteDataType::int8_datatype(),
1625            None,
1626            true,
1627        );
1628        assert!(result.is_err());
1629
1630        // Test string to int16
1631        let result = parse_string_to_value(
1632            "col",
1633            "1000".to_string(),
1634            &ConcreteDataType::int16_datatype(),
1635            None,
1636            true,
1637        )
1638        .unwrap();
1639        assert_eq!(Value::Int16(1000), result);
1640
1641        // Test invalid string to int16 with auto cast
1642        let result = parse_string_to_value(
1643            "col",
1644            "not_an_int16".to_string(),
1645            &ConcreteDataType::int16_datatype(),
1646            None,
1647            true,
1648        );
1649        assert!(result.is_err());
1650
1651        // Test string to int32
1652        let result = parse_string_to_value(
1653            "col",
1654            "100000".to_string(),
1655            &ConcreteDataType::int32_datatype(),
1656            None,
1657            true,
1658        )
1659        .unwrap();
1660        assert_eq!(Value::Int32(100000), result);
1661
1662        // Test invalid string to int32 with auto cast
1663        let result = parse_string_to_value(
1664            "col",
1665            "not_an_int32".to_string(),
1666            &ConcreteDataType::int32_datatype(),
1667            None,
1668            true,
1669        );
1670        assert!(result.is_err());
1671
1672        // Test string to int64
1673        let result = parse_string_to_value(
1674            "col",
1675            "1000000".to_string(),
1676            &ConcreteDataType::int64_datatype(),
1677            None,
1678            true,
1679        )
1680        .unwrap();
1681        assert_eq!(Value::Int64(1000000), result);
1682
1683        // Test invalid string to int64 with auto cast
1684        let result = parse_string_to_value(
1685            "col",
1686            "not_an_int64".to_string(),
1687            &ConcreteDataType::int64_datatype(),
1688            None,
1689            true,
1690        );
1691        assert!(result.is_err());
1692
1693        // Test string to uint8
1694        let result = parse_string_to_value(
1695            "col",
1696            "200".to_string(),
1697            &ConcreteDataType::uint8_datatype(),
1698            None,
1699            true,
1700        )
1701        .unwrap();
1702        assert_eq!(Value::UInt8(200), result);
1703
1704        // Test invalid string to uint8 with auto cast
1705        let result = parse_string_to_value(
1706            "col",
1707            "not_a_uint8".to_string(),
1708            &ConcreteDataType::uint8_datatype(),
1709            None,
1710            true,
1711        );
1712        assert!(result.is_err());
1713
1714        // Test string to uint16
1715        let result = parse_string_to_value(
1716            "col",
1717            "60000".to_string(),
1718            &ConcreteDataType::uint16_datatype(),
1719            None,
1720            true,
1721        )
1722        .unwrap();
1723        assert_eq!(Value::UInt16(60000), result);
1724
1725        // Test invalid string to uint16 with auto cast
1726        let result = parse_string_to_value(
1727            "col",
1728            "not_a_uint16".to_string(),
1729            &ConcreteDataType::uint16_datatype(),
1730            None,
1731            true,
1732        );
1733        assert!(result.is_err());
1734
1735        // Test string to uint32
1736        let result = parse_string_to_value(
1737            "col",
1738            "4000000000".to_string(),
1739            &ConcreteDataType::uint32_datatype(),
1740            None,
1741            true,
1742        )
1743        .unwrap();
1744        assert_eq!(Value::UInt32(4000000000), result);
1745
1746        // Test invalid string to uint32 with auto cast
1747        let result = parse_string_to_value(
1748            "col",
1749            "not_a_uint32".to_string(),
1750            &ConcreteDataType::uint32_datatype(),
1751            None,
1752            true,
1753        );
1754        assert!(result.is_err());
1755
1756        // Test string to uint64
1757        let result = parse_string_to_value(
1758            "col",
1759            "18446744073709551615".to_string(),
1760            &ConcreteDataType::uint64_datatype(),
1761            None,
1762            true,
1763        )
1764        .unwrap();
1765        assert_eq!(Value::UInt64(18446744073709551615), result);
1766
1767        // Test invalid string to uint64 with auto cast
1768        let result = parse_string_to_value(
1769            "col",
1770            "not_a_uint64".to_string(),
1771            &ConcreteDataType::uint64_datatype(),
1772            None,
1773            true,
1774        );
1775        assert!(result.is_err());
1776
1777        // Test string to float32
1778        let result = parse_string_to_value(
1779            "col",
1780            "3.5".to_string(),
1781            &ConcreteDataType::float32_datatype(),
1782            None,
1783            true,
1784        )
1785        .unwrap();
1786        assert_eq!(Value::Float32(OrderedF32::from(3.5)), result);
1787
1788        // Test invalid string to float32 with auto cast
1789        let result = parse_string_to_value(
1790            "col",
1791            "not_a_float32".to_string(),
1792            &ConcreteDataType::float32_datatype(),
1793            None,
1794            true,
1795        );
1796        assert!(result.is_err());
1797
1798        // Test string to float64
1799        let result = parse_string_to_value(
1800            "col",
1801            "3.5".to_string(),
1802            &ConcreteDataType::float64_datatype(),
1803            None,
1804            true,
1805        )
1806        .unwrap();
1807        assert_eq!(Value::Float64(OrderedF64::from(3.5)), result);
1808
1809        // Test invalid string to float64 with auto cast
1810        let result = parse_string_to_value(
1811            "col",
1812            "not_a_float64".to_string(),
1813            &ConcreteDataType::float64_datatype(),
1814            None,
1815            true,
1816        );
1817        assert!(result.is_err());
1818    }
1819
1820    #[test]
1821    fn test_auto_string_to_numeric() {
1822        // Test with auto_string_to_numeric=true
1823        let sql_val = SqlValue::SingleQuotedString("123".to_string());
1824        let v = sql_value_to_value(
1825            "a",
1826            &ConcreteDataType::int32_datatype(),
1827            &sql_val,
1828            None,
1829            None,
1830            true,
1831        )
1832        .unwrap();
1833        assert_eq!(Value::Int32(123), v);
1834
1835        // Test with a float string
1836        let sql_val = SqlValue::SingleQuotedString("3.5".to_string());
1837        let v = sql_value_to_value(
1838            "a",
1839            &ConcreteDataType::float64_datatype(),
1840            &sql_val,
1841            None,
1842            None,
1843            true,
1844        )
1845        .unwrap();
1846        assert_eq!(Value::Float64(OrderedFloat(3.5)), v);
1847
1848        // Test with auto_string_to_numeric=false
1849        let sql_val = SqlValue::SingleQuotedString("123".to_string());
1850        let v = sql_value_to_value(
1851            "a",
1852            &ConcreteDataType::int32_datatype(),
1853            &sql_val,
1854            None,
1855            None,
1856            false,
1857        );
1858        assert!(v.is_err());
1859
1860        // Test with an invalid numeric string but auto_string_to_numeric=true
1861        // Should return an error now with the new auto_cast_to_numeric behavior
1862        let sql_val = SqlValue::SingleQuotedString("not_a_number".to_string());
1863        let v = sql_value_to_value(
1864            "a",
1865            &ConcreteDataType::int32_datatype(),
1866            &sql_val,
1867            None,
1868            None,
1869            true,
1870        );
1871        assert!(v.is_err());
1872
1873        // Test with boolean type
1874        let sql_val = SqlValue::SingleQuotedString("true".to_string());
1875        let v = sql_value_to_value(
1876            "a",
1877            &ConcreteDataType::boolean_datatype(),
1878            &sql_val,
1879            None,
1880            None,
1881            true,
1882        )
1883        .unwrap();
1884        assert_eq!(Value::Boolean(true), v);
1885
1886        // Non-numeric types should still be handled normally
1887        let sql_val = SqlValue::SingleQuotedString("hello".to_string());
1888        let v = sql_value_to_value(
1889            "a",
1890            &ConcreteDataType::string_datatype(),
1891            &sql_val,
1892            None,
1893            None,
1894            true,
1895        );
1896        assert!(v.is_ok());
1897    }
1898}
sql/statements.rs

sql/
statements.rs
