From 83c68af3fef95831b1d7fce36c532825246937b4 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Tue, 4 Oct 2022 11:37:06 +0200 Subject: Adding upstream version 6.2.8. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/merge_subqueries.sql | 8 ++++++++ tests/fixtures/optimizer/optimizer.sql | 17 +++++++++++++++++ tests/fixtures/optimizer/pushdown_projections.sql | 12 ++++++++++++ 3 files changed, 37 insertions(+) (limited to 'tests/fixtures/optimizer') diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql index 35aed3b..e13d3b3 100644 --- a/tests/fixtures/optimizer/merge_subqueries.sql +++ b/tests/fixtures/optimizer/merge_subqueries.sql @@ -97,3 +97,11 @@ WITH x AS (SELECT x.a AS a, x.b AS b FROM x AS x) SELECT x.a AS a, y.b AS b FROM -- Nested CTE SELECT * FROM (WITH x AS (SELECT a, b FROM x) SELECT a, b FROM x); SELECT x.a AS a, x.b AS b FROM x AS x; + +-- Inner select is an expression +SELECT a FROM (SELECT a FROM (SELECT COALESCE(a) AS a FROM x LEFT JOIN y ON x.a = y.b) AS x) AS x; +SELECT COALESCE(x.a) AS a FROM x AS x LEFT JOIN y AS y ON x.a = y.b; + +-- CTE select is an expression +WITH x AS (SELECT COALESCE(a) AS a FROM x LEFT JOIN y ON x.a = y.b) SELECT a FROM (SELECT a FROM x AS x) AS x; +SELECT COALESCE(x.a) AS a FROM x AS x LEFT JOIN y AS y ON x.a = y.b; diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index 0bb742b..eb6761a 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -137,3 +137,20 @@ SELECT AGGREGATE(ARRAY(x.a, x.b), 0, (x, acc) -> x + acc + a) AS sum_agg FROM x; SELECT AGGREGATE(ARRAY("x"."a", "x"."b"), 0, ("x", "acc") -> "x" + "acc" + "x"."a") AS "sum_agg" FROM "x" AS "x"; + +SELECT cola, colb FROM (VALUES (1, 'test'), (2, 'test2')) AS tab(cola, colb); +SELECT + "tab"."cola" AS "cola", + "tab"."colb" AS "colb" +FROM (VALUES + (1, 'test'), + (2, 'test2')) AS "tab"("cola", "colb"); + +# dialect: spark +SELECT cola, colb FROM (VALUES (1, 'test'), (2, 'test2')) AS tab(cola, colb); +SELECT + `tab`.`cola` AS `cola`, + `tab`.`colb` AS `colb` +FROM VALUES + (1, 'test'), + (2, 'test2') AS `tab`(`cola`, `colb`); diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql index 9deceb6..b03ffab 100644 --- a/tests/fixtures/optimizer/pushdown_projections.sql +++ b/tests/fixtures/optimizer/pushdown_projections.sql @@ -39,3 +39,15 @@ SELECT "_q_0".b AS b FROM (SELECT SUM(x.b) AS b FROM x AS x GROUP BY x.a) AS "_q SELECT b FROM (SELECT a, SUM(b) AS b FROM x ORDER BY a); SELECT "_q_0".b AS b FROM (SELECT x.a AS a, SUM(x.b) AS b FROM x AS x ORDER BY a) AS "_q_0"; + +SELECT x FROM (VALUES(1, 2)) AS q(x, y); +SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y); + +SELECT x FROM UNNEST([1, 2]) AS q(x, y); +SELECT q.x AS x FROM UNNEST(ARRAY(1, 2)) AS q(x, y); + +WITH t1 AS (SELECT cola, colb FROM UNNEST([STRUCT(1 AS cola, 'test' AS colb)]) AS "q"("cola", "colb")) SELECT cola FROM t1; +WITH t1 AS (SELECT q.cola AS cola FROM UNNEST(ARRAY(STRUCT(1 AS cola, 'test' AS colb))) AS "q"("cola", "colb")) SELECT t1.cola AS cola FROM t1; + +SELECT x FROM VALUES(1, 2) AS q(x, y); +SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y); -- cgit v1.2.3