oracle full outer join bug,测试一下你的Oracle有Full Outer Join的Bug么？9i以上请进

测试一下你的Oracle有Full Outer Join的Bug么？9i以上请进

BUG描述：FULL OUTER JOIN <> LEFT OUTER JOIN UNION RIGHT OUTER JOIN

不知道Oracle网站上有没有相关描述，我在网上相关信息只搜索到left outer join可使用户可以查询任何表

我的数据库服务器环境：OS：Windows 2000 Server  DB：Oracle 9.2.0.1

测试请写出你的数据库服务器环境

创建测试表

--子表

CREATE TABLE TS

(

ID   VARCHAR2(4 BYTE),

PID  VARCHAR2(4 BYTE),

DM1  VARCHAR2(4 BYTE),

DM2  VARCHAR2(4 BYTE)

);

--父表，ID与子表PID对应

CREATE TABLE TP

(

ID   VARCHAR2(4 BYTE),

CON  VARCHAR2(4 BYTE)

);

--代码表

CREATE TABLE TDM

(

TYPE  VARCHAR2(4 BYTE),

DM    VARCHAR2(4 BYTE),

MC    VARCHAR2(10 BYTE)

);

插入测试数据

INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (

'1', '1', 'a', 'aa');

INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (

'2', '2', 'b', NULL);

INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (

'3', '1', 'a', 'ss');

INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (

'4', '3', 'a', 'ss');

INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (

'5', '3', 'c', NULL);

INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (

'7', '7', 'c', 'ee');

INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (

'9', NULL, 'a', NULL);

INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (

'10', '9', NULL, NULL);

COMMIT;

INSERT INTO TP ( ID, CON ) VALUES (

'1', 'a');

INSERT INTO TP ( ID, CON ) VALUES (

'2', 'b');

INSERT INTO TP ( ID, CON ) VALUES (

'3', 'c');

INSERT INTO TP ( ID, CON ) VALUES (

'6', 'f');

COMMIT;

INSERT INTO TDM ( TYPE, DM, MC ) VALUES (

'1', 'a', 'AA');

INSERT INTO TDM ( TYPE, DM, MC ) VALUES (

'1', 'b', 'BB');

INSERT INTO TDM ( TYPE, DM, MC ) VALUES (

'1', 'c', 'CC');

INSERT INTO TDM ( TYPE, DM, MC ) VALUES (

'1', 'd', 'DD');

INSERT INTO TDM ( TYPE, DM, MC ) VALUES (

'1', 'e', 'EE');

INSERT INTO TDM ( TYPE, DM, MC ) VALUES (

'2', 'aa', 'AAAA');

INSERT INTO TDM ( TYPE, DM, MC ) VALUES (

'2', 'ss', 'SSSS');

INSERT INTO TDM ( TYPE, DM, MC ) VALUES (

'2', 'dd', 'DDDD');

COMMIT;

表TS与表TDM外连接两次，查出DM1和DM2对应的MC

select a.*,b.mc MC1, c.mc MC2

from ts a,tdm b, tdm c

where  A.dm1 = B.DM(+) AND B.type(+)='1'

and  A.dm2 = c.DM(+) AND c.type(+)='2'

这个sql将会在下面的测试中多次用到

(其实只要3和6都做了就能看到这个BUG)

1)测试LEFT OUTER JOIN

select s.*,p.* from

(

select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c

where  A.dm1 = B.DM(+) AND B.type(+)='1'

and  A.dm2 = c.DM(+) AND c.type(+)='2'

) s left outer join

(

select * from tp

)  p

on s.pid=p.id

2)测试RIGHT OUTER JOIN

select s.*,p.* from

(

select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c

where  A.dm1 = B.DM(+) AND B.type(+)='1'

and  A.dm2 = c.DM(+) AND c.type(+)='2'

) s right outer join

(

select * from tp

)  p

on s.pid=p.id

3)测试LEFT OUTER JOIN UNION RIGHT OUTER JOIN

select s.*,p.* from

(

select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c

where  A.dm1 = B.DM(+) AND B.type(+)='1'

and  A.dm2 = c.DM(+) AND c.type(+)='2'

) s left outer join

(

select * from tp

)  p

on s.pid=p.id

union

select s.*,p.* from

(

select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c

where  A.dm1 = B.DM(+) AND B.type(+)='1'

and  A.dm2 = c.DM(+) AND c.type(+)='2'

) s right outer join

(

select * from tp

)  p

on s.pid=p.id

4)测试LEFT OUTER JOIN UNION ALL RIGHT OUTER JOIN

select s.*,p.* from

(

select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c

where  A.dm1 = B.DM(+) AND B.type(+)='1'

and  A.dm2 = c.DM(+) AND c.type(+)='2'

) s left outer join

(

select * from tp

)  p

on s.pid=p.id

union all

select s.*,p.* from

(

select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c

where  A.dm1 = B.DM(+) AND B.type(+)='1'

and  A.dm2 = c.DM(+) AND c.type(+)='2'

) s right outer join

(

select * from tp

)  p

on s.pid=p.id

5)测试内连接(INNER JOIN)

select s.* from

(

select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c

where  A.dm1 = B.DM(+) AND B.type(+)='1'

and  A.dm2 = c.DM(+) AND c.type(+)='2'

) s inner  join

(

select * from tp

)  p

on s.pid=p.id

6)测试全外连接(FULL OUTER JOIN)

select s.*,p.* from

(

select a.*,b.mc MC1, c.mc MC2

from ts a,tdm b, tdm c

where  A.dm1 = B.DM(+) AND B.type(+)='1'

and  A.dm2 = c.DM(+) AND c.type(+)='2'

) s full outer join

