Hi,
We have identified an issue with NATURAL JOIN operation in Oracle Database 23ai Free (version 23.5.0.24.07).
The NATURAL JOIN should select rows with equal values in column of the same names in each table. Here's the 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.
However, we have observed that when NATURAL JOIN is used on tables with duplicate column names, it return a Cartesian product rather than performing the expected join.
Steps to Reproduce:
The issue can be reproduced with the following minimized query:
CREATE TABLE t0 (c0 NUMBER, c1 NUMBER);
CREATE TABLE t1 (c0 NUMBER, c2 NUMBER);
CREATE TABLE t2 (c0 NUMBER, c3 NUMBER);
INSERT INTO t0 (c0, c1) VALUES (1, 1), (2, 2);
INSERT INTO t1 (c0, c2) VALUES (2, 20), (3, 30);
INSERT INTO t2 (c0, c3) VALUES (1, 100), (3, 300);
SELECT * FROM t0 LEFT JOIN t1 ON t0.c0 = t1.c0 NATURAL JOIN t2;
Expected Result:
- The query should either raise an error due to ambiguous column names (e.g., ORA-00918). or
- Perform a
NATURAL JOIN based on one of the c0 columns.
-- expected result 1: Raise an error
ORA-00918: : column ambiguously specified - appears in ... and T2
Help: https://docs.oracle.com/error-help/db/ora-00918/
-- Expected result 2: Perform a NATURAL JOIN based on one of the `c0` columns
-- when t0.c0 is used
C0 C1 C0 C2 C3
---------- ---------- ---------- ---------- ----------
1 1 100
-- when t1.c0 is used
no rows selected
This is because the left operand of NATURAL JOIN has two columns named c0, which is generally impossible but can happen when the table is the result of a LEFT JOIN (or other joins like INNER JOIN, CROSS JOIN, etc).
SQL> SELECT * FROM t0 LEFT JOIN t1 ON t0.c0 = t1.c0;
C0 C1 C0 C2
---------- ---------- ---------- ----------
2 2 2 20
1 1
Actual result (Tested on Oracle Database 23ai Free, version 23.5.0.24.07):
SQL> SELECT * FROM t0 LEFT JOIN t1 ON t0.c0 = t1.c0 NATURAL JOIN t2;
C0 C1 C0 C2 C0 C3
---------- ---------- ---------- ---------- ---------- ----------
2 2 2 20 1 100
2 2 2 20 3 300
1 1 1 100
1 1 3 300
However, the query unexpectedly returns a Cartesian product.
This behavior does not seem intensional. It fails to perform NATURAL JOIN the two tables, resulting in three columns named c0. Additionally, the result contains rows with unmatched values in columns with the same names. Even if this is an Oracle Database design choice, it is not documentized as far as we have reviwed.
We also tested this query on other DBMSs. Below is a summary of the results:
- SQLite: Performs
NATURAL JOIN on the first c0 column.
- MySQL: Raises an error: “Column 'c0' in from clause is ambiguous”
- PostgreSQL: Raises an error: "common column name "c0" appears more than once in left table"
We’ve attached additional test cases (on the same schema) that reproduce the issue.
Thank you for your time and attention. We look forward to your response.
Best regards.
Additional test cases that reproduce the same issue:
SELECT * FROM t0 INNER JOIN t1 ON t0.c0 = t1.c0 NATURAL JOIN t2;
SELECT * FROM t0 CROSS JOIN t1 NATURAL JOIN t2;
Additional comment:
This issue has a slightly different nuance from the one we reported a few days ago. The previous issue is caused by ambiguous table references, whereas this one is due to duplicate column names in the left operand of the NATURAL JOIN.