summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-01-17 10:32:12 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-01-17 10:32:12 +0000
commit244a05de60c9417daab9528b51788c3d2a00dc5f (patch)
tree89a9c82aa41d397e1b81c320ad7a287b6c80f313 /tests/fixtures/optimizer
parentAdding upstream version 10.4.2. (diff)
downloadsqlglot-244a05de60c9417daab9528b51788c3d2a00dc5f.tar.xz
sqlglot-244a05de60c9417daab9528b51788c3d2a00dc5f.zip
Adding upstream version 10.5.2.upstream/10.5.2
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/merge_subqueries.sql39
-rw-r--r--tests/fixtures/optimizer/optimizer.sql2
-rw-r--r--tests/fixtures/optimizer/pushdown_projections.sql3
-rw-r--r--tests/fixtures/optimizer/simplify.sql12
-rw-r--r--tests/fixtures/optimizer/tpc-h/tpc-h.sql8
-rw-r--r--tests/fixtures/optimizer/unnest_subqueries.sql59
6 files changed, 91 insertions, 32 deletions
diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql
index 4a3ad4b..4c06e42 100644
--- a/tests/fixtures/optimizer/merge_subqueries.sql
+++ b/tests/fixtures/optimizer/merge_subqueries.sql
@@ -311,3 +311,42 @@ FROM
ON
t1.cola = t2.cola;
SELECT /*+ BROADCAST(a2) */ a1.cola AS cola, a2.cola AS cola FROM VALUES (1) AS a1(cola) JOIN VALUES (1) AS a2(cola) ON a1.cola = a2.cola;
+
+# title: Nested subquery selects from same table as another subquery
+WITH i AS (
+ SELECT
+ x.a AS a
+ FROM x AS x
+), j AS (
+ SELECT
+ x.a,
+ x.b
+ FROM x AS x
+), k AS (
+ SELECT
+ j.a,
+ j.b
+ FROM j AS j
+)
+SELECT
+ i.a,
+ k.b
+FROM i AS i
+LEFT JOIN k AS k
+ON i.a = k.a;
+SELECT x.a AS a, x_2.b AS b FROM x AS x LEFT JOIN x AS x_2 ON x.a = x_2.a;
+
+# title: Outer select joins on inner select join
+WITH i AS (
+ SELECT
+ x.a AS a
+ FROM y AS y
+ JOIN x AS x
+ ON y.b = x.b
+)
+SELECT
+ x.a AS a
+FROM x AS x
+LEFT JOIN i AS i
+ ON x.a = i.a;
+WITH i AS (SELECT x.a AS a FROM y AS y JOIN x AS x ON y.b = x.b) SELECT x.a AS a FROM x AS x LEFT JOIN i AS i ON x.a = i.a;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index b502d81..664b3c7 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -105,7 +105,7 @@ LEFT JOIN "_u_0" AS "_u_0"
JOIN "y" AS "y"
ON "x"."b" = "y"."b"
WHERE
- "_u_0"."_col_0" >= 0 AND "x"."a" > 1 AND NOT "_u_0"."_u_1" IS NULL
+ "_u_0"."_col_0" >= 0 AND "x"."a" > 1
GROUP BY
"x"."a";
diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql
index 2a21f65..b9f6c3f 100644
--- a/tests/fixtures/optimizer/pushdown_projections.sql
+++ b/tests/fixtures/optimizer/pushdown_projections.sql
@@ -54,3 +54,6 @@ WITH t1 AS (SELECT q.cola AS cola FROM UNNEST(ARRAY(STRUCT(1 AS cola, 'test' AS
SELECT x FROM VALUES(1, 2) AS q(x, y);
SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y);
+
+SELECT i.a FROM x AS i LEFT JOIN (SELECT a, b FROM (SELECT a, b FROM x)) AS j ON i.a = j.a;
+SELECT i.a AS a FROM x AS i LEFT JOIN (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) AS j ON i.a = j.a;
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index cf4195d..4e9e70c 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -375,6 +375,18 @@ CAST('1998-12-01' AS DATE) - INTERVAL '90' foo;
date '1998-12-01' + interval '90' foo;
CAST('1998-12-01' AS DATE) + INTERVAL '90' foo;
+CAST(x AS DATE) + interval '1' week;
+CAST(x AS DATE) + INTERVAL '1' week;
+
+CAST('2008-11-11' AS DATETIME) + INTERVAL '5' MONTH;
+CAST('2009-04-11 00:00:00' AS DATETIME);
+
+datetime '1998-12-01' - interval '90' day;
+CAST('1998-09-02 00:00:00' AS DATETIME);
+
+CAST(x AS DATETIME) + interval '1' week;
+CAST(x AS DATETIME) + INTERVAL '1' week;
+
--------------------------------------
-- Comparisons
--------------------------------------
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index 9c1f138..272fb26 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -150,7 +150,6 @@ WHERE
"part"."p_size" = 15
AND "part"."p_type" LIKE '%BRASS'
AND "partsupp"."ps_supplycost" = "_u_0"."_col_0"
- AND NOT "_u_0"."_u_1" IS NULL
ORDER BY
"s_acctbal" DESC,
"n_name",
@@ -1008,7 +1007,7 @@ JOIN "part" AS "part"
LEFT JOIN "_u_0" AS "_u_0"
ON "_u_0"."_u_1" = "part"."p_partkey"
WHERE
- "lineitem"."l_quantity" < "_u_0"."_col_0" AND NOT "_u_0"."_u_1" IS NULL;
+ "lineitem"."l_quantity" < "_u_0"."_col_0";
--------------------------------------
-- TPC-H 18
@@ -1253,10 +1252,7 @@ WITH "_u_0" AS (
LEFT JOIN "_u_3" AS "_u_3"
ON "partsupp"."ps_partkey" = "_u_3"."p_partkey"
WHERE
- "partsupp"."ps_availqty" > "_u_0"."_col_0"
- AND NOT "_u_0"."_u_1" IS NULL
- AND NOT "_u_0"."_u_2" IS NULL
- AND NOT "_u_3"."p_partkey" IS NULL
+ "partsupp"."ps_availqty" > "_u_0"."_col_0" AND NOT "_u_3"."p_partkey" IS NULL
GROUP BY
"partsupp"."ps_suppkey"
)
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index a444945..9d760e0 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -22,6 +22,8 @@ WHERE
AND x.a > ANY (SELECT y.a FROM y)
AND x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a LIMIT 10)
AND x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10)
+ AND x.a > ALL (SELECT y.c FROM y WHERE y.a = x.a)
+ AND x.a > (SELECT COUNT(*) as d FROM y WHERE y.a = x.a)
;
SELECT
*
@@ -130,37 +132,42 @@ LEFT JOIN (
y.a
) AS _u_15
ON x.a = _u_15.a
+LEFT JOIN (
+ SELECT
+ ARRAY_AGG(c),
+ y.a AS _u_20
+ FROM y
+ WHERE
+ TRUE
+ GROUP BY
+ y.a
+) AS _u_19
+ ON _u_19._u_20 = x.a
+LEFT JOIN (
+ SELECT
+ COUNT(*) AS d,
+ y.a AS _u_22
+ FROM y
+ WHERE
+ TRUE
+ GROUP BY
+ y.a
+) AS _u_21
+ ON _u_21._u_22 = x.a
WHERE
x.a = _u_0.a
AND NOT "_u_1"."a" IS NULL
AND NOT "_u_2"."b" IS NULL
AND NOT "_u_3"."a" IS NULL
+ AND x.a = _u_4.b
+ AND x.a > _u_6.b
+ AND x.a = _u_8.a
+ AND NOT x.a = _u_9.a
+ AND ARRAY_ANY(_u_10.a, _x -> _x = x.a)
AND (
- x.a = _u_4.b AND NOT _u_4._u_5 IS NULL
- )
- AND (
- x.a > _u_6.b AND NOT _u_6._u_7 IS NULL
- )
- AND (
- None = _u_8.a AND NOT _u_8.a IS NULL
- )
- AND NOT (
- x.a = _u_9.a AND NOT _u_9.a IS NULL
- )
- AND (
- ARRAY_ANY(_u_10.a, _x -> _x = x.a) AND NOT _u_10._u_11 IS NULL
- )
- AND (
- (
- (
- x.a < _u_12.a AND NOT _u_12._u_13 IS NULL
- ) AND NOT _u_12._u_13 IS NULL
- )
- AND ARRAY_ANY(_u_12._u_14, "_x" -> _x <> x.d)
- )
- AND (
- NOT _u_15.a IS NULL AND NOT _u_15.a IS NULL
+ x.a < _u_12.a AND ARRAY_ANY(_u_12._u_14, "_x" -> _x <> x.d)
)
+ AND NOT _u_15.a IS NULL
AND x.a IN (
SELECT
y.a AS a
@@ -199,4 +206,6 @@ WHERE
WHERE
y.a = x.a
OFFSET 10
- );
+ )
+ AND ARRAY_ALL(_u_19."", _x -> _x = x.a)
+ AND x.a > COALESCE(_u_21.d, 0);