Named window frames (#881)
* after over clause, named window can be parsed with window ... as after having clause
* Lint errors are fixed
* Support for multiple windows
* fix lint errors
* simplifications
* rename function
* Rewrite named window search in functional style
* Test added and some minor changes
* Minor changes on tests and namings, and semantic check is removed
---------
Co-authored-by: Mustafa Akur <mustafa.akur@synnada.ai>
Co-authored-by: Mehmet Ozan Kabak <ozankabak@gmail.com>
diff --git a/src/ast/mod.rs b/src/ast/mod.rs
index 14cf04f..ee3366a 100644
--- a/src/ast/mod.rs
+++ b/src/ast/mod.rs
@@ -17,7 +17,7 @@
string::{String, ToString},
vec::Vec,
};
-use core::fmt;
+use core::fmt::{self, Display};
#[cfg(feature = "serde")]
use serde::{Deserialize, Serialize};
@@ -35,10 +35,10 @@
pub use self::operator::{BinaryOperator, UnaryOperator};
pub use self::query::{
Cte, Distinct, ExceptSelectItem, ExcludeSelectItem, Fetch, IdentWithAlias, Join,
- JoinConstraint, JoinOperator, LateralView, LockClause, LockType, NonBlock, Offset, OffsetRows,
- OrderByExpr, Query, RenameSelectItem, ReplaceSelectElement, ReplaceSelectItem, Select,
- SelectInto, SelectItem, SetExpr, SetOperator, SetQuantifier, Table, TableAlias, TableFactor,
- TableWithJoins, Top, Values, WildcardAdditionalOptions, With,
+ JoinConstraint, JoinOperator, LateralView, LockClause, LockType, NamedWindowDefinition,
+ NonBlock, Offset, OffsetRows, OrderByExpr, Query, RenameSelectItem, ReplaceSelectElement,
+ ReplaceSelectItem, Select, SelectInto, SelectItem, SetExpr, SetOperator, SetQuantifier, Table,
+ TableAlias, TableFactor, TableWithJoins, Top, Values, WildcardAdditionalOptions, With,
};
pub use self::value::{
escape_quoted_string, DateTimeField, DollarQuotedString, TrimWhereField, Value,
@@ -930,6 +930,23 @@
}
}
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub enum WindowType {
+ WindowSpec(WindowSpec),
+ NamedWindow(Ident),
+}
+
+impl Display for WindowType {
+ fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
+ match self {
+ WindowType::WindowSpec(spec) => write!(f, "({})", spec),
+ WindowType::NamedWindow(name) => write!(f, "{}", name),
+ }
+ }
+}
+
/// A window specification (i.e. `OVER (PARTITION BY .. ORDER BY .. etc.)`)
#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
@@ -3360,7 +3377,7 @@
pub struct Function {
pub name: ObjectName,
pub args: Vec<FunctionArg>,
- pub over: Option<WindowSpec>,
+ pub over: Option<WindowType>,
// aggregate functions may specify eg `COUNT(DISTINCT x)`
pub distinct: bool,
// Some functions must be called without trailing parentheses, for example Postgres
@@ -3409,7 +3426,7 @@
)?;
if let Some(o) = &self.over {
- write!(f, " OVER ({o})")?;
+ write!(f, " OVER {o}")?;
}
}
diff --git a/src/ast/query.rs b/src/ast/query.rs
index a85c62a..a709e10 100644
--- a/src/ast/query.rs
+++ b/src/ast/query.rs
@@ -216,6 +216,8 @@
pub sort_by: Vec<Expr>,
/// HAVING
pub having: Option<Expr>,
+ /// WINDOW AS
+ pub named_window: Vec<NamedWindowDefinition>,
/// QUALIFY (Snowflake)
pub qualify: Option<Expr>,
}
@@ -269,6 +271,9 @@
if let Some(ref having) = self.having {
write!(f, " HAVING {having}")?;
}
+ if !self.named_window.is_empty() {
+ write!(f, " WINDOW {}", display_comma_separated(&self.named_window))?;
+ }
if let Some(ref qualify) = self.qualify {
write!(f, " QUALIFY {qualify}")?;
}
@@ -314,6 +319,17 @@
#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub struct NamedWindowDefinition(pub Ident, pub WindowSpec);
+
+impl fmt::Display for NamedWindowDefinition {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ write!(f, "{} AS ({})", self.0, self.1)
+ }
+}
+
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
pub struct With {
pub recursive: bool,
pub cte_tables: Vec<Cte>,
diff --git a/src/keywords.rs b/src/keywords.rs
index 6132b31..e73b89a 100644
--- a/src/keywords.rs
+++ b/src/keywords.rs
@@ -688,6 +688,7 @@
Keyword::OUTER,
Keyword::SET,
Keyword::QUALIFY,
+ Keyword::WINDOW,
];
/// Can't be used as a column alias, so that `SELECT <expr> alias`
diff --git a/src/parser.rs b/src/parser.rs
index 9e593eb..bab5814 100644
--- a/src/parser.rs
+++ b/src/parser.rs
@@ -884,32 +884,12 @@
let distinct = self.parse_all_or_distinct()?.is_some();
let (args, order_by) = self.parse_optional_args_with_orderby()?;
let over = if self.parse_keyword(Keyword::OVER) {
- // TBD: support window names (`OVER mywin`) in place of inline specification
- self.expect_token(&Token::LParen)?;
- let partition_by = if self.parse_keywords(&[Keyword::PARTITION, Keyword::BY]) {
- // a list of possibly-qualified column names
- self.parse_comma_separated(Parser::parse_expr)?
+ if self.consume_token(&Token::LParen) {
+ let window_spec = self.parse_window_spec()?;
+ Some(WindowType::WindowSpec(window_spec))
} else {
- vec![]
- };
- let order_by = if self.parse_keywords(&[Keyword::ORDER, Keyword::BY]) {
- self.parse_comma_separated(Parser::parse_order_by_expr)?
- } else {
- vec![]
- };
- let window_frame = if !self.consume_token(&Token::RParen) {
- let window_frame = self.parse_window_frame()?;
- self.expect_token(&Token::RParen)?;
- Some(window_frame)
- } else {
- None
- };
-
- Some(WindowSpec {
- partition_by,
- order_by,
- window_frame,
- })
+ Some(WindowType::NamedWindow(self.parse_identifier()?))
+ }
} else {
None
};
@@ -5367,6 +5347,12 @@
None
};
+ let named_windows = if self.parse_keyword(Keyword::WINDOW) {
+ self.parse_comma_separated(Parser::parse_named_window)?
+ } else {
+ vec![]
+ };
+
let qualify = if self.parse_keyword(Keyword::QUALIFY) {
Some(self.parse_expr()?)
} else {
@@ -5386,6 +5372,7 @@
distribute_by,
sort_by,
having,
+ named_window: named_windows,
qualify,
})
}
@@ -7033,6 +7020,39 @@
pub fn index(&self) -> usize {
self.index
}
+
+ pub fn parse_named_window(&mut self) -> Result<NamedWindowDefinition, ParserError> {
+ let ident = self.parse_identifier()?;
+ self.expect_keyword(Keyword::AS)?;
+ self.expect_token(&Token::LParen)?;
+ let window_spec = self.parse_window_spec()?;
+ Ok(NamedWindowDefinition(ident, window_spec))
+ }
+
+ pub fn parse_window_spec(&mut self) -> Result<WindowSpec, ParserError> {
+ let partition_by = if self.parse_keywords(&[Keyword::PARTITION, Keyword::BY]) {
+ self.parse_comma_separated(Parser::parse_expr)?
+ } else {
+ vec![]
+ };
+ let order_by = if self.parse_keywords(&[Keyword::ORDER, Keyword::BY]) {
+ self.parse_comma_separated(Parser::parse_order_by_expr)?
+ } else {
+ vec![]
+ };
+ let window_frame = if !self.consume_token(&Token::RParen) {
+ let window_frame = self.parse_window_frame()?;
+ self.expect_token(&Token::RParen)?;
+ Some(window_frame)
+ } else {
+ None
+ };
+ Ok(WindowSpec {
+ partition_by,
+ order_by,
+ window_frame,
+ })
+ }
}
impl Word {
diff --git a/tests/sqlparser_clickhouse.rs b/tests/sqlparser_clickhouse.rs
index 23a3996..24c6415 100644
--- a/tests/sqlparser_clickhouse.rs
+++ b/tests/sqlparser_clickhouse.rs
@@ -101,6 +101,7 @@
distribute_by: vec![],
sort_by: vec![],
having: None,
+ named_window: vec![],
qualify: None
},
select
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs
index fadefcf..a5458ca 100644
--- a/tests/sqlparser_common.rs
+++ b/tests/sqlparser_common.rs
@@ -19,7 +19,6 @@
//! dialect-specific parsing rules).
use matches::assert_matches;
-
use sqlparser::ast::SelectItem::UnnamedExpr;
use sqlparser::ast::TableFactor::Pivot;
use sqlparser::ast::*;
@@ -251,6 +250,7 @@
distribute_by: vec![],
sort_by: vec![],
having: None,
+ named_window: vec![],
qualify: None
}))),
order_by: vec![],
@@ -1721,7 +1721,7 @@
left: Box::new(Expr::Function(Function {
name: ObjectName(vec![Ident::new("ROW_NUMBER")]),
args: vec![],
- over: Some(WindowSpec {
+ over: Some(WindowType::WindowSpec(WindowSpec {
partition_by: vec![Expr::Identifier(Ident::new("p"))],
order_by: vec![OrderByExpr {
expr: Expr::Identifier(Ident::new("o")),
@@ -1729,7 +1729,7 @@
nulls_first: None,
}],
window_frame: None,
- }),
+ })),
distinct: false,
special: false,
order_by: vec![],
@@ -3295,7 +3295,7 @@
&Expr::Function(Function {
name: ObjectName(vec![Ident::new("row_number")]),
args: vec![],
- over: Some(WindowSpec {
+ over: Some(WindowType::WindowSpec(WindowSpec {
partition_by: vec![],
order_by: vec![OrderByExpr {
expr: Expr::Identifier(Ident::new("dt")),
@@ -3303,7 +3303,7 @@
nulls_first: None,
}],
window_frame: None,
- }),
+ })),
distinct: false,
special: false,
order_by: vec![],
@@ -3313,6 +3313,128 @@
}
#[test]
+fn test_parse_named_window() {
+ let sql = "SELECT \
+ MIN(c12) OVER window1 AS min1, \
+ MAX(c12) OVER window2 AS max1 \
+ FROM aggregate_test_100 \
+ WINDOW window1 AS (ORDER BY C12), \
+ window2 AS (PARTITION BY C11) \
+ ORDER BY C3";
+ let actual_select_only = verified_only_select(sql);
+ let expected = Select {
+ distinct: None,
+ top: None,
+ projection: vec![
+ SelectItem::ExprWithAlias {
+ expr: Expr::Function(Function {
+ name: ObjectName(vec![Ident {
+ value: "MIN".to_string(),
+ quote_style: None,
+ }]),
+ args: vec![FunctionArg::Unnamed(FunctionArgExpr::Expr(
+ Expr::Identifier(Ident {
+ value: "c12".to_string(),
+ quote_style: None,
+ }),
+ ))],
+ over: Some(WindowType::NamedWindow(Ident {
+ value: "window1".to_string(),
+ quote_style: None,
+ })),
+ distinct: false,
+ special: false,
+ }),
+ alias: Ident {
+ value: "min1".to_string(),
+ quote_style: None,
+ },
+ },
+ SelectItem::ExprWithAlias {
+ expr: Expr::Function(Function {
+ name: ObjectName(vec![Ident {
+ value: "MAX".to_string(),
+ quote_style: None,
+ }]),
+ args: vec![FunctionArg::Unnamed(FunctionArgExpr::Expr(
+ Expr::Identifier(Ident {
+ value: "c12".to_string(),
+ quote_style: None,
+ }),
+ ))],
+ over: Some(WindowType::NamedWindow(Ident {
+ value: "window2".to_string(),
+ quote_style: None,
+ })),
+ distinct: false,
+ special: false,
+ }),
+ alias: Ident {
+ value: "max1".to_string(),
+ quote_style: None,
+ },
+ },
+ ],
+ into: None,
+ from: vec![TableWithJoins {
+ relation: TableFactor::Table {
+ name: ObjectName(vec![Ident {
+ value: "aggregate_test_100".to_string(),
+ quote_style: None,
+ }]),
+ alias: None,
+ args: None,
+ with_hints: vec![],
+ },
+ joins: vec![],
+ }],
+ lateral_views: vec![],
+ selection: None,
+ group_by: vec![],
+ cluster_by: vec![],
+ distribute_by: vec![],
+ sort_by: vec![],
+ having: None,
+ named_window: vec![
+ NamedWindowDefinition(
+ Ident {
+ value: "window1".to_string(),
+ quote_style: None,
+ },
+ WindowSpec {
+ partition_by: vec![],
+ order_by: vec![OrderByExpr {
+ expr: Expr::Identifier(Ident {
+ value: "C12".to_string(),
+ quote_style: None,
+ }),
+ asc: None,
+ nulls_first: None,
+ }],
+ window_frame: None,
+ },
+ ),
+ NamedWindowDefinition(
+ Ident {
+ value: "window2".to_string(),
+ quote_style: None,
+ },
+ WindowSpec {
+ partition_by: vec![Expr::Identifier(Ident {
+ value: "C11".to_string(),
+ quote_style: None,
+ })],
+ order_by: vec![],
+ window_frame: None,
+ },
+ ),
+ ],
+ qualify: None,
+ };
+ assert_eq!(actual_select_only, expected);
+}
+
+#[test]
fn parse_aggregate_with_group_by() {
let sql = "SELECT a, COUNT(1), MIN(b), MAX(b) FROM foo GROUP BY a";
let _ast = verified_only_select(sql);
@@ -3659,6 +3781,7 @@
distribute_by: vec![],
sort_by: vec![],
having: None,
+ named_window: vec![],
qualify: None,
}))),
order_by: vec![],
@@ -5929,6 +6052,7 @@
distribute_by: vec![],
sort_by: vec![],
having: None,
+ named_window: vec![],
qualify: None,
}))),
order_by: vec![],
diff --git a/tests/sqlparser_mysql.rs b/tests/sqlparser_mysql.rs
index ba6f2b5..3e5c810 100644
--- a/tests/sqlparser_mysql.rs
+++ b/tests/sqlparser_mysql.rs
@@ -460,6 +460,7 @@
distribute_by: vec![],
sort_by: vec![],
having: None,
+ named_window: vec![],
qualify: None
}))),
order_by: vec![],
@@ -494,6 +495,7 @@
distribute_by: vec![],
sort_by: vec![],
having: None,
+ named_window: vec![],
qualify: None
}))),
order_by: vec![],
@@ -884,6 +886,7 @@
distribute_by: vec![],
sort_by: vec![],
having: None,
+ named_window: vec![],
qualify: None,
})))
);
@@ -927,6 +930,7 @@
distribute_by: vec![],
sort_by: vec![],
having: None,
+ named_window: vec![],
qualify: None,
})))
);
@@ -1116,6 +1120,7 @@
distribute_by: vec![],
sort_by: vec![],
having: None,
+ named_window: vec![],
qualify: None
}))),
order_by: vec![],
@@ -1394,6 +1399,7 @@
distribute_by: vec![],
sort_by: vec![],
having: None,
+ named_window: vec![],
qualify: None,
into: None
}))),
diff --git a/tests/sqlparser_postgres.rs b/tests/sqlparser_postgres.rs
index 94e5d23..80a4261 100644
--- a/tests/sqlparser_postgres.rs
+++ b/tests/sqlparser_postgres.rs
@@ -954,6 +954,7 @@
selection: None,
group_by: vec![],
having: None,
+ named_window: vec![],
cluster_by: vec![],
distribute_by: vec![],
sort_by: vec![],
@@ -1799,6 +1800,7 @@
distribute_by: vec![],
sort_by: vec![],
having: None,
+ named_window: vec![],
qualify: None,
}))),
right: Box::new(SetExpr::Select(Box::new(Select {
@@ -1820,6 +1822,7 @@
distribute_by: vec![],
sort_by: vec![],
having: None,
+ named_window: vec![],
qualify: None,
}))),
}),
