Repository: tobymao/sqlglot

Issue:
`optimizer.qualify.qualify` raises "Cannot automatically join" for multiple joins with `USING` and `ON` clauses
When there are two or more `JOIN` operations, and a `JOIN` with an `ON` condition precedes a `JOIN` with a `USING` clause, the `optimizer.qualify.qualify` method throws the error: **"Cannot automatically join"**.


## Steps to Reproduce

```python
from sqlglot import parse_one
from sqlglot.optimizer.qualify import qualify

schema = {
    'A': {'b_id': 'int'}, 
    'B': {'b_id': 'int', 'c_id': 'int'}, 
    'C': {'c_id': 'int'}
}

sql = "SELECT A.b_id FROM A JOIN B ON A.b_id=B.b_id JOIN C USING(c_id)"
print(qualify(parse_one(sql, read='postgres'), schema=schema, quote_identifiers=False).sql(dialect='postgres'))
```

### Current Behavior
The code throws the following error:

```python
sqlglot.errors.OptimizeError: Cannot automatically join: c_id
```

### Expected Behavior
The optimizer should properly qualify the columns and return the following SQL:

```sql
SELECT a.b_id AS b_id FROM a AS a JOIN b AS b ON a.b_id = b.b_id JOIN c AS c ON b.c_id = c.c_id
```


## Additional Context
The error seems to originate from the `_expand_using` method's handling of `JOIN` clauses. It misinterprets the mix of `ON` and `USING` join strategies, leading to failure.

Hints from issue comments:

Solution patch:
diff --git a/sqlglot/optimizer/qualify_columns.py b/sqlglot/optimizer/qualify_columns.py
index c7f3cda0a4..bffd0ab5b8 100644
--- a/sqlglot/optimizer/qualify_columns.py
+++ b/sqlglot/optimizer/qualify_columns.py
@@ -153,23 +153,22 @@ def _expand_using(scope: Scope, resolver: Resolver) -> t.Dict[str, t.Any]:
     column_tables: t.Dict[str, t.Dict[str, t.Any]] = {}
 
     for i, join in enumerate(joins):
-        using = join.args.get("using")
+        source_table = ordered[-1]
+        join_table = join.alias_or_name
+        ordered.append(join_table)
 
+        using = join.args.get("using")
         if not using:
             continue
 
-        join_table = join.alias_or_name
-
         columns = {}
 
         for source_name in scope.selected_sources:
-            if source_name in ordered:
+            if source_name in ordered[:-1]:
                 for column_name in resolver.get_source_columns(source_name):
                     if column_name not in columns:
                         columns[column_name] = source_name
 
-        source_table = ordered[-1]
-        ordered.append(join_table)
         join_columns = resolver.get_source_columns(join_table)
         conditions = []
         using_identifier_count = len(using)


Test patch:
diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py
index fe5a4d7c54..a71644a39c 100644
--- a/tests/test_optimizer.py
+++ b/tests/test_optimizer.py
@@ -397,6 +397,22 @@ def test_qualify_columns(self, logger):
             "SELECT u.user_id AS user_id, l.log_date AS log_date FROM users AS u CROSS JOIN LATERAL (SELECT l1.log_date AS log_date FROM (SELECT l.log_date AS log_date FROM logs AS l WHERE l.user_id = u.user_id AND l.log_date <= 100 ORDER BY l.log_date LIMIT 1) AS l1) AS l",
         )
 
+        self.assertEqual(
+            optimizer.qualify.qualify(
+                parse_one(
+                    "SELECT A.b_id FROM A JOIN B ON A.b_id=B.b_id JOIN C USING(c_id)",
+                    dialect="postgres",
+                ),
+                schema={
+                    "A": {"b_id": "int"},
+                    "B": {"b_id": "int", "c_id": "int"},
+                    "C": {"c_id": "int"},
+                },
+                quote_identifiers=False,
+            ).sql("postgres"),
+            "SELECT a.b_id AS b_id FROM a AS a JOIN b AS b ON a.b_id = b.b_id JOIN c AS c ON b.c_id = c.c_id",
+        )
+
         self.check_file(
             "qualify_columns",
             qualify_columns,