Repository: tobymao/sqlglot
Pull request: fix: Issue 5188 eliminate_join_marks has multiple issues
```
select t1.a, t2.b
from t1, t2
where t1.id = case when t2.id (+) = "n/a" then null else t2.id (+) end
```

becomes a broken CASE statement

```
SELECT
  t1.a,
  t2.b
FROM t1
LEFT JOIN t2
  ON t2.id = "n/a" AND t1.id = CASE WHEN  THEN NULL ELSE t2.id END
```


```
select t1.a, t2.b
from t1, t2
where t1.id = t2.id1 (+) or t1.id = t2.id2 (+)
```

changes `OR` to `AND`

```
SELECT
  t1.a,
  t2.b
FROM t1
LEFT JOIN t2
  ON t1.id = t2.id1 AND t1.id = t2.id2
```

tbh I am not really happy with the code. looks a bit ugly for my taste. looking forward to suggestions

I will have a fresh look tomorrow

Fixes #5188

Problem statement:
eliminate_join_marks has multiple issues
```
select t1.a, t2.b
from t1, t2
where t1.id = case when t2.id (+) = "n/a" then null else t2.id (+) end
```

becomes a broken CASE statement

```
SELECT
  t1.a,
  t2.b
FROM t1
LEFT JOIN t2
  ON t2.id = "n/a" AND t1.id = CASE WHEN  THEN NULL ELSE t2.id END
```


```
select t1.a, t2.b
from t1, t2
where t1.id = t2.id1 (+) or t1.id = t2.id2 (+)
```

changes `OR` to `AND`

```
SELECT
  t1.a,
  t2.b
FROM t1
LEFT JOIN t2
  ON t1.id = t2.id1 AND t1.id = t2.id2
```

Hints from issue discussion:
Moving the discussion here: https://github.com/tobymao/sqlglot/pull/5189.

Commit message: fix for parentheses

Patch to review:
diff --git a/sqlglot/transforms.py b/sqlglot/transforms.py
index 57059d9d52..3bee5f5158 100644
--- a/sqlglot/transforms.py
+++ b/sqlglot/transforms.py
@@ -842,113 +842,123 @@ def struct_kv_to_alias(expression: exp.Expression) -> exp.Expression:
 
 
 def eliminate_join_marks(expression: exp.Expression) -> exp.Expression:
-    """
-    Remove join marks from an AST. This rule assumes that all marked columns are qualified.
-    If this does not hold for a query, consider running `sqlglot.optimizer.qualify` first.
+    """https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm#sthref3178
 
-    For example,
-        SELECT * FROM a, b WHERE a.id = b.id(+)    -- ... is converted to
-        SELECT * FROM a LEFT JOIN b ON a.id = b.id -- this
+    1. You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.
 
