Oracle 19 gotcha: Ora-018 error

Today, a coworker brought me a strange SQL that produced ORA-018 error on one test Oracle instance and not in others. I looked at the SQL that read something like below. It was flawed with same table joined in twice, unnecessarily:

Assuming 2 tables employee and department_team and both having department_id and team_id, here is a sample SQL:

SELECT employee.employee_id, department_team.department_id,
department_team.department_name, department_team.team_name
FROM employee
LEFT JOIN department ON employee.department_id = department_team.department_id
LEFT JOIN department ON employee.team_id = department_team.team_id
;

This gave error “ORA-00918: column ambiguously defined” in one instance, while other instances produced result.

I told them to fix the SQL, so it will run on all instances. The fix was to remove redundant join:

SELECT employee.employee_id, department_team.department_id,
department_team.department_name, department_team.team_name
FROM employee
LEFT JOIN department ON (employee.department_id = department_team.department_id
AND employee.team_id = department_team.team_id)
;

There may be other scenarios, where you genuinely need same table joined multiple times. For e.g., if you want to if an employee is a manager that manages 2 departments and we want to list out employees in both departments. Here is an example for that:
https://www.queryexamples.com/sql/select/join-same-table-twice-in-sql-query/

In either case, having the same name (be it column or table) repeated twice should throw the above error. But Oracle didn’t, before 19.17! This was a bug in Oracle that allowed that weird SQL with double join work.

It was hard to find on Google, but I eventually landed on the below page that explains this bug beautifully!

https://mikedietrichde.com/2022/10/27/silent-ora-918-behavior-change-in-ru-19-17-0-and-newer/

Oh yeah, as for our databases the one where the SQL worked was indeed at 19.17 and others older! Our tables are more complicated than the ones I used in this example, but I just wanted to show a simple example.

Thanks for reading! Happy Coding!