summaryrefslogtreecommitdiffstats
path: root/tests/fixtures
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures')
-rw-r--r--tests/fixtures/identity.sql2
-rw-r--r--tests/fixtures/optimizer/canonicalize.sql45
-rw-r--r--tests/fixtures/optimizer/optimizer.sql41
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql17
-rw-r--r--tests/fixtures/optimizer/quote_identifiers.sql31
-rw-r--r--tests/fixtures/optimizer/quote_identities.sql11
-rw-r--r--tests/fixtures/optimizer/simplify.sql47
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql16
8 files changed, 184 insertions, 26 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 6e0a3e5..effebca 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -731,7 +731,6 @@ WITH a AS (SELECT 1) INSERT INTO b SELECT * FROM a
WITH a AS (SELECT * FROM b) UPDATE a SET col = 1
WITH a AS (SELECT * FROM b) CREATE TABLE b AS SELECT * FROM a
WITH a AS (SELECT * FROM b) DELETE FROM a
-WITH a AS (SELECT * FROM b) CACHE TABLE a
SELECT ? AS ? FROM x WHERE b BETWEEN ? AND ? GROUP BY ?, 1 LIMIT ?
SELECT :hello, ? FROM x LIMIT :my_limit
SELECT a FROM b WHERE c IS ?
@@ -867,3 +866,4 @@ KILL QUERY '123'
CHR(97)
SELECT * FROM UNNEST(x) WITH ORDINALITY UNION ALL SELECT * FROM UNNEST(y) WITH ORDINALITY
WITH use(use) AS (SELECT 1) SELECT use FROM use
+SELECT recursive FROM t
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index 954b1c1..302acb9 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -10,15 +10,27 @@ 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";
+SELECT '1' + 1 AS "col";
+SELECT '1' + 1 AS "col";
+
+SELECT '1' + '1' AS "col";
+SELECT CONCAT('1', '1') AS "col";
+
SELECT CAST('2022-01-01' AS DATE) + INTERVAL '1' day;
SELECT CAST('2022-01-01' AS DATE) + INTERVAL '1' day AS "_col_0";
+SELECT CAST('2022-01-01' AS DATE) IS NULL AS "a";
+SELECT CAST('2022-01-01' AS DATE) IS NULL AS "a";
+
--------------------------------------
-- Ensure boolean predicates
--------------------------------------
SELECT a FROM x WHERE b;
SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE "x"."b" <> 0;
+SELECT NOT b FROM x;
+SELECT NOT "x"."b" <> 0 AS "_col_0" FROM "x" AS "x";
+
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;
@@ -46,8 +58,41 @@ CAST('2023-01-01' AS TIMESTAMP);
TIMESTAMP('2023-01-01', '12:00:00');
TIMESTAMP('2023-01-01', '12:00:00');
+--------------------------------------
+-- Coerce date function args
+--------------------------------------
+'2023-01-01' + INTERVAL '1' DAY;
+CAST('2023-01-01' AS DATE) + INTERVAL '1' DAY;
+
+'2023-01-01' + INTERVAL '1' HOUR;
+CAST('2023-01-01' AS DATETIME) + INTERVAL '1' HOUR;
+
+'2023-01-01 00:00:01' + INTERVAL '1' HOUR;
+CAST('2023-01-01 00:00:01' AS DATETIME) + INTERVAL '1' HOUR;
+
+CAST('2023-01-01' AS DATE) + INTERVAL '1' HOUR;
+CAST(CAST('2023-01-01' AS DATE) AS DATETIME) + INTERVAL '1' HOUR;
+
+SELECT t.d + INTERVAL '1' HOUR FROM temporal AS t;
+SELECT CAST("t"."d" AS DATETIME) + INTERVAL '1' HOUR AS "_col_0" FROM "temporal" AS "t";
+
DATE_ADD(CAST("x" AS DATE), 1, 'YEAR');
DATE_ADD(CAST("x" AS DATE), 1, 'YEAR');
DATE_ADD('2023-01-01', 1, 'YEAR');
DATE_ADD(CAST('2023-01-01' AS DATE), 1, 'YEAR');
+
+DATE_ADD('2023-01-01 00:00:00', 1, 'DAY');
+DATE_ADD(CAST('2023-01-01 00:00:00' AS DATETIME), 1, 'DAY');
+
+SELECT DATE_ADD(t.d, 1, 'HOUR') FROM temporal AS t;
+SELECT DATE_ADD(CAST("t"."d" AS DATETIME), 1, 'HOUR') AS "_col_0" FROM "temporal" AS "t";
+
+SELECT DATE_TRUNC('SECOND', t.d) FROM temporal AS t;
+SELECT DATE_TRUNC('SECOND', CAST("t"."d" AS DATETIME)) AS "_col_0" FROM "temporal" AS "t";
+
+DATE_TRUNC('DAY', '2023-01-01');
+DATE_TRUNC('DAY', CAST('2023-01-01' AS DATE));
+
+DATEDIFF('2023-01-01', '2023-01-02', DAY);
+DATEDIFF(CAST('2023-01-01' AS DATETIME), CAST('2023-01-02' AS DATETIME), DAY);
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index a9d6584..f81d54a 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -548,6 +548,23 @@ FROM (
FROM "sc"."tb" AS "tb"
) AS "_q_0" PIVOT(SUM("_q_0"."c") FOR "_q_0"."b" IN ('x', 'y', 'z')) AS "_q_1";
+# title: pivoted source with explicit selections where one of them is excluded & selected at the same time
+# note: we need to respect the exclude when selecting * from pivoted source and not include the computed column twice
+# execute: false
+SELECT * EXCEPT (x), CAST(x AS TEXT) AS x FROM (SELECT a, b, c FROM sc.tb) PIVOT (SUM(c) FOR b IN ('x','y','z'));
+SELECT
+ "_q_1"."a" AS "a",
+ "_q_1"."y" AS "y",
+ "_q_1"."z" AS "z",
+ CAST("_q_1"."x" AS TEXT) AS "x"
+FROM (
+ SELECT
+ "tb"."a" AS "a",
+ "tb"."b" AS "b",
+ "tb"."c" AS "c"
+ FROM "sc"."tb" AS "tb"
+) AS "_q_0" PIVOT(SUM("_q_0"."c") FOR "_q_0"."b" IN ('x', 'y', 'z')) AS "_q_1";
+
# title: pivoted source with implicit selections
# execute: false
SELECT * FROM (SELECT * FROM u) PIVOT (SUM(f) FOR h IN ('x', 'y'));
@@ -1074,3 +1091,27 @@ SELECT
`_q_0`.`fruitstruct`.`$id` AS `$id`,
`_q_0`.`fruitstruct`.`value` AS `value`
FROM `_q_0` AS `_q_0`;
+
+# title: mysql is case-sensitive by default
+# dialect: mysql
+# execute: false
+WITH T AS (SELECT 1 AS CoL) SELECT * FROM `T`;
+WITH `T` AS (
+ SELECT
+ 1 AS `CoL`
+)
+SELECT
+ `T`.`CoL` AS `CoL`
+FROM `T`;
+
+# title: override mysql's settings so it normalizes to lowercase
+# dialect: mysql, normalization_strategy = lowercase
+# execute: false
+WITH T AS (SELECT 1 AS `CoL`) SELECT * FROM T;
+WITH `t` AS (
+ SELECT
+ 1 AS `CoL`
+)
+SELECT
+ `t`.`CoL` AS `CoL`
+FROM `t`;
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 3224a83..43127a9 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -65,10 +65,10 @@ SELECT a AS j, b FROM x ORDER BY j;
SELECT x.a AS j, x.b AS b FROM x AS x ORDER BY j;
SELECT a AS j, b AS a FROM x ORDER BY 1;
-SELECT x.a AS j, x.b AS a FROM x AS x ORDER BY x.a;
+SELECT x.a AS j, x.b AS a FROM x AS x ORDER BY j;
SELECT SUM(a) AS c, SUM(b) AS d FROM x ORDER BY 1, 2;
-SELECT SUM(x.a) AS c, SUM(x.b) AS d FROM x AS x ORDER BY SUM(x.a), SUM(x.b);
+SELECT SUM(x.a) AS c, SUM(x.b) AS d FROM x AS x ORDER BY c, d;
# execute: false
SELECT CAST(a AS INT) FROM x ORDER BY a;
@@ -76,7 +76,7 @@ SELECT CAST(x.a AS INT) AS a FROM x AS x ORDER BY a;
# execute: false
SELECT SUM(a), SUM(b) AS c FROM x ORDER BY 1, 2;
-SELECT SUM(x.a) AS _col_0, SUM(x.b) AS c FROM x AS x ORDER BY SUM(x.a), SUM(x.b);
+SELECT SUM(x.a) AS _col_0, SUM(x.b) AS c FROM x AS x ORDER BY _col_0, c;
SELECT a AS j, b FROM x GROUP BY j, b;
SELECT x.a AS j, x.b AS b FROM x AS x GROUP BY x.a, x.b;
@@ -85,7 +85,10 @@ SELECT a, b FROM x GROUP BY 1, 2;
SELECT x.a AS a, x.b AS b FROM x AS x GROUP BY x.a, x.b;
SELECT a, b FROM x ORDER BY 1, 2;
-SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY x.a, x.b;
+SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY a, b;
+
+SELECT DISTINCT a AS c, b AS d FROM x ORDER BY 1;
+SELECT DISTINCT x.a AS c, x.b AS d FROM x AS x ORDER BY c;
SELECT 2 FROM x GROUP BY 1;
SELECT 2 AS "2" FROM x AS x GROUP BY 1;
@@ -306,6 +309,10 @@ WITH cte AS (SELECT 1 AS x) SELECT cte.a AS a FROM cte AS cte(a);
WITH cte(x, y) AS (SELECT 1, 2) SELECT cte.* FROM cte AS cte(a);
WITH cte AS (SELECT 1 AS x, 2 AS y) SELECT cte.a AS a, cte.y AS y FROM cte AS cte(a);
+# execute: false
+WITH player AS (SELECT player.name, player.asset.info FROM players) SELECT * FROM player;
+WITH player AS (SELECT players.player.name AS name, players.player.asset.info AS info FROM players AS players) SELECT player.name AS name, player.info AS info FROM player;
+
--------------------------------------
-- Except and Replace
--------------------------------------
@@ -488,7 +495,7 @@ FROM (
);
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;
-# title: wrap expanded alias to ensure operator precedence isn't broken
+# title: wrap expanded alias to ensure operator precedence isnt broken
# execute: false
SELECT x.a + x.b AS f, f * x.b FROM x;
SELECT x.a + x.b AS f, (x.a + x.b) * x.b AS _col_1 FROM x AS x;
diff --git a/tests/fixtures/optimizer/quote_identifiers.sql b/tests/fixtures/optimizer/quote_identifiers.sql
new file mode 100644
index 0000000..21181f7
--- /dev/null
+++ b/tests/fixtures/optimizer/quote_identifiers.sql
@@ -0,0 +1,31 @@
+SELECT a FROM x;
+SELECT "a" FROM "x";
+
+SELECT "a" FROM "x";
+SELECT "a" FROM "x";
+
+SELECT x.a AS a FROM db.x;
+SELECT "x"."a" AS "a" FROM "db"."x";
+
+SELECT @x;
+SELECT @x;
+
+# dialect: snowflake
+SELECT * FROM DUAL;
+SELECT * FROM DUAL;
+
+# dialect: snowflake
+SELECT * FROM "DUAL";
+SELECT * FROM "DUAL";
+
+# dialect: snowflake
+SELECT * FROM "dual";
+SELECT * FROM "dual";
+
+# dialect: snowflake
+SELECT dual FROM t;
+SELECT "dual" FROM "t";
+
+# dialect: snowflake
+SELECT * FROM t AS dual;
+SELECT * FROM "t" AS "dual";
diff --git a/tests/fixtures/optimizer/quote_identities.sql b/tests/fixtures/optimizer/quote_identities.sql
deleted file mode 100644
index d6cfbf8..0000000
--- a/tests/fixtures/optimizer/quote_identities.sql
+++ /dev/null
@@ -1,11 +0,0 @@
-SELECT a FROM x;
-SELECT "a" FROM "x";
-
-SELECT "a" FROM "x";
-SELECT "a" FROM "x";
-
-SELECT x.a AS a FROM db.x;
-SELECT "x"."a" AS "a" FROM "db"."x";
-
-SELECT @x;
-SELECT @x;
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index f50f688..2206e28 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -43,6 +43,9 @@ TRUE;
1.0 = 1;
TRUE;
+CAST('2023-01-01' AS DATE) = CAST('2023-01-01' AS DATE);
+TRUE;
+
'x' = 'y';
FALSE;
@@ -360,6 +363,15 @@ x * (1 - y);
(1.0 * 3) * 4 - 2 * (5 / 2);
12.0 - 2 * (5 / 2);
+a * 0.5 / 10 / (2.0 + 3);
+a * 0.5 / 10 / 5.0;
+
+a * 0.5 - 10 - (2.0 + 3);
+a * 0.5 - 10 - 5.0;
+
+x * (10 - 5);
+x * 5;
+
6 - 2 + 4 * 2 + a;
12 + a;
@@ -414,6 +426,9 @@ FALSE;
1 IS NOT NULL;
TRUE;
+date '1998-12-01' - interval x day;
+CAST('1998-12-01' AS DATE) - INTERVAL x day;
+
date '1998-12-01' - interval '90' day;
CAST('1998-09-02' AS DATE);
@@ -447,6 +462,24 @@ CAST(x AS DATETIME) + INTERVAL '1' week;
TS_OR_DS_TO_DATE('1998-12-01 00:00:01') - interval '90' day;
CAST('1998-09-02' AS DATE);
+DATE_ADD(CAST('2023-01-02' AS DATE), -2, 'MONTH');
+CAST('2022-11-02' AS DATE);
+
+DATE_SUB(CAST('2023-01-02' AS DATE), 1 + 1, 'DAY');
+CAST('2022-12-31' AS DATE);
+
+DATE_ADD(CAST('2023-01-02' AS DATETIME), -2, 'HOUR');
+CAST('2023-01-01 22:00:00' AS DATETIME);
+
+DATETIME_ADD(CAST('2023-01-02' AS DATETIME), -2, 'HOUR');
+CAST('2023-01-01 22:00:00' AS DATETIME);
+
+DATETIME_SUB(CAST('2023-01-02' AS DATETIME), 1 + 1, 'HOUR');
+CAST('2023-01-01 22:00:00' AS DATETIME);
+
+DATE_ADD(x, 1, 'MONTH');
+DATE_ADD(x, 1, 'MONTH');
+
--------------------------------------
-- Comparisons
--------------------------------------
@@ -663,6 +696,15 @@ 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);
+COALESCE(1, 2);
+1;
+
+COALESCE(CAST(CAST('2023-01-01' AS TIMESTAMP) AS DATE), x);
+CAST(CAST('2023-01-01' AS TIMESTAMP) AS DATE);
+
+COALESCE(CAST(NULL AS DATE), x);
+COALESCE(CAST(NULL AS DATE), x);
+
--------------------------------------
-- CONCAT
--------------------------------------
@@ -673,7 +715,7 @@ CONCAT_WS(sep, x, y);
CONCAT_WS(sep, x, y);
CONCAT(x);
-x;
+CONCAT(x);
CONCAT('a', 'b', 'c');
'abc';
@@ -776,6 +818,9 @@ x >= CAST('2022-01-01' AS DATE);
DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02'));
x >= CAST('2022-01-01' AS DATE);
+DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02', '%Y'));
+DATE_TRUNC('year', x) > TS_OR_DS_TO_DATE(TS_OR_DS_TO_DATE('2021-01-02', '%Y'));
+
-- right is not a date
DATE_TRUNC('year', x) <> '2021-01-02';
DATE_TRUNC('year', x) <> '2021-01-02';
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index 62f1d79..d8cf64f 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -5699,7 +5699,7 @@ WHERE
END > 0.1
ORDER BY
"v1"."sum_sales" - "v1"."avg_monthly_sales",
- "v1"."d_moy"
+ "d_moy"
LIMIT 100;
--------------------------------------
@@ -6020,9 +6020,9 @@ WITH "date_dim_2" AS (
WHERE
"store"."currency_rank" <= 10 OR "store"."return_rank" <= 10
ORDER BY
- 1,
- "store"."return_rank",
- "store"."currency_rank"
+ "channel",
+ "return_rank",
+ "currency_rank"
LIMIT 100
), "cte_4" AS (
SELECT
@@ -6997,7 +6997,7 @@ WHERE
END > 0.1
ORDER BY
"v1"."sum_sales" - "v1"."avg_monthly_sales",
- "v1"."avg_monthly_sales"
+ "avg_monthly_sales"
LIMIT 100;
--------------------------------------
@@ -10061,9 +10061,9 @@ WHERE
AND "t_s_firstyear"."year1" = 1999
AND "t_s_firstyear"."year_total" > 0
ORDER BY
- "t_s_secyear"."customer_id",
- "t_s_secyear"."customer_first_name",
- "t_s_secyear"."customer_last_name"
+ "customer_id",
+ "customer_first_name",
+ "customer_last_name"
LIMIT 100;
--------------------------------------
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-04 03:00:01 +0000