summaryrefslogtreecommitdiffstats
path: root/tests/fixtures
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-03-03 14:11:07 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-03-03 14:11:07 +0000
commit42a1548cecf48d18233f56e3385cf9c89abcb9c2 (patch)
tree5e0fff4ecbd1fd7dd1022a7580139038df2a824c /tests/fixtures
parentReleasing debian version 21.1.2-1. (diff)
downloadsqlglot-42a1548cecf48d18233f56e3385cf9c89abcb9c2.tar.xz
sqlglot-42a1548cecf48d18233f56e3385cf9c89abcb9c2.zip
Merging upstream version 22.2.0.
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures')
-rw-r--r--tests/fixtures/identity.sql15
-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
32 files changed, 90 insertions, 8 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index d9efc57..6d3bb07 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -196,10 +196,10 @@ SET LOCAL variable = value
@"x"
COMMIT
USE db
-USE role x
-USE warehouse x
-USE database x
-USE schema x.y
+USE ROLE x
+USE WAREHOUSE x
+USE DATABASE x
+USE SCHEMA x.y
NOT 1
NOT NOT 1
SELECT * FROM test
@@ -643,6 +643,7 @@ DROP MATERIALIZED VIEW x.y.z
CACHE TABLE x
CACHE LAZY TABLE x
CACHE LAZY TABLE x OPTIONS('storageLevel' = 'value')
+CACHE LAZY TABLE x OPTIONS(N'storageLevel' = 'value')
CACHE LAZY TABLE x OPTIONS('storageLevel' = 'value') AS SELECT 1
CACHE LAZY TABLE x OPTIONS('storageLevel' = 'value') AS WITH a AS (SELECT 1) SELECT a.* FROM a
CACHE LAZY TABLE x AS WITH a AS (SELECT 1) SELECT a.* FROM a
@@ -708,6 +709,7 @@ COMMENT ON COLUMN my_schema.my_table.my_column IS 'Employee ID number'
COMMENT ON DATABASE my_database IS 'Development Database'
COMMENT ON PROCEDURE my_proc(integer, integer) IS 'Runs a report'
COMMENT ON TABLE my_schema.my_table IS 'Employee Information'
+COMMENT ON TABLE my_schema.my_table IS N'National String'
WITH a AS (SELECT 1) INSERT INTO b SELECT * FROM a
WITH a AS (SELECT * FROM b) UPDATE a SET col = 1
WITH a AS (SELECT * FROM b) CREATE TABLE b AS SELECT * FROM a
@@ -785,6 +787,7 @@ ALTER TABLE baa ADD CONSTRAINT boo PRIMARY KEY (x, y) NOT ENFORCED DEFERRABLE IN
ALTER TABLE baa ADD CONSTRAINT boo FOREIGN KEY (x, y) REFERENCES persons ON UPDATE NO ACTION ON DELETE NO ACTION MATCH FULL
ALTER TABLE a ADD PRIMARY KEY (x, y) NOT ENFORCED
ALTER TABLE a ADD FOREIGN KEY (x, y) REFERENCES bla
+ALTER TABLE s_ut ADD CONSTRAINT s_ut_uq UNIQUE hajo
SELECT partition FROM a
SELECT end FROM a
SELECT id FROM b.a AS a QUALIFY ROW_NUMBER() OVER (PARTITION BY br ORDER BY sadf DESC) = 1
@@ -850,3 +853,7 @@ CAST(foo AS BPCHAR)
values
SELECT values
SELECT values AS values FROM t WHERE values + 1 > 3
+SELECT truncate
+SELECT only
+TRUNCATE(a, b)
+SELECT enum
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((