| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Wrong results from SQL in 8174
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Poras, Henry R.
Sent: Wednesday, May 05, 2004 4:21 PM
To: 'oracle-l_at_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=' '
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=' '
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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Wed May 05 2004 - 16:19:52 CDT
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
 |
 |