Oracle 8.1.7 returns wrong result set

From: Peter Lasner <plasner_at_de.imshealth.com>
Date: Wed, 12 Dec 2001 09:00:53 +0100
Message-ID: <3c1710ed_3@news2.prserv.net>

Oracle 8.1.7 returns wrong result set.
The select:

   SELECT /*+ ORDERED USE_NL(m,b,n) */

          m.mb_id
         ,b.bl_valid_from_date,b.bl_valid_to_date
         ,n.NDF_FCC
  FROM pdgi_master_bridges m


      ,pdgi_bridge_links b

      ,pdgi_ndf n

  WHERE m.mb_type  = 'P'
    and m.MB_CTRY_ID = 'D'
    AND m.mb_institution_id IS NULL
    and b.bl_mb_id (+) = m.mb_id
    AND b.bl_basis (+) = 'NDF'

    AND b.bl_basis_version (+) = '0'
    AND n.NDF_id (+) = b.BL_NDF_ID
and n.ndf_fcc = 239145 -- for this test only

   AND NOT EXISTS

        (SELECT NULL
           FROM pdgi_bridge_links y
           WHERE y.bl_mb_id           = b.bl_mb_id
             AND y.bl_basis           = b.bl_basis
             AND y.bl_basis_version   = b.bl_basis_version
             AND y.bl_valid_from_date > b.bl_valid_from_date)

The result:
MB_ID BL_VALID_FROM_DATE BL_VALID_TO_DATE NDF_FCC 746156 01.01.0001 31.12.2000 23:59:59 239145 this row shouldn't be returned !

746156   01.01.2001      null                239145

Without the subselect the same two rows are returned. Can anyone help ? Received on Wed Dec 12 2001 - 02:00:53 CST