Wrong result on a simple SQL statement

cadwinval

20201002-bug-reproducer.txt

Hello

I think that I've found a bug in Oracle 19 (probably reproducible on Oracle 12 but I haven't tested).

Please find enclosed a self-contained reproducer which creates a single table, insert 4 lines and runs a select query.

It gives the expected result if and if I use the optimizer hint "NO_QUERY_TRANSFORMATION" or if I drive "r_m_s_id" non nullable or if I drive the select query much more complicated by duplicating some code and using a coalesce or if I remove one inner join but those workarounds aren't satisfying (performance considerations).

I assume that Oracle SQL transforms the query to optimize the execution plan but it seems to treat a nullable column as always null.

By the way, PostgreSQL 9.6 always returns the expected result.

Has someone else experienced the same problem? Does it work in Oracle 20?

jaramill

Always post your code HERE on the message, instead of attachments. NO one is going to download unknown files from unknown persons. Viruses/malware, etc...

So please copy/paste it so we can see it

Gaz in Oz

A lot of people will not click on a link that goes who knows where and rightly so.

Provide a small simple reproducible test case and post it here.

Jonathan Lewis

It looks like there's a run-time bug rather than an optmization/transformation bug.

If you enclose your inline scalar subquery with one more layer so you get

(select * from (select rmis.s_id ...) fetch first 1 rows only) as the "column then I think you'll get the correct result.

Inerestingly the optimizer trace file for your query (wrong result for SUPPLIER) and for the query with the extra layer (right SUPPLIER) have the same final transformed form - which include 3 "bind" variables for correlation variables. So I think what may be happening is that at run time Oracle is failing to populate all three correlation variables and that allows the NULL row to get in, at which point the "fetch first" gets the null row. (If you swap the order of the two selects from dual in the RMIS union all you'll also get the right result).

Raise an SR with the sample code.

There are a couple of similar patterns of bug with fetch first, so there may already be a patch.

Regards

Jonathan Lewis