Oracle: Support for MERGE predicates (#2101)
Co-authored-by: Ifeanyi Ubah <ify1992@yahoo.com>
diff --git a/src/ast/dml.rs b/src/ast/dml.rs
index d6009ce..d740b14 100644
--- a/src/ast/dml.rs
+++ b/src/ast/dml.rs
@@ -24,13 +24,16 @@
#[cfg(feature = "visitor")]
use sqlparser_derive::{Visit, VisitMut};
-use crate::display_utils::{indented_list, Indent, SpaceOrNewline};
+use crate::{
+ ast::display_separated,
+ display_utils::{indented_list, Indent, SpaceOrNewline},
+};
use super::{
display_comma_separated, helpers::attached_token::AttachedToken, query::InputFormatClause,
Assignment, Expr, FromTable, Ident, InsertAliases, MysqlInsertPriority, ObjectName, OnInsert,
- OrderByExpr, Query, SelectItem, Setting, SqliteOnConflict, TableObject, TableWithJoins,
- UpdateTableFromKind,
+ OrderByExpr, Query, SelectInto, SelectItem, Setting, SqliteOnConflict, TableFactor,
+ TableObject, TableWithJoins, UpdateTableFromKind, Values,
};
/// INSERT statement.
@@ -310,3 +313,334 @@
Ok(())
}
}
+
+/// A `MERGE` statement.
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub struct Merge {
+ /// The `MERGE` token that starts the statement.
+ pub merge_token: AttachedToken,
+ /// optional INTO keyword
+ pub into: bool,
+ /// Specifies the table to merge
+ pub table: TableFactor,
+ /// Specifies the table or subquery to join with the target table
+ pub source: TableFactor,
+ /// Specifies the expression on which to join the target table and source
+ pub on: Box<Expr>,
+ /// Specifies the actions to perform when values match or do not match.
+ pub clauses: Vec<MergeClause>,
+ // Specifies the output to save changes in MSSQL
+ pub output: Option<OutputClause>,
+}
+
+impl Display for Merge {
+ fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
+ write!(
+ f,
+ "MERGE{int} {table} USING {source} ",
+ int = if self.into { " INTO" } else { "" },
+ table = self.table,
+ source = self.source,
+ )?;
+ write!(f, "ON {on} ", on = self.on)?;
+ write!(f, "{}", display_separated(&self.clauses, " "))?;
+ if let Some(ref output) = self.output {
+ write!(f, " {output}")?;
+ }
+ Ok(())
+ }
+}
+
+/// A `WHEN` clause within a `MERGE` Statement
+///
+/// Example:
+/// ```sql
+/// WHEN NOT MATCHED BY SOURCE AND product LIKE '%washer%' THEN DELETE
+/// ```
+/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
+/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub struct MergeClause {
+ /// The `WHEN` token that starts the sub-expression.
+ pub when_token: AttachedToken,
+ pub clause_kind: MergeClauseKind,
+ pub predicate: Option<Expr>,
+ pub action: MergeAction,
+}
+
+impl Display for MergeClause {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ let MergeClause {
+ when_token: _,
+ clause_kind,
+ predicate,
+ action,
+ } = self;
+
+ write!(f, "WHEN {clause_kind}")?;
+ if let Some(pred) = predicate {
+ write!(f, " AND {pred}")?;
+ }
+ write!(f, " THEN {action}")
+ }
+}
+
+/// Variant of `WHEN` clause used within a `MERGE` Statement.
+///
+/// Example:
+/// ```sql
+/// MERGE INTO T USING U ON FALSE WHEN MATCHED THEN DELETE
+/// ```
+/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
+/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub enum MergeClauseKind {
+ /// `WHEN MATCHED`
+ Matched,
+ /// `WHEN NOT MATCHED`
+ NotMatched,
+ /// `WHEN MATCHED BY TARGET`
+ ///
+ /// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
+ NotMatchedByTarget,
+ /// `WHEN MATCHED BY SOURCE`
+ ///
+ /// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
+ NotMatchedBySource,
+}
+
+impl Display for MergeClauseKind {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ MergeClauseKind::Matched => write!(f, "MATCHED"),
+ MergeClauseKind::NotMatched => write!(f, "NOT MATCHED"),
+ MergeClauseKind::NotMatchedByTarget => write!(f, "NOT MATCHED BY TARGET"),
+ MergeClauseKind::NotMatchedBySource => write!(f, "NOT MATCHED BY SOURCE"),
+ }
+ }
+}
+
+/// Underlying statement of a `WHEN` clause within a `MERGE` Statement
+///
+/// Example
+/// ```sql
+/// INSERT (product, quantity) VALUES(product, quantity)
+/// ```
+///
+/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
+/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
+/// [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/MERGE.html)
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub enum MergeAction {
+ /// An `INSERT` clause
+ ///
+ /// Example:
+ /// ```sql
+ /// INSERT (product, quantity) VALUES(product, quantity)
+ /// ```
+ Insert(MergeInsertExpr),
+ /// An `UPDATE` clause
+ ///
+ /// Example:
+ /// ```sql
+ /// UPDATE SET quantity = T.quantity + S.quantity
+ /// ```
+ Update(MergeUpdateExpr),
+ /// A plain `DELETE` clause
+ Delete {
+ /// The `DELETE` token that starts the sub-expression.
+ delete_token: AttachedToken,
+ },
+}
+
+impl Display for MergeAction {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ MergeAction::Insert(insert) => {
+ write!(f, "INSERT {insert}")
+ }
+ MergeAction::Update(update) => {
+ write!(f, "UPDATE {update}")
+ }
+ MergeAction::Delete { .. } => {
+ write!(f, "DELETE")
+ }
+ }
+ }
+}
+
+/// The type of expression used to insert rows within a `MERGE` statement.
+///
+/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
+/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub enum MergeInsertKind {
+ /// The insert expression is defined from an explicit `VALUES` clause
+ ///
+ /// Example:
+ /// ```sql
+ /// INSERT VALUES(product, quantity)
+ /// ```
+ Values(Values),
+ /// The insert expression is defined using only the `ROW` keyword.
+ ///
+ /// Example:
+ /// ```sql
+ /// INSERT ROW
+ /// ```
+ /// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
+ Row,
+}
+
+impl Display for MergeInsertKind {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ MergeInsertKind::Values(values) => {
+ write!(f, "{values}")
+ }
+ MergeInsertKind::Row => {
+ write!(f, "ROW")
+ }
+ }
+ }
+}
+
+/// The expression used to insert rows within a `MERGE` statement.
+///
+/// Examples
+/// ```sql
+/// INSERT (product, quantity) VALUES(product, quantity)
+/// INSERT ROW
+/// ```
+///
+/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
+/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
+/// [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/MERGE.html)
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub struct MergeInsertExpr {
+ /// The `INSERT` token that starts the sub-expression.
+ pub insert_token: AttachedToken,
+ /// Columns (if any) specified by the insert.
+ ///
+ /// Example:
+ /// ```sql
+ /// INSERT (product, quantity) VALUES(product, quantity)
+ /// INSERT (product, quantity) ROW
+ /// ```
+ pub columns: Vec<ObjectName>,
+ /// The token, `[VALUES | ROW]` starting `kind`.
+ pub kind_token: AttachedToken,
+ /// The insert type used by the statement.
+ pub kind: MergeInsertKind,
+ /// An optional condition to restrict the insertion (Oracle specific)
+ pub insert_predicate: Option<Expr>,
+}
+
+impl Display for MergeInsertExpr {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ if !self.columns.is_empty() {
+ write!(f, "({}) ", display_comma_separated(self.columns.as_slice()))?;
+ }
+ write!(f, "{}", self.kind)?;
+ if let Some(predicate) = self.insert_predicate.as_ref() {
+ write!(f, " WHERE {}", predicate)?;
+ }
+ Ok(())
+ }
+}
+
+/// The expression used to update rows within a `MERGE` statement.
+///
+/// Examples
+/// ```sql
+/// UPDATE SET quantity = T.quantity + S.quantity
+/// ```
+///
+/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
+/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
+/// [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/MERGE.html)
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub struct MergeUpdateExpr {
+ /// The `UPDATE` token that starts the sub-expression.
+ pub update_token: AttachedToken,
+ /// The update assiment expressions
+ pub assignments: Vec<Assignment>,
+ /// `where_clause` for the update (Oralce specific)
+ pub update_predicate: Option<Expr>,
+ /// `delete_clause` for the update "delete where" (Oracle specific)
+ pub delete_predicate: Option<Expr>,
+}
+
+impl Display for MergeUpdateExpr {
+ fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
+ write!(f, "SET {}", display_comma_separated(&self.assignments))?;
+ if let Some(predicate) = self.update_predicate.as_ref() {
+ write!(f, " WHERE {predicate}")?;
+ }
+ if let Some(predicate) = self.delete_predicate.as_ref() {
+ write!(f, " DELETE WHERE {predicate}")?;
+ }
+ Ok(())
+ }
+}
+
+/// A `OUTPUT` Clause in the end of a `MERGE` Statement
+///
+/// Example:
+/// OUTPUT $action, deleted.* INTO dbo.temp_products;
+/// [mssql](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql)
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub enum OutputClause {
+ Output {
+ output_token: AttachedToken,
+ select_items: Vec<SelectItem>,
+ into_table: Option<SelectInto>,
+ },
+ Returning {
+ returning_token: AttachedToken,
+ select_items: Vec<SelectItem>,
+ },
+}
+
+impl fmt::Display for OutputClause {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ OutputClause::Output {
+ output_token: _,
+ select_items,
+ into_table,
+ } => {
+ f.write_str("OUTPUT ")?;
+ display_comma_separated(select_items).fmt(f)?;
+ if let Some(into_table) = into_table {
+ f.write_str(" ")?;
+ into_table.fmt(f)?;
+ }
+ Ok(())
+ }
+ OutputClause::Returning {
+ returning_token: _,
+ select_items,
+ } => {
+ f.write_str("RETURNING ")?;
+ display_comma_separated(select_items).fmt(f)
+ }
+ }
+ }
+}
diff --git a/src/ast/mod.rs b/src/ast/mod.rs
index b9d947e..6cb4c33 100644
--- a/src/ast/mod.rs
+++ b/src/ast/mod.rs
@@ -77,7 +77,10 @@
UserDefinedTypeInternalLength, UserDefinedTypeRangeOption, UserDefinedTypeRepresentation,
UserDefinedTypeSqlDefinitionOption, UserDefinedTypeStorage, ViewColumnDef,
};
-pub use self::dml::{Delete, Insert, Update};
+pub use self::dml::{
+ Delete, Insert, Merge, MergeAction, MergeClause, MergeClauseKind, MergeInsertExpr,
+ MergeInsertKind, MergeUpdateExpr, OutputClause, Update,
+};
pub use self::operator::{BinaryOperator, UnaryOperator};
pub use self::query::{
AfterMatchSkip, ConnectBy, Cte, CteAsMaterialized, Distinct, EmptyMatchesMode,
@@ -344,6 +347,12 @@
}
}
+impl From<Ident> for ObjectName {
+ fn from(ident: Ident) -> Self {
+ ObjectName(vec![ObjectNamePart::Identifier(ident)])
+ }
+}
+
impl fmt::Display for ObjectName {
fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
write!(f, "{}", display_separated(&self.0, "."))
@@ -4087,22 +4096,7 @@
/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
/// [MSSQL](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16)
- Merge {
- /// The `MERGE` token that starts the statement.
- merge_token: AttachedToken,
- /// optional INTO keyword
- into: bool,
- /// Specifies the table to merge
- table: TableFactor,
- /// Specifies the table or subquery to join with the target table
- source: TableFactor,
- /// Specifies the expression on which to join the target table and source
- on: Box<Expr>,
- /// Specifies the actions to perform when values match or do not match.
- clauses: Vec<MergeClause>,
- // Specifies the output to save changes in MSSQL
- output: Option<OutputClause>,
- },
+ Merge(Merge),
/// ```sql
/// CACHE [ FLAG ] TABLE <table_name> [ OPTIONS('K1' = 'V1', 'K2' = V2) ] [ AS ] [ <query> ]
/// ```
@@ -5520,27 +5514,7 @@
Statement::ReleaseSavepoint { name } => {
write!(f, "RELEASE SAVEPOINT {name}")
}
- Statement::Merge {
- merge_token: _,
- into,
- table,
- source,
- on,
- clauses,
- output,
- } => {
- write!(
- f,
- "MERGE{int} {table} USING {source} ",
- int = if *into { " INTO" } else { "" }
- )?;
- write!(f, "ON {on} ")?;
- write!(f, "{}", display_separated(clauses, " "))?;
- if let Some(output) = output {
- write!(f, " {output}")?;
- }
- Ok(())
- }
+ Statement::Merge(merge) => merge.fmt(f),
Statement::Cache {
table_name,
table_flag,
@@ -8565,257 +8539,6 @@
}
}
-/// Variant of `WHEN` clause used within a `MERGE` Statement.
-///
-/// Example:
-/// ```sql
-/// MERGE INTO T USING U ON FALSE WHEN MATCHED THEN DELETE
-/// ```
-/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
-/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
-#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
-#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
-#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
-pub enum MergeClauseKind {
- /// `WHEN MATCHED`
- Matched,
- /// `WHEN NOT MATCHED`
- NotMatched,
- /// `WHEN MATCHED BY TARGET`
- ///
- /// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
- NotMatchedByTarget,
- /// `WHEN MATCHED BY SOURCE`
- ///
- /// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
- NotMatchedBySource,
-}
-
-impl Display for MergeClauseKind {
- fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
- match self {
- MergeClauseKind::Matched => write!(f, "MATCHED"),
- MergeClauseKind::NotMatched => write!(f, "NOT MATCHED"),
- MergeClauseKind::NotMatchedByTarget => write!(f, "NOT MATCHED BY TARGET"),
- MergeClauseKind::NotMatchedBySource => write!(f, "NOT MATCHED BY SOURCE"),
- }
- }
-}
-
-/// The type of expression used to insert rows within a `MERGE` statement.
-///
-/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
-/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
-#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
-#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
-#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
-pub enum MergeInsertKind {
- /// The insert expression is defined from an explicit `VALUES` clause
- ///
- /// Example:
- /// ```sql
- /// INSERT VALUES(product, quantity)
- /// ```
- Values(Values),
- /// The insert expression is defined using only the `ROW` keyword.
- ///
- /// Example:
- /// ```sql
- /// INSERT ROW
- /// ```
- /// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
- Row,
-}
-
-impl Display for MergeInsertKind {
- fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
- match self {
- MergeInsertKind::Values(values) => {
- write!(f, "{values}")
- }
- MergeInsertKind::Row => {
- write!(f, "ROW")
- }
- }
- }
-}
-
-/// The expression used to insert rows within a `MERGE` statement.
-///
-/// Examples
-/// ```sql
-/// INSERT (product, quantity) VALUES(product, quantity)
-/// INSERT ROW
-/// ```
-///
-/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
-/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
-#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
-#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
-#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
-pub struct MergeInsertExpr {
- /// The `INSERT` token that starts the sub-expression.
- pub insert_token: AttachedToken,
- /// Columns (if any) specified by the insert.
- ///
- /// Example:
- /// ```sql
- /// INSERT (product, quantity) VALUES(product, quantity)
- /// INSERT (product, quantity) ROW
- /// ```
- pub columns: Vec<Ident>,
- /// The token, `[VALUES | ROW]` starting `kind`.
- pub kind_token: AttachedToken,
- /// The insert type used by the statement.
- pub kind: MergeInsertKind,
-}
-
-impl Display for MergeInsertExpr {
- fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
- if !self.columns.is_empty() {
- write!(f, "({}) ", display_comma_separated(self.columns.as_slice()))?;
- }
- write!(f, "{}", self.kind)
- }
-}
-
-/// Underlying statement of a when clause within a `MERGE` Statement
-///
-/// Example
-/// ```sql
-/// INSERT (product, quantity) VALUES(product, quantity)
-/// ```
-///
-/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
-/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
-#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
-#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
-#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
-pub enum MergeAction {
- /// An `INSERT` clause
- ///
- /// Example:
- /// ```sql
- /// INSERT (product, quantity) VALUES(product, quantity)
- /// ```
- Insert(MergeInsertExpr),
- /// An `UPDATE` clause
- ///
- /// Example:
- /// ```sql
- /// UPDATE SET quantity = T.quantity + S.quantity
- /// ```
- Update {
- /// The `UPDATE` token that starts the sub-expression.
- update_token: AttachedToken,
- assignments: Vec<Assignment>,
- },
- /// A plain `DELETE` clause
- Delete {
- /// The `DELETE` token that starts the sub-expression.
- delete_token: AttachedToken,
- },
-}
-
-impl Display for MergeAction {
- fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
- match self {
- MergeAction::Insert(insert) => {
- write!(f, "INSERT {insert}")
- }
- MergeAction::Update { assignments, .. } => {
- write!(f, "UPDATE SET {}", display_comma_separated(assignments))
- }
- MergeAction::Delete { .. } => {
- write!(f, "DELETE")
- }
- }
- }
-}
-
-/// A when clause within a `MERGE` Statement
-///
-/// Example:
-/// ```sql
-/// WHEN NOT MATCHED BY SOURCE AND product LIKE '%washer%' THEN DELETE
-/// ```
-/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
-/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
-#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
-#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
-#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
-pub struct MergeClause {
- /// The `WHEN` token that starts the sub-expression.
- pub when_token: AttachedToken,
- pub clause_kind: MergeClauseKind,
- pub predicate: Option<Expr>,
- pub action: MergeAction,
-}
-
-impl Display for MergeClause {
- fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
- let MergeClause {
- when_token: _,
- clause_kind,
- predicate,
- action,
- } = self;
-
- write!(f, "WHEN {clause_kind}")?;
- if let Some(pred) = predicate {
- write!(f, " AND {pred}")?;
- }
- write!(f, " THEN {action}")
- }
-}
-
-/// A Output Clause in the end of a 'MERGE' Statement
-///
-/// Example:
-/// OUTPUT $action, deleted.* INTO dbo.temp_products;
-/// [mssql](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql)
-#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
-#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
-#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
-pub enum OutputClause {
- Output {
- output_token: AttachedToken,
- select_items: Vec<SelectItem>,
- into_table: Option<SelectInto>,
- },
- Returning {
- returning_token: AttachedToken,
- select_items: Vec<SelectItem>,
- },
-}
-
-impl fmt::Display for OutputClause {
- fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
- match self {
- OutputClause::Output {
- output_token: _,
- select_items,
- into_table,
- } => {
- f.write_str("OUTPUT ")?;
- display_comma_separated(select_items).fmt(f)?;
- if let Some(into_table) = into_table {
- f.write_str(" ")?;
- into_table.fmt(f)?;
- }
- Ok(())
- }
- OutputClause::Returning {
- returning_token: _,
- select_items,
- } => {
- f.write_str("RETURNING ")?;
- display_comma_separated(select_items).fmt(f)
- }
- }
- }
-}
-
#[derive(Debug, Copy, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
diff --git a/src/ast/spans.rs b/src/ast/spans.rs
index 20a0525..d63ed62 100644
--- a/src/ast/spans.rs
+++ b/src/ast/spans.rs
@@ -38,15 +38,15 @@
FunctionArgumentClause, FunctionArgumentList, FunctionArguments, GroupByExpr, HavingBound,
IfStatement, IlikeSelectItem, IndexColumn, Insert, Interpolate, InterpolateExpr, Join,
JoinConstraint, JoinOperator, JsonPath, JsonPathElem, LateralView, LimitClause,
- MatchRecognizePattern, Measure, MergeAction, MergeClause, MergeInsertExpr, MergeInsertKind,
- NamedParenthesizedList, NamedWindowDefinition, ObjectName, ObjectNamePart, Offset, OnConflict,
- OnConflictAction, OnInsert, OpenStatement, OrderBy, OrderByExpr, OrderByKind, OutputClause,
- Partition, PivotValueSource, ProjectionSelect, Query, RaiseStatement, RaiseStatementValue,
- ReferentialAction, RenameSelectItem, ReplaceSelectElement, ReplaceSelectItem, Select,
- SelectInto, SelectItem, SetExpr, SqlOption, Statement, Subscript, SymbolDefinition, TableAlias,
- TableAliasColumnDef, TableConstraint, TableFactor, TableObject, TableOptionsClustered,
- TableWithJoins, Update, UpdateTableFromKind, Use, Value, Values, ViewColumnDef, WhileStatement,
- WildcardAdditionalOptions, With, WithFill,
+ MatchRecognizePattern, Measure, Merge, MergeAction, MergeClause, MergeInsertExpr,
+ MergeInsertKind, MergeUpdateExpr, NamedParenthesizedList, NamedWindowDefinition, ObjectName,
+ ObjectNamePart, Offset, OnConflict, OnConflictAction, OnInsert, OpenStatement, OrderBy,
+ OrderByExpr, OrderByKind, OutputClause, Partition, PivotValueSource, ProjectionSelect, Query,
+ RaiseStatement, RaiseStatementValue, ReferentialAction, RenameSelectItem, ReplaceSelectElement,
+ ReplaceSelectItem, Select, SelectInto, SelectItem, SetExpr, SqlOption, Statement, Subscript,
+ SymbolDefinition, TableAlias, TableAliasColumnDef, TableConstraint, TableFactor, TableObject,
+ TableOptionsClustered, TableWithJoins, Update, UpdateTableFromKind, Use, Value, Values,
+ ViewColumnDef, WhileStatement, WildcardAdditionalOptions, With, WithFill,
};
/// Given an iterator of spans, return the [Span::union] of all spans.
@@ -453,20 +453,7 @@
Statement::Explain { .. } => Span::empty(),
Statement::Savepoint { .. } => Span::empty(),
Statement::ReleaseSavepoint { .. } => Span::empty(),
- Statement::Merge {
- merge_token,
- into: _,
- table: _,
- source: _,
- on,
- clauses,
- output,
- } => union_spans(
- [merge_token.0.span, on.span()]
- .into_iter()
- .chain(clauses.iter().map(Spanned::span))
- .chain(output.iter().map(Spanned::span)),
- ),
+ Statement::Merge(merge) => merge.span(),
Statement::Cache { .. } => Span::empty(),
Statement::UNCache { .. } => Span::empty(),
Statement::CreateSequence { .. } => Span::empty(),
@@ -927,6 +914,17 @@
}
}
+impl Spanned for Merge {
+ fn span(&self) -> Span {
+ union_spans(
+ [self.merge_token.0.span, self.on.span()]
+ .into_iter()
+ .chain(self.clauses.iter().map(Spanned::span))
+ .chain(self.output.iter().map(Spanned::span)),
+ )
+ }
+}
+
impl Spanned for FromTable {
fn span(&self) -> Span {
match self {
@@ -2421,12 +2419,7 @@
fn span(&self) -> Span {
match self {
MergeAction::Insert(expr) => expr.span(),
- MergeAction::Update {
- update_token,
- assignments,
- } => union_spans(
- core::iter::once(update_token.0.span).chain(assignments.iter().map(Spanned::span)),
- ),
+ MergeAction::Update(expr) => expr.span(),
MergeAction::Delete { delete_token } => delete_token.0.span,
}
}
@@ -2444,7 +2437,19 @@
},
]
.into_iter()
- .chain(self.columns.iter().map(|i| i.span)),
+ .chain(self.insert_predicate.iter().map(Spanned::span))
+ .chain(self.columns.iter().map(|i| i.span())),
+ )
+ }
+}
+
+impl Spanned for MergeUpdateExpr {
+ fn span(&self) -> Span {
+ union_spans(
+ core::iter::once(self.update_token.0.span)
+ .chain(self.assignments.iter().map(Spanned::span))
+ .chain(self.update_predicate.iter().map(Spanned::span))
+ .chain(self.delete_predicate.iter().map(Spanned::span)),
)
}
}
@@ -2768,7 +2773,7 @@
assert_eq!(stmt_span.end, (16, 67).into());
// ~ individual tokens within the statement
- let Statement::Merge {
+ let Statement::Merge(Merge {
merge_token,
into: _,
table: _,
@@ -2776,7 +2781,7 @@
on: _,
clauses,
output,
- } = &r[0]
+ }) = &r[0]
else {
panic!("not a MERGE statement");
};
@@ -2814,10 +2819,12 @@
clauses[1].when_token.0.span,
Span::new(Location::new(12, 17), Location::new(12, 21))
);
- if let MergeAction::Update {
+ if let MergeAction::Update(MergeUpdateExpr {
update_token,
assignments: _,
- } = &clauses[1].action
+ update_predicate: _,
+ delete_predicate: _,
+ }) = &clauses[1].action
{
assert_eq!(
update_token.0.span,
@@ -2890,7 +2897,7 @@
);
// ~ individual tokens within the statement
- if let Statement::Merge { output, .. } = &r[0] {
+ if let Statement::Merge(Merge { output, .. }) = &r[0] {
if let Some(OutputClause::Returning {
returning_token, ..
}) = output
@@ -2924,7 +2931,7 @@
);
// ~ individual tokens within the statement
- if let Statement::Merge { output, .. } = &r[0] {
+ if let Statement::Merge(Merge { output, .. }) = &r[0] {
if let Some(OutputClause::Output { output_token, .. }) = output {
assert_eq!(
output_token.0.span,
@@ -2937,4 +2944,44 @@
panic!("not a MERGE statement");
};
}
+
+ #[test]
+ fn test_merge_statement_spans_with_update_predicates() {
+ let sql = r#"
+ MERGE INTO a USING b ON a.id = b.id
+ WHEN MATCHED THEN
+ UPDATE set a.x = a.x + b.x
+ WHERE b.x != 2
+ DELETE WHERE a.x <> 3"#;
+
+ let r = Parser::parse_sql(&crate::dialect::GenericDialect, sql).unwrap();
+ assert_eq!(1, r.len());
+
+ // ~ assert the span of the whole statement
+ let stmt_span = r[0].span();
+ assert_eq!(
+ stmt_span,
+ Span::new(Location::new(2, 8), Location::new(6, 36))
+ );
+ }
+
+ #[test]
+ fn test_merge_statement_spans_with_insert_predicate() {
+ let sql = r#"
+ MERGE INTO a USING b ON a.id = b.id
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (b.x, b.y) WHERE b.x != 2
+-- qed
+"#;
+
+ let r = Parser::parse_sql(&crate::dialect::GenericDialect, sql).unwrap();
+ assert_eq!(1, r.len());
+
+ // ~ assert the span of the whole statement
+ let stmt_span = r[0].span();
+ assert_eq!(
+ stmt_span,
+ Span::new(Location::new(2, 8), Location::new(4, 52))
+ );
+ }
}
diff --git a/src/dialect/mod.rs b/src/dialect/mod.rs
index 83c6da4..1d99d86 100644
--- a/src/dialect/mod.rs
+++ b/src/dialect/mod.rs
@@ -603,7 +603,7 @@
false
}
- /// Return true if the dialect supports specifying multiple options
+ /// Returns true if the dialect supports specifying multiple options
/// in a `CREATE TABLE` statement for the structure of the new table. For example:
/// `CREATE TABLE t (a INT, b INT) AS SELECT 1 AS b, 2 AS a`
fn supports_create_table_multi_schema_info_sources(&self) -> bool {
diff --git a/src/parser/merge.rs b/src/parser/merge.rs
new file mode 100644
index 0000000..b2283b6
--- /dev/null
+++ b/src/parser/merge.rs
@@ -0,0 +1,242 @@
+// Licensed under the Apache License, Version 2.0 (the "License");
+// you may not use this file except in compliance with the License.
+// You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+
+//! SQL Parser for a `MERGE` statement
+
+#[cfg(not(feature = "std"))]
+use alloc::{boxed::Box, format, string::ToString, vec, vec::Vec};
+
+use crate::{
+ ast::{
+ Merge, MergeAction, MergeClause, MergeClauseKind, MergeInsertExpr, MergeInsertKind,
+ MergeUpdateExpr, ObjectName, OutputClause, SetExpr, Statement,
+ },
+ dialect::{BigQueryDialect, GenericDialect, MySqlDialect},
+ keywords::Keyword,
+ parser::IsOptional,
+ tokenizer::TokenWithSpan,
+};
+
+use super::{Parser, ParserError};
+
+impl Parser<'_> {
+ /// Parse a `MERGE` statement, returning a `Box`ed SetExpr
+ ///
+ /// This is used to reduce the size of the stack frames in debug builds
+ pub(super) fn parse_merge_setexpr_boxed(
+ &mut self,
+ merge_token: TokenWithSpan,
+ ) -> Result<Box<SetExpr>, ParserError> {
+ Ok(Box::new(SetExpr::Merge(self.parse_merge(merge_token)?)))
+ }
+
+ pub fn parse_merge(&mut self, merge_token: TokenWithSpan) -> Result<Statement, ParserError> {
+ let into = self.parse_keyword(Keyword::INTO);
+
+ let table = self.parse_table_factor()?;
+
+ self.expect_keyword_is(Keyword::USING)?;
+ let source = self.parse_table_factor()?;
+ self.expect_keyword_is(Keyword::ON)?;
+ let on = self.parse_expr()?;
+ let clauses = self.parse_merge_clauses()?;
+ let output = match self.parse_one_of_keywords(&[Keyword::OUTPUT, Keyword::RETURNING]) {
+ Some(keyword) => Some(self.parse_output(keyword, self.get_current_token().clone())?),
+ None => None,
+ };
+
+ Ok(Statement::Merge(Merge {
+ merge_token: merge_token.into(),
+ into,
+ table,
+ source,
+ on: Box::new(on),
+ clauses,
+ output,
+ }))
+ }
+
+ fn parse_merge_clauses(&mut self) -> Result<Vec<MergeClause>, ParserError> {
+ let mut clauses = vec![];
+ loop {
+ if !(self.parse_keyword(Keyword::WHEN)) {
+ break;
+ }
+ let when_token = self.get_current_token().clone();
+
+ let mut clause_kind = MergeClauseKind::Matched;
+ if self.parse_keyword(Keyword::NOT) {
+ clause_kind = MergeClauseKind::NotMatched;
+ }
+ self.expect_keyword_is(Keyword::MATCHED)?;
+
+ if matches!(clause_kind, MergeClauseKind::NotMatched)
+ && self.parse_keywords(&[Keyword::BY, Keyword::SOURCE])
+ {
+ clause_kind = MergeClauseKind::NotMatchedBySource;
+ } else if matches!(clause_kind, MergeClauseKind::NotMatched)
+ && self.parse_keywords(&[Keyword::BY, Keyword::TARGET])
+ {
+ clause_kind = MergeClauseKind::NotMatchedByTarget;
+ }
+
+ let predicate = if self.parse_keyword(Keyword::AND) {
+ Some(self.parse_expr()?)
+ } else {
+ None
+ };
+
+ self.expect_keyword_is(Keyword::THEN)?;
+
+ let merge_clause = match self.parse_one_of_keywords(&[
+ Keyword::UPDATE,
+ Keyword::INSERT,
+ Keyword::DELETE,
+ ]) {
+ Some(Keyword::UPDATE) => {
+ if matches!(
+ clause_kind,
+ MergeClauseKind::NotMatched | MergeClauseKind::NotMatchedByTarget
+ ) {
+ return parser_err!(
+ format_args!("UPDATE is not allowed in a {clause_kind} merge clause"),
+ self.get_current_token().span.start
+ );
+ }
+
+ let update_token = self.get_current_token().clone();
+ self.expect_keyword_is(Keyword::SET)?;
+ let assignments = self.parse_comma_separated(Parser::parse_assignment)?;
+ let update_predicate = if self.parse_keyword(Keyword::WHERE) {
+ Some(self.parse_expr()?)
+ } else {
+ None
+ };
+ let delete_predicate = if self.parse_keyword(Keyword::DELETE) {
+ let _ = self.expect_keyword(Keyword::WHERE)?;
+ Some(self.parse_expr()?)
+ } else {
+ None
+ };
+ MergeAction::Update(MergeUpdateExpr {
+ update_token: update_token.into(),
+ assignments,
+ update_predicate,
+ delete_predicate,
+ })
+ }
+ Some(Keyword::DELETE) => {
+ if matches!(
+ clause_kind,
+ MergeClauseKind::NotMatched | MergeClauseKind::NotMatchedByTarget
+ ) {
+ return parser_err!(
+ format_args!("DELETE is not allowed in a {clause_kind} merge clause"),
+ self.get_current_token().span.start
+ );
+ };
+
+ let delete_token = self.get_current_token().clone();
+ MergeAction::Delete {
+ delete_token: delete_token.into(),
+ }
+ }
+ Some(Keyword::INSERT) => {
+ if !matches!(
+ clause_kind,
+ MergeClauseKind::NotMatched | MergeClauseKind::NotMatchedByTarget
+ ) {
+ return parser_err!(
+ format_args!("INSERT is not allowed in a {clause_kind} merge clause"),
+ self.get_current_token().span.start
+ );
+ };
+
+ let insert_token = self.get_current_token().clone();
+ let is_mysql = dialect_of!(self is MySqlDialect);
+
+ let columns = self.parse_merge_clause_insert_columns(is_mysql)?;
+ let (kind, kind_token) = if dialect_of!(self is BigQueryDialect | GenericDialect)
+ && self.parse_keyword(Keyword::ROW)
+ {
+ (MergeInsertKind::Row, self.get_current_token().clone())
+ } else {
+ self.expect_keyword_is(Keyword::VALUES)?;
+ let values_token = self.get_current_token().clone();
+ let values = self.parse_values(is_mysql, false)?;
+ (MergeInsertKind::Values(values), values_token)
+ };
+ let insert_predicate = if self.parse_keyword(Keyword::WHERE) {
+ Some(self.parse_expr()?)
+ } else {
+ None
+ };
+
+ MergeAction::Insert(MergeInsertExpr {
+ insert_token: insert_token.into(),
+ columns,
+ kind_token: kind_token.into(),
+ kind,
+ insert_predicate,
+ })
+ }
+ _ => {
+ return parser_err!(
+ "expected UPDATE, DELETE or INSERT in merge clause",
+ self.peek_token_ref().span.start
+ );
+ }
+ };
+ clauses.push(MergeClause {
+ when_token: when_token.into(),
+ clause_kind,
+ predicate,
+ action: merge_clause,
+ });
+ }
+ Ok(clauses)
+ }
+
+ fn parse_merge_clause_insert_columns(
+ &mut self,
+ allow_empty: bool,
+ ) -> Result<Vec<ObjectName>, ParserError> {
+ self.parse_parenthesized_qualified_column_list(IsOptional::Optional, allow_empty)
+ }
+
+ fn parse_output(
+ &mut self,
+ start_keyword: Keyword,
+ start_token: TokenWithSpan,
+ ) -> Result<OutputClause, ParserError> {
+ let select_items = self.parse_projection()?;
+ let into_table = if start_keyword == Keyword::OUTPUT && self.peek_keyword(Keyword::INTO) {
+ self.expect_keyword_is(Keyword::INTO)?;
+ Some(self.parse_select_into()?)
+ } else {
+ None
+ };
+
+ Ok(if start_keyword == Keyword::OUTPUT {
+ OutputClause::Output {
+ output_token: start_token.into(),
+ select_items,
+ into_table,
+ }
+ } else {
+ OutputClause::Returning {
+ returning_token: start_token.into(),
+ select_items,
+ }
+ })
+ }
+}
diff --git a/src/parser/mod.rs b/src/parser/mod.rs
index ce83ada..c499829 100644
--- a/src/parser/mod.rs
+++ b/src/parser/mod.rs
@@ -45,8 +45,6 @@
use crate::tokenizer::*;
use sqlparser::parser::ParserState::ColumnDefinition;
-mod alter;
-
#[derive(Debug, Clone, PartialEq, Eq)]
pub enum ParserError {
TokenizerError(String),
@@ -61,6 +59,9 @@
};
}
+mod alter;
+mod merge;
+
#[cfg(feature = "std")]
/// Implementation [`RecursionCounter`] if std is available
mod recursion {
@@ -11882,7 +11883,7 @@
token => {
return Err(ParserError::ParserError(format!(
"Unexpected token in identifier: {token}"
- )))?
+ )))?;
}
}
}
@@ -12386,16 +12387,6 @@
Ok(Box::new(SetExpr::Delete(self.parse_delete(delete_token)?)))
}
- /// Parse a MERGE statement, returning a `Box`ed SetExpr
- ///
- /// This is used to reduce the size of the stack frames in debug builds
- fn parse_merge_setexpr_boxed(
- &mut self,
- merge_token: TokenWithSpan,
- ) -> Result<Box<SetExpr>, ParserError> {
- Ok(Box::new(SetExpr::Merge(self.parse_merge(merge_token)?)))
- }
-
pub fn parse_delete(&mut self, delete_token: TokenWithSpan) -> Result<Statement, ParserError> {
let (tables, with_from_keyword) = if !self.parse_keyword(Keyword::FROM) {
// `FROM` keyword is optional in BigQuery SQL.
@@ -17476,153 +17467,6 @@
})
}
- pub fn parse_merge_clauses(&mut self) -> Result<Vec<MergeClause>, ParserError> {
- let mut clauses = vec![];
- loop {
- if !(self.parse_keyword(Keyword::WHEN)) {
- break;
- }
- let when_token = self.get_current_token().clone();
-
- let mut clause_kind = MergeClauseKind::Matched;
- if self.parse_keyword(Keyword::NOT) {
- clause_kind = MergeClauseKind::NotMatched;
- }
- self.expect_keyword_is(Keyword::MATCHED)?;
-
- if matches!(clause_kind, MergeClauseKind::NotMatched)
- && self.parse_keywords(&[Keyword::BY, Keyword::SOURCE])
- {
- clause_kind = MergeClauseKind::NotMatchedBySource;
- } else if matches!(clause_kind, MergeClauseKind::NotMatched)
- && self.parse_keywords(&[Keyword::BY, Keyword::TARGET])
- {
- clause_kind = MergeClauseKind::NotMatchedByTarget;
- }
-
- let predicate = if self.parse_keyword(Keyword::AND) {
- Some(self.parse_expr()?)
- } else {
- None
- };
-
- self.expect_keyword_is(Keyword::THEN)?;
-
- let merge_clause = match self.parse_one_of_keywords(&[
- Keyword::UPDATE,
- Keyword::INSERT,
- Keyword::DELETE,
- ]) {
- Some(Keyword::UPDATE) => {
- if matches!(
- clause_kind,
- MergeClauseKind::NotMatched | MergeClauseKind::NotMatchedByTarget
- ) {
- return parser_err!(
- format_args!("UPDATE is not allowed in a {clause_kind} merge clause"),
- self.get_current_token().span.start
- );
- }
-
- let update_token = self.get_current_token().clone();
- self.expect_keyword_is(Keyword::SET)?;
- MergeAction::Update {
- update_token: update_token.into(),
- assignments: self.parse_comma_separated(Parser::parse_assignment)?,
- }
- }
- Some(Keyword::DELETE) => {
- if matches!(
- clause_kind,
- MergeClauseKind::NotMatched | MergeClauseKind::NotMatchedByTarget
- ) {
- return parser_err!(
- format_args!("DELETE is not allowed in a {clause_kind} merge clause"),
- self.get_current_token().span.start
- );
- };
-
- let delete_token = self.get_current_token().clone();
- MergeAction::Delete {
- delete_token: delete_token.into(),
- }
- }
- Some(Keyword::INSERT) => {
- if !matches!(
- clause_kind,
- MergeClauseKind::NotMatched | MergeClauseKind::NotMatchedByTarget
- ) {
- return parser_err!(
- format_args!("INSERT is not allowed in a {clause_kind} merge clause"),
- self.get_current_token().span.start
- );
- };
-
- let insert_token = self.get_current_token().clone();
- let is_mysql = dialect_of!(self is MySqlDialect);
-
- let columns = self.parse_parenthesized_column_list(Optional, is_mysql)?;
- let (kind, kind_token) = if dialect_of!(self is BigQueryDialect | GenericDialect)
- && self.parse_keyword(Keyword::ROW)
- {
- (MergeInsertKind::Row, self.get_current_token().clone())
- } else {
- self.expect_keyword_is(Keyword::VALUES)?;
- let values_token = self.get_current_token().clone();
- let values = self.parse_values(is_mysql, false)?;
- (MergeInsertKind::Values(values), values_token)
- };
- MergeAction::Insert(MergeInsertExpr {
- insert_token: insert_token.into(),
- columns,
- kind_token: kind_token.into(),
- kind,
- })
- }
- _ => {
- return parser_err!(
- "expected UPDATE, DELETE or INSERT in merge clause",
- self.peek_token_ref().span.start
- );
- }
- };
- clauses.push(MergeClause {
- when_token: when_token.into(),
- clause_kind,
- predicate,
- action: merge_clause,
- });
- }
- Ok(clauses)
- }
-
- fn parse_output(
- &mut self,
- start_keyword: Keyword,
- start_token: TokenWithSpan,
- ) -> Result<OutputClause, ParserError> {
- let select_items = self.parse_projection()?;
- let into_table = if start_keyword == Keyword::OUTPUT && self.peek_keyword(Keyword::INTO) {
- self.expect_keyword_is(Keyword::INTO)?;
- Some(self.parse_select_into()?)
- } else {
- None
- };
-
- Ok(if start_keyword == Keyword::OUTPUT {
- OutputClause::Output {
- output_token: start_token.into(),
- select_items,
- into_table,
- }
- } else {
- OutputClause::Returning {
- returning_token: start_token.into(),
- select_items,
- }
- })
- }
-
fn parse_select_into(&mut self) -> Result<SelectInto, ParserError> {
let temporary = self
.parse_one_of_keywords(&[Keyword::TEMP, Keyword::TEMPORARY])
@@ -17639,32 +17483,6 @@
})
}
- pub fn parse_merge(&mut self, merge_token: TokenWithSpan) -> Result<Statement, ParserError> {
- let into = self.parse_keyword(Keyword::INTO);
-
- let table = self.parse_table_factor()?;
-
- self.expect_keyword_is(Keyword::USING)?;
- let source = self.parse_table_factor()?;
- self.expect_keyword_is(Keyword::ON)?;
- let on = self.parse_expr()?;
- let clauses = self.parse_merge_clauses()?;
- let output = match self.parse_one_of_keywords(&[Keyword::OUTPUT, Keyword::RETURNING]) {
- Some(keyword) => Some(self.parse_output(keyword, self.get_current_token().clone())?),
- None => None,
- };
-
- Ok(Statement::Merge {
- merge_token: merge_token.into(),
- into,
- table,
- source,
- on: Box::new(on),
- clauses,
- output,
- })
- }
-
fn parse_pragma_value(&mut self) -> Result<Value, ParserError> {
match self.parse_value()?.value {
v @ Value::SingleQuotedString(_) => Ok(v),
diff --git a/tests/sqlparser_bigquery.rs b/tests/sqlparser_bigquery.rs
index f2b9f2a..24b9efc 100644
--- a/tests/sqlparser_bigquery.rs
+++ b/tests/sqlparser_bigquery.rs
@@ -1806,15 +1806,16 @@
);
let insert_action = MergeAction::Insert(MergeInsertExpr {
insert_token: AttachedToken::empty(),
- columns: vec![Ident::new("product"), Ident::new("quantity")],
+ columns: vec![Ident::new("product").into(), Ident::new("quantity").into()],
kind_token: AttachedToken::empty(),
kind: MergeInsertKind::Values(Values {
value_keyword: false,
explicit_row: false,
rows: vec![vec![Expr::value(number("1")), Expr::value(number("2"))]],
}),
+ insert_predicate: None,
});
- let update_action = MergeAction::Update {
+ let update_action = MergeAction::Update(MergeUpdateExpr {
update_token: AttachedToken::empty(),
assignments: vec![
Assignment {
@@ -1826,17 +1827,19 @@
value: Expr::value(number("2")),
},
],
- };
+ update_predicate: None,
+ delete_predicate: None,
+ });
match bigquery_and_generic().verified_stmt(sql) {
- Statement::Merge {
+ Statement::Merge(Merge {
into,
table,
source,
on,
clauses,
..
- } => {
+ }) => {
assert!(!into);
assert_eq!(
TableFactor::Table {
@@ -1917,9 +1920,13 @@
predicate: Some(Expr::value(number("1"))),
action: MergeAction::Insert(MergeInsertExpr {
insert_token: AttachedToken::empty(),
- columns: vec![Ident::new("product"), Ident::new("quantity"),],
+ columns: vec![
+ Ident::new("product").into(),
+ Ident::new("quantity").into(),
+ ],
kind_token: AttachedToken::empty(),
kind: MergeInsertKind::Row,
+ insert_predicate: None,
})
},
MergeClause {
@@ -1928,9 +1935,13 @@
predicate: None,
action: MergeAction::Insert(MergeInsertExpr {
insert_token: AttachedToken::empty(),
- columns: vec![Ident::new("product"), Ident::new("quantity"),],
+ columns: vec![
+ Ident::new("product").into(),
+ Ident::new("quantity").into(),
+ ],
kind_token: AttachedToken::empty(),
kind: MergeInsertKind::Row,
+ insert_predicate: None,
})
},
MergeClause {
@@ -1941,7 +1952,8 @@
insert_token: AttachedToken::empty(),
columns: vec![],
kind_token: AttachedToken::empty(),
- kind: MergeInsertKind::Row
+ kind: MergeInsertKind::Row,
+ insert_predicate: None,
})
},
MergeClause {
@@ -1952,7 +1964,8 @@
insert_token: AttachedToken::empty(),
columns: vec![],
kind_token: AttachedToken::empty(),
- kind: MergeInsertKind::Row
+ kind: MergeInsertKind::Row,
+ insert_predicate: None,
})
},
MergeClause {
@@ -1975,7 +1988,7 @@
predicate: None,
action: MergeAction::Insert(MergeInsertExpr {
insert_token: AttachedToken::empty(),
- columns: vec![Ident::new("a"), Ident::new("b"),],
+ columns: vec![Ident::new("a").into(), Ident::new("b").into(),],
kind_token: AttachedToken::empty(),
kind: MergeInsertKind::Values(Values {
value_keyword: false,
@@ -1984,7 +1997,8 @@
Expr::value(number("1")),
Expr::Identifier(Ident::new("DEFAULT")),
]]
- })
+ }),
+ insert_predicate: None,
})
},
MergeClause {
@@ -2002,7 +2016,8 @@
Expr::value(number("1")),
Expr::Identifier(Ident::new("DEFAULT")),
]]
- })
+ }),
+ insert_predicate: None,
})
},
],
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs
index ccad67e..cfee262 100644
--- a/tests/sqlparser_common.rs
+++ b/tests/sqlparser_common.rs
@@ -9793,22 +9793,22 @@
let sql_no_into = "MERGE s.bar AS dest USING (SELECT * FROM s.foo) AS stg ON dest.D = stg.D AND dest.E = stg.E WHEN NOT MATCHED THEN INSERT (A, B, C) VALUES (stg.A, stg.B, stg.C) WHEN MATCHED AND dest.A = 'a' THEN UPDATE SET dest.F = stg.F, dest.G = stg.G WHEN MATCHED THEN DELETE";
match (verified_stmt(sql), verified_stmt(sql_no_into)) {
(
- Statement::Merge {
+ Statement::Merge(Merge {
into,
table,
source,
on,
clauses,
..
- },
- Statement::Merge {
+ }),
+ Statement::Merge(Merge {
into: no_into,
table: table_no_into,
source: source_no_into,
on: on_no_into,
clauses: clauses_no_into,
..
- },
+ }),
) => {
assert!(into);
assert!(!no_into);
@@ -9921,7 +9921,11 @@
predicate: None,
action: MergeAction::Insert(MergeInsertExpr {
insert_token: AttachedToken::empty(),
- columns: vec![Ident::new("A"), Ident::new("B"), Ident::new("C")],
+ columns: vec![
+ Ident::new("A").into(),
+ Ident::new("B").into(),
+ Ident::new("C").into()
+ ],
kind_token: AttachedToken::empty(),
kind: MergeInsertKind::Values(Values {
value_keyword: false,
@@ -9941,6 +9945,7 @@
]),
]]
}),
+ insert_predicate: None,
}),
},
MergeClause {
@@ -9956,7 +9961,7 @@
(Value::SingleQuotedString("a".to_string())).with_empty_span()
)),
}),
- action: MergeAction::Update {
+ action: MergeAction::Update(MergeUpdateExpr {
update_token: AttachedToken::empty(),
assignments: vec![
Assignment {
@@ -9980,7 +9985,9 @@
]),
},
],
- },
+ update_predicate: None,
+ delete_predicate: None,
+ }),
},
MergeClause {
when_token: AttachedToken::empty(),
@@ -9999,6 +10006,45 @@
let sql = "MERGE INTO s.bar AS dest USING newArrivals AS S ON (1 > 1) WHEN NOT MATCHED THEN INSERT VALUES (stg.A, stg.B, stg.C)";
verified_stmt(sql);
+
+ // MERGE with predicates
+ let sql = "\
+MERGE INTO FOO \
+USING FOO_IMPORT \
+ON (FOO.ID = FOO_IMPORT.ID) \
+WHEN MATCHED THEN \
+UPDATE SET FOO.NAME = FOO_IMPORT.NAME \
+WHERE 1 = 1 \
+DELETE WHERE FOO.NAME LIKE '%.DELETE' \
+WHEN NOT MATCHED THEN \
+INSERT (ID, NAME) \
+VALUES (FOO_IMPORT.ID, UPPER(FOO_IMPORT.NAME)) \
+WHERE NOT FOO_IMPORT.NAME LIKE '%.DO_NOT_INSERT'";
+ all_dialects().verified_stmt(sql);
+
+ // MERGE with simple insert columns
+ let sql = "\
+MERGE INTO FOO USING FOO_IMPORT ON (FOO.ID = FOO_IMPORT.ID) \
+WHEN NOT MATCHED THEN \
+INSERT (ID, NAME) \
+VALUES (1, 'abc')";
+ all_dialects().verified_stmt(sql);
+
+ // MERGE with qualified insert columns
+ let sql = "\
+MERGE INTO FOO USING FOO_IMPORT ON (FOO.ID = FOO_IMPORT.ID) \
+WHEN NOT MATCHED THEN \
+INSERT (FOO.ID, FOO.NAME) \
+VALUES (1, 'abc')";
+ all_dialects().verified_stmt(sql);
+
+ // MERGE with schema qualified insert columns
+ let sql = "\
+MERGE INTO PLAYGROUND.FOO USING FOO_IMPORT ON (PLAYGROUND.FOO.ID = FOO_IMPORT.ID) \
+WHEN NOT MATCHED THEN \
+INSERT (PLAYGROUND.FOO.ID, PLAYGROUND.FOO.NAME) \
+VALUES (1, 'abc')";
+ all_dialects().verified_stmt(sql);
}
#[test]
