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

sql/
statements.rs
