@@ -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
8685def 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 )
0 commit comments