// 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::key_value_options::{KeyValueOption, KeyValueOptionKind};

use sqlparser::ast::helpers::stmt_data_loading::{StageLoadSelectItem, StageLoadSelectItemKind};

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

for sql in [

"CREATE SECURE VIEW v AS SELECT 1",

"CREATE SECURE MATERIALIZED VIEW v AS SELECT 1",

"CREATE OR REPLACE SECURE VIEW v AS SELECT 1",

"CREATE OR REPLACE SECURE MATERIALIZED VIEW v AS SELECT 1",

] {

match snowflake().verified_stmt(sql) {

Statement::CreateView(CreateView {

secure,

materialized,

..

}) => {

assert!(secure);

if sql.contains("MATERIALIZED") {

assert!(materialized);

} else {

assert!(!materialized);

}

}

_ => unreachable!(),

}

assert_eq!(snowflake().verified_stmt(sql).to_string(), sql);

}

}

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

// WITH keyword

match snowflake().verified_stmt(

"CREATE TABLE IF NOT EXISTS my_table (a NUMBER(38, 0), b VARIANT) WITH STORAGE LIFECYCLE POLICY dba.global_settings.my_policy ON (a)",

) {

Statement::CreateTable(CreateTable {

name,

with_storage_lifecycle_policy,

..

}) => {

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

let policy = with_storage_lifecycle_policy.unwrap();

assert_eq!("dba.global_settings.my_policy", policy.policy.to_string());

assert_eq!(vec![Ident::new("a")], policy.on);

}

_ => unreachable!(),

}

// Without WITH keyword — canonicalizes to WITH form

snowflake().one_statement_parses_to(

"CREATE TABLE my_table (a NUMBER(38, 0)) STORAGE LIFECYCLE POLICY my_policy ON (a, b)",

"CREATE TABLE my_table (a NUMBER(38, 0)) WITH STORAGE LIFECYCLE POLICY my_policy ON (a, b)",

);

}

#[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(ObjectName::from(vec![Ident::new("A")]), "TAG A".to_string()),

Tag::new(ObjectName::from(vec![Ident::new("B")]), "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(ObjectName::from(vec![Ident::new("A")]), "TAG A".to_string()),

Tag::new(ObjectName::from(vec![Ident::new("B")]), "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())

}],

}],

columns

)

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_table_on_commit() {

snowflake().verified_stmt(

r#"CREATE LOCAL TEMPORARY TABLE "AAA"."foo" ("bar" INTEGER) ON COMMIT PRESERVE ROWS"#,

);

snowflake().verified_stmt(r#"CREATE TABLE "AAA"."foo" ("bar" INTEGER) ON COMMIT DELETE ROWS"#);

snowflake().verified_stmt(r#"CREATE TABLE "AAA"."foo" ("bar" INTEGER) ON COMMIT DROP"#);

}

#[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 TRANSIENT TABLE my_table (a INT)"),

Err(ParserError::ParserError(

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

))

);

}

#[test]

fn test_snowflake_create_invalid_temporal_file_format() {

assert_eq!(

snowflake().parse_sql_statements("CREATE TEMPORARY VOLATILE FILE FORMAT my_fmt"),

Err(ParserError::ParserError(

"Expected: an object type after CREATE, found: FILE".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!(),

}

for (sql, parse_to) in [

(

r#"CREATE TABLE IF NOT EXISTS "A"."B"."C" (v VARIANT)"#,

r#"CREATE TABLE IF NOT EXISTS "A"."B"."C" (v VARIANT)"#,

),

(

r#"CREATE TABLE "A"."B"."C" IF NOT EXISTS (v VARIANT)"#,

r#"CREATE TABLE IF NOT EXISTS "A"."B"."C" (v VARIANT)"#,

),

(

r#"CREATE TRANSIENT TABLE IF NOT EXISTS "A"."B"."C" (v VARIANT)"#,

r#"CREATE TRANSIENT TABLE IF NOT EXISTS "A"."B"."C" (v VARIANT)"#,

),

(

r#"CREATE TRANSIENT TABLE "A"."B"."C" IF NOT EXISTS (v VARIANT)"#,

r#"CREATE TRANSIENT TABLE IF NOT EXISTS "A"."B"."C" (v VARIANT)"#,

),

] {

snowflake().one_statement_parses_to(sql, parse_to);

}

}

#[test]

fn test_snowflake_create_table_cluster_by() {

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

Statement::CreateTable(CreateTable {

name, cluster_by, ..

}) => {

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

assert_eq!(

Some(WrappedCollection::Parentheses(vec![

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

Expr::Identifier(Ident::new("b")),

Expr::Function(Function {

name: ObjectName::from(vec![Ident::new("my_func")]),

uses_odbc_syntax: false,

parameters: FunctionArguments::None,

args: FunctionArguments::List(FunctionArgumentList {

args: vec![FunctionArg::Unnamed(FunctionArgExpr::Expr(

Expr::Identifier(Ident::new("c"))

))],

duplicate_treatment: None,

clauses: vec![],

}),

filter: None,

null_treatment: None,

over: None,

within_group: vec![],

}),

])),

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,

table_options,

..

}) => {

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

let plain_options = match table_options {

CreateTableOptions::Plain(options) => options,

_ => unreachable!(),

};

let comment = match plain_options.first().unwrap() {

SqlOption::Comment(CommentDef::WithEq(c))

| SqlOption::Comment(CommentDef::WithoutEq(c)) => c,

_ => unreachable!(),

};

assert_eq!("some comment", comment);

}

_ => unreachable!(),

}

}

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

}),

