From ca57dc468e5d8d0920e964d45ad25271ae6e633d Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Mon, 24 Jul 2023 10:03:45 +0200 Subject: Adding upstream version 17.7.0. Signed-off-by: Daniel Baumann --- tests/fixtures/identity.sql | 3 + tests/fixtures/optimizer/eliminate_subqueries.sql | 4 ++ tests/fixtures/optimizer/merge_subqueries.sql | 24 +++++++ tests/fixtures/optimizer/optimizer.sql | 84 ++++++++++++++++++++++ tests/fixtures/optimizer/pushdown_projections.sql | 9 +-- tests/fixtures/optimizer/qualify_columns.sql | 44 +++++++++++- .../optimizer/qualify_columns__invalid.sql | 2 +- tests/fixtures/optimizer/qualify_tables.sql | 8 +++ 8 files changed, 168 insertions(+), 10 deletions(-) (limited to 'tests/fixtures') diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index 5b2c29c..b460c15 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -76,6 +76,7 @@ map.x SELECT update SELECT x.update SELECT call.x +SELECT end a.b.INT(1.234) INT(x / 100) time * 100 @@ -269,6 +270,8 @@ SELECT x LIKE '%x%' FROM test SELECT * FROM test LIMIT 100 SELECT * FROM test LIMIT 1 + 1 SELECT * FROM test LIMIT 100 OFFSET 200 +SELECT * FROM test LIMIT (SELECT 1) +SELECT * FROM test LIMIT (SELECT 1) OFFSET (SELECT 1) SELECT * FROM test FETCH FIRST ROWS ONLY SELECT * FROM test FETCH FIRST 1 ROWS ONLY SELECT * FROM test ORDER BY id DESC FETCH FIRST 10 ROWS WITH TIES diff --git a/tests/fixtures/optimizer/eliminate_subqueries.sql b/tests/fixtures/optimizer/eliminate_subqueries.sql index 4c79c22..8542c3e 100644 --- a/tests/fixtures/optimizer/eliminate_subqueries.sql +++ b/tests/fixtures/optimizer/eliminate_subqueries.sql @@ -93,3 +93,7 @@ WITH cte2 AS (SELECT a FROM x), cte1 AS (SELECT t.a FROM cte2 AS t) SELECT a FRO -- Duplicate CTE nested in CTE WITH cte1 AS (SELECT a FROM x), cte2 AS (WITH cte3 AS (SELECT a FROM x) SELECT a FROM cte3) SELECT a FROM cte2; WITH cte1 AS (SELECT a FROM x), cte2 AS (SELECT a FROM cte1 AS cte3) SELECT a FROM cte2; + +-- Wrapped subquery joined with table +SELECT * FROM ((SELECT c FROM t1) JOIN t2); +WITH cte AS (SELECT c FROM t1) SELECT * FROM (cte AS cte, t2); diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql index fb69ea7..b89e2fb 100644 --- a/tests/fixtures/optimizer/merge_subqueries.sql +++ b/tests/fixtures/optimizer/merge_subqueries.sql @@ -372,3 +372,27 @@ 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; + +# title: Outer scope selects from wrapped table with a join (unknown schema) +# execute: false +WITH _q_0 AS (SELECT t1.c AS c FROM t1 AS t1) SELECT * FROM (_q_0 AS _q_0 CROSS JOIN t2 AS t2); +WITH _q_0 AS (SELECT t1.c AS c FROM t1 AS t1) SELECT * FROM (_q_0 AS _q_0 CROSS JOIN t2 AS t2); + +# title: Outer scope selects single column from wrapped table with a join +WITH _q_0 AS ( + SELECT + x.a AS a + FROM x AS x +), y_2 AS ( + SELECT + y.b AS b + FROM y AS y +) +SELECT + y.b AS b +FROM ( + _q_0 AS _q_0 + JOIN y_2 AS y + ON _q_0.a = y.b +); +SELECT y.b AS b FROM (x AS x JOIN y AS y ON x.a = y.b); diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index 4b58ea5..14f5cfe 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -806,3 +806,87 @@ LEFT JOIN ( ON "y"."c" = "z"."c" ) ON "x"."b" = "y"."b"; + +# title: select * from wrapped subquery +# execute: false +SELECT * FROM ((SELECT * FROM tbl)); +WITH "_q_0" AS ( + SELECT + * + FROM "tbl" AS "tbl" +) +SELECT + * +FROM ( + "_q_0" AS "_q_0" +); + +# title: select * from wrapped subquery joined to a table (known schema) +SELECT * FROM ((SELECT * FROM x) INNER JOIN y ON a = c); +SELECT + "x"."a" AS "a", + "x"."b" AS "b", + "y"."b" AS "b", + "y"."c" AS "c" +FROM ( + "x" AS "x" + JOIN "y" AS "y" + ON "x"."a" = "y"."c" +); + +# title: select * from wrapped subquery joined to a table (unknown schema) +# execute: false +SELECT * FROM ((SELECT c FROM t1) JOIN t2); +WITH "_q_0" AS ( + SELECT + "t1"."c" AS "c" + FROM "t1" AS "t1" +) +SELECT + * +FROM ( + "_q_0" AS "_q_0" + CROSS JOIN "t2" AS "t2" +); + +# title: select specific columns from wrapped subquery joined to a table +SELECT b FROM ((SELECT a FROM x) INNER JOIN y ON a = b); +SELECT + "y"."b" AS "b" +FROM ( + "x" AS "x" + JOIN "y" AS "y" + ON "x"."a" = "y"."b" +); + +# title: select * from wrapped join of subqueries (unknown schema) +# execute: false +SELECT * FROM ((SELECT * FROM t1) JOIN (SELECT * FROM t2)); +WITH "_q_0" AS ( + SELECT + * + FROM "t1" AS "t1" +), "_q_1" AS ( + SELECT + * + FROM "t2" AS "t2" +) +SELECT + * +FROM ( + "_q_0" AS "_q_0" + CROSS JOIN "_q_1" AS "_q_1" +); + +# title: select * from wrapped join of subqueries (known schema) +SELECT * FROM ((SELECT * FROM x) INNER JOIN (SELECT * FROM y) ON a = c); +SELECT + "x"."a" AS "a", + "x"."b" AS "b", + "y"."b" AS "b", + "y"."c" AS "c" +FROM ( + "x" AS "x" + JOIN "y" AS "y" + ON "x"."a" = "y"."c" +); diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql index 0821339..86dea8c 100644 --- a/tests/fixtures/optimizer/pushdown_projections.sql +++ b/tests/fixtures/optimizer/pushdown_projections.sql @@ -16,9 +16,6 @@ SELECT x.a AS a FROM x AS x JOIN (SELECT y.b AS b FROM y AS y) AS z ON x.b = z.b SELECT x1.a FROM (SELECT * FROM x) AS x1, (SELECT * FROM x) AS x2; SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS _ FROM x AS x) AS x2; -SELECT x1.a FROM (SELECT * FROM x) AS x1, (SELECT * FROM x) AS x2; -SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS _ FROM x AS x) AS x2; - SELECT a FROM (SELECT DISTINCT a, b FROM x); SELECT _q_0.a AS a FROM (SELECT DISTINCT x.a AS a, x.b AS b FROM x AS x) AS _q_0; @@ -77,9 +74,6 @@ SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa UNION ALL SELECT bb.a AS SELECT a FROM (SELECT a FROM aa UNION ALL SELECT * FROM bb UNION ALL SELECT * from cc); SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa UNION ALL SELECT bb.a AS a FROM bb AS bb UNION ALL SELECT cc.a AS a FROM cc AS cc) AS _q_0; -SELECT a FROM (SELECT * FROM aa UNION ALL SELECT * FROM bb UNION ALL SELECT * from cc); -SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa UNION ALL SELECT bb.a AS a FROM bb AS bb UNION ALL SELECT cc.a AS a FROM cc AS cc) AS _q_0; - SELECT a FROM (SELECT * FROM aa CROSS JOIN bb); SELECT _q_0.a AS a FROM (SELECT a AS a FROM aa AS aa CROSS JOIN bb AS bb) AS _q_0; @@ -91,3 +85,6 @@ SELECT _q_1.a AS a FROM (SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa with cte1 as (SELECT cola, colb FROM tb UNION ALL SELECT colc, cold FROM tb2) SELECT cola FROM cte1; WITH cte1 AS (SELECT tb.cola AS cola FROM tb AS tb UNION ALL SELECT tb2.colc AS colc FROM tb2 AS tb2) SELECT cte1.cola AS cola FROM cte1; + +SELECT * FROM ((SELECT c FROM t1) JOIN t2); +SELECT * FROM ((SELECT t1.c AS c FROM t1 AS t1) AS _q_0, t2 AS t2); diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 487a831..90505ac 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -307,6 +307,11 @@ SELECT x.b AS b, y.b AS b, y.c AS c FROM x AS x, y AS y; SELECT * EXCEPT(a) FROM x; SELECT x.b AS b FROM x AS x; +# execute: false +# note: this query would fail in the engine level because there are 0 selected columns +SELECT * EXCEPT (a, b) FROM x; +SELECT * EXCEPT (x.a, x.b) FROM x AS x; + -------------------------------------- -- Using -------------------------------------- @@ -410,8 +415,7 @@ SELECT _q_0.c AS c FROM (SELECT 1 AS a) AS x LATERAL VIEW EXPLODE(x.a) _q_0 AS c -------------------------------------- -- Window functions -------------------------------------- - --- ORDER BY in window function +# title: ORDER BY in window function SELECT a + 1 AS a, ROW_NUMBER() OVER (PARTITION BY b ORDER BY a) AS row_num FROM x; SELECT x.a + 1 AS a, ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a) AS row_num FROM x AS x; @@ -429,7 +433,6 @@ SELECT x.a AS a, x.b AS b FROM x AS x QUALIFY COUNT(x.a) OVER (PARTITION BY x.b) -------------------------------------- -- Expand laterals -------------------------------------- - # execute: false select 2 AS d, d + 1 FROM x WHERE d = 2 GROUP BY d; SELECT 2 AS d, 2 + 1 AS _col_1 FROM x AS x WHERE 2 = 2 GROUP BY 1; @@ -457,3 +460,38 @@ FROM ( FROM x ); SELECT _q_0.i AS i, _q_0.j AS j FROM (SELECT x.a + 1 AS i, x.a + 1 + 1 AS j FROM x AS x) AS _q_0; + +-------------------------------------- +-- Wrapped tables / join constructs +-------------------------------------- +# execute: false +SELECT * FROM ((tbl)); +SELECT * FROM ((tbl AS tbl)); + +SELECT a, c FROM (x LEFT JOIN y ON a = c); +SELECT x.a AS a, y.c AS c FROM (x AS x LEFT JOIN y AS y ON x.a = y.c); + +# execute: false +SELECT * FROM ((a CROSS JOIN ((b CROSS JOIN c) CROSS JOIN (d CROSS JOIN e)))); +SELECT * FROM ((a AS a CROSS JOIN ((b AS b CROSS JOIN c AS c) CROSS JOIN (d AS d CROSS JOIN e AS e)))); + +# execute: false +SELECT * FROM ((SELECT * FROM tbl)); +SELECT * FROM ((SELECT * FROM tbl AS tbl) AS _q_0); + +# execute: false +SELECT * FROM ((SELECT c FROM t1) JOIN t2); +SELECT * FROM ((SELECT t1.c AS c FROM t1 AS t1) AS _q_0, t2 AS t2); + +# execute: false +SELECT * FROM ((SELECT * FROM x) INNER JOIN y ON a = c); +SELECT y.b AS b, y.c AS c, _q_0.a AS a, _q_0.b AS b FROM ((SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0 INNER JOIN y AS y ON _q_0.a = y.c); + +SELECT x.a, y.b, z.c FROM x LEFT JOIN (y INNER JOIN z ON y.c = z.c) ON x.b = y.b; +SELECT x.a AS a, y.b AS b, z.c AS c FROM x AS x LEFT JOIN (y AS y INNER JOIN z AS z ON y.c = z.c) ON x.b = y.b; + +SELECT * FROM ((SELECT * FROM x) INNER JOIN (SELECT * FROM y) ON a = c); +SELECT _q_0.a AS a, _q_0.b AS b, _q_1.b AS b, _q_1.c AS c FROM ((SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0 INNER JOIN (SELECT y.b AS b, y.c AS c FROM y AS y) AS _q_1 ON _q_0.a = _q_1.c); + +SELECT b FROM ((SELECT a FROM x) INNER JOIN y ON a = b); +SELECT y.b AS b FROM ((SELECT x.a AS a FROM x AS x) AS _q_0 INNER JOIN y AS y ON _q_0.a = y.b); diff --git a/tests/fixtures/optimizer/qualify_columns__invalid.sql b/tests/fixtures/optimizer/qualify_columns__invalid.sql index d2d4959..f3d8b6a 100644 --- a/tests/fixtures/optimizer/qualify_columns__invalid.sql +++ b/tests/fixtures/optimizer/qualify_columns__invalid.sql @@ -10,4 +10,4 @@ SELECT b FROM x AS a CROSS JOIN y AS b CROSS JOIN y AS c; SELECT x.a FROM x JOIN y USING (a); SELECT a, SUM(b) FROM x GROUP BY 3; SELECT p FROM (SELECT x from xx) y CROSS JOIN yy CROSS JOIN zz -select a from (select * from x cross join y); +SELECT a FROM (SELECT * FROM x CROSS JOIN y); diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql index cd749a0..f43ac01 100644 --- a/tests/fixtures/optimizer/qualify_tables.sql +++ b/tests/fixtures/optimizer/qualify_tables.sql @@ -101,3 +101,11 @@ SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN c.db.tbl2 AS tbl2 ON id1 = i # title: join construct within join construct SELECT * FROM (tbl1 AS tbl1 JOIN (tbl2 AS tbl2 JOIN tbl3 AS tbl3 ON id2 = id3) AS _q_0 ON id1 = id3) AS _q_1; SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN (SELECT * FROM c.db.tbl2 AS tbl2 JOIN c.db.tbl3 AS tbl3 ON id2 = id3) AS _q_0 ON id1 = id3) AS _q_1; + +# title: wrapped subquery without alias +SELECT * FROM ((SELECT * FROM t)); +SELECT * FROM ((SELECT * FROM c.db.t AS t) AS _q_0); + +# title: wrapped subquery without alias joined with a table +SELECT * FROM ((SELECT * FROM t1) INNER JOIN t2 ON a = b); +SELECT * FROM ((SELECT * FROM c.db.t1 AS t1) AS _q_0 INNER JOIN c.db.t2 AS t2 ON a = b); -- cgit v1.2.3