Re: Queries give impossible results

From: Matthias Rogel <rogel_at_web.de>
Date: Fri, 15 Nov 2002 11:16:41 +0100
Message-ID: <ar2hia$emul1$1@ID-86071.news.dfncis.de>

sorry jusung,

when you have a (finite) set
- of course in this newsgroup every set is finite - with say n elements
"count(*) = n"

and you have property P1 which is fulfilled by p1 members of the set "(count(*) where P1) = p1"
and a property P2 which is fulfilled by p2 members of the set "(count(*) where P2) = p2"

"count(*) where (P1 AND P2)"
MUST BE AT LEAST p1 + p2 - n

so in jakes case
n = 138295
p1 = 135324
p2 = 138292

so jake's final query which results in "4" should be at least 135321.

as jake posted , a
SELECT /*+ RULE */ count(*)
FROM ci_customer
WHERE customer_state = 'ACTIVE'
AND customer_type <> 'PERSON';

yields exactly 135321

Jusung Yang wrote:
> Maybe by now you have figured out what is going with your data and
> that ORACLE, after all, did not give you wrong results. I don't think
> ORACLE is capable of giving wrong results for simple queries like
> these.
>
>

>>Using common sense it's easy to understand that there are more
>>than 4 customers that are not 'PERSON' but are 'ACTIVE'. 

>
>
> I failed to see how this is common sense.
> Did I misunderstand you or did you come to this conclusion :
>
> count( customer_state = 'ACTIVE and customer_type <>
> 'PERSON')
> must be much larger than 4 because both these counts are so high
> count(customer_state = 'ACTIVE) : 135324
> count(customer_type <> 'PERSON') : 138292
> ?
>
> (123450000444555666667890123456780xxxx)
>
> (xxxxabcdefghijklmmmmmmmnnnoppppptyuuuuu)
>
> The two groups above can each have many, many members. But there may
> be only a few members (the xxxx) that belong to both groups ( the
> "and" operator in your case).
>
> You can even try a count on the "or"
> count( customer_state = 'ACTIVE' or customer_type <>
> 'PERSON')
> The result should be 135324 + 138292 - 4.
> This will be true regardless whether or not there are NULLs in the
> table.
>
>
> - Jusung Yang
Received on Fri Nov 15 2002 - 04:16:41 CST