Recently one my clients encountered an issue with a SQL query which returned no rows in the 12c database which had been upgraded, but was returning rows in any of the 11g databases which had not been upgraded as yet.
The query was
SELECT * FROM STORAGE t0 WHERE ( ( ( ( ( ( (ROWNUM <= 30) AND (t0.BUSINESS_UNIT_ID = 2)) AND (t0.PLCODE = 1001)) AND (t0.SM_SERIALNUM = '5500100000149000994')) AND ( (t0.SM_MODDATE IS NULL) OR (t0.SM_MODDATE <= SYSDATE))) AND ( (t0.DEALER_ID IS NULL) OR EXISTS (SELECT t1.CUSTOMER_ID FROM CUSTOMER_ALL t1 WHERE ( (t1.CUSTOMER_ID = t0.DEALER_ID) AND (t1.CSTYPE <> 'd'))) ) ) AND (t0.SM_STATUS <> 'b'));
If we added the hint /*+ optimizer_features_enable(‘220.127.116.11’) */ to the query it worked fine.
After a bit of investigation we found that we could possibly be hitting this bug
Bug 18650065 : WRONG RESULTS ON QUERY WITH SUBQUERY USING OR EXISTS
The solution was either to enable this hidden parameter at the session or database level or to apply the patch 18650065 which is now available for download from MOS.
ALTER SESSION SET “_optimizer_null_accepting_semijoin”=FALSE;
The patch 18650065 can be applied online in both a non-RAC as well as RAC environment
For Non-RAC Environments $ opatch apply online -connectString orcl:SYS:SYS_PASSWORD For RAC Environments 2 node RAC example: $ opatch apply online -connectString orcl1:SYS:SYS_PASSWORD:node1, orcl2:SYS:SYS_PASSWORD:node2