Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Wrong Results Bug in Oracle 8.1.7.1

Wrong Results Bug in Oracle 8.1.7.1

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Sat, 08 Jun 2002 18:58:19 -0800
Message-ID: <F001.004788BA.20020608185819@fatcity.com>


SQL> SELECT VOUCHER_ID FROM PS_VCHR_MM_VW WHERE ROWNUM < 10;

VOUCHER_



00003394
00003395
00003396
00003397
00003398
00003399
00003400
00003401
00003402

set feedback on

  1 SELECT VOUCHER_ID FROM PS_VCHR_MM_VW   2* WHERE VOUCHER_ID = '00003394'
SQL> /
VOUCHER_


1 row selected.



Zounds !!! Select dump(voucher_id) shows that Oracle is returning a null here.

Here a function is used to force the query to do full tablescans

 1 SELECT VOUCHER_ID FROM PS_VCHR_MM_VW   2* WHERE CONCAT(VOUCHER_ID,'A') = CONCAT('00003394', 'A') SQL> / VOUCHER_



00003394

I get the expected results if I force full table scans.

I looked at the explain plan for the failing query and rebuilt the ps_voucher index. This did not change its erroneous results. I rebuilt the view itself again to no avail. A check on Metalink revealed bug 1852163. Although this bug's conditions were different from the one above, their were some similarities. One workaround for bug 1852163

 alter session set "_complex_view_merging" = true;

I tried this and the original query still gave improper results.



All queries against the component tables of the view work fine.

The view text is

CREATE VIEW SYSADM.PS_VCHR_MM_VW
AS
SELECT DISTINCT A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_ID, A.INVOICE_DT, A.PROCESS_INSTANCE, A.ENTRY_STATUS, A.POST_STATUS_AP, A.MATCH_ACTION, C.PPV_POST_FLG, C.ERV_POST_FLG, A.ORIGIN FROM SYSADM.PS_VOUCHER A, SYSADM.PS_PO_LINE_MATCHED C WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT_AP AND A.VOUCHER_ID = C.VOUCHER_ID AND A.MATCH_ACTION IN ('Y', 'E')



If I run the select statement outside of the view and tack on the 'voucher_id = ' clause SELECT DISTINCT A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_ID, A.INVOICE_DT, A.PROCESS_INSTANCE, A.ENTRY_STATUS, A.POST_STATUS_AP, A.MATCH_ACTION, C.PPV_POST_FLG, C.ERV_POST_FLG, A.ORIGIN FROM SYSADM.PS_VOUCHER A, SYSADM.PS_PO_LINE_MATCHED C WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT_AP AND A.VOUCHER_ID = C.VOUCHER_ID AND A.MATCH_ACTION IN ('Y', 'E')
and a.voucher_id = '00003394'
/

I get the expected results. The query plan matches the one for the failing statement.



If I select more than voucher_id from the view with the 'voucher_id = ' predicate the other fields are projected correctly, but returns voucher_id as null.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists

--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Received on Sat Jun 08 2002 - 21:58:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US