| // Licensed 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. |
| |
| #![warn(clippy::all)] |
| //! Test SQL syntax specific to ClickHouse. |
| |
| #[macro_use] |
| mod test_utils; |
| |
| use test_utils::*; |
| |
| use sqlparser::ast::Expr::{BinaryOp, Identifier, MapAccess}; |
| use sqlparser::ast::Ident; |
| use sqlparser::ast::SelectItem::UnnamedExpr; |
| use sqlparser::ast::TableFactor::Table; |
| use sqlparser::ast::*; |
| |
| use sqlparser::dialect::ClickHouseDialect; |
| |
| #[test] |
| fn parse_map_access_expr() { |
| let sql = r#"SELECT string_values[indexOf(string_names, 'endpoint')] FROM foos WHERE id = 'test' AND string_value[indexOf(string_name, 'app')] <> 'foo'"#; |
| let select = clickhouse().verified_only_select(sql); |
| assert_eq!( |
| Select { |
| distinct: None, |
| top: None, |
| projection: vec![UnnamedExpr(MapAccess { |
| column: Box::new(Identifier(Ident { |
| value: "string_values".to_string(), |
| quote_style: None, |
| })), |
| keys: vec![Expr::Function(Function { |
| name: ObjectName(vec!["indexOf".into()]), |
| args: vec![ |
| FunctionArg::Unnamed(FunctionArgExpr::Expr(Expr::Identifier(Ident::new( |
| "string_names" |
| )))), |
| FunctionArg::Unnamed(FunctionArgExpr::Expr(Expr::Value( |
| Value::SingleQuotedString("endpoint".to_string()) |
| ))), |
| ], |
| over: None, |
| distinct: false, |
| special: false, |
| order_by: vec![], |
| })], |
| })], |
| into: None, |
| from: vec![TableWithJoins { |
| relation: Table { |
| name: ObjectName(vec![Ident::new("foos")]), |
| alias: None, |
| args: None, |
| with_hints: vec![], |
| }, |
| joins: vec![] |
| }], |
| lateral_views: vec![], |
| selection: Some(BinaryOp { |
| left: Box::new(BinaryOp { |
| left: Box::new(Identifier(Ident::new("id"))), |
| op: BinaryOperator::Eq, |
| right: Box::new(Expr::Value(Value::SingleQuotedString("test".to_string()))) |
| }), |
| op: BinaryOperator::And, |
| right: Box::new(BinaryOp { |
| left: Box::new(MapAccess { |
| column: Box::new(Identifier(Ident::new("string_value"))), |
| keys: vec![Expr::Function(Function { |
| name: ObjectName(vec![Ident::new("indexOf")]), |
| args: vec![ |
| FunctionArg::Unnamed(FunctionArgExpr::Expr(Expr::Identifier( |
| Ident::new("string_name") |
| ))), |
| FunctionArg::Unnamed(FunctionArgExpr::Expr(Expr::Value( |
| Value::SingleQuotedString("app".to_string()) |
| ))), |
| ], |
| over: None, |
| distinct: false, |
| special: false, |
| order_by: vec![], |
| })] |
| }), |
| op: BinaryOperator::NotEq, |
| right: Box::new(Expr::Value(Value::SingleQuotedString("foo".to_string()))) |
| }) |
| }), |
| group_by: vec![], |
| cluster_by: vec![], |
| distribute_by: vec![], |
| sort_by: vec![], |
| having: None, |
| qualify: None |
| }, |
| select |
| ); |
| } |
| |
| #[test] |
| fn parse_array_expr() { |
| let sql = "SELECT ['1', '2'] FROM test"; |
| let select = clickhouse().verified_only_select(sql); |
| assert_eq!( |
| &Expr::Array(Array { |
| elem: vec![ |
| Expr::Value(Value::SingleQuotedString("1".to_string())), |
| Expr::Value(Value::SingleQuotedString("2".to_string())) |
| ], |
| named: false, |
| }), |
| expr_from_projection(only(&select.projection)) |
| ) |
| } |
| |
| #[test] |
| fn parse_array_fn() { |
| let sql = "SELECT array(x1, x2) FROM foo"; |
| let select = clickhouse().verified_only_select(sql); |
| assert_eq!( |
| &Expr::Function(Function { |
| name: ObjectName(vec![Ident::new("array")]), |
| args: vec![ |
| FunctionArg::Unnamed(FunctionArgExpr::Expr(Expr::Identifier(Ident::new("x1")))), |
| FunctionArg::Unnamed(FunctionArgExpr::Expr(Expr::Identifier(Ident::new("x2")))), |
| ], |
| over: None, |
| distinct: false, |
| special: false, |
| order_by: vec![], |
| }), |
| expr_from_projection(only(&select.projection)) |
| ); |
| } |
| |
| #[test] |
| fn parse_kill() { |
| let stmt = clickhouse().verified_stmt("KILL MUTATION 5"); |
| assert_eq!( |
| stmt, |
| Statement::Kill { |
| modifier: Some(KillType::Mutation), |
| id: 5, |
| } |
| ); |
| } |
| |
| #[test] |
| fn parse_delimited_identifiers() { |
| // check that quoted identifiers in any position remain quoted after serialization |
| let select = clickhouse().verified_only_select( |
| r#"SELECT "alias"."bar baz", "myfun"(), "simple id" AS "column alias" FROM "a table" AS "alias""#, |
| ); |
| // check FROM |
| match only(select.from).relation { |
| TableFactor::Table { |
| name, |
| alias, |
| args, |
| with_hints, |
| } => { |
| assert_eq!(vec![Ident::with_quote('"', "a table")], name.0); |
| assert_eq!(Ident::with_quote('"', "alias"), alias.unwrap().name); |
| assert!(args.is_none()); |
| assert!(with_hints.is_empty()); |
| } |
| _ => panic!("Expecting TableFactor::Table"), |
| } |
| // check SELECT |
| assert_eq!(3, select.projection.len()); |
| assert_eq!( |
| &Expr::CompoundIdentifier(vec![ |
| Ident::with_quote('"', "alias"), |
| Ident::with_quote('"', "bar baz"), |
| ]), |
| expr_from_projection(&select.projection[0]), |
| ); |
| assert_eq!( |
| &Expr::Function(Function { |
| name: ObjectName(vec![Ident::with_quote('"', "myfun")]), |
| args: vec![], |
| over: None, |
| distinct: false, |
| special: false, |
| order_by: vec![], |
| }), |
| expr_from_projection(&select.projection[1]), |
| ); |
| match &select.projection[2] { |
| SelectItem::ExprWithAlias { expr, alias } => { |
| assert_eq!(&Expr::Identifier(Ident::with_quote('"', "simple id")), expr); |
| assert_eq!(&Ident::with_quote('"', "column alias"), alias); |
| } |
| _ => panic!("Expected ExprWithAlias"), |
| } |
| |
| clickhouse().verified_stmt(r#"CREATE TABLE "foo" ("bar" "int")"#); |
| clickhouse().verified_stmt(r#"ALTER TABLE foo ADD CONSTRAINT "bar" PRIMARY KEY (baz)"#); |
| //TODO verified_stmt(r#"UPDATE foo SET "bar" = 5"#); |
| } |
| |
| #[test] |
| fn parse_like() { |
| fn chk(negated: bool) { |
| let sql = &format!( |
| "SELECT * FROM customers WHERE name {}LIKE '%a'", |
| if negated { "NOT " } else { "" } |
| ); |
| let select = clickhouse().verified_only_select(sql); |
| assert_eq!( |
| Expr::Like { |
| expr: Box::new(Expr::Identifier(Ident::new("name"))), |
| negated, |
| pattern: Box::new(Expr::Value(Value::SingleQuotedString("%a".to_string()))), |
| escape_char: None, |
| }, |
| select.selection.unwrap() |
| ); |
| |
| // Test with escape char |
| let sql = &format!( |
| "SELECT * FROM customers WHERE name {}LIKE '%a' ESCAPE '\\'", |
| if negated { "NOT " } else { "" } |
| ); |
| let select = clickhouse().verified_only_select(sql); |
| assert_eq!( |
| Expr::Like { |
| expr: Box::new(Expr::Identifier(Ident::new("name"))), |
| negated, |
| pattern: Box::new(Expr::Value(Value::SingleQuotedString("%a".to_string()))), |
| escape_char: Some('\\'), |
| }, |
| select.selection.unwrap() |
| ); |
| |
| // This statement tests that LIKE and NOT LIKE have the same precedence. |
| // This was previously mishandled (#81). |
| let sql = &format!( |
| "SELECT * FROM customers WHERE name {}LIKE '%a' IS NULL", |
| if negated { "NOT " } else { "" } |
| ); |
| let select = clickhouse().verified_only_select(sql); |
| assert_eq!( |
| Expr::IsNull(Box::new(Expr::Like { |
| expr: Box::new(Expr::Identifier(Ident::new("name"))), |
| negated, |
| pattern: Box::new(Expr::Value(Value::SingleQuotedString("%a".to_string()))), |
| escape_char: None, |
| })), |
| select.selection.unwrap() |
| ); |
| } |
| chk(false); |
| chk(true); |
| } |
| |
| #[test] |
| fn parse_similar_to() { |
| fn chk(negated: bool) { |
| let sql = &format!( |
| "SELECT * FROM customers WHERE name {}SIMILAR TO '%a'", |
| if negated { "NOT " } else { "" } |
| ); |
| let select = clickhouse().verified_only_select(sql); |
| assert_eq!( |
| Expr::SimilarTo { |
| expr: Box::new(Expr::Identifier(Ident::new("name"))), |
| negated, |
| pattern: Box::new(Expr::Value(Value::SingleQuotedString("%a".to_string()))), |
| escape_char: None, |
| }, |
| select.selection.unwrap() |
| ); |
| |
| // Test with escape char |
| let sql = &format!( |
| "SELECT * FROM customers WHERE name {}SIMILAR TO '%a' ESCAPE '\\'", |
| if negated { "NOT " } else { "" } |
| ); |
| let select = clickhouse().verified_only_select(sql); |
| assert_eq!( |
| Expr::SimilarTo { |
| expr: Box::new(Expr::Identifier(Ident::new("name"))), |
| negated, |
| pattern: Box::new(Expr::Value(Value::SingleQuotedString("%a".to_string()))), |
| escape_char: Some('\\'), |
| }, |
| select.selection.unwrap() |
| ); |
| |
| // This statement tests that SIMILAR TO and NOT SIMILAR TO have the same precedence. |
| let sql = &format!( |
| "SELECT * FROM customers WHERE name {}SIMILAR TO '%a' ESCAPE '\\' IS NULL", |
| if negated { "NOT " } else { "" } |
| ); |
| let select = clickhouse().verified_only_select(sql); |
| assert_eq!( |
| Expr::IsNull(Box::new(Expr::SimilarTo { |
| expr: Box::new(Expr::Identifier(Ident::new("name"))), |
| negated, |
| pattern: Box::new(Expr::Value(Value::SingleQuotedString("%a".to_string()))), |
| escape_char: Some('\\'), |
| })), |
| select.selection.unwrap() |
| ); |
| } |
| chk(false); |
| chk(true); |
| } |
| |
| #[test] |
| fn parse_create_table() { |
| clickhouse().verified_stmt(r#"CREATE TABLE "x" ("a" "int") ENGINE=MergeTree ORDER BY ("x")"#); |
| clickhouse().one_statement_parses_to( |
| r#"CREATE TABLE "x" ("a" "int") ENGINE=MergeTree ORDER BY "x""#, |
| r#"CREATE TABLE "x" ("a" "int") ENGINE=MergeTree ORDER BY ("x")"#, |
| ); |
| clickhouse().verified_stmt( |
| r#"CREATE TABLE "x" ("a" "int") ENGINE=MergeTree ORDER BY ("x") AS SELECT * FROM "t" WHERE true"#, |
| ); |
| } |
| |
| fn clickhouse() -> TestedDialects { |
| TestedDialects { |
| dialects: vec![Box::new(ClickHouseDialect {})], |
| options: None, |
| } |
| } |
