Repository navigation
Expand file tree
/
Copy pathcanonicalize.py
More file actions
258 lines (206 loc) · 8.35 KB
/
Copy pathcanonicalize.py
File metadata and controls
258 lines (206 loc) · 8.35 KB
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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
from __future__ import annotations
import itertools
import typing as t
from sqlglot import exp
from sqlglot.dialects.dialect import Dialect, DialectType
from sqlglot.helper import is_date_unit, is_iso_date, is_iso_datetime
from sqlglot.optimizer.annotate_types import TypeAnnotator
def canonicalize(expression: exp.Expr, dialect: DialectType = None) -> exp.Expr:
"""Converts a sql expression into a standard form.
This method relies on annotate_types because many of the
conversions rely on type inference.
Args:
expression: The expression to canonicalize.
"""
_dialect = Dialect.get_or_raise(dialect)
def _canonicalize(expression: exp.Expr) -> exp.Expr:
if not isinstance(expression, _CANONICALIZE_TYPES):
return expression
expression = add_text_to_concat(expression)
expression = replace_date_funcs(expression, dialect=_dialect)
expression = coerce_type(expression, _dialect.PROMOTE_TO_INFERRED_DATETIME_TYPE)
expression = remove_redundant_casts(expression)
expression = ensure_bools(expression, _replace_int_predicate)
expression = remove_ascending_order(expression)
return expression
return exp.replace_tree(expression, _canonicalize)
COERCIBLE_DATE_OPS = (
exp.Add,
exp.Sub,
exp.EQ,
exp.NEQ,
exp.GT,
exp.GTE,
exp.LT,
exp.LTE,
exp.NullSafeEQ,
exp.NullSafeNEQ,
)
# All expression types that any of the canonicalize functions can act on
_CANONICALIZE_TYPES = tuple(
{
# add_text_to_concat
exp.Add,
# replace_date_funcs
exp.Date,
exp.TsOrDsToDate,
exp.Timestamp,
# coerce_type (COERCIBLE_DATE_OPS + Between, Extract, DateAdd, DateSub, DateTrunc, DateDiff)
*COERCIBLE_DATE_OPS,
exp.Between,
exp.Extract,
exp.DateAdd,
exp.DateSub,
exp.DateTrunc,
exp.DateDiff,
# remove_redundant_casts
exp.Cast,
# ensure_bools (Connector, Not, If, Where, Having)
exp.Connector,
exp.Not,
exp.If,
exp.Where,
exp.Having,
# remove_ascending_order
exp.Ordered,
}
)
def add_text_to_concat(node: exp.Expr) -> exp.Expr:
if isinstance(node, exp.Add) and node.type and node.type.this in exp.DataType.TEXT_TYPES:
node = exp.Concat(
expressions=[node.left, node.right],
# All known dialects, i.e. Redshift and T-SQL, that support
# concatenating strings with the + operator do not coalesce NULLs.
coalesce=False,
)
return node
def replace_date_funcs(node: exp.Expr, dialect: DialectType) -> exp.Expr:
if (
isinstance(node, (exp.Date, exp.TsOrDsToDate))
and not node.expressions
and not node.args.get("zone")
and node.this.is_string
and is_iso_date(node.this.name)
):
return exp.cast(node.this, to=exp.DType.DATE)
if isinstance(node, exp.Timestamp) and not node.args.get("zone"):
if not node.type:
from sqlglot.optimizer.annotate_types import annotate_types
node = annotate_types(node, dialect=dialect)
return exp.cast(node.this, to=node.type or exp.DType.TIMESTAMP)
return node
def coerce_type(node: exp.Expr, promote_to_inferred_datetime_type: bool) -> exp.Expr:
if isinstance(node, COERCIBLE_DATE_OPS):
_coerce_date(node.left, node.right, promote_to_inferred_datetime_type)
elif isinstance(node, exp.Between):
_coerce_date(node.this, node.args["low"], promote_to_inferred_datetime_type)
elif isinstance(node, exp.Extract) and not node.expression.is_type(
*exp.DataType.TEMPORAL_TYPES
):
_replace_cast(node.expression, exp.DType.DATETIME)
elif isinstance(node, (exp.DateAdd, exp.DateSub, exp.DateTrunc)):
_coerce_timeunit_arg(node.this, node.unit)
elif isinstance(node, exp.DateDiff):
_coerce_datediff_args(node)
return node
def remove_redundant_casts(expression: exp.Expr) -> exp.Expr:
if (
isinstance(expression, exp.Cast)
and expression.this.type
and expression.to == expression.this.type
):
return expression.this
if (
isinstance(expression, (exp.Date, exp.TsOrDsToDate))
and expression.this.type
and expression.this.type.this == exp.DType.DATE
and not expression.this.type.expressions
):
return expression.this
return expression
def ensure_bools(expression: exp.Expr, replace_func: t.Callable[[exp.Expr], None]) -> exp.Expr:
if isinstance(expression, exp.Connector):
replace_func(expression.left)
replace_func(expression.right)
elif isinstance(expression, exp.Not):
replace_func(expression.this)
# We can't replace num in CASE x WHEN num ..., because it's not the full predicate
elif isinstance(expression, exp.If) and not (
isinstance(expression.parent, exp.Case) and expression.parent.this
):
replace_func(expression.this)
elif isinstance(expression, (exp.Where, exp.Having)):
replace_func(expression.this)
return expression
def remove_ascending_order(expression: exp.Expr) -> exp.Expr:
if isinstance(expression, exp.Ordered) and expression.args.get("desc") is False:
# Convert ORDER BY a ASC to ORDER BY a
expression.set("desc", None)
return expression
def _coerce_date(
a: exp.Expr,
b: exp.Expr,
promote_to_inferred_datetime_type: bool,
) -> None:
for a, b in itertools.permutations([a, b]):
if isinstance(b, exp.Interval):
a = _coerce_timeunit_arg(a, b.unit)
a_type = a.type
if (
not a_type
or a_type.this not in exp.DataType.TEMPORAL_TYPES
or not b.type
or b.type.this not in exp.DataType.TEXT_TYPES
):
continue
if promote_to_inferred_datetime_type:
if b.is_string:
date_text = b.name
if is_iso_date(date_text):
b_type = exp.DType.DATE
elif is_iso_datetime(date_text):
b_type = exp.DType.DATETIME
else:
b_type = a_type.this
else:
# If b is not a datetime string, we conservatively promote it to a DATETIME,
# in order to ensure there are no surprising truncations due to downcasting
b_type = exp.DType.DATETIME
target_type = (
b_type if b_type in TypeAnnotator.COERCES_TO.get(a_type.this, {}) else a_type
)
else:
target_type = a_type
if target_type != a_type:
_replace_cast(a, target_type)
_replace_cast(b, target_type)
def _coerce_timeunit_arg(arg: exp.Expr, unit: exp.Expr | None) -> exp.Expr:
if not arg.type:
return arg
if arg.type.this in exp.DataType.TEXT_TYPES:
date_text = arg.name
is_iso_date_ = is_iso_date(date_text)
if is_iso_date_ and is_date_unit(unit):
return arg.replace(exp.cast(arg.copy(), to=exp.DType.DATE))
# An ISO date is also an ISO datetime, but not vice versa
if is_iso_date_ or is_iso_datetime(date_text):
return arg.replace(exp.cast(arg.copy(), to=exp.DType.DATETIME))
elif arg.type.this == exp.DType.DATE and not is_date_unit(unit):
return arg.replace(exp.cast(arg.copy(), to=exp.DType.DATETIME))
return arg
def _coerce_datediff_args(node: exp.DateDiff) -> None:
for e in (node.this, node.expression):
if e.type is None or e.type.this not in exp.DataType.TEMPORAL_TYPES:
e.replace(exp.cast(e.copy(), to=exp.DType.DATETIME))
def _replace_cast(node: exp.Expr, to: exp.DATA_TYPE) -> None:
node.replace(exp.cast(node.copy(), to=to))
# this was originally designed for presto, there is a similar transform for tsql
# this is different in that it only operates on int types, this is because
# presto has a boolean type whereas tsql doesn't (people use bits)
# with y as (select true as x) select x = 0 FROM y -- illegal presto query
def _replace_int_predicate(expression: exp.Expr) -> None:
if isinstance(expression, exp.Coalesce):
for child in expression.iter_expressions():
_replace_int_predicate(child)
elif expression.type and expression.type.this in exp.DataType.INTEGER_TYPES:
expression.replace(expression.neq(0))