Silent ORA-918 behavior change in RU 19.17.0 and newer

I think we have the best customers out there. It is incredible what some people find. We would be lost without you, and this blog would contain only a fraction of the information we can share with you. In this case let me explain the silent ORA-918 behavior change in RU 19.17.0 and newer. And all credits go to Peter Lehmann from Deutsche Telekom who found this issue only a few days after we released 19.17.0.

Photo by Drew Farwell on Unsplash

What is happening?

Peter shared an awesome and simple test case with me. A query with ambiguously defined column names gives a result until Oracle 19.16.0 – but from Oracle 19.17.0 on it will flag (correctly) and ORA-918.

This is the test case Peter shared with me:

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

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

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

commit;

Now up to RU 19.16.0 a query would give this result:

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
;
 

        ID
----------
         1
         2

But as soon as you run the same query in 19.17.0 (or newer), you will receive the ORA-918 error:

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
;

ORA-00918: column ambiguously defined 00918. 00000 -  "column ambiguously defined"
*Cause:
*Action:
Error in line: 18 column: 8

And of course, you will receive no result.

Why is this happening?

With Peter’s information I could quickly find the bug fix for this new behavior:

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

When you look at the bug you will see this explanation:

Queries with nested ANSI joins may not raise an error for ambiguous column references. And the fix corrects this misbehavior. The issue for you could be now that your application – as it was the case for Peter – has exactly such ambiguously defined columns and never threw an error. And now it may give an error.

Peter was lucky – he tested and found the root cause quickly. Since he has access to the source code of this app, he could adjust it. And thanks for sharing the testcase with me!!

But this may not be the case for everybody. Hence, please keep your eyes open.

Can you revert to the previous behavior?

As far as I see from the bug, there is no “workaround” mentioned to revert to the previous behavior. So please keep your eyes open when you move to 19.17.0 (or newer).

This fix is included from Oracle Release Update 19.17.0 onwards, and in Oracle 23c.

Addition Nov 7, 2022:

After discussing this topic with the development teams, we agreed that a switch should be made available to revert to the previous behavior. This will be either an underscore or a fix control setting. I will keep you posted. This gets tracked internally under bug 34774426.

Update Nov 24, 2022:

It is confirmed that the fix allowing to disable this bug fix – and then revert to the previous, incorrect behavior – will be included in the following RU, 19.18.0 in January 2023. In addition, you can request a one-off patch on top of 19.17.0 with the bug number 34774426 by opening an SR – but please check the patch download before. While I’m writing this, no one-off patch is available but this should change within the next weeks certainly.

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‘

I suspect that you should be able to use exec dbms_optim_bundle.set_fix_controls(‘29015273:ON’,‘*’, ‘BOTH’,‘YES’); too but I haven’t verified it yet.

Update Mar 16, 2023

Things are developing further, and I can still report some important news about this issue and the fix.

At first, a new bug 35162446 got filed to change the default behavior of the _fix_control. So from Oracle Database RU 19.19.0 onward, you don’t have to set the above _fix_control anymore to disable the fix. The fix for this bug will then create the default:

• DEFAULT: _fix_control=’29015273:ON’

meaning you don’t have to set it explicitly anymore by yourself. This fix for bug 35162446 will be available as a backport on top of previous RUs as well.

And looking a bit further down the road, with Oracle Database 23c, the default will change again, and the initial fix to flag ambiguously defined columns with an ORA-918 will be enabled straight out of the box.

Further Links and Information

• Bug 29015273 – ORA-918 not raised when expected in ansi sql query
• MOS Note: 2893075.1 – SELECT fails with ORA-00918: column ambiguously defined

–Mike