From 918abde014f9e5c75dfbe21110c379f7f70435c9 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Sun, 12 Feb 2023 11:06:28 +0100
Subject: Merging upstream version 11.0.1.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
---
 tests/fixtures/optimizer/qualify_columns.sql | 57 +++++++++++++++++++---------
 1 file changed, 40 insertions(+), 17 deletions(-)

(limited to 'tests/fixtures/optimizer/qualify_columns.sql')

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

