ITPUB SQL大賽之BUG（二）

由於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上沒有找到相關的問題描述，可能這個問題還沒有被發現。