summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-02-12 10:03:37 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-02-12 10:03:37 +0000
commit24e839c04c39d1f4423b267c371e8e5b5bc33867 (patch)
tree2395ec71424fe1ff783e0dc7ca0f9b4e16def8a9 /tests/fixtures/optimizer
parentAdding upstream version 10.6.3. (diff)
downloadsqlglot-24e839c04c39d1f4423b267c371e8e5b5bc33867.tar.xz
sqlglot-24e839c04c39d1f4423b267c371e8e5b5bc33867.zip
Adding upstream version 11.0.1.upstream/11.0.1
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/expand_laterals.sql40
-rw-r--r--tests/fixtures/optimizer/optimizer.sql23
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql57
3 files changed, 102 insertions, 18 deletions
diff --git a/tests/fixtures/optimizer/expand_laterals.sql b/tests/fixtures/optimizer/expand_laterals.sql
new file mode 100644
index 0000000..09bbd0f
--- /dev/null
+++ b/tests/fixtures/optimizer/expand_laterals.sql
@@ -0,0 +1,40 @@
+# title: expand alias reference
+SELECT
+ x.a + 1 AS i,
+ i + 1 AS j,
+ j + 1 AS k
+FROM x;
+SELECT
+ x.a + 1 AS i,
+ x.a + 1 + 1 AS j,
+ x.a + 1 + 1 + 1 AS k
+FROM x;
+
+# title: noop - reference comes before alias
+SELECT
+ b + 1 AS j,
+ x.a + 1 AS i
+FROM x;
+SELECT
+ b + 1 AS j,
+ x.a + 1 AS i
+FROM x;
+
+
+# title: subquery
+SELECT
+ *
+FROM (
+ SELECT
+ x.a + 1 AS i,
+ i + 1 AS j
+ FROM x
+);
+SELECT
+ *
+FROM (
+ SELECT
+ x.a + 1 AS i,
+ x.a + 1 + 1 AS j
+ FROM x
+);
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 664b3c7..9c14ec1 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -1,14 +1,20 @@
# title: lateral
# execute: false
SELECT a, m FROM z LATERAL VIEW EXPLODE([1, 2]) q AS m;
+WITH "z_2" AS (
+ SELECT
+ "z"."a" AS "a"
+ FROM "z" AS "z"
+)
SELECT
"z"."a" AS "a",
"q"."m" AS "m"
-FROM "z" AS "z"
+FROM "z_2" AS "z"
LATERAL VIEW
EXPLODE(ARRAY(1, 2)) q AS "m";
# title: unnest
+# execute: false
SELECT x FROM UNNEST([1, 2]) AS q(x, y);
SELECT
"q"."x" AS "x"
@@ -369,3 +375,18 @@ SELECT
FROM "x" AS "x"
RIGHT JOIN "y_2" AS "y"
ON "x"."a" = "y"."b";
+
+
+# title: lateral column alias reference
+SELECT x.a + 1 AS c, c + 1 AS d FROM x;
+SELECT
+ "x"."a" + 1 AS "c",
+ "x"."a" + 2 AS "d"
+FROM "x" AS "x";
+
+# title: column reference takes priority over lateral column alias reference
+SELECT x.a + 1 AS b, b + 1 AS c FROM x;
+SELECT
+ "x"."a" + 1 AS "b",
+ "x"."b" + 1 AS "c"
+FROM "x" AS "x";
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index ee041e2..141f028 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -104,14 +104,6 @@ SELECT x.a AS a FROM x AS x ORDER BY x.b;
SELECT SUM(a) AS a FROM x ORDER BY SUM(a);
SELECT SUM(x.a) AS a FROM x AS x ORDER BY SUM(x.a);
-# dialect: bigquery
-SELECT ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) AS row_num FROM x QUALIFY row_num = 1;
-SELECT ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) AS row_num FROM x AS x QUALIFY row_num = 1;
-
-# dialect: bigquery
-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;
@@ -199,15 +191,6 @@ 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);
-
--------------------------------------
-- Expand *
--------------------------------------
@@ -302,3 +285,43 @@ SELECT COALESCE(x.b, y.b, z.b) AS b FROM x AS x JOIN y AS y ON x.b = y.b JOIN z
# 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;
+
+--------------------------------------
+-- UDTF
+--------------------------------------
+# execute: false
+SELECT c FROM x LATERAL VIEW EXPLODE (a) AS c;
+SELECT _q_0.c AS c FROM x AS x LATERAL VIEW EXPLODE(x.a) _q_0 AS c;
+
+# execute: false
+SELECT c FROM xx LATERAL VIEW EXPLODE (a) AS c;
+SELECT _q_0.c AS c FROM xx AS xx LATERAL VIEW EXPLODE(xx.a) _q_0 AS c;
+
+# execute: false
+SELECT c FROM x LATERAL VIEW EXPLODE (a) t AS c;
+SELECT t.c AS c FROM x AS x LATERAL VIEW EXPLODE(x.a) t AS c;
+
+# 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);
+
+# 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;
+
+--------------------------------------
+-- Window functions
+--------------------------------------
+
+-- ORDER BY in window function
+SELECT a + 1 AS a, ROW_NUMBER() OVER (PARTITION BY b ORDER BY a) AS row_num FROM x;
+SELECT x.a + 1 AS a, ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a) AS row_num FROM x AS x;
+
+# dialect: bigquery
+SELECT ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) AS row_num FROM x QUALIFY row_num = 1;
+SELECT ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) AS row_num FROM x AS x QUALIFY row_num = 1;
+
+# dialect: bigquery
+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;