I'm seeing a very strange behavior of a query that is using COALESCE function with a correlated subquery.
Environment:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
I have the following tables:
CREATE TABLE A
(
ID NUMBER(10),
VERSION NUMBER(10)
);
CREATE TABLE B
(
ID NUMBER(10),
VERSION NUMBER(10)
);
INSERT INTO A VALUES (1, 1);
INSERT INTO A VALUES (1, 2);
INSERT INTO A VALUES (1, 3);
COMMIT;
The following query unexpectedly returns no rows:
SELECT *
FROM A
WHERE A.ID = 1
AND A.VERSION > COALESCE ((SELECT MAX(B.VERSION) FROM B WHERE B.ID = A.ID), 0);
However, the next two queries work as expected (the first returning 1 and the second 3 rows):
SELECT *
FROM (SELECT 1 ID FROM DUAL) A
WHERE COALESCE ((SELECT MAX(B.VERSION) FROM B WHERE B.ID = A.ID), 0) = 0;
SELECT COALESCE ((SELECT MAX(B.VERSION) FROM B WHERE B.ID = A.ID), 0)
FROM A
WHERE A.ID = 1;
All three queries work as expected in Oracle Enterprise Edition.
Is it a bug in Oracle XE or is there something wrong I'm doing?