We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
'Union' object has no attribute 'group_by'
```
from sqlglot import optimizer, parse_one

sql = "SELECT * FROM event WHERE priority = 'High' AND tagname IN (SELECT tag_input AS tagname FROM cascade WHERE tag_input = 'XXX' OR tag_output = 'XXX' UNION SELECT tag_output AS tagname FROM cascade WHERE tag_input = 'XXX' OR tag_output = 'XXX')"

q = parse_one(sql, "postgres")
oq = optimizer.optimize(q, dnf=True, dialect="postgres")
```

will produce the following error:
```Traceback (most recent call last):
  File "/Users/elham/Documents/KG_LLM/siwarex/api/exm/test.py", line 6, in <module>
    oq = optimizer.optimize(q, dnf=True, dialect="postgres")
  File "/Users/elham/anaconda3/envs/siwarex3/lib/python3.10/site-packages/sqlglot/optimizer/optimizer.py", line 92, in optimize
    expression = rule(expression, **rule_kwargs)
  File "/Users/elham/anaconda3/envs/siwarex3/lib/python3.10/site-packages/sqlglot/optimizer/unnest_subqueries.py", line 34, in unnest_subqueries
    unnest(select, parent, next_alias_name)
  File "/Users/elham/anaconda3/envs/siwarex3/lib/python3.10/site-packages/sqlglot/optimizer/unnest_subqueries.py", line 107, in unnest
    select = select.group_by(value.this, copy=False)
AttributeError: 'Union' object has no attribute 'group_by'
```

