From 42a1548cecf48d18233f56e3385cf9c89abcb9c2 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 3 Mar 2024 15:11:07 +0100 Subject: Merging upstream version 22.2.0. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/optimizer.sql | 2 +- tests/fixtures/optimizer/qualify_columns.sql | 12 ++++++++++ tests/fixtures/optimizer/qualify_tables.sql | 23 +++++++++++++++++-- tests/fixtures/optimizer/quote_identifiers.sql | 4 ++++ tests/fixtures/optimizer/tpc-ds/call_center.csv.gz | Bin 421 -> 425 bytes .../fixtures/optimizer/tpc-ds/catalog_page.csv.gz | Bin 463753 -> 460883 bytes .../optimizer/tpc-ds/catalog_returns.csv.gz | Bin 157676 -> 158215 bytes .../fixtures/optimizer/tpc-ds/catalog_sales.csv.gz | Bin 1803802 -> 1814673 bytes tests/fixtures/optimizer/tpc-ds/customer.csv.gz | Bin 107615 -> 107573 bytes .../optimizer/tpc-ds/customer_address.csv.gz | Bin 28336 -> 28719 bytes .../optimizer/tpc-ds/customer_demographics.csv.gz | Bin 126457 -> 126715 bytes tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz | Bin 1531293 -> 1575448 bytes .../optimizer/tpc-ds/household_demographics.csv.gz | Bin 23425 -> 23544 bytes tests/fixtures/optimizer/tpc-ds/income_band.csv.gz | Bin 188 -> 191 bytes tests/fixtures/optimizer/tpc-ds/inventory.csv.gz | Bin 206882 -> 202661 bytes tests/fixtures/optimizer/tpc-ds/item.csv.gz | Bin 31392 -> 31336 bytes tests/fixtures/optimizer/tpc-ds/promotion.csv.gz | Bin 497 -> 501 bytes tests/fixtures/optimizer/tpc-ds/reason.csv.gz | Bin 81 -> 83 bytes tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz | Bin 617 -> 633 bytes tests/fixtures/optimizer/tpc-ds/store.csv.gz | Bin 396 -> 397 bytes .../fixtures/optimizer/tpc-ds/store_returns.csv.gz | Bin 254858 -> 255650 bytes tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz | Bin 2417178 -> 2436694 bytes tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz | Bin 668972 -> 680588 bytes tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 25 +++++++++++++++++++++ tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz | Bin 218 -> 221 bytes tests/fixtures/optimizer/tpc-ds/web_page.csv.gz | Bin 208 -> 212 bytes tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz | Bin 67542 -> 67833 bytes tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz | Bin 864379 -> 867887 bytes tests/fixtures/optimizer/tpc-ds/web_site.csv.gz | Bin 404 -> 406 bytes tests/fixtures/optimizer/tpc-h/tpc-h.sql | 1 - tests/fixtures/optimizer/unnest_subqueries.sql | 16 +++++++++++++ 31 files changed, 79 insertions(+), 4 deletions(-) (limited to 'tests/fixtures/optimizer') 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 Binary files a/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz and b/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz and b/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz and b/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz and b/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/customer.csv.gz and b/tests/fixtures/optimizer/tpc-ds/customer.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz and b/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz and b/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz and b/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz and b/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz and b/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz and b/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/item.csv.gz and b/tests/fixtures/optimizer/tpc-ds/item.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz and b/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/reason.csv.gz and b/tests/fixtures/optimizer/tpc-ds/reason.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz and b/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/store.csv.gz and b/tests/fixtures/optimizer/tpc-ds/store.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz and b/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz and b/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz and b/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz and b/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz and b/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz and b/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz and b/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz 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 Binary files a/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz and b/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz 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(( -- cgit v1.2.3