Wrong results from SQL in 8174
- From: "Poras, Henry R." <Henry_Poras@xxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 5 May 2004 16:20:57 -0400
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