--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `sqlglot/optimizer/unnest_subqueries.py`
Content:
```
1 from sqlglot import exp
2 from sqlglot.helper import name_sequence
3 from sqlglot.optimizer.scope import ScopeType, traverse_scope
4 
5 
6 def unnest_subqueries(expression):
7     """
8     Rewrite sqlglot AST to convert some predicates with subqueries into joins.
9 
10     Convert scalar subqueries into cross joins.
11     Convert correlated or vectorized subqueries into a group by so it is not a many to many left join.
12 
13     Example:
14         >>> import sqlglot
15         >>> expression = sqlglot.parse_one("SELECT * FROM x AS x WHERE (SELECT y.a AS a FROM y AS y WHERE x.a = y.a) = 1 ")
16         >>> unnest_subqueries(expression).sql()
17         'SELECT * FROM x AS x LEFT JOIN (SELECT y.a AS a FROM y AS y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE _u_0.a = 1'
18 
19     Args:
20         expression (sqlglot.Expression): expression to unnest
21     Returns:
22         sqlglot.Expression: unnested expression
23     """
24     next_alias_name = name_sequence("_u_")
25 
26     for scope in traverse_scope(expression):
27         select = scope.expression
28         parent = select.parent_select
29         if not parent:
30             continue
31         if scope.external_columns:
32             decorrelate(select, parent, scope.external_columns, next_alias_name)
33         elif scope.scope_type == ScopeType.SUBQUERY:
34             unnest(select, parent, next_alias_name)
35 
36     return expression
37 
38 
39 def unnest(select, parent_select, next_alias_name):
40     if len(select.selects) > 1:
41         return
42 
43     predicate = select.find_ancestor(exp.Condition)
44     alias = next_alias_name()
45 
46     if (
47         not predicate
48         or parent_select is not predicate.parent_select
49         or not parent_select.args.get("from")
50     ):
51         return
52 
53     clause = predicate.find_ancestor(exp.Having, exp.Where, exp.Join)
54 
55     # This subquery returns a scalar and can just be converted to a cross join
56     if not isinstance(predicate, (exp.In, exp.Any)):
57         column = exp.column(select.selects[0].alias_or_name, alias)
58 
59         clause_parent_select = clause.parent_select if clause else None
60 
61         if (isinstance(clause, exp.Having) and clause_parent_select is parent_select) or (
62             (not clause or clause_parent_select is not parent_select)
63             and (
64                 parent_select.args.get("group")
65                 or any(projection.find(exp.AggFunc) for projection in parent_select.selects)
66             )
67         ):
68             column = exp.Max(this=column)
69         elif not isinstance(select.parent, exp.Subquery):
70             return
71 
72         _replace(select.parent, column)
73         parent_select.join(select, join_type="CROSS", join_alias=alias, copy=False)
74         return
75 
76     if select.find(exp.Limit, exp.Offset):
77         return
78 
79     if isinstance(predicate, exp.Any):
80         predicate = predicate.find_ancestor(exp.EQ)
81 
82         if not predicate or parent_select is not predicate.parent_select:
83             return
84 
85     column = _other_operand(predicate)
86     value = select.selects[0]
87 
88     join_key = exp.column(value.alias, alias)
89     join_key_not_null = join_key.is_(exp.null()).not_()
90 
91     if isinstance(clause, exp.Join):
92         _replace(predicate, exp.true())
93         parent_select.where(join_key_not_null, copy=False)
94     else:
95         _replace(predicate, join_key_not_null)
96 
97     group = select.args.get("group")
98 
99     if group:
100         if {value.this} != set(group.expressions):
101             select = (
102                 exp.select(exp.column(value.alias, "_q"))
103                 .from_(select.subquery("_q", copy=False), copy=False)
104                 .group_by(exp.column(value.alias, "_q"), copy=False)
105             )
106     else:
107         select = select.group_by(value.this, copy=False)
108 
109     parent_select.join(
110         select,
111         on=column.eq(join_key),
112         join_type="LEFT",
113         join_alias=alias,
114         copy=False,
115     )
116 
117 
118 def decorrelate(select, parent_select, external_columns, next_alias_name):
119     where = select.args.get("where")
120 
121     if not where or where.find(exp.Or) or select.find(exp.Limit, exp.Offset):
122         return
123 
124     table_alias = next_alias_name()
125     keys = []
126 
127     # for all external columns in the where statement, find the relevant predicate
128     # keys to convert it into a join
129     for column in external_columns:
130         if column.find_ancestor(exp.Where) is not where:
131             return
132 
133         predicate = column.find_ancestor(exp.Predicate)
134 
135         if not predicate or predicate.find_ancestor(exp.Where) is not where:
136             return
137 
138         if isinstance(predicate, exp.Binary):
139             key = (
140                 predicate.right
141                 if any(node is column for node in predicate.left.walk())
142                 else predicate.left
143             )
144         else:
145             return
146 
147         keys.append((key, column, predicate))
148 
149     if not any(isinstance(predicate, exp.EQ) for *_, predicate in keys):
150         return
151 
152     is_subquery_projection = any(
153         node is select.parent for node in parent_select.selects if isinstance(node, exp.Subquery)
154     )
155 
156     value = select.selects[0]
157     key_aliases = {}
158     group_by = []
159 
160     for key, _, predicate in keys:
161         # if we filter on the value of the subquery, it needs to be unique
162         if key == value.this:
163             key_aliases[key] = value.alias
164             group_by.append(key)
165         else:
166             if key not in key_aliases:
167                 key_aliases[key] = next_alias_name()
168             # all predicates that are equalities must also be in the unique
169             # so that we don't do a many to many join
170             if isinstance(predicate, exp.EQ) and key not in group_by:
171                 group_by.append(key)
172 
173     parent_predicate = select.find_ancestor(exp.Predicate)
174 
175     # if the value of the subquery is not an agg or a key, we need to collect it into an array
176     # so that it can be grouped. For subquery projections, we use a MAX aggregation instead.
177     agg_func = exp.Max if is_subquery_projection else exp.ArrayAgg
178     if not value.find(exp.AggFunc) and value.this not in group_by:
179         select.select(
180             exp.alias_(agg_func(this=value.this), value.alias, quoted=False),
181             append=False,
182             copy=False,
183         )
184 
185     # exists queries should not have any selects as it only checks if there are any rows
186     # all selects will be added by the optimizer and only used for join keys
187     if isinstance(parent_predicate, exp.Exists):
188         select.args["expressions"] = []
189 
190     for key, alias in key_aliases.items():
191         if key in group_by:
192             # add all keys to the projections of the subquery
193             # so that we can use it as a join key
194             if isinstance(parent_predicate, exp.Exists) or key != value.this:
195                 select.select(f"{key} AS {alias}", copy=False)
196         else:
197             select.select(exp.alias_(agg_func(this=key.copy()), alias, quoted=False), copy=False)
198 
199     alias = exp.column(value.alias, table_alias)
200     other = _other_operand(parent_predicate)
201 
202     if isinstance(parent_predicate, exp.Exists):
203         alias = exp.column(list(key_aliases.values())[0], table_alias)
204         parent_predicate = _replace(parent_predicate, f"NOT {alias} IS NULL")
205     elif isinstance(parent_predicate, exp.All):
206         parent_predicate = _replace(
207             parent_predicate.parent, f"ARRAY_ALL({alias}, _x -> _x = {other})"
208         )
209     elif isinstance(parent_predicate, exp.Any):
210         if value.this in group_by:
211             parent_predicate = _replace(parent_predicate.parent, f"{other} = {alias}")
212         else:
213             parent_predicate = _replace(parent_predicate, f"ARRAY_ANY({alias}, _x -> _x = {other})")
214     elif isinstance(parent_predicate, exp.In):
215         if value.this in group_by:
216             parent_predicate = _replace(parent_predicate, f"{other} = {alias}")
217         else:
218             parent_predicate = _replace(
219                 parent_predicate,
220                 f"ARRAY_ANY({alias}, _x -> _x = {parent_predicate.this})",
221             )
222     else:
223         if is_subquery_projection:
224             alias = exp.alias_(alias, select.parent.alias)
225 
226         # COUNT always returns 0 on empty datasets, so we need take that into consideration here
227         # by transforming all counts into 0 and using that as the coalesced value
228         if value.find(exp.Count):
229 
230             def remove_aggs(node):
231                 if isinstance(node, exp.Count):
232                     return exp.Literal.number(0)
233                 elif isinstance(node, exp.AggFunc):
234                     return exp.null()
235                 return node
236 
237             alias = exp.Coalesce(
238                 this=alias,
239                 expressions=[value.this.transform(remove_aggs)],
240             )
241 
242         select.parent.replace(alias)
243 
244     for key, column, predicate in keys:
245         predicate.replace(exp.true())
246         nested = exp.column(key_aliases[key], table_alias)
247 
248         if is_subquery_projection:
249             key.replace(nested)
250             continue
251 
252         if key in group_by:
253             key.replace(nested)
254         elif isinstance(predicate, exp.EQ):
255             parent_predicate = _replace(
256                 parent_predicate,
257                 f"({parent_predicate} AND ARRAY_CONTAINS({nested}, {column}))",
258             )
259         else:
260             key.replace(exp.to_identifier("_x"))
261             parent_predicate = _replace(
262                 parent_predicate,
263                 f"({parent_predicate} AND ARRAY_ANY({nested}, _x -> {predicate}))",
264             )
265 
266     parent_select.join(
267         select.group_by(*group_by, copy=False),
268         on=[predicate for *_, predicate in keys if isinstance(predicate, exp.EQ)],
269         join_type="LEFT",
270         join_alias=table_alias,
271         copy=False,
272     )
273 
274 
275 def _replace(expression, condition):
276     return expression.replace(exp.condition(condition))
277 
278 
279 def _other_operand(expression):
280     if isinstance(expression, exp.In):
281         return expression.this
282 
283     if isinstance(expression, (exp.Any, exp.All)):
284         return _other_operand(expression.parent)
285 
286     if isinstance(expression, exp.Binary):
287         return (
288             expression.right
289             if isinstance(expression.left, (exp.Subquery, exp.Any, exp.Exists, exp.All))
290             else expression.left
291         )
292 
293     return None
294
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/sqlglot/optimizer/unnest_subqueries.py b/sqlglot/optimizer/unnest_subqueries.py
--- a/sqlglot/optimizer/unnest_subqueries.py
+++ b/sqlglot/optimizer/unnest_subqueries.py
@@ -41,8 +41,6 @@
         return
 
     predicate = select.find_ancestor(exp.Condition)
-    alias = next_alias_name()
-
     if (
         not predicate
         or parent_select is not predicate.parent_select
@@ -50,6 +48,10 @@
     ):
         return
 
+    if isinstance(select, exp.Union):
+        select = exp.select(*select.selects).from_(select.subquery(next_alias_name()))
+
+    alias = next_alias_name()
     clause = predicate.find_ancestor(exp.Having, exp.Where, exp.Join)
 
     # This subquery returns a scalar and can just be converted to a cross join

{"golden_diff": "diff --git a/sqlglot/optimizer/unnest_subqueries.py b/sqlglot/optimizer/unnest_subqueries.py\n--- a/sqlglot/optimizer/unnest_subqueries.py\n+++ b/sqlglot/optimizer/unnest_subqueries.py\n@@ -41,8 +41,6 @@\n         return\n \n     predicate = select.find_ancestor(exp.Condition)\n-    alias = next_alias_name()\n-\n     if (\n         not predicate\n         or parent_select is not predicate.parent_select\n@@ -50,6 +48,10 @@\n     ):\n         return\n \n+    if isinstance(select, exp.Union):\n+        select = exp.select(*select.selects).from_(select.subquery(next_alias_name()))\n+\n+    alias = next_alias_name()\n     clause = predicate.find_ancestor(exp.Having, exp.Where, exp.Join)\n \n     # This subquery returns a scalar and can just be converted to a cross join\n", "issue": "'Union' object has no attribute 'group_by'\n```\r\nfrom sqlglot import optimizer, parse_one\r\n\r\nsql = \"SELECT * FROM event WHERE priority = 'High' AND tagname IN (SELECT tag_input AS tagname FROM cascade WHERE tag_input = 'XXX' OR tag_output = 'XXX' UNION SELECT tag_output AS tagname FROM cascade WHERE tag_input = 'XXX' OR tag_output = 'XXX')\"\r\n\r\nq = parse_one(sql, \"postgres\")\r\noq = optimizer.optimize(q, dnf=True, dialect=\"postgres\")\r\n```\r\n\r\nwill produce the following error:\r\n```Traceback (most recent call last):\r\n  File \"/Users/elham/Documents/KG_LLM/siwarex/api/exm/test.py\", line 6, in <module>\r\n    oq = optimizer.optimize(q, dnf=True, dialect=\"postgres\")\r\n  File \"/Users/elham/anaconda3/envs/siwarex3/lib/python3.10/site-packages/sqlglot/optimizer/optimizer.py\", line 92, in optimize\r\n    expression = rule(expression, **rule_kwargs)\r\n  File \"/Users/elham/anaconda3/envs/siwarex3/lib/python3.10/site-packages/sqlglot/optimizer/unnest_subqueries.py\", line 34, in unnest_subqueries\r\n    unnest(select, parent, next_alias_name)\r\n  File \"/Users/elham/anaconda3/envs/siwarex3/lib/python3.10/site-packages/sqlglot/optimizer/unnest_subqueries.py\", line 107, in unnest\r\n    select = select.group_by(value.this, copy=False)\r\nAttributeError: 'Union' object has no attribute 'group_by'\r\n```\n", "before_files": [{"content": "from sqlglot import exp\nfrom sqlglot.helper import name_sequence\nfrom sqlglot.optimizer.scope import ScopeType, traverse_scope\n\n\ndef unnest_subqueries(expression):\n    \"\"\"\n    Rewrite sqlglot AST to convert some predicates with subqueries into joins.\n\n    Convert scalar subqueries into cross joins.\n    Convert correlated or vectorized subqueries into a group by so it is not a many to many left join.\n\n    Example:\n        >>> import sqlglot\n        >>> expression = sqlglot.parse_one(\"SELECT * FROM x AS x WHERE (SELECT y.a AS a FROM y AS y WHERE x.a = y.a) = 1 \")\n        >>> unnest_subqueries(expression).sql()\n        'SELECT * FROM x AS x LEFT JOIN (SELECT y.a AS a FROM y AS y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE _u_0.a = 1'\n\n    Args:\n        expression (sqlglot.Expression): expression to unnest\n    Returns:\n        sqlglot.Expression: unnested expression\n    \"\"\"\n    next_alias_name = name_sequence(\"_u_\")\n\n    for scope in traverse_scope(expression):\n        select = scope.expression\n        parent = select.parent_select\n        if not parent:\n            continue\n        if scope.external_columns:\n            decorrelate(select, parent, scope.external_columns, next_alias_name)\n        elif scope.scope_type == ScopeType.SUBQUERY:\n            unnest(select, parent, next_alias_name)\n\n    return expression\n\n\ndef unnest(select, parent_select, next_alias_name):\n    if len(select.selects) > 1:\n        return\n\n    predicate = select.find_ancestor(exp.Condition)\n    alias = next_alias_name()\n\n    if (\n        not predicate\n        or parent_select is not predicate.parent_select\n        or not parent_select.args.get(\"from\")\n    ):\n        return\n\n    clause = predicate.find_ancestor(exp.Having, exp.Where, exp.Join)\n\n    # This subquery returns a scalar and can just be converted to a cross join\n    if not isinstance(predicate, (exp.In, exp.Any)):\n        column = exp.column(select.selects[0].alias_or_name, alias)\n\n        clause_parent_select = clause.parent_select if clause else None\n\n        if (isinstance(clause, exp.Having) and clause_parent_select is parent_select) or (\n            (not clause or clause_parent_select is not parent_select)\n            and (\n                parent_select.args.get(\"group\")\n                or any(projection.find(exp.AggFunc) for projection in parent_select.selects)\n            )\n        ):\n            column = exp.Max(this=column)\n        elif not isinstance(select.parent, exp.Subquery):\n            return\n\n        _replace(select.parent, column)\n        parent_select.join(select, join_type=\"CROSS\", join_alias=alias, copy=False)\n        return\n\n    if select.find(exp.Limit, exp.Offset):\n        return\n\n    if isinstance(predicate, exp.Any):\n        predicate = predicate.find_ancestor(exp.EQ)\n\n        if not predicate or parent_select is not predicate.parent_select:\n            return\n\n    column = _other_operand(predicate)\n    value = select.selects[0]\n\n    join_key = exp.column(value.alias, alias)\n    join_key_not_null = join_key.is_(exp.null()).not_()\n\n    if isinstance(clause, exp.Join):\n        _replace(predicate, exp.true())\n        parent_select.where(join_key_not_null, copy=False)\n    else:\n        _replace(predicate, join_key_not_null)\n\n    group = select.args.get(\"group\")\n\n    if group:\n        if {value.this} != set(group.expressions):\n            select = (\n                exp.select(exp.column(value.alias, \"_q\"))\n                .from_(select.subquery(\"_q\", copy=False), copy=False)\n                .group_by(exp.column(value.alias, \"_q\"), copy=False)\n            )\n    else:\n        select = select.group_by(value.this, copy=False)\n\n    parent_select.join(\n        select,\n        on=column.eq(join_key),\n        join_type=\"LEFT\",\n        join_alias=alias,\n        copy=False,\n    )\n\n\ndef decorrelate(select, parent_select, external_columns, next_alias_name):\n    where = select.args.get(\"where\")\n\n    if not where or where.find(exp.Or) or select.find(exp.Limit, exp.Offset):\n        return\n\n    table_alias = next_alias_name()\n    keys = []\n\n    # for all external columns in the where statement, find the relevant predicate\n    # keys to convert it into a join\n    for column in external_columns:\n        if column.find_ancestor(exp.Where) is not where:\n            return\n\n        predicate = column.find_ancestor(exp.Predicate)\n\n        if not predicate or predicate.find_ancestor(exp.Where) is not where:\n            return\n\n        if isinstance(predicate, exp.Binary):\n            key = (\n                predicate.right\n                if any(node is column for node in predicate.left.walk())\n                else predicate.left\n            )\n        else:\n            return\n\n        keys.append((key, column, predicate))\n\n    if not any(isinstance(predicate, exp.EQ) for *_, predicate in keys):\n        return\n\n    is_subquery_projection = any(\n        node is select.parent for node in parent_select.selects if isinstance(node, exp.Subquery)\n    )\n\n    value = select.selects[0]\n    key_aliases = {}\n    group_by = []\n\n    for key, _, predicate in keys:\n        # if we filter on the value of the subquery, it needs to be unique\n        if key == value.this:\n            key_aliases[key] = value.alias\n            group_by.append(key)\n        else:\n            if key not in key_aliases:\n                key_aliases[key] = next_alias_name()\n            # all predicates that are equalities must also be in the unique\n            # so that we don't do a many to many join\n            if isinstance(predicate, exp.EQ) and key not in group_by:\n                group_by.append(key)\n\n    parent_predicate = select.find_ancestor(exp.Predicate)\n\n    # if the value of the subquery is not an agg or a key, we need to collect it into an array\n    # so that it can be grouped. For subquery projections, we use a MAX aggregation instead.\n    agg_func = exp.Max if is_subquery_projection else exp.ArrayAgg\n    if not value.find(exp.AggFunc) and value.this not in group_by:\n        select.select(\n            exp.alias_(agg_func(this=value.this), value.alias, quoted=False),\n            append=False,\n            copy=False,\n        )\n\n    # exists queries should not have any selects as it only checks if there are any rows\n    # all selects will be added by the optimizer and only used for join keys\n    if isinstance(parent_predicate, exp.Exists):\n        select.args[\"expressions\"] = []\n\n    for key, alias in key_aliases.items():\n        if key in group_by:\n            # add all keys to the projections of the subquery\n            # so that we can use it as a join key\n            if isinstance(parent_predicate, exp.Exists) or key != value.this:\n                select.select(f\"{key} AS {alias}\", copy=False)\n        else:\n            select.select(exp.alias_(agg_func(this=key.copy()), alias, quoted=False), copy=False)\n\n    alias = exp.column(value.alias, table_alias)\n    other = _other_operand(parent_predicate)\n\n    if isinstance(parent_predicate, exp.Exists):\n        alias = exp.column(list(key_aliases.values())[0], table_alias)\n        parent_predicate = _replace(parent_predicate, f\"NOT {alias} IS NULL\")\n    elif isinstance(parent_predicate, exp.All):\n        parent_predicate = _replace(\n            parent_predicate.parent, f\"ARRAY_ALL({alias}, _x -> _x = {other})\"\n        )\n    elif isinstance(parent_predicate, exp.Any):\n        if value.this in group_by:\n            parent_predicate = _replace(parent_predicate.parent, f\"{other} = {alias}\")\n        else:\n            parent_predicate = _replace(parent_predicate, f\"ARRAY_ANY({alias}, _x -> _x = {other})\")\n    elif isinstance(parent_predicate, exp.In):\n        if value.this in group_by:\n            parent_predicate = _replace(parent_predicate, f\"{other} = {alias}\")\n        else:\n            parent_predicate = _replace(\n                parent_predicate,\n                f\"ARRAY_ANY({alias}, _x -> _x = {parent_predicate.this})\",\n            )\n    else:\n        if is_subquery_projection:\n            alias = exp.alias_(alias, select.parent.alias)\n\n        # COUNT always returns 0 on empty datasets, so we need take that into consideration here\n        # by transforming all counts into 0 and using that as the coalesced value\n        if value.find(exp.Count):\n\n            def remove_aggs(node):\n                if isinstance(node, exp.Count):\n                    return exp.Literal.number(0)\n                elif isinstance(node, exp.AggFunc):\n                    return exp.null()\n                return node\n\n            alias = exp.Coalesce(\n                this=alias,\n                expressions=[value.this.transform(remove_aggs)],\n            )\n\n        select.parent.replace(alias)\n\n    for key, column, predicate in keys:\n        predicate.replace(exp.true())\n        nested = exp.column(key_aliases[key], table_alias)\n\n        if is_subquery_projection:\n            key.replace(nested)\n            continue\n\n        if key in group_by:\n            key.replace(nested)\n        elif isinstance(predicate, exp.EQ):\n            parent_predicate = _replace(\n                parent_predicate,\n                f\"({parent_predicate} AND ARRAY_CONTAINS({nested}, {column}))\",\n            )\n        else:\n            key.replace(exp.to_identifier(\"_x\"))\n            parent_predicate = _replace(\n                parent_predicate,\n                f\"({parent_predicate} AND ARRAY_ANY({nested}, _x -> {predicate}))\",\n            )\n\n    parent_select.join(\n        select.group_by(*group_by, copy=False),\n        on=[predicate for *_, predicate in keys if isinstance(predicate, exp.EQ)],\n        join_type=\"LEFT\",\n        join_alias=table_alias,\n        copy=False,\n    )\n\n\ndef _replace(expression, condition):\n    return expression.replace(exp.condition(condition))\n\n\ndef _other_operand(expression):\n    if isinstance(expression, exp.In):\n        return expression.this\n\n    if isinstance(expression, (exp.Any, exp.All)):\n        return _other_operand(expression.parent)\n\n    if isinstance(expression, exp.Binary):\n        return (\n            expression.right\n            if isinstance(expression.left, (exp.Subquery, exp.Any, exp.Exists, exp.All))\n            else expression.left\n        )\n\n    return None\n", "path": "sqlglot/optimizer/unnest_subqueries.py"}], "after_files": [{"content": "from sqlglot import exp\nfrom sqlglot.helper import name_sequence\nfrom sqlglot.optimizer.scope import ScopeType, traverse_scope\n\n\ndef unnest_subqueries(expression):\n    \"\"\"\n    Rewrite sqlglot AST to convert some predicates with subqueries into joins.\n\n    Convert scalar subqueries into cross joins.\n    Convert correlated or vectorized subqueries into a group by so it is not a many to many left join.\n\n    Example:\n        >>> import sqlglot\n        >>> expression = sqlglot.parse_one(\"SELECT * FROM x AS x WHERE (SELECT y.a AS a FROM y AS y WHERE x.a = y.a) = 1 \")\n        >>> unnest_subqueries(expression).sql()\n        'SELECT * FROM x AS x LEFT JOIN (SELECT y.a AS a FROM y AS y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE _u_0.a = 1'\n\n    Args:\n        expression (sqlglot.Expression): expression to unnest\n    Returns:\n        sqlglot.Expression: unnested expression\n    \"\"\"\n    next_alias_name = name_sequence(\"_u_\")\n\n    for scope in traverse_scope(expression):\n        select = scope.expression\n        parent = select.parent_select\n        if not parent:\n            continue\n        if scope.external_columns:\n            decorrelate(select, parent, scope.external_columns, next_alias_name)\n        elif scope.scope_type == ScopeType.SUBQUERY:\n            unnest(select, parent, next_alias_name)\n\n    return expression\n\n\ndef unnest(select, parent_select, next_alias_name):\n    if len(select.selects) > 1:\n        return\n\n    predicate = select.find_ancestor(exp.Condition)\n    if (\n        not predicate\n        or parent_select is not predicate.parent_select\n        or not parent_select.args.get(\"from\")\n    ):\n        return\n\n    if isinstance(select, exp.Union):\n        select = exp.select(*select.selects).from_(select.subquery(next_alias_name()))\n\n    alias = next_alias_name()\n    clause = predicate.find_ancestor(exp.Having, exp.Where, exp.Join)\n\n    # This subquery returns a scalar and can just be converted to a cross join\n    if not isinstance(predicate, (exp.In, exp.Any)):\n        column = exp.column(select.selects[0].alias_or_name, alias)\n\n        clause_parent_select = clause.parent_select if clause else None\n\n        if (isinstance(clause, exp.Having) and clause_parent_select is parent_select) or (\n            (not clause or clause_parent_select is not parent_select)\n            and (\n                parent_select.args.get(\"group\")\n                or any(projection.find(exp.AggFunc) for projection in parent_select.selects)\n            )\n        ):\n            column = exp.Max(this=column)\n        elif not isinstance(select.parent, exp.Subquery):\n            return\n\n        _replace(select.parent, column)\n        parent_select.join(select, join_type=\"CROSS\", join_alias=alias, copy=False)\n        return\n\n    if select.find(exp.Limit, exp.Offset):\n        return\n\n    if isinstance(predicate, exp.Any):\n        predicate = predicate.find_ancestor(exp.EQ)\n\n        if not predicate or parent_select is not predicate.parent_select:\n            return\n\n    column = _other_operand(predicate)\n    value = select.selects[0]\n\n    join_key = exp.column(value.alias, alias)\n    join_key_not_null = join_key.is_(exp.null()).not_()\n\n    if isinstance(clause, exp.Join):\n        _replace(predicate, exp.true())\n        parent_select.where(join_key_not_null, copy=False)\n    else:\n        _replace(predicate, join_key_not_null)\n\n    group = select.args.get(\"group\")\n\n    if group:\n        if {value.this} != set(group.expressions):\n            select = (\n                exp.select(exp.column(value.alias, \"_q\"))\n                .from_(select.subquery(\"_q\", copy=False), copy=False)\n                .group_by(exp.column(value.alias, \"_q\"), copy=False)\n            )\n    else:\n        select = select.group_by(value.this, copy=False)\n\n    parent_select.join(\n        select,\n        on=column.eq(join_key),\n        join_type=\"LEFT\",\n        join_alias=alias,\n        copy=False,\n    )\n\n\ndef decorrelate(select, parent_select, external_columns, next_alias_name):\n    where = select.args.get(\"where\")\n\n    if not where or where.find(exp.Or) or select.find(exp.Limit, exp.Offset):\n        return\n\n    table_alias = next_alias_name()\n    keys = []\n\n    # for all external columns in the where statement, find the relevant predicate\n    # keys to convert it into a join\n    for column in external_columns:\n        if column.find_ancestor(exp.Where) is not where:\n            return\n\n        predicate = column.find_ancestor(exp.Predicate)\n\n        if not predicate or predicate.find_ancestor(exp.Where) is not where:\n            return\n\n        if isinstance(predicate, exp.Binary):\n            key = (\n                predicate.right\n                if any(node is column for node in predicate.left.walk())\n                else predicate.left\n            )\n        else:\n            return\n\n        keys.append((key, column, predicate))\n\n    if not any(isinstance(predicate, exp.EQ) for *_, predicate in keys):\n        return\n\n    is_subquery_projection = any(\n        node is select.parent for node in parent_select.selects if isinstance(node, exp.Subquery)\n    )\n\n    value = select.selects[0]\n    key_aliases = {}\n    group_by = []\n\n    for key, _, predicate in keys:\n        # if we filter on the value of the subquery, it needs to be unique\n        if key == value.this:\n            key_aliases[key] = value.alias\n            group_by.append(key)\n        else:\n            if key not in key_aliases:\n                key_aliases[key] = next_alias_name()\n            # all predicates that are equalities must also be in the unique\n            # so that we don't do a many to many join\n            if isinstance(predicate, exp.EQ) and key not in group_by:\n                group_by.append(key)\n\n    parent_predicate = select.find_ancestor(exp.Predicate)\n\n    # if the value of the subquery is not an agg or a key, we need to collect it into an array\n    # so that it can be grouped. For subquery projections, we use a MAX aggregation instead.\n    agg_func = exp.Max if is_subquery_projection else exp.ArrayAgg\n    if not value.find(exp.AggFunc) and value.this not in group_by:\n        select.select(\n            exp.alias_(agg_func(this=value.this), value.alias, quoted=False),\n            append=False,\n            copy=False,\n        )\n\n    # exists queries should not have any selects as it only checks if there are any rows\n    # all selects will be added by the optimizer and only used for join keys\n    if isinstance(parent_predicate, exp.Exists):\n        select.args[\"expressions\"] = []\n\n    for key, alias in key_aliases.items():\n        if key in group_by:\n            # add all keys to the projections of the subquery\n            # so that we can use it as a join key\n            if isinstance(parent_predicate, exp.Exists) or key != value.this:\n                select.select(f\"{key} AS {alias}\", copy=False)\n        else:\n            select.select(exp.alias_(agg_func(this=key.copy()), alias, quoted=False), copy=False)\n\n    alias = exp.column(value.alias, table_alias)\n    other = _other_operand(parent_predicate)\n\n    if isinstance(parent_predicate, exp.Exists):\n        alias = exp.column(list(key_aliases.values())[0], table_alias)\n        parent_predicate = _replace(parent_predicate, f\"NOT {alias} IS NULL\")\n    elif isinstance(parent_predicate, exp.All):\n        parent_predicate = _replace(\n            parent_predicate.parent, f\"ARRAY_ALL({alias}, _x -> _x = {other})\"\n        )\n    elif isinstance(parent_predicate, exp.Any):\n        if value.this in group_by:\n            parent_predicate = _replace(parent_predicate.parent, f\"{other} = {alias}\")\n        else:\n            parent_predicate = _replace(parent_predicate, f\"ARRAY_ANY({alias}, _x -> _x = {other})\")\n    elif isinstance(parent_predicate, exp.In):\n        if value.this in group_by:\n            parent_predicate = _replace(parent_predicate, f\"{other} = {alias}\")\n        else:\n            parent_predicate = _replace(\n                parent_predicate,\n                f\"ARRAY_ANY({alias}, _x -> _x = {parent_predicate.this})\",\n            )\n    else:\n        if is_subquery_projection:\n            alias = exp.alias_(alias, select.parent.alias)\n\n        # COUNT always returns 0 on empty datasets, so we need take that into consideration here\n        # by transforming all counts into 0 and using that as the coalesced value\n        if value.find(exp.Count):\n\n            def remove_aggs(node):\n                if isinstance(node, exp.Count):\n                    return exp.Literal.number(0)\n                elif isinstance(node, exp.AggFunc):\n                    return exp.null()\n                return node\n\n            alias = exp.Coalesce(\n                this=alias,\n                expressions=[value.this.transform(remove_aggs)],\n            )\n\n        select.parent.replace(alias)\n\n    for key, column, predicate in keys:\n        predicate.replace(exp.true())\n        nested = exp.column(key_aliases[key], table_alias)\n\n        if is_subquery_projection:\n            key.replace(nested)\n            continue\n\n        if key in group_by:\n            key.replace(nested)\n        elif isinstance(predicate, exp.EQ):\n            parent_predicate = _replace(\n                parent_predicate,\n                f\"({parent_predicate} AND ARRAY_CONTAINS({nested}, {column}))\",\n            )\n        else:\n            key.replace(exp.to_identifier(\"_x\"))\n            parent_predicate = _replace(\n                parent_predicate,\n                f\"({parent_predicate} AND ARRAY_ANY({nested}, _x -> {predicate}))\",\n            )\n\n    parent_select.join(\n        select.group_by(*group_by, copy=False),\n        on=[predicate for *_, predicate in keys if isinstance(predicate, exp.EQ)],\n        join_type=\"LEFT\",\n        join_alias=table_alias,\n        copy=False,\n    )\n\n\ndef _replace(expression, condition):\n    return expression.replace(exp.condition(condition))\n\n\ndef _other_operand(expression):\n    if isinstance(expression, exp.In):\n        return expression.this\n\n    if isinstance(expression, (exp.Any, exp.All)):\n        return _other_operand(expression.parent)\n\n    if isinstance(expression, exp.Binary):\n        return (\n            expression.right\n            if isinstance(expression.left, (exp.Subquery, exp.Any, exp.Exists, exp.All))\n            else expression.left\n        )\n\n    return None\n", "path": "sqlglot/optimizer/unnest_subqueries.py"}]}