This is an automated email from the ASF dual-hosted git repository. github-bot pushed a commit to branch gh-readonly-queue/main/pr-2148-03f00cdb2724d482ac7f909c9e79080942b8bce3 in repository https://gitbox.apache.org/repos/asf/datafusion-sqlparser-rs.git
commit 75f6f4b17ae316694e877ce15ee81f692ba297a3 Author: finchxxia <[email protected]> AuthorDate: Tue Feb 10 19:18:57 2026 +0800 Snowflake: Add multi table insert support (#2148) --- src/ast/dml.rs | 198 ++++++++++++++++++++++++-- src/ast/mod.rs | 4 +- src/ast/spans.rs | 8 +- src/dialect/snowflake.rs | 201 ++++++++++++++++++++++++++- src/parser/mod.rs | 4 + tests/sqlparser_postgres.rs | 12 ++ tests/sqlparser_snowflake.rs | 321 +++++++++++++++++++++++++++++++++++++++++++ 7 files changed, 733 insertions(+), 15 deletions(-) diff --git a/src/ast/dml.rs b/src/ast/dml.rs index 4c36f705..f9c8823a 100644 --- a/src/ast/dml.rs +++ b/src/ast/dml.rs @@ -96,10 +96,35 @@ pub struct Insert { /// /// [ClickHouse formats JSON insert](https://clickhouse.com/docs/en/interfaces/formats#json-inserting-data) pub format_clause: Option<InputFormatClause>, + /// For Snowflake multi-table insert: specifies the type (`ALL` or `FIRST`) + /// + /// - `None` means this is a regular single-table INSERT + /// - `Some(All)` means `INSERT ALL` (all matching WHEN clauses are executed) + /// - `Some(First)` means `INSERT FIRST` (only the first matching WHEN clause is executed) + /// + /// See: <https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table> + pub multi_table_insert_type: Option<MultiTableInsertType>, + /// For multi-table insert: additional INTO clauses (unconditional) + /// + /// Used for `INSERT ALL INTO t1 INTO t2 ... SELECT ...` + /// + /// See: <https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table> + pub multi_table_into_clauses: Vec<MultiTableInsertIntoClause>, + /// For conditional multi-table insert: WHEN clauses + /// + /// Used for `INSERT ALL/FIRST WHEN cond THEN INTO t1 ... SELECT ...` + /// + /// See: <https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table> + pub multi_table_when_clauses: Vec<MultiTableInsertWhenClause>, + /// For conditional multi-table insert: ELSE clause + /// + /// See: <https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table> + pub multi_table_else_clause: Option<Vec<MultiTableInsertIntoClause>>, } impl Display for Insert { fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result { + // SQLite OR conflict has a special format: INSERT OR ... INTO table_name let table_name = if let Some(alias) = &self.table_alias { format!("{0} AS {alias}", self.table) } else { @@ -126,29 +151,46 @@ impl Display for Insert { write!(f, " {hint}")?; } if let Some(priority) = self.priority { - write!(f, " {priority}",)?; + write!(f, " {priority}")?; } - write!( - f, - "{ignore}{over}{int}{tbl} {table_name} ", - table_name = table_name, - ignore = if self.ignore { " IGNORE" } else { "" }, - over = if self.overwrite { " OVERWRITE" } else { "" }, - int = if self.into { " INTO" } else { "" }, - tbl = if self.has_table_keyword { " TABLE" } else { "" }, - )?; + if self.ignore { + write!(f, " IGNORE")?; + } + + if self.overwrite { + write!(f, " OVERWRITE")?; + } + + if let Some(insert_type) = &self.multi_table_insert_type { + write!(f, " {}", insert_type)?; + } + + if self.into { + write!(f, " INTO")?; + } + + if self.has_table_keyword { + write!(f, " TABLE")?; + } + + if !table_name.is_empty() { + write!(f, " {table_name} ")?; + } } + if !self.columns.is_empty() { write!(f, "({})", display_comma_separated(&self.columns))?; SpaceOrNewline.fmt(f)?; } + if let Some(ref parts) = self.partitioned { if !parts.is_empty() { write!(f, "PARTITION ({})", display_comma_separated(parts))?; SpaceOrNewline.fmt(f)?; } } + if !self.after_columns.is_empty() { write!(f, "({})", display_comma_separated(&self.after_columns))?; SpaceOrNewline.fmt(f)?; @@ -159,7 +201,31 @@ impl Display for Insert { SpaceOrNewline.fmt(f)?; } + for into_clause in &self.multi_table_into_clauses { + SpaceOrNewline.fmt(f)?; + write!(f, "{}", into_clause)?; + } + + for when_clause in &self.multi_table_when_clauses { + SpaceOrNewline.fmt(f)?; + write!(f, "{}", when_clause)?; + } + + if let Some(else_clauses) = &self.multi_table_else_clause { + SpaceOrNewline.fmt(f)?; + write!(f, "ELSE")?; + for into_clause in else_clauses { + SpaceOrNewline.fmt(f)?; + write!(f, "{}", into_clause)?; + } + } + if let Some(source) = &self.source { + if !self.multi_table_into_clauses.is_empty() + || !self.multi_table_when_clauses.is_empty() + { + SpaceOrNewline.fmt(f)?; + } source.fmt(f)?; } else if !self.assignments.is_empty() { write!(f, "SET")?; @@ -189,6 +255,7 @@ impl Display for Insert { f.write_str("RETURNING")?; indented_list(f, returning)?; } + Ok(()) } } @@ -695,3 +762,114 @@ impl fmt::Display for OutputClause { } } } + +/// A WHEN clause in a conditional multi-table INSERT. +/// +/// Syntax: +/// ```sql +/// WHEN n1 > 100 THEN +/// INTO t1 +/// INTO t2 (c1, c2) VALUES (n1, n2) +/// ``` +#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)] +#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] +#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))] +pub struct MultiTableInsertWhenClause { + /// The condition for this WHEN clause + pub condition: Expr, + /// The INTO clauses to execute when the condition is true + pub into_clauses: Vec<MultiTableInsertIntoClause>, +} + +impl Display for MultiTableInsertWhenClause { + fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result { + write!(f, "WHEN {} THEN", self.condition)?; + for into_clause in &self.into_clauses { + SpaceOrNewline.fmt(f)?; + write!(f, "{}", into_clause)?; + } + Ok(()) + } +} + +/// An INTO clause in a multi-table INSERT. +/// +/// Syntax: +/// ```sql +/// INTO <target_table> [ ( <target_col_name> [ , ... ] ) ] [ VALUES ( { <source_col_name> | DEFAULT | NULL } [ , ... ] ) ] +/// ``` +#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)] +#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] +#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))] +pub struct MultiTableInsertIntoClause { + /// The target table + pub table_name: ObjectName, + /// The target columns (optional) + pub columns: Vec<Ident>, + /// The VALUES clause (optional) + pub values: Option<MultiTableInsertValues>, +} + +impl Display for MultiTableInsertIntoClause { + fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result { + write!(f, "INTO {}", self.table_name)?; + if !self.columns.is_empty() { + write!(f, " ({})", display_comma_separated(&self.columns))?; + } + if let Some(values) = &self.values { + write!(f, " VALUES ({})", display_comma_separated(&values.values))?; + } + Ok(()) + } +} + +/// The VALUES clause in a multi-table INSERT INTO clause. +#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)] +#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] +#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))] +pub struct MultiTableInsertValues { + /// The values to insert (can be column references, DEFAULT, or NULL) + pub values: Vec<MultiTableInsertValue>, +} + +/// A value in a multi-table INSERT VALUES clause. +#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)] +#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] +#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))] +pub enum MultiTableInsertValue { + /// A column reference or expression from the source + Expr(Expr), + /// The DEFAULT keyword + Default, +} + +impl Display for MultiTableInsertValue { + fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result { + match self { + MultiTableInsertValue::Expr(expr) => write!(f, "{}", expr), + MultiTableInsertValue::Default => write!(f, "DEFAULT"), + } + } +} + +/// The type of multi-table INSERT statement(Snowflake). +/// +/// See: <https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table> +#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)] +#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] +#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))] +pub enum MultiTableInsertType { + /// `INSERT ALL` - all matching WHEN clauses are executed + All, + /// `INSERT FIRST` - only the first matching WHEN clause is executed + First, +} + +impl Display for MultiTableInsertType { + fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result { + match self { + MultiTableInsertType::All => write!(f, "ALL"), + MultiTableInsertType::First => write!(f, "FIRST"), + } + } +} diff --git a/src/ast/mod.rs b/src/ast/mod.rs index cc34ec7a..65568b77 100644 --- a/src/ast/mod.rs +++ b/src/ast/mod.rs @@ -83,7 +83,9 @@ pub use self::ddl::{ }; pub use self::dml::{ Delete, Insert, Merge, MergeAction, MergeClause, MergeClauseKind, MergeInsertExpr, - MergeInsertKind, MergeUpdateExpr, OutputClause, Update, + MergeInsertKind, MergeUpdateExpr, MultiTableInsertIntoClause, MultiTableInsertType, + MultiTableInsertValue, MultiTableInsertValues, MultiTableInsertWhenClause, OutputClause, + Update, }; pub use self::operator::{BinaryOperator, UnaryOperator}; pub use self::query::{ diff --git a/src/ast/spans.rs b/src/ast/spans.rs index bdd430e7..8354cea6 100644 --- a/src/ast/spans.rs +++ b/src/ast/spans.rs @@ -1312,8 +1312,12 @@ impl Spanned for Insert { priority: _, // todo, mysql specific insert_alias: _, // todo, mysql specific assignments, - settings: _, // todo, clickhouse specific - format_clause: _, // todo, clickhouse specific + settings: _, // todo, clickhouse specific + format_clause: _, // todo, clickhouse specific + multi_table_insert_type: _, // snowflake multi-table insert + multi_table_into_clauses: _, // snowflake multi-table insert + multi_table_when_clauses: _, // snowflake multi-table insert + multi_table_else_clause: _, // snowflake multi-table insert } = self; union_spans( diff --git a/src/dialect/snowflake.rs b/src/dialect/snowflake.rs index 3b6fa1c2..14e4ad45 100644 --- a/src/dialect/snowflake.rs +++ b/src/dialect/snowflake.rs @@ -31,13 +31,16 @@ use crate::ast::{ ColumnPolicy, ColumnPolicyProperty, ContactEntry, CopyIntoSnowflakeKind, CreateTable, CreateTableLikeKind, DollarQuotedString, Ident, IdentityParameters, IdentityProperty, IdentityPropertyFormatKind, IdentityPropertyKind, IdentityPropertyOrder, InitializeKind, - ObjectName, ObjectNamePart, RefreshModeKind, RowAccessPolicy, ShowObjects, SqlOption, - Statement, StorageSerializationPolicy, TagsColumnOption, Value, WrappedCollection, + Insert, MultiTableInsertIntoClause, MultiTableInsertType, MultiTableInsertValue, + MultiTableInsertValues, MultiTableInsertWhenClause, ObjectName, ObjectNamePart, + RefreshModeKind, RowAccessPolicy, ShowObjects, SqlOption, Statement, + StorageSerializationPolicy, TableObject, TagsColumnOption, Value, WrappedCollection, }; use crate::dialect::{Dialect, Precedence}; use crate::keywords::Keyword; use crate::parser::{IsOptional, Parser, ParserError}; use crate::tokenizer::Token; +use crate::tokenizer::TokenWithSpan; #[cfg(not(feature = "std"))] use alloc::boxed::Box; #[cfg(not(feature = "std"))] @@ -353,6 +356,33 @@ impl Dialect for SnowflakeDialect { parser.prev_token(); } + // Check for multi-table INSERT + // `INSERT [OVERWRITE] ALL ... or INSERT [OVERWRITE] FIRST ...` + if parser.parse_keyword(Keyword::INSERT) { + let insert_token = parser.get_current_token().clone(); + let overwrite = parser.parse_keyword(Keyword::OVERWRITE); + + // Check for ALL or FIRST keyword + if let Some(kw) = parser.parse_one_of_keywords(&[Keyword::ALL, Keyword::FIRST]) { + let multi_table_insert_type = match kw { + Keyword::FIRST => MultiTableInsertType::First, + _ => MultiTableInsertType::All, + }; + return Some(parse_multi_table_insert( + parser, + insert_token, + overwrite, + multi_table_insert_type, + )); + } + + // Not a multi-table insert, rewind + if overwrite { + parser.prev_token(); // rewind OVERWRITE + } + parser.prev_token(); // rewind INSERT + } + None } @@ -1678,3 +1708,170 @@ fn parse_show_objects(terse: bool, parser: &mut Parser) -> Result<Statement, Par show_options, })) } + +/// Parse multi-table INSERT statement. +/// +/// Syntax: +/// ```sql +/// -- Unconditional multi-table insert +/// INSERT [ OVERWRITE ] ALL +/// intoClause [ ... ] +/// <subquery> +/// +/// -- Conditional multi-table insert +/// INSERT [ OVERWRITE ] { FIRST | ALL } +/// { WHEN <condition> THEN intoClause [ ... ] } +/// [ ... ] +/// [ ELSE intoClause ] +/// <subquery> +/// ``` +/// +/// See: <https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table> +fn parse_multi_table_insert( + parser: &mut Parser, + insert_token: TokenWithSpan, + overwrite: bool, + multi_table_insert_type: MultiTableInsertType, +) -> Result<Statement, ParserError> { + // Check if this is conditional (has WHEN clauses) or unconditional (direct INTO clauses) + let is_conditional = parser.peek_keyword(Keyword::WHEN); + + let (multi_table_into_clauses, multi_table_when_clauses, multi_table_else_clause) = + if is_conditional { + // Conditional multi-table insert: WHEN clauses + let (when_clauses, else_clause) = parse_multi_table_insert_when_clauses(parser)?; + (vec![], when_clauses, else_clause) + } else { + // Unconditional multi-table insert: direct INTO clauses + let into_clauses = parse_multi_table_insert_into_clauses(parser)?; + (into_clauses, vec![], None) + }; + + // Parse the source query + let source = parser.parse_query()?; + + Ok(Statement::Insert(Insert { + insert_token: insert_token.into(), + optimizer_hint: None, + or: None, + ignore: false, + into: false, + table: TableObject::TableName(ObjectName(vec![])), // Not used for multi-table insert + table_alias: None, + columns: vec![], + overwrite, + source: Some(source), + assignments: vec![], + partitioned: None, + after_columns: vec![], + has_table_keyword: false, + on: None, + returning: None, + replace_into: false, + priority: None, + insert_alias: None, + settings: None, + format_clause: None, + multi_table_insert_type: Some(multi_table_insert_type), + multi_table_into_clauses, + multi_table_when_clauses, + multi_table_else_clause, + })) +} + +/// Parse one or more INTO clauses for multi-table INSERT. +fn parse_multi_table_insert_into_clauses( + parser: &mut Parser, +) -> Result<Vec<MultiTableInsertIntoClause>, ParserError> { + let mut into_clauses = vec![]; + while parser.parse_keyword(Keyword::INTO) { + into_clauses.push(parse_multi_table_insert_into_clause(parser)?); + } + if into_clauses.is_empty() { + return parser.expected("INTO clause in multi-table INSERT", parser.peek_token()); + } + Ok(into_clauses) +} + +/// Parse a single INTO clause for multi-table INSERT. +/// +/// Syntax: `INTO <table> [ ( <columns> ) ] [ VALUES ( <values> ) ]` +fn parse_multi_table_insert_into_clause( + parser: &mut Parser, +) -> Result<MultiTableInsertIntoClause, ParserError> { + let table_name = parser.parse_object_name(false)?; + + // Parse optional column list: ( <column_name> [, ...] ) + let columns = parser + .maybe_parse(|p| p.parse_parenthesized_column_list(IsOptional::Mandatory, false))? + .unwrap_or_default(); + + // Parse optional VALUES clause + let values = if parser.parse_keyword(Keyword::VALUES) { + parser.expect_token(&Token::LParen)?; + let values = parser.parse_comma_separated(parse_multi_table_insert_value)?; + parser.expect_token(&Token::RParen)?; + Some(MultiTableInsertValues { values }) + } else { + None + }; + + Ok(MultiTableInsertIntoClause { + table_name, + columns, + values, + }) +} + +/// Parse a single value in a multi-table INSERT VALUES clause. +fn parse_multi_table_insert_value( + parser: &mut Parser, +) -> Result<MultiTableInsertValue, ParserError> { + if parser.parse_keyword(Keyword::DEFAULT) { + Ok(MultiTableInsertValue::Default) + } else { + Ok(MultiTableInsertValue::Expr(parser.parse_expr()?)) + } +} + +/// Parse WHEN clauses for conditional multi-table INSERT. +fn parse_multi_table_insert_when_clauses( + parser: &mut Parser, +) -> Result< + ( + Vec<MultiTableInsertWhenClause>, + Option<Vec<MultiTableInsertIntoClause>>, + ), + ParserError, +> { + let mut when_clauses = vec![]; + let mut else_clause = None; + + // Parse WHEN clauses + while parser.parse_keyword(Keyword::WHEN) { + let condition = parser.parse_expr()?; + parser.expect_keyword(Keyword::THEN)?; + + // Parse INTO clauses for this WHEN + let into_clauses = parse_multi_table_insert_into_clauses(parser)?; + + when_clauses.push(MultiTableInsertWhenClause { + condition, + into_clauses, + }); + } + + // Parse optional ELSE clause + if parser.parse_keyword(Keyword::ELSE) { + else_clause = Some(parse_multi_table_insert_into_clauses(parser)?); + } + + if when_clauses.is_empty() { + return parser.expected( + "at least one WHEN clause in conditional multi-table INSERT", + parser.peek_token(), + ); + } + + Ok((when_clauses, else_clause)) +} diff --git a/src/parser/mod.rs b/src/parser/mod.rs index 8f3ae38f..2d702978 100644 --- a/src/parser/mod.rs +++ b/src/parser/mod.rs @@ -17171,6 +17171,10 @@ impl<'a> Parser<'a> { insert_alias, settings, format_clause, + multi_table_insert_type: None, + multi_table_into_clauses: vec![], + multi_table_when_clauses: vec![], + multi_table_else_clause: None, } .into()) } diff --git a/tests/sqlparser_postgres.rs b/tests/sqlparser_postgres.rs index cfb03737..f8c73813 100644 --- a/tests/sqlparser_postgres.rs +++ b/tests/sqlparser_postgres.rs @@ -5454,6 +5454,10 @@ fn test_simple_postgres_insert_with_alias() { insert_alias: None, settings: None, format_clause: None, + multi_table_insert_type: None, + multi_table_into_clauses: vec![], + multi_table_when_clauses: vec![], + multi_table_else_clause: None, }) ) } @@ -5529,6 +5533,10 @@ fn test_simple_postgres_insert_with_alias() { insert_alias: None, settings: None, format_clause: None, + multi_table_insert_type: None, + multi_table_into_clauses: vec![], + multi_table_when_clauses: vec![], + multi_table_else_clause: None, }) ) } @@ -5602,6 +5610,10 @@ fn test_simple_insert_with_quoted_alias() { insert_alias: None, settings: None, format_clause: None, + multi_table_insert_type: None, + multi_table_into_clauses: vec![], + multi_table_when_clauses: vec![], + multi_table_else_clause: None, }) ) } diff --git a/tests/sqlparser_snowflake.rs b/tests/sqlparser_snowflake.rs index 014a241f..222a9e53 100644 --- a/tests/sqlparser_snowflake.rs +++ b/tests/sqlparser_snowflake.rs @@ -3421,6 +3421,327 @@ fn test_subquery_sample() { .verified_stmt("SELECT * FROM (SELECT * FROM mytable) SAMPLE (10) SEED (42)"); } +#[test] +fn test_multi_table_insert_unconditional() { + // Basic unconditional multi-table insert + // See: https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table + snowflake().verified_stmt("INSERT ALL INTO t1 SELECT n1, n2, n3 FROM src"); + + // Multiple INTO clauses + snowflake().verified_stmt("INSERT ALL INTO t1 INTO t2 SELECT n1, n2, n3 FROM src"); + + // With column list + snowflake().verified_stmt("INSERT ALL INTO t1 (c1, c2, c3) SELECT n1, n2, n3 FROM src"); + + // With VALUES clause + snowflake().verified_stmt( + "INSERT ALL INTO t1 (c1, c2, c3) VALUES (n2, n1, DEFAULT) SELECT n1, n2, n3 FROM src", + ); + + // Complex example from Snowflake docs + snowflake().verified_stmt( + "INSERT ALL INTO t1 INTO t1 (c1, c2, c3) VALUES (n2, n1, DEFAULT) INTO t2 (c1, c2, c3) INTO t2 VALUES (n3, n2, n1) SELECT n1, n2, n3 FROM src" + ); + + // With OVERWRITE + snowflake().verified_stmt("INSERT OVERWRITE ALL INTO t1 INTO t2 SELECT n1, n2, n3 FROM src"); +} + +#[test] +fn test_multi_table_insert_conditional() { + // Basic conditional multi-table insert with WHEN clause + // See: https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table + snowflake().verified_stmt("INSERT ALL WHEN n1 > 100 THEN INTO t1 SELECT n1 FROM src"); + + // Multiple WHEN clauses + snowflake().verified_stmt( + "INSERT ALL WHEN n1 > 100 THEN INTO t1 WHEN n1 > 10 THEN INTO t2 SELECT n1 FROM src", + ); + + // WHEN with multiple INTO clauses + snowflake().verified_stmt("INSERT ALL WHEN n1 > 10 THEN INTO t1 INTO t2 SELECT n1 FROM src"); + + // With ELSE clause + snowflake() + .verified_stmt("INSERT ALL WHEN n1 > 100 THEN INTO t1 ELSE INTO t2 SELECT n1 FROM src"); + + // Complex conditional insert from Snowflake docs + snowflake().verified_stmt( + "INSERT ALL WHEN n1 > 100 THEN INTO t1 WHEN n1 > 10 THEN INTO t1 INTO t2 ELSE INTO t2 SELECT n1 FROM src" + ); + + // INSERT FIRST - only first matching WHEN clause executes + snowflake().verified_stmt( + "INSERT FIRST WHEN n1 > 100 THEN INTO t1 WHEN n1 > 10 THEN INTO t1 INTO t2 ELSE INTO t2 SELECT n1 FROM src" + ); + + // With OVERWRITE + snowflake().verified_stmt( + "INSERT OVERWRITE ALL WHEN n1 > 100 THEN INTO t1 ELSE INTO t2 SELECT n1 FROM src", + ); + + // WHEN with always-true condition + snowflake().verified_stmt("INSERT ALL WHEN 1 = 1 THEN INTO t1 SELECT n1 FROM src"); +} + +#[test] +fn test_multi_table_insert_with_values() { + // INTO clause with VALUES using column references + snowflake().verified_stmt("INSERT ALL INTO t1 VALUES (n1, n2) SELECT n1, n2 FROM src"); + + // INTO clause with VALUES using DEFAULT + snowflake().verified_stmt( + "INSERT ALL INTO t1 (c1, c2, c3) VALUES (n1, n2, DEFAULT) SELECT n1, n2 FROM src", + ); + + // INTO clause with VALUES using NULL + snowflake().verified_stmt( + "INSERT ALL INTO t1 (c1, c2, c3) VALUES (n1, NULL, n2) SELECT n1, n2 FROM src", + ); + + // Positional alias in VALUES + snowflake().verified_stmt("INSERT ALL INTO t1 VALUES ($1, $2) SELECT 1, 50 AS an_alias"); +} + +/// Unit tests for multi-table INSERT AST structure validation +#[test] +fn test_multi_table_insert_ast_unconditional() { + // Test basic unconditional multi-table insert AST + let sql = "INSERT ALL INTO t1 INTO t2 (c1, c2) SELECT n1, n2 FROM src"; + let stmt = snowflake().verified_stmt(sql); + + match stmt { + Statement::Insert(Insert { + multi_table_insert_type, + overwrite, + multi_table_into_clauses, + multi_table_when_clauses, + multi_table_else_clause, + source, + .. + }) => { + // Should be INSERT ALL (not FIRST) + assert_eq!(multi_table_insert_type, Some(MultiTableInsertType::All)); + assert!(!overwrite); + + // Should have 2 INTO clauses + assert_eq!(multi_table_into_clauses.len(), 2); + + // First INTO clause: INTO t1 + assert_eq!(multi_table_into_clauses[0].table_name.to_string(), "t1"); + assert!(multi_table_into_clauses[0].columns.is_empty()); + assert!(multi_table_into_clauses[0].values.is_none()); + + // Second INTO clause: INTO t2 (c1, c2) + assert_eq!(multi_table_into_clauses[1].table_name.to_string(), "t2"); + assert_eq!(multi_table_into_clauses[1].columns.len(), 2); + assert_eq!(multi_table_into_clauses[1].columns[0].to_string(), "c1"); + assert_eq!(multi_table_into_clauses[1].columns[1].to_string(), "c2"); + assert!(multi_table_into_clauses[1].values.is_none()); + + // No WHEN clauses for unconditional insert + assert!(multi_table_when_clauses.is_empty()); + assert!(multi_table_else_clause.is_none()); + + // Should have source query + assert!(source.is_some()); + } + _ => panic!("Expected INSERT statement"), + } +} + +#[test] +fn test_multi_table_insert_ast_with_values() { + // Test INTO clause with VALUES + let sql = "INSERT ALL INTO t1 (c1, c2, c3) VALUES (n1, n2, DEFAULT) SELECT n1, n2 FROM src"; + let stmt = snowflake().verified_stmt(sql); + + match stmt { + Statement::Insert(Insert { + multi_table_into_clauses, + .. + }) => { + assert_eq!(multi_table_into_clauses.len(), 1); + + let into_clause = &multi_table_into_clauses[0]; + assert_eq!(into_clause.table_name.to_string(), "t1"); + assert_eq!(into_clause.columns.len(), 3); + + // Check VALUES clause + let values = into_clause.values.as_ref().expect("Expected VALUES clause"); + assert_eq!(values.values.len(), 3); + + // First value: n1 (expression) + match &values.values[0] { + MultiTableInsertValue::Expr(expr) => { + assert_eq!(expr.to_string(), "n1"); + } + _ => panic!("Expected Expr"), + } + + // Second value: n2 (expression) + match &values.values[1] { + MultiTableInsertValue::Expr(expr) => { + assert_eq!(expr.to_string(), "n2"); + } + _ => panic!("Expected Expr"), + } + + // Third value: DEFAULT + match &values.values[2] { + MultiTableInsertValue::Default => {} + _ => panic!("Expected DEFAULT"), + } + } + _ => panic!("Expected INSERT statement"), + } +} + +#[test] +fn test_multi_table_insert_ast_conditional() { + // Test conditional multi-table insert with WHEN clauses + let sql = "INSERT ALL WHEN n1 > 100 THEN INTO t1 WHEN n1 > 10 THEN INTO t2 INTO t3 ELSE INTO t4 SELECT n1 FROM src"; + let stmt = snowflake().verified_stmt(sql); + + match stmt { + Statement::Insert(Insert { + multi_table_insert_type, + multi_table_into_clauses, + multi_table_when_clauses, + multi_table_else_clause, + .. + }) => { + // Should be INSERT ALL (not FIRST) + assert_eq!(multi_table_insert_type, Some(MultiTableInsertType::All)); + + // Unconditional INTO clauses should be empty for conditional insert + assert!(multi_table_into_clauses.is_empty()); + + // Should have 2 WHEN clauses + assert_eq!(multi_table_when_clauses.len(), 2); + + // First WHEN clause: WHEN n1 > 100 THEN INTO t1 + assert_eq!( + multi_table_when_clauses[0].condition.to_string(), + "n1 > 100" + ); + assert_eq!(multi_table_when_clauses[0].into_clauses.len(), 1); + assert_eq!( + multi_table_when_clauses[0].into_clauses[0] + .table_name + .to_string(), + "t1" + ); + + // Second WHEN clause: WHEN n1 > 10 THEN INTO t2 INTO t3 + assert_eq!(multi_table_when_clauses[1].condition.to_string(), "n1 > 10"); + assert_eq!(multi_table_when_clauses[1].into_clauses.len(), 2); + assert_eq!( + multi_table_when_clauses[1].into_clauses[0] + .table_name + .to_string(), + "t2" + ); + assert_eq!( + multi_table_when_clauses[1].into_clauses[1] + .table_name + .to_string(), + "t3" + ); + + // ELSE clause: ELSE INTO t4 + let else_clause = multi_table_else_clause.expect("Expected ELSE clause"); + assert_eq!(else_clause.len(), 1); + assert_eq!(else_clause[0].table_name.to_string(), "t4"); + } + _ => panic!("Expected INSERT statement"), + } +} + +#[test] +fn test_multi_table_insert_ast_first() { + // Test INSERT FIRST vs INSERT ALL + let sql = + "INSERT FIRST WHEN n1 > 100 THEN INTO t1 WHEN n1 > 10 THEN INTO t2 SELECT n1 FROM src"; + let stmt = snowflake().verified_stmt(sql); + + match stmt { + Statement::Insert(Insert { + multi_table_insert_type, + multi_table_when_clauses, + .. + }) => { + // Should be INSERT FIRST + assert_eq!(multi_table_insert_type, Some(MultiTableInsertType::First)); + assert_eq!(multi_table_when_clauses.len(), 2); + } + _ => panic!("Expected INSERT statement"), + } +} + +#[test] +fn test_multi_table_insert_ast_overwrite() { + // Test INSERT OVERWRITE ALL + let sql = "INSERT OVERWRITE ALL INTO t1 INTO t2 SELECT n1 FROM src"; + let stmt = snowflake().verified_stmt(sql); + + match stmt { + Statement::Insert(Insert { + overwrite, + multi_table_insert_type, + multi_table_into_clauses, + .. + }) => { + assert!(overwrite); + assert_eq!(multi_table_insert_type, Some(MultiTableInsertType::All)); + assert_eq!(multi_table_into_clauses.len(), 2); + } + _ => panic!("Expected INSERT statement"), + } +} + +#[test] +fn test_multi_table_insert_ast_complex_values() { + // Test complex VALUES with expressions + let sql = "INSERT ALL INTO t1 VALUES (n1 + n2, n3 * 2, DEFAULT) SELECT n1, n2, n3 FROM src"; + let stmt = snowflake().verified_stmt(sql); + + match stmt { + Statement::Insert(Insert { + multi_table_into_clauses, + .. + }) => { + assert_eq!(multi_table_into_clauses.len(), 1); + + let values = multi_table_into_clauses[0] + .values + .as_ref() + .expect("Expected VALUES"); + assert_eq!(values.values.len(), 3); + + // First value: n1 + n2 (binary expression) + match &values.values[0] { + MultiTableInsertValue::Expr(Expr::BinaryOp { op, .. }) => { + assert_eq!(*op, BinaryOperator::Plus); + } + _ => panic!("Expected BinaryOp expression"), + } + + // Second value: n3 * 2 (binary expression) + match &values.values[1] { + MultiTableInsertValue::Expr(Expr::BinaryOp { op, .. }) => { + assert_eq!(*op, BinaryOperator::Multiply); + } + _ => panic!("Expected BinaryOp expression"), + } + + // Third value: DEFAULT + assert!(matches!(&values.values[2], MultiTableInsertValue::Default)); + } + _ => panic!("Expected INSERT statement"), + } +} + #[test] fn parse_ls_and_rm() { snowflake().one_statement_parses_to("LS @~", "LIST @~"); --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
