oracle full out join,[ORACLE]Oracle 10G中触发full outer join BUG的1个例子及解决方法

Oracle 10G中已知的关于full outer

join的BUG不少，有时候明明自己写的SQL没有问题，翻来覆去地检查，但查寻出的结果怎么都和自己预期的不一样，着实让人很郁闷。我这里就举了一个小例子：

首先给出版本信息：

ETL@RACTEST> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 -

64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

然后有两个测试表t1和t2：

ETL@RACTEST> select * from t1;

A B

----- ----------

c 10

e 5

Elapsed: 00:00:00.02

ETL@RACTEST> select * from t2;

A B

----- ----------

e 3

e 2

f 2

Elapsed: 00:00:00.01

我对t2表做了一个很简单的分组操作：

ETL@RACTEST> select a,sum(b) s from t2 group by

a;

A S

----- ----------

e 5

f 2

然后我执行一个全外连接：

select *

from t1

a

full

outer join

(select

a,sum(b) s from t2 group by a) b

on

(a.a=b.a and a.b=b.s);

A B

A S

----- ---------- ----- ----------

e 5

e 5

c 10

Elapsed: 00:00:00.01

是不是很诡异的结果？

正确的结果应该是：

A B

A S

----- ---------- ----- ----------

e 5

e 5

c 10

f 2

Elapsed: 00:00:00.02

我在11G中验证结果是正确的。

经过分析执行计划发现Oracle在做查询优化改写时做了不等价的改写，导致查询结果错误。

这里给出一种方法避免触发这个oracle

10g的BUG，让SQL先老老实实地做内层查询，不做查询优化改写，方法是在内层查询中加一个rownum

> 0的筛选条件：

select *

from t1

a

full

outer join

(select

a,sum(b) s from t2 where rownum > 0 group by a)

b

on

(a.a=b.a and a.b=b.s);

A B

A S

----- ---------- ----- ----------

e 5

e 5

c 10

f 2

这回结果就正确了。

如果感兴趣可以自己研究一下执行计划在哪一步做了错误的不等价改写，这里就不给出了。