Re: Oracle 8.1.7 returns wrong result set

Peter Lasner wrote:

> 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 ?

It could be a bug #1578644... we was hit on it. Try to set _optim_enhance_nnull_detection = false If it'll no result - remove this parameter.

Try to search metalink for bugs, apply resent patches, everything as usual B-)

• Best regards, Igor Ushkalo (igorus!) - igorus(at)mail.ru, ICQ #19972198