When the same alias is used twice, which one will subsequent references point to?

There is a query that joins the same table twice with the same alias (but under different conditions). To which of the two data sets will subsequent references to this alias point?

SELECT ...
  FROM table_1
  JOIN table_2 A ON ...
  JOIN table_3 A ON ...
  JOIN table_4 B ON A.col_1 = B.col_1

A simple experiment I conducted with the following query:

  WITH
    test_1 AS (SELECT 1 AS col_1 FROM dual UNION ALL SELECT 2 AS col_1 FROM dual)
  , test_2 AS (SELECT 2 AS col_1 FROM dual UNION ALL SELECT 3 AS col_1 FROM dual)
  , test_3 AS (SELECT 1 AS col_1 FROM dual UNION ALL SELECT 2 AS col_1 FROM dual)
  , test_4 AS (SELECT 1 AS col_1 FROM dual UNION ALL SELECT 2 AS col_1 FROM dual)
SELECT *
  FROM test_1 b
  LEFT JOIN test_2 a ON a.col_1 = b.col_1
  LEFT JOIN test_3 a ON a.col_1 = b.col_1
  LEFT JOIN test_4 c ON a.col_1 = c.col_1;

Result:

Suggests that the join of the table test_4 is associated with the first occurrence of the alias. Meanwhile, all chatbots are confident that the last mentioned alias should be used. Therefore, a "carved in stone" rule will be needed to justify the bug fix (since the experiment might not cover all possible behavior scenarios). So, according to the "rules," which alias should be used when it's referenced twice in Oracle?

If you give the columns unambiguous identifiers then the table alias will refer to the alias that corresponds to the table (or inline-view) with the appropriate column identifier. For example, if you have the sample data:

CREATE TABLE test_1 (col_1) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL UNION ALL
SELECT 4 FROM DUAL;

CREATE TABLE test_2 (col_2) AS
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL;

CREATE TABLE test_3 (col_3) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 4 FROM DUAL;

CREATE TABLE test_4 (col_4) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL;

Then:

SELECT *
  FROM test_1 b
  LEFT JOIN test_2 a ON a.col_2 = b.col_1
  LEFT JOIN test_3 a ON a.col_3 = b.col_1
  LEFT JOIN test_4 c1 ON a.col_2 = c1.col_4
  LEFT JOIN test_4 c2 ON a.col_3 = c2.col_4;

is the same as:

SELECT *
  FROM test_1 b
  LEFT JOIN test_2 a1 ON a1.col_2 = b.col_1
  LEFT JOIN test_3 a2 ON a2.col_3 = b.col_1
  LEFT JOIN test_4 c1 ON a1.col_2 = c1.col_4
  LEFT JOIN test_4 c2 ON a2.col_3 = c2.col_4;

and both output:

In that case the a alias used depends on the subsequent column identifier and a.col_2 and a.col_3 refer to different a aliases.

---

If you have ambiguous columns:

CREATE TABLE test_1 (col_1) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL UNION ALL
SELECT 4 FROM DUAL;

CREATE TABLE test_2 (col_1) AS
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL;

CREATE TABLE test_3 (col_1) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 4 FROM DUAL;

CREATE TABLE test_4 (col_1) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL;

Then the query:

SELECT *
  FROM test_1 b
  LEFT JOIN test_2 a ON a.col_1 = b.col_1
  LEFT JOIN test_3 a ON a.col_1 = b.col_1
  LEFT JOIN test_4 c ON a.col_1 = c.col_1;

is the same as:

SELECT *
  FROM test_1 b
  LEFT JOIN test_2 a1 ON a1.col_1 = b.col_1
  LEFT JOIN test_3 a2 ON a2.col_1 = b.col_1
  LEFT JOIN test_4 c ON a1.col_1 = c.col_1;

and both output:

Where a.col_1 is ambiguous but appears to be resolved as the first instance of the a table alias that contains a col_1 column identifier.

Oracle Fiddles: 11gR2, 18, 21, 23