ORA-00918: column ambiguously defined

 

    Ever written a perfectly fine SQL query... only to upgrade Oracle and suddenly hit ORA-00918: column ambiguously defined?  

 

If that sounds familiar, you're not alone. This error is common post-upgrade—especially with Oracle 19c (19.17 and newer)—and usually pops up when you're using joins with duplicate table aliases. 

Let’s walk through an actual test case that shows exactly what’s happening — and how to fix it.

• Create two tables with the same column name 

create table t1 (id int);
create table t2 (id int); 

• Insert values into both tables and commit 

insert into t1 values (1);
insert into t1 values (2);
insert into t2 values (1);
insert into t2 values (2);
commit;

•  Attempting an ambiguous SELECT query 

select a_t2.id 
from t1 a_t1
  inner join t2 a_t2 on a_t1.id = a_t2.id
  inner join t2 a_t2 on a_t1.id = a_t2.id;

• On Oracle 19.16.0 or older, this query returns: 

ID
--
 1
 2

• Same Query on 19.17? Say Hello to ORA-00918

ORA-00918: column ambiguously defined

• Why ? Bug 29015273  ORA-918 not raised when expected in ansi sql query 

• Oracle SQL never complied fully to any ANSI/ISO SQL Standard. The current status of compliance (Oracle Compliance to Core SQL 19C) shows that various features of ANSI SQL supported mostly partially.
• Even if a query contained ambiguous column references where the same column name existed in multiple tables and wasn’t properly qualified. Oracle sometimes did not raise the ORA-00918 error as it ideally should have. This meant queries that were not correctly written (from the perspective of clarity and best practices) could still execute without complaints.
• Oracle 19.17 introduced a more strict enforcement of column aliasing and naming rules in SQL queries—especially around joins.
• As a result after applying 19.17 Oct 22 RU, some queries with ANSI joins may start failing with error ORA-00918: column ambiguously defined.
• The fix for 29015273 is first included in 19.17.0.0.221018 (October 2022) DB Release Update.
• If you are on 19.17 apply oneoff patch 35162446.
• Once the fix is installed (or you are on 19.18.0 or newer), you can disable the correct behavior and revert to the previous, incorrect behavior with: _fix_control = '29015273:ON'

exec dbms_optim_bundle.set_fix_controls('29015273:ON','*', 'BOTH','YES');