Add support for first, last aggregate function parsing (#882)
* Add order by parsing to functions
* Fix doc error
* minor changes
diff --git a/src/ast/mod.rs b/src/ast/mod.rs
index e700685..14cf04f 100644
--- a/src/ast/mod.rs
+++ b/src/ast/mod.rs
@@ -3366,6 +3366,8 @@
// Some functions must be called without trailing parentheses, for example Postgres
// do it for current_catalog, current_schema, etc. This flags is used for formatting.
pub special: bool,
+ // Required ordering for the function (if empty, there is no requirement).
+ pub order_by: Vec<OrderByExpr>,
}
#[derive(Debug, Copy, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
@@ -3392,12 +3394,18 @@
if self.special {
write!(f, "{}", self.name)?;
} else {
+ let order_by = if !self.order_by.is_empty() {
+ " ORDER BY "
+ } else {
+ ""
+ };
write!(
f,
- "{}({}{})",
+ "{}({}{}{order_by}{})",
self.name,
if self.distinct { "DISTINCT " } else { "" },
display_comma_separated(&self.args),
+ display_comma_separated(&self.order_by),
)?;
if let Some(o) = &self.over {
diff --git a/src/ast/visitor.rs b/src/ast/visitor.rs
index 6787bfd..8134322 100644
--- a/src/ast/visitor.rs
+++ b/src/ast/visitor.rs
@@ -480,7 +480,7 @@
/// *expr = Expr::Function(Function {
/// name: ObjectName(vec![Ident::new("f")]),
/// args: vec![FunctionArg::Unnamed(FunctionArgExpr::Expr(old_expr))],
-/// over: None, distinct: false, special: false,
+/// over: None, distinct: false, special: false, order_by: vec![],
/// });
/// }
/// ControlFlow::<()>::Continue(())
diff --git a/src/parser.rs b/src/parser.rs
index 610ac74..9e593eb 100644
--- a/src/parser.rs
+++ b/src/parser.rs
@@ -706,6 +706,7 @@
over: None,
distinct: false,
special: true,
+ order_by: vec![],
}))
}
Keyword::CURRENT_TIMESTAMP
@@ -881,7 +882,7 @@
pub fn parse_function(&mut self, name: ObjectName) -> Result<Expr, ParserError> {
self.expect_token(&Token::LParen)?;
let distinct = self.parse_all_or_distinct()?.is_some();
- let args = self.parse_optional_args()?;
+ 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)?;
@@ -918,14 +919,15 @@
over,
distinct,
special: false,
+ order_by,
}))
}
pub fn parse_time_functions(&mut self, name: ObjectName) -> Result<Expr, ParserError> {
- let args = if self.consume_token(&Token::LParen) {
- self.parse_optional_args()?
+ let (args, order_by) = if self.consume_token(&Token::LParen) {
+ self.parse_optional_args_with_orderby()?
} else {
- vec![]
+ (vec![], vec![])
};
Ok(Expr::Function(Function {
name,
@@ -933,6 +935,7 @@
over: None,
distinct: false,
special: false,
+ order_by,
}))
}
@@ -6376,6 +6379,23 @@
}
}
+ pub fn parse_optional_args_with_orderby(
+ &mut self,
+ ) -> Result<(Vec<FunctionArg>, Vec<OrderByExpr>), ParserError> {
+ if self.consume_token(&Token::RParen) {
+ Ok((vec![], vec![]))
+ } else {
+ let args = self.parse_comma_separated(Parser::parse_function_args)?;
+ let order_by = if self.parse_keywords(&[Keyword::ORDER, Keyword::BY]) {
+ self.parse_comma_separated(Parser::parse_order_by_expr)?
+ } else {
+ vec![]
+ };
+ self.expect_token(&Token::RParen)?;
+ Ok((args, order_by))
+ }
+ }
+
/// Parse a comma-delimited list of projections after SELECT
pub fn parse_select_item(&mut self) -> Result<SelectItem, ParserError> {
match self.parse_wildcard_expr()? {
diff --git a/tests/sqlparser_bigquery.rs b/tests/sqlparser_bigquery.rs
index 0850bd4..11998ae 100644
--- a/tests/sqlparser_bigquery.rs
+++ b/tests/sqlparser_bigquery.rs
@@ -437,6 +437,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
})],
})
);
diff --git a/tests/sqlparser_clickhouse.rs b/tests/sqlparser_clickhouse.rs
index bb0ec48..23a3996 100644
--- a/tests/sqlparser_clickhouse.rs
+++ b/tests/sqlparser_clickhouse.rs
@@ -52,6 +52,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
})],
})],
into: None,
@@ -88,6 +89,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
})]
}),
op: BinaryOperator::NotEq,
@@ -135,6 +137,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(only(&select.projection))
);
@@ -189,6 +192,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[1]),
);
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs
index 448100e..fadefcf 100644
--- a/tests/sqlparser_common.rs
+++ b/tests/sqlparser_common.rs
@@ -840,6 +840,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(only(&select.projection))
);
@@ -859,6 +860,7 @@
over: None,
distinct: true,
special: false,
+ order_by: vec![],
}),
expr_from_projection(only(&select.projection))
);
@@ -1696,6 +1698,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
})),
op: BinaryOperator::Gt,
right: Box::new(Expr::Value(number("1"))),
@@ -1729,6 +1732,7 @@
}),
distinct: false,
special: false,
+ order_by: vec![],
})),
op: BinaryOperator::Eq,
right: Box::new(Expr::Value(number("1"))),
@@ -2077,6 +2081,29 @@
}
#[test]
+fn parse_agg_with_order_by() {
+ let supported_dialects = TestedDialects {
+ dialects: vec![
+ Box::new(GenericDialect {}),
+ Box::new(PostgreSqlDialect {}),
+ Box::new(MsSqlDialect {}),
+ Box::new(AnsiDialect {}),
+ Box::new(HiveDialect {}),
+ ],
+ options: None,
+ };
+
+ for sql in [
+ "SELECT FIRST_VALUE(x ORDER BY x) AS a FROM T",
+ "SELECT FIRST_VALUE(x ORDER BY x) FROM tbl",
+ "SELECT LAST_VALUE(x ORDER BY x, y) AS a FROM T",
+ "SELECT LAST_VALUE(x ORDER BY x ASC, y DESC) AS a FROM T",
+ ] {
+ supported_dialects.verified_stmt(sql);
+ }
+}
+
+#[test]
fn parse_create_table() {
let sql = "CREATE TABLE uk_cities (\
name VARCHAR(100) NOT NULL,\
@@ -3121,6 +3148,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(only(&select.projection))
);
@@ -3239,6 +3267,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(only(&select.projection))
);
@@ -3277,6 +3306,7 @@
}),
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[0])
);
@@ -3675,6 +3705,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
})),
time_zone: "UTC-06:00".to_string(),
},
@@ -3701,6 +3732,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
},)),
time_zone: "UTC-06:00".to_string(),
},),),
@@ -3711,6 +3743,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
},),
alias: Ident {
value: "hour".to_string(),
@@ -3868,6 +3901,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
});
assert_eq!(expr, expected_expr);
assert_eq!(alias, table_alias("a"))
@@ -6292,6 +6326,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[0])
);
@@ -6308,6 +6343,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[0])
);
@@ -6324,6 +6360,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[0])
);
@@ -6340,6 +6377,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[0])
);
@@ -6356,6 +6394,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[0])
);
@@ -6820,6 +6859,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
value_column: vec![Ident::new("a"), Ident::new("MONTH")],
pivot_values: vec![
diff --git a/tests/sqlparser_hive.rs b/tests/sqlparser_hive.rs
index 87c2660..ddc5a8c 100644
--- a/tests/sqlparser_hive.rs
+++ b/tests/sqlparser_hive.rs
@@ -346,6 +346,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[1]),
);
diff --git a/tests/sqlparser_mssql.rs b/tests/sqlparser_mssql.rs
index 1f708b5..53db332 100644
--- a/tests/sqlparser_mssql.rs
+++ b/tests/sqlparser_mssql.rs
@@ -177,6 +177,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[1]),
);
diff --git a/tests/sqlparser_mysql.rs b/tests/sqlparser_mysql.rs
index c70f2cb..ba6f2b5 100644
--- a/tests/sqlparser_mysql.rs
+++ b/tests/sqlparser_mysql.rs
@@ -791,6 +791,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
})
},
Assignment {
@@ -803,6 +804,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
})
},
Assignment {
@@ -815,6 +817,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
})
},
Assignment {
@@ -827,6 +830,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
})
},
Assignment {
@@ -839,6 +843,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
})
},
])),
@@ -1182,6 +1187,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
})),
},],
}],
diff --git a/tests/sqlparser_postgres.rs b/tests/sqlparser_postgres.rs
index 5abf172..94e5d23 100644
--- a/tests/sqlparser_postgres.rs
+++ b/tests/sqlparser_postgres.rs
@@ -2057,7 +2057,8 @@
)))],
over: None,
distinct: false,
- special: false
+ special: false,
+ order_by: vec![],
}))))
}),
select.projection[0]
@@ -2219,6 +2220,7 @@
over: None,
distinct: false,
special: true,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[0])
);
@@ -2229,6 +2231,7 @@
over: None,
distinct: false,
special: true,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[1])
);
@@ -2239,6 +2242,7 @@
over: None,
distinct: false,
special: true,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[2])
);
@@ -2249,6 +2253,7 @@
over: None,
distinct: false,
special: true,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[3])
);
@@ -2503,6 +2508,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[1]),
);
diff --git a/tests/sqlparser_redshift.rs b/tests/sqlparser_redshift.rs
index d3a6769..c44f6de 100644
--- a/tests/sqlparser_redshift.rs
+++ b/tests/sqlparser_redshift.rs
@@ -132,6 +132,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[1]),
);
diff --git a/tests/sqlparser_snowflake.rs b/tests/sqlparser_snowflake.rs
index d1a63dc..9a54c89 100644
--- a/tests/sqlparser_snowflake.rs
+++ b/tests/sqlparser_snowflake.rs
@@ -249,6 +249,7 @@
over: None,
distinct: false,
special: false,
+ order_by: vec![],
}),
expr_from_projection(&select.projection[1]),
);
