diff options
Diffstat (limited to 'tests/fixtures/optimizer/unnest_subqueries.sql')
-rw-r--r-- | tests/fixtures/optimizer/unnest_subqueries.sql | 206 |
1 files changed, 206 insertions, 0 deletions
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql new file mode 100644 index 0000000..9c4bd27 --- /dev/null +++ b/tests/fixtures/optimizer/unnest_subqueries.sql @@ -0,0 +1,206 @@ +-------------------------------------- +-- Unnest Subqueries +-------------------------------------- +SELECT * +FROM x AS x +WHERE + 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) +; +SELECT + * +FROM x AS x +LEFT JOIN ( + SELECT + y.a AS a + FROM y + GROUP BY + y.a +) AS "_u_0" + ON x.a = "_u_0"."a" +LEFT JOIN ( + SELECT + y.b AS b + FROM y + GROUP BY + y.b +) AS "_u_1" + ON x.a = "_u_1"."b" +LEFT JOIN ( + SELECT + y.a AS a + FROM y + GROUP BY + y.a +) AS "_u_2" + ON x.a = "_u_2"."a" +LEFT JOIN ( + SELECT + SUM(y.b) AS b, + y.a AS _u_4 + FROM y + WHERE + TRUE + GROUP BY + y.a +) AS "_u_3" + ON x.a = "_u_3"."_u_4" +LEFT JOIN ( + SELECT + SUM(y.b) AS b, + y.a AS _u_6 + FROM y + WHERE + TRUE + GROUP BY + y.a +) AS "_u_5" + ON x.a = "_u_5"."_u_6" +LEFT JOIN ( + SELECT + y.a AS a + FROM y + WHERE + TRUE + GROUP BY + y.a +) AS "_u_7" + ON "_u_7".a = x.a +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 + ARRAY_AGG(y.a) AS a, + y.b AS _u_10 + FROM y + WHERE + TRUE + GROUP BY + y.b +) AS "_u_9" + ON "_u_9"."_u_10" = x.a +LEFT JOIN ( + SELECT + SUM(y.a) AS a, + y.a AS _u_12, + ARRAY_AGG(y.b) AS _u_13 + FROM y + WHERE + TRUE + AND TRUE + AND TRUE + GROUP BY + y.a +) AS "_u_11" + ON "_u_11"."_u_12" = x.a + AND "_u_11"."_u_12" = x.b +LEFT JOIN ( + SELECT + y.a AS a + FROM y + WHERE + TRUE + GROUP BY + y.a +) AS "_u_14" + ON x.a = "_u_14".a +WHERE + NOT "_u_0"."a" IS NULL + AND NOT "_u_1"."b" IS NULL + AND NOT "_u_2"."a" IS NULL + AND ( + x.a = "_u_3".b + AND NOT "_u_3"."_u_4" IS NULL + ) + AND ( + x.a > "_u_5".b + AND NOT "_u_5"."_u_6" IS NULL + ) + AND ( + None = "_u_7".a + AND NOT "_u_7".a IS NULL + ) + AND NOT ( + x.a = "_u_8".a + AND NOT "_u_8".a IS NULL + ) + AND ( + ARRAY_ANY("_u_9".a, _x -> _x = x.a) + AND NOT "_u_9"."_u_10" IS NULL + ) + AND ( + ( + ( + x.a < "_u_11".a + AND NOT "_u_11"."_u_12" IS NULL + ) + AND NOT "_u_11"."_u_12" IS NULL + ) + AND ARRAY_ANY("_u_11"."_u_13", "_x" -> "_x" <> x.d) + ) + AND ( + NOT "_u_14".a IS NULL + AND NOT "_u_14".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 + ); + |