You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by if...@apache.org on 2025/08/01 14:52:46 UTC
(datafusion-sqlparser-rs) branch main updated: feat: support multi value columns and aliases in unpivot (#1969)
This is an automated email from the ASF dual-hosted git repository.
iffyio 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 dd650b88 feat: support multi value columns and aliases in unpivot (#1969)
dd650b88 is described below
commit dd650b88f3fed130cc34fc3ae50435e61aa1f947
Author: Chen Chongchen <ch...@qq.com>
AuthorDate: Fri Aug 1 22:52:41 2025 +0800
feat: support multi value columns and aliases in unpivot (#1969)
---
src/ast/query.rs | 5 +-
src/ast/spans.rs | 4 +-
src/parser/mod.rs | 6 +-
tests/sqlparser_common.rs | 159 +++++++++++++++++++++++++++++++++++++++-------
4 files changed, 144 insertions(+), 30 deletions(-)
diff --git a/src/ast/query.rs b/src/ast/query.rs
index 7ffb64d9..ea641deb 100644
--- a/src/ast/query.rs
+++ b/src/ast/query.rs
@@ -1349,11 +1349,12 @@ pub enum TableFactor {
/// ```
///
/// See <https://docs.snowflake.com/en/sql-reference/constructs/unpivot>.
+ /// See <https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-unpivot>.
Unpivot {
table: Box<TableFactor>,
- value: Ident,
+ value: Expr,
name: Ident,
- columns: Vec<Ident>,
+ columns: Vec<ExprWithAlias>,
null_inclusion: Option<NullInclusion>,
alias: Option<TableAlias>,
},
diff --git a/src/ast/spans.rs b/src/ast/spans.rs
index 58583660..dec26566 100644
--- a/src/ast/spans.rs
+++ b/src/ast/spans.rs
@@ -2002,9 +2002,9 @@ impl Spanned for TableFactor {
alias,
} => union_spans(
core::iter::once(table.span())
- .chain(core::iter::once(value.span))
+ .chain(core::iter::once(value.span()))
.chain(core::iter::once(name.span))
- .chain(columns.iter().map(|i| i.span))
+ .chain(columns.iter().map(|ilist| ilist.span()))
.chain(alias.as_ref().map(|alias| alias.span())),
),
TableFactor::MatchRecognize {
diff --git a/src/parser/mod.rs b/src/parser/mod.rs
index 75b2cf52..455e0caa 100644
--- a/src/parser/mod.rs
+++ b/src/parser/mod.rs
@@ -14033,11 +14033,13 @@ impl<'a> Parser<'a> {
None
};
self.expect_token(&Token::LParen)?;
- let value = self.parse_identifier()?;
+ let value = self.parse_expr()?;
self.expect_keyword_is(Keyword::FOR)?;
let name = self.parse_identifier()?;
self.expect_keyword_is(Keyword::IN)?;
- let columns = self.parse_parenthesized_column_list(Mandatory, false)?;
+ let columns = self.parse_parenthesized_column_list_inner(Mandatory, false, |p| {
+ p.parse_expr_with_alias()
+ })?;
self.expect_token(&Token::RParen)?;
let alias = self.maybe_parse_table_alias()?;
Ok(TableFactor::Unpivot {
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs
index 72b6c4e4..5e389aee 100644
--- a/tests/sqlparser_common.rs
+++ b/tests/sqlparser_common.rs
@@ -11009,20 +11009,14 @@ fn parse_unpivot_table() {
index_hints: vec![],
}),
null_inclusion: None,
- value: Ident {
- value: "quantity".to_string(),
- quote_style: None,
- span: Span::empty(),
- },
-
- name: Ident {
- value: "quarter".to_string(),
- quote_style: None,
- span: Span::empty(),
- },
+ value: Expr::Identifier(Ident::new("quantity")),
+ name: Ident::new("quarter"),
columns: ["Q1", "Q2", "Q3", "Q4"]
.into_iter()
- .map(Ident::new)
+ .map(|col| ExprWithAlias {
+ expr: Expr::Identifier(Ident::new(col)),
+ alias: None,
+ })
.collect(),
alias: Some(TableAlias {
name: Ident::new("u"),
@@ -11084,6 +11078,129 @@ fn parse_unpivot_table() {
verified_stmt(sql_unpivot_include_nulls).to_string(),
sql_unpivot_include_nulls
);
+
+ let sql_unpivot_with_alias = concat!(
+ "SELECT * FROM sales AS s ",
+ "UNPIVOT INCLUDE NULLS ",
+ "(quantity FOR quarter IN ",
+ "(Q1 AS Quater1, Q2 AS Quater2, Q3 AS Quater3, Q4 AS Quater4)) ",
+ "AS u (product, quarter, quantity)"
+ );
+
+ if let Unpivot { value, columns, .. } =
+ &verified_only_select(sql_unpivot_with_alias).from[0].relation
+ {
+ assert_eq!(
+ *columns,
+ vec![
+ ExprWithAlias {
+ expr: Expr::Identifier(Ident::new("Q1")),
+ alias: Some(Ident::new("Quater1")),
+ },
+ ExprWithAlias {
+ expr: Expr::Identifier(Ident::new("Q2")),
+ alias: Some(Ident::new("Quater2")),
+ },
+ ExprWithAlias {
+ expr: Expr::Identifier(Ident::new("Q3")),
+ alias: Some(Ident::new("Quater3")),
+ },
+ ExprWithAlias {
+ expr: Expr::Identifier(Ident::new("Q4")),
+ alias: Some(Ident::new("Quater4")),
+ },
+ ]
+ );
+ assert_eq!(*value, Expr::Identifier(Ident::new("quantity")));
+ }
+
+ assert_eq!(
+ verified_stmt(sql_unpivot_with_alias).to_string(),
+ sql_unpivot_with_alias
+ );
+
+ let sql_unpivot_with_alias_and_multi_value = concat!(
+ "SELECT * FROM sales AS s ",
+ "UNPIVOT INCLUDE NULLS ((first_quarter, second_quarter) ",
+ "FOR half_of_the_year IN (",
+ "(Q1, Q2) AS H1, ",
+ "(Q3, Q4) AS H2",
+ "))"
+ );
+
+ if let Unpivot { value, columns, .. } =
+ &verified_only_select(sql_unpivot_with_alias_and_multi_value).from[0].relation
+ {
+ assert_eq!(
+ *columns,
+ vec![
+ ExprWithAlias {
+ expr: Expr::Tuple(vec![
+ Expr::Identifier(Ident::new("Q1")),
+ Expr::Identifier(Ident::new("Q2")),
+ ]),
+ alias: Some(Ident::new("H1")),
+ },
+ ExprWithAlias {
+ expr: Expr::Tuple(vec![
+ Expr::Identifier(Ident::new("Q3")),
+ Expr::Identifier(Ident::new("Q4")),
+ ]),
+ alias: Some(Ident::new("H2")),
+ },
+ ]
+ );
+ assert_eq!(
+ *value,
+ Expr::Tuple(vec![
+ Expr::Identifier(Ident::new("first_quarter")),
+ Expr::Identifier(Ident::new("second_quarter")),
+ ])
+ );
+ }
+
+ assert_eq!(
+ verified_stmt(sql_unpivot_with_alias_and_multi_value).to_string(),
+ sql_unpivot_with_alias_and_multi_value
+ );
+
+ let sql_unpivot_with_alias_and_multi_value_and_qualifier = concat!(
+ "SELECT * FROM sales AS s ",
+ "UNPIVOT INCLUDE NULLS ((first_quarter, second_quarter) ",
+ "FOR half_of_the_year IN (",
+ "(sales.Q1, sales.Q2) AS H1, ",
+ "(sales.Q3, sales.Q4) AS H2",
+ "))"
+ );
+
+ if let Unpivot { columns, .. } =
+ &verified_only_select(sql_unpivot_with_alias_and_multi_value_and_qualifier).from[0].relation
+ {
+ assert_eq!(
+ *columns,
+ vec![
+ ExprWithAlias {
+ expr: Expr::Tuple(vec![
+ Expr::CompoundIdentifier(vec![Ident::new("sales"), Ident::new("Q1"),]),
+ Expr::CompoundIdentifier(vec![Ident::new("sales"), Ident::new("Q2"),]),
+ ]),
+ alias: Some(Ident::new("H1")),
+ },
+ ExprWithAlias {
+ expr: Expr::Tuple(vec![
+ Expr::CompoundIdentifier(vec![Ident::new("sales"), Ident::new("Q3"),]),
+ Expr::CompoundIdentifier(vec![Ident::new("sales"), Ident::new("Q4"),]),
+ ]),
+ alias: Some(Ident::new("H2")),
+ },
+ ]
+ );
+ }
+
+ assert_eq!(
+ verified_stmt(sql_unpivot_with_alias_and_multi_value_and_qualifier).to_string(),
+ sql_unpivot_with_alias_and_multi_value_and_qualifier
+ );
}
#[test]
@@ -11181,20 +11298,14 @@ fn parse_pivot_unpivot_table() {
index_hints: vec![],
}),
null_inclusion: None,
- value: Ident {
- value: "population".to_string(),
- quote_style: None,
- span: Span::empty()
- },
-
- name: Ident {
- value: "year".to_string(),
- quote_style: None,
- span: Span::empty()
- },
+ value: Expr::Identifier(Ident::new("population")),
+ name: Ident::new("year"),
columns: ["population_2000", "population_2010"]
.into_iter()
- .map(Ident::new)
+ .map(|col| ExprWithAlias {
+ expr: Expr::Identifier(Ident::new(col)),
+ alias: None,
+ })
.collect(),
alias: Some(TableAlias {
name: Ident::new("u"),
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org