LucaCappelletti94 commented on code in PR #2503:
URL:
https://github.com/apache/datafusion-sqlparser-rs/pull/2503#discussion_r4106128395
##########
tests/sqlparser_duckdb.rs:
##########
@@ -925,3 +926,159 @@ fn test_duckdb_lambda_function() {
let sql_transform = "SELECT list_transform([1, 2, 3], lambda x : x * 2)";
duckdb().verified_stmt(sql_transform);
}
+
+#[test]
+fn create_table_sorted_by_round_trip() {
+ for sql in [
+ "CREATE TABLE events (id INTEGER, category VARCHAR) SORTED BY (id,
lower(category), id + 1)",
+ "CREATE TABLE events (id INTEGER) SORTED BY (id) WITH (format =
'parquet')",
+ "CREATE TABLE events SORTED BY (id) WITH (format = 'parquet') AS
SELECT 1 AS id",
+ "CREATE TABLE events PARTITIONED BY (id) SORTED BY (abs(id)) AS SELECT
1 AS id",
+ ] {
+ let statement = duckdb().verified_stmt(sql);
+ assert_eq!(statement, duckdb().verified_stmt(&statement.to_string()));
+ }
+ for (sql, canonical) in [
+ (
+ "CREATE TABLE events (id INTEGER) SORTED BY (id) PARTITIONED BY
(id)",
+ "CREATE TABLE events (id INTEGER) PARTITIONED BY (id) SORTED BY
(id)",
+ ),
+ (
+ "CREATE TABLE events (id INTEGER,) SORTED /* sort */ BY (id + 1,);
-- end",
+ "CREATE TABLE events (id INTEGER) SORTED BY (id + 1)",
+ ),
+ ] {
+ duckdb().one_statement_parses_to(sql, canonical);
+ }
+}
+
+#[test]
+fn create_table_sorted_by_ast_and_builder() {
+ let sql = "CREATE TABLE events (id INTEGER) SORTED BY (id, id + 1)";
+ let Statement::CreateTable(table) = duckdb().verified_stmt(sql) else {
+ panic!("expected CREATE TABLE")
+ };
+ let expressions = vec![
+ Expr::Identifier(Ident::new("id")),
+ Expr::BinaryOp {
+ left: Box::new(Expr::Identifier(Ident::new("id"))),
+ op: BinaryOperator::Plus,
+ right: Box::new(Expr::Value(number("1").into())),
+ },
+ ];
+ assert_eq!(table.sorted_by, Some(expressions.clone()));
+ assert!(table.clustered_by.is_none());
+ assert!(table.order_by.is_none());
+ assert!(table.sortkey.is_none());
+ let rebuilt =
helpers::stmt_create_table::CreateTableBuilder::from(table.clone()).build();
+ assert_eq!(table, rebuilt);
+ assert_eq!(rebuilt.to_string(), sql);
+ let built = helpers::stmt_create_table::CreateTableBuilder::new(table.name)
+ .columns(table.columns)
+ .sorted_by(Some(expressions))
+ .build();
+ assert_eq!(built.sorted_by, rebuilt.sorted_by);
+ assert_eq!(built.to_string(), sql);
+}
+
+#[test]
+fn create_table_sorted_by_span() {
+ let sql = "CREATE TABLE events (id INTEGER) SORTED BY (id + 1)";
+ let Statement::CreateTable(table) =
+ Parser::parse_sql(&DuckDbDialect {}, sql).unwrap().remove(0)
+ else {
+ unreachable!()
+ };
+ assert_eq!(
+ table.sorted_by.as_ref().unwrap()[0].span(),
+ Span::new(Location::new(1, 45), Location::new(1, 51))
+ );
+ assert_eq!(
+ table.span(),
+ Span::new(Location::new(1, 14), Location::new(1, 51))
+ );
+}
+
+#[test]
+fn create_table_sorted_by_errors() {
+ for (tail, expected) in [
+ ("SORTED BY ()", "Expected: an expression, found: )"),
+ ("SORTED BY id", "Expected: (, found: id"),
+ ("SORTED BY (id", "Expected: ), found: EOF"),
+ ("SORTED BY (id ASC)", "Expected: ), found: ASC"),
+ ("SORTED BY (id DESC)", "Expected: ), found: DESC"),
+ ("SORTED BY (id NULLS FIRST)", "Expected: ), found: NULLS"),
+ ("SORTED BY (id AS alias)", "Expected: ), found: AS"),
+ ("SORTED BY (id alias)", "Expected: ), found: alias"),
+ (
+ "SORTED BY (id) SORTED BY (id)",
+ "Expected: end of statement, found: SORTED",
+ ),
+ (
+ "SORTED BY (id) PARTITIONED BY (id) SORTED BY (id)",
+ "Expected: end of statement, found: SORTED",
+ ),
+ (
+ "PARTITIONED BY (id) SORTED BY (id) PARTITIONED BY (id)",
+ "Expected: end of statement, found: PARTITIONED",
+ ),
+ (
+ "WITH (format = 'parquet') SORTED BY (id)",
+ "Expected: end of statement, found: SORTED",
+ ),
Review Comment:
You should drop the three repeated-clause cases. DuckDB's
`OptPartitionSortedOptions` is a list of `PARTITIONED BY` / `SORTED BY` in any
order and count, and DuckDB 1.5.5 parses all three (it fails later at bind time
with `Catalog Error: ... not supported for tables in a duckdb catalog`), so the
test pins valid DuckDB SQL as a syntax error.
```suggestion
(
"WITH (format = 'parquet') SORTED BY (id)",
"Expected: end of statement, found: SORTED",
),
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
