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?