summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer/unnest_subqueries.sql
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures/optimizer/unnest_subqueries.sql')
-rw-r--r--tests/fixtures/optimizer/unnest_subqueries.sql312
1 files changed, 69 insertions, 243 deletions
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index 45e462b..a5a35b1 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -1,243 +1,69 @@
---SELECT x.a > (SELECT SUM(y.a) AS b FROM y) FROM x;
---------------------------------------
--- Unnest Subqueries
---------------------------------------
-SELECT *
-FROM x AS x
-WHERE
- x.a = (SELECT SUM(y.a) AS a FROM y)
- AND x.a IN (SELECT y.a AS a FROM y)
- AND x.a IN (SELECT y.b AS b FROM y)
- AND x.a = ANY (SELECT y.a AS a FROM y)
- AND x.a = (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a)
- AND x.a > (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a)
- AND x.a <> ANY (SELECT y.a AS a FROM y WHERE y.a = x.a)
- AND x.a NOT IN (SELECT y.a AS a FROM y WHERE y.a = x.a)
- AND x.a IN (SELECT y.a AS a FROM y WHERE y.b = x.a)
- AND x.a < (SELECT SUM(y.a) AS a FROM y WHERE y.a = x.a and y.a = x.b and y.b <> x.d)
- AND EXISTS (SELECT y.a AS a, y.b AS b FROM y WHERE x.a = y.a)
- AND x.a IN (SELECT y.a AS a FROM y LIMIT 10)
- AND x.a IN (SELECT y.a AS a FROM y OFFSET 10)
- AND x.a IN (SELECT y.a AS a, y.b AS b FROM y)
- 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)
- AND x.a = SUM(SELECT 1) -- invalid statement left alone
- AND x.a IN (SELECT max(y.b) AS b FROM y GROUP BY y.a)
-;
-SELECT
- *
-FROM x AS x
-CROSS JOIN (
- SELECT
- SUM(y.a) AS a
- FROM y
-) AS _u_0
-LEFT JOIN (
- SELECT
- y.a AS a
- FROM y
- GROUP BY
- y.a
-) AS _u_1
- ON x.a = _u_1.a
-LEFT JOIN (
- SELECT
- y.b AS b
- FROM y
- GROUP BY
- y.b
-) AS _u_2
- ON x.a = _u_2.b
-LEFT JOIN (
- SELECT
- y.a AS a
- FROM y
- GROUP BY
- y.a
-) AS _u_3
- ON x.a = _u_3.a
-LEFT JOIN (
- SELECT
- SUM(y.b) AS b,
- y.a AS _u_5
- FROM y
- WHERE
- TRUE
- GROUP BY
- y.a
-) AS _u_4
- ON x.a = _u_4._u_5
-LEFT JOIN (
- SELECT
- SUM(y.b) AS b,
- y.a AS _u_7
- FROM y
- WHERE
- TRUE
- GROUP BY
- y.a
-) AS _u_6
- ON x.a = _u_6._u_7
-LEFT JOIN (
- SELECT
- y.a AS a
- FROM y
- WHERE
- TRUE
- GROUP BY
- y.a
-) AS _u_8
- ON _u_8.a = x.a
-LEFT JOIN (
- SELECT
- y.a AS a
- FROM y
- WHERE
- TRUE
- GROUP BY
- y.a
-) AS _u_9
- ON _u_9.a = x.a
-LEFT JOIN (
- SELECT
- ARRAY_AGG(y.a) AS a,
- y.b AS _u_11
- FROM y
- WHERE
- TRUE
- GROUP BY
- y.b
-) AS _u_10
- ON _u_10._u_11 = x.a
-LEFT JOIN (
- SELECT
- SUM(y.a) AS a,
- y.a AS _u_13,
- ARRAY_AGG(y.b) AS _u_14
- FROM y
- WHERE
- TRUE AND TRUE AND TRUE
- GROUP BY
- y.a
-) AS _u_12
- ON _u_12._u_13 = x.a AND _u_12._u_13 = x.b
-LEFT JOIN (
- SELECT
- y.a AS a
- FROM y
- WHERE
- TRUE
- GROUP BY
- 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
-LEFT JOIN (
- SELECT
- _q.b
- FROM (
- SELECT
- MAX(y.b) AS b
- FROM y
- GROUP BY
- y.a
- ) AS _q
- GROUP BY
- _q.b
-) AS _u_24
- ON x.a = _u_24.b
-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_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
- FROM y
- LIMIT 10
- )
- AND x.a IN (
- SELECT
- y.a AS a
- FROM y
- OFFSET 10
- )
- AND x.a IN (
- SELECT
- y.a AS a,
- y.b AS b
- FROM y
- )
- 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 ARRAY_ALL(_u_19."", _x -> _x = x.a)
- AND x.a > COALESCE(_u_21.d, 0)
- AND x.a = SUM(SELECT
- 1) /* invalid statement left alone */
- AND NOT _u_24.b IS NULL
-;
-SELECT
- CAST((
- SELECT
- x.a AS a
- FROM x
- ) AS TEXT) AS a;
-SELECT
- CAST((
- SELECT
- x.a AS a
- FROM x
- ) AS TEXT) AS a;
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.a) AS a FROM y);
+SELECT * FROM x CROSS JOIN (SELECT SUM(y.a) AS a FROM y) AS _u_0 WHERE x.a = _u_0.a;
+
+SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE NOT _u_0.a IS NULL;
+
+SELECT * FROM x WHERE x.a IN (SELECT y.b AS b FROM y);
+SELECT * FROM x LEFT JOIN (SELECT y.b AS b FROM y GROUP BY y.b) AS _u_0 ON x.a = _u_0.b WHERE NOT _u_0.b IS NULL;
+
+SELECT * FROM x WHERE x.a = ANY (SELECT y.a AS a FROM y);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE NOT _u_0.a IS NULL;
+
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a);
+SELECT * FROM x LEFT JOIN (SELECT SUM(y.b) AS b, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0._u_1 WHERE x.a = _u_0.b;
+
+SELECT * FROM x WHERE x.a > (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a);
+SELECT * FROM x LEFT JOIN (SELECT SUM(y.b) AS b, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0._u_1 WHERE x.a > _u_0.b;
+
+SELECT * FROM x WHERE x.a <> ANY (SELECT y.a AS a FROM y WHERE y.a = x.a);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0.a = x.a WHERE x.a <> _u_0.a;
+
+SELECT * FROM x WHERE x.a NOT IN (SELECT y.a AS a FROM y WHERE y.a = x.a);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0.a = x.a WHERE NOT x.a = _u_0.a;
+
+SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y WHERE y.b = x.a);
+SELECT * FROM x LEFT JOIN (SELECT ARRAY_AGG(y.a) AS a, y.b AS _u_1 FROM y WHERE TRUE GROUP BY y.b) AS _u_0 ON _u_0._u_1 = x.a WHERE ARRAY_ANY(_u_0.a, _x -> _x = x.a);
+
+SELECT * FROM x WHERE x.a < (SELECT SUM(y.a) AS a FROM y WHERE y.a = x.a and y.a = x.b and y.b <> x.d);
+SELECT * FROM x LEFT JOIN (SELECT SUM(y.a) AS a, y.a AS _u_1, ARRAY_AGG(y.b) AS _u_2 FROM y WHERE TRUE AND TRUE AND TRUE GROUP BY y.a) AS _u_0 ON _u_0._u_1 = x.a AND _u_0._u_1 = x.b WHERE (x.a < _u_0.a AND ARRAY_ANY(_u_0._u_2, _x -> _x <> x.d));
+
+SELECT * FROM x WHERE EXISTS (SELECT y.a AS a, y.b AS b FROM y WHERE x.a = y.a);
+SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE NOT _u_0.a IS NULL;
+
+SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y LIMIT 10);
+SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y LIMIT 10);
+
+SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a FROM y OFFSET 10);
+SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a FROM y OFFSET 10);
+
+SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a, y.b AS b FROM y);
+SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a, y.b AS b FROM y);
+
+SELECT * FROM x.a WHERE x.a > ANY (SELECT y.a FROM y);
+SELECT * FROM x.a WHERE x.a > ANY (SELECT y.a FROM y);
+
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a LIMIT 10);
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a LIMIT 10);
+
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10);
+SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10);
+
+SELECT * FROM x WHERE x.a > ALL (SELECT y.c AS c FROM y WHERE y.a = x.a);
+SELECT * FROM x LEFT JOIN (SELECT ARRAY_AGG(y.c) AS c, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0._u_1 = x.a WHERE ARRAY_ALL(_u_0.c, _x -> x.a > _x);
+
+SELECT * FROM x WHERE x.a > (SELECT COUNT(*) as d FROM y WHERE y.a = x.a);
+SELECT * FROM x LEFT JOIN (SELECT COUNT(*) AS d, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0._u_1 = x.a WHERE x.a > COALESCE(_u_0.d, 0);
+
+# title: invalid statement left alone
+SELECT * FROM x WHERE x.a = SUM(SELECT 1);
+SELECT * FROM x WHERE x.a = SUM(SELECT 1);
+
+SELECT * FROM x WHERE x.a IN (SELECT max(y.b) AS b FROM y GROUP BY y.a);
+SELECT * FROM x LEFT JOIN (SELECT _q.b AS b FROM (SELECT MAX(y.b) AS b FROM y GROUP BY y.a) AS _q GROUP BY _q.b) AS _u_0 ON x.a = _u_0.b WHERE NOT _u_0.b IS NULL;
+
+SELECT x.a > (SELECT SUM(y.a) AS b FROM y) FROM x;
+SELECT x.a > _u_0.b FROM x CROSS JOIN (SELECT SUM(y.a) AS b FROM y) AS _u_0;
+
+SELECT (SELECT MAX(t2.c1) AS c1 FROM t2 WHERE t2.c2 = t1.c2 AND t2.c3 <= TRUNC(t1.c3)) AS c FROM t1;
+SELECT _u_0.c1 AS c FROM t1 LEFT JOIN (SELECT MAX(t2.c1) AS c1, t2.c2 AS _u_1, MAX(t2.c3) AS _u_2 FROM t2 WHERE TRUE AND TRUE GROUP BY t2.c2) AS _u_0 ON _u_0._u_1 = t1.c2 WHERE _u_0._u_2 <= TRUNC(t1.c3);
generated by cgit v1.2.3 (git 2.39.1) at 2024-09-23 09:57:59 +0000