Wrong results from SQL in 8174

From: Poras, Henry R. <Henry_Poras_at_dfci.harvard.edu>
Date: Wed, 5 May 2004 16:20:57 -0400
Message-ID: <0D3AAB332E29BA4F9D8BE559F244A914558B38@phsexch27.mgh.harvard.edu>

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_at_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
-----------------------------------------------------------------

Received on Wed May 05 2004 - 15:20:34 CDT