From 9b39dac84e82bf473216939e50b8836170f01d23 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Thu, 29 Jun 2023 15:02:29 +0200 Subject: Merging upstream version 16.7.3. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/merge_subqueries.sql | 2 +- tests/fixtures/optimizer/optimizer.sql | 38 ++++++++++++++++++--------- tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 34 ++++++++++++------------ 3 files changed, 43 insertions(+), 31 deletions(-) (limited to 'tests/fixtures') diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql index 1124a79..bd56e07 100644 --- a/tests/fixtures/optimizer/merge_subqueries.sql +++ b/tests/fixtures/optimizer/merge_subqueries.sql @@ -252,7 +252,7 @@ FROM t1 GROUP BY t1.row_num ORDER BY t1.row_num; -WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, SUM(t1.a) AS total FROM t1 GROUP BY t1.row_num ORDER BY t1.row_num; +WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, SUM(t1.a) AS total FROM t1 GROUP BY t1.row_num ORDER BY row_num; # title: Test prevent merging of window if in order by func with t1 as ( diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index 214535a..f71ddde 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -577,10 +577,10 @@ FROM `u_cte` AS `u_cte` PIVOT(SUM(`u_cte`.`f`) AS `sum` FOR `u_cte`.`h` IN ('x', # dialect: snowflake SELECT * FROM u PIVOT (SUM(f) FOR h IN ('x', 'y')); SELECT - "_q_0"."G" AS "G", - "_q_0"."'x'" AS "'x'", - "_q_0"."'y'" AS "'y'" -FROM "U" AS "U" PIVOT(SUM("U"."F") FOR "U"."H" IN ('x', 'y')) AS "_q_0" + "_Q_0"."G" AS "G", + "_Q_0"."'x'" AS "'x'", + "_Q_0"."'y'" AS "'y'" +FROM "U" AS "U" PIVOT(SUM("U"."F") FOR "U"."H" IN ('x', 'y')) AS "_Q_0" ; # title: selecting all columns from a pivoted source and generating spark @@ -668,16 +668,28 @@ WHERE GROUP BY `dAy`, `top_term`, rank ORDER BY `DaY` DESC; SELECT - `TOp_TeRmS`.`refresh_date` AS `day`, - `TOp_TeRmS`.`term` AS `top_term`, - `TOp_TeRmS`.`rank` AS `rank` -FROM `bigquery-public-data`.`GooGle_tReNDs`.`TOp_TeRmS` AS `TOp_TeRmS` + `top_terms`.`refresh_date` AS `day`, + `top_terms`.`term` AS `top_term`, + `top_terms`.`rank` AS `rank` +FROM `bigquery-public-data`.`GooGle_tReNDs`.`TOp_TeRmS` AS `top_terms` WHERE - `TOp_TeRmS`.`rank` = 1 - AND CAST(`TOp_TeRmS`.`refresh_date` AS DATE) >= DATE_SUB(CURRENT_DATE, INTERVAL 2 WEEK) + `top_terms`.`rank` = 1 + AND CAST(`top_terms`.`refresh_date` AS DATE) >= DATE_SUB(CURRENT_DATE, INTERVAL 2 WEEK) GROUP BY - `TOp_TeRmS`.`refresh_date`, - `TOp_TeRmS`.`term`, - `TOp_TeRmS`.`rank` + `day`, + `top_term`, + `rank` ORDER BY `day` DESC; + + +# title: group by keys cannot be simplified +SELECT a + 1 + 1 + 1 + 1 AS b, 2 + 1 AS c FROM x GROUP BY a + 1 + 1 HAVING a + 1 + 1 + 1 + 1 > 1; +SELECT + "x"."a" + 1 + 1 + 1 + 1 AS "b", + 3 AS "c" +FROM "x" AS "x" +GROUP BY + "x"."a" + 1 + 1 +HAVING + "x"."a" + 1 + 1 + 1 + 1 > 1; diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index 7ef7a6d..bbfd47f 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -254,7 +254,7 @@ GROUP BY "item"."i_brand", "item"."i_brand_id" ORDER BY - "dt"."d_year", + "d_year", "sum_agg" DESC, "brand_id" LIMIT 100; @@ -2767,8 +2767,8 @@ GROUP BY "item"."i_manufact" ORDER BY "ext_price" DESC, - "item"."i_brand", - "item"."i_brand_id", + "brand", + "brand_id", "i_manufact_id", "i_manufact" LIMIT 100; @@ -5112,10 +5112,10 @@ GROUP BY "item"."i_category_id", "item"."i_category" ORDER BY - SUM("store_sales"."ss_ext_sales_price") DESC, - "dt"."d_year", - "item"."i_category_id", - "item"."i_category" + "_col_3" DESC, + "d_year", + "i_category_id", + "i_category" LIMIT 100; -------------------------------------- @@ -6353,7 +6353,7 @@ GROUP BY "item"."i_brand", "item"."i_brand_id" ORDER BY - "dt"."d_year", + "d_year", "ext_price" DESC, "brand_id" LIMIT 100; @@ -6648,7 +6648,7 @@ GROUP BY "item"."i_brand_id" ORDER BY "ext_price" DESC, - "item"."i_brand_id" + "brand_id" LIMIT 100; -------------------------------------- @@ -7770,7 +7770,7 @@ GROUP BY "ship_mode"."sm_type", "web_site"."web_name" ORDER BY - SUBSTR("warehouse"."w_warehouse_name", 1, 20), + "_col_0", "sm_type", "web_name" LIMIT 100; @@ -9668,7 +9668,7 @@ GROUP BY "time_dim"."t_minute" ORDER BY "ext_price" DESC, - "item"."i_brand_id"; + "brand_id"; -------------------------------------- -- TPC-DS 72 @@ -11692,10 +11692,10 @@ JOIN "customer_demographics" AS "cd1" GROUP BY "reason"."r_reason_desc" ORDER BY - SUBSTR("reason"."r_reason_desc", 1, 20), - AVG("web_sales"."ws_quantity"), - AVG("web_returns"."wr_refunded_cash"), - AVG("web_returns"."wr_fee") + "_col_0", + "_col_1", + "_col_2", + "_col_3" LIMIT 100; -------------------------------------- @@ -12364,7 +12364,7 @@ GROUP BY "customer_demographics"."cd_marital_status", "customer_demographics"."cd_education_status" ORDER BY - SUM("catalog_returns"."cr_net_loss") DESC; + "returns_loss" DESC; -------------------------------------- -- TPC-DS 92 @@ -12940,7 +12940,7 @@ GROUP BY "ship_mode"."sm_type", "call_center"."cc_name" ORDER BY - SUBSTR("warehouse"."w_warehouse_name", 1, 20), + "_col_0", "sm_type", "cc_name" LIMIT 100; -- cgit v1.2.3