Repository navigation
Expand file tree
/
Copy pathoptimize_joins.py
More file actions
129 lines (101 loc) · 4.32 KB
/
Copy pathoptimize_joins.py
File metadata and controls
129 lines (101 loc) · 4.32 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
from __future__ import annotations
from collections.abc import Iterable
from sqlglot import exp
from sqlglot.errors import OptimizeError
from sqlglot._typing import E
from sqlglot.helper import tsort
JOIN_ATTRS = ("on", "side", "kind", "using", "method")
def optimize_joins(expression: E) -> E:
"""
Removes cross joins if possible and reorder joins based on predicate dependencies.
Example:
>>> from sqlglot import parse_one
>>> optimize_joins(parse_one("SELECT * FROM x CROSS JOIN y JOIN z ON x.a = z.a AND y.a = z.a")).sql()
'SELECT * FROM x JOIN z ON x.a = z.a AND TRUE JOIN y ON y.a = z.a'
"""
for select in expression.find_all(exp.Select):
joins: list[exp.Join] = select.args.get("joins", [])
if not _is_reorderable(joins):
continue
references: dict[str, list[exp.Join]] = {}
cross_joins: list[tuple[str, exp.Join]] = []
for join in joins:
tables = other_table_names(join)
if tables:
for table in tables:
references[table] = references.get(table, []) + [join]
else:
cross_joins.append((join.alias_or_name, join))
for name, join in cross_joins:
for dep in references.get(name, []):
on = dep.args["on"]
if isinstance(on, exp.Connector):
if len(other_table_names(dep)) < 2:
continue
operator = type(on)
for predicate in on.flatten():
if name in exp.column_table_names(predicate):
predicate.replace(exp.true())
predicate = exp._combine(
[join.args.get("on"), predicate], operator, copy=False
)
join.on(predicate, append=False, copy=False)
expression = reorder_joins(expression)
expression = normalize(expression)
return expression
def reorder_joins(expression: E) -> E:
"""
Reorder joins by topological sort order based on predicate references.
"""
for from_ in expression.find_all(exp.From):
parent = from_.parent
if parent is None:
raise OptimizeError("FROM clause without parent expression")
joins: list[exp.Join] = parent.args.get("joins", [])
if not _is_reorderable(joins):
continue
joins_by_name = {join.alias_or_name: join for join in joins}
dag = {name: other_table_names(join) for name, join in joins_by_name.items()}
parent.set(
"joins",
[
joins_by_name[name]
for name in tsort(dag)
if name != from_.alias_or_name and name in joins_by_name
],
)
return expression
def normalize(expression: E) -> E:
"""
Remove INNER and OUTER from joins as they are optional.
"""
for join in expression.find_all(exp.Join):
if not any(join.args.get(k) for k in JOIN_ATTRS):
join.set("kind", "CROSS")
if join.kind == "CROSS":
join.set("on", None)
else:
if join.kind in ("INNER", "OUTER"):
join.set("kind", None)
if not join.args.get("on") and not join.args.get("using"):
join.set("on", exp.true())
return expression
def other_table_names(join: exp.Join) -> set[str]:
on = join.args.get("on")
return exp.column_table_names(on, join.alias_or_name) if on else set()
def _is_reorderable(joins: Iterable[exp.Join]) -> bool:
"""
Checks if joins can be reordered without changing query semantics.
Joins with a side (LEFT, RIGHT, FULL) cannot be reordered easily,
the order affects which rows are included in the result.
Example:
>>> from sqlglot import parse_one, exp
>>> from sqlglot.optimizer.optimize_joins import _is_reorderable
>>> ast = parse_one("SELECT * FROM x JOIN y ON x.id = y.id JOIN z ON y.id = z.id")
>>> _is_reorderable(ast.find(exp.Select).args.get("joins", []))
True
>>> ast = parse_one("SELECT * FROM x LEFT JOIN y ON x.id = y.id JOIN z ON y.id = z.id")
>>> _is_reorderable(ast.find(exp.Select).args.get("joins", []))
False
"""
return not any(join.side for join in joins)