summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-29 13:02:26 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-06-29 13:02:26 +0000
commita299be925028c6243d34b020920dfd0135bb9574 (patch)
tree39a7a231466fd9d139047a67672f09040dd23394 /tests/fixtures/optimizer
parentAdding upstream version 16.4.2. (diff)
downloadsqlglot-a299be925028c6243d34b020920dfd0135bb9574.tar.xz
sqlglot-a299be925028c6243d34b020920dfd0135bb9574.zip
Adding upstream version 16.7.3.upstream/16.7.3
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/merge_subqueries.sql2
-rw-r--r--tests/fixtures/optimizer/optimizer.sql38
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql34
3 files changed, 43 insertions, 31 deletions
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;