summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/canonicalize.sql12
-rw-r--r--tests/fixtures/optimizer/normalize_identifiers.sql8
-rw-r--r--tests/fixtures/optimizer/optimizer.sql22
-rw-r--r--tests/fixtures/optimizer/simplify.sql23
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql6
5 files changed, 68 insertions, 3 deletions
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index e27b2d3..2ba762d 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -29,6 +29,12 @@ SELECT "x"."a" AS "a" FROM "x" AS "x" GROUP BY "x"."a" HAVING SUM("x"."b") <> 0
SELECT a FROM x WHERE 1;
SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE 1 <> 0;
+SELECT a FROM x WHERE COALESCE(0, 1);
+SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE COALESCE(0 <> 0, 1 <> 0);
+
+SELECT a FROM x WHERE CASE WHEN COALESCE(b, 1) THEN 1 ELSE 0 END;
+SELECT "x"."a" AS "a" FROM "x" AS "x" WHERE CASE WHEN COALESCE("x"."b" <> 0, 1 <> 0) THEN 1 ELSE 0 END <> 0;
+
--------------------------------------
-- Replace date functions
--------------------------------------
@@ -40,3 +46,9 @@ CAST('2023-01-01' AS TIMESTAMP);
TIMESTAMP('2023-01-01', '12:00:00');
TIMESTAMP('2023-01-01', '12:00:00');
+
+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');
diff --git a/tests/fixtures/optimizer/normalize_identifiers.sql b/tests/fixtures/optimizer/normalize_identifiers.sql
index 2ab4778..4cb7dd1 100644
--- a/tests/fixtures/optimizer/normalize_identifiers.sql
+++ b/tests/fixtures/optimizer/normalize_identifiers.sql
@@ -62,3 +62,11 @@ SELECT a AS a FROM x UNION SELECT a AS a FROM x;
(SELECT A AS A FROM X);
(SELECT a AS a FROM x);
+
+# dialect: snowflake
+SELECT a /* sqlglot.meta case_sensitive */, b FROM table /* sqlglot.meta case_sensitive */;
+SELECT a /* sqlglot.meta case_sensitive */, B FROM table /* sqlglot.meta case_sensitive */;
+
+# dialect: redshift
+SELECT COALESCE(json_val.a /* sqlglot.meta case_sensitive */, json_val.A /* sqlglot.meta case_sensitive */) FROM table;
+SELECT COALESCE(json_val.a /* sqlglot.meta case_sensitive */, json_val.A /* sqlglot.meta case_sensitive */) FROM table;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index e59f14d..4cc62c9 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -1023,3 +1023,25 @@ SELECT
FROM "table1" AS "table1"
LEFT JOIN "alias3"
ON "table1"."cid" = "alias3"."cid";
+
+# title: CTE with EXPLODE cannot be merged
+# dialect: spark
+# execute: false
+SELECT Name,
+ FruitStruct.`$id`,
+ FruitStruct.value
+ FROM
+ (SELECT Name,
+ explode(Fruits) as FruitStruct
+ FROM fruits_table);
+WITH `_q_0` AS (
+ SELECT
+ `fruits_table`.`name` AS `name`,
+ EXPLODE(`fruits_table`.`fruits`) AS `fruitstruct`
+ FROM `fruits_table` AS `fruits_table`
+)
+SELECT
+ `_q_0`.`name` AS `name`,
+ `_q_0`.`fruitstruct`.`$id` AS `$id`,
+ `_q_0`.`fruitstruct`.`value` AS `value`
+FROM `_q_0` AS `_q_0`;
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index 584e9d6..a9ae192 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -444,6 +444,9 @@ CAST('1998-09-02 00:00:00' AS DATETIME);
CAST(x AS DATETIME) + interval '1' week;
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);
+
--------------------------------------
-- Comparisons
--------------------------------------
@@ -681,6 +684,9 @@ CONCAT('a', x, y, 'bc');
'a' || 'b' || x;
CONCAT('ab', x);
+CONCAT(a, b) IN (SELECT * FROM foo WHERE cond);
+CONCAT(a, b) IN (SELECT * FROM foo WHERE cond);
+
--------------------------------------
-- DATE_TRUNC
--------------------------------------
@@ -740,6 +746,9 @@ x >= CAST('2022-01-01' AS DATE);
DATE_TRUNC('year', x) > CAST('2021-01-02' AS DATE);
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);
+
-- right is not a date
DATE_TRUNC('year', x) <> '2021-01-02';
DATE_TRUNC('year', x) <> '2021-01-02';
@@ -758,6 +767,17 @@ x < CAST('2022-01-01' AS DATE) AND x >= CAST('2021-01-01' AS DATE);
TIMESTAMP_TRUNC(x, YEAR) = CAST('2021-01-01' AS DATETIME);
x < CAST('2022-01-01 00:00:00' AS DATETIME) AND x >= CAST('2021-01-01 00:00:00' AS DATETIME);
+-- right side is not a date literal
+DATE_TRUNC('day', x) = CAST(y AS DATE);
+DATE_TRUNC('day', x) = CAST(y AS DATE);
+
+-- nested cast
+DATE_TRUNC('day', x) = CAST(CAST('2021-01-01 01:02:03' AS DATETIME) AS DATE);
+x < CAST('2021-01-02' AS DATE) AND x >= CAST('2021-01-01' AS DATE);
+
+TIMESTAMP_TRUNC(x, YEAR) = CAST(CAST('2021-01-01 01:02:03' AS DATE) AS DATETIME);
+x < CAST('2022-01-01 00:00:00' AS DATETIME) AND x >= CAST('2021-01-01 00:00:00' AS DATETIME);
+
--------------------------------------
-- EQUALITY
--------------------------------------
@@ -794,6 +814,9 @@ x = 2;
x - INTERVAL 1 DAY = CAST('2021-01-01' AS DATE);
x = CAST('2021-01-02' AS DATE);
+x - INTERVAL 1 DAY = TS_OR_DS_TO_DATE('2021-01-01 00:00:01');
+x = CAST('2021-01-02' AS DATE);
+
x - INTERVAL 1 HOUR > CAST('2021-01-01' AS DATETIME);
x > CAST('2021-01-01 01:00:00' AS DATETIME);
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index f50cf0b..2218182 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -4793,10 +4793,10 @@ WITH "foo" AS (
"foo"."i_item_sk" AS "i_item_sk",
"foo"."d_moy" AS "d_moy",
"foo"."mean" AS "mean",
- CASE "foo"."mean" WHEN 0 THEN NULL ELSE "foo"."stdev" / "foo"."mean" END AS "cov"
+ CASE "foo"."mean" WHEN FALSE THEN NULL ELSE "foo"."stdev" / "foo"."mean" END AS "cov"
FROM "foo" AS "foo"
WHERE
- CASE "foo"."mean" WHEN 0 THEN 0 ELSE "foo"."stdev" / "foo"."mean" END > 1
+ CASE "foo"."mean" WHEN FALSE THEN 0 ELSE "foo"."stdev" / "foo"."mean" END > 1
)
SELECT
"inv1"."w_warehouse_sk" AS "w_warehouse_sk",
@@ -9775,7 +9775,7 @@ JOIN "date_dim" AS "d1"
ON "catalog_sales"."cs_sold_date_sk" = "d1"."d_date_sk"
AND "d1"."d_week_seq" = "d2"."d_week_seq"
AND "d1"."d_year" = 2002
- AND "d3"."d_date" > CONCAT("d1"."d_date", INTERVAL '5' day)
+ AND "d3"."d_date" > "d1"."d_date" + INTERVAL '5' day
GROUP BY
"item"."i_item_desc",
"warehouse"."w_warehouse_name",