1. Home
  2. Knowledge Base
  3. Database Upgrade
  4. Wrong Results On Query With Subquery Using OR EXISTS After upgrade to 12.1.0.2

Wrong Results On Query With Subquery Using OR EXISTS After upgrade to 12.1.0.2

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(‘11.2.0.4’) */ 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


Updated on June 2, 2021

Was this article helpful?

Related Articles

Leave a Comment