From 3517257d82008871c863f71a402742556dacc5cf Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Mon, 10 Jul 2023 07:36:25 +0200 Subject: Adding upstream version 17.3.0. Signed-off-by: Daniel Baumann --- tests/fixtures/identity.sql | 2 +- tests/fixtures/optimizer/qualify_tables.sql | 74 ++++++++++++++++++++++++++--- 2 files changed, 68 insertions(+), 8 deletions(-) (limited to 'tests/fixtures') diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index 162d627..63631c4 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -423,7 +423,6 @@ SELECT 1 INTERSECT SELECT 2 SELECT 1 INTERSECT SELECT 2 SELECT 1 AS delete, 2 AS alter SELECT * FROM (x) -SELECT * FROM ((x)) SELECT * FROM ((SELECT 1)) SELECT * FROM (x CROSS JOIN foo LATERAL VIEW EXPLODE(y)) SELECT * FROM (SELECT 1) AS x @@ -838,3 +837,4 @@ SELECT * FROM schema.case SELECT * FROM current_date SELECT * FROM schema.current_date SELECT /*+ SOME_HINT(foo) */ 1 +SELECT * FROM (tbl1 CROSS JOIN (SELECT * FROM tbl2) AS t1) diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql index 24d1b65..d8ce2b0 100644 --- a/tests/fixtures/optimizer/qualify_tables.sql +++ b/tests/fixtures/optimizer/qualify_tables.sql @@ -1,43 +1,103 @@ +# title: single table SELECT 1 FROM z; SELECT 1 FROM c.db.z AS z; +# title: single table with db SELECT 1 FROM y.z; SELECT 1 FROM c.y.z AS z; +# title: single table with db, catalog SELECT 1 FROM x.y.z; SELECT 1 FROM x.y.z AS z; +# title: single table with db, catalog, alias SELECT 1 FROM x.y.z AS z; SELECT 1 FROM x.y.z AS z; +# title: cte can't be qualified WITH a AS (SELECT 1 FROM z) SELECT 1 FROM a; WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a; +# title: query that yields a single column as projection SELECT (SELECT y.c FROM y AS y) FROM x; SELECT (SELECT y.c FROM c.db.y AS y) FROM c.db.x AS x; +# title: pivoted table SELECT * FROM x PIVOT (SUM(a) FOR b IN ('a', 'b')); SELECT * FROM c.db.x AS x PIVOT(SUM(a) FOR b IN ('a', 'b')) AS _q_0; ----------------------------- --- Expand join constructs ----------------------------- +----------------------------------------------------------- +--- Unnest wrapped tables / joins, expand join constructs +----------------------------------------------------------- --- This is valid in Trino, so we treat the (tbl AS tbl) as a "join construct" per postgres' terminology. -SELECT * FROM (tbl AS tbl) AS _q_0; -SELECT * FROM (SELECT * FROM c.db.tbl AS tbl) AS _q_0; +# title: wrapped table without alias +SELECT * FROM (tbl); +SELECT * FROM c.db.tbl AS tbl; + +# title: wrapped table with alias +SELECT * FROM (tbl AS tbl); +SELECT * FROM c.db.tbl AS tbl; + +# title: wrapped table with alias and multiple redundant parentheses +SELECT * FROM ((((tbl AS tbl)))); +SELECT * FROM c.db.tbl AS tbl; + +# title: chained wrapped joins without aliases (1) +SELECT * FROM ((a CROSS JOIN b) CROSS JOIN c); +SELECT * FROM c.db.a AS a CROSS JOIN c.db.b AS b CROSS JOIN c.db.c AS c; + +# title: chained wrapped joins without aliases (2) +SELECT * FROM (a CROSS JOIN (b CROSS JOIN c)); +SELECT * FROM c.db.a AS a CROSS JOIN c.db.b AS b CROSS JOIN c.db.c AS c; + +# title: chained wrapped joins without aliases (3) +SELECT * FROM ((a CROSS JOIN ((b CROSS JOIN c) CROSS JOIN d))); +SELECT * FROM c.db.a AS a CROSS JOIN c.db.b AS b CROSS JOIN c.db.c AS c CROSS JOIN c.db.d AS d; + +# title: chained wrapped joins without aliases (4) +SELECT * FROM ((a CROSS JOIN ((b CROSS JOIN c) CROSS JOIN (d CROSS JOIN e)))); +SELECT * FROM c.db.a AS a CROSS JOIN c.db.b AS b CROSS JOIN c.db.c AS c CROSS JOIN c.db.d AS d CROSS JOIN c.db.e AS e; + +# title: chained wrapped joins with aliases +SELECT * FROM ((a AS foo CROSS JOIN b AS bar) CROSS JOIN c AS baz); +SELECT * FROM c.db.a AS foo CROSS JOIN c.db.b AS bar CROSS JOIN c.db.c AS baz; + +# title: wrapped join with subquery without alias +SELECT * FROM (tbl1 CROSS JOIN (SELECT * FROM tbl2) AS t1); +SELECT * FROM c.db.tbl1 AS tbl1 CROSS JOIN (SELECT * FROM c.db.tbl2 AS tbl2) AS t1; + +# title: wrapped join with subquery with alias, parentheses can't be omitted because of alias +SELECT * FROM (tbl1 CROSS JOIN (SELECT * FROM tbl2) AS t1) AS t2; +SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 CROSS JOIN (SELECT * FROM c.db.tbl2 AS tbl2) AS t1) AS t2; + +# title: join construct as the right operand of a left join +SELECT * FROM a LEFT JOIN (b INNER JOIN c ON c.id = b.id) ON b.id = a.id; +SELECT * FROM c.db.a AS a LEFT JOIN c.db.b AS b ON b.id = a.id INNER JOIN c.db.c AS c ON c.id = b.id; + +# title: nested joins converted to canonical form +SELECT * FROM a LEFT JOIN b INNER JOIN c ON c.id = b.id ON b.id = a.id; +SELECT * FROM c.db.a AS a LEFT JOIN c.db.b AS b ON b.id = a.id INNER JOIN c.db.c AS c ON c.id = b.id; + +# title: parentheses can't be omitted because alias shadows inner table names +SELECT t.a FROM (tbl AS tbl) AS t; +SELECT t.a FROM (SELECT * FROM c.db.tbl AS tbl) AS t; +# title: outermost set of parentheses can't be omitted due to shadowing (1) SELECT * FROM ((tbl AS tbl)) AS _q_0; SELECT * FROM (SELECT * FROM c.db.tbl AS tbl) AS _q_0; -SELECT * FROM (((tbl AS tbl))) AS _q_0; +# title: outermost set of parentheses can't be omitted due to shadowing (2) +SELECT * FROM ((((tbl AS tbl)))) AS _q_0; SELECT * FROM (SELECT * FROM c.db.tbl AS tbl) AS _q_0; +# title: join construct with three tables in canonical form SELECT * FROM (tbl1 AS tbl1 JOIN tbl2 AS tbl2 ON id1 = id2 JOIN tbl3 AS tbl3 ON id1 = id3) AS _q_0; SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN c.db.tbl2 AS tbl2 ON id1 = id2 JOIN c.db.tbl3 AS tbl3 ON id1 = id3) AS _q_0; +# title: join construct with three tables in canonical form and redundant set of parentheses SELECT * FROM ((tbl1 AS tbl1 JOIN tbl2 AS tbl2 ON id1 = id2 JOIN tbl3 AS tbl3 ON id1 = id3)) AS _q_0; SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN c.db.tbl2 AS tbl2 ON id1 = id2 JOIN c.db.tbl3 AS tbl3 ON id1 = id3) AS _q_0; +# title: nested join construct in canonical form 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; -- cgit v1.2.3