RE: Wrong Results Bug in Oracle 8.1.7.1

Yep sure is.

Ian

-----Original Message-----
Sent: Sunday, June 09, 2002 5:43 AM
To: Multiple recipients of list ORACLE-L

Ian, is that peopleslop?

joe

MacGregor, Ian A. wrote:

>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: Joe Testa
  INET: jtesta_at_dmc-it.com

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).
-- 
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).