Bizarre Select count(distinct column) issue
Oracle 10.2.0.3 64bit for Solaris, optimizer features enabled to 10.2.0.3
A client is running SQL that looks like the following
select count(distinct table1id)
from table1,table2
where table1.tableid=table2.tableid; and getting 0 returned.
when they run
select count(*)
from (select distinct table1id)
from table1,table2
where table1.tableid=table2.tableid) they get 10 as the result, which is correct.
we cannot duplicate the issue here on our 10.2.0.4 windows 64 bit database and a select distinct table1id from table1 does not return any null values.
I'm stumped, has anyone come across something like this before? we cannot see their system (security rules) and i can't think of any more revealing queries to see if there is a data issue.
I appreciate any help anyone can give me.