summaryrefslogtreecommitdiffstats
path: root/tests/fixtures
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--tests/fixtures/identity.sql2
-rw-r--r--tests/fixtures/optimizer/canonicalize.sql15
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql50
3 files changed, 40 insertions, 27 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 0e13a64..67145b1 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -883,3 +883,5 @@ GRANT SELECT, INSERT ON FUNCTION tbl TO user
GRANT SELECT ON orders TO ROLE PUBLIC
GRANT SELECT ON nation TO alice WITH GRANT OPTION
GRANT DELETE ON SCHEMA finance TO bob
+SELECT attach
+SELECT detach \ No newline at end of file
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index 66c6c95..3610f16 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -2,7 +2,7 @@ SELECT w.d + w.e AS c FROM w AS w;
SELECT CONCAT("w"."d", "w"."e") AS "c" FROM "w" AS "w";
SELECT CAST(w.d AS DATE) > w.e AS a FROM w AS w;
-SELECT CAST("w"."d" AS DATE) > CAST("w"."e" AS DATETIME) AS "a" FROM "w" AS "w";
+SELECT CAST("w"."d" AS DATE) > CAST("w"."e" AS DATE) AS "a" FROM "w" AS "w";
SELECT CAST(1 AS VARCHAR) AS a FROM w AS w;
SELECT CAST(1 AS VARCHAR) AS "a" FROM "w" AS "w";
@@ -102,7 +102,7 @@ DATEDIFF('2023-01-01', '2023-01-02', DAY);
DATEDIFF(CAST('2023-01-01' AS DATETIME), CAST('2023-01-02' AS DATETIME), DAY);
SELECT "t"."d" > '2023-01-01' AS "d" FROM "temporal" AS "t";
-SELECT "t"."d" > CAST('2023-01-01' AS DATETIME) AS "d" FROM "temporal" AS "t";
+SELECT "t"."d" > CAST('2023-01-01' AS DATE) AS "d" FROM "temporal" AS "t";
SELECT "t"."d" > CAST('2023-01-01' AS DATETIME) AS "d" FROM "temporal" AS "t";
SELECT "t"."d" > CAST('2023-01-01' AS DATETIME) AS "d" FROM "temporal" AS "t";
@@ -110,6 +110,17 @@ SELECT "t"."d" > CAST('2023-01-01' AS DATETIME) AS "d" FROM "temporal" AS "t";
SELECT "t"."t" > '2023-01-01 00:00:01' AS "t" FROM "temporal" AS "t";
SELECT "t"."t" > CAST('2023-01-01 00:00:01' AS DATETIME) AS "t" FROM "temporal" AS "t";
+WITH "t" AS (SELECT CAST("ext"."created_at" AS TIMESTAMP) AS "created_at" FROM "ext" AS "ext") SELECT "t"."created_at" > '2024-10-01 12:05:02' AS "col" FROM "t" AS "t";
+WITH "t" AS (SELECT CAST("ext"."created_at" AS TIMESTAMP) AS "created_at" FROM "ext" AS "ext") SELECT "t"."created_at" > CAST('2024-10-01 12:05:02' AS TIMESTAMP) AS "col" FROM "t" AS "t";
+
+# dialect: mysql
+SELECT `t`.`d` < '2023-01-01 00:00:01' AS `col` FROM `temporal` AS `t`;
+SELECT CAST(`t`.`d` AS DATETIME) < CAST('2023-01-01 00:00:01' AS DATETIME) AS `col` FROM `temporal` AS `t`;
+
+# dialect: mysql
+SELECT CAST(`t`.`some_col` AS DATE) < CAST(`t`.`other_col` AS CHAR) AS `col` FROM `other_table` AS `t`;
+SELECT CAST(CAST(`t`.`some_col` AS DATE) AS DATETIME) < CAST(CAST(`t`.`other_col` AS CHAR) AS DATETIME) AS `col` FROM `other_table` AS `t`;
+
--------------------------------------
-- Remove redundant casts
--------------------------------------
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index 290d276..59bc432 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -736,8 +736,8 @@ WITH "salesreturns" AS (
"date_dim"."d_date" AS "d_date"
FROM "date_dim" AS "date_dim"
WHERE
- CAST("date_dim"."d_date" AS DATETIME) <= CAST('2002-09-05' AS DATE)
- AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2002-08-22' AS DATE)
+ CAST("date_dim"."d_date" AS DATE) <= CAST('2002-09-05' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) >= CAST('2002-08-22' AS DATE)
), "ssr" AS (
SELECT
"store"."s_store_id" AS "s_store_id",
@@ -1853,8 +1853,8 @@ SELECT
FROM "web_sales" AS "web_sales"
JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_date_sk" = "web_sales"."ws_sold_date_sk"
- AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2000-06-10' AS DATE)
- AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2000-05-11' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-10' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-05-11' AS DATE)
JOIN "item" AS "item"
ON "item"."i_category" IN ('Home', 'Men', 'Women')
AND "item"."i_item_sk" = "web_sales"."ws_item_sk"
@@ -2422,7 +2422,7 @@ JOIN "date_dim" AS "date_dim"
AND "date_dim"."d_date" >= '2002-3-01'
AND (
CAST('2002-3-01' AS DATE) + INTERVAL '60' DAY
- ) >= CAST("date_dim"."d_date" AS DATETIME)
+ ) >= CAST("date_dim"."d_date" AS DATE)
WHERE
"_u_3"."_u_4" IS NULL
AND ARRAY_ANY("_u_0"."_u_2", "_x" -> "cs1"."cs_warehouse_sk" <> "_x")
@@ -2731,8 +2731,8 @@ SELECT
FROM "catalog_sales" AS "catalog_sales"
JOIN "date_dim" AS "date_dim"
ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
- AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2001-03-05' AS DATE)
- AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2001-02-03' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) <= CAST('2001-03-05' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) >= CAST('2001-02-03' AS DATE)
JOIN "item" AS "item"
ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
AND "item"."i_category" IN ('Children', 'Women', 'Electronics')
@@ -2811,8 +2811,8 @@ WITH "x" AS (
FROM "inventory" AS "inventory"
JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk"
- AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2000-06-12' AS DATE)
- AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2000-04-13' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-12' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-04-13' AS DATE)
JOIN "item" AS "item"
ON "inventory"."inv_item_sk" = "item"."i_item_sk"
AND "item"."i_current_price" <= 1.49
@@ -3944,7 +3944,7 @@ WITH "catalog_sales_2" AS (
FROM "date_dim" AS "date_dim"
WHERE
"date_dim"."d_date" >= '2001-03-04'
- AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2001-06-02' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) <= CAST('2001-06-02' AS DATE)
), "_u_0" AS (
SELECT
1.3 * AVG("catalog_sales"."cs_ext_discount_amt") AS "_col_0",
@@ -4510,8 +4510,8 @@ JOIN "inventory" AS "inventory"
AND "inventory"."inv_quantity_on_hand" >= 100
JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk"
- AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('1999-05-05' AS DATE)
- AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('1999-03-06' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) <= CAST('1999-05-05' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) >= CAST('1999-03-06' AS DATE)
WHERE
"item"."i_current_price" <= 50
AND "item"."i_current_price" >= 20
@@ -4787,8 +4787,8 @@ LEFT JOIN "catalog_returns" AS "catalog_returns"
AND "catalog_returns"."cr_order_number" = "catalog_sales"."cs_order_number"
JOIN "date_dim" AS "date_dim"
ON "catalog_sales"."cs_sold_date_sk" = "date_dim"."d_date_sk"
- AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2002-07-01' AS DATE)
- AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2002-05-02' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) <= CAST('2002-07-01' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) >= CAST('2002-05-02' AS DATE)
JOIN "item" AS "item"
ON "catalog_sales"."cs_item_sk" = "item"."i_item_sk"
AND "item"."i_current_price" <= 1.49
@@ -10318,8 +10318,8 @@ WITH "date_dim_2" AS (
"date_dim"."d_date" AS "d_date"
FROM "date_dim" AS "date_dim"
WHERE
- CAST("date_dim"."d_date" AS DATETIME) <= CAST('2001-09-15' AS DATE)
- AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2001-08-16' AS DATE)
+ CAST("date_dim"."d_date" AS DATE) <= CAST('2001-09-15' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) >= CAST('2001-08-16' AS DATE)
), "store_2" AS (
SELECT
"store"."s_store_sk" AS "s_store_sk"
@@ -10828,8 +10828,8 @@ WITH "date_dim_2" AS (
"date_dim"."d_date" AS "d_date"
FROM "date_dim" AS "date_dim"
WHERE
- CAST("date_dim"."d_date" AS DATETIME) <= CAST('2000-09-25' AS DATE)
- AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2000-08-26' AS DATE)
+ CAST("date_dim"."d_date" AS DATE) <= CAST('2000-09-25' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-08-26' AS DATE)
), "item_2" AS (
SELECT
"item"."i_item_sk" AS "i_item_sk",
@@ -11109,8 +11109,8 @@ JOIN "store_sales" AS "store_sales"
ON "item"."i_item_sk" = "store_sales"."ss_item_sk"
JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_date_sk" = "inventory"."inv_date_sk"
- AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('1998-06-26' AS DATE)
- AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('1998-04-27' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) <= CAST('1998-06-26' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) >= CAST('1998-04-27' AS DATE)
WHERE
"item"."i_current_price" <= 93
AND "item"."i_current_price" >= 63
@@ -12180,7 +12180,7 @@ WITH "web_sales_2" AS (
FROM "date_dim" AS "date_dim"
WHERE
"date_dim"."d_date" >= '2002-03-29'
- AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2002-06-27' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) <= CAST('2002-06-27' AS DATE)
), "_u_0" AS (
SELECT
1.3 * AVG("web_sales"."ws_ext_discount_amt") AS "_col_0",
@@ -12321,7 +12321,7 @@ JOIN "date_dim" AS "date_dim"
AND "date_dim"."d_date_sk" = "ws1"."ws_ship_date_sk"
AND (
CAST('2000-3-01' AS DATE) + INTERVAL '60' DAY
- ) >= CAST("date_dim"."d_date" AS DATETIME)
+ ) >= CAST("date_dim"."d_date" AS DATE)
JOIN "web_site" AS "web_site"
ON "web_site"."web_company_name" = 'pri'
AND "web_site"."web_site_sk" = "ws1"."ws_web_site_sk"
@@ -12411,7 +12411,7 @@ JOIN "date_dim" AS "date_dim"
AND "date_dim"."d_date_sk" = "ws1"."ws_ship_date_sk"
AND (
CAST('2000-4-01' AS DATE) + INTERVAL '60' DAY
- ) >= CAST("date_dim"."d_date" AS DATETIME)
+ ) >= CAST("date_dim"."d_date" AS DATE)
JOIN "web_site" AS "web_site"
ON "web_site"."web_company_name" = 'pri'
AND "web_site"."web_site_sk" = "ws1"."ws_web_site_sk"
@@ -12595,8 +12595,8 @@ SELECT
FROM "store_sales" AS "store_sales"
JOIN "date_dim" AS "date_dim"
ON "date_dim"."d_date_sk" = "store_sales"."ss_sold_date_sk"
- AND CAST("date_dim"."d_date" AS DATETIME) <= CAST('2000-06-17' AS DATE)
- AND CAST("date_dim"."d_date" AS DATETIME) >= CAST('2000-05-18' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) <= CAST('2000-06-17' AS DATE)
+ AND CAST("date_dim"."d_date" AS DATE) >= CAST('2000-05-18' AS DATE)
JOIN "item" AS "item"
ON "item"."i_category" IN ('Men', 'Home', 'Electronics')
AND "item"."i_item_sk" = "store_sales"."ss_item_sk"
generated by cgit v1.2.3 (git 2.39.1) at 2025-01-06 07:29:29 +0000