遭遇sql查询返回错误结果的bug(hash group by的bug)

同事发过来一个sql，结果非常奇怪：
SELECT *
FROM (SELECT VOUCHERCODE V1, SUM(VALUE) AS CNT1
FROM SEASHELL.ENTRY
WHERE CRDR = 1
GROUP BY VOUCHERCODE) A,
(SELECT VOUCHERCODE V2, SUM(VALUE) AS CNT2
FROM SEASHELL.ENTRY
WHERE CRDR = 2
GROUP BY VOUCHERCODE) B
WHERE A.V1 = B.V2
AND A.CNT1 != B.CNT2
会返回结果集如下：
2007121700001432377 75542390 2007121700001432377 75539390
但是单独把这个vcouchercode写到sql中，却查不出结果，如下：
SELECT *
FROM (SELECT VOUCHERCODE V1, SUM(VALUE) AS CNT1
FROM SEASHELL.ENTRY
WHERE CRDR = 1
AND VCOUCHERCODE = 2007121700001432377
GROUP BY VOUCHERCODE) A,
(SELECT VOUCHERCODE V2, SUM(VALUE) AS CNT2
FROM SEASHELL.ENTRY
WHERE CRDR = 2
AND VCOUCHERCODE = 2007121700001432377
GROUP BY VOUCHERCODE) B
WHERE A.V1 = B.V2
AND A.CNT1 != B.CNT2
这样返回的结果却是NULL。见鬼了，于是去查明细如下：
SELECT VOUCHERCODE, CRDR, VALUE
FROM ENTRY
WHERE VOUCHERCODE = 2007121700001432377；
返回结果如下：
2007121700001432377 1 3000
2007121700001432377 1 75539390
2007121700001432377 2 75539390
2007121700001432377 2 3000

就是说本来不该出现的结果却出现了，难道oracle还会犯这样的错误，查询返回错误的结果？？？

百思不得起解得时候，想到了是否会是一个bug，因为我们的版本是10.2.0.2，所以去查10.2.0.3和10.2.0.4中修复的bug列表，乖乖，吓了一跳，返回错误结果集的bug居然有几十个，只不过我是第一次碰到而已。

此bug的描述如下：
This problem is introduced in the 10.2.0.1 base release.

Wrong results possible from the result of a non-distinct aggregation
with a group by (such as sum(col)) when HASH GROUP BY is used.
The wrong results show as missing values from the aggregate.

Workaround:
Increase the memory used by hash group by
or
Disable the use of hash group by
eg: set optimizer_features_enable to "10.1.0"
or
set "_gby_hash_aggregation_enabled" to FALSE.
Versions confirmed as being affected 10.2.0.1
10.2.0.2

This issue is fixed in 10.2.0.3 (Server Patch Set)
11g (Future version)

可以看到，正好我们的版本中有这个bug，因为optimizer_features_enable参数可以动态修改，而且开发查询的这个库是一个逻辑的dataguard库，于是改这个参数的成本最低，我们来看看修改前后的执行计划的区别。
修改前：
SQL> set autotrace traceonly explain;
SQL> SELECT *
2 FROM (SELECT VOUCHERCODE V1, SUM(VALUE) AS CNT1
3 FROM SEASHELL.ENTRY
4 WHERE CRDR = 1
5 GROUP BY VOUCHERCODE) A,
6 (SELECT VOUCHERCODE V2, SUM(VALUE) AS CNT2
7 FROM SEASHELL.ENTRY
8 WHERE CRDR = 2
9 GROUP BY VOUCHERCODE) B
10 WHERE A.V1 = B.V2
11 AND A.CNT1 != B.CNT2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1119328810

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43309 | 2199K| | 41613 (2)| 00:08:20 | | |
|* 1 | HASH JOIN | | 43309 | 2199K| 31M| 41613 (2)| 00:08:20 | | |
| 2 | VIEW | | 866K| 21M| | 19229 (2)| 00:03:51 | | |
| 3 | HASH GROUP BY | | 866K| 15M| 92M| 19229 (2)| 00:03:51 | | |
| 4 | PARTITION RANGE ALL| | 2139K| 38M| | 11357 (2)| 00:02:17 | 1 | 10 |
| 5 | PARTITION HASH ALL| | 2139K| 38M| | 11357 (2)| 00:02:17 | 1 | 1 |
|* 6 | TABLE ACCESS FULL| ENTRY | 2139K| 38M| | 11357 (2)| 00:02:17 | 1 | 10 |
| 7 | VIEW | | 866K| 21M| | 19243 (2)| 00:03:51 | | |
| 8 | HASH GROUP BY | | 866K| 15M| 92M| 19243 (2)| 00:03:51 | | |
| 9 | PARTITION RANGE ALL| | 2145K| 38M| | 11357 (2)| 00:02:17 | 1 | 10 |
| 10 | PARTITION HASH ALL| | 2145K| 38M| | 11357 (2)| 00:02:17 | 1 | 1 |
|* 11 | TABLE ACCESS FULL| ENTRY | 2145K| 38M| | 11357 (2)| 00:02:17 | 1 | 10 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."V1"="B"."V2")
filter("A"."CNT1"<>"B"."CNT2")
6 - filter("CRDR"=1)
11 - filter("CRDR"=2)

