This is an automated email from the ASF dual-hosted git repository. github-bot pushed a commit to branch gh-readonly-queue/main/pr-2156-802c7d3e03df900392a009ce60b9f30fd954ac4e in repository https://gitbox.apache.org/repos/asf/datafusion-sqlparser-rs.git
commit 2d47fec0ab0632d2c7453387a226a4c4d293f93a Author: Alexander Beedie <[email protected]> AuthorDate: Thu Jan 29 17:50:47 2026 +0400 Fix identifier parsing not breaking on the `|>` pipe operator (#2156) --- src/parser/mod.rs | 4 +- tests/sqlparser_common.rs | 425 ++++++++++++++++++++++------------------------ 2 files changed, 207 insertions(+), 222 deletions(-) diff --git a/src/parser/mod.rs b/src/parser/mod.rs index e0712017..5847f779 100644 --- a/src/parser/mod.rs +++ b/src/parser/mod.rs @@ -12418,7 +12418,9 @@ impl<'a> Parser<'a> { Token::Word(w) => { idents.push(w.to_ident(token.span)); } - Token::EOF | Token::Eq | Token::SemiColon => break, + Token::EOF | Token::Eq | Token::SemiColon | Token::VerticalBarRightAngleBracket => { + break + } _ => {} } self.advance_token(); diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs index 6da4ea53..87c15e2d 100644 --- a/tests/sqlparser_common.rs +++ b/tests/sqlparser_common.rs @@ -72,9 +72,7 @@ fn parse_numeric_literal_underscore() { assert_eq!( select.projection, - vec![UnnamedExpr(Expr::Value( - (number("10_000")).with_empty_span() - ))] + vec![UnnamedExpr(Expr::Value(number("10_000").with_empty_span()))] ); } @@ -16223,303 +16221,288 @@ fn parse_set_names() { } #[test] -fn parse_pipeline_operator() { +fn parse_pipe_operator_as() { let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> AS new_users"); +} - // select pipe operator - dialects.verified_stmt("SELECT * FROM users |> SELECT id"); - dialects.verified_stmt("SELECT * FROM users |> SELECT id, name"); +#[test] +fn parse_pipe_operator_select() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> SELECT id"); + dialects.verified_stmt("SELECT * FROM tbl |> SELECT id, name"); dialects.verified_query_with_canonical( - "SELECT * FROM users |> SELECT id user_id", - "SELECT * FROM users |> SELECT id AS user_id", + "SELECT * FROM tbl |> SELECT id user_id", + "SELECT * FROM tbl |> SELECT id AS user_id", ); - dialects.verified_stmt("SELECT * FROM users |> SELECT id AS user_id"); + dialects.verified_stmt("SELECT * FROM tbl |> SELECT id AS user_id"); +} - // extend pipe operator - dialects.verified_stmt("SELECT * FROM users |> EXTEND id + 1 AS new_id"); - dialects.verified_stmt("SELECT * FROM users |> EXTEND id AS new_id, name AS new_name"); +#[test] +fn parse_pipe_operator_extend() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> EXTEND id + 1 AS new_id"); + dialects.verified_stmt("SELECT * FROM tbl |> EXTEND id AS new_id, name AS new_name"); dialects.verified_query_with_canonical( - "SELECT * FROM users |> EXTEND id user_id", - "SELECT * FROM users |> EXTEND id AS user_id", + "SELECT * FROM tbl |> EXTEND id user_id", + "SELECT * FROM tbl |> EXTEND id AS user_id", ); +} - // set pipe operator - dialects.verified_stmt("SELECT * FROM users |> SET id = id + 1"); - dialects.verified_stmt("SELECT * FROM users |> SET id = id + 1, name = name + ' Doe'"); - - // drop pipe operator - dialects.verified_stmt("SELECT * FROM users |> DROP id"); - dialects.verified_stmt("SELECT * FROM users |> DROP id, name"); +#[test] +fn parse_pipe_operator_set() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> SET id = id + 1"); + dialects.verified_stmt("SELECT * FROM tbl |> SET id = id + 1, name = name + ' Doe'"); +} - // as pipe operator - dialects.verified_stmt("SELECT * FROM users |> AS new_users"); +#[test] +fn parse_pipe_operator_drop() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> DROP id"); + dialects.verified_stmt("SELECT * FROM tbl |> DROP id, name"); + dialects.verified_stmt("SELECT * FROM tbl |> DROP c |> RENAME a AS x"); + dialects.verified_stmt("SELECT * FROM tbl |> DROP a, b |> SELECT c"); +} - // limit pipe operator - dialects.verified_stmt("SELECT * FROM users |> LIMIT 10"); - dialects.verified_stmt("SELECT * FROM users |> LIMIT 10 OFFSET 5"); - dialects.verified_stmt("SELECT * FROM users |> LIMIT 10 |> LIMIT 5"); - dialects.verified_stmt("SELECT * FROM users |> LIMIT 10 |> WHERE true"); +#[test] +fn parse_pipe_operator_limit() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> LIMIT 10"); + dialects.verified_stmt("SELECT * FROM tbl |> LIMIT 10 OFFSET 5"); + dialects.verified_stmt("SELECT * FROM tbl |> LIMIT 10 |> LIMIT 5"); + dialects.verified_stmt("SELECT * FROM tbl |> LIMIT 10 |> WHERE true"); +} - // where pipe operator - dialects.verified_stmt("SELECT * FROM users |> WHERE id = 1"); - dialects.verified_stmt("SELECT * FROM users |> WHERE id = 1 AND name = 'John'"); - dialects.verified_stmt("SELECT * FROM users |> WHERE id = 1 OR name = 'John'"); +#[test] +fn parse_pipe_operator_where() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> WHERE id = 1"); + dialects.verified_stmt("SELECT * FROM tbl |> WHERE id = 1 AND name = 'John'"); + dialects.verified_stmt("SELECT * FROM tbl |> WHERE id = 1 OR name = 'John'"); +} - // aggregate pipe operator full table - dialects.verified_stmt("SELECT * FROM users |> AGGREGATE COUNT(*)"); +#[test] +fn parse_pipe_operator_aggregate() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> AGGREGATE COUNT(*)"); dialects.verified_query_with_canonical( - "SELECT * FROM users |> AGGREGATE COUNT(*) total_users", - "SELECT * FROM users |> AGGREGATE COUNT(*) AS total_users", - ); - dialects.verified_stmt("SELECT * FROM users |> AGGREGATE COUNT(*) AS total_users"); - dialects.verified_stmt("SELECT * FROM users |> AGGREGATE COUNT(*), MIN(id)"); - - // aggregate pipe opeprator with grouping - dialects.verified_stmt( - "SELECT * FROM users |> AGGREGATE SUM(o_totalprice) AS price, COUNT(*) AS cnt GROUP BY EXTRACT(YEAR FROM o_orderdate) AS year", + "SELECT * FROM tbl |> AGGREGATE COUNT(*) total_users", + "SELECT * FROM tbl |> AGGREGATE COUNT(*) AS total_users", ); + dialects.verified_stmt("SELECT * FROM tbl |> AGGREGATE COUNT(*) AS total_users"); + dialects.verified_stmt("SELECT * FROM tbl |> AGGREGATE COUNT(*), MIN(id)"); + dialects.verified_stmt("SELECT * FROM tbl |> AGGREGATE SUM(o_totalprice) AS price, COUNT(*) AS cnt GROUP BY EXTRACT(YEAR FROM o_orderdate) AS year"); dialects.verified_stmt( - "SELECT * FROM users |> AGGREGATE GROUP BY EXTRACT(YEAR FROM o_orderdate) AS year", + "SELECT * FROM tbl |> AGGREGATE GROUP BY EXTRACT(YEAR FROM o_orderdate) AS year", ); dialects - .verified_stmt("SELECT * FROM users |> AGGREGATE GROUP BY EXTRACT(YEAR FROM o_orderdate)"); - dialects.verified_stmt("SELECT * FROM users |> AGGREGATE GROUP BY a, b"); - dialects.verified_stmt("SELECT * FROM users |> AGGREGATE SUM(c) GROUP BY a, b"); - dialects.verified_stmt("SELECT * FROM users |> AGGREGATE SUM(c) ASC"); + .verified_stmt("SELECT * FROM tbl |> AGGREGATE GROUP BY EXTRACT(YEAR FROM o_orderdate)"); + dialects.verified_stmt("SELECT * FROM tbl |> AGGREGATE GROUP BY a, b"); + dialects.verified_stmt("SELECT * FROM tbl |> AGGREGATE SUM(c) GROUP BY a, b"); + dialects.verified_stmt("SELECT * FROM tbl |> AGGREGATE SUM(c) ASC"); +} - // order by pipe operator - dialects.verified_stmt("SELECT * FROM users |> ORDER BY id ASC"); - dialects.verified_stmt("SELECT * FROM users |> ORDER BY id DESC"); - dialects.verified_stmt("SELECT * FROM users |> ORDER BY id DESC, name ASC"); +#[test] +fn parse_pipe_operator_order_by() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> ORDER BY id ASC"); + dialects.verified_stmt("SELECT * FROM tbl |> ORDER BY id DESC"); + dialects.verified_stmt("SELECT * FROM tbl |> ORDER BY id DESC, name ASC"); +} - // tablesample pipe operator +#[test] +fn parse_pipe_operator_tablesample() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); dialects.verified_stmt("SELECT * FROM tbl |> TABLESAMPLE BERNOULLI (50)"); dialects.verified_stmt("SELECT * FROM tbl |> TABLESAMPLE SYSTEM (50 PERCENT)"); dialects.verified_stmt("SELECT * FROM tbl |> TABLESAMPLE SYSTEM (50) REPEATABLE (10)"); +} - // rename pipe operator - dialects.verified_stmt("SELECT * FROM users |> RENAME old_name AS new_name"); - dialects.verified_stmt("SELECT * FROM users |> RENAME id AS user_id, name AS user_name"); +#[test] +fn parse_pipe_operator_rename() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> RENAME old_name AS new_name"); + dialects.verified_stmt("SELECT * FROM tbl |> RENAME id AS user_id, name AS user_name"); dialects.verified_query_with_canonical( - "SELECT * FROM users |> RENAME id user_id", - "SELECT * FROM users |> RENAME id AS user_id", + "SELECT * FROM tbl |> RENAME id user_id", + "SELECT * FROM tbl |> RENAME id AS user_id", ); +} - // union pipe operator - dialects.verified_stmt("SELECT * FROM users |> UNION ALL (SELECT * FROM admins)"); - dialects.verified_stmt("SELECT * FROM users |> UNION DISTINCT (SELECT * FROM admins)"); - dialects.verified_stmt("SELECT * FROM users |> UNION (SELECT * FROM admins)"); - - // union pipe operator with multiple queries - dialects.verified_stmt( - "SELECT * FROM users |> UNION ALL (SELECT * FROM admins), (SELECT * FROM guests)", - ); - dialects.verified_stmt("SELECT * FROM users |> UNION DISTINCT (SELECT * FROM admins), (SELECT * FROM guests), (SELECT * FROM employees)"); +#[test] +fn parse_pipe_operator_union() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> UNION ALL (SELECT * FROM admins)"); + dialects.verified_stmt("SELECT * FROM tbl |> UNION DISTINCT (SELECT * FROM admins)"); + dialects.verified_stmt("SELECT * FROM tbl |> UNION (SELECT * FROM admins)"); dialects.verified_stmt( - "SELECT * FROM users |> UNION (SELECT * FROM admins), (SELECT * FROM guests)", + "SELECT * FROM tbl |> UNION ALL (SELECT * FROM admins), (SELECT * FROM guests)", ); - - // union pipe operator with BY NAME modifier - dialects.verified_stmt("SELECT * FROM users |> UNION BY NAME (SELECT * FROM admins)"); - dialects.verified_stmt("SELECT * FROM users |> UNION ALL BY NAME (SELECT * FROM admins)"); - dialects.verified_stmt("SELECT * FROM users |> UNION DISTINCT BY NAME (SELECT * FROM admins)"); - - // union pipe operator with BY NAME and multiple queries + dialects.verified_stmt("SELECT * FROM tbl |> UNION DISTINCT (SELECT * FROM admins), (SELECT * FROM guests), (SELECT * FROM employees)"); + dialects + .verified_stmt("SELECT * FROM tbl |> UNION (SELECT * FROM admins), (SELECT * FROM guests)"); + dialects.verified_stmt("SELECT * FROM tbl |> UNION BY NAME (SELECT * FROM admins)"); + dialects.verified_stmt("SELECT * FROM tbl |> UNION ALL BY NAME (SELECT * FROM admins)"); + dialects.verified_stmt("SELECT * FROM tbl |> UNION DISTINCT BY NAME (SELECT * FROM admins)"); dialects.verified_stmt( - "SELECT * FROM users |> UNION BY NAME (SELECT * FROM admins), (SELECT * FROM guests)", + "SELECT * FROM tbl |> UNION BY NAME (SELECT * FROM admins), (SELECT * FROM guests)", ); +} - // intersect pipe operator (BigQuery requires DISTINCT modifier for INTERSECT) - dialects.verified_stmt("SELECT * FROM users |> INTERSECT DISTINCT (SELECT * FROM admins)"); - - // intersect pipe operator with BY NAME modifier +#[test] +fn parse_pipe_operator_intersect() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> INTERSECT DISTINCT (SELECT * FROM admins)"); dialects - .verified_stmt("SELECT * FROM users |> INTERSECT DISTINCT BY NAME (SELECT * FROM admins)"); - - // intersect pipe operator with multiple queries + .verified_stmt("SELECT * FROM tbl |> INTERSECT DISTINCT BY NAME (SELECT * FROM admins)"); dialects.verified_stmt( - "SELECT * FROM users |> INTERSECT DISTINCT (SELECT * FROM admins), (SELECT * FROM guests)", + "SELECT * FROM tbl |> INTERSECT DISTINCT (SELECT * FROM admins), (SELECT * FROM guests)", ); + dialects.verified_stmt("SELECT * FROM tbl |> INTERSECT DISTINCT BY NAME (SELECT * FROM admins), (SELECT * FROM guests)"); +} - // intersect pipe operator with BY NAME and multiple queries - dialects.verified_stmt("SELECT * FROM users |> INTERSECT DISTINCT BY NAME (SELECT * FROM admins), (SELECT * FROM guests)"); - - // except pipe operator (BigQuery requires DISTINCT modifier for EXCEPT) - dialects.verified_stmt("SELECT * FROM users |> EXCEPT DISTINCT (SELECT * FROM admins)"); - - // except pipe operator with BY NAME modifier - dialects.verified_stmt("SELECT * FROM users |> EXCEPT DISTINCT BY NAME (SELECT * FROM admins)"); - - // except pipe operator with multiple queries +#[test] +fn parse_pipe_operator_except() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> EXCEPT DISTINCT (SELECT * FROM admins)"); + dialects.verified_stmt("SELECT * FROM tbl |> EXCEPT DISTINCT BY NAME (SELECT * FROM admins)"); dialects.verified_stmt( - "SELECT * FROM users |> EXCEPT DISTINCT (SELECT * FROM admins), (SELECT * FROM guests)", + "SELECT * FROM tbl |> EXCEPT DISTINCT (SELECT * FROM admins), (SELECT * FROM guests)", ); + dialects.verified_stmt("SELECT * FROM tbl |> EXCEPT DISTINCT BY NAME (SELECT * FROM admins), (SELECT * FROM guests)"); +} - // except pipe operator with BY NAME and multiple queries - dialects.verified_stmt("SELECT * FROM users |> EXCEPT DISTINCT BY NAME (SELECT * FROM admins), (SELECT * FROM guests)"); - - // call pipe operator - dialects.verified_stmt("SELECT * FROM users |> CALL my_function()"); - dialects.verified_stmt("SELECT * FROM users |> CALL process_data(5, 'test')"); +#[test] +fn parse_pipe_operator_call() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> CALL my_function()"); + dialects.verified_stmt("SELECT * FROM tbl |> CALL process_data(5, 'test')"); + dialects + .verified_stmt("SELECT * FROM tbl |> CALL namespace.function_name(col1, col2, 'literal')"); + dialects.verified_stmt("SELECT * FROM tbl |> CALL transform_data(col1 + col2)"); + dialects.verified_stmt("SELECT * FROM tbl |> CALL analyze_data('param1', 100, true)"); + dialects.verified_stmt("SELECT * FROM tbl |> CALL tvf1(arg1) AS al"); + dialects.verified_stmt("SELECT * FROM tbl |> CALL process_data(5) AS result_table"); + dialects.verified_stmt("SELECT * FROM tbl |> CALL namespace.func() AS my_alias"); + dialects.verified_stmt("SELECT * FROM tbl |> CALL tvf1(arg1) |> CALL tvf2(arg2, arg3)"); dialects.verified_stmt( - "SELECT * FROM users |> CALL namespace.function_name(col1, col2, 'literal')", + "SELECT * FROM tbl |> CALL transform(col1) |> CALL validate() |> CALL process(param)", ); - - // call pipe operator with complex arguments - dialects.verified_stmt("SELECT * FROM users |> CALL transform_data(col1 + col2)"); - dialects.verified_stmt("SELECT * FROM users |> CALL analyze_data('param1', 100, true)"); - - // call pipe operator with aliases - dialects.verified_stmt("SELECT * FROM input_table |> CALL tvf1(arg1) AS al"); - dialects.verified_stmt("SELECT * FROM users |> CALL process_data(5) AS result_table"); - dialects.verified_stmt("SELECT * FROM users |> CALL namespace.func() AS my_alias"); - - // multiple call pipe operators in sequence - dialects.verified_stmt("SELECT * FROM input_table |> CALL tvf1(arg1) |> CALL tvf2(arg2, arg3)"); + dialects + .verified_stmt("SELECT * FROM tbl |> CALL tvf1(arg1) AS step1 |> CALL tvf2(arg2) AS step2"); dialects.verified_stmt( - "SELECT * FROM data |> CALL transform(col1) |> CALL validate() |> CALL process(param)", + "SELECT * FROM tbl |> CALL preprocess() AS clean_data |> CALL analyze(mode) AS results", ); - - // multiple call pipe operators with aliases dialects.verified_stmt( - "SELECT * FROM input_table |> CALL tvf1(arg1) AS step1 |> CALL tvf2(arg2) AS step2", + "SELECT * FROM tbl |> CALL transform() |> WHERE status = 'active' |> CALL process(param)", ); dialects.verified_stmt( - "SELECT * FROM data |> CALL preprocess() AS clean_data |> CALL analyze(mode) AS results", + "SELECT * FROM tbl |> CALL preprocess() AS clean |> SELECT col1, col2 |> CALL validate()", ); +} - // call pipe operators mixed with other pipe operators +#[test] +fn parse_pipe_operator_pivot() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); dialects.verified_stmt( - "SELECT * FROM users |> CALL transform() |> WHERE status = 'active' |> CALL process(param)", + "SELECT * FROM tbl |> PIVOT(SUM(amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))", ); dialects.verified_stmt( - "SELECT * FROM data |> CALL preprocess() AS clean |> SELECT col1, col2 |> CALL validate()", + "SELECT * FROM tbl |> PIVOT(AVG(revenue) FOR region IN ('North', 'South', 'East', 'West'))", ); - - // pivot pipe operator + dialects.verified_stmt("SELECT * FROM tbl |> PIVOT(SUM(sales) AS total_sales, COUNT(*) AS num_transactions FOR month IN ('Jan', 'Feb', 'Mar'))"); + dialects.verified_stmt("SELECT * FROM tbl |> PIVOT(SUM(amount) FOR product.category IN ('Electronics', 'Clothing'))"); + dialects.verified_stmt("SELECT * FROM tbl |> WHERE year = 2023 |> PIVOT(SUM(revenue) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))"); dialects.verified_stmt( - "SELECT * FROM monthly_sales |> PIVOT(SUM(amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))", + "SELECT * FROM tbl |> PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2')) AS quarterly_sales", ); - dialects.verified_stmt("SELECT * FROM sales_data |> PIVOT(AVG(revenue) FOR region IN ('North', 'South', 'East', 'West'))"); - - // pivot pipe operator with multiple aggregate functions - dialects.verified_stmt("SELECT * FROM data |> PIVOT(SUM(sales) AS total_sales, COUNT(*) AS num_transactions FOR month IN ('Jan', 'Feb', 'Mar'))"); - - // pivot pipe operator with compound column names - dialects.verified_stmt("SELECT * FROM sales |> PIVOT(SUM(amount) FOR product.category IN ('Electronics', 'Clothing'))"); - - // pivot pipe operator mixed with other pipe operators - dialects.verified_stmt("SELECT * FROM sales_data |> WHERE year = 2023 |> PIVOT(SUM(revenue) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))"); - - // pivot pipe operator with aliases - dialects.verified_stmt("SELECT * FROM monthly_sales |> PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2')) AS quarterly_sales"); - dialects.verified_stmt("SELECT * FROM data |> PIVOT(AVG(price) FOR category IN ('A', 'B', 'C')) AS avg_by_category"); - dialects.verified_stmt("SELECT * FROM sales |> PIVOT(COUNT(*) AS transactions, SUM(amount) AS total FOR region IN ('North', 'South')) AS regional_summary"); - - // pivot pipe operator with implicit aliases (without AS keyword) + dialects.verified_stmt( + "SELECT * FROM tbl |> PIVOT(AVG(price) FOR category IN ('A', 'B', 'C')) AS avg_by_category", + ); + dialects.verified_stmt("SELECT * FROM tbl |> PIVOT(COUNT(*) AS transactions, SUM(amount) AS total FOR region IN ('North', 'South')) AS regional_summary"); dialects.verified_query_with_canonical( - "SELECT * FROM monthly_sales |> PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2')) quarterly_sales", - "SELECT * FROM monthly_sales |> PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2')) AS quarterly_sales", + "SELECT * FROM tbl |> PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2')) quarterly_sales", + "SELECT * FROM tbl |> PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2')) AS quarterly_sales", ); dialects.verified_query_with_canonical( - "SELECT * FROM data |> PIVOT(AVG(price) FOR category IN ('A', 'B', 'C')) avg_by_category", - "SELECT * FROM data |> PIVOT(AVG(price) FOR category IN ('A', 'B', 'C')) AS avg_by_category", + "SELECT * FROM tbl |> PIVOT(AVG(price) FOR category IN ('A', 'B', 'C')) avg_by_category", + "SELECT * FROM tbl |> PIVOT(AVG(price) FOR category IN ('A', 'B', 'C')) AS avg_by_category", ); +} - // unpivot pipe operator basic usage - dialects - .verified_stmt("SELECT * FROM sales |> UNPIVOT(revenue FOR quarter IN (Q1, Q2, Q3, Q4))"); - dialects.verified_stmt("SELECT * FROM data |> UNPIVOT(value FOR category IN (A, B, C))"); +#[test] +fn parse_pipe_operator_unpivot() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> UNPIVOT(revenue FOR quarter IN (Q1, Q2, Q3, Q4))"); + dialects.verified_stmt("SELECT * FROM tbl |> UNPIVOT(value FOR category IN (A, B, C))"); dialects.verified_stmt( - "SELECT * FROM metrics |> UNPIVOT(measurement FOR metric_type IN (cpu, memory, disk))", + "SELECT * FROM tbl |> UNPIVOT(measurement FOR metric_type IN (cpu, memory, disk))", ); - - // unpivot pipe operator with multiple columns - dialects.verified_stmt("SELECT * FROM quarterly_sales |> UNPIVOT(amount FOR period IN (jan, feb, mar, apr, may, jun))"); dialects.verified_stmt( - "SELECT * FROM report |> UNPIVOT(score FOR subject IN (math, science, english, history))", + "SELECT * FROM tbl |> UNPIVOT(amount FOR period IN (jan, feb, mar, apr, may, jun))", ); - - // unpivot pipe operator mixed with other pipe operators - dialects.verified_stmt("SELECT * FROM sales_data |> WHERE year = 2023 |> UNPIVOT(revenue FOR quarter IN (Q1, Q2, Q3, Q4))"); - - // unpivot pipe operator with aliases - dialects.verified_stmt("SELECT * FROM quarterly_sales |> UNPIVOT(amount FOR period IN (Q1, Q2)) AS unpivoted_sales"); dialects.verified_stmt( - "SELECT * FROM data |> UNPIVOT(value FOR category IN (A, B, C)) AS transformed_data", - ); - dialects.verified_stmt("SELECT * FROM metrics |> UNPIVOT(measurement FOR metric_type IN (cpu, memory)) AS metric_measurements"); - - // unpivot pipe operator with implicit aliases (without AS keyword) - dialects.verified_query_with_canonical( - "SELECT * FROM quarterly_sales |> UNPIVOT(amount FOR period IN (Q1, Q2)) unpivoted_sales", - "SELECT * FROM quarterly_sales |> UNPIVOT(amount FOR period IN (Q1, Q2)) AS unpivoted_sales", - ); - dialects.verified_query_with_canonical( - "SELECT * FROM data |> UNPIVOT(value FOR category IN (A, B, C)) transformed_data", - "SELECT * FROM data |> UNPIVOT(value FOR category IN (A, B, C)) AS transformed_data", + "SELECT * FROM tbl |> UNPIVOT(score FOR subject IN (math, science, english, history))", ); - - // many pipes + dialects.verified_stmt("SELECT * FROM tbl |> WHERE year = 2023 |> UNPIVOT(revenue FOR quarter IN (Q1, Q2, Q3, Q4))"); dialects.verified_stmt( - "SELECT * FROM CustomerOrders |> AGGREGATE SUM(cost) AS total_cost GROUP BY customer_id, state, item_type |> EXTEND COUNT(*) OVER (PARTITION BY customer_id) AS num_orders |> WHERE num_orders > 1 |> AGGREGATE AVG(total_cost) AS average GROUP BY state DESC, item_type ASC", + "SELECT * FROM tbl |> UNPIVOT(amount FOR period IN (Q1, Q2)) AS unpivoted_sales", ); - - // join pipe operator - INNER JOIN - dialects.verified_stmt("SELECT * FROM users |> JOIN orders ON users.id = orders.user_id"); - dialects.verified_stmt("SELECT * FROM users |> INNER JOIN orders ON users.id = orders.user_id"); - - // join pipe operator - LEFT JOIN - dialects.verified_stmt("SELECT * FROM users |> LEFT JOIN orders ON users.id = orders.user_id"); dialects.verified_stmt( - "SELECT * FROM users |> LEFT OUTER JOIN orders ON users.id = orders.user_id", + "SELECT * FROM tbl |> UNPIVOT(value FOR category IN (A, B, C)) AS transformed_data", ); - - // join pipe operator - RIGHT JOIN - dialects.verified_stmt("SELECT * FROM users |> RIGHT JOIN orders ON users.id = orders.user_id"); - dialects.verified_stmt( - "SELECT * FROM users |> RIGHT OUTER JOIN orders ON users.id = orders.user_id", + dialects.verified_stmt("SELECT * FROM tbl |> UNPIVOT(measurement FOR metric_type IN (cpu, memory)) AS metric_measurements"); + dialects.verified_query_with_canonical( + "SELECT * FROM tbl |> UNPIVOT(amount FOR period IN (Q1, Q2)) unpivoted_sales", + "SELECT * FROM tbl |> UNPIVOT(amount FOR period IN (Q1, Q2)) AS unpivoted_sales", ); - - // join pipe operator - FULL JOIN - dialects.verified_stmt("SELECT * FROM users |> FULL JOIN orders ON users.id = orders.user_id"); dialects.verified_query_with_canonical( - "SELECT * FROM users |> FULL OUTER JOIN orders ON users.id = orders.user_id", - "SELECT * FROM users |> FULL JOIN orders ON users.id = orders.user_id", + "SELECT * FROM tbl |> UNPIVOT(value FOR category IN (A, B, C)) transformed_data", + "SELECT * FROM tbl |> UNPIVOT(value FOR category IN (A, B, C)) AS transformed_data", ); +} - // join pipe operator - CROSS JOIN - dialects.verified_stmt("SELECT * FROM users |> CROSS JOIN orders"); - - // join pipe operator with USING +#[test] +fn parse_pipe_operator_join() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> JOIN orders ON users.id = orders.user_id"); + dialects.verified_stmt("SELECT * FROM tbl |> INNER JOIN orders ON users.id = orders.user_id"); + dialects.verified_stmt("SELECT * FROM tbl |> LEFT JOIN orders ON users.id = orders.user_id"); + dialects + .verified_stmt("SELECT * FROM tbl |> LEFT OUTER JOIN orders ON users.id = orders.user_id"); + dialects.verified_stmt("SELECT * FROM tbl |> RIGHT JOIN orders ON users.id = orders.user_id"); + dialects + .verified_stmt("SELECT * FROM tbl |> RIGHT OUTER JOIN orders ON users.id = orders.user_id"); + dialects.verified_stmt("SELECT * FROM tbl |> FULL JOIN orders ON users.id = orders.user_id"); dialects.verified_query_with_canonical( - "SELECT * FROM users |> JOIN orders USING (user_id)", - "SELECT * FROM users |> JOIN orders USING(user_id)", + "SELECT * FROM tbl |> FULL OUTER JOIN orders ON users.id = orders.user_id", + "SELECT * FROM tbl |> FULL JOIN orders ON users.id = orders.user_id", ); + dialects.verified_stmt("SELECT * FROM tbl |> CROSS JOIN orders"); dialects.verified_query_with_canonical( - "SELECT * FROM users |> LEFT JOIN orders USING (user_id, order_date)", - "SELECT * FROM users |> LEFT JOIN orders USING(user_id, order_date)", + "SELECT * FROM tbl |> JOIN orders USING (user_id)", + "SELECT * FROM tbl |> JOIN orders USING(user_id)", ); - - // join pipe operator with alias (with an omitted "AS" keyword) dialects.verified_query_with_canonical( - "SELECT * FROM users |> JOIN orders o ON users.id = o.user_id", - "SELECT * FROM users |> JOIN orders o ON users.id = o.user_id", + "SELECT * FROM tbl |> LEFT JOIN orders USING (user_id, order_date)", + "SELECT * FROM tbl |> LEFT JOIN orders USING(user_id, order_date)", ); - dialects.verified_stmt("SELECT * FROM users |> LEFT JOIN orders AS o ON users.id = o.user_id"); - - // join pipe operator with complex ON condition - dialects.verified_stmt("SELECT * FROM users |> JOIN orders ON users.id = orders.user_id AND orders.status = 'active'"); - dialects.verified_stmt("SELECT * FROM users |> LEFT JOIN orders ON users.id = orders.user_id AND orders.amount > 100"); - - // multiple join pipe operators - dialects.verified_stmt("SELECT * FROM users |> JOIN orders ON users.id = orders.user_id |> JOIN products ON orders.product_id = products.id"); - dialects.verified_stmt("SELECT * FROM users |> LEFT JOIN orders ON users.id = orders.user_id |> RIGHT JOIN products ON orders.product_id = products.id"); + dialects.verified_stmt("SELECT * FROM tbl |> JOIN orders o ON users.id = o.user_id"); + dialects.verified_stmt("SELECT * FROM tbl |> LEFT JOIN orders AS o ON users.id = o.user_id"); + dialects.verified_stmt("SELECT * FROM tbl |> JOIN orders ON users.id = orders.user_id AND orders.status = 'active'"); + dialects.verified_stmt("SELECT * FROM tbl |> LEFT JOIN orders ON users.id = orders.user_id AND orders.amount > 100"); + dialects.verified_stmt("SELECT * FROM tbl |> JOIN orders ON users.id = orders.user_id |> JOIN products ON orders.product_id = products.id"); + dialects.verified_stmt("SELECT * FROM tbl |> LEFT JOIN orders ON users.id = orders.user_id |> RIGHT JOIN products ON orders.product_id = products.id"); + dialects.verified_stmt("SELECT * FROM tbl |> JOIN orders ON users.id = orders.user_id |> WHERE orders.amount > 100"); + dialects.verified_stmt("SELECT * FROM tbl |> WHERE users.active = true |> LEFT JOIN orders ON users.id = orders.user_id"); + dialects.verified_stmt("SELECT * FROM tbl |> JOIN orders ON users.id = orders.user_id |> SELECT users.name, orders.amount"); +} - // join pipe operator with other pipe operators - dialects.verified_stmt("SELECT * FROM users |> JOIN orders ON users.id = orders.user_id |> WHERE orders.amount > 100"); - dialects.verified_stmt("SELECT * FROM users |> WHERE users.active = true |> LEFT JOIN orders ON users.id = orders.user_id"); - dialects.verified_stmt("SELECT * FROM users |> JOIN orders ON users.id = orders.user_id |> SELECT users.name, orders.amount"); +#[test] +fn parse_pipe_operator_chained() { + let dialects = all_dialects_where(|d| d.supports_pipe_operator()); + dialects.verified_stmt("SELECT * FROM tbl |> AGGREGATE SUM(cost) AS total_cost GROUP BY customer_id, state, item_type |> EXTEND COUNT(*) OVER (PARTITION BY customer_id) AS num_orders |> WHERE num_orders > 1 |> AGGREGATE AVG(total_cost) AS average GROUP BY state DESC, item_type ASC"); } #[test] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
