Well, it was bound to happen “sooner” than “later”; we hit our fist bug with a newly installed 11.2.0.2 instance. She is installed on 64bit Linux. This one is a sqlplus issue.
The bug report from Oracle is as follows:
Bug 10269193 - Wrong results with outer join and CASE expression optimization [ID 10269193.8]
Modified 27-MAY-2011 Type PATCH Status PUBLISHED
Bug 10269193 Wrong results with outer join and CASE expression optimization
This note gives a brief overview of bug 10269193.
The content was last updated on: 27-MAY-2011
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 12.1
Versions confirmed as being affected 11.2.0.2
Platforms affected Generic (all / most platforms affected)
Wrong results are possible with outer join and case expression optimization containing a ROWID column.
Rediscovery Notes:
1. Wrong Results OR an ORA-600 in Parallel Query
2. Outer Join
3. CASE Expression
4. ROWID column is involved in CASE Expression
This bug is possible even when ROWID column or CASE expression is not explicitly present in the query, because
* A ROWID column can be added during query transformation
(eg. non-native full outer join)
* CASE and ROWID column can be added during view merge
A 10053 trace maybe needed to identify it.
Here is an example of what we saw:
If you make a query that (1) doesn’t select a case statement (2) from a left join with no matches, then the result is correct - no matches from the right table.
If you make a query that (1) selects a case statement (2) from a left join with no matches, then the result behaves as if there were actually matches in the right table.
On other, more reasonable databases:
If you write the same query (left join to a table with no matches), then you get the correct result regardless of whether or not there was a case statement in the select.
SELECT
CASE WHEN 1=1 THEN 'comment this out' END case_statement,
VAL_A should_be_A,
VAL_B should_be_null
FROM
(SELECT 'A' VAL_A from dual),
(SELECT 'B' VAL_B from dual)
WHERE
VAL_A = VAL_B(+);
SELECT
--CASE WHEN 1=1 THEN 'comment this out' END case_statement,
VAL_A should_be_A,
VAL_B should_be_null
FROM
(SELECT 'A' VAL_A from dual),
(SELECT 'B' VAL_B from dual)
WHERE
VAL_A = VAL_B(+);
The corrective patch set we applied was:
p10269193_112020_Linux-x86-64.zip
*** Since this was posted we have found that there is a workaround for this as well.
It appears that if you substitute a regular table where you have dual, it works correctly.
Thx
