summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer/optimizer.sql
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2022-09-30 05:07:13 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2022-09-30 05:07:13 +0000
commit276f5723c8ab7e0b2938693db546dbd30be3f01a (patch)
treee6294c63de34a03e373245ec4cb1efbca1edfe61 /tests/fixtures/optimizer/optimizer.sql
parentAdding upstream version 6.2.1. (diff)
downloadsqlglot-276f5723c8ab7e0b2938693db546dbd30be3f01a.tar.xz
sqlglot-276f5723c8ab7e0b2938693db546dbd30be3f01a.zip
Adding upstream version 6.2.6.upstream/6.2.6
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.sql34
1 files changed, 22 insertions, 12 deletions
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index f1d0f7d..0bb742b 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -65,18 +65,14 @@ WITH "cte1" AS (
SELECT
"x"."a" AS "a"
FROM "x" AS "x"
-), "cte2" AS (
- SELECT
- "cte1"."a" + 1 AS "a"
- FROM "cte1"
)
SELECT
"cte1"."a" AS "a"
FROM "cte1"
UNION ALL
SELECT
- "cte2"."a" AS "a"
-FROM "cte2";
+ "cte1"."a" + 1 AS "a"
+FROM "cte1";
SELECT a, SUM(b)
FROM (
@@ -86,18 +82,19 @@ FROM (
) d
WHERE (TRUE AND TRUE OR 'a' = 'b') AND a > 1
GROUP BY a;
-SELECT
- "x"."a" AS "a",
- SUM("y"."b") AS "_col_1"
-FROM "x" AS "x"
-LEFT JOIN (
+WITH "_u_0" AS (
SELECT
MAX("y"."b") AS "_col_0",
"y"."a" AS "_u_1"
FROM "y" AS "y"
GROUP BY
"y"."a"
-) AS "_u_0"
+)
+SELECT
+ "x"."a" AS "a",
+ SUM("y"."b") AS "_col_1"
+FROM "x" AS "x"
+LEFT JOIN "_u_0" AS "_u_0"
ON "x"."a" = "_u_0"."_u_1"
JOIN "y" AS "y"
ON "x"."a" = "y"."a"
@@ -127,3 +124,16 @@ LIMIT 1;
FROM "y" AS "y"
)
LIMIT 1;
+
+# dialect: spark
+SELECT /*+ BROADCAST(y) */ x.b FROM x JOIN y ON x.b = y.b;
+SELECT /*+ BROADCAST(`y`) */
+ `x`.`b` AS `b`
+FROM `x` AS `x`
+JOIN `y` AS `y`
+ ON `x`.`b` = `y`.`b`;
+
+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";