| // 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 Snowflake. The parser based on the |
| //! generic dialect is also tested (on the inputs it can handle). |
| |
| use sqlparser::ast::*; |
| use sqlparser::dialect::{GenericDialect, SnowflakeDialect}; |
| use sqlparser::parser::ParserError; |
| use sqlparser::tokenizer::*; |
| use test_utils::*; |
| |
| #[macro_use] |
| mod test_utils; |
| |
| #[test] |
| fn test_snowflake_create_table() { |
| let sql = "CREATE TABLE _my_$table (am00unt number)"; |
| match snowflake_and_generic().verified_stmt(sql) { |
| Statement::CreateTable { name, .. } => { |
| assert_eq!("_my_$table", name.to_string()); |
| } |
| _ => unreachable!(), |
| } |
| } |
| |
| #[test] |
| fn test_snowflake_create_transient_table() { |
| let sql = "CREATE TRANSIENT TABLE CUSTOMER (id INT, name VARCHAR(255))"; |
| match snowflake_and_generic().verified_stmt(sql) { |
| Statement::CreateTable { |
| name, transient, .. |
| } => { |
| assert_eq!("CUSTOMER", name.to_string()); |
| assert!(transient) |
| } |
| _ => unreachable!(), |
| } |
| } |
| |
| #[test] |
| fn test_snowflake_single_line_tokenize() { |
| let sql = "CREATE TABLE# this is a comment \ntable_1"; |
| let dialect = SnowflakeDialect {}; |
| let mut tokenizer = Tokenizer::new(&dialect, sql); |
| let tokens = tokenizer.tokenize().unwrap(); |
| |
| let expected = vec![ |
| Token::make_keyword("CREATE"), |
| Token::Whitespace(Whitespace::Space), |
| Token::make_keyword("TABLE"), |
| Token::Whitespace(Whitespace::SingleLineComment { |
| prefix: "#".to_string(), |
| comment: " this is a comment \n".to_string(), |
| }), |
| Token::make_word("table_1", None), |
| ]; |
| |
| assert_eq!(expected, tokens); |
| |
| let sql = "CREATE TABLE// this is a comment \ntable_1"; |
| let mut tokenizer = Tokenizer::new(&dialect, sql); |
| let tokens = tokenizer.tokenize().unwrap(); |
| |
| let expected = vec![ |
| Token::make_keyword("CREATE"), |
| Token::Whitespace(Whitespace::Space), |
| Token::make_keyword("TABLE"), |
| Token::Whitespace(Whitespace::SingleLineComment { |
| prefix: "//".to_string(), |
| comment: " this is a comment \n".to_string(), |
| }), |
| Token::make_word("table_1", None), |
| ]; |
| |
| assert_eq!(expected, tokens); |
| } |
| |
| #[test] |
| fn test_sf_derived_table_in_parenthesis() { |
| // Nesting a subquery in an extra set of parentheses is non-standard, |
| // but supported in Snowflake SQL |
| snowflake_and_generic().one_statement_parses_to( |
| "SELECT * FROM ((SELECT 1) AS t)", |
| "SELECT * FROM (SELECT 1) AS t", |
| ); |
| snowflake_and_generic().one_statement_parses_to( |
| "SELECT * FROM (((SELECT 1) AS t))", |
| "SELECT * FROM (SELECT 1) AS t", |
| ); |
| } |
| |
| #[test] |
| fn test_single_table_in_parenthesis() { |
| // Parenthesized table names are non-standard, but supported in Snowflake SQL |
| snowflake_and_generic().one_statement_parses_to( |
| "SELECT * FROM (a NATURAL JOIN (b))", |
| "SELECT * FROM (a NATURAL JOIN b)", |
| ); |
| snowflake_and_generic().one_statement_parses_to( |
| "SELECT * FROM (a NATURAL JOIN ((b)))", |
| "SELECT * FROM (a NATURAL JOIN b)", |
| ); |
| } |
| |
| #[test] |
| fn test_single_table_in_parenthesis_with_alias() { |
| snowflake_and_generic().one_statement_parses_to( |
| "SELECT * FROM (a NATURAL JOIN (b) c )", |
| "SELECT * FROM (a NATURAL JOIN b AS c)", |
| ); |
| |
| snowflake_and_generic().one_statement_parses_to( |
| "SELECT * FROM (a NATURAL JOIN ((b)) c )", |
| "SELECT * FROM (a NATURAL JOIN b AS c)", |
| ); |
| |
| snowflake_and_generic().one_statement_parses_to( |
| "SELECT * FROM (a NATURAL JOIN ( (b) c ) )", |
| "SELECT * FROM (a NATURAL JOIN b AS c)", |
| ); |
| |
| snowflake_and_generic().one_statement_parses_to( |
| "SELECT * FROM (a NATURAL JOIN ( (b) as c ) )", |
| "SELECT * FROM (a NATURAL JOIN b AS c)", |
| ); |
| |
| snowflake_and_generic().one_statement_parses_to( |
| "SELECT * FROM (a alias1 NATURAL JOIN ( (b) c ) )", |
| "SELECT * FROM (a AS alias1 NATURAL JOIN b AS c)", |
| ); |
| |
| snowflake_and_generic().one_statement_parses_to( |
| "SELECT * FROM (a as alias1 NATURAL JOIN ( (b) as c ) )", |
| "SELECT * FROM (a AS alias1 NATURAL JOIN b AS c)", |
| ); |
| |
| snowflake_and_generic().one_statement_parses_to( |
| "SELECT * FROM (a NATURAL JOIN b) c", |
| "SELECT * FROM (a NATURAL JOIN b) AS c", |
| ); |
| |
| let res = snowflake().parse_sql_statements("SELECT * FROM (a b) c"); |
| assert_eq!( |
| ParserError::ParserError("duplicate alias b".to_string()), |
| res.unwrap_err() |
| ); |
| } |
| |
| #[test] |
| fn parse_array() { |
| let sql = "SELECT CAST(a AS ARRAY) FROM customer"; |
| let select = snowflake().verified_only_select(sql); |
| assert_eq!( |
| &Expr::Cast { |
| expr: Box::new(Expr::Identifier(Ident::new("a"))), |
| data_type: DataType::Array(None), |
| }, |
| expr_from_projection(only(&select.projection)) |
| ); |
| } |
| |
| #[test] |
| fn parse_json_using_colon() { |
| let sql = "SELECT a:b FROM t"; |
| let select = snowflake().verified_only_select(sql); |
| assert_eq!( |
| SelectItem::UnnamedExpr(Expr::JsonAccess { |
| left: Box::new(Expr::Identifier(Ident::new("a"))), |
| operator: JsonOperator::Colon, |
| right: Box::new(Expr::Value(Value::UnQuotedString("b".to_string()))), |
| }), |
| select.projection[0] |
| ); |
| |
| let sql = "SELECT a:type FROM t"; |
| let select = snowflake().verified_only_select(sql); |
| assert_eq!( |
| SelectItem::UnnamedExpr(Expr::JsonAccess { |
| left: Box::new(Expr::Identifier(Ident::new("a"))), |
| operator: JsonOperator::Colon, |
| right: Box::new(Expr::Value(Value::UnQuotedString("type".to_string()))), |
| }), |
| select.projection[0] |
| ); |
| |
| let sql = "SELECT a:location FROM t"; |
| let select = snowflake().verified_only_select(sql); |
| assert_eq!( |
| SelectItem::UnnamedExpr(Expr::JsonAccess { |
| left: Box::new(Expr::Identifier(Ident::new("a"))), |
| operator: JsonOperator::Colon, |
| right: Box::new(Expr::Value(Value::UnQuotedString("location".to_string()))), |
| }), |
| select.projection[0] |
| ); |
| |
| snowflake().one_statement_parses_to("SELECT a:b::int FROM t", "SELECT CAST(a:b AS INT) FROM t"); |
| } |
| |
| #[test] |
| fn parse_delimited_identifiers() { |
| // check that quoted identifiers in any position remain quoted after serialization |
| let select = snowflake().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, |
| }), |
| 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"), |
| } |
| |
| snowflake().verified_stmt(r#"CREATE TABLE "foo" ("bar" "int")"#); |
| snowflake().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 = snowflake().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 = snowflake().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 = snowflake().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 = snowflake().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 = snowflake().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 = snowflake().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 test_array_agg_func() { |
| for sql in [ |
| "SELECT ARRAY_AGG(x) WITHIN GROUP (ORDER BY x) AS a FROM T", |
| "SELECT ARRAY_AGG(DISTINCT x) WITHIN GROUP (ORDER BY x ASC) FROM tbl", |
| ] { |
| snowflake().verified_stmt(sql); |
| } |
| |
| let sql = "select array_agg(x order by x) as a from T"; |
| let result = snowflake().parse_sql_statements(sql); |
| assert_eq!( |
| result, |
| Err(ParserError::ParserError(String::from( |
| "Expected ), found: order" |
| ))) |
| ) |
| } |
| |
| fn snowflake() -> TestedDialects { |
| TestedDialects { |
| dialects: vec![Box::new(SnowflakeDialect {})], |
| } |
| } |
| |
| fn snowflake_and_generic() -> TestedDialects { |
| TestedDialects { |
| dialects: vec![Box::new(SnowflakeDialect {}), Box::new(GenericDialect {})], |
| } |
| } |
| |
| #[test] |
| fn test_select_wildcard_with_exclude() { |
| let select = snowflake_and_generic().verified_only_select("SELECT * EXCLUDE (col_a) FROM data"); |
| let expected = SelectItem::Wildcard(WildcardAdditionalOptions { |
| opt_exclude: Some(ExcludeSelectItem::Multiple(vec![Ident::new("col_a")])), |
| ..Default::default() |
| }); |
| assert_eq!(expected, select.projection[0]); |
| |
| let select = snowflake_and_generic() |
| .verified_only_select("SELECT name.* EXCLUDE department_id FROM employee_table"); |
| let expected = SelectItem::QualifiedWildcard( |
| ObjectName(vec![Ident::new("name")]), |
| WildcardAdditionalOptions { |
| opt_exclude: Some(ExcludeSelectItem::Single(Ident::new("department_id"))), |
| ..Default::default() |
| }, |
| ); |
| assert_eq!(expected, select.projection[0]); |
| |
| let select = snowflake_and_generic() |
| .verified_only_select("SELECT * EXCLUDE (department_id, employee_id) FROM employee_table"); |
| let expected = SelectItem::Wildcard(WildcardAdditionalOptions { |
| opt_exclude: Some(ExcludeSelectItem::Multiple(vec![ |
| Ident::new("department_id"), |
| Ident::new("employee_id"), |
| ])), |
| ..Default::default() |
| }); |
| assert_eq!(expected, select.projection[0]); |
| } |
| |
| #[test] |
| fn test_select_wildcard_with_rename() { |
| let select = |
| snowflake_and_generic().verified_only_select("SELECT * RENAME col_a AS col_b FROM data"); |
| let expected = SelectItem::Wildcard(WildcardAdditionalOptions { |
| opt_rename: Some(RenameSelectItem::Single(IdentWithAlias { |
| ident: Ident::new("col_a"), |
| alias: Ident::new("col_b"), |
| })), |
| ..Default::default() |
| }); |
| assert_eq!(expected, select.projection[0]); |
| |
| let select = snowflake_and_generic().verified_only_select( |
| "SELECT name.* RENAME (department_id AS new_dep, employee_id AS new_emp) FROM employee_table", |
| ); |
| let expected = SelectItem::QualifiedWildcard( |
| ObjectName(vec![Ident::new("name")]), |
| WildcardAdditionalOptions { |
| opt_rename: Some(RenameSelectItem::Multiple(vec![ |
| IdentWithAlias { |
| ident: Ident::new("department_id"), |
| alias: Ident::new("new_dep"), |
| }, |
| IdentWithAlias { |
| ident: Ident::new("employee_id"), |
| alias: Ident::new("new_emp"), |
| }, |
| ])), |
| ..Default::default() |
| }, |
| ); |
| assert_eq!(expected, select.projection[0]); |
| } |
| |
| #[test] |
| fn test_select_wildcard_with_exclude_and_rename() { |
| let select = snowflake_and_generic() |
| .verified_only_select("SELECT * EXCLUDE col_z RENAME col_a AS col_b FROM data"); |
| let expected = SelectItem::Wildcard(WildcardAdditionalOptions { |
| opt_exclude: Some(ExcludeSelectItem::Single(Ident::new("col_z"))), |
| opt_rename: Some(RenameSelectItem::Single(IdentWithAlias { |
| ident: Ident::new("col_a"), |
| alias: Ident::new("col_b"), |
| })), |
| ..Default::default() |
| }); |
| assert_eq!(expected, select.projection[0]); |
| |
| // rename cannot precede exclude |
| assert_eq!( |
| snowflake_and_generic() |
| .parse_sql_statements("SELECT * RENAME col_a AS col_b EXCLUDE col_z FROM data") |
| .unwrap_err() |
| .to_string(), |
| "sql parser error: Expected end of statement, found: EXCLUDE" |
| ); |
| } |
