This is an automated email from the ASF dual-hosted git repository.
github-bot pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion-sqlparser-rs.git
The following commit(s) were added to refs/heads/main by this push:
new 75f6f4b1 Snowflake: Add multi table insert support (#2148)
75f6f4b1 is described below
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]
