Hello, everyone.
Has anyone encountered the following bug in 11.2 on Linux server?
select sd_id, amount, sum(amount) over()
from (select d.sd_id, d.amount
from stlm_docs d
where d.sd_id = 2372962
union all
select rel.parent_sd_id sd_id, -rel.src_amount amount
from stlm_doc_relations rel
where rel.parent_sd_id = 2372962);
2372962 0,48 199,78
2372962 -0,7 199,78
I expect to see -0.22 in the last column, but somehow Oracle returns 199.78
If I add + 1 to either select's amount it returns the correct result
select sd_id, amount, sum(amount) over()
from (select d.sd_id, d.amount
from stlm_docs d
where d.sd_id = 2372962
union all
select rel.parent_sd_id sd_id, -rel.src_amount + 1 amount
from stlm_doc_relations rel
where rel.parent_sd_id = 2372962);
2372962 0,48 0,78
2372962 0,3 0,78
Simple sum (not analytical) returns the wrong results as well
select sum(amount)
from (select d.sd_id, d.amount
from stlm_docs d
where d.sd_id = 2372962
union all
select rel.parent_sd_id sd_id, -rel.src_amount amount
from stlm_doc_relations rel
where rel.parent_sd_id = 2372962);
199,78
Changing execution plan does not solve the problem either. All three plans end up returning the wrong result
Plan Hash Value : 180622559
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 7 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 2 | 26 | 7 | 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | STLM_DOCS | 1 | 10 | 3 | 00:00:01 |
| * 5 | INDEX UNIQUE SCAN | PK_SD_SD_ID | 1 | | 2 | 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | STLM_DOC_RELATIONS | 1 | 10 | 4 | 00:00:01 |
| * 7 | INDEX RANGE SCAN | IND_SDR_PARENT_SD_ID | 1 | | 3 | 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("D"."SD_ID"=2372962)
* 7 - access("REL"."PARENT_S
D_ID"=2372962)
Plan Hash Value : 3838268115
--------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 7 | 00:00:01 |
| 1 | WINDOW BUFFER | | 2 | 52 | 7 | 00:00:01 |
| 2 | VIEW | | 2 | 52 | 7 | 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | STLM_DOCS | 1 | 10 | 3 | 00:00:01 |
| * 5 | INDEX UNIQUE SCAN | PK_SD_SD_ID | 1 | | 2 | 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | STLM_DOC_RELATIONS | 1 | 10 | 4 | 00:00:01 |
| * 7 | INDEX RANGE SCAN | IND_SDR_PARENT_SD_ID | 1 | | 3 | 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("D"."SD_ID"=2372962)
* 7 - access("REL"."PARENT_SD_ID"=2372962)
Plan Hash Value : 234777423
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 155 | 00:00:01 |
| 1 | WINDOW BUFFER | | 2 | 52 | 155 | 00:00:01 |
| 2 | VIEW | | 2 | 52 | 155 | 00:00:01 |
| 3 | UNION-ALL | | | | | |
| * 4 | TABLE ACCESS FULL | STLM_DOCS | 1 | 10 | 106 | 00:00:01 |
| * 5 | TABLE ACCESS FULL | STLM_DOC_RELATIONS | 1 | 10 | 49 | 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 4 - filter("D"."SD_ID"=2372962)
* 5 - filter("REL"."PARENT_SD_ID"=2372962)
Thank you in advance!