2007-01-26

Bug I found in Oracle DB

Sometimes I( or people on my team) encounter some Oracle bugs.
I present here the test case of the last bug I found in PL/SQL that gives bad results
(I already warn Oracle and the Bug got the number 5847371) in Oracle 10.2.0.2.


CREATE TABLE a_test (a VARCHAR2(20));
INSERT INTO a_test VALUES ('test');

CREATE OR REPLACE
FUNCTION teste_a0 (p_notifica IN INTEGER DEFAULT 0)
RETURN PLS_INTEGER
IS
l_found PLS_INTEGER := 0;
BEGIN
FOR c
IN (SELECT a_test.a
FROM a_test
WHERE (p_notifica = 0))
LOOP
l_found := 1;
END LOOP;

RETURN l_found;
END
;
/

SELECT TESTE_A0(0) from dual;
--should return 1 (correct !)


CREATE INDEX a_test_i ON a_test
(
a ASC,
0 ASC
)
/

SELECT TESTE_A0(0) from dual;

--returns 0!! when should return 1

if the line
WHERE (p_notifica = 0))
is exchanged by
WHERE (p_notifica+1 = 1))

the result is correct =>1

No comments:

Post a Comment

Os comentários são moderados.
The comments are moderated.