RE: Wrong Results Bug in Oracle 8.1.7.1

From: Lord, David - CSG <David.Lord_at_hayscsg.com>
Date: Mon, 10 Jun 2002 00:58:19 -0800
Message-ID: <F001.00478CE8.20020610005819@fatcity.com>

Ian

Not sure its related, but I had a similar problem recently on 8.1.7.0 - a query returning the wrong number of rows - but in this case it was throwing an ora-7445 after a few reruns and the table had both function-based and IMT indexes. OWS came up with 'Stack trace matches bug 1561106 which leads back to several other bugs which use a text query or index and or a first rows hint'. Applying 8.1.7.3 fixed it so I never really knew the cause.

David Lord

> -----Original Message-----
> From: MacGregor, Ian A. [mailto:ian_at_SLAC.Stanford.EDU]
> Sent: 09 June 2002 03:58
> To: Multiple recipients of list ORACLE-L
> Subject: Wrong Results Bug in Oracle 8.1.7.1
>
>
> 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).
>

---

This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at email.helpdesk_at_hays.plc.uk
Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays.  

A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ.

---

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Lord, David - CSG
  INET: David.Lord_at_hayscsg.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).

Received on Mon Jun 10 2002 - 03:58:19 CDT