From 379c6d1f52e1d311867c4f789dc389da1d9af898 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 6 Aug 2023 09:48:11 +0200 Subject: Merging upstream version 17.9.1. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/qualify_columns_ddl.sql | 35 ++++++++++++++++++++++++ 1 file changed, 35 insertions(+) create mode 100644 tests/fixtures/optimizer/qualify_columns_ddl.sql (limited to 'tests/fixtures/optimizer/qualify_columns_ddl.sql') diff --git a/tests/fixtures/optimizer/qualify_columns_ddl.sql b/tests/fixtures/optimizer/qualify_columns_ddl.sql new file mode 100644 index 0000000..87e0f6d --- /dev/null +++ b/tests/fixtures/optimizer/qualify_columns_ddl.sql @@ -0,0 +1,35 @@ +# title: Create with CTE +WITH cte AS (SELECT b FROM y) CREATE TABLE s AS SELECT * FROM cte; +WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT cte.b AS b FROM cte; + +# title: Create without CTE +CREATE TABLE foo AS SELECT a FROM tbl; +CREATE TABLE foo AS SELECT tbl.a AS a FROM tbl AS tbl; + +# title: Create with complex CTE with derived table +WITH cte AS (SELECT a FROM (SELECT a from x)) CREATE TABLE s AS SELECT * FROM cte; +WITH cte AS (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) CREATE TABLE s AS SELECT cte.a AS a FROM cte; + +# title: Create wtih multiple CTEs +WITH cte1 AS (SELECT b FROM y), cte2 AS (SELECT b FROM cte1) CREATE TABLE s AS SELECT * FROM cte2; +WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1) CREATE TABLE s AS SELECT cte2.b AS b FROM cte2; + +# title: Create with multiple CTEs, selecting only from the first CTE (unnecessary code) +WITH cte1 AS (SELECT b FROM y), cte2 AS (SELECT b FROM cte1) CREATE TABLE s AS SELECT * FROM cte1; +WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1) CREATE TABLE s AS SELECT cte1.b AS b FROM cte1; + +# title: Create with multiple derived tables +CREATE TABLE s AS SELECT * FROM (SELECT b FROM (SELECT b FROM y)); +CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT y.b AS b FROM y AS y) AS _q_0) AS _q_1; + +# title: Create with a CTE and a derived table +WITH cte AS (SELECT b FROM y) CREATE TABLE s AS SELECT * FROM (SELECT b FROM (SELECT b FROM cte)); +WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT cte.b AS b FROM cte) AS _q_0) AS _q_1; + +# title: Insert with CTE +WITH cte AS (SELECT b FROM y) INSERT INTO s SELECT * FROM cte; +WITH cte AS (SELECT y.b AS b FROM y AS y) INSERT INTO s SELECT cte.b AS b FROM cte; + +# title: Insert without CTE +INSERT INTO foo SELECT a FROM tbl; +INSERT INTO foo SELECT tbl.a AS a FROM tbl AS tbl; -- cgit v1.2.3