From 90150543f9314be683d22a16339effd774192f6d Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Thu, 22 Sep 2022 06:31:28 +0200 Subject: Merging upstream version 6.1.1. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/merge_derived_tables.sql | 63 +++++++++++++++++++++++ 1 file changed, 63 insertions(+) create mode 100644 tests/fixtures/optimizer/merge_derived_tables.sql (limited to 'tests/fixtures/optimizer/merge_derived_tables.sql') diff --git a/tests/fixtures/optimizer/merge_derived_tables.sql b/tests/fixtures/optimizer/merge_derived_tables.sql new file mode 100644 index 0000000..c5aa7e9 --- /dev/null +++ b/tests/fixtures/optimizer/merge_derived_tables.sql @@ -0,0 +1,63 @@ +-- Simple +SELECT a, b FROM (SELECT a, b FROM x); +SELECT x.a AS a, x.b AS b FROM x AS x; + +-- Inner table alias is merged +SELECT a, b FROM (SELECT a, b FROM x AS q) AS r; +SELECT q.a AS a, q.b AS b FROM x AS q; + +-- Double nesting +SELECT a, b FROM (SELECT a, b FROM (SELECT a, b FROM x)); +SELECT x.a AS a, x.b AS b FROM x AS x; + +-- WHERE clause is merged +SELECT a, SUM(b) FROM (SELECT a, b FROM x WHERE a > 1) GROUP BY a; +SELECT x.a AS a, SUM(x.b) AS "_col_1" FROM x AS x WHERE x.a > 1 GROUP BY x.a; + +-- Outer query has join +SELECT a, c FROM (SELECT a, b FROM x WHERE a > 1) AS x JOIN y ON x.b = y.b; +SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b WHERE x.a > 1; + +-- Join on derived table +SELECT a, c FROM x JOIN (SELECT b, c FROM y) AS y ON x.b = y.b; +SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b; + +-- Inner query has a join +SELECT a, c FROM (SELECT a, c FROM x JOIN y ON x.b = y.b); +SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b; + +-- Inner query has conflicting name in outer query +SELECT a, c FROM (SELECT q.a, q.b FROM x AS q) AS x JOIN y AS q ON x.b = q.b; +SELECT q_2.a AS a, q.c AS c FROM x AS q_2 JOIN y AS q ON q_2.b = q.b; + +-- Inner query has conflicting name in joined source +SELECT x.a, q.c FROM (SELECT a, x.b FROM x JOIN y AS q ON x.b = q.b) AS x JOIN y AS q ON x.b = q.b; +SELECT x.a AS a, q.c AS c FROM x AS x JOIN y AS q_2 ON x.b = q_2.b JOIN y AS q ON x.b = q.b; + +-- Inner query has multiple conflicting names +SELECT x.a, q.c, r.c FROM (SELECT q.a, r.b FROM x AS q JOIN y AS r ON q.b = r.b) AS x JOIN y AS q ON x.b = q.b JOIN y AS r ON x.b = r.b; +SELECT q_2.a AS a, q.c AS c, r.c AS c FROM x AS q_2 JOIN y AS r_2 ON q_2.b = r_2.b JOIN y AS q ON r_2.b = q.b JOIN y AS r ON r_2.b = r.b; + +-- Inner queries have conflicting names with each other +SELECT r.b FROM (SELECT b FROM x AS x) AS q JOIN (SELECT b FROM x) AS r ON q.b = r.b; +SELECT x_2.b AS b FROM x AS x JOIN x AS x_2 ON x.b = x_2.b; + +-- WHERE clause in joined derived table is merged +SELECT x.a, y.c FROM x JOIN (SELECT b, c FROM y WHERE c > 1) AS y; +SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y WHERE y.c > 1; + +-- WHERE clause in outer joined derived table is merged to ON clause +SELECT x.a, y.c FROM x LEFT JOIN (SELECT b, c FROM y WHERE c > 1) AS y; +SELECT x.a AS a, y.c AS c FROM x AS x LEFT JOIN y AS y ON y.c > 1; + +-- Comma JOIN in outer query +SELECT x.a, y.c FROM (SELECT a FROM x) AS x, (SELECT c FROM y) AS y; +SELECT x.a AS a, y.c AS c FROM x AS x, y AS y; + +-- Comma JOIN in inner query +SELECT x.a, x.c FROM (SELECT x.a, z.c FROM x, y AS z) AS x; +SELECT x.a AS a, z.c AS c FROM x AS x CROSS JOIN y AS z; + +-- (Regression) Column in ORDER BY +SELECT * FROM (SELECT * FROM (SELECT * FROM x)) ORDER BY a LIMIT 1; +SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY x.a LIMIT 1; -- cgit v1.2.3