From 918abde014f9e5c75dfbe21110c379f7f70435c9 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 12 Feb 2023 11:06:28 +0100 Subject: Merging upstream version 11.0.1. Signed-off-by: Daniel Baumann --- tests/fixtures/optimizer/expand_laterals.sql | 40 +++++++++++++++++++ tests/fixtures/optimizer/optimizer.sql | 23 ++++++++++- tests/fixtures/optimizer/qualify_columns.sql | 57 +++++++++++++++++++--------- 3 files changed, 102 insertions(+), 18 deletions(-) create mode 100644 tests/fixtures/optimizer/expand_laterals.sql (limited to 'tests/fixtures/optimizer') diff --git a/tests/fixtures/optimizer/expand_laterals.sql b/tests/fixtures/optimizer/expand_laterals.sql new file mode 100644 index 0000000..09bbd0f --- /dev/null +++ b/tests/fixtures/optimizer/expand_laterals.sql @@ -0,0 +1,40 @@ +# title: expand alias reference +SELECT + x.a + 1 AS i, + i + 1 AS j, + j + 1 AS k +FROM x; +SELECT + x.a + 1 AS i, + x.a + 1 + 1 AS j, + x.a + 1 + 1 + 1 AS k +FROM x; + +# title: noop - reference comes before alias +SELECT + b + 1 AS j, + x.a + 1 AS i +FROM x; +SELECT + b + 1 AS j, + x.a + 1 AS i +FROM x; + + +# title: subquery +SELECT + * +FROM ( + SELECT + x.a + 1 AS i, + i + 1 AS j + FROM x +); +SELECT + * +FROM ( + SELECT + x.a + 1 AS i, + x.a + 1 + 1 AS j + FROM x +); diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index 664b3c7..9c14ec1 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -1,14 +1,20 @@ # title: lateral # execute: false SELECT a, m FROM z LATERAL VIEW EXPLODE([1, 2]) q AS m; +WITH "z_2" AS ( + SELECT + "z"."a" AS "a" + FROM "z" AS "z" +) SELECT "z"."a" AS "a", "q"."m" AS "m" -FROM "z" AS "z" +FROM "z_2" AS "z" LATERAL VIEW EXPLODE(ARRAY(1, 2)) q AS "m"; # title: unnest +# execute: false SELECT x FROM UNNEST([1, 2]) AS q(x, y); SELECT "q"."x" AS "x" @@ -369,3 +375,18 @@ SELECT FROM "x" AS "x" RIGHT JOIN "y_2" AS "y" ON "x"."a" = "y"."b"; + + +# title: lateral column alias reference +SELECT x.a + 1 AS c, c + 1 AS d FROM x; +SELECT + "x"."a" + 1 AS "c", + "x"."a" + 2 AS "d" +FROM "x" AS "x"; + +# title: column reference takes priority over lateral column alias reference +SELECT x.a + 1 AS b, b + 1 AS c FROM x; +SELECT + "x"."a" + 1 AS "b", + "x"."b" + 1 AS "c" +FROM "x" AS "x"; diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index ee041e2..141f028 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -104,14 +104,6 @@ SELECT x.a AS a FROM x AS x ORDER BY x.b; SELECT SUM(a) AS a FROM x ORDER BY SUM(a); SELECT SUM(x.a) AS a FROM x AS x ORDER BY SUM(x.a); -# dialect: bigquery -SELECT ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) AS row_num FROM x QUALIFY row_num = 1; -SELECT ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) AS row_num FROM x AS x QUALIFY row_num = 1; - -# dialect: bigquery -SELECT x.b, x.a FROM x LEFT JOIN y ON x.b = y.b QUALIFY ROW_NUMBER() OVER(PARTITION BY x.b ORDER BY x.a DESC) = 1; -SELECT x.b AS b, x.a AS a FROM x AS x LEFT JOIN y AS y ON x.b = y.b QUALIFY ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a DESC) = 1; - # execute: false SELECT AGGREGATE(ARRAY(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; @@ -199,15 +191,6 @@ SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT x.b AS b FROM y AS x); SELECT a FROM x AS i WHERE b IN (SELECT b FROM y AS j WHERE j.b IN (SELECT c FROM y AS k WHERE k.b = j.b)); SELECT i.a AS a FROM x AS i WHERE i.b IN (SELECT j.b AS b FROM y AS j WHERE j.b IN (SELECT k.c AS c FROM y AS k WHERE k.b = j.b)); -# execute: false -# dialect: bigquery -SELECT aa FROM x, UNNEST(a) AS aa; -SELECT aa AS aa FROM x AS x, UNNEST(x.a) AS aa; - -# execute: false -SELECT aa FROM x, UNNEST(a) AS t(aa); -SELECT t.aa AS aa FROM x AS x, UNNEST(x.a) AS t(aa); - -------------------------------------- -- Expand * -------------------------------------- @@ -302,3 +285,43 @@ SELECT COALESCE(x.b, y.b, z.b) AS b FROM x AS x JOIN y AS y ON x.b = y.b JOIN z # dialect: spark SELECT /*+ BROADCAST(y) */ x.b FROM x JOIN y ON x.b = y.b; SELECT /*+ BROADCAST(y) */ x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b; + +-------------------------------------- +-- UDTF +-------------------------------------- +# execute: false +SELECT c FROM x LATERAL VIEW EXPLODE (a) AS c; +SELECT _q_0.c AS c FROM x AS x LATERAL VIEW EXPLODE(x.a) _q_0 AS c; + +# execute: false +SELECT c FROM xx LATERAL VIEW EXPLODE (a) AS c; +SELECT _q_0.c AS c FROM xx AS xx LATERAL VIEW EXPLODE(xx.a) _q_0 AS c; + +# execute: false +SELECT c FROM x LATERAL VIEW EXPLODE (a) t AS c; +SELECT t.c AS c FROM x AS x LATERAL VIEW EXPLODE(x.a) t AS c; + +# execute: false +SELECT aa FROM x, UNNEST(a) AS t(aa); +SELECT t.aa AS aa FROM x AS x, UNNEST(x.a) AS t(aa); + +# execute: false +# dialect: bigquery +SELECT aa FROM x, UNNEST(a) AS aa; +SELECT aa AS aa FROM x AS x, UNNEST(x.a) AS aa; + +-------------------------------------- +-- Window functions +-------------------------------------- + +-- 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; + +# dialect: bigquery +SELECT ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) AS row_num FROM x QUALIFY row_num = 1; +SELECT ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) AS row_num FROM x AS x QUALIFY row_num = 1; + +# dialect: bigquery +SELECT x.b, x.a FROM x LEFT JOIN y ON x.b = y.b QUALIFY ROW_NUMBER() OVER(PARTITION BY x.b ORDER BY x.a DESC) = 1; +SELECT x.b AS b, x.a AS a FROM x AS x LEFT JOIN y AS y ON x.b = y.b QUALIFY ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a DESC) = 1; -- cgit v1.2.3