Simple Query Acts different in Ora8 than Ora7

From: Frank Siegel <NorthernSnow_at_worldnet.att.net>
Date: Thu, 30 Sep 1999 11:14:10 -0400
Message-ID: <7svv3h$i06$1@bgtnsc02.worldnet.att.net>

Thanks in advance for any support you may provide. Why are the rows counts different between Oracle 7.3.3 and 8.0.5?

SELECT count(*)

  FROM   customer_table        a,
                date_control_table   b

  WHERE
    b.enddate BETWEEN
    a.effectivedate AND NVL(a.terminationdate, TO_DATE('99991231','YYYYMMDD')); Oracle 7.3.3 returns 519,881 rows
Oracle 8.0.5 returns 541,185 rows

WHY THE DIFFERENCE of 21K rows????

SOME NOTES:
-customer_table has 4 million rows
-date_control_table has 1 row
-Oracle uses cartesian product but only 1 row in date_control_table
-b.endate is defined as DATE and equals '01-AUG-99'
-a.effectivedate and a.terminationdate are defined as DATE
-The difference in row count is 21K but we only have 7500 year 2000 rows in
the
  table so y2k does not seem to be the issue.

Thanks again,
Frank S.
New Hampshire Received on Thu Sep 30 1999 - 10:14:10 CDT