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

#![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::helpers::stmt_data_loading::{

DataLoadingOption, DataLoadingOptionType, StageLoadSelectItem,

};

use sqlparser::ast::*;

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

use sqlparser::parser::{ParserError, ParserOptions};

use sqlparser::tokenizer::*;

use test_utils::*;

#[macro_use]

mod test_utils;

#[cfg(test)]

use pretty_assertions::assert_eq;

#[test]

fn test_snowflake_create_table() {

let sql = "CREATE TABLE _my_$table (am00unt number)";

match snowflake_and_generic().verified_stmt(sql) {

Statement::CreateTable(CreateTable { name, .. }) => {

assert_eq!("_my_$table", name.to_string());

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_or_replace_table() {

let sql = "CREATE OR REPLACE TABLE my_table (a number)";

match snowflake().verified_stmt(sql) {

Statement::CreateTable(CreateTable {

name, or_replace, ..

}) => {

assert_eq!("my_table", name.to_string());

assert!(or_replace);

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_or_replace_table_copy_grants() {

let sql = "CREATE OR REPLACE TABLE my_table (a number) COPY GRANTS";

match snowflake().verified_stmt(sql) {

Statement::CreateTable(CreateTable {

name,

or_replace,

copy_grants,

..

}) => {

assert_eq!("my_table", name.to_string());

assert!(or_replace);

assert!(copy_grants);

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_or_replace_table_copy_grants_at_end() {

let sql = "CREATE OR REPLACE TABLE my_table COPY GRANTS (a number) ";

let parsed = "CREATE OR REPLACE TABLE my_table (a number) COPY GRANTS";

match snowflake().one_statement_parses_to(sql, parsed) {

Statement::CreateTable(CreateTable {

name,

or_replace,

copy_grants,

..

}) => {

assert_eq!("my_table", name.to_string());

assert!(or_replace);

assert!(copy_grants);

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_or_replace_table_copy_grants_cta() {

let sql = "CREATE OR REPLACE TABLE my_table COPY GRANTS AS SELECT 1 AS a";

match snowflake().verified_stmt(sql) {

Statement::CreateTable(CreateTable {

name,

or_replace,

copy_grants,

..

}) => {

assert_eq!("my_table", name.to_string());

assert!(or_replace);

assert!(copy_grants);

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_enable_schema_evolution() {

let sql = "CREATE TABLE my_table (a number) ENABLE_SCHEMA_EVOLUTION=TRUE";

match snowflake().verified_stmt(sql) {

Statement::CreateTable(CreateTable {

name,

enable_schema_evolution,

..

}) => {

assert_eq!("my_table", name.to_string());

assert_eq!(Some(true), enable_schema_evolution);

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_change_tracking() {

let sql = "CREATE TABLE my_table (a number) CHANGE_TRACKING=TRUE";

match snowflake().verified_stmt(sql) {

Statement::CreateTable(CreateTable {

name,

change_tracking,

..

}) => {

assert_eq!("my_table", name.to_string());

assert_eq!(Some(true), change_tracking);

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_data_retention_time_in_days() {

let sql = "CREATE TABLE my_table (a number) DATA_RETENTION_TIME_IN_DAYS=5";

match snowflake().verified_stmt(sql) {

Statement::CreateTable(CreateTable {

name,

data_retention_time_in_days,

..

}) => {

assert_eq!("my_table", name.to_string());

assert_eq!(Some(5), data_retention_time_in_days);

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_max_data_extension_time_in_days() {

let sql = "CREATE TABLE my_table (a number) MAX_DATA_EXTENSION_TIME_IN_DAYS=5";

match snowflake().verified_stmt(sql) {

Statement::CreateTable(CreateTable {

name,

max_data_extension_time_in_days,

..

}) => {

assert_eq!("my_table", name.to_string());

assert_eq!(Some(5), max_data_extension_time_in_days);

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_with_aggregation_policy() {

match snowflake()

.verified_stmt("CREATE TABLE my_table (a number) WITH AGGREGATION POLICY policy_name")

{

Statement::CreateTable(CreateTable {

name,

with_aggregation_policy,

..

}) => {

assert_eq!("my_table", name.to_string());

assert_eq!(

Some("policy_name".to_string()),

with_aggregation_policy.map(|name| name.to_string())

);

}

_ => unreachable!(),

}

match snowflake()

.parse_sql_statements("CREATE TABLE my_table (a number) AGGREGATION POLICY policy_name")

.unwrap()

.pop()

.unwrap()

{

Statement::CreateTable(CreateTable {

name,

with_aggregation_policy,

..

}) => {

assert_eq!("my_table", name.to_string());

assert_eq!(

Some("policy_name".to_string()),

with_aggregation_policy.map(|name| name.to_string())

);

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_with_row_access_policy() {

match snowflake().verified_stmt(

"CREATE TABLE my_table (a number, b number) WITH ROW ACCESS POLICY policy_name ON (a)",

) {

Statement::CreateTable(CreateTable {

name,

with_row_access_policy,

..

}) => {

assert_eq!("my_table", name.to_string());

assert_eq!(

Some("WITH ROW ACCESS POLICY policy_name ON (a)".to_string()),

with_row_access_policy.map(|policy| policy.to_string())

);

}

_ => unreachable!(),

}

match snowflake()

.parse_sql_statements(

"CREATE TABLE my_table (a number, b number) ROW ACCESS POLICY policy_name ON (a)",

)

.unwrap()

.pop()

.unwrap()

{

Statement::CreateTable(CreateTable {

name,

with_row_access_policy,

..

}) => {

assert_eq!("my_table", name.to_string());

assert_eq!(

Some("WITH ROW ACCESS POLICY policy_name ON (a)".to_string()),

with_row_access_policy.map(|policy| policy.to_string())

);

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_with_tag() {

match snowflake()

.verified_stmt("CREATE TABLE my_table (a number) WITH TAG (A='TAG A', B='TAG B')")

{

Statement::CreateTable(CreateTable {

name, with_tags, ..

}) => {

assert_eq!("my_table", name.to_string());

assert_eq!(

Some(vec![

Tag::new("A".into(), "TAG A".to_string()),

Tag::new("B".into(), "TAG B".to_string())

]),

with_tags

);

}

_ => unreachable!(),

}

match snowflake()

.parse_sql_statements("CREATE TABLE my_table (a number) TAG (A='TAG A', B='TAG B')")

.unwrap()

.pop()

.unwrap()

{

Statement::CreateTable(CreateTable {

name, with_tags, ..

}) => {

assert_eq!("my_table", name.to_string());

assert_eq!(

Some(vec![

Tag::new("A".into(), "TAG A".to_string()),

Tag::new("B".into(), "TAG B".to_string())

]),

with_tags

);

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_default_ddl_collation() {

let sql = "CREATE TABLE my_table (a number) DEFAULT_DDL_COLLATION='de'";

match snowflake().verified_stmt(sql) {

Statement::CreateTable(CreateTable {

name,

default_ddl_collation,

..

}) => {

assert_eq!("my_table", name.to_string());

assert_eq!(Some("de".to_string()), default_ddl_collation);

}

_ => 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(CreateTable {

name, transient, ..

}) => {

assert_eq!("CUSTOMER", name.to_string());

assert!(transient)

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_column_comment() {

let sql = "CREATE TABLE my_table (a STRING COMMENT 'some comment')";

match snowflake().verified_stmt(sql) {

Statement::CreateTable(CreateTable { name, columns, .. }) => {

assert_eq!("my_table", name.to_string());

assert_eq!(

vec![ColumnDef {

name: "a".into(),

data_type: DataType::String(None),

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::Comment("some comment".to_string())

}],

collation: None

}],

columns

)

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_local_table() {

match snowflake().verified_stmt("CREATE TABLE my_table (a INT)") {

Statement::CreateTable(CreateTable { name, global, .. }) => {

assert_eq!("my_table", name.to_string());

assert!(global.is_none())

}

_ => unreachable!(),

}

match snowflake().verified_stmt("CREATE LOCAL TABLE my_table (a INT)") {

Statement::CreateTable(CreateTable { name, global, .. }) => {

assert_eq!("my_table", name.to_string());

assert_eq!(Some(false), global)

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_global_table() {

match snowflake().verified_stmt("CREATE GLOBAL TABLE my_table (a INT)") {

Statement::CreateTable(CreateTable { name, global, .. }) => {

assert_eq!("my_table", name.to_string());

assert_eq!(Some(true), global)

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_invalid_local_global_table() {

assert_eq!(

snowflake().parse_sql_statements("CREATE LOCAL GLOBAL TABLE my_table (a INT)"),

Err(ParserError::ParserError(

"Expected: an SQL statement, found: LOCAL".to_string()

))

);

assert_eq!(

snowflake().parse_sql_statements("CREATE GLOBAL LOCAL TABLE my_table (a INT)"),

Err(ParserError::ParserError(

"Expected: an SQL statement, found: GLOBAL".to_string()

))

);

}

#[test]

fn test_snowflake_create_invalid_temporal_table() {

assert_eq!(

snowflake().parse_sql_statements("CREATE TEMP TEMPORARY TABLE my_table (a INT)"),

Err(ParserError::ParserError(

"Expected: an object type after CREATE, found: TEMPORARY".to_string()

))

);

assert_eq!(

snowflake().parse_sql_statements("CREATE TEMP VOLATILE TABLE my_table (a INT)"),

Err(ParserError::ParserError(

"Expected: an object type after CREATE, found: VOLATILE".to_string()

))

);

assert_eq!(

snowflake().parse_sql_statements("CREATE TEMP TRANSIENT TABLE my_table (a INT)"),

Err(ParserError::ParserError(

"Expected: an object type after CREATE, found: TRANSIENT".to_string()

))

);

}

#[test]

fn test_snowflake_create_table_if_not_exists() {

match snowflake().verified_stmt("CREATE TABLE IF NOT EXISTS my_table (a INT)") {

Statement::CreateTable(CreateTable {

name,

if_not_exists,

..

}) => {

assert_eq!("my_table", name.to_string());

assert!(if_not_exists)

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_cluster_by() {

match snowflake().verified_stmt("CREATE TABLE my_table (a INT) CLUSTER BY (a, b)") {

Statement::CreateTable(CreateTable {

name, cluster_by, ..

}) => {

assert_eq!("my_table", name.to_string());

assert_eq!(

Some(WrappedCollection::Parentheses(vec![

Ident::new("a"),

Ident::new("b"),

])),

cluster_by

)

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_comment() {

match snowflake().verified_stmt("CREATE TABLE my_table (a INT) COMMENT = 'some comment'") {

Statement::CreateTable(CreateTable { name, comment, .. }) => {

assert_eq!("my_table", name.to_string());

assert_eq!("some comment", comment.unwrap().to_string());

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_incomplete_statement() {

assert_eq!(

snowflake().parse_sql_statements("CREATE TABLE my_table"),

Err(ParserError::ParserError(

"unexpected end of input".to_string()

))

);

assert_eq!(

snowflake().parse_sql_statements("CREATE TABLE my_table; (c int)"),

Err(ParserError::ParserError(

"unexpected end of input".to_string()

))

);

}

#[test]

fn test_snowflake_single_line_tokenize() {

let sql = "CREATE TABLE# this is a comment \ntable_1";

let dialect = SnowflakeDialect {};

let tokens = Tokenizer::new(&dialect, sql).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 tokens = Tokenizer::new(&dialect, sql).tokenize().unwrap();

let expected = vec![

Token::make_keyword("CREATE"),

Token::Whitespace(Whitespace::Space),

Token::make_keyword("TABLE"),

Token::Whitespace(Whitespace::Space),

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_snowflake_create_table_with_autoincrement_columns() {

let sql = concat!(

"CREATE TABLE my_table (",

"a INT AUTOINCREMENT ORDER, ",

"b INT AUTOINCREMENT(100, 1) NOORDER, ",

"c INT IDENTITY, ",

"d INT IDENTITY START 100 INCREMENT 1 ORDER",

")"

);

// it is a snowflake specific options (AUTOINCREMENT/IDENTITY)

match snowflake().verified_stmt(sql) {

Statement::CreateTable(CreateTable { columns, .. }) => {

assert_eq!(

columns,

vec![

ColumnDef {

name: "a".into(),

data_type: DataType::Int(None),

collation: None,

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::Identity(IdentityPropertyKind::Autoincrement(

IdentityProperty {

parameters: None,

order: Some(IdentityPropertyOrder::Order),

}

))

}]

},

ColumnDef {

name: "b".into(),

data_type: DataType::Int(None),

collation: None,

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::Identity(IdentityPropertyKind::Autoincrement(

IdentityProperty {

parameters: Some(IdentityPropertyFormatKind::FunctionCall(

IdentityParameters {

seed: Expr::Value(number("100")),

increment: Expr::Value(number("1")),

}

)),

order: Some(IdentityPropertyOrder::NoOrder),

}

))

}]

},

ColumnDef {

name: "c".into(),

data_type: DataType::Int(None),

collation: None,

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::Identity(IdentityPropertyKind::Identity(

IdentityProperty {

parameters: None,

order: None,

}

))

}]

},

ColumnDef {

name: "d".into(),

data_type: DataType::Int(None),

collation: None,

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::Identity(IdentityPropertyKind::Identity(

IdentityProperty {

parameters: Some(

IdentityPropertyFormatKind::StartAndIncrement(

IdentityParameters {

seed: Expr::Value(number("100")),

increment: Expr::Value(number("1")),

}

)

),

order: Some(IdentityPropertyOrder::Order),

}

))

}]

},

]

);

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_with_collated_column() {

match snowflake_and_generic().verified_stmt("CREATE TABLE my_table (a TEXT COLLATE 'de_DE')") {

Statement::CreateTable(CreateTable { columns, .. }) => {

assert_eq!(

columns,

vec![ColumnDef {

name: "a".into(),

data_type: DataType::Text,

collation: Some(ObjectName(vec![Ident::with_quote('\'', "de_DE")])),

options: vec![]

},]

);

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_with_columns_masking_policy() {

for (sql, with, using_columns) in [

(

"CREATE TABLE my_table (a INT WITH MASKING POLICY p)",

true,

None,

),

(

"CREATE TABLE my_table (a INT MASKING POLICY p)",

false,

None,

),

(

"CREATE TABLE my_table (a INT WITH MASKING POLICY p USING (a, b))",

true,

Some(vec!["a".into(), "b".into()]),

),

(

"CREATE TABLE my_table (a INT MASKING POLICY p USING (a, b))",

false,

Some(vec!["a".into(), "b".into()]),

),

] {

match snowflake().verified_stmt(sql) {

Statement::CreateTable(CreateTable { columns, .. }) => {

assert_eq!(

columns,

vec![ColumnDef {

name: "a".into(),

data_type: DataType::Int(None),

collation: None,

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::Policy(ColumnPolicy::MaskingPolicy(

ColumnPolicyProperty {

with,

policy_name: "p".into(),

using_columns,

}

))

}],

},]

);

}

_ => unreachable!(),

}

}

}

#[test]

fn test_snowflake_create_table_with_columns_projection_policy() {

for (sql, with) in [

(

"CREATE TABLE my_table (a INT WITH PROJECTION POLICY p)",

true,

),

("CREATE TABLE my_table (a INT PROJECTION POLICY p)", false),

] {

match snowflake().verified_stmt(sql) {

Statement::CreateTable(CreateTable { columns, .. }) => {

assert_eq!(

columns,

vec![ColumnDef {

name: "a".into(),

data_type: DataType::Int(None),

collation: None,

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::Policy(ColumnPolicy::ProjectionPolicy(

ColumnPolicyProperty {

with,

policy_name: "p".into(),

using_columns: None,

}

))

}],

},]

);

}

_ => unreachable!(),

}

}

}

#[test]

fn test_snowflake_create_table_with_columns_tags() {

for (sql, with) in [

(

"CREATE TABLE my_table (a INT WITH TAG (A='TAG A', B='TAG B'))",

true,

),

(

"CREATE TABLE my_table (a INT TAG (A='TAG A', B='TAG B'))",

false,

),

] {

match snowflake().verified_stmt(sql) {

Statement::CreateTable(CreateTable { columns, .. }) => {

assert_eq!(

columns,

vec![ColumnDef {

name: "a".into(),

data_type: DataType::Int(None),

collation: None,

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::Tags(TagsColumnOption {

with,

tags: vec![

Tag::new("A".into(), "TAG A".into()),

Tag::new("B".into(), "TAG B".into()),

]

}),

}],

},]

);

}

_ => unreachable!(),

}

}

}

#[test]

fn test_snowflake_create_table_with_several_column_options() {

let sql = concat!(

"CREATE TABLE my_table (",

"a INT IDENTITY WITH MASKING POLICY p1 USING (a, b) WITH TAG (A='TAG A', B='TAG B'), ",

"b TEXT COLLATE 'de_DE' PROJECTION POLICY p2 TAG (C='TAG C', D='TAG D')",

")"

);

match snowflake().verified_stmt(sql) {

Statement::CreateTable(CreateTable { columns, .. }) => {

assert_eq!(

columns,

vec![

ColumnDef {

name: "a".into(),

data_type: DataType::Int(None),

collation: None,

options: vec![

ColumnOptionDef {

name: None,

option: ColumnOption::Identity(IdentityPropertyKind::Identity(

IdentityProperty {

parameters: None,

order: None

}

)),

},

ColumnOptionDef {

name: None,

option: ColumnOption::Policy(ColumnPolicy::MaskingPolicy(

ColumnPolicyProperty {

with: true,

policy_name: "p1".into(),

using_columns: Some(vec!["a".into(), "b".into()]),

}

)),

},

ColumnOptionDef {

name: None,

option: ColumnOption::Tags(TagsColumnOption {

with: true,

tags: vec![

Tag::new("A".into(), "TAG A".into()),

Tag::new("B".into(), "TAG B".into()),

]

}),

}

],

},

ColumnDef {

name: "b".into(),

data_type: DataType::Text,

collation: Some(ObjectName(vec![Ident::with_quote('\'', "de_DE")])),

options: vec![

ColumnOptionDef {

name: None,

option: ColumnOption::Policy(ColumnPolicy::ProjectionPolicy(

ColumnPolicyProperty {

with: false,

policy_name: "p2".into(),

using_columns: None,

}

)),

},

ColumnOptionDef {

name: None,

option: ColumnOption::Tags(TagsColumnOption {

with: false,

tags: vec![

Tag::new("C".into(), "TAG C".into()),

Tag::new("D".into(), "TAG D".into()),

]

}),

}

],

},

]

);

}

_ => unreachable!(),

}

}

#[test]

fn parse_sf_create_or_replace_view_with_comment_missing_equal() {

assert!(snowflake_and_generic()

.parse_sql_statements("CREATE OR REPLACE VIEW v COMMENT = 'hello, world' AS SELECT 1")

.is_ok());

assert!(snowflake_and_generic()

.parse_sql_statements("CREATE OR REPLACE VIEW v COMMENT 'hello, world' AS SELECT 1")

.is_err());

}

#[test]

fn parse_sf_create_or_replace_with_comment_for_snowflake() {

let sql = "CREATE OR REPLACE VIEW v COMMENT = 'hello, world' AS SELECT 1";

let dialect =

test_utils::TestedDialects::new(vec![Box::new(SnowflakeDialect {}) as Box<dyn Dialect>]);

match dialect.verified_stmt(sql) {

Statement::CreateView {

name,

columns,

or_replace,

options,

query,

materialized,

cluster_by,

comment,

with_no_schema_binding: late_binding,

if_not_exists,

temporary,

..

} => {

assert_eq!("v", name.to_string());

assert_eq!(columns, vec![]);

assert_eq!(options, CreateTableOptions::None);

assert_eq!("SELECT 1", query.to_string());

assert!(!materialized);

assert!(or_replace);

assert_eq!(cluster_by, vec![]);

assert!(comment.is_some());

assert_eq!(comment.expect("expected comment"), "hello, world");

assert!(!late_binding);

assert!(!if_not_exists);

assert!(!temporary);

}

_ => unreachable!(),

}

}

#[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 {

kind: CastKind::Cast,

expr: Box::new(Expr::Identifier(Ident::new("a"))),

data_type: DataType::Array(ArrayElemTypeDef::None),

format: None,

},

expr_from_projection(only(&select.projection))

);

}

#[test]

fn parse_lateral_flatten() {

snowflake().verified_only_select(r#"SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88]}'), outer => true)) AS f"#);

snowflake().verified_only_select(r#"SELECT emp.employee_ID, emp.last_name, index, value AS project_name FROM employees AS emp, LATERAL FLATTEN(INPUT => emp.project_names) AS proj_names"#);

}

// https://docs.snowflake.com/en/user-guide/querying-semistructured

#[test]

fn parse_semi_structured_data_traversal() {

// most basic case

let sql = "SELECT a:b FROM t";

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

assert_eq!(

SelectItem::UnnamedExpr(Expr::JsonAccess {

value: Box::new(Expr::Identifier(Ident::new("a"))),

path: JsonPath {

path: vec![JsonPathElem::Dot {

key: "b".to_owned(),

quoted: false

}]

},

}),

select.projection[0]