| // Licensed to the Apache Software Foundation (ASF) under one | |
| // or more contributor license agreements. See the NOTICE file | |
| // distributed with this work for additional information | |
| // regarding copyright ownership. The ASF licenses this file | |
| // to you under the Apache License, Version 2.0 (the | |
| // "License"); you may not use this file except in compliance | |
| // with the License. You may obtain a copy of the License at | |
| // | |
| // http://www.apache.org/licenses/LICENSE-2.0 | |
| // | |
| // Unless required by applicable law or agreed to in writing, | |
| // software distributed under the License is distributed on an | |
| // "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY | |
| // KIND, either express or implied. See the License for the | |
| // specific language governing permissions and limitations | |
| // under the License. | |
| #[macro_use] | |
| mod test_utils; | |
| use helpers::attached_token::AttachedToken; | |
| use sqlparser::tokenizer::Span; | |
| use test_utils::*; | |
| use sqlparser::ast::*; | |
| use sqlparser::dialect::{DuckDbDialect, GenericDialect}; | |
| use sqlparser::parser::ParserError; | |
| fn duckdb() -> TestedDialects { | |
| TestedDialects::new(vec![Box::new(DuckDbDialect {})]) | |
| } | |
| fn duckdb_and_generic() -> TestedDialects { | |
| TestedDialects::new(vec![ | |
| Box::new(DuckDbDialect {}), | |
| Box::new(GenericDialect {}), | |
| ]) | |
| } | |
| #[test] | |
| fn test_struct() { | |
| // s STRUCT(v VARCHAR, i INTEGER) | |
| let struct_type1 = DataType::Struct( | |
| vec![ | |
| StructField { | |
| field_name: Some(Ident::new("v")), | |
| field_type: DataType::Varchar(None), | |
| options: None, | |
| }, | |
| StructField { | |
| field_name: Some(Ident::new("i")), | |
| field_type: DataType::Integer(None), | |
| options: None, | |
| }, | |
| ], | |
| StructBracketKind::Parentheses, | |
| ); | |
| // basic struct | |
| let statement = duckdb().verified_stmt(r#"CREATE TABLE t1 (s STRUCT(v VARCHAR, i INTEGER))"#); | |
| assert_eq!( | |
| column_defs(statement), | |
| vec![ColumnDef { | |
| name: "s".into(), | |
| data_type: struct_type1.clone(), | |
| options: vec![], | |
| }] | |
| ); | |
| // struct array | |
| let statement = duckdb().verified_stmt(r#"CREATE TABLE t1 (s STRUCT(v VARCHAR, i INTEGER)[])"#); | |
| assert_eq!( | |
| column_defs(statement), | |
| vec![ColumnDef { | |
| name: "s".into(), | |
| data_type: DataType::Array(ArrayElemTypeDef::SquareBracket( | |
| Box::new(struct_type1), | |
| None | |
| )), | |
| options: vec![], | |
| }] | |
| ); | |
| // s STRUCT(v VARCHAR, s STRUCT(a1 INTEGER, a2 VARCHAR)) | |
| let struct_type2 = DataType::Struct( | |
| vec![ | |
| StructField { | |
| field_name: Some(Ident::new("v")), | |
| field_type: DataType::Varchar(None), | |
| options: None, | |
| }, | |
| StructField { | |
| field_name: Some(Ident::new("s")), | |
| field_type: DataType::Struct( | |
| vec![ | |
| StructField { | |
| field_name: Some(Ident::new("a1")), | |
| field_type: DataType::Integer(None), | |
| options: None, | |
| }, | |
| StructField { | |
| field_name: Some(Ident::new("a2")), | |
| field_type: DataType::Varchar(None), | |
| options: None, | |
| }, | |
| ], | |
| StructBracketKind::Parentheses, | |
| ), | |
| options: None, | |
| }, | |
| ], | |
| StructBracketKind::Parentheses, | |
| ); | |
| // nested struct | |
| let statement = duckdb().verified_stmt( | |
| r#"CREATE TABLE t1 (s STRUCT(v VARCHAR, s STRUCT(a1 INTEGER, a2 VARCHAR))[])"#, | |
| ); | |
| assert_eq!( | |
| column_defs(statement), | |
| vec![ColumnDef { | |
| name: "s".into(), | |
| data_type: DataType::Array(ArrayElemTypeDef::SquareBracket( | |
| Box::new(struct_type2), | |
| None | |
| )), | |
| options: vec![], | |
| }] | |
| ); | |
| // failing test (duckdb does not support bracket syntax) | |
| let sql_list = vec![ | |
| r#"CREATE TABLE t1 (s STRUCT(v VARCHAR, i INTEGER)))"#, | |
| r#"CREATE TABLE t1 (s STRUCT(v VARCHAR, i INTEGER>)"#, | |
| r#"CREATE TABLE t1 (s STRUCT<v VARCHAR, i INTEGER>)"#, | |
| r#"CREATE TABLE t1 (s STRUCT v VARCHAR, i INTEGER )"#, | |
| r#"CREATE TABLE t1 (s STRUCT VARCHAR, i INTEGER )"#, | |
| r#"CREATE TABLE t1 (s STRUCT (VARCHAR, INTEGER))"#, | |
| ]; | |
| for sql in sql_list { | |
| duckdb().parse_sql_statements(sql).unwrap_err(); | |
| } | |
| } | |
| /// Returns the ColumnDefinitions from a CreateTable statement | |
| fn column_defs(statement: Statement) -> Vec<ColumnDef> { | |
| match statement { | |
| Statement::CreateTable(CreateTable { columns, .. }) => columns, | |
| _ => panic!("Expected CreateTable"), | |
| } | |
| } | |
| #[test] | |
| fn test_select_wildcard_with_exclude() { | |
| let select = duckdb().verified_only_select("SELECT * EXCLUDE (col_a) FROM data"); | |
| let expected = SelectItem::Wildcard(WildcardAdditionalOptions { | |
| opt_exclude: Some(ExcludeSelectItem::Multiple(vec![ObjectName::from( | |
| Ident::new("col_a"), | |
| )])), | |
| ..Default::default() | |
| }); | |
| assert_eq!(expected, select.projection[0]); | |
| let select = | |
| duckdb().verified_only_select("SELECT name.* EXCLUDE department_id FROM employee_table"); | |
| let expected = SelectItem::QualifiedWildcard( | |
| SelectItemQualifiedWildcardKind::ObjectName(ObjectName::from(vec![Ident::new("name")])), | |
| WildcardAdditionalOptions { | |
| opt_exclude: Some(ExcludeSelectItem::Single(ObjectName::from(Ident::new( | |
| "department_id", | |
| )))), | |
| ..Default::default() | |
| }, | |
| ); | |
| assert_eq!(expected, select.projection[0]); | |
| let select = duckdb() | |
| .verified_only_select("SELECT * EXCLUDE (department_id, employee_id) FROM employee_table"); | |
| let expected = SelectItem::Wildcard(WildcardAdditionalOptions { | |
| opt_exclude: Some(ExcludeSelectItem::Multiple(vec![ | |
| ObjectName::from(Ident::new("department_id")), | |
| ObjectName::from(Ident::new("employee_id")), | |
| ])), | |
| ..Default::default() | |
| }); | |
| assert_eq!(expected, select.projection[0]); | |
| } | |
| #[test] | |
| fn parse_div_infix() { | |
| duckdb_and_generic().verified_stmt(r#"SELECT 5 // 2"#); | |
| } | |
| #[test] | |
| fn test_create_macro() { | |
| let macro_ = duckdb().verified_stmt("CREATE MACRO schema.add(a, b) AS a + b"); | |
| let expected = Statement::CreateMacro { | |
| or_replace: false, | |
| temporary: false, | |
| name: ObjectName::from(vec![Ident::new("schema"), Ident::new("add")]), | |
| args: Some(vec![MacroArg::new("a"), MacroArg::new("b")]), | |
| definition: MacroDefinition::Expr(Expr::BinaryOp { | |
| left: Box::new(Expr::Identifier(Ident::new("a"))), | |
| op: BinaryOperator::Plus, | |
| right: Box::new(Expr::Identifier(Ident::new("b"))), | |
| }), | |
| }; | |
| assert_eq!(expected, macro_); | |
| } | |
| #[test] | |
| fn test_create_macro_default_args() { | |
| let macro_ = duckdb().verified_stmt("CREATE MACRO add_default(a, b := 5) AS a + b"); | |
| let expected = Statement::CreateMacro { | |
| or_replace: false, | |
| temporary: false, | |
| name: ObjectName::from(vec![Ident::new("add_default")]), | |
| args: Some(vec![ | |
| MacroArg::new("a"), | |
| MacroArg { | |
| name: Ident::new("b"), | |
| default_expr: Some(Expr::value(number("5"))), | |
| }, | |
| ]), | |
| definition: MacroDefinition::Expr(Expr::BinaryOp { | |
| left: Box::new(Expr::Identifier(Ident::new("a"))), | |
| op: BinaryOperator::Plus, | |
| right: Box::new(Expr::Identifier(Ident::new("b"))), | |
| }), | |
| }; | |
| assert_eq!(expected, macro_); | |
| } | |
| #[test] | |
| fn test_create_table_macro() { | |
| let query = "SELECT col1_value AS column1, col2_value AS column2 UNION ALL SELECT 'Hello' AS col1_value, 456 AS col2_value"; | |
| let macro_ = duckdb().verified_stmt( | |
| &("CREATE OR REPLACE TEMPORARY MACRO dynamic_table(col1_value, col2_value) AS TABLE " | |
| .to_string() | |
| + query), | |
| ); | |
| let expected = Statement::CreateMacro { | |
| or_replace: true, | |
| temporary: true, | |
| name: ObjectName::from(vec![Ident::new("dynamic_table")]), | |
| args: Some(vec![ | |
| MacroArg::new("col1_value"), | |
| MacroArg::new("col2_value"), | |
| ]), | |
| definition: MacroDefinition::Table(duckdb().verified_query(query).into()), | |
| }; | |
| assert_eq!(expected, macro_); | |
| } | |
| #[test] | |
| fn test_select_union_by_name() { | |
| let q1 = "SELECT * FROM capitals UNION BY NAME SELECT * FROM weather"; | |
| let q2 = "SELECT * FROM capitals UNION ALL BY NAME SELECT * FROM weather"; | |
| let q3 = "SELECT * FROM capitals UNION DISTINCT BY NAME SELECT * FROM weather"; | |
| for (ast, expected_quantifier) in &[ | |
| (duckdb().verified_query(q1), SetQuantifier::ByName), | |
| (duckdb().verified_query(q2), SetQuantifier::AllByName), | |
| (duckdb().verified_query(q3), SetQuantifier::DistinctByName), | |
| ] { | |
| let expected = Box::<SetExpr>::new(SetExpr::SetOperation { | |
| op: SetOperator::Union, | |
| set_quantifier: *expected_quantifier, | |
| left: Box::<SetExpr>::new(SetExpr::Select(Box::new(Select { | |
| select_token: AttachedToken::empty(), | |
| optimizer_hints: vec![], | |
| distinct: None, | |
| select_modifiers: None, | |
| top: None, | |
| projection: vec![SelectItem::Wildcard(WildcardAdditionalOptions::default())], | |
| exclude: None, | |
| top_before_distinct: false, | |
| into: None, | |
| from: vec![TableWithJoins { | |
| relation: table_from_name(ObjectName::from(vec![Ident { | |
| value: "capitals".to_string(), | |
| quote_style: None, | |
| span: Span::empty(), | |
| }])), | |
| joins: vec![], | |
| }], | |
| lateral_views: vec![], | |
| prewhere: None, | |
| selection: None, | |
| group_by: GroupByExpr::Expressions(vec![], vec![]), | |
| cluster_by: vec![], | |
| distribute_by: vec![], | |
| sort_by: vec![], | |
| having: None, | |
| named_window: vec![], | |
| window_before_qualify: false, | |
| qualify: None, | |
| value_table_mode: None, | |
| connect_by: vec![], | |
| flavor: SelectFlavor::Standard, | |
| }))), | |
| right: Box::<SetExpr>::new(SetExpr::Select(Box::new(Select { | |
| select_token: AttachedToken::empty(), | |
| optimizer_hints: vec![], | |
| distinct: None, | |
| select_modifiers: None, | |
| top: None, | |
| projection: vec![SelectItem::Wildcard(WildcardAdditionalOptions::default())], | |
| exclude: None, | |
| top_before_distinct: false, | |
| into: None, | |
| from: vec![TableWithJoins { | |
| relation: table_from_name(ObjectName::from(vec![Ident { | |
| value: "weather".to_string(), | |
| quote_style: None, | |
| span: Span::empty(), | |
| }])), | |
| joins: vec![], | |
| }], | |
| lateral_views: vec![], | |
| prewhere: None, | |
| selection: None, | |
| group_by: GroupByExpr::Expressions(vec![], vec![]), | |
| cluster_by: vec![], | |
| distribute_by: vec![], | |
| sort_by: vec![], | |
| having: None, | |
| named_window: vec![], | |
| window_before_qualify: false, | |
| qualify: None, | |
| value_table_mode: None, | |
| connect_by: vec![], | |
| flavor: SelectFlavor::Standard, | |
| }))), | |
| }); | |
| assert_eq!(ast.body, expected); | |
| } | |
| } | |
| #[test] | |
| fn test_duckdb_install() { | |
| let stmt = duckdb().verified_stmt("INSTALL tpch"); | |
| assert_eq!( | |
| stmt, | |
| Statement::Install { | |
| extension_name: Ident { | |
| value: "tpch".to_string(), | |
| quote_style: None, | |
| span: Span::empty() | |
| } | |
| } | |
| ); | |
| } | |
| #[test] | |
| fn test_duckdb_load_extension() { | |
| let stmt = duckdb().verified_stmt("LOAD my_extension"); | |
| assert_eq!( | |
| Statement::Load { | |
| extension_name: Ident { | |
| value: "my_extension".to_string(), | |
| quote_style: None, | |
| span: Span::empty() | |
| } | |
| }, | |
| stmt | |
| ); | |
| } | |
| #[test] | |
| fn test_duckdb_specific_int_types() { | |
| let duckdb_dtypes = vec![ | |
| ("UTINYINT", DataType::UTinyInt), | |
| ("USMALLINT", DataType::USmallInt), | |
| ("UBIGINT", DataType::UBigInt), | |
| ("UHUGEINT", DataType::UHugeInt), | |
| ("HUGEINT", DataType::HugeInt), | |
| ]; | |
| for (dtype_string, data_type) in duckdb_dtypes { | |
| let sql = format!("SELECT 123::{dtype_string}"); | |
| let select = duckdb().verified_only_select(&sql); | |
| assert_eq!( | |
| &Expr::Cast { | |
| kind: CastKind::DoubleColon, | |
| expr: Box::new(Expr::Value( | |
| Value::Number("123".parse().unwrap(), false).with_empty_span() | |
| )), | |
| data_type: data_type.clone(), | |
| format: None, | |
| }, | |
| expr_from_projection(&select.projection[0]) | |
| ); | |
| } | |
| } | |
| #[test] | |
| fn test_duckdb_struct_literal() { | |
| //struct literal syntax https://duckdb.org/docs/sql/data_types/struct#creating-structs | |
| //syntax: {'field_name': expr1[, ... ]} | |
| let sql = "SELECT {'a': 1, 'b': 2, 'c': 3}, [{'a': 'abc'}], {'a': 1, 'b': [t.str_col]}, {'a': 1, 'b': 'abc'}, {'abc': str_col}, {'a': {'aa': 1}}"; | |
| let select = duckdb_and_generic().verified_only_select(sql); | |
| assert_eq!(6, select.projection.len()); | |
| assert_eq!( | |
| &Expr::Dictionary(vec![ | |
| DictionaryField { | |
| key: Ident::with_quote('\'', "a"), | |
| value: Box::new(Expr::value(number("1"))), | |
| }, | |
| DictionaryField { | |
| key: Ident::with_quote('\'', "b"), | |
| value: Box::new(Expr::value(number("2"))), | |
| }, | |
| DictionaryField { | |
| key: Ident::with_quote('\'', "c"), | |
| value: Box::new(Expr::value(number("3"))), | |
| }, | |
| ],), | |
| expr_from_projection(&select.projection[0]) | |
| ); | |
| assert_eq!( | |
| &Expr::Array(Array { | |
| elem: vec![Expr::Dictionary(vec![DictionaryField { | |
| key: Ident::with_quote('\'', "a"), | |
| value: Box::new(Expr::Value( | |
| (Value::SingleQuotedString("abc".to_string())).with_empty_span() | |
| )), | |
| },],)], | |
| named: false | |
| }), | |
| expr_from_projection(&select.projection[1]) | |
| ); | |
| assert_eq!( | |
| &Expr::Dictionary(vec![ | |
| DictionaryField { | |
| key: Ident::with_quote('\'', "a"), | |
| value: Box::new(Expr::value(number("1"))), | |
| }, | |
| DictionaryField { | |
| key: Ident::with_quote('\'', "b"), | |
| value: Box::new(Expr::Array(Array { | |
| elem: vec![Expr::CompoundIdentifier(vec![ | |
| Ident::from("t"), | |
| Ident::from("str_col") | |
| ])], | |
| named: false | |
| })), | |
| }, | |
| ],), | |
| expr_from_projection(&select.projection[2]) | |
| ); | |
| assert_eq!( | |
| &Expr::Dictionary(vec![ | |
| DictionaryField { | |
| key: Ident::with_quote('\'', "a"), | |
| value: Expr::value(number("1")).into(), | |
| }, | |
| DictionaryField { | |
| key: Ident::with_quote('\'', "b"), | |
| value: Expr::Value( | |
| (Value::SingleQuotedString("abc".to_string())).with_empty_span() | |
| ) | |
| .into(), | |
| }, | |
| ],), | |
| expr_from_projection(&select.projection[3]) | |
| ); | |
| assert_eq!( | |
| &Expr::Dictionary(vec![DictionaryField { | |
| key: Ident::with_quote('\'', "abc"), | |
| value: Expr::Identifier(Ident::from("str_col")).into(), | |
| }],), | |
| expr_from_projection(&select.projection[4]) | |
| ); | |
| assert_eq!( | |
| &Expr::Dictionary(vec![DictionaryField { | |
| key: Ident::with_quote('\'', "a"), | |
| value: Expr::Dictionary(vec![DictionaryField { | |
| key: Ident::with_quote('\'', "aa"), | |
| value: Expr::value(number("1")).into(), | |
| }],) | |
| .into(), | |
| }],), | |
| expr_from_projection(&select.projection[5]) | |
| ); | |
| } | |
| #[test] | |
| fn test_create_secret() { | |
| let sql = r#"CREATE OR REPLACE PERSISTENT SECRET IF NOT EXISTS name IN storage ( TYPE type, key1 value1, key2 value2 )"#; | |
| let stmt = duckdb().verified_stmt(sql); | |
| assert_eq!( | |
| Statement::CreateSecret { | |
| or_replace: true, | |
| temporary: Some(false), | |
| if_not_exists: true, | |
| name: Some(Ident::new("name")), | |
| storage_specifier: Some(Ident::new("storage")), | |
| secret_type: Ident::new("type"), | |
| options: vec![ | |
| SecretOption { | |
| key: Ident::new("key1"), | |
| value: Ident::new("value1"), | |
| }, | |
| SecretOption { | |
| key: Ident::new("key2"), | |
| value: Ident::new("value2"), | |
| } | |
| ] | |
| }, | |
| stmt | |
| ); | |
| } | |
| #[test] | |
| fn test_create_secret_simple() { | |
| let sql = r#"CREATE SECRET ( TYPE type )"#; | |
| let stmt = duckdb().verified_stmt(sql); | |
| assert_eq!( | |
| Statement::CreateSecret { | |
| or_replace: false, | |
| temporary: None, | |
| if_not_exists: false, | |
| name: None, | |
| storage_specifier: None, | |
| secret_type: Ident::new("type"), | |
| options: vec![] | |
| }, | |
| stmt | |
| ); | |
| } | |
| #[test] | |
| fn test_drop_secret() { | |
| let sql = r#"DROP PERSISTENT SECRET IF EXISTS secret FROM storage"#; | |
| let stmt = duckdb().verified_stmt(sql); | |
| assert_eq!( | |
| Statement::DropSecret { | |
| if_exists: true, | |
| temporary: Some(false), | |
| name: Ident::new("secret"), | |
| storage_specifier: Some(Ident::new("storage")) | |
| }, | |
| stmt | |
| ); | |
| } | |
| #[test] | |
| fn test_drop_secret_simple() { | |
| let sql = r#"DROP SECRET secret"#; | |
| let stmt = duckdb().verified_stmt(sql); | |
| assert_eq!( | |
| Statement::DropSecret { | |
| if_exists: false, | |
| temporary: None, | |
| name: Ident::new("secret"), | |
| storage_specifier: None | |
| }, | |
| stmt | |
| ); | |
| } | |
| #[test] | |
| fn test_attach_database() { | |
| let sql = r#"ATTACH DATABASE IF NOT EXISTS 'sqlite_file.db' AS sqlite_db (READ_ONLY false, TYPE SQLITE)"#; | |
| let stmt = duckdb().verified_stmt(sql); | |
| assert_eq!( | |
| Statement::AttachDuckDBDatabase { | |
| if_not_exists: true, | |
| database: true, | |
| database_path: Ident::with_quote('\'', "sqlite_file.db"), | |
| database_alias: Some(Ident::new("sqlite_db")), | |
| attach_options: vec![ | |
| AttachDuckDBDatabaseOption::ReadOnly(Some(false)), | |
| AttachDuckDBDatabaseOption::Type(Ident::new("SQLITE")), | |
| ] | |
| }, | |
| stmt | |
| ); | |
| } | |
| #[test] | |
| fn test_attach_database_simple() { | |
| let sql = r#"ATTACH 'postgres://user.name:pass-word@some.url.com:5432/postgres'"#; | |
| let stmt = duckdb().verified_stmt(sql); | |
| assert_eq!( | |
| Statement::AttachDuckDBDatabase { | |
| if_not_exists: false, | |
| database: false, | |
| database_path: Ident::with_quote( | |
| '\'', | |
| "postgres://user.name:pass-word@some.url.com:5432/postgres" | |
| ), | |
| database_alias: None, | |
| attach_options: vec![] | |
| }, | |
| stmt | |
| ); | |
| } | |
| #[test] | |
| fn test_detach_database() { | |
| let sql = r#"DETACH DATABASE IF EXISTS db_name"#; | |
| let stmt = duckdb().verified_stmt(sql); | |
| assert_eq!( | |
| Statement::DetachDuckDBDatabase { | |
| if_exists: true, | |
| database: true, | |
| database_alias: Ident::new("db_name"), | |
| }, | |
| stmt | |
| ); | |
| } | |
| #[test] | |
| fn test_detach_database_simple() { | |
| let sql = r#"DETACH db_name"#; | |
| let stmt = duckdb().verified_stmt(sql); | |
| assert_eq!( | |
| Statement::DetachDuckDBDatabase { | |
| if_exists: false, | |
| database: false, | |
| database_alias: Ident::new("db_name"), | |
| }, | |
| stmt | |
| ); | |
| } | |
| #[test] | |
| fn test_duckdb_named_argument_function_with_assignment_operator() { | |
| let sql = "SELECT FUN(a := '1', b := '2') FROM foo"; | |
| let select = duckdb_and_generic().verified_only_select(sql); | |
| assert_eq!( | |
| &Expr::Function(Function { | |
| name: ObjectName::from(vec![Ident::new("FUN")]), | |
| uses_odbc_syntax: false, | |
| parameters: FunctionArguments::None, | |
| args: FunctionArguments::List(FunctionArgumentList { | |
| duplicate_treatment: None, | |
| args: vec![ | |
| FunctionArg::Named { | |
| name: Ident::new("a"), | |
| arg: FunctionArgExpr::Expr(Expr::Value( | |
| (Value::SingleQuotedString("1".to_owned())).with_empty_span() | |
| )), | |
| operator: FunctionArgOperator::Assignment | |
| }, | |
| FunctionArg::Named { | |
| name: Ident::new("b"), | |
| arg: FunctionArgExpr::Expr(Expr::Value( | |
| (Value::SingleQuotedString("2".to_owned())).with_empty_span() | |
| )), | |
| operator: FunctionArgOperator::Assignment | |
| }, | |
| ], | |
| clauses: vec![], | |
| }), | |
| null_treatment: None, | |
| filter: None, | |
| over: None, | |
| within_group: vec![], | |
| }), | |
| expr_from_projection(only(&select.projection)) | |
| ); | |
| } | |
| #[test] | |
| fn test_array_index() { | |
| let sql = r#"SELECT ['a', 'b', 'c'][3] AS three"#; | |
| let select = duckdb().verified_only_select(sql); | |
| let projection = &select.projection; | |
| assert_eq!(1, projection.len()); | |
| let expr = match &projection[0] { | |
| SelectItem::ExprWithAlias { expr, .. } => expr, | |
| _ => panic!("Expected an expression with alias"), | |
| }; | |
| assert_eq!( | |
| &Expr::CompoundFieldAccess { | |
| root: Box::new(Expr::Array(Array { | |
| elem: vec![ | |
| Expr::Value((Value::SingleQuotedString("a".to_owned())).with_empty_span()), | |
| Expr::Value((Value::SingleQuotedString("b".to_owned())).with_empty_span()), | |
| Expr::Value((Value::SingleQuotedString("c".to_owned())).with_empty_span()) | |
| ], | |
| named: false | |
| })), | |
| access_chain: vec![AccessExpr::Subscript(Subscript::Index { | |
| index: Expr::value(number("3")) | |
| })] | |
| }, | |
| expr | |
| ); | |
| } | |
| #[test] | |
| fn test_duckdb_union_datatype() { | |
| let sql = "CREATE TABLE tbl1 (one UNION(a INT), two UNION(a INT, b INT), nested UNION(a UNION(b INT)))"; | |
| let stmt = duckdb_and_generic().verified_stmt(sql); | |
| assert_eq!( | |
| Statement::CreateTable(CreateTable { | |
| or_replace: Default::default(), | |
| temporary: Default::default(), | |
| unlogged: Default::default(), | |
| external: Default::default(), | |
| global: Default::default(), | |
| if_not_exists: Default::default(), | |
| transient: Default::default(), | |
| volatile: Default::default(), | |
| iceberg: Default::default(), | |
| snapshot: false, | |
| dynamic: Default::default(), | |
| name: ObjectName::from(vec!["tbl1".into()]), | |
| columns: vec![ | |
| ColumnDef { | |
| name: "one".into(), | |
| data_type: DataType::Union(vec![UnionField { | |
| field_name: "a".into(), | |
| field_type: DataType::Int(None) | |
| }]), | |
| options: Default::default() | |
| }, | |
| ColumnDef { | |
| name: "two".into(), | |
| data_type: DataType::Union(vec![ | |
| UnionField { | |
| field_name: "a".into(), | |
| field_type: DataType::Int(None) | |
| }, | |
| UnionField { | |
| field_name: "b".into(), | |
| field_type: DataType::Int(None) | |
| } | |
| ]), | |
| options: Default::default() | |
| }, | |
| ColumnDef { | |
| name: "nested".into(), | |
| data_type: DataType::Union(vec![UnionField { | |
| field_name: "a".into(), | |
| field_type: DataType::Union(vec![UnionField { | |
| field_name: "b".into(), | |
| field_type: DataType::Int(None) | |
| }]) | |
| }]), | |
| options: Default::default() | |
| } | |
| ], | |
| constraints: Default::default(), | |
| hive_distribution: HiveDistributionStyle::NONE, | |
| hive_formats: None, | |
| file_format: Default::default(), | |
| location: Default::default(), | |
| query: Default::default(), | |
| without_rowid: Default::default(), | |
| like: Default::default(), | |
| clone: Default::default(), | |
| comment: Default::default(), | |
| on_commit: Default::default(), | |
| on_cluster: Default::default(), | |
| primary_key: Default::default(), | |
| order_by: Default::default(), | |
| partition_by: Default::default(), | |
| cluster_by: Default::default(), | |
| clustered_by: Default::default(), | |
| inherits: Default::default(), | |
| partition_of: Default::default(), | |
| for_values: Default::default(), | |
| strict: Default::default(), | |
| copy_grants: Default::default(), | |
| enable_schema_evolution: Default::default(), | |
| change_tracking: Default::default(), | |
| data_retention_time_in_days: Default::default(), | |
| max_data_extension_time_in_days: Default::default(), | |
| default_ddl_collation: Default::default(), | |
| with_aggregation_policy: Default::default(), | |
| with_row_access_policy: Default::default(), | |
| with_storage_lifecycle_policy: Default::default(), | |
| with_tags: Default::default(), | |
| base_location: Default::default(), | |
| external_volume: Default::default(), | |
| with_connection: Default::default(), | |
| catalog: Default::default(), | |
| catalog_sync: Default::default(), | |
| storage_serialization_policy: Default::default(), | |
| table_options: CreateTableOptions::None, | |
| target_lag: None, | |
| warehouse: None, | |
| version: None, | |
| refresh_mode: None, | |
| initialize: None, | |
| require_user: Default::default(), | |
| diststyle: Default::default(), | |
| distkey: Default::default(), | |
| sortkey: Default::default(), | |
| backup: Default::default(), | |
| multiset: Default::default(), | |
| fallback: Default::default(), | |
| with_data: Default::default(), | |
| }), | |
| stmt | |
| ); | |
| } | |
| #[test] | |
| fn parse_use() { | |
| let valid_object_names = [ | |
| "mydb", | |
| "SCHEMA", | |
| "DATABASE", | |
| "CATALOG", | |
| "WAREHOUSE", | |
| "DEFAULT", | |
| ]; | |
| let quote_styles = ['"', '\'']; | |
| for object_name in &valid_object_names { | |
| // Test single identifier without quotes | |
| assert_eq!( | |
| duckdb().verified_stmt(&format!("USE {object_name}")), | |
| Statement::Use(Use::Object(ObjectName::from(vec![Ident::new( | |
| object_name.to_string() | |
| )]))) | |
| ); | |
| for "e in "e_styles { | |
| // Test single identifier with different type of quotes | |
| assert_eq!( | |
| duckdb().verified_stmt(&format!("USE {quote}{object_name}{quote}")), | |
| Statement::Use(Use::Object(ObjectName::from(vec![Ident::with_quote( | |
| quote, | |
| object_name.to_string(), | |
| )]))) | |
| ); | |
| } | |
| } | |
| for "e in "e_styles { | |
| // Test double identifier with different type of quotes | |
| assert_eq!( | |
| duckdb().verified_stmt(&format!( | |
| "USE {quote}CATALOG{quote}.{quote}my_schema{quote}" | |
| )), | |
| Statement::Use(Use::Object(ObjectName::from(vec![ | |
| Ident::with_quote(quote, "CATALOG"), | |
| Ident::with_quote(quote, "my_schema") | |
| ]))) | |
| ); | |
| } | |
| // Test double identifier without quotes | |
| assert_eq!( | |
| duckdb().verified_stmt("USE mydb.my_schema"), | |
| Statement::Use(Use::Object(ObjectName::from(vec![ | |
| Ident::new("mydb"), | |
| Ident::new("my_schema") | |
| ]))) | |
| ); | |
| } | |
| #[test] | |
| fn test_duckdb_trim() { | |
| let real_sql = r#"SELECT customer_id, TRIM(item_price_id, '"', "a") AS item_price_id FROM models_staging.subscriptions"#; | |
| assert_eq!(duckdb().verified_stmt(real_sql).to_string(), real_sql); | |
| let sql_only_select = "SELECT TRIM('xyz', 'a')"; | |
| let select = duckdb().verified_only_select(sql_only_select); | |
| assert_eq!( | |
| &Expr::Trim { | |
| expr: Box::new(Expr::Value( | |
| Value::SingleQuotedString("xyz".to_owned()).with_empty_span() | |
| )), | |
| trim_where: None, | |
| trim_what: None, | |
| trim_characters: Some(vec![Expr::Value( | |
| Value::SingleQuotedString("a".to_owned()).with_empty_span() | |
| )]), | |
| }, | |
| expr_from_projection(only(&select.projection)) | |
| ); | |
| // missing comma separation | |
| let error_sql = "SELECT TRIM('xyz' 'a')"; | |
| assert_eq!( | |
| ParserError::ParserError("Expected: ), found: 'a'".to_owned()), | |
| duckdb().parse_sql_statements(error_sql).unwrap_err() | |
| ); | |
| } | |
| #[test] | |
| fn parse_extract_single_quotes() { | |
| let sql = "SELECT EXTRACT('month' FROM my_timestamp) FROM my_table"; | |
| duckdb().verified_stmt(sql); | |
| } | |
| #[test] | |
| fn test_duckdb_lambda_function() { | |
| // Test basic lambda with list_filter | |
| let sql = "SELECT [3, 4, 5, 6].list_filter(lambda x : x > 4)"; | |
| duckdb().verified_stmt(sql); | |
| // Test lambda with arrow syntax (also supported by DuckDB) | |
| let sql_arrow = "SELECT list_filter([1, 2, 3], x -> x > 1)"; | |
| duckdb().verified_stmt(sql_arrow); | |
| // Both readings of `->` print identically, so round-tripping cannot tell | |
| // a lambda from JSON member access. Assert the shape instead. | |
| let select = duckdb().verified_only_select(sql_arrow); | |
| let Expr::Function(func) = expr_from_projection(only(&select.projection)) else { | |
| panic!("expected a function call"); | |
| }; | |
| let FunctionArguments::List(args) = &func.args else { | |
| panic!("expected an argument list"); | |
| }; | |
| let [_, FunctionArg::Unnamed(FunctionArgExpr::Expr(Expr::Lambda(lambda)))] = &args.args[..] | |
| else { | |
| panic!("expected the second argument to be a lambda"); | |
| }; | |
| assert_eq!(LambdaSyntax::Arrow, lambda.syntax); | |
| // Test lambda with multiple parameters (with index) | |
| let sql_multi = "SELECT list_filter([1, 3, 1, 5], lambda x, i : x > i)"; | |
| duckdb().verified_stmt(sql_multi); | |
| // Test lambda in list_transform | |
| let sql_transform = "SELECT list_transform([1, 2, 3], lambda x : x * 2)"; | |
| duckdb().verified_stmt(sql_transform); | |
| } | |
| #[test] | |
| fn test_duckdb_nested_block_comments() { | |
| duckdb().one_statement_parses_to( | |
| "SELECT a /* outer /* inner */ still comment */ FROM t", | |
| "SELECT a FROM t", | |
| ); | |
| let err = duckdb() | |
| .parse_sql_statements("SELECT a /* outer /* inner */ FROM t") | |
| .unwrap_err(); | |
| assert!( | |
| err.to_string() | |
| .contains("Unexpected EOF while in a multi-line comment"), | |
| "{err}" | |
| ); | |
| } |