summaryrefslogtreecommitdiffstats
path: root/tests/fixtures
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures')
-rw-r--r--tests/fixtures/identity.sql6
-rw-r--r--tests/fixtures/optimizer/merge_subqueries.sql168
-rw-r--r--tests/fixtures/optimizer/optimizer.sql140
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql43
-rw-r--r--tests/fixtures/optimizer/qualify_columns__with_invisible.sql35
-rw-r--r--tests/fixtures/optimizer/simplify.sql6
-rw-r--r--tests/fixtures/optimizer/tpc-h/tpc-h.sql13
7 files changed, 344 insertions, 67 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index a0de281..40e7cc1 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -321,6 +321,10 @@ SELECT 1 FROM a INNER JOIN b ON a.x = b.x
SELECT 1 FROM a LEFT JOIN b ON a.x = b.x
SELECT 1 FROM a RIGHT JOIN b ON a.x = b.x
SELECT 1 FROM a CROSS JOIN b ON a.x = b.x
+SELECT 1 FROM a LEFT SEMI JOIN b ON a.x = b.x
+SELECT 1 FROM a LEFT ANTI JOIN b ON a.x = b.x
+SELECT 1 FROM a RIGHT SEMI JOIN b ON a.x = b.x
+SELECT 1 FROM a RIGHT ANTI JOIN b ON a.x = b.x
SELECT 1 FROM a JOIN b USING (x)
SELECT 1 FROM a JOIN b USING (x, y, z)
SELECT 1 FROM a JOIN (SELECT a FROM c) AS b ON a.x = b.x AND a.x < 2
@@ -529,12 +533,14 @@ UPDATE db.tbl_name SET foo = 123 WHERE tbl_name.bar = 234
UPDATE db.tbl_name SET foo = 123, foo_1 = 234 WHERE tbl_name.bar = 234
TRUNCATE TABLE x
OPTIMIZE TABLE y
+VACUUM FREEZE my_table
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
WITH a AS (SELECT * FROM b) DELETE FROM a
WITH a AS (SELECT * FROM b) CACHE TABLE a
SELECT ? AS ? FROM x WHERE b BETWEEN ? AND ? GROUP BY ?, 1 LIMIT ?
+SELECT :hello, ? FROM x LIMIT :my_limit
WITH a AS ((SELECT b.foo AS foo, b.bar AS bar FROM b) UNION ALL (SELECT c.foo AS foo, c.bar AS bar FROM c)) SELECT * FROM a
WITH a AS ((SELECT 1 AS b) UNION ALL (SELECT 1 AS b)) SELECT * FROM a
SELECT (WITH x AS (SELECT 1 AS y) SELECT * FROM x) AS z
diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql
index e13d3b3..c8186cc 100644
--- a/tests/fixtures/optimizer/merge_subqueries.sql
+++ b/tests/fixtures/optimizer/merge_subqueries.sql
@@ -1,107 +1,189 @@
--- Simple
+# title: Simple
SELECT a, b FROM (SELECT a, b FROM x);
SELECT x.a AS a, x.b AS b FROM x AS x;
--- Inner table alias is merged
+# title: Inner table alias is merged
SELECT a, b FROM (SELECT a, b FROM x AS q) AS r;
SELECT q.a AS a, q.b AS b FROM x AS q;
--- Double nesting
+# title: Double nesting
SELECT a, b FROM (SELECT a, b FROM (SELECT a, b FROM x));
SELECT x.a AS a, x.b AS b FROM x AS x;
--- WHERE clause is merged
-SELECT a, SUM(b) FROM (SELECT a, b FROM x WHERE a > 1) GROUP BY a;
-SELECT x.a AS a, SUM(x.b) AS "_col_1" FROM x AS x WHERE x.a > 1 GROUP BY x.a;
+# title: WHERE clause is merged
+SELECT a, SUM(b) AS b FROM (SELECT a, b FROM x WHERE a > 1) GROUP BY a;
+SELECT x.a AS a, SUM(x.b) AS b FROM x AS x WHERE x.a > 1 GROUP BY x.a;
--- Outer query has join
-SELECT a, c FROM (SELECT a, b FROM x WHERE a > 1) AS x JOIN y ON x.b = y.b;
-SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b WHERE x.a > 1;
-
--- Outer query has join
+# title: Outer query has join
SELECT a, c FROM (SELECT a, b FROM x WHERE a > 1) AS x JOIN y ON x.b = y.b;
SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b WHERE x.a > 1;
+# title: Leave tables isolated
# leave_tables_isolated: true
SELECT a, c FROM (SELECT a, b FROM x WHERE a > 1) AS x JOIN y ON x.b = y.b;
SELECT x.a AS a, y.c AS c FROM (SELECT x.a AS a, x.b AS b FROM x AS x WHERE x.a > 1) AS x JOIN y AS y ON x.b = y.b;
--- Join on derived table
+# title: Join on derived table
SELECT a, c FROM x JOIN (SELECT b, c FROM y) AS y ON x.b = y.b;
SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b;
--- Inner query has a join
+# title: Inner query has a join
SELECT a, c FROM (SELECT a, c FROM x JOIN y ON x.b = y.b);
SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b;
--- Inner query has conflicting name in outer query
+# title: Inner query has conflicting name in outer query
SELECT a, c FROM (SELECT q.a, q.b FROM x AS q) AS x JOIN y AS q ON x.b = q.b;
SELECT q_2.a AS a, q.c AS c FROM x AS q_2 JOIN y AS q ON q_2.b = q.b;
--- Inner query has conflicting name in joined source
+# title: Inner query has conflicting name in joined source
SELECT x.a, q.c FROM (SELECT a, x.b FROM x JOIN y AS q ON x.b = q.b) AS x JOIN y AS q ON x.b = q.b;
SELECT x.a AS a, q.c AS c FROM x AS x JOIN y AS q_2 ON x.b = q_2.b JOIN y AS q ON x.b = q.b;
--- Inner query has multiple conflicting names
-SELECT x.a, q.c, r.c FROM (SELECT q.a, r.b FROM x AS q JOIN y AS r ON q.b = r.b) AS x JOIN y AS q ON x.b = q.b JOIN y AS r ON x.b = r.b;
-SELECT q_2.a AS a, q.c AS c, r.c AS c FROM x AS q_2 JOIN y AS r_2 ON q_2.b = r_2.b JOIN y AS q ON r_2.b = q.b JOIN y AS r ON r_2.b = r.b;
+# title: Inner query has multiple conflicting names
+SELECT x.a, q.c, r.c FROM (SELECT q.a, r.b FROM x AS q JOIN y AS r ON q.b = r.b) AS x JOIN y AS q ON x.b = q.b JOIN y AS r ON x.b = r.b ORDER BY x.a, q.c, r.c;
+SELECT q_2.a AS a, q.c AS c, r.c AS c FROM x AS q_2 JOIN y AS r_2 ON q_2.b = r_2.b JOIN y AS q ON r_2.b = q.b JOIN y AS r ON r_2.b = r.b ORDER BY q_2.a, q.c, r.c;
--- Inner queries have conflicting names with each other
+# title: Inner queries have conflicting names with each other
SELECT r.b FROM (SELECT b FROM x AS x) AS q JOIN (SELECT b FROM x) AS r ON q.b = r.b;
SELECT x_2.b AS b FROM x AS x JOIN x AS x_2 ON x.b = x_2.b;
--- WHERE clause in joined derived table is merged to ON clause
-SELECT x.a, y.c FROM x JOIN (SELECT b, c FROM y WHERE c > 1) AS y;
-SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON y.c > 1;
+# title: WHERE clause in joined derived table is merged to ON clause
+SELECT x.a, y.c FROM x JOIN (SELECT b, c FROM y WHERE c > 1) AS y ON x.b = y.b;
+SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b AND y.c > 1;
--- Comma JOIN in outer query
+# title: Comma JOIN in outer query
SELECT x.a, y.c FROM (SELECT a FROM x) AS x, (SELECT c FROM y) AS y;
SELECT x.a AS a, y.c AS c FROM x AS x, y AS y;
--- Comma JOIN in inner query
+# title: Comma JOIN in inner query
SELECT x.a, x.c FROM (SELECT x.a, z.c FROM x, y AS z) AS x;
SELECT x.a AS a, z.c AS c FROM x AS x CROSS JOIN y AS z;
--- (Regression) Column in ORDER BY
+# title: (Regression) Column in ORDER BY
SELECT * FROM (SELECT * FROM (SELECT * FROM x)) ORDER BY a LIMIT 1;
SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY x.a LIMIT 1;
--- CTE
+# title: CTE
WITH x AS (SELECT a, b FROM x) SELECT a, b FROM x;
SELECT x.a AS a, x.b AS b FROM x AS x;
--- CTE with outer table alias
+# title: CTE with outer table alias
WITH y AS (SELECT a, b FROM x) SELECT a, b FROM y AS z;
SELECT x.a AS a, x.b AS b FROM x AS x;
--- Nested CTE
-WITH x AS (SELECT a FROM x), x2 AS (SELECT a FROM x) SELECT a FROM x2;
+# title: Nested CTE
+WITH x2 AS (SELECT a FROM x), x3 AS (SELECT a FROM x2) SELECT a FROM x3;
SELECT x.a AS a FROM x AS x;
--- CTE WHERE clause is merged
-WITH x AS (SELECT a, b FROM x WHERE a > 1) SELECT a, SUM(b) FROM x GROUP BY a;
-SELECT x.a AS a, SUM(x.b) AS "_col_1" FROM x AS x WHERE x.a > 1 GROUP BY x.a;
+# title: CTE WHERE clause is merged
+WITH x AS (SELECT a, b FROM x WHERE a > 1) SELECT a, SUM(b) AS b FROM x GROUP BY a;
+SELECT x.a AS a, SUM(x.b) AS b FROM x AS x WHERE x.a > 1 GROUP BY x.a;
--- CTE Outer query has join
-WITH x AS (SELECT a, b FROM x WHERE a > 1) SELECT a, c FROM x AS x JOIN y ON x.b = y.b;
+# title: CTE Outer query has join
+WITH x2 AS (SELECT a, b FROM x WHERE a > 1) SELECT a, c FROM x2 AS x JOIN y ON x.b = y.b;
SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b WHERE x.a > 1;
--- CTE with inner table alias
+# title: CTE with inner table alias
WITH y AS (SELECT a, b FROM x AS q) SELECT a, b FROM y AS z;
SELECT q.a AS a, q.b AS b FROM x AS q;
--- Duplicate queries to CTE
-WITH x AS (SELECT a, b FROM x) SELECT x.a, y.b FROM x JOIN x AS y;
-WITH x AS (SELECT x.a AS a, x.b AS b FROM x AS x) SELECT x.a AS a, y.b AS b FROM x JOIN x AS y;
-
--- Nested CTE
+# title: Nested CTE
SELECT * FROM (WITH x AS (SELECT a, b FROM x) SELECT a, b FROM x);
SELECT x.a AS a, x.b AS b FROM x AS x;
--- Inner select is an expression
+# title: Inner select is an expression
SELECT a FROM (SELECT a FROM (SELECT COALESCE(a) AS a FROM x LEFT JOIN y ON x.a = y.b) AS x) AS x;
SELECT COALESCE(x.a) AS a FROM x AS x LEFT JOIN y AS y ON x.a = y.b;
--- CTE select is an expression
-WITH x AS (SELECT COALESCE(a) AS a FROM x LEFT JOIN y ON x.a = y.b) SELECT a FROM (SELECT a FROM x AS x) AS x;
+# title: CTE select is an expression
+WITH x2 AS (SELECT COALESCE(a) AS a FROM x LEFT JOIN y ON x.a = y.b) SELECT a FROM (SELECT a FROM x2 AS x) AS x;
SELECT COALESCE(x.a) AS a FROM x AS x LEFT JOIN y AS y ON x.a = y.b;
+
+# title: Full outer join
+SELECT x.b AS b, y.b AS b2 FROM (SELECT x.b AS b FROM x AS x WHERE x.b = 1) AS x FULL OUTER JOIN (SELECT y.b AS b FROM y AS y WHERE y.b = 2) AS y ON x.b = y.b;
+SELECT x.b AS b, y.b AS b2 FROM (SELECT x.b AS b FROM x AS x WHERE x.b = 1) AS x FULL OUTER JOIN (SELECT y.b AS b FROM y AS y WHERE y.b = 2) AS y ON x.b = y.b;
+
+# title: Full outer join, no predicates
+SELECT x.b AS b, y.b AS b2 FROM (SELECT x.b AS b FROM x AS x) AS x FULL OUTER JOIN (SELECT y.b AS b FROM y AS y) AS y ON x.b = y.b;
+SELECT x.b AS b, y.b AS b2 FROM x AS x FULL OUTER JOIN y AS y ON x.b = y.b;
+
+# title: Left join
+SELECT x.b AS b, y.b AS b2 FROM (SELECT x.b AS b FROM x AS x WHERE x.b = 1) AS x LEFT JOIN (SELECT y.b AS b FROM y AS y WHERE y.b = 2) AS y ON x.b = y.b;
+SELECT x.b AS b, y.b AS b2 FROM x AS x LEFT JOIN (SELECT y.b AS b FROM y AS y WHERE y.b = 2) AS y ON x.b = y.b WHERE x.b = 1;
+
+# title: Left join, no predicates
+SELECT x.b AS b, y.b AS b2 FROM (SELECT x.b AS b FROM x AS x) AS x LEFT JOIN (SELECT y.b AS b FROM y AS y) AS y ON x.b = y.b;
+SELECT x.b AS b, y.b AS b2 FROM x AS x LEFT JOIN y AS y ON x.b = y.b;
+
+# title: Right join
+SELECT x.b AS b, y.b AS b2 FROM (SELECT x.b AS b FROM x AS x WHERE x.b = 1) AS x RIGHT JOIN (SELECT y.b AS b FROM y AS y WHERE y.b = 2) AS y ON x.b = y.b;
+SELECT x.b AS b, y.b AS b2 FROM (SELECT x.b AS b FROM x AS x WHERE x.b = 1) AS x RIGHT JOIN (SELECT y.b AS b FROM y AS y WHERE y.b = 2) AS y ON x.b = y.b;
+
+# title: Right join, no predicates
+SELECT x.b AS b, y.b AS b2 FROM (SELECT x.b AS b FROM x AS x) AS x RIGHT JOIN (SELECT y.b AS b FROM y AS y) AS y ON x.b = y.b;
+SELECT x.b AS b, y.b AS b2 FROM x AS x RIGHT JOIN y AS y ON x.b = y.b;
+
+# title: Inner join
+SELECT x.b AS b, y.b AS b2 FROM (SELECT x.b AS b FROM x AS x WHERE x.b = 1) AS x INNER JOIN (SELECT y.b AS b FROM y AS y WHERE y.b = 2) AS y ON x.b = y.b;
+SELECT x.b AS b, y.b AS b2 FROM x AS x INNER JOIN y AS y ON x.b = y.b AND y.b = 2 WHERE x.b = 1;
+
+# title: Inner join, no predicates
+SELECT x.b AS b, y.b AS b2 FROM (SELECT x.b AS b FROM x AS x) AS x INNER JOIN (SELECT y.b AS b FROM y AS y) AS y ON x.b = y.b;
+SELECT x.b AS b, y.b AS b2 FROM x AS x INNER JOIN y AS y ON x.b = y.b;
+
+# title: Cross join
+SELECT x.b AS b, y.b AS b2 FROM (SELECT x.b AS b FROM x AS x WHERE x.b = 1) AS x CROSS JOIN (SELECT y.b AS b FROM y AS y WHERE y.b = 2) AS y;
+SELECT x.b AS b, y.b AS b2 FROM x AS x JOIN y AS y ON y.b = 2 WHERE x.b = 1;
+
+# title: Cross join, no predicates
+SELECT x.b AS b, y.b AS b2 FROM (SELECT x.b AS b FROM x AS x) AS x CROSS JOIN (SELECT y.b AS b FROM y AS y) AS y;
+SELECT x.b AS b, y.b AS b2 FROM x AS x CROSS JOIN y AS y;
+
+# title: Broadcast hint
+# dialect: spark
+WITH m AS (SELECT x.a, x.b FROM x), n AS (SELECT y.b, y.c FROM y), joined as (SELECT /*+ BROADCAST(k) */ m.a, k.c FROM m JOIN n AS k ON m.b = k.b) SELECT joined.a, joined.c FROM joined;
+SELECT /*+ BROADCAST(y) */ x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b;
+
+# title: Broadcast hint multiple tables
+# dialect: spark
+WITH m AS (SELECT x.a, x.b FROM x), n AS (SELECT y.b, y.c FROM y), joined as (SELECT /*+ BROADCAST(m, n) */ m.a, n.c FROM m JOIN n ON m.b = n.b) SELECT joined.a, joined.c FROM joined;
+SELECT /*+ BROADCAST(x, y) */ x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b;
+
+# title: Multiple Table Hints
+# dialect: spark
+WITH m AS (SELECT x.a, x.b FROM x), n AS (SELECT y.b, y.c FROM y), joined as (SELECT /*+ BROADCAST(m), MERGE(m, n) */ m.a, n.c FROM m JOIN n ON m.b = n.b) SELECT joined.a, joined.c FROM joined;
+SELECT /*+ BROADCAST(x), MERGE(x, y) */ x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b;
+
+# title: Mix Table and Column Hints
+# dialect: spark
+WITH m AS (SELECT x.a, x.b FROM x), n AS (SELECT y.b, y.c FROM y), joined as (SELECT /*+ BROADCAST(m), MERGE(m, n) */ m.a, n.c FROM m JOIN n ON m.b = n.b) SELECT /*+ COALESCE(3) */ joined.a, joined.c FROM joined;
+SELECT /*+ COALESCE(3), BROADCAST(x), MERGE(x, y) */ x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b;
+
+# title: Hint Subquery
+# dialect: spark
+SELECT
+ subquery.a,
+ subquery.c
+FROM (
+ SELECT /*+ BROADCAST(m), MERGE(m, n) */ m.a, n.c FROM (SELECT x.a, x.b FROM x) AS m JOIN (SELECT y.b, y.c FROM y) AS n ON m.b = n.b
+) AS subquery;
+SELECT /*+ BROADCAST(x), MERGE(x, y) */ x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b;
+
+# title: Subquery Test
+# dialect: spark
+SELECT /*+ BROADCAST(x) */
+ x.a,
+ x.c
+FROM (
+ SELECT
+ x.a,
+ x.c
+ FROM (
+ SELECT
+ x.a,
+ COUNT(1) AS c
+ FROM x
+ GROUP BY x.a
+ ) AS x
+) AS x;
+SELECT /*+ BROADCAST(x) */ x.a AS a, x.c AS c FROM (SELECT x.a AS a, COUNT(1) AS c FROM x AS x GROUP BY x.a) AS x;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index eb6761a..ab4f769 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -1,3 +1,5 @@
+# title: lateral
+# execute: false
SELECT a, m FROM z LATERAL VIEW EXPLODE([1, 2]) q AS m;
SELECT
"z"."a" AS "a",
@@ -6,11 +8,13 @@ FROM "z" AS "z"
LATERAL VIEW
EXPLODE(ARRAY(1, 2)) q AS "m";
+# title: unnest
SELECT x FROM UNNEST([1, 2]) AS q(x, y);
SELECT
"q"."x" AS "x"
FROM UNNEST(ARRAY(1, 2)) AS "q"("x", "y");
+# title: Union in CTE
WITH cte AS (
(
SELECT
@@ -21,7 +25,7 @@ WITH cte AS (
UNION ALL
(
SELECT
- a
+ b AS a
FROM
y
)
@@ -39,7 +43,7 @@ WITH "cte" AS (
UNION ALL
(
SELECT
- "y"."a" AS "a"
+ "y"."b" AS "a"
FROM "y" AS "y"
)
)
@@ -47,6 +51,7 @@ SELECT
"cte"."a" AS "a"
FROM "cte";
+# title: Chained CTEs
WITH cte1 AS (
SELECT a
FROM x
@@ -74,30 +79,31 @@ SELECT
"cte1"."a" + 1 AS "a"
FROM "cte1";
-SELECT a, SUM(b)
+# title: Correlated subquery
+SELECT a, SUM(b) AS sum_b
FROM (
SELECT x.a, y.b
FROM x, y
- WHERE (SELECT max(b) FROM y WHERE x.a = y.a) >= 0 AND x.a = y.a
+ WHERE (SELECT max(b) FROM y WHERE x.b = y.b) >= 0 AND x.b = y.b
) d
WHERE (TRUE AND TRUE OR 'a' = 'b') AND a > 1
GROUP BY a;
WITH "_u_0" AS (
SELECT
MAX("y"."b") AS "_col_0",
- "y"."a" AS "_u_1"
+ "y"."b" AS "_u_1"
FROM "y" AS "y"
GROUP BY
- "y"."a"
+ "y"."b"
)
SELECT
"x"."a" AS "a",
- SUM("y"."b") AS "_col_1"
+ SUM("y"."b") AS "sum_b"
FROM "x" AS "x"
LEFT JOIN "_u_0" AS "_u_0"
- ON "x"."a" = "_u_0"."_u_1"
+ ON "x"."b" = "_u_0"."_u_1"
JOIN "y" AS "y"
- ON "x"."a" = "y"."a"
+ ON "x"."b" = "y"."b"
WHERE
"_u_0"."_col_0" >= 0
AND "x"."a" > 1
@@ -105,6 +111,7 @@ WHERE
GROUP BY
"x"."a";
+# title: Root subquery
(SELECT a FROM x) LIMIT 1;
(
SELECT
@@ -113,6 +120,7 @@ GROUP BY
)
LIMIT 1;
+# title: Root subquery is union
(SELECT b FROM x UNION SELECT b FROM y) LIMIT 1;
(
SELECT
@@ -125,6 +133,7 @@ LIMIT 1;
)
LIMIT 1;
+# title: broadcast
# dialect: spark
SELECT /*+ BROADCAST(y) */ x.b FROM x JOIN y ON x.b = y.b;
SELECT /*+ BROADCAST(`y`) */
@@ -133,11 +142,14 @@ FROM `x` AS `x`
JOIN `y` AS `y`
ON `x`.`b` = `y`.`b`;
+# title: aggregate
+# execute: false
SELECT AGGREGATE(ARRAY(x.a, x.b), 0, (x, acc) -> x + acc + a) AS sum_agg FROM x;
SELECT
AGGREGATE(ARRAY("x"."a", "x"."b"), 0, ("x", "acc") -> "x" + "acc" + "x"."a") AS "sum_agg"
FROM "x" AS "x";
+# title: values
SELECT cola, colb FROM (VALUES (1, 'test'), (2, 'test2')) AS tab(cola, colb);
SELECT
"tab"."cola" AS "cola",
@@ -146,6 +158,7 @@ FROM (VALUES
(1, 'test'),
(2, 'test2')) AS "tab"("cola", "colb");
+# title: spark values
# dialect: spark
SELECT cola, colb FROM (VALUES (1, 'test'), (2, 'test2')) AS tab(cola, colb);
SELECT
@@ -154,3 +167,112 @@ SELECT
FROM VALUES
(1, 'test'),
(2, 'test2') AS `tab`(`cola`, `colb`);
+
+# title: complex CTE dependencies
+WITH m AS (
+ SELECT a, b FROM (VALUES (1, 2)) AS a1(a, b)
+), n AS (
+ SELECT a, b FROM m WHERE m.a = 1
+), o AS (
+ SELECT a, b FROM m WHERE m.a = 2
+) SELECT
+ n.a,
+ n.b,
+ o.b
+FROM n
+FULL OUTER JOIN o ON n.a = o.a
+CROSS JOIN n AS n2
+WHERE o.b > 0 AND n.a = n2.a;
+WITH "m" AS (
+ SELECT
+ "a1"."a" AS "a",
+ "a1"."b" AS "b"
+ FROM (VALUES
+ (1, 2)) AS "a1"("a", "b")
+), "n" AS (
+ SELECT
+ "m"."a" AS "a",
+ "m"."b" AS "b"
+ FROM "m"
+ WHERE
+ "m"."a" = 1
+), "o" AS (
+ SELECT
+ "m"."a" AS "a",
+ "m"."b" AS "b"
+ FROM "m"
+ WHERE
+ "m"."a" = 2
+)
+SELECT
+ "n"."a" AS "a",
+ "n"."b" AS "b",
+ "o"."b" AS "b"
+FROM "n"
+FULL JOIN "o"
+ ON "n"."a" = "o"."a"
+JOIN "n" AS "n2"
+ ON "n"."a" = "n2"."a"
+WHERE
+ "o"."b" > 0;
+
+# title: Broadcast hint
+# dialect: spark
+WITH m AS (
+ SELECT
+ x.a,
+ x.b
+ FROM x
+), n AS (
+ SELECT
+ y.b,
+ y.c
+ FROM y
+), joined as (
+ SELECT /*+ BROADCAST(n) */
+ m.a,
+ n.c
+ FROM m JOIN n ON m.b = n.b
+)
+SELECT
+ joined.a,
+ joined.c
+FROM joined;
+SELECT /*+ BROADCAST(`y`) */
+ `x`.`a` AS `a`,
+ `y`.`c` AS `c`
+FROM `x` AS `x`
+JOIN `y` AS `y`
+ ON `x`.`b` = `y`.`b`;
+
+# title: Mix Table and Column Hints
+# dialect: spark
+WITH m AS (
+ SELECT
+ x.a,
+ x.b
+ FROM x
+), n AS (
+ SELECT
+ y.b,
+ y.c
+ FROM y
+), joined as (
+ SELECT /*+ BROADCAST(m), MERGE(m, n) */
+ m.a,
+ n.c
+ FROM m JOIN n ON m.b = n.b
+)
+SELECT
+ /*+ COALESCE(3) */
+ joined.a,
+ joined.c
+FROM joined;
+SELECT /*+ COALESCE(3),
+ BROADCAST(`x`),
+ MERGE(`x`, `y`) */
+ `x`.`a` AS `a`,
+ `y`.`c` AS `c`
+FROM `x` AS `x`
+JOIN `y` AS `y`
+ ON `x`.`b` = `y`.`b`;
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index f848e7a..83a3bf8 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -19,38 +19,49 @@ SELECT x.a AS a FROM x AS x;
SELECT a AS b FROM x;
SELECT x.a AS b FROM x AS x;
+# execute: false
SELECT 1, 2 FROM x;
SELECT 1 AS "_col_0", 2 AS "_col_1" FROM x AS x;
+# execute: false
SELECT a + b FROM x;
SELECT x.a + x.b AS "_col_0" FROM x AS x;
-SELECT a + b FROM x;
-SELECT x.a + x.b AS "_col_0" FROM x AS x;
-
+# execute: false
SELECT a, SUM(b) FROM x WHERE a > 1 AND b > 1 GROUP BY a;
SELECT x.a AS a, SUM(x.b) AS "_col_1" FROM x AS x WHERE x.a > 1 AND x.b > 1 GROUP BY x.a;
SELECT a AS j, b FROM x ORDER BY j;
SELECT x.a AS j, x.b AS b FROM x AS x ORDER BY j;
-SELECT a AS j, b FROM x GROUP BY j;
-SELECT x.a AS j, x.b AS b FROM x AS x GROUP BY x.a;
+SELECT a AS j, b AS a FROM x ORDER BY 1;
+SELECT x.a AS j, x.b AS a FROM x AS x ORDER BY x.a;
+
+SELECT SUM(a) AS c, SUM(b) AS d FROM x ORDER BY 1, 2;
+SELECT SUM(x.a) AS c, SUM(x.b) AS d FROM x AS x ORDER BY SUM(x.a), SUM(x.b);
+
+# execute: false
+SELECT SUM(a), SUM(b) AS c FROM x ORDER BY 1, 2;
+SELECT SUM(x.a) AS "_col_0", SUM(x.b) AS c FROM x AS x ORDER BY SUM(x.a), SUM(x.b);
+
+SELECT a AS j, b FROM x GROUP BY j, b;
+SELECT x.a AS j, x.b AS b FROM x AS x GROUP BY x.a, x.b;
SELECT a, b FROM x GROUP BY 1, 2;
SELECT x.a AS a, x.b AS b FROM x AS x GROUP BY x.a, x.b;
SELECT a, b FROM x ORDER BY 1, 2;
-SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY a, b;
+SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY x.a, x.b;
+# execute: false
SELECT DATE(a), DATE(b) AS c FROM x GROUP BY 1, 2;
SELECT DATE(x.a) AS "_col_0", DATE(x.b) AS c FROM x AS x GROUP BY DATE(x.a), DATE(x.b);
-SELECT x.a AS c FROM x JOIN y ON x.b = y.b GROUP BY c;
-SELECT x.a AS c FROM x AS x JOIN y AS y ON x.b = y.b GROUP BY y.c;
+SELECT SUM(x.a) AS c FROM x JOIN y ON x.b = y.b GROUP BY c;
+SELECT SUM(x.a) AS c FROM x AS x JOIN y AS y ON x.b = y.b GROUP BY y.c;
-SELECT DATE(x.a) AS d FROM x JOIN y ON x.b = y.b GROUP BY d;
-SELECT DATE(x.a) AS d FROM x AS x JOIN y AS y ON x.b = y.b GROUP BY DATE(x.a);
+SELECT COALESCE(x.a) AS d FROM x JOIN y ON x.b = y.b GROUP BY d;
+SELECT COALESCE(x.a) AS d FROM x AS x JOIN y AS y ON x.b = y.b GROUP BY COALESCE(x.a);
SELECT a AS a, b FROM x ORDER BY a;
SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY a;
@@ -69,6 +80,7 @@ SELECT ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) AS row_num FROM x AS x
SELECT x.b, x.a FROM x LEFT JOIN y ON x.b = y.b QUALIFY ROW_NUMBER() OVER(PARTITION BY x.b ORDER BY x.a DESC) = 1;
SELECT x.b AS b, x.a AS a FROM x AS x LEFT JOIN y AS y ON x.b = y.b QUALIFY ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a DESC) = 1;
+# execute: false
SELECT AGGREGATE(ARRAY(a, x.b), 0, (x, acc) -> x + acc + a) AS sum_agg FROM x;
SELECT AGGREGATE(ARRAY(x.a, x.b), 0, (x, acc) -> x + acc + x.a) AS sum_agg FROM x AS x;
@@ -93,8 +105,8 @@ SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
SELECT a FROM (SELECT a FROM (SELECT a FROM x));
SELECT "_q_1".a AS a FROM (SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0") AS "_q_1";
-SELECT x.a FROM x AS x JOIN (SELECT * FROM x);
-SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a, x.b AS b FROM x AS x) AS "_q_0";
+SELECT x.a FROM x AS x JOIN (SELECT * FROM x) AS y ON x.a = y.a;
+SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a, x.b AS b FROM x AS x) AS y ON x.a = y.a;
--------------------------------------
-- Joins
@@ -123,6 +135,7 @@ SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FRO
SELECT a FROM x WHERE b IN (SELECT c FROM y);
SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT y.c AS c FROM y AS y);
+# execute: false
SELECT (SELECT c FROM y) FROM x;
SELECT (SELECT y.c AS c FROM y AS y) AS "_col_0" FROM x AS x;
@@ -144,10 +157,12 @@ SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT x.b AS b FROM y AS x);
SELECT a FROM x AS i WHERE b IN (SELECT b FROM y AS j WHERE j.b IN (SELECT c FROM y AS k WHERE k.b = j.b));
SELECT i.a AS a FROM x AS i WHERE i.b IN (SELECT j.b AS b FROM y AS j WHERE j.b IN (SELECT k.c AS c FROM y AS k WHERE k.b = j.b));
+# execute: false
# dialect: bigquery
SELECT aa FROM x, UNNEST(a) AS aa;
SELECT aa AS aa FROM x AS x, UNNEST(x.a) AS aa;
+# execute: false
SELECT aa FROM x, UNNEST(a) AS t(aa);
SELECT t.aa AS aa FROM x AS x, UNNEST(x.a) AS t(aa);
@@ -205,15 +220,19 @@ WITH z AS ((SELECT x.b AS b FROM x AS x UNION ALL SELECT y.b AS b FROM y AS y) O
--------------------------------------
-- Except and Replace
--------------------------------------
+# execute: false
SELECT * REPLACE(a AS d) FROM x;
SELECT x.a AS d, x.b AS b FROM x AS x;
+# execute: false
SELECT * EXCEPT(b) REPLACE(a AS d) FROM x;
SELECT x.a AS d FROM x AS x;
+# execute: false
SELECT x.* EXCEPT(a), y.* FROM x, y;
SELECT x.b AS b, y.b AS b, y.c AS c FROM x AS x, y AS y;
+# execute: false
SELECT * EXCEPT(a) FROM x;
SELECT x.b AS b FROM x AS x;
diff --git a/tests/fixtures/optimizer/qualify_columns__with_invisible.sql b/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
new file mode 100644
index 0000000..ee46c23
--- /dev/null
+++ b/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
@@ -0,0 +1,35 @@
+--------------------------------------
+-- Qualify columns
+--------------------------------------
+SELECT a FROM x;
+SELECT x.a AS a FROM x AS x;
+
+SELECT b FROM x;
+SELECT x.b AS b FROM x AS x;
+
+--------------------------------------
+-- Derived tables
+--------------------------------------
+SELECT x.a FROM x AS x JOIN (SELECT * FROM x);
+SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a FROM x AS x) AS "_q_0";
+
+SELECT x.b FROM x AS x JOIN (SELECT b FROM x);
+SELECT x.b AS b FROM x AS x JOIN (SELECT x.b AS b FROM x AS x) AS "_q_0";
+
+--------------------------------------
+-- Expand *
+--------------------------------------
+SELECT * FROM x;
+SELECT x.a AS a FROM x AS x;
+
+SELECT * FROM y JOIN z ON y.b = z.b;
+SELECT y.b AS b, z.b AS b FROM y AS y JOIN z AS z ON y.b = z.b;
+
+SELECT * FROM y JOIN z ON y.c = z.c;
+SELECT y.b AS b, z.b AS b FROM y AS y JOIN z AS z ON y.c = z.c;
+
+SELECT a FROM (SELECT * FROM x);
+SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
+
+SELECT * FROM (SELECT a FROM x);
+SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index d7217cf..07e818f 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -52,6 +52,9 @@ TRUE;
NULL AND TRUE;
NULL;
+NULL AND FALSE;
+FALSE;
+
NULL AND NULL;
NULL;
@@ -70,6 +73,9 @@ FALSE;
NOT FALSE;
TRUE;
+NOT NULL;
+NULL;
+
NULL = NULL;
NULL;
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index d2f10fc..936a0af 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -769,13 +769,20 @@ group by
order by
custdist desc,
c_count desc;
-WITH "c_orders" AS (
+WITH "orders_2" AS (
+ SELECT
+ "orders"."o_orderkey" AS "o_orderkey",
+ "orders"."o_custkey" AS "o_custkey",
+ "orders"."o_comment" AS "o_comment"
+ FROM "orders" AS "orders"
+ WHERE
+ NOT "orders"."o_comment" LIKE '%special%requests%'
+), "c_orders" AS (
SELECT
COUNT("orders"."o_orderkey") AS "c_count"
FROM "customer" AS "customer"
- LEFT JOIN "orders" AS "orders"
+ LEFT JOIN "orders_2" AS "orders"
ON "customer"."c_custkey" = "orders"."o_custkey"
- AND NOT "orders"."o_comment" LIKE '%special%requests%'
GROUP BY
"customer"."c_custkey"
)
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-11 08:26:00 +0000