Oracle EXISTS query not working properly - DB Link error?

Question

I am completely disappointed with the results of this query:

select count(*) from my_tab mt where mt.stat = '2473' and mt.name= 'Tom' and exists (select * from [e-mail] cu, [e-mail] pr where cu.user_id = pr.user_id and mt.name = pr.name and mt.stat = cu.stat ) 

Returns: 1

There are 0 entries in [e-mail] with stat = '2473', so why does it return true for an existing one?

If I changed the request like this, it returns 0:

 select count(*) from my_tab mt where mt.stat = '2473' and mt.name= 'Tom' and exists (select * from [e-mail] cu, [e-mail] pr where cu.user_id = pr.user_id and mt.name = pr.name and cu.stat = '2473' ) 

UPDATE Okay, this is really weird. To see what happens, I executed a query from another database (the one referenced by the database links) and gave different (correct) results.

 select count(*) from [e-mail] mt where mt.stat = '2473' and mt.name= 'Tom' and exists (select * from company_users cu, personnel_records pr where cu.user_id = pr.user_id and mt.name = pr.name and mt.stat = cu.stat ) 

Returns 0 (as expected).

Answer 1

Take a look at the plan for explaining the first request. I suspect there is an error, and the query plan may show how an invalid rewrite is performed.

Answer 2

The second request in your question is slightly different - it does not look at cu.stat at all, and therefore the fact that nothing with cu.stat = '2473' is being processed. What results will you get if you follow

 select count(*) from [e-mail] cu, [e-mail] pr, my_tab mt where mt.stat = '2473' and mt.name = 'Tom' and pr.name = mt.name and cu.user_id = pr.user_id and cu.stat = mt.stat 

I think this is equivalent to the first query without using EXISTS and should provide the correct results.

Share and enjoy.