summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/normalize.sql2
-rw-r--r--tests/fixtures/optimizer/normalize_identifiers.sql3
-rw-r--r--tests/fixtures/optimizer/optimizer.sql4
-rw-r--r--tests/fixtures/optimizer/pushdown_predicates.sql8
-rw-r--r--tests/fixtures/optimizer/qualify_tables.sql4
-rw-r--r--tests/fixtures/optimizer/quote_identities.sql3
-rw-r--r--tests/fixtures/optimizer/simplify.sql20
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql4
-rw-r--r--tests/fixtures/optimizer/tpc-h/tpc-h.sql6
9 files changed, 32 insertions, 22 deletions
diff --git a/tests/fixtures/optimizer/normalize.sql b/tests/fixtures/optimizer/normalize.sql
index 12bc388..8e94c19 100644
--- a/tests/fixtures/optimizer/normalize.sql
+++ b/tests/fixtures/optimizer/normalize.sql
@@ -44,4 +44,4 @@ SELECT * FROM x WHERE (A AND B) OR C;
SELECT * FROM x WHERE (A OR C) AND (B OR C);
dt2 between '2022-01-01 12:00:00' and '2022-12-31' and dt2 >= '2022-05-01 12:00:00' or dt2 = '2021-06-01 12:00:00';
-(dt2 <= '2022-12-31' OR dt2 = '2021-06-01 12:00:00') AND (dt2 = '2021-06-01 12:00:00' OR dt2 >= '2022-01-01 12:00:00') AND (dt2 = '2021-06-01 12:00:00' OR dt2 >= '2022-05-01 12:00:00')
+(dt2 <= '2022-12-31' OR dt2 = '2021-06-01 12:00:00') AND (dt2 = '2021-06-01 12:00:00' OR dt2 >= '2022-01-01 12:00:00') AND (dt2 = '2021-06-01 12:00:00' OR dt2 >= '2022-05-01 12:00:00');
diff --git a/tests/fixtures/optimizer/normalize_identifiers.sql b/tests/fixtures/optimizer/normalize_identifiers.sql
index 4cb7dd1..7ddcc6d 100644
--- a/tests/fixtures/optimizer/normalize_identifiers.sql
+++ b/tests/fixtures/optimizer/normalize_identifiers.sql
@@ -70,3 +70,6 @@ SELECT a /* sqlglot.meta case_sensitive */, B FROM table /* sqlglot.meta case_se
# dialect: redshift
SELECT COALESCE(json_val.a /* sqlglot.meta case_sensitive */, json_val.A /* sqlglot.meta case_sensitive */) FROM table;
SELECT COALESCE(json_val.a /* sqlglot.meta case_sensitive */, json_val.A /* sqlglot.meta case_sensitive */) FROM table;
+
+SELECT @X;
+SELECT @X;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index a812bc5..a9d6584 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -480,8 +480,8 @@ JOIN "company_table" AS "company_table_2"
LEFT JOIN "unlocked" AS "unlocked"
ON "company_table_2"."id" = "unlocked"."company_id"
WHERE
- CASE WHEN "unlocked"."company_id" IS NULL THEN 0 ELSE 1 END = FALSE
- AND NOT "company_table_2"."id" IS NULL;
+ NOT "company_table_2"."id" IS NULL
+ AND CASE WHEN "unlocked"."company_id" IS NULL THEN 0 ELSE 1 END = FALSE;
# title: db.table alias clash
# execute: false
diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql
index cfa69fb..9912b89 100644
--- a/tests/fixtures/optimizer/pushdown_predicates.sql
+++ b/tests/fixtures/optimizer/pushdown_predicates.sql
@@ -11,7 +11,7 @@ SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE (x.a = y.a AND x.a = 1 AND x
SELECT x.a FROM (SELECT * FROM x) AS x JOIN y ON x.a = y.a WHERE TRUE;
SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE (x.a = y.a AND x.a = 1 AND x.b = 1) OR x.a = y.b;
-SELECT x.a FROM (SELECT * FROM x) AS x JOIN y ON x.a = y.a OR x.a = y.b WHERE (x.a = y.a AND x.a = 1 AND x.b = 1) OR x.a = y.b;
+SELECT x.a FROM (SELECT * FROM x) AS x JOIN y ON x.a = y.a OR x.a = y.b WHERE (x.a = 1 AND x.a = y.a AND x.b = 1) OR x.a = y.b;
SELECT x.a FROM (SELECT x.a AS a, x.b * 1 AS c FROM x) AS x WHERE x.c = 1;
SELECT x.a FROM (SELECT x.a AS a, x.b * 1 AS c FROM x WHERE x.b * 1 = 1) AS x WHERE TRUE;
@@ -26,10 +26,10 @@ SELECT x.a FROM x AS x JOIN (SELECT y.a FROM y AS y) AS y ON y.a = 1 AND x.a = y
SELECT x.a FROM x AS x JOIN (SELECT y.a FROM y AS y WHERE y.a = 1) AS y ON x.a = y.a AND TRUE;
SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y) AS y ON y.a = 1 WHERE x.a = 1 AND x.b = 1 AND y.a = x.a;
-SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON y.a = x.a AND TRUE WHERE x.a = 1 AND x.b = 1 AND TRUE;
+SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON TRUE AND y.a = x.a WHERE x.a = 1 AND x.b = 1 AND TRUE;
SELECT x.a AS a FROM x AS x CROSS JOIN (SELECT * FROM y AS y) AS y WHERE x.a = 1 AND x.b = 1 AND y.a = x.a AND y.a = 1;
-SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON y.a = x.a AND TRUE WHERE x.a = 1 AND x.b = 1 AND TRUE AND TRUE;
+SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON TRUE AND y.a = x.a WHERE x.a = 1 AND x.b = 1 AND TRUE AND TRUE;
with t1 as (SELECT x.a, x.b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num FROM x) SELECT t1.a, t1.b FROM t1 WHERE row_num = 1;
WITH t1 AS (SELECT x.a, x.b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x) SELECT t1.a, t1.b FROM t1 WHERE row_num = 1;
@@ -43,4 +43,4 @@ SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt FROM x AS x HAVING COUNT(1) > 0
-- Pushdown predicate to HAVING (DNF)
SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt, COUNT(x.a) AS cnt_a, COUNT(x.b) AS cnt_b FROM x AS x) AS x WHERE (x.cnt_a > 0 AND x.cnt_b > 0) OR x.cnt > 0;
-SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt, COUNT(x.a) AS cnt_a, COUNT(x.b) AS cnt_b FROM x AS x HAVING COUNT(1) > 0 OR (COUNT(x.a) > 0 AND COUNT(x.b) > 0)) AS x WHERE x.cnt > 0 OR (x.cnt_a > 0 AND x.cnt_b > 0); \ No newline at end of file
+SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt, COUNT(x.a) AS cnt_a, COUNT(x.b) AS cnt_b FROM x AS x HAVING COUNT(1) > 0 OR (COUNT(x.a) > 0 AND COUNT(x.b) > 0)) AS x WHERE x.cnt > 0 OR (x.cnt_a > 0 AND x.cnt_b > 0);
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
index 3717cd4..248d7e9 100644
--- a/tests/fixtures/optimizer/qualify_tables.sql
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -117,3 +117,7 @@ SELECT x FROM c.db.t AS t, LATERAL UNNEST(t.xs) AS x;
# title: lateral unnest without alias
SELECT x FROM t, LATERAL UNNEST(t.xs);
SELECT x FROM c.db.t AS t, LATERAL UNNEST(t.xs) AS _q_0;
+
+# title: table with ordinality
+SELECT * FROM t CROSS JOIN JSON_ARRAY_ELEMENTS(t.response) WITH ORDINALITY AS kv_json;
+SELECT * FROM c.db.t AS t CROSS JOIN JSON_ARRAY_ELEMENTS(t.response) WITH ORDINALITY AS kv_json;
diff --git a/tests/fixtures/optimizer/quote_identities.sql b/tests/fixtures/optimizer/quote_identities.sql
index 407b7f6..d6cfbf8 100644
--- a/tests/fixtures/optimizer/quote_identities.sql
+++ b/tests/fixtures/optimizer/quote_identities.sql
@@ -6,3 +6,6 @@ SELECT "a" FROM "x";
SELECT x.a AS a FROM db.x;
SELECT "x"."a" AS "a" FROM "db"."x";
+
+SELECT @x;
+SELECT @x;
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index c53a972..f50f688 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -631,19 +631,19 @@ COALESCE(x);
x;
COALESCE(x, 1) = 2;
-x = 2 AND NOT x IS NULL;
+NOT x IS NULL AND x = 2;
2 = COALESCE(x, 1);
2 = x AND NOT x IS NULL;
COALESCE(x, 1, 1) = 1 + 1;
-x = 2 AND NOT x IS NULL;
+NOT x IS NULL AND x = 2;
COALESCE(x, 1, 2) = 2;
-x = 2 AND NOT x IS NULL;
+NOT x IS NULL AND x = 2;
COALESCE(x, 3) <= 2;
-x <= 2 AND NOT x IS NULL;
+NOT x IS NULL AND x <= 2;
COALESCE(x, 1) <> 2;
x <> 2 OR x IS NULL;
@@ -872,7 +872,7 @@ x = 5 AND y = x;
x = 5 AND y = 5;
5 = x AND y = x;
-y = 5 AND 5 = x;
+5 = x AND y = 5;
x = 5 OR y = x;
x = 5 OR y = x;
@@ -899,13 +899,13 @@ x = 5 AND x + 3 = 8;
x = 5;
x = 5 AND (SELECT x FROM t WHERE y = 1);
-x = 5 AND (SELECT x FROM t WHERE y = 1);
+(SELECT x FROM t WHERE y = 1) AND x = 5;
x = 1 AND y > 0 AND (SELECT z = 5 FROM t WHERE y = 1);
-x = 1 AND y > 0 AND (SELECT z = 5 FROM t WHERE y = 1);
+(SELECT z = 5 FROM t WHERE y = 1) AND x = 1 AND y > 0;
x = 1 AND x = y AND (SELECT z FROM t WHERE a AND (b OR c));
-x = 1 AND (SELECT z FROM t WHERE a AND (b OR c)) AND 1 = y;
+(SELECT z FROM t WHERE a AND (b OR c)) AND 1 = y AND x = 1;
t1.a = 39 AND t2.b = t1.a AND t3.c = t2.b;
t1.a = 39 AND t2.b = 39 AND t3.c = 39;
@@ -920,10 +920,10 @@ x = 1 AND CASE x WHEN 5 THEN FALSE ELSE TRUE END;
x = 1;
x = y AND CASE WHEN x = 5 THEN FALSE ELSE TRUE END;
-x = y AND CASE WHEN x = 5 THEN FALSE ELSE TRUE END;
+CASE WHEN x = 5 THEN FALSE ELSE TRUE END AND x = y;
x = 1 AND CASE WHEN y = 5 THEN x = z END;
-x = 1 AND CASE WHEN y = 5 THEN 1 = z END;
+CASE WHEN y = 5 THEN 1 = z END AND x = 1;
--------------------------------------
-- Simplify Conditionals
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index 52ee12c..62f1d79 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -2547,8 +2547,8 @@ JOIN "date_dim" AS "date_dim"
)
WHERE
"_u_3"."_u_4" IS NULL
- AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "cs1"."cs_warehouse_sk" <> "_x")
AND NOT "_u_0"."_u_1" IS NULL
+ AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "cs1"."cs_warehouse_sk" <> "_x")
ORDER BY
COUNT(DISTINCT "cs1"."cs_order_number")
LIMIT 100;
@@ -12586,8 +12586,8 @@ JOIN "web_site" AS "web_site"
AND "ws1"."ws_web_site_sk" = "web_site"."web_site_sk"
WHERE
"_u_3"."_u_4" IS NULL
- AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "ws1"."ws_warehouse_sk" <> "_x")
AND NOT "_u_0"."_u_1" IS NULL
+ AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "ws1"."ws_warehouse_sk" <> "_x")
ORDER BY
COUNT(DISTINCT "ws1"."ws_order_number")
LIMIT 100;
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index 942295e..14d8b53 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -249,9 +249,9 @@ FROM "orders" AS "orders"
LEFT JOIN "_u_0" AS "_u_0"
ON "_u_0"."l_orderkey" = "orders"."o_orderkey"
WHERE
- CAST("orders"."o_orderdate" AS DATE) < CAST('1993-10-01' AS DATE)
+ NOT "_u_0"."l_orderkey" IS NULL
+ AND CAST("orders"."o_orderdate" AS DATE) < CAST('1993-10-01' AS DATE)
AND CAST("orders"."o_orderdate" AS DATE) >= CAST('1993-07-01' AS DATE)
- AND NOT "_u_0"."l_orderkey" IS NULL
GROUP BY
"orders"."o_orderpriority"
ORDER BY
@@ -1348,8 +1348,8 @@ WHERE
"_u_2"."l_orderkey" IS NULL
OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "_x" <> "l1"."l_suppkey")
)
- AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "_x" <> "l1"."l_suppkey")
AND NOT "_u_0"."l_orderkey" IS NULL
+ AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "_x" <> "l1"."l_suppkey")
GROUP BY
"supplier"."s_name"
ORDER BY
generated by cgit v1.2.3 (git 2.39.1) at 2024-05-23 04:34:58 +0000