Skip to content

Commit b8aa36a

Browse files
authored
merge_subqueries fixes (tobymao#914)
* merge_subqueries fixes * fixup
1 parent 9bddf97 commit b8aa36a

6 files changed

Lines changed: 90 additions & 14 deletions

File tree

‎sqlglot/helper.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -256,7 +256,7 @@ def csv_reader(read_csv: exp.ReadCSV) -> t.Any:
256256
file.close()
257257

258258

259-
def find_new_name(taken: t.Sequence[str], base: str) -> str:
259+
def find_new_name(taken: t.Collection[str], base: str) -> str:
260260
"""
261261
Searches for a new name.
262262

‎sqlglot/optimizer/merge_subqueries.py‎

Lines changed: 42 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -67,11 +67,9 @@ def merge_ctes(expression, leave_tables_isolated=False):
6767

6868
singular_cte_selections = [v[0] for k, v in cte_selections.items() if len(v) == 1]
6969
for outer_scope, inner_scope, table in singular_cte_selections:
70-
inner_select = inner_scope.expression.unnest()
7170
from_or_join = table.find_ancestor(exp.From, exp.Join)
72-
if _mergeable(outer_scope, inner_select, leave_tables_isolated, from_or_join):
71+
if _mergeable(outer_scope, inner_scope, leave_tables_isolated, from_or_join):
7372
alias = table.alias_or_name
74-
7573
_rename_inner_sources(outer_scope, inner_scope, alias)
7674
_merge_from(outer_scope, inner_scope, table, alias)
7775
_merge_expressions(outer_scope, inner_scope, alias)
@@ -80,40 +78,41 @@ def merge_ctes(expression, leave_tables_isolated=False):
8078
_merge_order(outer_scope, inner_scope)
8179
_merge_hints(outer_scope, inner_scope)
8280
_pop_cte(inner_scope)
81+
outer_scope.clear_cache()
8382
return expression
8483

8584

8685
def merge_derived_tables(expression, leave_tables_isolated=False):
8786
for outer_scope in traverse_scope(expression):
8887
for subquery in outer_scope.derived_tables:
89-
inner_select = subquery.unnest()
9088
from_or_join = subquery.find_ancestor(exp.From, exp.Join)
91-
if _mergeable(outer_scope, inner_select, leave_tables_isolated, from_or_join):
92-
alias = subquery.alias_or_name
93-
inner_scope = outer_scope.sources[alias]
94-
89+
alias = subquery.alias_or_name
90+
inner_scope = outer_scope.sources[alias]
91+
if _mergeable(outer_scope, inner_scope, leave_tables_isolated, from_or_join):
9592
_rename_inner_sources(outer_scope, inner_scope, alias)
9693
_merge_from(outer_scope, inner_scope, subquery, alias)
9794
_merge_expressions(outer_scope, inner_scope, alias)
9895
_merge_joins(outer_scope, inner_scope, from_or_join)
9996
_merge_where(outer_scope, inner_scope, from_or_join)
10097
_merge_order(outer_scope, inner_scope)
10198
_merge_hints(outer_scope, inner_scope)
99+
outer_scope.clear_cache()
102100
return expression
103101

104102

105-
def _mergeable(outer_scope, inner_select, leave_tables_isolated, from_or_join):
103+
def _mergeable(outer_scope, inner_scope, leave_tables_isolated, from_or_join):
106104
"""
107105
Return True if `inner_select` can be merged into outer query.
108106
109107
Args:
110108
outer_scope (Scope)
111-
inner_select (exp.Select)
109+
inner_scope (Scope)
112110
leave_tables_isolated (bool)
113111
from_or_join (exp.From|exp.Join)
114112
Returns:
115113
bool: True if can be merged
116114
"""
115+
inner_select = inner_scope.expression.unnest()
117116

118117
def _is_a_window_expression_in_unmergable_operation():
119118
window_expressions = inner_select.find_all(exp.Window)
@@ -133,10 +132,40 @@ def _is_a_window_expression_in_unmergable_operation():
133132
]
134133
return any(window_expressions_in_unmergable)
135134

135+
def _outer_select_joins_on_inner_select_join():
136+
"""
137+
All columns from the inner select in the ON clause must be from the first FROM table.
138+
139+
That is, this can be merged:
140+
SELECT * FROM x JOIN (SELECT y.a AS a FROM y JOIN z) AS q ON x.a = q.a
141+
^^^ ^
142+
But this can't:
143+
SELECT * FROM x JOIN (SELECT z.a AS a FROM y JOIN z) AS q ON x.a = q.a
144+
^^^ ^
145+
"""
146+
if not isinstance(from_or_join, exp.Join):
147+
return False
148+
149+
alias = from_or_join.this.alias_or_name
150+
151+
on = from_or_join.args.get("on")
152+
if not on:
153+
return False
154+
selections = [c.name for c in on.find_all(exp.Column) if c.table == alias]
155+
inner_from = inner_scope.expression.args.get("from")
156+
if not inner_from:
157+
return False
158+
inner_from_table = inner_from.expressions[0].alias_or_name
159+
inner_projections = {s.alias_or_name: s for s in inner_scope.selects}
160+
return any(
161+
col.table != inner_from_table
162+
for selection in selections
163+
for col in inner_projections[selection].find_all(exp.Column)
164+
)
165+
136166
return (
137167
isinstance(outer_scope.expression, exp.Select)
138168
and isinstance(inner_select, exp.Select)
139-
and isinstance(inner_select, exp.Select)
140169
and not any(inner_select.args.get(arg) for arg in UNMERGABLE_ARGS)
141170
and inner_select.args.get("from")
142171
and not any(e.find(exp.AggFunc, exp.Select) for e in inner_select.expressions)
@@ -153,6 +182,7 @@ def _is_a_window_expression_in_unmergable_operation():
153182
j.side in {"FULL", "RIGHT"} for j in outer_scope.expression.args.get("joins", [])
154183
)
155184
)
185+
and not _outer_select_joins_on_inner_select_join()
156186
and not _is_a_window_expression_in_unmergable_operation()
157187
)
158188

@@ -168,7 +198,7 @@ def _rename_inner_sources(outer_scope, inner_scope, alias):
168198
"""
169199
taken = set(outer_scope.selected_sources)
170200
conflicts = taken.intersection(set(inner_scope.selected_sources))
171-
conflicts = conflicts - {alias}
201+
conflicts -= {alias}
172202

173203
for conflict in conflicts:
174204
new_name = find_new_name(taken, conflict)

‎sqlglot/optimizer/optimizer.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -52,7 +52,7 @@ def optimize(expression, schema=None, db=None, catalog=None, rules=RULES, **kwar
5252
If no schema is provided then the default schema defined at `sqlgot.schema` will be used
5353
db (str): specify the default database, as might be set by a `USE DATABASE db` statement
5454
catalog (str): specify the default catalog, as might be set by a `USE CATALOG c` statement
55-
rules (list): sequence of optimizer rules to use
55+
rules (sequence): sequence of optimizer rules to use
5656
**kwargs: If a rule has a keyword argument with a same name in **kwargs, it will be passed in.
5757
Returns:
5858
sqlglot.Expression: optimized expression

‎sqlglot/optimizer/pushdown_projections.py‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -79,6 +79,7 @@ def _remove_unused_selections(scope, parent_selections):
7979
order_refs = set()
8080

8181
new_selections = []
82+
removed = False
8283
for i, selection in enumerate(scope.selects):
8384
if (
8485
SELECT_ALL in parent_selections
@@ -88,12 +89,15 @@ def _remove_unused_selections(scope, parent_selections):
8889
new_selections.append(selection)
8990
else:
9091
removed_indexes.append(i)
92+
removed = True
9193

9294
# If there are no remaining selections, just select a single constant
9395
if not new_selections:
9496
new_selections.append(DEFAULT_SELECTION.copy())
9597

9698
scope.expression.set("expressions", new_selections)
99+
if removed:
100+
scope.clear_cache()
97101
return removed_indexes
98102

99103

‎tests/fixtures/optimizer/merge_subqueries.sql‎

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -311,3 +311,42 @@ FROM
311311
ON
312312
t1.cola = t2.cola;
313313
SELECT /*+ BROADCAST(a2) */ a1.cola AS cola, a2.cola AS cola FROM VALUES (1) AS a1(cola) JOIN VALUES (1) AS a2(cola) ON a1.cola = a2.cola;
314+
315+
# title: Nested subquery selects from same table as another subquery
316+
WITH i AS (
317+
SELECT
318+
x.a AS a
319+
FROM x AS x
320+
), j AS (
321+
SELECT
322+
x.a,
323+
x.b
324+
FROM x AS x
325+
), k AS (
326+
SELECT
327+
j.a,
328+
j.b
329+
FROM j AS j
330+
)
331+
SELECT
332+
i.a,
333+
k.b
334+
FROM i AS i
335+
LEFT JOIN k AS k
336+
ON i.a = k.a;
337+
SELECT x.a AS a, x_2.b AS b FROM x AS x LEFT JOIN x AS x_2 ON x.a = x_2.a;
338+
339+
# title: Outer select joins on inner select join
340+
WITH i AS (
341+
SELECT
342+
x.a AS a
343+
FROM y AS y
344+
JOIN x AS x
345+
ON y.b = x.b
346+
)
347+
SELECT
348+
x.a AS a
349+
FROM x AS x
350+
LEFT JOIN i AS i
351+
ON x.a = i.a;
352+
WITH i AS (SELECT x.a AS a FROM y AS y JOIN x AS x ON y.b = x.b) SELECT x.a AS a FROM x AS x LEFT JOIN i AS i ON x.a = i.a;

‎tests/fixtures/optimizer/pushdown_projections.sql‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -54,3 +54,6 @@ WITH t1 AS (SELECT q.cola AS cola FROM UNNEST(ARRAY(STRUCT(1 AS cola, 'test' AS
5454

5555
SELECT x FROM VALUES(1, 2) AS q(x, y);
5656
SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y);
57+
58+
SELECT i.a FROM x AS i LEFT JOIN (SELECT a, b FROM (SELECT a, b FROM x)) AS j ON i.a = j.a;
59+
SELECT i.a AS a FROM x AS i LEFT JOIN (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) AS j ON i.a = j.a;

0 commit comments

Comments
 (0)