Wrong Results on 12c

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Mon, 25 Jul 2016 16:50:00 +0800
Message-ID: <CABx0cSXXeKJX_NucKnEnLj-O=5ST4DL++C+QEeJgjYXqBC6_vg_at_mail.gmail.com>

Just thought I would share an invalid results issue we just encountered on 12c.

DROP TABLE MY_COMPANY;
DROP TABLE MY_SITE;
CREATE TABLE MY_COMPANY(SITE_ID NUMBER); CREATE TABLE MY_SITE(SITE_ID NUMBER NOT NULL PRIMARY KEY); INSERT INTO MY_COMPANY VALUES (NULL);
COMMIT; SELECT COUNT(*) FROM MY_COMPANY c WHERE c.site_id is null or EXISTS

      (SELECT 1
         FROM MY_SITE S
        WHERE S.Site_Id = C.Site_Id
          AND S.Site_Id = 2);

Very similar but not quite the same as bug 18650065. Can see from CBO trace that query is transformed (incorrectly) to the following:

WHERE "S"."SITE_ID"="C"."SITE_ID" AND "S"."SITE_ID"=2

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 25 2016 - 10:50:00 CEST