summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-03-03 14:11:03 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-03-03 14:11:03 +0000
commit5d0ea770947ae1da51537ff75b14b48218d729aa (patch)
tree86e66fc69feeae4c4b23749e7779f6731791edcc /tests/fixtures/optimizer
parentAdding upstream version 21.1.2. (diff)
downloadsqlglot-5d0ea770947ae1da51537ff75b14b48218d729aa.tar.xz
sqlglot-5d0ea770947ae1da51537ff75b14b48218d729aa.zip
Adding upstream version 22.2.0.upstream/22.2.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/optimizer.sql2
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql12
-rw-r--r--tests/fixtures/optimizer/qualify_tables.sql23
-rw-r--r--tests/fixtures/optimizer/quote_identifiers.sql4
-rw-r--r--tests/fixtures/optimizer/tpc-ds/call_center.csv.gzbin421 -> 425 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gzbin463753 -> 460883 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gzbin157676 -> 158215 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gzbin1803802 -> 1814673 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/customer.csv.gzbin107615 -> 107573 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/customer_address.csv.gzbin28336 -> 28719 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gzbin126457 -> 126715 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/date_dim.csv.gzbin1531293 -> 1575448 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gzbin23425 -> 23544 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/income_band.csv.gzbin188 -> 191 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/inventory.csv.gzbin206882 -> 202661 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/item.csv.gzbin31392 -> 31336 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/promotion.csv.gzbin497 -> 501 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/reason.csv.gzbin81 -> 83 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gzbin617 -> 633 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/store.csv.gzbin396 -> 397 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/store_returns.csv.gzbin254858 -> 255650 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/store_sales.csv.gzbin2417178 -> 2436694 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/time_dim.csv.gzbin668972 -> 680588 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql25
-rw-r--r--tests/fixtures/optimizer/tpc-ds/warehouse.csv.gzbin218 -> 221 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/web_page.csv.gzbin208 -> 212 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/web_returns.csv.gzbin67542 -> 67833 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/web_sales.csv.gzbin864379 -> 867887 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/web_site.csv.gzbin404 -> 406 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-h/tpc-h.sql1
-rw-r--r--tests/fixtures/optimizer/unnest_subqueries.sql16
31 files changed, 79 insertions, 4 deletions
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index a33c81b..990453b 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -820,7 +820,7 @@ 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`
+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)
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index df8c1a5..71c6f45 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -172,6 +172,10 @@ SELECT _q_0._col_0 AS _col_0, _q_0._col_1 AS _col_1 FROM (VALUES (1, 2)) AS _q_0
select * from (values (1, 2)) x;
SELECT x._col_0 AS _col_0, x._col_1 AS _col_1 FROM (VALUES (1, 2)) AS x(_col_0, _col_1);
+# execute: false
+SELECT SOME_UDF(data).* FROM t;
+SELECT SOME_UDF(t.data).* FROM t AS t;
+
--------------------------------------
-- Derived tables
--------------------------------------
@@ -333,6 +337,10 @@ WITH cte AS (SELECT 1 AS x) SELECT cte.a AS a FROM cte AS cte(a);
WITH cte(x, y) AS (SELECT 1, 2) SELECT cte.* FROM cte AS cte(a);
WITH cte AS (SELECT 1 AS x, 2 AS y) SELECT cte.a AS a, cte.y AS y FROM cte AS cte(a);
+-- Cannot pop table column aliases for recursive ctes (redshift).
+WITH RECURSIVE cte(x) AS (SELECT 1), cte2(y) AS (SELECT 2) SELECT * FROM cte, cte2;
+WITH RECURSIVE cte(x) AS (SELECT 1 AS x), cte2(y) AS (SELECT 2 AS y) SELECT cte.x AS x, cte2.y AS y FROM cte AS cte, cte2 AS cte2;
+
# execute: false
WITH player AS (SELECT player.name, player.asset.info FROM players) SELECT * FROM player;
WITH player AS (SELECT players.player.name AS name, players.player.asset.info AS info FROM players AS players) SELECT player.name AS name, player.info AS info FROM player AS player;
@@ -549,6 +557,10 @@ SELECT x.a + x.b AS f, (x.a + x.b) * x.b AS _col_1 FROM x AS x;
SELECT x.a + x.b AS f, f, f + 5 FROM x;
SELECT x.a + x.b AS f, x.a + x.b AS _col_1, x.a + x.b + 5 AS _col_2 FROM x AS x;
+# title: expand double agg if window func
+SELECT a, SUM(b) AS c, SUM(c) OVER(PARTITION BY a) AS d from x group by 1 ORDER BY a;
+SELECT x.a AS a, SUM(x.b) AS c, SUM(SUM(x.b)) OVER (PARTITION BY x.a) AS d FROM x AS x GROUP BY x.a ORDER BY a;
+
--------------------------------------
-- Wrapped tables / join constructs
--------------------------------------
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
index 1426aa7..99b5153 100644
--- a/tests/fixtures/optimizer/qualify_tables.sql
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -19,6 +19,21 @@ SELECT 1 FROM x.y.z AS z;
SELECT 1 FROM y.z AS z, z.a;
SELECT 1 FROM c.y.z AS z, z.a;
+# title: bigquery implicit unnest syntax, coordinates.position should be a column, not a table
+# dialect: bigquery
+SELECT results FROM Coordinates, coordinates.position AS results;
+SELECT results FROM c.db.Coordinates AS Coordinates, UNNEST(coordinates.position) AS results;
+
+# title: bigquery implicit unnest syntax, table is already qualified
+# dialect: bigquery
+SELECT results FROM db.coordinates, Coordinates.position AS results;
+SELECT results FROM c.db.coordinates AS coordinates, UNNEST(Coordinates.position) AS results;
+
+# title: bigquery schema name clashes with CTE name - this is a join, not an implicit unnest
+# dialect: bigquery
+WITH Coordinates AS (SELECT [1, 2] AS position) SELECT results FROM Coordinates, `Coordinates.position` AS results;
+WITH Coordinates AS (SELECT [1, 2] AS position) SELECT results FROM Coordinates AS Coordinates, `c.Coordinates.position` AS results;
+
# title: single cte
WITH a AS (SELECT 1 FROM z) SELECT 1 FROM a;
WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a AS a;
@@ -83,7 +98,7 @@ SELECT * FROM ((c.db.a AS foo CROSS JOIN c.db.b AS bar) CROSS JOIN c.db.c AS baz
SELECT * FROM (tbl1 CROSS JOIN (SELECT * FROM tbl2) AS t1);
SELECT * FROM (c.db.tbl1 AS tbl1 CROSS JOIN (SELECT * FROM c.db.tbl2 AS tbl2) AS t1);
-# title: wrapped join with subquery with alias, parentheses can't be omitted because of alias
+# title: wrapped join with subquery with alias, parentheses cant be omitted because of alias
SELECT * FROM (tbl1 CROSS JOIN (SELECT * FROM tbl2) AS t1) AS t2;
SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 CROSS JOIN (SELECT * FROM c.db.tbl2 AS tbl2) AS t1) AS t2;
@@ -95,7 +110,7 @@ SELECT * FROM c.db.a AS a LEFT JOIN (c.db.b AS b INNER JOIN c.db.c AS c ON c.id
SELECT * FROM a LEFT JOIN b INNER JOIN c ON c.id = b.id ON b.id = a.id;
SELECT * FROM c.db.a AS a LEFT JOIN c.db.b AS b INNER JOIN c.db.c AS c ON c.id = b.id ON b.id = a.id;
-# title: parentheses can't be omitted because alias shadows inner table names
+# title: parentheses cant be omitted because alias shadows inner table names
SELECT t.a FROM (tbl AS tbl) AS t;
SELECT t.a FROM (SELECT * FROM c.db.tbl AS tbl) AS t;
@@ -146,3 +161,7 @@ CREATE TABLE c.db.t1 AS (WITH cte AS (SELECT x FROM c.db.t2 AS t2) SELECT * FROM
# title: insert statement with cte
WITH cte AS (SELECT b FROM y) INSERT INTO s SELECT * FROM cte;
WITH cte AS (SELECT b FROM c.db.y AS y) INSERT INTO c.db.s SELECT * FROM cte AS cte;
+
+# title: qualify wrapped query
+(SELECT x FROM t);
+(SELECT x FROM c.db.t AS t);
diff --git a/tests/fixtures/optimizer/quote_identifiers.sql b/tests/fixtures/optimizer/quote_identifiers.sql
index 21181f7..34500c4 100644
--- a/tests/fixtures/optimizer/quote_identifiers.sql
+++ b/tests/fixtures/optimizer/quote_identifiers.sql
@@ -29,3 +29,7 @@ SELECT "dual" FROM "t";
# dialect: snowflake
SELECT * FROM t AS dual;
SELECT * FROM "t" AS "dual";
+
+# dialect: bigquery
+SELECT `p.d.udf`(data).* FROM `p.d.t`;
+SELECT `p.d.udf`(`data`).* FROM `p.d.t`;
diff --git a/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz b/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz
index f36e23b..ad5043f 100644
--- a/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz b/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz
index 702242c..eed1508 100644
--- a/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz b/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz
index e87a0ec..e160514 100644
--- a/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz b/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz
index a40b0da..1828149 100644
--- a/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/customer.csv.gz b/tests/fixtures/optimizer/tpc-ds/customer.csv.gz
index f4af4f7..2277f72 100644
--- a/tests/fixtures/optimizer/tpc-ds/customer.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/customer.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz b/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz
index 8698e39..c553721 100644
--- a/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz b/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz
index c4b7b68..dfc65a0 100644
--- a/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz b/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz
index 35be08f..26280bf 100644
--- a/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz b/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz
index b8addb7..f0cde03 100644
--- a/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz b/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz
index d34d870..4374587 100644
--- a/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz b/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz
index c6f0d47..5afaaf6 100644
--- a/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/item.csv.gz b/tests/fixtures/optimizer/tpc-ds/item.csv.gz
index 4a316cd..9f65d87 100644
--- a/tests/fixtures/optimizer/tpc-ds/item.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/item.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz b/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz
index 339666c..e8692c2 100644
--- a/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/reason.csv.gz b/tests/fixtures/optimizer/tpc-ds/reason.csv.gz
index 0094849..de1f50f 100644
--- a/tests/fixtures/optimizer/tpc-ds/reason.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/reason.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz b/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz
index 8dec386..14465e8 100644
--- a/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/store.csv.gz b/tests/fixtures/optimizer/tpc-ds/store.csv.gz
index b4e8de0..8d04078 100644
--- a/tests/fixtures/optimizer/tpc-ds/store.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/store.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz b/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz
index 8469492..cba1300 100644
--- a/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz b/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz
index 3dd22e1..68caa83 100644
--- a/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz b/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz
index bf4fcaf..3e0fa35 100644
--- a/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index 5ea51e0..76e6431 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -1,6 +1,7 @@
--------------------------------------
-- TPC-DS 1
--------------------------------------
+# execute: true
WITH customer_total_return
AS (SELECT sr_customer_sk AS ctr_customer_sk,
sr_store_sk AS ctr_store_sk,
@@ -219,6 +220,7 @@ ORDER BY
--------------------------------------
-- TPC-DS 3
--------------------------------------
+# execute: true
SELECT dt.d_year,
item.i_brand_id brand_id,
item.i_brand brand,
@@ -859,6 +861,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 6
--------------------------------------
+# execute: true
SELECT a.ca_state state,
Count(*) cnt
FROM customer_address a,
@@ -924,6 +927,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 7
--------------------------------------
+# execute: true
SELECT i_item_id,
Avg(ss_quantity) agg1,
Avg(ss_list_price) agg2,
@@ -1247,6 +1251,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 9
--------------------------------------
+# execute: true
SELECT CASE
WHEN (SELECT Count(*)
FROM store_sales
@@ -1448,6 +1453,7 @@ WHERE
--------------------------------------
-- TPC-DS 10
--------------------------------------
+# execute: true
SELECT cd_gender,
cd_marital_status,
cd_education_status,
@@ -3056,6 +3062,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 24
--------------------------------------
+# execute: true
WITH ssales
AS (SELECT c_last_name,
c_first_name,
@@ -3158,6 +3165,7 @@ HAVING
--------------------------------------
-- TPC-DS 25
--------------------------------------
+# execute: true
SELECT i_item_id,
i_item_desc,
s_store_id,
@@ -3247,6 +3255,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 26
--------------------------------------
+# execute: true
SELECT i_item_id,
Avg(cs_quantity) agg1,
Avg(cs_list_price) agg2,
@@ -3527,6 +3536,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 29
--------------------------------------
+# execute: true
SELECT i_item_id,
i_item_desc,
s_store_id,
@@ -3726,6 +3736,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 31
--------------------------------------
+# execute: true
WITH ss
AS (SELECT ca_county,
d_qoy,
@@ -3948,6 +3959,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 33
--------------------------------------
+# execute: true
WITH ss
AS (SELECT i_manufact_id,
Sum(ss_ext_sales_price) total_sales
@@ -5014,6 +5026,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 43
--------------------------------------
+# execute: true
SELECT s_store_name,
s_store_id,
Sum(CASE
@@ -6194,6 +6207,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 52
--------------------------------------
+# execute: true
SELECT dt.d_year,
item.i_brand_id brand_id,
item.i_brand brand,
@@ -6357,6 +6371,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 54
--------------------------------------
+# execute: true
WITH my_customers
AS (SELECT DISTINCT c_customer_sk,
c_current_addr_sk
@@ -6493,6 +6508,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 55
--------------------------------------
+# execute: true
SELECT i_brand_id brand_id,
i_brand brand,
Sum(ss_ext_sales_price) ext_price
@@ -6531,6 +6547,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 56
--------------------------------------
+# execute: true
WITH ss
AS (SELECT i_item_id,
Sum(ss_ext_sales_price) total_sales
@@ -7231,6 +7248,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 60
--------------------------------------
+# execute: true
WITH ss
AS (SELECT i_item_id,
Sum(ss_ext_sales_price) total_sales
@@ -8012,6 +8030,7 @@ ORDER BY
--------------------------------------
-- TPC-DS 65
--------------------------------------
+# execute: true
SELECT s_store_name,
i_item_desc,
sc.revenue,
@@ -9113,6 +9132,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 69
--------------------------------------
+# execute: true
SELECT cd_gender,
cd_marital_status,
cd_education_status,
@@ -9355,6 +9375,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 71
--------------------------------------
+# execute: true
SELECT i_brand_id brand_id,
i_brand brand,
t_hour,
@@ -11064,6 +11085,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 83
--------------------------------------
+# execute: true
WITH sr_items
AS (SELECT i_item_id item_id,
Sum(sr_return_quantity) sr_item_qty
@@ -11262,6 +11284,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 84
--------------------------------------
+# execute: true
SELECT c_customer_id AS customer_id,
c_last_name
|| ', '
@@ -11563,6 +11586,7 @@ FROM "cool_cust" AS "cool_cust";
--------------------------------------
-- TPC-DS 88
--------------------------------------
+# execute: true
select *
from
(select count(*) h8_30_to_9
@@ -12140,6 +12164,7 @@ LIMIT 100;
--------------------------------------
-- TPC-DS 93
--------------------------------------
+# execute: true
SELECT ss_customer_sk,
Sum(act_sales) sumsales
FROM (SELECT ss_item_sk,
diff --git a/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz b/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz
index 1dd95a0..cf64636 100644
--- a/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz b/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz
index 10a06a2..894ce3b 100644
--- a/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz b/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz
index 811e079..21f7040 100644
--- a/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz b/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz
index b1ac3b8..b384c78 100644
--- a/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz b/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz
index ccedce2..b9b5f72 100644
--- a/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz
+++ b/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index a99abcd..39b5ffa 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -1047,7 +1047,6 @@ WITH "_u_0" AS (
"lineitem"."l_orderkey" AS "l_orderkey"
FROM "lineitem" AS "lineitem"
GROUP BY
- "lineitem"."l_orderkey",
"lineitem"."l_orderkey"
HAVING
SUM("lineitem"."l_quantity") > 300
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index 3caeef6..45e462b 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -25,6 +25,7 @@ WHERE
AND x.a > ALL (SELECT y.c FROM y WHERE y.a = x.a)
AND x.a > (SELECT COUNT(*) as d FROM y WHERE y.a = x.a)
AND x.a = SUM(SELECT 1) -- invalid statement left alone
+ AND x.a IN (SELECT max(y.b) AS b FROM y GROUP BY y.a)
;
SELECT
*
@@ -155,6 +156,20 @@ LEFT JOIN (
y.a
) AS _u_21
ON _u_21._u_22 = x.a
+LEFT JOIN (
+ SELECT
+ _q.b
+ FROM (
+ SELECT
+ MAX(y.b) AS b
+ FROM y
+ GROUP BY
+ y.a
+ ) AS _q
+ GROUP BY
+ _q.b
+) AS _u_24
+ ON x.a = _u_24.b
WHERE
x.a = _u_0.a
AND NOT _u_1.a IS NULL
@@ -212,6 +227,7 @@ WHERE
AND x.a > COALESCE(_u_21.d, 0)
AND x.a = SUM(SELECT
1) /* invalid statement left alone */
+ AND NOT _u_24.b IS NULL
;
SELECT
CAST((