Path: news.easynews.com!easynews!newshub2.home.com!news.home.com!news.maxwell.syr.edu!newsfeed.sovam.com!sovam!not-for-mail
From: Ushkalo Igor <igorus@protek.ru>
Newsgroups: comp.databases.oracle.server
Subject: Re: Oracle 8.1.7 returns wrong result set
Date: Wed, 12 Dec 2001 12:30:31 +0300
Organization: Protek
Lines: 47
Message-ID: <3C1723B7.3060609@protek.ru>
References: <3c1710ed_3@news2.prserv.net>
NNTP-Posting-Host: 194.154.72.10
Mime-Version: 1.0
Content-Type: text/plain; charset=KOI8-R; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: news.sovam.com 1008149389 4267 194.154.72.10 (12 Dec 2001 09:29:49 GMT)
X-Complaints-To: abuse@online.ru
NNTP-Posting-Date: Wed, 12 Dec 2001 09:29:49 +0000 (UTC)
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:0.9.4) Gecko/20011128 Netscape6/6.2.1
X-Accept-Language: ru,en
Xref: easynews comp.databases.oracle.server:127925
X-Received-Date: Wed, 12 Dec 2001 02:50:09 MST (news.easynews.com)

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