summaryrefslogtreecommitdiffstats
path: root/tests/fixtures
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures')
-rw-r--r--tests/fixtures/identity.sql15
-rw-r--r--tests/fixtures/optimizer/eliminate_subqueries.sql4
-rw-r--r--tests/fixtures/optimizer/optimize_joins.sql12
-rw-r--r--tests/fixtures/optimizer/optimizer.sql37
-rw-r--r--tests/fixtures/optimizer/pushdown_predicates.sql8
-rw-r--r--tests/fixtures/optimizer/simplify.sql15
6 files changed, 82 insertions, 9 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index b21d65d..0953fee 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -139,6 +139,7 @@ x ILIKE '%y%' ESCAPE '\'
1 AS escape
INTERVAL '1' day
INTERVAL '1' MONTH
+INTERVAL '1' YEAR
INTERVAL '-1' CURRENT_DATE
INTERVAL '-31' CAST(GETDATE() AS DATE)
INTERVAL (1 + 3) DAYS
@@ -620,13 +621,13 @@ CREATE FUNCTION a() LANGUAGE sql
CREATE FUNCTION a() LANGUAGE sql RETURNS INT
CREATE FUNCTION a.b(x INT) RETURNS INT AS RETURN x + 1
CREATE FUNCTION a.b.c()
-CREATE INDEX abc ON t (a)
-CREATE INDEX "abc" ON t (a)
-CREATE INDEX abc ON t (a, b, b)
-CREATE INDEX abc ON t (a NULLS LAST)
+CREATE INDEX abc ON t(a)
+CREATE INDEX "abc" ON t(a)
+CREATE INDEX abc ON t(a, b, b)
+CREATE INDEX abc ON t(a NULLS LAST)
CREATE INDEX pointloc ON points USING GIST(BOX(location, location))
-CREATE UNIQUE INDEX abc ON t (a, b, b)
-CREATE UNIQUE INDEX IF NOT EXISTS my_idx ON tbl (a, b)
+CREATE UNIQUE INDEX abc ON t(a, b, b)
+CREATE UNIQUE INDEX IF NOT EXISTS my_idx ON tbl(a, b)
CREATE SCHEMA x
CREATE SCHEMA IF NOT EXISTS y
CREATE DATABASE x
@@ -836,6 +837,7 @@ JSON_OBJECT('x': NULL, 'y': 1 WITH UNIQUE KEYS)
JSON_OBJECT('x': NULL, 'y': 1 ABSENT ON NULL WITH UNIQUE KEYS)
JSON_OBJECT('x': 1 RETURNING VARCHAR(100))
JSON_OBJECT('x': 1 RETURNING VARBINARY FORMAT JSON ENCODING UTF8)
+PRIOR AS x
SELECT if.x
SELECT NEXT VALUE FOR db.schema.sequence_name
SELECT NEXT VALUE FOR db.schema.sequence_name OVER (ORDER BY foo), col
@@ -855,3 +857,4 @@ SELECT * FROM (tbl1 CROSS JOIN (SELECT * FROM tbl2) AS t1)
/* comment1 */ DELETE FROM x /* comment2 */ WHERE y > 1
/* comment */ CREATE TABLE foo AS SELECT 1
SELECT next, transform, if
+SELECT "any", "case", "if", "next"
diff --git a/tests/fixtures/optimizer/eliminate_subqueries.sql b/tests/fixtures/optimizer/eliminate_subqueries.sql
index 8542c3e..e18d2a4 100644
--- a/tests/fixtures/optimizer/eliminate_subqueries.sql
+++ b/tests/fixtures/optimizer/eliminate_subqueries.sql
@@ -97,3 +97,7 @@ WITH cte1 AS (SELECT a FROM x), cte2 AS (SELECT a FROM cte1 AS cte3) SELECT a FR
-- 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);
+
+-- Wrapped subquery with redundant parentheses
+SELECT * FROM (((SELECT * FROM tbl)));
+WITH cte AS (SELECT * FROM tbl) SELECT * FROM cte AS cte;
diff --git a/tests/fixtures/optimizer/optimize_joins.sql b/tests/fixtures/optimizer/optimize_joins.sql
index b8e39c3..6d5bed2 100644
--- a/tests/fixtures/optimizer/optimize_joins.sql
+++ b/tests/fixtures/optimizer/optimize_joins.sql
@@ -14,7 +14,7 @@ SELECT * FROM x INNER JOIN z ON x.id = z.id;
SELECT * FROM x JOIN z ON x.id = z.id;
SELECT * FROM x LEFT OUTER JOIN z;
-SELECT * FROM x LEFT JOIN z;
+SELECT * FROM x LEFT JOIN z ON TRUE;
SELECT * FROM x CROSS JOIN z;
SELECT * FROM x CROSS JOIN z;
@@ -22,11 +22,17 @@ SELECT * FROM x CROSS JOIN z;
SELECT * FROM x JOIN z;
SELECT * FROM x CROSS JOIN z;
+SELECT * FROM x FULL JOIN z;
+SELECT * FROM x FULL JOIN z ON TRUE;
+
SELECT * FROM x NATURAL JOIN z;
-SELECT * FROM x NATURAL JOIN z;
+SELECT * FROM x NATURAL JOIN z ON TRUE;
SELECT * FROM x RIGHT JOIN z;
-SELECT * FROM x RIGHT JOIN z;
+SELECT * FROM x RIGHT JOIN z ON TRUE;
SELECT * FROM x JOIN z USING (id);
SELECT * FROM x JOIN z USING (id);
+
+SELECT * FROM x CROSS JOIN z ON TRUE;
+SELECT * FROM x CROSS JOIN z;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index b318a92..18ee804 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -950,3 +950,40 @@ JOIN "n" AS "foo"("a")
SELECT CONCAT('a', 'b') || CONCAT(CONCAT('c', 'd'), CONCAT('e', 'f')) + ('g' || 'h' || 'i');
SELECT
'abcdefghi' AS "_col_0";
+
+# title: complex query with derived tables and redundant parentheses
+# execute: false
+# dialect: snowflake
+SELECT
+ ("SUBQUERY_0"."KEY") AS "SUBQUERY_1_COL_0"
+FROM
+ (
+ SELECT
+ *
+ FROM
+ (((
+ SELECT
+ *
+ FROM
+ (
+ SELECT
+ event_name AS key,
+ insert_ts
+ FROM
+ (
+ SELECT
+ insert_ts,
+ event_name
+ FROM
+ sales
+ WHERE
+ insert_ts > '2023-08-07 21:03:35.590 -0700'
+ )
+ )
+ ))) AS "SF_CONNECTOR_QUERY_ALIAS"
+ ) AS "SUBQUERY_0";
+SELECT
+ "SALES"."EVENT_NAME" AS "SUBQUERY_1_COL_0"
+FROM "SALES" AS "SALES"
+WHERE
+ "SALES"."INSERT_TS" > '2023-08-07 21:03:35.590 -0700';
diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql
index 79ce353..cfa69fb 100644
--- a/tests/fixtures/optimizer/pushdown_predicates.sql
+++ b/tests/fixtures/optimizer/pushdown_predicates.sql
@@ -36,3 +36,11 @@ WITH t1 AS (SELECT x.a, x.b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) A
WITH m AS (SELECT a, b FROM (VALUES (1, 2)) AS a1(a, b)), n AS (SELECT a, b FROM m WHERE m.a = 1), o AS (SELECT a, b FROM m WHERE m.a = 2) SELECT n.a, n.b, n.a, o.b FROM n FULL OUTER JOIN o ON n.a = o.a;
WITH m AS (SELECT a, b FROM (VALUES (1, 2)) AS a1(a, b)), n AS (SELECT a, b FROM m WHERE m.a = 1), o AS (SELECT a, b FROM m WHERE m.a = 2) SELECT n.a, n.b, n.a, o.b FROM n FULL OUTER JOIN o ON n.a = o.a;
+
+-- Pushdown predicate to HAVING (CNF)
+SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt FROM x AS x) AS x WHERE x.cnt > 0;
+SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt FROM x AS x HAVING COUNT(1) > 0) AS x WHERE TRUE;
+
+-- Pushdown predicate to HAVING (DNF)
+SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt, COUNT(x.a) AS cnt_a, COUNT(x.b) AS cnt_b FROM x AS x) AS x WHERE (x.cnt_a > 0 AND x.cnt_b > 0) OR x.cnt > 0;
+SELECT x.cnt AS cnt FROM (SELECT COUNT(1) AS cnt, COUNT(x.a) AS cnt_a, COUNT(x.b) AS cnt_b FROM x AS x HAVING COUNT(1) > 0 OR (COUNT(x.a) > 0 AND COUNT(x.b) > 0)) AS x WHERE x.cnt > 0 OR (x.cnt_a > 0 AND x.cnt_b > 0); \ No newline at end of file
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index 3ed02cd..66fb19c 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -264,6 +264,9 @@ TRUE;
(FALSE);
FALSE;
+((TRUE));
+TRUE;
+
(FALSE OR TRUE);
TRUE;
@@ -288,6 +291,9 @@ x = y AND z;
x * (1 - y);
x * (1 - y);
+(((x % 20) = 0) = TRUE);
+((x % 20) = 0) = TRUE;
+
--------------------------------------
-- Literals
--------------------------------------
@@ -612,6 +618,9 @@ TRUE;
x = 2018 OR x <> 2018;
x <> 2018 OR x = 2018;
+t0.x = t1.x AND t0.y < t1.y AND t0.y <= t1.y;
+t0.x = t1.x AND t0.y < t1.y AND t0.y <= t1.y;
+
--------------------------------------
-- Coalesce
--------------------------------------
@@ -645,6 +654,12 @@ x = 1 OR x IS NULL;
COALESCE(x, 1) IS NULL;
FALSE;
+COALESCE(ROW() OVER (), 1) = 1;
+ROW() OVER () = 1 OR ROW() OVER () IS NULL;
+
+a AND b AND COALESCE(ROW() OVER (), 1) = 1;
+a AND b AND (ROW() OVER () = 1 OR ROW() OVER () IS NULL);
+
--------------------------------------
-- CONCAT
--------------------------------------