Skip to Main Content

Oracle Database Express Edition (XE)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Problem with using COALESCE with a correlated subquery

user5557094Dec 23 2016 — edited Jan 9 2017

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?

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 6 2017
Added on Dec 23 2016
11 comments
2,241 views