-    Args:
-        expression: The AST to remove join marks from.
+    2. The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (that is, when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.
 
-    Returns:
-       The AST with join marks removed.
+    The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
+
+    You cannot use the (+) operator to outer-join a table to itself, although self joins are valid.
+
+    The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
+
+    A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
+
+    A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
+
+    A WHERE condition cannot compare any column marked with the (+) operator with a subquery.
+
+    -- example with WHERE
+    SELECT d.department_name, sum(e.salary) as total_salary
+    FROM departments d, employees e
+    WHERE e.department_id(+) = d.department_id
+    group by department_name
+
+    -- example of left correlation in select
+    SELECT d.department_name, (
+        SELECT SUM(e.salary)
+            FROM employees e
+            WHERE e.department_id(+) = d.department_id) AS total_salary
+    FROM departments d;
+
+    -- example of left correlation in from
+    SELECT d.department_name, t.total_salary
+    FROM departments d, (
+            SELECT SUM(e.salary) AS total_salary
+            FROM employees e
+            WHERE e.department_id(+) = d.department_id
+        ) t
     """
+
     from sqlglot.optimizer.scope import traverse_scope
+    from sqlglot import optimizer
+    from collections import defaultdict
 
-    for scope in traverse_scope(expression):
+    for scope in reversed(traverse_scope(expression)):  # reversed to check knockout
         query = scope.expression
 
         where = query.args.get("where")
-        joins = query.args.get("joins")
+        joins = query.args.get("joins", [])
 
-        if not where or not joins:
-            continue
+        # knockout: we do not support left correlation (see point 2)
+        assert not any(
+            c.args.get("join_mark") for e in query.expressions for c in e.find_all(exp.Column)
+        ), "Correlated queries are not supported"
 
-        query_from = query.args["from"]
+        # nothing to do - we check it here afrer knockout above
+        if not where or not any(c.args.get("join_mark") for c in where.find_all(exp.Column)):
+            continue
 
-        # These keep track of the joins to be replaced
-        new_joins: t.Dict[str, exp.Join] = {}
-        old_joins = {join.alias_or_name: join for join in joins}
+        # make sure we have AND of ORs to have clear join terms
+        where = optimizer.normalize.normalize(where.this)
+        assert optimizer.normalize.normalized(where), "Cannot normalize JOIN predicates"
 
-        for column in scope.columns:
-            if not column.args.get("join_mark"):
+        joins_ons = defaultdict(list)  # dict of {name: list of join AND conditions}
+        for cond in [where] if not isinstance(where, exp.And) else where.flatten():
+            left_join_table = list(
+                set(col.table for col in cond.find_all(exp.Column) if col.args.get("join_mark"))
+            )
+            if not left_join_table:
                 continue
 
-            predicate = column.find_ancestor(exp.Predicate, exp.Select)
-            assert isinstance(
-                predicate, exp.Binary
-            ), "Columns can only be marked with (+) when involved in a binary operation"
-
-            predicate_parent = predicate.parent
-            join_predicate = predicate.pop()
-
-            left_columns = [
-                c for c in join_predicate.left.find_all(exp.Column) if c.args.get("join_mark")
-            ]
-            right_columns = [
-                c for c in join_predicate.right.find_all(exp.Column) if c.args.get("join_mark")
-            ]
-
             assert not (
-                left_columns and right_columns
-            ), "The (+) marker cannot appear in both sides of a binary predicate"
-
-            marked_column_tables = set()
-            for col in left_columns or right_columns:
-                table = col.table
-                assert table, f"Column {col} needs to be qualified with a table"
+                len(left_join_table) > 1
+            ), "Cannot combine JOIN predicates from different tables"
 
+            for col in cond.find_all(exp.Column):
                 col.set("join_mark", False)
-                marked_column_tables.add(table)
 
-            assert (
-                len(marked_column_tables) == 1
-            ), "Columns of only a single table can be marked with (+) in a given binary predicate"
-
-            # Add predicate if join already copied, or add join if it is new
-            join_this = old_joins.get(col.table, query_from).this
-            existing_join = new_joins.get(join_this.alias_or_name)
-            if existing_join:
-                existing_join.set("on", exp.and_(existing_join.args["on"], join_predicate))
-            else:
-                new_joins[join_this.alias_or_name] = exp.Join(
-                    this=join_this.copy(), on=join_predicate.copy(), kind="LEFT"
-                )
+            joins_ons[left_join_table[0]].append(cond)
 
-            # If the parent of the target predicate is a binary node, then it now has only one child
-            if isinstance(predicate_parent, exp.Binary):
-                if predicate_parent.left is None:
-                    predicate_parent.replace(predicate_parent.right)
-                else:
-                    predicate_parent.replace(predicate_parent.left)
+        old_joins = {join.alias_or_name: join for join in joins}
+        new_joins = {}
+        query_from = query.args["from"]
+
+        for table, predicates in joins_ons.items():
+            join_what = old_joins.get(table, query_from).this.copy()
+            new_joins[join_what.alias_or_name] = exp.Join(
+                this=join_what, on=exp.and_(*predicates), kind="LEFT"
+            )
 
-        only_old_join_sources = old_joins.keys() - new_joins.keys()
+            for p in predicates:
+                while isinstance(p.parent, exp.Paren):
+                    p.parent.replace(p)
+
+                parent = p.parent
+                p.pop()
+                if isinstance(parent, exp.Binary):
+                    parent.replace(parent.right if parent.left is None else parent.left)
+                elif isinstance(parent, exp.Where):
+                    parent.pop()
 
         if query_from.alias_or_name in new_joins:
+            only_old_joins = old_joins.keys() - new_joins.keys()
             assert (
-                len(only_old_join_sources) >= 1
+                len(only_old_joins) >= 1
             ), "Cannot determine which table to use in the new FROM clause"
 
-            new_from_name = list(only_old_join_sources)[0]
-            query.set("from", exp.From(this=old_joins.pop(new_from_name).this))
-            only_old_join_sources.remove(new_from_name)
+            new_from_name = list(only_old_joins)[0]
+            query.set("from", exp.From(this=old_joins[new_from_name].this))
 
         if new_joins:
-            only_old_join_expressions = []
-            for old_join_source in only_old_join_sources:
-                old_join_expression = old_joins[old_join_source]
-                if not old_join_expression.kind:
-                    old_join_expression.set("kind", "CROSS")
-
-                only_old_join_expressions.append(old_join_expression)
-
-            query.set("joins", list(new_joins.values()) + only_old_join_expressions)
-
-        if not where.this:
-            where.pop()
+            for n, j in old_joins.items():  # preserve any other joins
+                if n not in new_joins and n != query.args["from"].name:
+                    if not j.kind:
+                        j.set("kind", "CROSS")
+                    new_joins[n] = j
+            query.set("joins", list(new_joins.values()))
 
     return expression
 
diff --git a/tests/test_transforms.py b/tests/test_transforms.py
index 59b879d7fd..b2fe495505 100644
--- a/tests/test_transforms.py
+++ b/tests/test_transforms.py
@@ -17,7 +17,8 @@ class TestTransforms(unittest.TestCase):
     def validate(self, transform, sql, target, dialect=None):
         with self.subTest(f"{dialect} - {sql}"):
             self.assertEqual(
-                parse_one(sql, dialect=dialect).transform(transform).sql(dialect=dialect), target
+                parse_one(sql, dialect=dialect).transform(transform).sql(dialect=dialect),
+                target,
             )
 
     def test_unalias_group(self):
@@ -274,6 +275,42 @@ def test_eliminate_join_marks(self):
                 == "SELECT a.id FROM a LEFT JOIN b ON a.id = b.id AND b.d = const"
             )
 
+            # validate parens
+            self.validate(
+                eliminate_join_marks,
+                "select t1.a, t2.b from t1, t2 where (1 = 1) and (t1.id = t2.id1 (+))",
+                "SELECT t1.a, t2.b FROM t1 LEFT JOIN t2 ON t1.id = t2.id1 WHERE (1 = 1)",
+                dialect,
+            )
+
+            # validate a CASE
+            self.validate(
+                eliminate_join_marks,
+                "select t1.a, t2.b from t1, t2 where t1.id = case when t2.id (+) = 'n/a' then null else t2.id (+) end",
+                "SELECT t1.a, t2.b FROM t1 LEFT JOIN t2 ON t1.id = CASE WHEN t2.id = 'n/a' THEN NULL ELSE t2.id END",
+                dialect,
+            )
+
+            # validate OR
+            self.validate(
+                eliminate_join_marks,
+                "select t1.a, t2.b from t1, t2 where t1.id = t2.id1 (+) or t1.id = t2.id2 (+)",
+                "SELECT t1.a, t2.b FROM t1 LEFT JOIN t2 ON t1.id = t2.id1 OR t1.id = t2.id2",
+                dialect,
+            )
+
+            # validate knockout
+            script = """
+                    SELECT c.customer_name,
+                            (SELECT MAX(o.order_date)
+                            FROM orders o
+                            WHERE o.customer_id(+) = c.customer_id) AS latest_order_date
+                    FROM customers c
+                    """
+            self.assertRaises(
+                AssertionError, eliminate_join_marks, parse_one(script, dialect=dialect)
+            )
+
     def test_eliminate_window_clause(self):
         self.validate(
             eliminate_window_clause,


Code review comments:
diff_hunk: @@ -842,113 +842,123 @@ def struct_kv_to_alias(expression: exp.Expression) -> exp.Expression:
 
 
 def eliminate_join_marks(expression: exp.Expression) -> exp.Expression:
-    """
-    Remove join marks from an AST. This rule assumes that all marked columns are qualified.
-    If this does not hold for a query, consider running `sqlglot.optimizer.qualify` first.
+    """https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm#sthref3178
 
-    For example,
-        SELECT * FROM a, b WHERE a.id = b.id(+)    -- ... is converted to
-        SELECT * FROM a LEFT JOIN b ON a.id = b.id -- this
+    1. You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.
 
-    Args:
-        expression: The AST to remove join marks from.
+    2. The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (that is, when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.
 
-    Returns:
-       The AST with join marks removed.
+    The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
+
+    You cannot use the (+) operator to outer-join a table to itself, although self joins are valid.
+
+    The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
+
+    A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
+
+    A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
+
+    A WHERE condition cannot compare any column marked with the (+) operator with a subquery.
+
+    -- example with WHERE
+    SELECT d.department_name, sum(e.salary) as total_salary
+    FROM departments d, employees e
+    WHERE e.department_id(+) = d.department_id
+    group by department_name
+
+    -- example of left correlation in select
+    SELECT d.department_name, (
+        SELECT SUM(e.salary)
+            FROM employees e
+            WHERE e.department_id(+) = d.department_id) AS total_salary
+    FROM departments d;
+
+    -- example of left correlation in from
+    SELECT d.department_name, t.total_salary
+    FROM departments d, (
+            SELECT SUM(e.salary) AS total_salary
+            FROM employees e
+            WHERE e.department_id(+) = d.department_id
+        ) t
     """
+
     from sqlglot.optimizer.scope import traverse_scope
+    from sqlglot import optimizer
path: sqlglot/transforms.py
text: @user1:
Nit: let's replace with `from sqlglot.optimizer.normalize import normalize, normalized`.

diff_hunk: @@ -842,113 +842,123 @@ def struct_kv_to_alias(expression: exp.Expression) -> exp.Expression:
 
 
 def eliminate_join_marks(expression: exp.Expression) -> exp.Expression:
-    """
-    Remove join marks from an AST. This rule assumes that all marked columns are qualified.
-    If this does not hold for a query, consider running `sqlglot.optimizer.qualify` first.
+    """https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm#sthref3178
 
-    For example,
-        SELECT * FROM a, b WHERE a.id = b.id(+)    -- ... is converted to
-        SELECT * FROM a LEFT JOIN b ON a.id = b.id -- this
+    1. You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.
 
-    Args:
-        expression: The AST to remove join marks from.
+    2. The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (that is, when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.
 
-    Returns:
-       The AST with join marks removed.
+    The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
+
+    You cannot use the (+) operator to outer-join a table to itself, although self joins are valid.
+
+    The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
+
+    A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
+
+    A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
+
+    A WHERE condition cannot compare any column marked with the (+) operator with a subquery.
+
+    -- example with WHERE
+    SELECT d.department_name, sum(e.salary) as total_salary
+    FROM departments d, employees e
+    WHERE e.department_id(+) = d.department_id
+    group by department_name
+
+    -- example of left correlation in select
+    SELECT d.department_name, (
+        SELECT SUM(e.salary)
+            FROM employees e
+            WHERE e.department_id(+) = d.department_id) AS total_salary
+    FROM departments d;
+
+    -- example of left correlation in from
+    SELECT d.department_name, t.total_salary
+    FROM departments d, (
+            SELECT SUM(e.salary) AS total_salary
+            FROM employees e
+            WHERE e.department_id(+) = d.department_id
+        ) t
     """
+
     from sqlglot.optimizer.scope import traverse_scope
+    from sqlglot import optimizer
+    from collections import defaultdict
 
-    for scope in traverse_scope(expression):
+    for scope in reversed(traverse_scope(expression)):  # reversed to check knockout
         query = scope.expression
 
         where = query.args.get("where")
-        joins = query.args.get("joins")
+        joins = query.args.get("joins", [])
 
-        if not where or not joins:
-            continue
+        # knockout: we do not support left correlation (see point 2)
+        assert not any(
+            c.args.get("join_mark") for e in query.expressions for c in e.find_all(exp.Column)
+        ), "Correlated queries are not supported"
 
-        query_from = query.args["from"]
+        # nothing to do - we check it here afrer knockout above
+        if not where or not any(c.args.get("join_mark") for c in where.find_all(exp.Column)):
+            continue
 
-        # These keep track of the joins to be replaced
-        new_joins: t.Dict[str, exp.Join] = {}
-        old_joins = {join.alias_or_name: join for join in joins}
+        # make sure we have AND of ORs to have clear join terms
+        where = optimizer.normalize.normalize(where.this)
+        assert optimizer.normalize.normalized(where), "Cannot normalize JOIN predicates"
 
-        for column in scope.columns:
-            if not column.args.get("join_mark"):
+        joins_ons = defaultdict(list)  # dict of {name: list of join AND conditions}
+        for cond in [where] if not isinstance(where, exp.And) else where.flatten():
+            left_join_table = list(
+                set(col.table for col in cond.find_all(exp.Column) if col.args.get("join_mark"))
+            )
+            if not left_join_table:
                 continue
 
-            predicate = column.find_ancestor(exp.Predicate, exp.Select)
-            assert isinstance(
-                predicate, exp.Binary
-            ), "Columns can only be marked with (+) when involved in a binary operation"
-
-            predicate_parent = predicate.parent
-            join_predicate = predicate.pop()
-
-            left_columns = [
-                c for c in join_predicate.left.find_all(exp.Column) if c.args.get("join_mark")
-            ]
-            right_columns = [
-                c for c in join_predicate.right.find_all(exp.Column) if c.args.get("join_mark")
-            ]
-
             assert not (
-                left_columns and right_columns
-            ), "The (+) marker cannot appear in both sides of a binary predicate"
-
-            marked_column_tables = set()
-            for col in left_columns or right_columns:
-                table = col.table
-                assert table, f"Column {col} needs to be qualified with a table"
+                len(left_join_table) > 1
+            ), "Cannot combine JOIN predicates from different tables"
 
+            for col in cond.find_all(exp.Column):
path: sqlglot/transforms.py
text: @user1:
Why are we doing a `find_all` again? Don't we already have the column with the join marker from L910-912?

@author:
I can refactor it a little bit to avoid repetition

diff_hunk: @@ -842,113 +842,123 @@ def struct_kv_to_alias(expression: exp.Expression) -> exp.Expression:
 
 
 def eliminate_join_marks(expression: exp.Expression) -> exp.Expression:
-    """
-    Remove join marks from an AST. This rule assumes that all marked columns are qualified.
-    If this does not hold for a query, consider running `sqlglot.optimizer.qualify` first.
+    """https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm#sthref3178
 
-    For example,
-        SELECT * FROM a, b WHERE a.id = b.id(+)    -- ... is converted to
-        SELECT * FROM a LEFT JOIN b ON a.id = b.id -- this
+    1. You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.
 
-    Args:
-        expression: The AST to remove join marks from.
+    2. The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (that is, when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.
 
-    Returns:
-       The AST with join marks removed.
+    The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
+
+    You cannot use the (+) operator to outer-join a table to itself, although self joins are valid.
+
+    The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
+
+    A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
+
+    A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
+
+    A WHERE condition cannot compare any column marked with the (+) operator with a subquery.
+
+    -- example with WHERE
+    SELECT d.department_name, sum(e.salary) as total_salary
+    FROM departments d, employees e
+    WHERE e.department_id(+) = d.department_id
+    group by department_name
+
+    -- example of left correlation in select
+    SELECT d.department_name, (
+        SELECT SUM(e.salary)
+            FROM employees e
+            WHERE e.department_id(+) = d.department_id) AS total_salary
+    FROM departments d;
+
+    -- example of left correlation in from
+    SELECT d.department_name, t.total_salary
+    FROM departments d, (
+            SELECT SUM(e.salary) AS total_salary
+            FROM employees e
+            WHERE e.department_id(+) = d.department_id
+        ) t
     """
+
     from sqlglot.optimizer.scope import traverse_scope
+    from sqlglot import optimizer
+    from collections import defaultdict
 
-    for scope in traverse_scope(expression):
+    for scope in reversed(traverse_scope(expression)):  # reversed to check knockout
         query = scope.expression
 
         where = query.args.get("where")
-        joins = query.args.get("joins")
+        joins = query.args.get("joins", [])
 
-        if not where or not joins:
-            continue
+        # knockout: we do not support left correlation (see point 2)
+        assert not any(
+            c.args.get("join_mark") for e in query.expressions for c in e.find_all(exp.Column)
+        ), "Correlated queries are not supported"
 
-        query_from = query.args["from"]
+        # nothing to do - we check it here afrer knockout above
path: sqlglot/transforms.py
text: @user1:
```suggestion
        # nothing to do - we check it here after knockout above
```

diff_hunk: @@ -842,113 +842,123 @@ def struct_kv_to_alias(expression: exp.Expression) -> exp.Expression:
 
 
 def eliminate_join_marks(expression: exp.Expression) -> exp.Expression:
-    """
-    Remove join marks from an AST. This rule assumes that all marked columns are qualified.
-    If this does not hold for a query, consider running `sqlglot.optimizer.qualify` first.
+    """https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm#sthref3178
 
-    For example,
-        SELECT * FROM a, b WHERE a.id = b.id(+)    -- ... is converted to
-        SELECT * FROM a LEFT JOIN b ON a.id = b.id -- this
+    1. You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.
 
-    Args:
-        expression: The AST to remove join marks from.
+    2. The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (that is, when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.
 
-    Returns:
-       The AST with join marks removed.
+    The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
+
+    You cannot use the (+) operator to outer-join a table to itself, although self joins are valid.
+
+    The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
+
+    A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
+
+    A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
+
+    A WHERE condition cannot compare any column marked with the (+) operator with a subquery.
+
+    -- example with WHERE
+    SELECT d.department_name, sum(e.salary) as total_salary
+    FROM departments d, employees e
+    WHERE e.department_id(+) = d.department_id
+    group by department_name
+
+    -- example of left correlation in select
+    SELECT d.department_name, (
+        SELECT SUM(e.salary)
+            FROM employees e
+            WHERE e.department_id(+) = d.department_id) AS total_salary
+    FROM departments d;
+
+    -- example of left correlation in from
+    SELECT d.department_name, t.total_salary
+    FROM departments d, (
+            SELECT SUM(e.salary) AS total_salary
+            FROM employees e
+            WHERE e.department_id(+) = d.department_id
+        ) t
     """
+
     from sqlglot.optimizer.scope import traverse_scope
+    from sqlglot import optimizer
+    from collections import defaultdict
 
-    for scope in traverse_scope(expression):
+    for scope in reversed(traverse_scope(expression)):  # reversed to check knockout
         query = scope.expression
 
         where = query.args.get("where")
-        joins = query.args.get("joins")
+        joins = query.args.get("joins", [])
 
-        if not where or not joins:
-            continue
+        # knockout: we do not support left correlation (see point 2)
+        assert not any(
+            c.args.get("join_mark") for e in query.expressions for c in e.find_all(exp.Column)
+        ), "Correlated queries are not supported"
 
-        query_from = query.args["from"]
+        # nothing to do - we check it here afrer knockout above
+        if not where or not any(c.args.get("join_mark") for c in where.find_all(exp.Column)):
+            continue
 
-        # These keep track of the joins to be replaced
-        new_joins: t.Dict[str, exp.Join] = {}
-        old_joins = {join.alias_or_name: join for join in joins}
+        # make sure we have AND of ORs to have clear join terms
+        where = optimizer.normalize.normalize(where.this)
+        assert optimizer.normalize.normalized(where), "Cannot normalize JOIN predicates"
 
-        for column in scope.columns:
-            if not column.args.get("join_mark"):
+        joins_ons = defaultdict(list)  # dict of {name: list of join AND conditions}
+        for cond in [where] if not isinstance(where, exp.And) else where.flatten():
+            left_join_table = list(
+                set(col.table for col in cond.find_all(exp.Column) if col.args.get("join_mark"))
+            )
path: sqlglot/transforms.py
text: @user1:
Why do we need to convert the set into a `list`? You can do `left_join_table.pop()` instead of accessing the element as `left_join_table[0]`.