(

select * from tp

)  p

on s.pid=p.id

下面是测试结果(只列举了3和6)

[php]

SQL> select * from ts;

ID   PID  DM1  DM2

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

1    1    a    aa

2    2    b

3    1    a    ss

4    3    a    ss

5    3    c

7    7    c    ee

9         a

10   9

已选择8行。

SQL> select * from tp;

ID   CON

---- ----

1    a

2    b

3    c

6    f

SQL> select * from tdm;

TYPE DM   MC

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

1    a    AA

1    b    BB

1    c    CC

1    d    DD

1    e    EE

2    aa   AAAA

2    ss   SSSS

2    dd   DDDD

已选择8行。

SQL> select s.*,p.* from

2  (

3  select a.*,b.mc MC1, c.mc MC2

4  from ts a,tdm b, tdm c

5  where  A.dm1 = B.DM(+) AND B.type(+)='1'

6  and  A.dm2 = c.DM(+) AND c.type(+)='2'

7  ) s full outer join

8  (

9  select * from tp

10  )  p

11  on s.pid=p.id

12  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON

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

3    1    a    ss   AA         SSSS       1    a

1    1    a    aa   AA         AAAA       1    a

2    2    b         BB                    2    b

5    3    c         CC                    3    c

4    3    a    ss   AA         SSSS       3    c

9         a         AA

10   9

7    7    c    ee   CC

6    f

2    b

已选择10行。

SQL> select s.*,p.* from

2  (

3  select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c

4  where  A.dm1 = B.DM(+) AND B.type(+)='1'

5  and  A.dm2 = c.DM(+) AND c.type(+)='2'

6  ) s left outer join

7  (

8  select * from tp

9  )  p

10  on s.pid=p.id

11  union

12  select s.*,p.* from

13  (

14  select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c

15  where  A.dm1 = B.DM(+) AND B.type(+)='1'

16  and  A.dm2 = c.DM(+) AND c.type(+)='2'

17  ) s right outer join

18  (

19  select * from tp

20  )  p

21  on s.pid=p.id

22  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON

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

1    1    a    aa   AA         AAAA       1    a

10   9

2    2    b         BB                    2    b

3    1    a    ss   AA         SSSS       1    a

4    3    a    ss   AA         SSSS       3    c

5    3    c         CC                    3    c

7    7    c    ee   CC

9         a         AA

6    f

已选择9行。

。。。。。。。。。。。。。。。

[/php]

可见在这里出现了FULL OUTER JOIN <> LEFT OUTER JOIN UNION RIGHT OUTER JOIN的现象，而按常理这两个应该是相等的

即FULL OUTER JOIN = LEFT OUTER JOIN UNION RIGHT OUTER JOIN并且还有

LEFT OUTER JOIN UNION ALL RIGHT OUTER JOIN - INNER JOIN = FULL OUTER JOIN

如果表TS与表TDM只外连接一次，那么不会出现这个现象。

另外还发现一个有趣的怪现象，对表TP做一个简单的变换(并不影响其结果集的记录数)，做一次full outer join

然后加上一个条件限制(也不影响其结果集的记录数)，再做一次full outer join

两次的结果居然不一样，而且还都是不对的

[php]

SQL> select s.*,p.* from

2  (

3  select a.*,b.mc MC1, c.mc MC2

4  from ts a,tdm b, tdm c

5  where  A.dm1 = B.DM(+) AND B.type(+)='1'

6  and  A.dm2 = c.DM(+) AND c.type(+)='2'

7  ) s full outer join

8  (

9  select * from (select tp.*,rank()over(order by id) rn from tp) --where rn<10

10  )  p

11  on s.pid=p.id

12  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON          RN

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

3    1    a    ss   AA         SSSS       1    a             1

1    1    a    aa   AA         AAAA       1    a             1

2    2    b         BB                    2    b             2

5    3    c         CC                    3    c             3

4    3    a    ss   AA         SSSS       3    c             3

9         a         AA

10   9

7    7    c    ee   CC

6    f

2    b

已选择10行。

这个和刚才的测试6效果一样，最后一条记录是多余的

SQL> select s.*,p.* from

2  (

3  select a.*,b.mc MC1, c.mc MC2

4  from ts a,tdm b, tdm c

5  where  A.dm1 = B.DM(+) AND B.type(+)='1'

6  and  A.dm2 = c.DM(+) AND c.type(+)='2'

7  ) s full outer join

8  (

9  select * from (select tp.*,rank()over(order by id) rn from tp) where rn<10

10  )  p

11  on s.pid=p.id

12  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON          RN

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

3    1    a    ss   AA         SSSS       1    a             1

1    1    a    aa   AA         AAAA       1    a             1

2    2    b         BB                    2    b             2

5    3    c         CC                    3    c             3

4    3    a    ss   AA         SSSS       3    c             3

9         a         AA

10   9

7    7    c    ee   CC

已选择8行。

这个结果跟左连接没啥区别-V-

SQL> select s.*,p.* from

2  (

3  select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c

4  where  A.dm1 = B.DM(+) AND B.type(+)='1'

5  and  A.dm2 = c.DM(+) AND c.type(+)='2'

6  ) s left outer join

7  (

8  select * from tp

9  )  p

10  on s.pid=p.id

11  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON

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

3    1    a    ss   AA         SSSS       1    a

1    1    a    aa   AA         AAAA       1    a

2    2    b         BB                    2    b

5    3    c         CC                    3    c

4    3    a    ss   AA         SSSS       3    c

9         a         AA

10   9

7    7    c    ee   CC

已选择8行。

。。。。。。。。。。。。。。。。。。。

[/php]