Hi,
We have identified an issue with NATURAL JOIN in Oracle Database 23ai Free (version 23.5.0.24.07).
Without table aliases, using NATURAL JOIN on the same table results in an ORA-00918 error:
SQL> CREATE TABLE t0 (c0 NUMBER);
Table created.
SQL> INSERT INTO t0 VALUES (0), (1), (2), (3), (4);
5 rows created.
SQL> SELECT * FROM t0 NATURAL JOIN t0;
SELECT * FROM t0 NATURAL JOIN t0
*
ERROR at line 1:
ORA-00918: : column ambiguously specified - appears in T0 and T0
Help: https://docs.oracle.com/error-help/db/ora-00918/
With aliases, the join behaves as expected, returning the original table:
SQL> SELECT * FROM t0 a NATURAL JOIN t0 b;
C0
----------
0
1
2
3
4
Relevant part of the documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__BABHGCAE
A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. If two columns with the same name do not have compatible data types, then an error is raised. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.
However, we have observed that when multiple NATURAL JOIN operations are performed on the same table, Oracle does not raise an error but instead returns incorrect results.
Steps to Reproduce:
CREATE TABLE t0 (c0 NUMBER);
INSERT INTO t0 VALUES (0), (1), (2), (3), (4);
SELECT * FROM t0 NATURAL JOIN t0 NATURAL JOIN t0;
SELECT * FROM t0 a NATURAL JOIN t0 b NATURAL JOIN t0 c;
Expected Result:
SQL> SELECT * FROM t0 NATURAL JOIN t0 NATURAL JOIN t0;
SELECT * FROM t0 NATURAL JOIN t0 NATURAL JOIN t0;
*
ERROR at line 1:
ORA-00918: : column ambiguously specified - appears in T0 and T0
Help: https://docs.oracle.com/error-help/db/ora-00918/
SQL> SELECT * FROM t0 a NATURAL JOIN t0 b NATURAL JOIN t0 c;
C0
----------
0
1
2
3
4
The first query should raise an error due to ambiguous column references when aliases are not used.
Actual result (Oracle Database 23ai Free, version 23.5.0.24.07):
SQL> SELECT * FROM t0 NATURAL JOIN t0 NATURAL JOIN t0;
C0
----------
0
0
0
0
0
1
1
1
1
1
2
C0
----------
2
2
2
2
3
3
3
3
3
4
4
C0
----------
4
4
4
25 rows selected.
SQL> SELECT * FROM t0 a NATURAL JOIN t0 b NATURAL JOIN t0 c;
C0
----------
0
1
2
3
4
However, the first query returns 25 rows instead of an error, seemingly corresponding to the first column of the Cartesian product of two t0s. The second query returns the expected results with aliases.
We reviewed several potential intended features; however, this is unlikely to be the case in any scenario. The number of rows in N Cartesian products is (number of rows) ^ N, but in this case, the result is always (number of rows) ^ 2, regardless of the number of NATURAL JOINs. Additionally, NATURAL cannot be interpreted as an identifier for a table alias in the query above, as JOIN requires a join condition.
SELECT * FROM t0 NATURAL JOIN t0 NATURAL JOIN t0;
SELECT * FROM t0 NATURAL JOIN t0 NATURAL JOIN t0 NATURAL JOIN t0;
SELECT * FROM t0 NATURAL JOIN t0 NATURAL JOIN t0 NATURAL JOIN t0 NATURAL JOIN t0;
…
-- all returns 25 rows
SELECT * FROM t0 naturalmaybealias1 JOIN t0;
SELECT * FROM t0 naturalmaybealias1 JOIN t0 naturalmaybealias2 JOIN t0;
-- all returns the following error
ERROR at line 1:
ORA-02000: missing ON or USING keyword
Help: https://docs.oracle.com/error-help/db/ora-02000/
We also tested this query on other DBMSs to observe their behavior. Here's a summary of the results:
- SQLite: Returns the original table, for any number of `NATURAL JOIN`s, with or without table aliases.
- MySQL: Returns the original table, for any number of `NATURAL JOIN`s, with table aliases. Error without table aliases.
- PostgreSQL: Returns the original table, for any number of `NATURAL JOIN`s, with table aliases. Error without table aliases.
Thank you for your time and attention to this matter.
We look forward to your response.
Best regards.