summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer/optimizer.sql
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2022-10-10 11:29:00 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2022-10-10 11:29:00 +0000
commit74b38d30f43f7005428e09fa80508c5f21324c99 (patch)
tree7a0d4e49fffdc0330fc941c6528d3c8669a2acc6 /tests/fixtures/optimizer/optimizer.sql
parentAdding upstream version 6.2.8. (diff)
downloadsqlglot-74b38d30f43f7005428e09fa80508c5f21324c99.tar.xz
sqlglot-74b38d30f43f7005428e09fa80508c5f21324c99.zip
Adding upstream version 6.3.1.upstream/6.3.1
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures/optimizer/optimizer.sql')
-rw-r--r--tests/fixtures/optimizer/optimizer.sql140
1 files changed, 131 insertions, 9 deletions
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`;