Re: Strange behavior

What version of oracle? Any bitmap indexes? Are the tables in question index or heap organized? There are numerous bugs which cause oracle to return an incorrect result, such as in your case. Have you contacted tech support? As for me, I can only quote the answer to the Asimov's story "The Last Question": insufficient information for a meaningful answer.

On 01/07/2004 02:49:26 PM, Stephen Andert wrote:
> Greetings fellow-DBA-folk:
>
> When I run several queries, I am getting very strange results.
> Selecting * from apple returns the expected data records, but count(*)
> does not.
> Selecting anything other than * from apple says no records.
> The subquery for pear works fine on it's own.
> Selecting anything other than * from apple returns the expected records
> when about 900+ values are placed literally in the subquery as shown in
> example 4 below.
>
> Am I missing something? Anyone have a direction for me to look for the
> cause of this?
>
> Thanks
> Stephen
>
> EX 1
> select rowid from common.apple
> where appleinter1 in (select pearinternal from common.pear where
> pear_clnt_src_cd = 'CCN')
> and appletermd is null
> and rownum < 10 ;
>
> no rows selected
>
> EX 2
> select rownum from common.apple
> where appleinter1 in (select pearinternal from common.pear where
> pear_clnt_src_cd = 'CCN')
> and appletermd is null
> and rownum < 10 ;
>
> no rows selected
>
> EX 3
> select * from common.apple
> where appleinter1 in (select pearinternal from common.pear where
> pear_clnt_src_cd = 'CCN')
> and appletermd is null
> and rownum < 10 ;
>
> --- 9 rows returned, I removed them for confidentiality reasons ---
>
> EX 4
> select count(*) from common.apple
> where appleinter1 in ( 7009 , 7010 , 7011 , 7012 , 7013 , 7014 , 7015 ,
> 7016 , 7017 , 7018 , 7019)
> and appletermd is null
> and rownum < 10 ;
>
> --- 9 rows returned, I removed them for confidentiality reasons ---
>
> EX 5
> select count(*) from common.apple
> where appleinter1 in (select pearinternal from common.pear where
> pear_clnt_src_cd = 'CCN')
> and appletermd is null;
>
> COUNT(*)
> ------------
> 0
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Stephen Andert
> INET: StephenAndert_at_firsthealth.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

--
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).