1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
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 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";
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;
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;
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
--------------------------------------
DATE('2023-01-01');
CAST('2023-01-01' AS DATE);
-- Some dialects only allow dates
DATE('2023-01-01 00:00:00');
DATE('2023-01-01 00:00:00');
TIMESTAMP('2023-01-01');
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);
SELECT "t"."d" > '2023-01-01' 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";
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
--------------------------------------
CAST(CAST("foo" AS DECIMAL(4, 2)) AS DECIMAL(8, 4)) AS "x";
CAST(CAST("foo" AS DECIMAL(4, 2)) AS DECIMAL(8, 4)) AS "x";
CAST(CAST("foo" AS DECIMAL(4, 2)) AS DECIMAL(4, 2)) AS "x";
CAST("foo" AS DECIMAL(4, 2)) AS "x";
CAST(CAST('2023-01-01' AS DATE) AS DATE);
CAST('2023-01-01' AS DATE);
CAST(DATE_TRUNC('YEAR', CAST('2023-01-01' AS DATE)) AS DATE);
DATE_TRUNC('YEAR', CAST('2023-01-01' AS DATE));
DATE(DATE_TRUNC('YEAR', CAST("x" AS DATE)));
DATE_TRUNC('YEAR', CAST("x" AS DATE));
|
