summaryrefslogtreecommitdiffstats
path: root/tests/fixtures
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures')
-rw-r--r--tests/fixtures/identity.sql6
-rw-r--r--tests/fixtures/optimizer/canonicalize.sql16
-rw-r--r--tests/fixtures/optimizer/optimizer.sql31
-rw-r--r--tests/fixtures/optimizer/pushdown_projections.sql3
-rw-r--r--tests/fixtures/pretty.sql3
5 files changed, 59 insertions, 0 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 380d945..3551423 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -132,6 +132,8 @@ INTERVAL '-31' CAST(GETDATE() AS DATE)
INTERVAL 2 months
INTERVAL (1 + 3) DAYS
CAST('45' AS INTERVAL DAYS)
+FILTER(a, x -> x.a.b.c.d.e.f.g)
+FILTER(a, x -> FOO(x.a.b.c.d.e.f.g) + x.a.b.c.d.e.f.g)
TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), 1, DAY)
DATETIME_DIFF(CURRENT_DATE, 1, DAY)
QUANTILE(x, 0.5)
@@ -161,6 +163,10 @@ CAST('2025-11-20 00:00:00+00' AS TIMESTAMP) AT TIME ZONE 'Africa/Cairo'
SET x = 1
SET -v
SET x = ';'
+SET variable = value
+SET GLOBAL variable = value
+SET LOCAL variable = value
+SET @user OFF
COMMIT
USE db
USE role x
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index 8c7cd45..50fee7f 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -9,3 +9,19 @@ SELECT CAST(1 AS VARCHAR) AS "a" FROM "w" AS "w";
SELECT CAST(1 + 3.2 AS DOUBLE) AS a FROM w AS w;
SELECT 1 + 3.2 AS "a" FROM "w" AS "w";
+
+--------------------------------------
+-- Ensure boolean predicates
+--------------------------------------
+
+SELECT a FROM x WHERE b;
+SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE "x"."b" <> 0;
+
+SELECT a FROM x GROUP BY a HAVING SUM(b);
+SELECT "x"."a" AS "a" FROM "x" AS "x" GROUP BY "x"."a" HAVING SUM("x"."b") <> 0;
+
+SELECT a FROM x GROUP BY a HAVING SUM(b) AND TRUE;
+SELECT "x"."a" AS "a" FROM "x" AS "x" GROUP BY "x"."a" HAVING SUM("x"."b") <> 0 AND TRUE;
+
+SELECT a FROM x WHERE 1;
+SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE 1 <> 0;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index a14e325..0b5504d 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -386,6 +386,29 @@ SELECT
"x"."b" + 1 AS "c"
FROM "x" AS "x";
+# title: unqualified struct element is selected in the outer query
+# execute: false
+WITH "cte" AS (
+ SELECT
+ FROM_JSON("value", 'STRUCT<f1: STRUCT<f2: STRUCT<f3: STRUCT<f4: STRING>>>>') AS "struct"
+ FROM "tbl"
+) SELECT "struct"."f1"."f2"."f3"."f4" AS "f4" FROM "cte";
+SELECT
+ FROM_JSON("tbl"."value", 'STRUCT<f1: STRUCT<f2: STRUCT<f3: STRUCT<f4: STRING>>>>')."f1"."f2"."f3"."f4" AS "f4"
+FROM "tbl" AS "tbl";
+
+# title: qualified struct element is selected in the outer query
+# execute: false
+WITH "cte" AS (
+ SELECT
+ FROM_JSON("value", 'STRUCT<f1: STRUCT<f2: INTEGER>, STRUCT<f3: STRING>>') AS "struct"
+ FROM "tbl"
+) SELECT "cte"."struct"."f1"."f2" AS "f2", "cte"."struct"."f1"."f3" AS "f3" FROM "cte";
+SELECT
+ FROM_JSON("tbl"."value", 'STRUCT<f1: STRUCT<f2: INTEGER>, STRUCT<f3: STRING>>')."f1"."f2" AS "f2",
+ FROM_JSON("tbl"."value", 'STRUCT<f1: STRUCT<f2: INTEGER>, STRUCT<f3: STRING>>')."f1"."f3" AS "f3"
+FROM "tbl" AS "tbl";
+
# title: left join doesnt push down predicate to join in merge subqueries
# execute: false
SELECT
@@ -430,3 +453,11 @@ LEFT JOIN "unlocked" AS "unlocked"
WHERE
CASE WHEN "unlocked"."company_id" IS NULL THEN 0 ELSE 1 END = FALSE
AND NOT "company_table_2"."id" IS NULL;
+
+# title: db.table alias clash
+# execute: false
+select * from db1.tbl, db2.tbl;
+SELECT
+ *
+FROM "db1"."tbl" AS "tbl"
+CROSS JOIN "db2"."tbl" AS "tbl_2";
diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql
index f3b1a69..6ff9383 100644
--- a/tests/fixtures/optimizer/pushdown_projections.sql
+++ b/tests/fixtures/optimizer/pushdown_projections.sql
@@ -4,6 +4,9 @@ SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
SELECT 1 FROM (SELECT * FROM x) WHERE b = 2;
SELECT 1 AS "1" FROM (SELECT x.b AS b FROM x AS x) AS _q_0 WHERE _q_0.b = 2;
+SELECT a, b, a from x;
+SELECT x.a AS a, x.b AS b, x.a AS a FROM x AS x;
+
SELECT (SELECT c FROM y WHERE q.b = y.b) FROM (SELECT * FROM x) AS q;
SELECT (SELECT y.c AS c FROM y AS y WHERE q.b = y.b) AS _col_0 FROM (SELECT x.b AS b FROM x AS x) AS q;
diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql
index a06af88..8de9c85 100644
--- a/tests/fixtures/pretty.sql
+++ b/tests/fixtures/pretty.sql
@@ -1,3 +1,6 @@
+SET x TO 1;
+SET x = 1;
+
SELECT * FROM test;
SELECT
*