summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-02-20 09:37:57 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-02-20 09:37:57 +0000
commit5c70c63284a8ff61607db1a51ac2829b74f71c1c (patch)
tree10a81ffbd8da8cae58e292848cbdd0550d08721d /tests/fixtures/optimizer
parentAdding upstream version 21.1.1. (diff)
downloadsqlglot-5c70c63284a8ff61607db1a51ac2829b74f71c1c.tar.xz
sqlglot-5c70c63284a8ff61607db1a51ac2829b74f71c1c.zip
Adding upstream version 21.1.2.upstream/21.1.2
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/optimizer.sql76
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql9
-rw-r--r--tests/fixtures/optimizer/tpc-ds/call_center.csv.gzbin0 -> 421 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/catalog_page.csv.gzbin0 -> 463753 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/catalog_returns.csv.gzbin0 -> 157676 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/catalog_sales.csv.gzbin0 -> 1803802 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/customer.csv.gzbin0 -> 107615 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/customer_address.csv.gzbin0 -> 28336 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/customer_demographics.csv.gzbin0 -> 126457 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/date_dim.csv.gzbin0 -> 1531293 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/household_demographics.csv.gzbin0 -> 23425 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/income_band.csv.gzbin0 -> 188 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/inventory.csv.gzbin0 -> 206882 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/item.csv.gzbin0 -> 31392 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/promotion.csv.gzbin0 -> 497 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/reason.csv.gzbin0 -> 81 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/ship_mode.csv.gzbin0 -> 617 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/store.csv.gzbin0 -> 396 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/store_returns.csv.gzbin0 -> 254858 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/store_sales.csv.gzbin0 -> 2417178 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/time_dim.csv.gzbin0 -> 668972 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/warehouse.csv.gzbin0 -> 218 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/web_page.csv.gzbin0 -> 208 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/web_returns.csv.gzbin0 -> 67542 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/web_sales.csv.gzbin0 -> 864379 bytes
-rw-r--r--tests/fixtures/optimizer/tpc-ds/web_site.csv.gzbin0 -> 404 bytes
-rw-r--r--tests/fixtures/optimizer/unnest_subqueries.sql2
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
new file mode 100644
index 0000000..f36e23b
--- /dev/null
+++ 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
new file mode 100644
index 0000000..702242c
--- /dev/null
+++ 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
new file mode 100644
index 0000000..e87a0ec
--- /dev/null
+++ 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
new file mode 100644
index 0000000..a40b0da
--- /dev/null
+++ 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
new file mode 100644
index 0000000..f4af4f7
--- /dev/null
+++ 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
new file mode 100644
index 0000000..8698e39
--- /dev/null
+++ 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
new file mode 100644
index 0000000..c4b7b68
--- /dev/null
+++ 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
new file mode 100644
index 0000000..35be08f
--- /dev/null
+++ 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
new file mode 100644
index 0000000..b8addb7
--- /dev/null
+++ 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
new file mode 100644
index 0000000..d34d870
--- /dev/null
+++ 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
new file mode 100644
index 0000000..c6f0d47
--- /dev/null
+++ 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
new file mode 100644
index 0000000..4a316cd
--- /dev/null
+++ 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
new file mode 100644
index 0000000..339666c
--- /dev/null
+++ 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
new file mode 100644
index 0000000..0094849
--- /dev/null
+++ 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
new file mode 100644
index 0000000..8dec386
--- /dev/null
+++ 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
new file mode 100644
index 0000000..b4e8de0
--- /dev/null
+++ 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
new file mode 100644
index 0000000..8469492
--- /dev/null
+++ 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
new file mode 100644
index 0000000..3dd22e1
--- /dev/null
+++ 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
new file mode 100644
index 0000000..bf4fcaf
--- /dev/null
+++ b/tests/fixtures/optimizer/tpc-ds/time_dim.csv.gz
Binary files differ
diff --git a/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz b/tests/fixtures/optimizer/tpc-ds/warehouse.csv.gz
new file mode 100644
index 0000000..1dd95a0
--- /dev/null
+++ 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
new file mode 100644
index 0000000..10a06a2
--- /dev/null
+++ 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
new file mode 100644
index 0000000..811e079
--- /dev/null
+++ 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
new file mode 100644
index 0000000..b1ac3b8
--- /dev/null
+++ 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
new file mode 100644
index 0000000..ccedce2
--- /dev/null
+++ b/tests/fixtures/optimizer/tpc-ds/web_site.csv.gz
Binary files differ
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 (