Repository navigation
Expand file tree
/
Copy pathpushdown_predicates.py
More file actions
267 lines (219 loc) · 9.79 KB
/
Copy pathpushdown_predicates.py
File metadata and controls
267 lines (219 loc) · 9.79 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
259
260
261
262
263
264
265
266
267
from __future__ import annotations
from collections.abc import Iterable, Mapping
import typing as t
from sqlglot import exp
from sqlglot.optimizer.normalize import normalized
from sqlglot.optimizer.scope import build_scope, find_in_scope, Scope
from sqlglot.optimizer.simplify import simplify
from sqlglot import Dialect
if t.TYPE_CHECKING:
from sqlglot._typing import E
from sqlglot.dialects.dialect import DialectType
Sources = Mapping[str, tuple[exp.Selectable, t.Union[exp.Table, Scope]]]
def pushdown_predicates(expression: E, dialect: DialectType = None) -> E:
"""
Rewrite sqlglot AST to pushdown predicates in FROMS and JOINS
Example:
>>> import sqlglot
>>> sql = "SELECT y.a AS a FROM (SELECT x.a AS a FROM x AS x) AS y WHERE y.a = 1"
>>> expression = sqlglot.parse_one(sql)
>>> pushdown_predicates(expression).sql()
'SELECT y.a AS a FROM (SELECT x.a AS a FROM x AS x WHERE x.a = 1) AS y WHERE TRUE'
Args:
expression (sqlglot.Expr): expression to optimize
Returns:
sqlglot.Expr: optimized expression
"""
from sqlglot.dialects.athena import Athena
from sqlglot.dialects.presto import Presto
root = build_scope(expression)
dialect = Dialect.get_or_raise(dialect)
unnest_requires_cross_join = isinstance(dialect, (Athena, Presto))
if root:
scope_ref_count = root.ref_count()
for scope in reversed(list(root.traverse())):
select = scope.expression
where: exp.Expr | None = select.args.get("where")
joins: list[exp.Expr] = select.args.get("joins") or []
if where:
selected_sources: Sources = scope.selected_sources
join_index = {join.alias_or_name: i for i, join in enumerate(joins)}
# a right join can only push down to itself and not the source FROM table
# presto, trino and athena don't support inner joins where the RHS is an UNNEST expression
pushdown_allowed = True
for k, (node, source) in selected_sources.items():
parent = node.find_ancestor(exp.Join, exp.From)
if isinstance(parent, exp.Join):
if parent.side == "RIGHT":
selected_sources = {k: (node, source)}
break
if isinstance(node, exp.Unnest) and unnest_requires_cross_join:
pushdown_allowed = False
break
if pushdown_allowed:
pushdown(where.this, selected_sources, scope_ref_count, dialect, join_index)
# joins should only pushdown into itself, not to other joins
# so we limit the selected sources to only itself
for join in joins:
name = join.alias_or_name
if name in scope.selected_sources:
pushdown(
join.args.get("on"),
{name: scope.selected_sources[name]},
scope_ref_count,
dialect,
)
return expression
def pushdown(
condition: exp.Expr | None,
sources: Sources,
scope_ref_count: Mapping[int, int],
dialect: DialectType,
join_index: Mapping[str, int] | None = None,
) -> None:
if not condition:
return
condition = condition.replace(simplify(condition, dialect=dialect))
cnf_like = normalized(condition) or not normalized(condition, dnf=True)
predicates = list(
condition.flatten()
if isinstance(condition, exp.And if cnf_like else exp.Or)
else [condition]
)
if cnf_like:
pushdown_cnf(predicates, sources, scope_ref_count, join_index=join_index)
else:
pushdown_dnf(predicates, sources, scope_ref_count, join_index=join_index)
def pushdown_cnf(
predicates: Iterable[exp.Expr],
sources: Sources,
scope_ref_count: Mapping[int, int],
join_index: Mapping[str, int] | None = None,
) -> None:
"""
If the predicates are in CNF like form, we can simply replace each block in the parent.
"""
for predicate in predicates:
for node in nodes_for_predicate(predicate, sources, scope_ref_count).values():
if isinstance(node, exp.Join):
name = node.alias_or_name
predicate_tables = exp.column_table_names(predicate, name)
if join_index:
# Don't push the predicate if it references tables that appear in later joins
this_index = join_index[name]
if all(join_index.get(table, -1) < this_index for table in predicate_tables):
predicate.replace(exp.true())
node.on(predicate, copy=False)
break
if isinstance(node, exp.Select):
predicate.replace(exp.true())
inner_predicate = replace_aliases(node, predicate)
if find_in_scope(inner_predicate, exp.AggFunc):
node.having(inner_predicate, copy=False)
else:
node.where(inner_predicate, copy=False)
def pushdown_dnf(
predicates: Iterable[exp.Expr],
sources: Sources,
scope_ref_count: Mapping[int, int],
join_index: Mapping[str, int] | None = None,
) -> None:
"""
If the predicates are in DNF form, we can only push down conditions that are in all blocks.
Additionally, we can't remove predicates from their original form.
"""
# find all the tables that can be pushdown too
# these are tables that are referenced in all blocks of a DNF
# (a.x AND b.x) OR (a.y AND c.y)
# only table a can be push down
pushdown_tables: set[str] = set()
for a in predicates:
a_tables = exp.column_table_names(a)
for b in predicates:
a_tables &= exp.column_table_names(b)
pushdown_tables.update(a_tables)
conditions: dict[str, exp.Expr] = {}
# pushdown all predicates to their respective nodes
for table in sorted(pushdown_tables):
for predicate in predicates:
nodes = nodes_for_predicate(predicate, sources, scope_ref_count)
if table not in nodes:
continue
conditions[table] = (
exp.or_(conditions[table], predicate) if table in conditions else predicate
)
for name, node in nodes.items():
if name not in conditions:
continue
predicate = conditions[name]
if isinstance(node, exp.Join):
if join_index:
this_index = join_index[name]
predicate_tables = exp.column_table_names(predicate, name)
if not all(join_index.get(t, -1) < this_index for t in predicate_tables):
continue
node.on(predicate, copy=False)
elif isinstance(node, exp.Select):
inner_predicate = replace_aliases(node, predicate)
if find_in_scope(inner_predicate, exp.AggFunc):
node.having(inner_predicate, copy=False)
else:
node.where(inner_predicate, copy=False)
def nodes_for_predicate(
predicate: exp.Expr,
sources: Sources,
scope_ref_count: Mapping[int, int],
) -> dict[str, exp.Expr]:
nodes: dict[str, exp.Expr] = {}
tables = exp.column_table_names(predicate)
where_condition = isinstance(predicate.find_ancestor(exp.Join, exp.Where), exp.Where)
for table in sorted(tables):
node: exp.Expr | None
node, source = sources.get(table) or (None, None)
# if the predicate is in a where statement we can try to push it down
# we want to find the root join or from statement
if node and where_condition:
node = node.find_ancestor(exp.Join, exp.From)
# a node can reference a CTE which should be pushed down
if isinstance(node, exp.From) and not isinstance(source, exp.Table) and source is not None:
parent = source.parent
if parent is None:
raise ValueError("Source node has no parent")
with_: exp.With | None = parent.expression.args.get("with_")
if with_ and with_.recursive:
return {}
node = source.expression
if isinstance(node, exp.Join):
if node.side and node.side != "RIGHT":
return {}
nodes[table] = node
elif isinstance(node, exp.Select) and len(tables) == 1:
# We can't push down window expressions
has_window_expression = any(
select for select in node.selects if select.find(exp.Window)
)
# we can't push down predicates to select statements if they are referenced in
# multiple places.
if (
not node.args.get("group")
and scope_ref_count[id(source)] < 2
and not has_window_expression
# LIMIT/OFFSET and QUALIFY select rows before the outer predicate runs
and not node.args.get("limit")
and not node.args.get("offset")
and not node.args.get("qualify")
):
nodes[table] = node
return nodes
def replace_aliases(source: exp.Select, predicate: exp.Expr) -> exp.Expr:
aliases: dict[str, exp.Expr] = {}
for select in source.selects:
if isinstance(select, exp.Alias):
aliases[select.alias] = select.this
else:
aliases[select.name] = select
def _replace_alias(column: exp.Expr) -> exp.Expr:
if isinstance(column, exp.Column) and column.name in aliases:
return aliases[column.name].copy()
return column
return predicate.transform(_replace_alias)