由于SQL大赛题目相对比较困难，不但需要使用大量的特性，且SQL实现十分复杂，一般运行时间也会比较长，这些因素导致碰到bug的几率直线上升。这里介绍SQL大赛期间碰到的几个bug。

第二个bug仍然和第一个问题有关，尝试利用OPTIMIZER_FEATURES_ENABLE解决问题时碰到了错误。

ITPUB SQL大赛之BUG（一）：http://yangtingkun.itpub.net/post/468/515815

正常情况下无论是在SQL提示中使用OPTIMIZER_FEATURES_ENABLE，还是设置会话或实例级的OPTIMIZER_FEATURES_ENABLE参数，同样都可以解决上一篇文章中提到的问题。

SQL> with i as
2 (select rownum i from dual connect by rownum <= :n),
3 j as
4 (select rownum j from dual connect by rownum <= :n),
5 position as
6 (select i, j
7 from i, j),
8 b as
9 (select rownum - 1 b from dual connect by rownum <= 2),
10 b_line as
11 (select replace(sys_connect_by_path(b, ','), ',', '') line
12 from b
13 where connect_by_isleaf = 1
14 connect by level <= :n),
15 lines as
16 (select line from b_line
17 where instr(line, 1, 1, :m) > 0
18 and instr(line, 0, 1, :n - :m) > 0),
19 lines_result as
20 (select replace(sys_connect_by_path(line, ','), ',', '') result
21 from lines
22 where connect_by_isleaf = 1
23 connect by level <= :n)
24 select :m M, :n N, count(*) AllCnt
25 from (
26 select result
27 from (
28 select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
29 from lines_result, position
30 group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
31 group by result
32 having max(case when l is null and r is null then c end) = :m
33 and max(case when j is null and l is null then c end) = :m
34 and max(case when j is null and r is null then c end) = :m
35 );

M N ALLCNT
---------- ---------- ----------
2 5 931

已用时间: 00: 01: 22.04

SQL> with i as
2 (select rownum i from dual connect by rownum <= :n),
3 j as
4 (select rownum j from dual connect by rownum <= :n),
5 position as
6 (select i, j
7 from i, j),
8 b as
9 (select rownum - 1 b from dual connect by rownum <= 2),
10 b_line as
11 (select replace(sys_connect_by_path(b, ','), ',', '') line
12 from b
13 where connect_by_isleaf = 1
14 connect by level <= :n),
15 lines as
16 (select line from b_line
17 where instr(line, 1, 1, :m) > 0
18 and instr(line, 0, 1, :n - :m) > 0),
19 lines_result as
20 (select replace(sys_connect_by_path(line, ','), ',', '') result
21 from lines
22 where connect_by_isleaf = 1
23 connect by level <= :n)
24 select /*+ optimizer_features_enable('10.2.0.3') */ :m M, :n N, count(*) AllCnt
25 from (
26 select result
27 from (
28 select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
29 from lines_result, position
30 group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
31 group by result
32 having max(case when l is null and r is null then c end) = :m
33 and max(case when j is null and l is null then c end) = :m
34 and max(case when j is null and r is null then c end) = :m
35 );

M N ALLCNT
---------- ---------- ----------
2 5 92

已用时间: 00: 00: 30.59

SQL> alter session set optimizer_features_enable = '10.2.0.4';

会话已更改。

已用时间: 00: 00: 00.01
SQL> with i as
2 (select rownum i from dual connect by rownum <= :n),
3 j as
4 (select rownum j from dual connect by rownum <= :n),
5 position as
6 (select i, j
7 from i, j),
8 b as
9 (select rownum - 1 b from dual connect by rownum <= 2),
10 b_line as
11 (select replace(sys_connect_by_path(b, ','), ',', '') line
12 from b
13 where connect_by_isleaf = 1
14 connect by level <= :n),
15 lines as
16 (select line from b_line
17 where instr(line, 1, 1, :m) > 0
18 and instr(line, 0, 1, :n - :m) > 0),
19 lines_result as
20 (select replace(sys_connect_by_path(line, ','), ',', '') result
21 from lines
22 where connect_by_isleaf = 1
23 connect by level <= :n)
24 select :m M, :n N, count(*) AllCnt
25 from (
26 select result
27 from (
28 select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
29 from lines_result, position
30 group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
31 group by result
32 having max(case when l is null and r is null then c end) = :m
33 and max(case when j is null and l is null then c end) = :m
34 and max(case when j is null and r is null then c end) = :m
35 );

M N ALLCNT
---------- ---------- ----------
2 5 92

已用时间: 00: 00: 23.26

不过如果尝试将优化器降级到11.1.0.6或11.1.0.7，则会导致ORA-976错误的出现：

SQL> with i as
2 (select rownum i from dual connect by rownum <= :n),
3 j as
4 (select rownum j from dual connect by rownum <= :n),
5 position as
6 (select i, j
7 from i, j),
8 b as
9 (select rownum - 1 b from dual connect by rownum <= 2),
10 b_line as
11 (select replace(sys_connect_by_path(b, ','), ',', '') line
12 from b
13 where connect_by_isleaf = 1
14 connect by level <= :n),
15 lines as
16 (select line from b_line
17 where instr(line, 1, 1, :m) > 0
18 and instr(line, 0, 1, :n - :m) > 0),
19 lines_result as
20 (select replace(sys_connect_by_path(line, ','), ',', '') result
21 from lines
22 where connect_by_isleaf = 1
23 connect by level <= :n)
24 select /*+ optimizer_features_enable('11.1.0.6') */ :m M, :n N, count(*) AllCnt
25 from (
26 select result
27 from (
28 select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
29 from lines_result, position
30 group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
31 group by result
32 having max(case when l is null and r is null then c end) = :m
33 and max(case when j is null and l is null then c end) = :m
34 and max(case when j is null and r is null then c end) = :m
35 );
select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
*
第 28 行出现错误:
ORA-00976: 此处不允许指定的伪列或运算符。

已用时间: 00: 00: 00.01

SQL> alter session set optimizer_features_enable = '11.1.0.6';

会话已更改。

已用时间: 00: 00: 00.02
SQL> with i as
2 (select rownum i from dual connect by rownum <= :n),
3 j as
4 (select rownum j from dual connect by rownum <= :n),
5 position as
6 (select i, j
7 from i, j),
8 b as
9 (select rownum - 1 b from dual connect by rownum <= 2),
10 b_line as
11 (select replace(sys_connect_by_path(b, ','), ',', '') line
12 from b
13 where connect_by_isleaf = 1
14 connect by level <= :n),
15 lines as
16 (select line from b_line
17 where instr(line, 1, 1, :m) > 0
18 and instr(line, 0, 1, :n - :m) > 0),
19 lines_result as
20 (select replace(sys_connect_by_path(line, ','), ',', '') result
21 from lines
22 where connect_by_isleaf = 1
23 connect by level <= :n)
24 select :m M, :n N, count(*) AllCnt
25 from (
26 select result
27 from (
28 select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
29 from lines_result, position
30 group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
31 group by result
32 having max(case when l is null and r is null then c end) = :m
33 and max(case when j is null and l is null then c end) = :m
34 and max(case when j is null and r is null then c end) = :m
35 );
select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
*
第 28 行出现错误:
ORA-00976: 此处不允许指定的伪列或运算符。

已用时间: 00: 00: 00.02

看来，无论是11.2还是11.1的优化器，在处理复杂的树形查询时，都存在bug。在metalink上没有找到相关的问题描述，可能这个问题还没有被发现。

ITPub博客

ITPUB SQL大赛之BUG（二）

最新文章