Geek Answers Handbook

Oracle 11g Release 1 vs 2 - Different LEFT OUTER JOIN Behaviors

I found this after upgrading from Oracle 11g Release 1 to Release 2.

The best I can summarize now is that LEFT OUTER JOIN against a query with a constant "fake" column and a WHERE generates different results in two Oracle RELEASES. In Release 2, the “fake” column appears in unmatched rows:

 TEST1 TEST2 ===== ===== ABAB - --- - --- 1 bar 1 hello 2 baz > SELECT * FROM test1 LEFT OUTER JOIN test2 ON test1.a = test2.a; AB A_1 B_1 - --- --- ----- 1 bar 1 hello 2 baz

So far so good. all of the above works on Rel. 1 and 2. Now add the constant column “fake” X, and everything will work as expected:

 > SELECT * FROM test1 LEFT OUTER JOIN (SELECT test2.*, 'X' AS X FROM test2) test3 ON test1.a = test3.a; AB A_1 B_1 X - --- --- ----- - 1 bar 1 hello X 2 baz

Now add the WHERE to the first table and get different results:

 > SELECT * FROM test1 LEFT OUTER JOIN (SELECT test2.*, 'X' AS X FROM test2) test3 ON test1.a = test3.a WHERE test1.b LIKE 'ba%'; Release 11.1.0.7.0 Release 11.2.0.2.0 ================== ================== AB A_1 B_1 XAB A_1 B_1 X - --- --- ----- - - --- --- ----- - 1 bar 1 hello X 1 bar 1 hello X 2 baz 2 baz X <--- WHAT THIS?!

Further puzzling: if the WHERE condition is numeric (for example, WHERE test1.a < 5 , the results are the same!

UPDATE (to clarify my actual question): What am I doing wrong? Is my last request somehow invoking undefined behavior, which makes it right for Oracle to change what was returned from one version to another? If not, is this an Oracle error?

+7
source share
2 answers

So, I'm going to answer my question, namely: "Am I doing something wrong or is this an Oracle error?" s This is an Oracle error .

I leave this for you to move on to the madness known as support.oracle.com, but as @AdamHawkes points out, this error is probably addressed in a recent Oracle patch set. A few errors in the release notes for 11.2.0.3 seem similar to my problem, although not exactly the same.

I will try to come back here and update the answer when I apply the latest fixes (I am in an environment where I do not control this).

0
source

Just links to some related posts for other readers.

https://forums.oracle.com/forums/thread.jspa?threadID=1113096

Strange behavior of a full outer join in Oracle - how can this be explained?

Basically it is suggested to change the session to disable certain optimizations. change session set "_optimizer_join_elimination_enabled" = false; change session set "_optimizer_native_full_outer_join" = off;

I would prefer that they have automatic tests that prove simple operation and do not just introduce these errors in the first place.

0
source

More articles:


All Articles