This is an automated email from the ASF dual-hosted git repository. github-bot pushed a commit to branch gh-readonly-queue/main/pr-2228-49bdb5ca2be7eb9b74b8471f63344d8fa296b337 in repository https://gitbox.apache.org/repos/asf/datafusion-sqlparser-rs.git
commit 31e19429a779793c1ee0338e658d76de053cd203 Author: Guan-Ming (Wesley) Chiu <[email protected]> AuthorDate: Fri Feb 27 22:57:02 2026 +0800 MSSQL: Add support for OUTPUT clause on INSERT/UPDATE/DELETE (#2228) Signed-off-by: Guan-Ming Chiu <[email protected]> Signed-off-by: Guan-Ming (Wesley) Chiu <[email protected]> Co-authored-by: Ifeanyi Ubah <[email protected]> --- src/ast/dml.rs | 26 ++++++++++++++++++++++++-- src/ast/spans.rs | 8 +++++++- src/dialect/snowflake.rs | 1 + src/keywords.rs | 2 ++ src/parser/merge.rs | 16 +++++++++++++++- src/parser/mod.rs | 24 +++++++++++++++++++++--- tests/sqlparser_common.rs | 2 ++ tests/sqlparser_mssql.rs | 42 ++++++++++++++++++++++++++++++++++++++++++ tests/sqlparser_mysql.rs | 1 + tests/sqlparser_postgres.rs | 3 +++ tests/sqlparser_sqlite.rs | 1 + 11 files changed, 119 insertions(+), 7 deletions(-) diff --git a/src/ast/dml.rs b/src/ast/dml.rs index 06f731c5..e2c48885 100644 --- a/src/ast/dml.rs +++ b/src/ast/dml.rs @@ -79,6 +79,9 @@ pub struct Insert { pub on: Option<OnInsert>, /// RETURNING pub returning: Option<Vec<SelectItem>>, + /// OUTPUT (MSSQL) + /// See <https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql> + pub output: Option<OutputClause>, /// Only for mysql pub replace_into: bool, /// Only for mysql @@ -203,6 +206,11 @@ impl Display for Insert { SpaceOrNewline.fmt(f)?; } + if let Some(output) = &self.output { + write!(f, "{output}")?; + SpaceOrNewline.fmt(f)?; + } + if let Some(settings) = &self.settings { write!(f, "SETTINGS {}", display_comma_separated(settings))?; SpaceOrNewline.fmt(f)?; @@ -289,6 +297,9 @@ pub struct Delete { pub selection: Option<Expr>, /// RETURNING pub returning: Option<Vec<SelectItem>>, + /// OUTPUT (MSSQL) + /// See <https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql> + pub output: Option<OutputClause>, /// ORDER BY (MySQL) pub order_by: Vec<OrderByExpr>, /// LIMIT (MySQL) @@ -314,6 +325,10 @@ impl Display for Delete { indented_list(f, from)?; } } + if let Some(output) = &self.output { + SpaceOrNewline.fmt(f)?; + write!(f, "{output}")?; + } if let Some(using) = &self.using { SpaceOrNewline.fmt(f)?; f.write_str("USING")?; @@ -367,6 +382,9 @@ pub struct Update { pub selection: Option<Expr>, /// RETURNING pub returning: Option<Vec<SelectItem>>, + /// OUTPUT (MSSQL) + /// See <https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql> + pub output: Option<OutputClause>, /// SQLite-specific conflict resolution clause pub or: Option<SqliteOnConflict>, /// LIMIT @@ -396,6 +414,10 @@ impl Display for Update { f.write_str("SET")?; indented_list(f, &self.assignments)?; } + if let Some(output) = &self.output { + SpaceOrNewline.fmt(f)?; + write!(f, "{output}")?; + } if let Some(UpdateTableFromKind::AfterSet(from)) = &self.from { SpaceOrNewline.fmt(f)?; f.write_str("FROM")?; @@ -717,11 +739,11 @@ impl Display for MergeUpdateExpr { } } -/// A `OUTPUT` Clause in the end of a `MERGE` Statement +/// An `OUTPUT` clause on `MERGE`, `INSERT`, `UPDATE`, or `DELETE` (MSSQL). /// /// Example: /// OUTPUT $action, deleted.* INTO dbo.temp_products; -/// [mssql](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql) +/// <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))] diff --git a/src/ast/spans.rs b/src/ast/spans.rs index 43005cfb..dd62c5ba 100644 --- a/src/ast/spans.rs +++ b/src/ast/spans.rs @@ -906,6 +906,7 @@ impl Spanned for Delete { using, selection, returning, + output, order_by, limit, } = self; @@ -923,6 +924,7 @@ impl Spanned for Delete { ) .chain(selection.iter().map(|i| i.span())) .chain(returning.iter().flat_map(|i| i.iter().map(|k| k.span()))) + .chain(output.iter().map(|i| i.span())) .chain(order_by.iter().map(|i| i.span())) .chain(limit.iter().map(|i| i.span())), ), @@ -940,6 +942,7 @@ impl Spanned for Update { from, selection, returning, + output, or: _, limit, } = self; @@ -951,6 +954,7 @@ impl Spanned for Update { .chain(from.iter().map(|i| i.span())) .chain(selection.iter().map(|i| i.span())) .chain(returning.iter().flat_map(|i| i.iter().map(|k| k.span()))) + .chain(output.iter().map(|i| i.span())) .chain(limit.iter().map(|i| i.span())), ) } @@ -1312,6 +1316,7 @@ impl Spanned for Insert { has_table_keyword: _, // bool on, returning, + output, replace_into: _, // bool priority: _, // todo, mysql specific insert_alias: _, // todo, mysql specific @@ -1334,7 +1339,8 @@ impl Spanned for Insert { .chain(partitioned.iter().flat_map(|i| i.iter().map(|k| k.span()))) .chain(after_columns.iter().map(|i| i.span)) .chain(on.as_ref().map(|i| i.span())) - .chain(returning.iter().flat_map(|i| i.iter().map(|k| k.span()))), + .chain(returning.iter().flat_map(|i| i.iter().map(|k| k.span()))) + .chain(output.iter().map(|i| i.span())), ) } } diff --git a/src/dialect/snowflake.rs b/src/dialect/snowflake.rs index a9d71fc4..f756c415 100644 --- a/src/dialect/snowflake.rs +++ b/src/dialect/snowflake.rs @@ -1784,6 +1784,7 @@ fn parse_multi_table_insert( has_table_keyword: false, on: None, returning: None, + output: None, replace_into: false, priority: None, insert_alias: None, diff --git a/src/keywords.rs b/src/keywords.rs index cc2b9e9d..80f679c0 100644 --- a/src/keywords.rs +++ b/src/keywords.rs @@ -1210,6 +1210,7 @@ pub const RESERVED_FOR_TABLE_ALIAS: &[Keyword] = &[ Keyword::ANTI, Keyword::SEMI, Keyword::RETURNING, + Keyword::OUTPUT, Keyword::ASOF, Keyword::MATCH_CONDITION, // for MSSQL-specific OUTER APPLY (seems reserved in most dialects) @@ -1264,6 +1265,7 @@ pub const RESERVED_FOR_COLUMN_ALIAS: &[Keyword] = &[ Keyword::CLUSTER, Keyword::DISTRIBUTE, Keyword::RETURNING, + Keyword::VALUES, // Reserved only as a column alias in the `SELECT` clause Keyword::FROM, Keyword::INTO, diff --git a/src/parser/merge.rs b/src/parser/merge.rs index a927bc4b..619be612 100644 --- a/src/parser/merge.rs +++ b/src/parser/merge.rs @@ -218,7 +218,21 @@ impl Parser<'_> { self.parse_parenthesized_qualified_column_list(IsOptional::Optional, allow_empty) } - fn parse_output( + /// Parses an `OUTPUT` clause if present (MSSQL). + pub(super) fn maybe_parse_output_clause( + &mut self, + ) -> Result<Option<OutputClause>, ParserError> { + if self.parse_keyword(Keyword::OUTPUT) { + Ok(Some(self.parse_output( + Keyword::OUTPUT, + self.get_current_token().clone(), + )?)) + } else { + Ok(None) + } + } + + pub(super) fn parse_output( &mut self, start_keyword: Keyword, start_token: TokenWithSpan, diff --git a/src/parser/mod.rs b/src/parser/mod.rs index bc91213f..75450f75 100644 --- a/src/parser/mod.rs +++ b/src/parser/mod.rs @@ -13309,6 +13309,9 @@ impl<'a> Parser<'a> { }; let from = self.parse_comma_separated(Parser::parse_table_and_joins)?; + + let output = self.maybe_parse_output_clause()?; + let using = if self.parse_keyword(Keyword::USING) { Some(self.parse_comma_separated(Parser::parse_table_and_joins)?) } else { @@ -13347,6 +13350,7 @@ impl<'a> Parser<'a> { using, selection, returning, + output, order_by, limit, })) @@ -17275,10 +17279,10 @@ impl<'a> Parser<'a> { let is_mysql = dialect_of!(self is MySqlDialect); - let (columns, partitioned, after_columns, source, assignments) = if self + let (columns, partitioned, after_columns, output, source, assignments) = if self .parse_keywords(&[Keyword::DEFAULT, Keyword::VALUES]) { - (vec![], None, vec![], None, vec![]) + (vec![], None, vec![], None, None, vec![]) } else { let (columns, partitioned, after_columns) = if !self.peek_subquery_start() { let columns = self.parse_parenthesized_column_list(Optional, is_mysql)?; @@ -17295,6 +17299,8 @@ impl<'a> Parser<'a> { Default::default() }; + let output = self.maybe_parse_output_clause()?; + let (source, assignments) = if self.peek_keyword(Keyword::FORMAT) || self.peek_keyword(Keyword::SETTINGS) { @@ -17305,7 +17311,14 @@ impl<'a> Parser<'a> { (Some(self.parse_query()?), vec![]) }; - (columns, partitioned, after_columns, source, assignments) + ( + columns, + partitioned, + after_columns, + output, + source, + assignments, + ) }; let (format_clause, settings) = if self.dialect.supports_insert_format() { @@ -17407,6 +17420,7 @@ impl<'a> Parser<'a> { has_table_keyword: table, on, returning, + output, replace_into, priority, insert_alias, @@ -17512,6 +17526,9 @@ impl<'a> Parser<'a> { }; self.expect_keyword(Keyword::SET)?; let assignments = self.parse_comma_separated(Parser::parse_assignment)?; + + let output = self.maybe_parse_output_clause()?; + let from = if from_before_set.is_none() && self.parse_keyword(Keyword::FROM) { Some(UpdateTableFromKind::AfterSet( self.parse_table_with_joins()?, @@ -17542,6 +17559,7 @@ impl<'a> Parser<'a> { from, selection, returning, + output, or, limit, } diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs index ad7697a7..7bf27640 100644 --- a/tests/sqlparser_common.rs +++ b/tests/sqlparser_common.rs @@ -530,6 +530,7 @@ fn parse_update_set_from() { ])), }), returning: None, + output: None, or: None, limit: None }) @@ -553,6 +554,7 @@ fn parse_update_with_table_alias() { limit: None, optimizer_hints, update_token: _, + output: _, }) if optimizer_hints.is_empty() => { assert_eq!( TableWithJoins { diff --git a/tests/sqlparser_mssql.rs b/tests/sqlparser_mssql.rs index 8bdb1c20..9033efe0 100644 --- a/tests/sqlparser_mssql.rs +++ b/tests/sqlparser_mssql.rs @@ -2806,3 +2806,45 @@ fn test_exec_dynamic_sql() { .expect("EXEC (@sql) followed by DROP TABLE should parse"); assert_eq!(stmts.len(), 2); } + +// MSSQL OUTPUT clause on INSERT/UPDATE/DELETE +// https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql +#[test] +fn parse_mssql_insert_with_output() { + ms_and_generic().verified_stmt( + "INSERT INTO customers (name, email) OUTPUT INSERTED.id, INSERTED.name VALUES ('John', '[email protected]')", + ); +} + +#[test] +fn parse_mssql_insert_with_output_into() { + ms_and_generic().verified_stmt( + "INSERT INTO customers (name, email) OUTPUT INSERTED.id, INSERTED.name INTO @new_ids VALUES ('John', '[email protected]')", + ); +} + +#[test] +fn parse_mssql_delete_with_output() { + ms_and_generic().verified_stmt("DELETE FROM customers OUTPUT DELETED.* WHERE id = 1"); +} + +#[test] +fn parse_mssql_delete_with_output_into() { + ms_and_generic().verified_stmt( + "DELETE FROM customers OUTPUT DELETED.id, DELETED.name INTO @deleted_rows WHERE active = 0", + ); +} + +#[test] +fn parse_mssql_update_with_output() { + ms_and_generic().verified_stmt( + "UPDATE employees SET salary = salary * 1.1 OUTPUT INSERTED.id, DELETED.salary, INSERTED.salary WHERE department = 'Engineering'", + ); +} + +#[test] +fn parse_mssql_update_with_output_into() { + ms_and_generic().verified_stmt( + "UPDATE employees SET salary = salary * 1.1 OUTPUT INSERTED.id, DELETED.salary, INSERTED.salary INTO @changes WHERE department = 'Engineering'", + ); +} diff --git a/tests/sqlparser_mysql.rs b/tests/sqlparser_mysql.rs index b4ae764c..541f7df6 100644 --- a/tests/sqlparser_mysql.rs +++ b/tests/sqlparser_mysql.rs @@ -2671,6 +2671,7 @@ fn parse_update_with_joins() { limit: None, optimizer_hints, update_token: _, + output: _, }) if optimizer_hints.is_empty() => { assert_eq!( TableWithJoins { diff --git a/tests/sqlparser_postgres.rs b/tests/sqlparser_postgres.rs index f4b3a282..434c5fd7 100644 --- a/tests/sqlparser_postgres.rs +++ b/tests/sqlparser_postgres.rs @@ -5530,6 +5530,7 @@ fn test_simple_postgres_insert_with_alias() { has_table_keyword: false, on: None, returning: None, + output: None, replace_into: false, priority: None, insert_alias: None, @@ -5612,6 +5613,7 @@ fn test_simple_postgres_insert_with_alias() { has_table_keyword: false, on: None, returning: None, + output: None, replace_into: false, priority: None, insert_alias: None, @@ -5692,6 +5694,7 @@ fn test_simple_insert_with_quoted_alias() { has_table_keyword: false, on: None, returning: None, + output: None, replace_into: false, priority: None, insert_alias: None, diff --git a/tests/sqlparser_sqlite.rs b/tests/sqlparser_sqlite.rs index a8fa8db2..33c38fb0 100644 --- a/tests/sqlparser_sqlite.rs +++ b/tests/sqlparser_sqlite.rs @@ -496,6 +496,7 @@ fn parse_update_tuple_row_values() { }, from: None, returning: None, + output: None, limit: None, update_token: AttachedToken::empty() }) --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
