summaryrefslogtreecommitdiffstats
path: root/tests/fixtures
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures')
-rw-r--r--tests/fixtures/identity.sql3
-rw-r--r--tests/fixtures/optimizer/eliminate_subqueries.sql4
-rw-r--r--tests/fixtures/optimizer/merge_subqueries.sql24
-rw-r--r--tests/fixtures/optimizer/optimizer.sql84
-rw-r--r--tests/fixtures/optimizer/pushdown_projections.sql9
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql44
-rw-r--r--tests/fixtures/optimizer/qualify_columns__invalid.sql2
-rw-r--r--tests/fixtures/optimizer/qualify_tables.sql8
8 files changed, 168 insertions, 10 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 5b2c29c..b460c15 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -76,6 +76,7 @@ map.x
SELECT update
SELECT x.update
SELECT call.x
+SELECT end
a.b.INT(1.234)
INT(x / 100)
time * 100
@@ -269,6 +270,8 @@ SELECT x LIKE '%x%' FROM test
SELECT * FROM test LIMIT 100
SELECT * FROM test LIMIT 1 + 1
SELECT * FROM test LIMIT 100 OFFSET 200
+SELECT * FROM test LIMIT (SELECT 1)
+SELECT * FROM test LIMIT (SELECT 1) OFFSET (SELECT 1)
SELECT * FROM test FETCH FIRST ROWS ONLY
SELECT * FROM test FETCH FIRST 1 ROWS ONLY
SELECT * FROM test ORDER BY id DESC FETCH FIRST 10 ROWS WITH TIES
diff --git a/tests/fixtures/optimizer/eliminate_subqueries.sql b/tests/fixtures/optimizer/eliminate_subqueries.sql
index 4c79c22..8542c3e 100644
--- a/tests/fixtures/optimizer/eliminate_subqueries.sql
+++ b/tests/fixtures/optimizer/eliminate_subqueries.sql
@@ -93,3 +93,7 @@ WITH cte2 AS (SELECT a FROM x), cte1 AS (SELECT t.a FROM cte2 AS t) SELECT a FRO
-- Duplicate CTE nested in CTE
WITH cte1 AS (SELECT a FROM x), cte2 AS (WITH cte3 AS (SELECT a FROM x) SELECT a FROM cte3) SELECT a FROM cte2;
WITH cte1 AS (SELECT a FROM x), cte2 AS (SELECT a FROM cte1 AS cte3) SELECT a FROM cte2;
+
+-- Wrapped subquery joined with table
+SELECT * FROM ((SELECT c FROM t1) JOIN t2);
+WITH cte AS (SELECT c FROM t1) SELECT * FROM (cte AS cte, t2);
diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql
index fb69ea7..b89e2fb 100644
--- a/tests/fixtures/optimizer/merge_subqueries.sql
+++ b/tests/fixtures/optimizer/merge_subqueries.sql
@@ -372,3 +372,27 @@ FROM x AS x
LEFT JOIN i AS i
ON x.a = i.a;
WITH i AS (SELECT x.a AS a FROM y AS y JOIN x AS x ON y.b = x.b) SELECT x.a AS a FROM x AS x LEFT JOIN i AS i ON x.a = i.a;
+
+# title: Outer scope selects from wrapped table with a join (unknown schema)
+# execute: false
+WITH _q_0 AS (SELECT t1.c AS c FROM t1 AS t1) SELECT * FROM (_q_0 AS _q_0 CROSS JOIN t2 AS t2);
+WITH _q_0 AS (SELECT t1.c AS c FROM t1 AS t1) SELECT * FROM (_q_0 AS _q_0 CROSS JOIN t2 AS t2);
+
+# title: Outer scope selects single column from wrapped table with a join
+WITH _q_0 AS (
+ SELECT
+ x.a AS a
+ FROM x AS x
+), y_2 AS (
+ SELECT
+ y.b AS b
+ FROM y AS y
+)
+SELECT
+ y.b AS b
+FROM (
+ _q_0 AS _q_0
+ JOIN y_2 AS y
+ ON _q_0.a = y.b
+);
+SELECT y.b AS b FROM (x AS x JOIN y AS y ON x.a = y.b);
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index 4b58ea5..14f5cfe 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -806,3 +806,87 @@ LEFT JOIN (
ON "y"."c" = "z"."c"
)
ON "x"."b" = "y"."b";
+
+# title: select * from wrapped subquery
+# execute: false
+SELECT * FROM ((SELECT * FROM tbl));
+WITH "_q_0" AS (
+ SELECT
+ *
+ FROM "tbl" AS "tbl"
+)
+SELECT
+ *
+FROM (
+ "_q_0" AS "_q_0"
+);
+
+# title: select * from wrapped subquery joined to a table (known schema)
+SELECT * FROM ((SELECT * FROM x) INNER JOIN y ON a = c);
+SELECT
+ "x"."a" AS "a",
+ "x"."b" AS "b",
+ "y"."b" AS "b",
+ "y"."c" AS "c"
+FROM (
+ "x" AS "x"
+ JOIN "y" AS "y"
+ ON "x"."a" = "y"."c"
+);
+
+# title: select * from wrapped subquery joined to a table (unknown schema)
+# execute: false
+SELECT * FROM ((SELECT c FROM t1) JOIN t2);
+WITH "_q_0" AS (
+ SELECT
+ "t1"."c" AS "c"
+ FROM "t1" AS "t1"
+)
+SELECT
+ *
+FROM (
+ "_q_0" AS "_q_0"
+ CROSS JOIN "t2" AS "t2"
+);
+
+# title: select specific columns from wrapped subquery joined to a table
+SELECT b FROM ((SELECT a FROM x) INNER JOIN y ON a = b);
+SELECT
+ "y"."b" AS "b"
+FROM (
+ "x" AS "x"
+ JOIN "y" AS "y"
+ ON "x"."a" = "y"."b"
+);
+
+# title: select * from wrapped join of subqueries (unknown schema)
+# execute: false
+SELECT * FROM ((SELECT * FROM t1) JOIN (SELECT * FROM t2));
+WITH "_q_0" AS (
+ SELECT
+ *
+ FROM "t1" AS "t1"
+), "_q_1" AS (
+ SELECT
+ *
+ FROM "t2" AS "t2"
+)
+SELECT
+ *
+FROM (
+ "_q_0" AS "_q_0"
+ CROSS JOIN "_q_1" AS "_q_1"
+);
+
+# title: select * from wrapped join of subqueries (known schema)
+SELECT * FROM ((SELECT * FROM x) INNER JOIN (SELECT * FROM y) ON a = c);
+SELECT
+ "x"."a" AS "a",
+ "x"."b" AS "b",
+ "y"."b" AS "b",
+ "y"."c" AS "c"
+FROM (
+ "x" AS "x"
+ JOIN "y" AS "y"
+ ON "x"."a" = "y"."c"
+);
diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql
index 0821339..86dea8c 100644
--- a/tests/fixtures/optimizer/pushdown_projections.sql
+++ b/tests/fixtures/optimizer/pushdown_projections.sql
@@ -16,9 +16,6 @@ SELECT x.a AS a FROM x AS x JOIN (SELECT y.b AS b FROM y AS y) AS z ON x.b = z.b
SELECT x1.a FROM (SELECT * FROM x) AS x1, (SELECT * FROM x) AS x2;
SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS _ FROM x AS x) AS x2;
-SELECT x1.a FROM (SELECT * FROM x) AS x1, (SELECT * FROM x) AS x2;
-SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS _ FROM x AS x) AS x2;
-
SELECT a FROM (SELECT DISTINCT a, b FROM x);
SELECT _q_0.a AS a FROM (SELECT DISTINCT x.a AS a, x.b AS b FROM x AS x) AS _q_0;
@@ -77,9 +74,6 @@ SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa UNION ALL SELECT bb.a AS
SELECT a FROM (SELECT a FROM aa UNION ALL SELECT * FROM bb UNION ALL SELECT * from cc);
SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa UNION ALL SELECT bb.a AS a FROM bb AS bb UNION ALL SELECT cc.a AS a FROM cc AS cc) AS _q_0;
-SELECT a FROM (SELECT * FROM aa UNION ALL SELECT * FROM bb UNION ALL SELECT * from cc);
-SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa UNION ALL SELECT bb.a AS a FROM bb AS bb UNION ALL SELECT cc.a AS a FROM cc AS cc) AS _q_0;
-
SELECT a FROM (SELECT * FROM aa CROSS JOIN bb);
SELECT _q_0.a AS a FROM (SELECT a AS a FROM aa AS aa CROSS JOIN bb AS bb) AS _q_0;
@@ -91,3 +85,6 @@ SELECT _q_1.a AS a FROM (SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa
with cte1 as (SELECT cola, colb FROM tb UNION ALL SELECT colc, cold FROM tb2) SELECT cola FROM cte1;
WITH cte1 AS (SELECT tb.cola AS cola FROM tb AS tb UNION ALL SELECT tb2.colc AS colc FROM tb2 AS tb2) SELECT cte1.cola AS cola FROM cte1;
+
+SELECT * FROM ((SELECT c FROM t1) JOIN t2);
+SELECT * FROM ((SELECT t1.c AS c FROM t1 AS t1) AS _q_0, t2 AS t2);
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 487a831..90505ac 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -307,6 +307,11 @@ SELECT x.b AS b, y.b AS b, y.c AS c FROM x AS x, y AS y;
SELECT * EXCEPT(a) FROM x;
SELECT x.b AS b FROM x AS x;
+# execute: false
+# note: this query would fail in the engine level because there are 0 selected columns
+SELECT * EXCEPT (a, b) FROM x;
+SELECT * EXCEPT (x.a, x.b) FROM x AS x;
+
--------------------------------------
-- Using
--------------------------------------
@@ -410,8 +415,7 @@ SELECT _q_0.c AS c FROM (SELECT 1 AS a) AS x LATERAL VIEW EXPLODE(x.a) _q_0 AS c
--------------------------------------
-- Window functions
--------------------------------------
-
--- ORDER BY in window function
+# title: 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;
@@ -429,7 +433,6 @@ SELECT x.a AS a, x.b AS b FROM x AS x QUALIFY COUNT(x.a) OVER (PARTITION BY x.b)
--------------------------------------
-- Expand laterals
--------------------------------------
-
# execute: false
select 2 AS d, d + 1 FROM x WHERE d = 2 GROUP BY d;
SELECT 2 AS d, 2 + 1 AS _col_1 FROM x AS x WHERE 2 = 2 GROUP BY 1;
@@ -457,3 +460,38 @@ FROM (
FROM x
);
SELECT _q_0.i AS i, _q_0.j AS j FROM (SELECT x.a + 1 AS i, x.a + 1 + 1 AS j FROM x AS x) AS _q_0;
+
+--------------------------------------
+-- Wrapped tables / join constructs
+--------------------------------------
+# execute: false
+SELECT * FROM ((tbl));
+SELECT * FROM ((tbl AS tbl));
+
+SELECT a, c FROM (x LEFT JOIN y ON a = c);
+SELECT x.a AS a, y.c AS c FROM (x AS x LEFT JOIN y AS y ON x.a = y.c);
+
+# execute: false
+SELECT * FROM ((a CROSS JOIN ((b CROSS JOIN c) CROSS JOIN (d CROSS JOIN e))));
+SELECT * FROM ((a AS a CROSS JOIN ((b AS b CROSS JOIN c AS c) CROSS JOIN (d AS d CROSS JOIN e AS e))));
+
+# execute: false
+SELECT * FROM ((SELECT * FROM tbl));
+SELECT * FROM ((SELECT * FROM tbl AS tbl) AS _q_0);
+
+# execute: false
+SELECT * FROM ((SELECT c FROM t1) JOIN t2);
+SELECT * FROM ((SELECT t1.c AS c FROM t1 AS t1) AS _q_0, t2 AS t2);
+
+# execute: false
+SELECT * FROM ((SELECT * FROM x) INNER JOIN y ON a = c);
+SELECT y.b AS b, y.c AS c, _q_0.a AS a, _q_0.b AS b FROM ((SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0 INNER JOIN y AS y ON _q_0.a = y.c);
+
+SELECT x.a, y.b, z.c FROM x LEFT JOIN (y INNER JOIN z ON y.c = z.c) ON x.b = y.b;
+SELECT x.a AS a, y.b AS b, z.c AS c FROM x AS x LEFT JOIN (y AS y INNER JOIN z AS z ON y.c = z.c) ON x.b = y.b;
+
+SELECT * FROM ((SELECT * FROM x) INNER JOIN (SELECT * FROM y) ON a = c);
+SELECT _q_0.a AS a, _q_0.b AS b, _q_1.b AS b, _q_1.c AS c FROM ((SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0 INNER JOIN (SELECT y.b AS b, y.c AS c FROM y AS y) AS _q_1 ON _q_0.a = _q_1.c);
+
+SELECT b FROM ((SELECT a FROM x) INNER JOIN y ON a = b);
+SELECT y.b AS b FROM ((SELECT x.a AS a FROM x AS x) AS _q_0 INNER JOIN y AS y ON _q_0.a = y.b);
diff --git a/tests/fixtures/optimizer/qualify_columns__invalid.sql b/tests/fixtures/optimizer/qualify_columns__invalid.sql
index d2d4959..f3d8b6a 100644
--- a/tests/fixtures/optimizer/qualify_columns__invalid.sql
+++ b/tests/fixtures/optimizer/qualify_columns__invalid.sql
@@ -10,4 +10,4 @@ SELECT b FROM x AS a CROSS JOIN y AS b CROSS JOIN y AS c;
SELECT x.a FROM x JOIN y USING (a);
SELECT a, SUM(b) FROM x GROUP BY 3;
SELECT p FROM (SELECT x from xx) y CROSS JOIN yy CROSS JOIN zz
-select a from (select * from x cross join y);
+SELECT a FROM (SELECT * FROM x CROSS JOIN y);
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
index cd749a0..f43ac01 100644
--- a/tests/fixtures/optimizer/qualify_tables.sql
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -101,3 +101,11 @@ SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN c.db.tbl2 AS tbl2 ON id1 = i
# title: join construct within join construct
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;
+
+# title: wrapped subquery without alias
+SELECT * FROM ((SELECT * FROM t));
+SELECT * FROM ((SELECT * FROM c.db.t AS t) AS _q_0);
+
+# title: wrapped subquery without alias joined with a table
+SELECT * FROM ((SELECT * FROM t1) INNER JOIN t2 ON a = b);
+SELECT * FROM ((SELECT * FROM c.db.t1 AS t1) AS _q_0 INNER JOIN c.db.t2 AS t2 ON a = b);
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-02 06:19:54 +0000