diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2023-01-17 10:32:12 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2023-01-17 10:32:12 +0000 |
commit | 244a05de60c9417daab9528b51788c3d2a00dc5f (patch) | |
tree | 89a9c82aa41d397e1b81c320ad7a287b6c80f313 /tests/fixtures/optimizer/unnest_subqueries.sql | |
parent | Adding upstream version 10.4.2. (diff) | |
download | sqlglot-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/unnest_subqueries.sql')
-rw-r--r-- | tests/fixtures/optimizer/unnest_subqueries.sql | 59 |
1 files changed, 34 insertions, 25 deletions
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); |