Strange Behaviour (with Test Case)

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Tue, 4 Aug 2015 17:51:07 +0800
Message-ID: <CABx0cSXbjHExV1AQNgkRZHMvYne==Ju-rb4FJD603wx_r9rb3w_at_mail.gmail.com>

I have been working though a strange case, seems specific to Exadata 12c. I have simplified as much as possible for now, testcase below. Obviously we are following up with Oracle support, but wonder if anyone is able to confirm what we are seeing.
And/Or anybody want to hazard a guess as to what is going wrong? TIA
Patrick

CREATE TABLE TEST_FACT(CHRTYPE VARCHAR2(3), RATE NUMBER)

INSERT INTO TEST_FACT VALUES('R03', 1.3)
INSERT INTO TEST_FACT VALUES('R03', 1.3)
INSERT INTO TEST_FACT VALUES('LDU', 0.21)

COMMIT; SELECT SUM(

      CASE
         WHEN (CHRTYPE = 'R03' OR CHRTYPE = 'LDU')
         THEN RATE     /10
      END )

   FROM TEST_FACT; Result on 11.2.0.4.4 and 12.1.0.2.3 (Linux) 0.281

12.1.0.2.7 (Exadata)
-8.70E+103

--

http://www.freelists.org/webpage/oracle-l Received on Tue Aug 04 2015 - 11:51:07 CEST