可以看到，其中在第8和第3步都有一个HASH GROUP BY的操作，而正是这个操作的bug引起的问题。
再来看看修改后的结果：
SQL> alter system set optimizer_features_enable='10.1.0';

System altered.

SQL> show parameter optimizer_features_enable ;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 10.1.0

SQL> SELECT *
2 FROM (SELECT VOUCHERCODE V1, SUM(VALUE) AS CNT1
3 FROM SEASHELL.ENTRY
4 WHERE CRDR = 1
5 GROUP BY VOUCHERCODE) A,
6 (SELECT VOUCHERCODE V2, SUM(VALUE) AS CNT2
7 FROM SEASHELL.ENTRY
8 WHERE CRDR = 2
9 GROUP BY VOUCHERCODE) B
10 WHERE A.V1 = B.V2
11 AND A.CNT1 != B.CNT2;

Execution Plan
----------------------------------------------------------
Plan hash value: 20432082

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46531 | 2362K| | 35973 (2)| 00:07:12 | | |
|* 1 | HASH JOIN | | 46531 | 2362K| 33M| 35973 (2)| 00:07:12 | | |
| 2 | VIEW | | 930K| 23M| | 16285 (2)| 00:03:16 | | |
| 3 | SORT GROUP BY | | 930K| 16M| 99M| 16285 (2)| 00:03:16 | | |
| 4 | PARTITION RANGE ALL| | 2301K| 41M| | 7820 (2)| 00:01:34 | 1 | 10 |
| 5 | PARTITION HASH ALL| | 2301K| 41M| | 7820 (2)| 00:01:34 | 1 | 1 |
|* 6 | TABLE ACCESS FULL| ENTRY | 2301K| 41M| | 7820 (2)| 00:01:34 | 1 | 10 |
| 7 | VIEW | | 931K| 23M| | 16312 (2)| 00:03:16 | | |
| 8 | SORT GROUP BY | | 931K| 16M| 99M| 16312 (2)| 00:03:16 | | |
| 9 | PARTITION RANGE ALL| | 2313K| 41M| | 7820 (2)| 00:01:34 | 1 | 10 |
| 10 | PARTITION HASH ALL| | 2313K| 41M| | 7820 (2)| 00:01:34 | 1 | 1 |
|* 11 | TABLE ACCESS FULL| ENTRY | 2313K| 41M| | 7820 (2)| 00:01:34 | 1 | 10 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."V1"="B"."V2")
filter("A"."CNT1"<>"B"."CNT2")
6 - filter("CRDR"=1)
11 - filter("CRDR"=2)
可以看到，原来的HASH GROUP BY变成了SORT GROUP BY，而结果也变成正确的了。

看来，oracle的神话要被打破了，原先只以为bug只会造成数据库的问题，而对返回结果是oracle肯定要保证是对的，不然oracle怎么混啊。
可是它还真的有那么多bug，以后要注意了。