Strange SQL Results

From: Phillippa Verity Davies <Phillippa_at_veritydavies.freeserve.co.uk>
Date: Sat, 10 Apr 1999 07:34:43 +0100
Message-ID: <7emrjc$eg9$1@news8.svr.pol.co.uk>

I would like to understand why I am getting the following results, it seems like a possible problem in the database engine to me, but if some one can explain it great! It is happening on Oracle 8, though I have no idea of the fix level of the product.

I have a large table with at least 3 date fields all of which allow nulls, I am just counting the rows that match certain criteria.

SELECT COUNT(*) WHERE DateA>TODATE('01-01-1998') returns approx. 20K

SELECT COUNT(*) WHERE DateA>TODATE('01-01-1998') OR DateB>TODATE('01-01-1998')
returns approx. 50K

SELECT COUNT(*) WHERE DateA>TODATE('01-01-1998') OR DateB>TODATE('01-01-1998') OR DateC>TODATE('01-01-1998') Returns approx. 30K

I am surprised that an additional OR condition could reduce the number of rows. Received on Sat Apr 10 1999 - 01:34:43 CDT