Forum Stats

  • 3,782,926 Users
  • 2,254,712 Discussions
  • 7,880,223 Comments

Discussions

Categories

Wrong result on a simple SQL statement

cadwinval
cadwinval Member Posts: 1 Blue Ribbon
in SQL & PL/SQL
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?

Tagged:
· Share on TwitterShare on Facebook

Answers

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy

    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

    mathguy
    · Share on TwitterShare on Facebook
  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown

    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.

    mathguy
    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,852 Gold Crown


    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

    · Share on TwitterShare on Facebook