Oracle EXISTS query is not working as expected - DB Link bug?

Question

I'm completely baffled by the results of this query:

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

Returns: 1

There are 0 records in [email protected] with stat='2473', so why is it returning true for the exists?

If I change the query like so, it returns 0:

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

UPDATE Okay, this is really weird. Just to see what would happen, I executed the query from the other database (the one referenced by the DB Links) and it gave different (correct) results.

select count(*) from [email protected] 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

The second query in your question is a bit different - it doesn't look at cu.stat at all, and thus the fact that there's nothing with cu.stat = '2473' isn't addressed. What results do you get if you execute

select count(*)
  from [email protected] cu,     
       [email protected] 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 your first query without the use of EXISTS, and should provide correct results.

Share and enjoy.

Answer 2

Have a look at the explain plan for the first query. I suspect there is a bug, and the query plan may show how an invalid rewrite is being done.