diff options
Diffstat (limited to 'tests/fixtures/optimizer')
27 files changed, 85 insertions, 2 deletions
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index 8f4a551..a33c81b 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -1311,3 +1311,79 @@ LEFT JOIN "_u_0" AS "_u_0" ON "C"."EMAIL_DOMAIN" = "_u_0"."DOMAIN" WHERE NOT "_u_0"."DOMAIN" IS NULL; + +# title: decorrelate subquery and transpile ArrayAny correctly when generating spark +# execute: false +# dialect: spark +SELECT + COUNT(DISTINCT cs1.cs_order_number) AS `order count`, + SUM(cs1.cs_ext_ship_cost) AS `total shipping cost`, + SUM(cs1.cs_net_profit) AS `total net profit` +FROM catalog_sales cs1, date_dim, customer_address, call_center +WHERE + date_dim.d_date BETWEEN '2002-02-01' AND (CAST('2002-02-01' AS DATE) + INTERVAL 60 days) + AND cs1.cs_ship_date_sk = date_dim.d_date_sk + AND cs1.cs_ship_addr_sk = customer_address.ca_address_sk + AND customer_address.ca_state = 'GA' + AND cs1.cs_call_center_sk = call_center.cc_call_center_sk + AND call_center.cc_county IN ( + 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County' + ) + AND EXISTS( + SELECT * + FROM catalog_sales cs2 + WHERE cs1.cs_order_number = cs2.cs_order_number + AND cs1.cs_warehouse_sk <> cs2.cs_warehouse_sk) + AND NOT EXISTS( + SELECT * + FROM catalog_returns cr1 + WHERE cs1.cs_order_number = cr1.cr_order_number + ) + ORDER BY COUNT(DISTINCT cs1.cs_order_number + ) +LIMIT 100; +WITH `_u_0` AS ( + SELECT + `cs2`.`cs_order_number` AS `_u_1`, + COLLECT_LIST(`cs2`.`cs_warehouse_sk`) AS `_u_2` + FROM `catalog_sales` AS `cs2` + GROUP BY + `cs2`.`cs_order_number` +), `_u_3` AS ( + SELECT + `cr1`.`cr_order_number` AS `_u_4` + FROM `catalog_returns` AS `cr1` + GROUP BY + `cr1`.`cr_order_number` +) +SELECT + COUNT(DISTINCT `cs1`.`cs_order_number`) AS `order count`, + SUM(`cs1`.`cs_ext_ship_cost`) AS `total shipping cost`, + SUM(`cs1`.`cs_net_profit`) AS `total net profit` +FROM `catalog_sales` AS `cs1` +LEFT JOIN `_u_0` AS `_u_0` + ON `_u_0`.`_u_1` = `cs1`.`cs_order_number` +LEFT JOIN `_u_3` AS `_u_3` + ON `_u_3`.`_u_4` = `cs1`.`cs_order_number` +JOIN `call_center` AS `call_center` + ON `call_center`.`cc_call_center_sk` = `cs1`.`cs_call_center_sk` + AND `call_center`.`cc_county` IN ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County') +JOIN `customer_address` AS `customer_address` + ON `cs1`.`cs_ship_addr_sk` = `customer_address`.`ca_address_sk` + AND `customer_address`.`ca_state` = 'GA' +JOIN `date_dim` AS `date_dim` + ON `cs1`.`cs_ship_date_sk` = `date_dim`.`d_date_sk` + AND `date_dim`.`d_date` <= ( + CAST(CAST('2002-02-01' AS DATE) AS TIMESTAMP) + INTERVAL '60' DAYS + ) + AND `date_dim`.`d_date` >= '2002-02-01' +WHERE + `_u_3`.`_u_4` IS NULL + AND NOT `_u_0`.`_u_1` IS NULL + AND ( + SIZE(`_u_0`.`_u_2`) = 0 + OR SIZE(FILTER(`_u_0`.`_u_2`, `_x` -> `cs1`.`cs_warehouse_sk` <> `_x`)) <> 0 + ) +ORDER BY + COUNT(DISTINCT `cs1`.`cs_order_number`) +LIMIT 100; diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 4fdf33b..df8c1a5 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -355,9 +355,16 @@ SELECT * EXCEPT(a) FROM x; SELECT x.b AS b FROM x AS x; # execute: false +SELECT * EXCEPT(x.a) FROM x AS x; +SELECT x.b AS b FROM x AS x; + +# execute: false # note: this query would fail in the engine level because there are 0 selected columns SELECT * EXCEPT (a, b) FROM x; -SELECT * EXCEPT (x.a, x.b) FROM x AS x; +SELECT * EXCEPT (a, b) FROM x AS x; + +SELECT x.a, * EXCEPT (a) FROM x AS x LEFT JOIN x AS y USING (a); +SELECT x.a AS a, x.b AS b, y.b AS b FROM x AS x LEFT JOIN x AS y ON x.a = y.a; SELECT COALESCE(CAST(t1.a AS VARCHAR), '') AS a, t2.* EXCEPT (a) FROM x AS t1, x AS t2; SELECT COALESCE(CAST(t1.a AS VARCHAR), '') AS a, t2.b AS b FROM x AS t1, x AS t2; diff --git a/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz b/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz Binary files differnew file mode 100644 index 0000000..f36e23b --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/call_center.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz b/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz Binary files differnew file mode 100644 index 0000000..702242c --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz b/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz Binary files differnew file mode 100644 index 0000000..e87a0ec --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz b/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz Binary files differnew file mode 100644 index 0000000..a40b0da --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/customer.csv.gz b/tests/fixtures/optimizer/tpc-ds/customer.csv.gz Binary files differnew file mode 100644 index 0000000..f4af4f7 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/customer.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz b/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz Binary files differnew file mode 100644 index 0000000..8698e39 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/customer_address.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz b/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz Binary files differnew file mode 100644 index 0000000..c4b7b68 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz b/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz Binary files differnew file mode 100644 index 0000000..35be08f --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/date_dim.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz b/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz Binary files differnew file mode 100644 index 0000000..b8addb7 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz b/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz Binary files differnew file mode 100644 index 0000000..d34d870 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/income_band.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz b/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz Binary files differnew file mode 100644 index 0000000..c6f0d47 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/inventory.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/item.csv.gz b/tests/fixtures/optimizer/tpc-ds/item.csv.gz Binary files differnew file mode 100644 index 0000000..4a316cd --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/item.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz b/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz Binary files differnew file mode 100644 index 0000000..339666c --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/promotion.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/reason.csv.gz b/tests/fixtures/optimizer/tpc-ds/reason.csv.gz Binary files differnew file mode 100644 index 0000000..0094849 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/reason.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz b/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz Binary files differnew file mode 100644 index 0000000..8dec386 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/store.csv.gz b/tests/fixtures/optimizer/tpc-ds/store.csv.gz Binary files differnew file mode 100644 index 0000000..b4e8de0 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/store.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz b/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz Binary files differnew file mode 100644 index 0000000..8469492 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/store_returns.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz b/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz Binary files differnew file mode 100644 index 0000000..3dd22e1 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/store_sales.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz b/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz Binary files differnew file mode 100644 index 0000000..bf4fcaf --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz b/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz Binary files differnew file mode 100644 index 0000000..1dd95a0 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz b/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz Binary files differnew file mode 100644 index 0000000..10a06a2 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/web_page.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz b/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz Binary files differnew file mode 100644 index 0000000..811e079 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/web_returns.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz b/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz Binary files differnew file mode 100644 index 0000000..b1ac3b8 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/web_sales.csv.gz diff --git a/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz b/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz Binary files differnew file mode 100644 index 0000000..ccedce2 --- /dev/null +++ b/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql index 3d1e7ec..3caeef6 100644 --- a/tests/fixtures/optimizer/unnest_subqueries.sql +++ b/tests/fixtures/optimizer/unnest_subqueries.sql @@ -166,7 +166,7 @@ WHERE AND NOT x.a = _u_9.a AND ARRAY_ANY(_u_10.a, _x -> _x = x.a) AND ( - x.a < _u_12.a AND ARRAY_ANY(_u_12._u_14, "_x" -> _x <> x.d) + x.a < _u_12.a AND ARRAY_ANY(_u_12._u_14, _x -> _x <> x.d) ) AND NOT _u_15.a IS NULL AND x.a IN ( |