// 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).

#[macro_use]

mod test_utils;

use test_utils::*;

use sqlparser::ast::*;

use sqlparser::dialect::{GenericDialect, SnowflakeDialect};

use sqlparser::parser::ParserError;

use sqlparser::tokenizer::*;

#[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_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 parse_colon_map_access_expr() {

let sql = "SELECT foo:key1:key2 FROM foos";

let select = snowflake_and_generic().verified_only_select(sql);

assert_eq!(

&Expr::MapAccess {

column: Box::new(Expr::Identifier(Ident {

value: "foo".to_string(),

quote_style: None

})),

keys: vec![

Value::ColonString("key1".to_string()),

Value::ColonString("key2".to_string()),

]

},

expr_from_projection(only(&select.projection)),

);

let sql = r#"SELECT foo:key1.key2["key3"] FROM foos"#;

let select = snowflake_and_generic().verified_only_select(sql);

assert_eq!(

&Expr::MapAccess {

column: Box::new(Expr::Identifier(Ident {

value: "foo".to_string(),

quote_style: None

})),

keys: vec![

Value::ColonString("key1".to_string()),

Value::PeriodString("key2".to_string()),

Value::SingleQuotedString("key3".to_string()),

]

},

expr_from_projection(only(&select.projection)),

);

let sql = "SELECT foo:key1:0 FROM foos";

let res = snowflake_and_generic().parse_sql_statements(sql);

assert_eq!(

ParserError::ParserError("Expected literal string, found: 0".to_string()),

res.unwrap_err()

);

}

#[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)",

);

let res = snowflake_and_generic().parse_sql_statements("SELECT * FROM (a NATURAL JOIN b) c");

assert_eq!(

ParserError::ParserError("Expected end of statement, found: c".to_string()),

res.unwrap_err()

);

let res = snowflake().parse_sql_statements("SELECT * FROM (a b) c");

assert_eq!(

ParserError::ParserError("duplicate alias b".to_string()),

res.unwrap_err()

);

}

fn snowflake() -> TestedDialects {

TestedDialects {

dialects: vec![Box::new(SnowflakeDialect {})],

}

}

fn snowflake_and_generic() -> TestedDialects {

TestedDialects {

dialects: vec![Box::new(SnowflakeDialect {}), Box::new(GenericDialect {})],

}

}