Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Wrong result on a simple SQL statement

cadwinvalOct 2 2020

20201002-bug-reproducer.txt (1.44 KB)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?

Comments

Gbenga Ajakaye

What specifically did you change in your Network Gateway?

2744216

Only the IP Address changed from A.B.C.D to A.B.C.E

Gbenga Ajakaye

That's what I thought. In your question mentioned that no IP was changed. 

Recently we had to change the network gateway for our 2 node MySQL cluster running on top of Oracle Grid (RHEL). None of the IPs changed. We noted the following behavior:

Can you look in the location below and make sure that the file reflects your changed IP. 

/etc/hosts
2744216

The gateway IP address is not in the  hosts file, it is (and has only ever been) in a routing table.

1 - 4

Post Details

Added on Oct 2 2020
#database, #error, #oracle, #sql
3 comments
765 views