Token::Whitespace(Whitespace::Newline),

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 ".to_string(),

}),

Token::Whitespace(Whitespace::Newline),

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),

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),

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),

options: vec![ColumnOptionDef {

name: None,

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

IdentityProperty {

parameters: None,

order: None,

}

))

}]

},

ColumnDef {

name: "d".into(),

data_type: DataType::Int(None),

options: vec![ColumnOptionDef {

name: None,

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

IdentityProperty {

parameters: Some(

IdentityPropertyFormatKind::StartAndIncrement(

IdentityParameters {

seed: Expr::Value(

(number("100")).with_empty_span()

),

increment: Expr::Value(

(number("1")).with_empty_span()

),

}

)

),

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,

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::Collation(ObjectName::from(vec![Ident::with_quote(

'\'', "de_DE"

)])),

}]

},]

);

}

_ => 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),

options: vec![ColumnOptionDef {

name: None,

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

ColumnPolicyProperty {

with,

policy_name: ObjectName::from(vec![Ident::new("p")]),

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),

options: vec![ColumnOptionDef {

name: None,

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

ColumnPolicyProperty {

with,

policy_name: ObjectName::from(vec![Ident::new("p")]),

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),

options: vec![ColumnOptionDef {

name: None,

option: ColumnOption::Tags(TagsColumnOption {

with,

tags: vec![

Tag::new(

ObjectName::from(vec![Ident::new("A")]),

"TAG A".into()

),

Tag::new(

ObjectName::from(vec![Ident::new("B")]),

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

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: ObjectName::from(vec![Ident::new("p1")]),

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

}

)),

},

ColumnOptionDef {

name: None,

option: ColumnOption::Tags(TagsColumnOption {

with: true,

tags: vec![

Tag::new(

ObjectName::from(vec![Ident::new("A")]),

"TAG A".into()

),

Tag::new(

ObjectName::from(vec![Ident::new("B")]),

"TAG B".into()

),

]

}),

}

],

},

ColumnDef {

name: "b".into(),

data_type: DataType::Text,

options: vec![

ColumnOptionDef {

name: None,

option: ColumnOption::Collation(ObjectName::from(vec![

Ident::with_quote('\'', "de_DE")

])),

},

ColumnOptionDef {

name: None,

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

ColumnPolicyProperty {

with: false,

policy_name: ObjectName::from(vec![Ident::new("p2")]),

using_columns: None,

}

)),

},

ColumnOptionDef {

name: None,

option: ColumnOption::Tags(TagsColumnOption {

with: false,

tags: vec![

Tag::new(

ObjectName::from(vec![Ident::new("C")]),

"TAG C".into()

),

Tag::new(

ObjectName::from(vec![Ident::new("D")]),

"TAG D".into()

),

]

}),

}

],

},

]

);

}

_ => unreachable!(),

}

}

#[test]

fn test_snowflake_create_iceberg_table_all_options() {

match snowflake().verified_stmt("CREATE ICEBERG TABLE my_table (a INT, b INT) \

CLUSTER BY (a, b) EXTERNAL_VOLUME='volume' CATALOG='SNOWFLAKE' BASE_LOCATION='relative/path' CATALOG_SYNC='OPEN_CATALOG' \

STORAGE_SERIALIZATION_POLICY=COMPATIBLE COPY GRANTS CHANGE_TRACKING=TRUE DATA_RETENTION_TIME_IN_DAYS=5 MAX_DATA_EXTENSION_TIME_IN_DAYS=10 \

WITH AGGREGATION POLICY policy_name WITH ROW ACCESS POLICY policy_name ON (a) WITH TAG (A='TAG A', B='TAG B')") {

Statement::CreateTable(CreateTable {

name, cluster_by, base_location,

external_volume, catalog, catalog_sync,

storage_serialization_policy, change_tracking,

copy_grants, data_retention_time_in_days,

max_data_extension_time_in_days, with_aggregation_policy,

with_row_access_policy, with_tags, ..

}) => {

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

assert_eq!(

Some(WrappedCollection::Parentheses(vec![

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

Expr::Identifier(Ident::new("b")),

])),

cluster_by

);

assert_eq!("relative/path", base_location.unwrap());

assert_eq!("volume", external_volume.unwrap());

assert_eq!("SNOWFLAKE", catalog.unwrap());

assert_eq!("OPEN_CATALOG", catalog_sync.unwrap());

assert_eq!(StorageSerializationPolicy::Compatible, storage_serialization_policy.unwrap());

assert!(change_tracking.unwrap());

assert!(copy_grants);

assert_eq!(Some(5), data_retention_time_in_days);

assert_eq!(Some(10), max_data_extension_time_in_days);

assert_eq!(

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

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

);

assert_eq!(

Some("policy_name".to_string()),