summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-09-07 11:39:43 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-09-07 11:39:43 +0000
commit341eb1a6bdf0dd5b015e5140d3b068c6fd3f4d87 (patch)
tree61fb7eca2238fb5d41d3906f4af41de03abd25ea /tests/fixtures/optimizer
parentAdding upstream version 17.12.0. (diff)
downloadsqlglot-341eb1a6bdf0dd5b015e5140d3b068c6fd3f4d87.tar.xz
sqlglot-341eb1a6bdf0dd5b015e5140d3b068c6fd3f4d87.zip
Adding upstream version 18.2.0.upstream/18.2.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/fixtures/optimizer')
-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
5 files changed, 73 insertions, 3 deletions
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
--------------------------------------