From oracle-l-bounce@freelists.org Wed May 5 16:19:52 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i45LJRA12909 for ; Wed, 5 May 2004 16:19:37 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i45LJH612855 for ; Wed, 5 May 2004 16:19:27 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3147972E1B1; Wed, 5 May 2004 16:08:45 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 15087-27; Wed, 5 May 2004 16:08:45 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6954B72E1A1; Wed, 5 May 2004 16:08:44 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 05 May 2004 16:07:20 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6A9B572E164 for ; Wed, 5 May 2004 16:07:19 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 14475-90 for ; Wed, 5 May 2004 16:07:19 -0500 (EST) Received: from cpmx.saic.com (cpmx.mail.saic.com [139.121.17.160]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 17E8672E14D for ; Wed, 5 May 2004 16:07:18 -0500 (EST) Received: from cp-its-ieg01.mail.saic.com by cpmx.saic.com for oracle-l@freelists.org; Wed, 5 May 2004 14:20:58 -0700 Received: from cp-its-exig01.mail.saic.com ([139.121.17.138]) by cp-its-ieg01.mail.saic.com (SAVSMTP 3.1.5.43) with SMTP id M2004050514205823392 for ; Wed, 05 May 2004 14:20:58 -0700 Received: by cp-its-exig01.mail.saic.com with Internet Mail Service (5.5.2657.72) id ; Wed, 5 May 2004 14:20:58 -0700 Message-Id: <88536557A737204B89D6B6B19FF5708F013AE85A@us-oak-ridge-tss.mail.saic.com> From: "Clark, Tommy R" To: oracle-l@freelists.org Subject: RE: Wrong results from SQL in 8174 Date: Wed, 5 May 2004 14:20:48 -0700 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-type: text/plain; charset=iso-8859-1 X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 4522 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: TOMMY.R.CLARK@saic.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org I feel sorry for you. It is an uneasy feeling. I had a similar problem in 9.0.1. Very minor changes to the where clause could result in totally different results. We first noticed it with a between statement. If you coded the literal, it worked. If you used a bind variable it failed. Then when we started looking closer, other minor changes to the where clause were causing us problems. After a few of weeks of going round and round with Oracle support, I gave up and upgraded to 9.2.0.3. That fixed the problem although we never did identify the root bug. -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Poras, Henry R. Sent: Wednesday, May 05, 2004 4:21 PM To: 'oracle-l@freelists.org' Subject: Wrong results from SQL in 8174 Seems to be the day for SQL bugs. A developer here was running a 3 table join. Part of the WHERE clause was a BETWEEN statement. When he increased the range of the BETWEEN, the number of records returned by the query dropped. I ran a sql_trace/tkprof to compare explain plans and row counts. Execution path changed (increasing the range in the BETWEEN increased the expected # of rows returned from the table. Thus the new plan.). The row counts in the problem execution-plan made no sense to me. I tried running both SQL's with an ORDERED hint to force the same execution plan. Both result sets were now the same. Definitely a SQL bug. So now I'm having developers ask "what assurance do we have that the numbers we are reporting in any report are correct?" I'm still trying to track down the root cause of this bug. Anyone else seen it? SQL and explain plans follows. SELECT L1.TREE_NODE_NUM, SUM(A.POSTED_TOTAL_AMT) FROM PS_LEDGER A, PSTREESELECT10 L1, PSTREESELECT15 L WHERE A.LEDGER='DETAIL' AND A.FISCAL_YEAR=2004 AND (A.ACCOUNTING_PERIOD BETWEEN 1 AND 8 OR A.ACCOUNTING_PERIOD=998) AND A.BUSINESS_UNIT IN ('00001', '00002') AND L1.SELECTOR_NUM=234 AND A.ACCOUNT>= L1.RANGE_FROM_10 AND A.ACCOUNT <= L1.RANGE_TO_10 AND L1.TREE_NODE_NUM BETWEEN 1302083332 AND 1311499998 AND L.SELECTOR_NUM=235 AND A.PROJECT_ID>= L.RANGE_FROM_15 AND A.PROJECT_ID <= L.RANGE_TO_15 AND L.TREE_NODE_NUM BETWEEN 609374999 AND 610243054 AND A.CURRENCY_CD='USD' AND A.STATISTICS_CODE=' ' GROUP BY L1.TREE_NODE_NUM Rows Row Source Operation ------- --------------------------------------------------- 6 SORT GROUP BY 14531 CONCATENATION 0 NESTED LOOPS 1 NESTED LOOPS 29 INDEX FAST FULL SCAN (object id 4249) 28 INLIST ITERATOR 56 TABLE ACCESS BY INDEX ROWID PS_LEDGER 56 INDEX RANGE SCAN (object id 24977) 0 INDEX RANGE SCAN (object id 4259) 14531 NESTED LOOPS 45064 MERGE JOIN 29 SORT JOIN 28 INDEX FAST FULL SCAN (object id 4249) <--PSTREESELECT10 INDEX 45091 FILTER 396664 SORT JOIN 51399 INLIST ITERATOR 51399 TABLE ACCESS BY INDEX ROWID PS_LEDGER 51401 INDEX RANGE SCAN (object id 24977) 14531 INDEX RANGE SCAN (object id 4259) <--PSTREESELECT15 INDEX **************************************************************************** **** SELECT L1.TREE_NODE_NUM, SUM(A.POSTED_TOTAL_AMT) FROM PS_LEDGER A, PSTREESELECT10 L1, PSTREESELECT15 L WHERE A.LEDGER='DETAIL' AND A.FISCAL_YEAR=2004 AND (A.ACCOUNTING_PERIOD BETWEEN 1 AND 8 OR A.ACCOUNTING_PERIOD=998) AND A.BUSINESS_UNIT IN ('00001', '00002') AND L1.SELECTOR_NUM=234 AND A.ACCOUNT>= L1.RANGE_FROM_10 AND A.ACCOUNT <= L1.RANGE_TO_10 AND L1.TREE_NODE_NUM BETWEEN 1302083332 AND 1312499998 <--- only change made in query AND L.SELECTOR_NUM=235 AND A.PROJECT_ID>= L.RANGE_FROM_15 AND A.PROJECT_ID <= L.RANGE_TO_15 AND L.TREE_NODE_NUM BETWEEN 609374999 AND 610243054 AND A.CURRENCY_CD='USD' AND A.STATISTICS_CODE=' ' GROUP BY L1.TREE_NODE_NUM Rows Row Source Operation ------- --------------------------------------------------- 0 SORT GROUP BY 0 CONCATENATION 0 MERGE JOIN 1 SORT JOIN 0 NESTED LOOPS 31 INDEX FAST FULL SCAN (object id 4249) 0 INLIST ITERATOR 0 TABLE ACCESS BY INDEX ROWID PS_LEDGER 60 INDEX RANGE SCAN (object id 24977) 0 FILTER 0 SORT JOIN 0 INDEX RANGE SCAN (object id 4259) 0 NESTED LOOPS 1 MERGE JOIN 3 SORT JOIN 2 INDEX RANGE SCAN (object id 4259) 2 FILTER 0 SORT JOIN 51399 INLIST ITERATOR 51399 TABLE ACCESS BY INDEX ROWID PS_LEDGER 51401 INDEX RANGE SCAN (object id 24977) 0 INDEX FAST FULL SCAN (object id 4249) **************************************************************************** **** Thanks. Henry ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------