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.
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:
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
I get the same error on my 19.16 and 12.1.02 databases. I am on Windows OS, though.
the fix, as far as I see, got included with 19.17.0, not with 19.16.0 yet since the customer cases I saw all confirm (including the fix itself) that it appears with 19.17.0. Can you please have Oracle Support check this?
The link to Bug 290115273 doesn’t work from outside Oracle employee network I guess.
Have anyone written a SQL to identify SQL’s from v$sql which will fail after RU 19.17.0, but slipped through before the bugfix?
correct, the bug is not public. But I have no SQL checking V$SQL seen yet unfortunately 🙁
You can probably do something like this:
(Won’t filter out same aliases used in different UNION)
sql_txt as (
select lower(sql_fulltext) my_sql, sql_id
where parsing_schema_name like ‘COL_4210’
and lower(sql_text) like ‘select% join % join %’),
my_pos as (select my_sql, sql_id, instr(my_sql, ‘join’,1,1) pos1, instr(my_sql,’join’,1,2) pos2, instr(my_sql,’join’,1,3) pos3, instr(my_sql,’join’,1,4) pos4, instr(my_sql,’join’,1,5) pos5 from sql_txt),
my_joins as (select my_sql, sql_id, substr(my_sql, pos1, 50) join1, substr(my_sql, pos2, 50) join2, substr(my_sql, pos3, 50) join3, substr(my_sql, pos4, 50) join4, substr(my_sql, pos5, 50) join5 from my_pos),
my_joins2 as (select my_sql, sql_id, join1, join2, case when join3 like ‘select%’ then null else join3 end join3,
case when join4 like ‘select%’ then null else join4 end join4,
case when join5 like ‘select%’ then null else join5 end join5
my_aliases as (select my_sql, sql_id,
to_char(REGEXP_SUBSTR (join1, ‘[^ ]+’, 1, 3)) alias1,
to_char(REGEXP_SUBSTR (join2, ‘[^ ]+’, 1, 3)) alias2,
to_char(REGEXP_SUBSTR (join3, ‘[^ ]+’, 1, 3)) alias3,
to_char(REGEXP_SUBSTR (join4, ‘[^ ]+’, 1, 3)) alias4,
to_char(REGEXP_SUBSTR (join5, ‘[^ ]+’, 1, 3)) alias5
select * from my_aliases
where (alias1=alias2 or alias1=alias3 or alias1 = alias4 or alias1=alias5 or
alias2=alias3 or alias2=alias4 or alias2 = alias5 or
alias3=alias4 or alias3=alias5 or
I thought that Critical Patch Updates only included security fixes and did not introduce new features? Im sure I once read that ~11g times in some patch information description document. But this seems clearly to be a feature?
Critical Patch Updates have been buried wit 11g already. There were no “security fixes only” bundles available anymore for years.
A Release Update (or Bundle Patch) as well as the old PSUs introduce a small to large number of fixes. For instance, in the current 19.17.0 RU you’ll find over 7000 fixes – and a given number of them are security fixes.
The above thing is not a feature but truly a bug fix for a misbehavior. You could access ambiguously defined columns in your SQL before – and this was the bug. Now, for years application developers didn’t care on writing their code nicely and correctly since the database gave no error where it should have flagged an error.
With 19.17.0 the long-awaited fix came in correction the database’s misbehavior – but this can lead to break “bad” application code which the developer of the fix had not foreseen. This is why we’ll get a one off (merge has happened already) which allows to turn back to the previous MISbehavior 🙂
It appears that Oracle is not releasing the one off patch to 19.16, to revert the behavior, for Windows.
Is known if they’ve committed to providing the revert behavior in the Windows 19.18 RU?
it is supposed to be in 19.18.0.
There is a new OneOff Patch out, which allows to revert to the old – wrong – behaviour
Patch 34774426: PLEASE PROVIDE EITHER AN UNDERSCORE OR A _FIX_CONTROL TO DISABLE FIX 29015273 SINCE IT CHANGES BEHAVIOR
The readme for the patch says:
34774426: PLEASE PROVIDE EITHER AN UNDERSCORE OR A _FIX_CONTROL TO DISABLE FIX 29015273 SINCE IT CHANGES BEHAVIOR
The patch is applied at binary level. So, after applying this patch, what do I do to revert back the behavior? The readme does not provide any instructions.
if you have the patch, you can use the underscore I added to the blog post. This will revert to the previous (old, false) behavior.
If we were to run with _fix_control” = ‘29015273:ON‘; for 60 days, would it flag the suppressed ORA-918 in a trace file, alert file, or system table?
No, not at all. You have to make sure to remove it in order to have the error flagged.
According to my test today, all methods are working with 19.18:
Awesome – thanks for the update, Julian!
We have hundreds of DBs to patch with 19.17, should we apply the one-off patch on all of them to avoid any potential error ?
That is a good question I can’t answer for you.
At first, the one-off only introduces an underscore to disable the correct behavior and revert to the wrong (old) behavior.
So I can’t decide this for you.
could you provide your primary source for the 23c and onwards behavior? thank you very much.
duh I should have read your bio first LOL never mind – good enough of a source for me then!
No worries, Alexander!
Hello – I just applied the April 2023 patch on RHEL8
(Patch 35058163: COMBO OF OJVM RU COMPONENT 18.104.22.168.230418 + DB RU 22.214.171.124.230418)
and it resolved the issue for me without having to add any parameters.
It looks like we are hitting same bug again with 19.19 if we had explicit setting of _FIX_CONTROL-29015273-ON( after applying one of patch with 19.17). Permanent fix is to disable the fix control again.
since it’s been included in 19.19 by default.
I added a description about the behavior change in 19.18 and 19.19 a while back. Isn’t that what you are seeing now?