ITPUB SQL大賽之BUG（三）

由於SQL大賽題目相對比較困難，不但需要使用大量的特性，且SQL實現十分複雜，一般執行時間也會比較長，這些因素導致碰到bug的機率直線上升。這裡介紹SQL大賽期間碰到的幾個bug。

這篇描述碰到的ORA-22813錯誤。

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

ITPUB SQL大賽之BUG（二）：http://yangtingkun.itpub.net/post/468/515926

 

嚴格意義上講，這個問題可能並不是Oracle的bug：

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 rownum, 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 result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c, max(replace(wmsys.wm_concat(substr(result, (i-1)*:n + j, 1)) over(partition by j order by i), ',' ,'')) col
 25  from lines_result, position
 26  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i)));
(select rownum - 1 b from dual connect by rownum <= 2),
 *
第 9 行出現錯誤:
ORA-22813: 運算元值超出系統的限制

導致問題的原因多半是由於wmsys.wm_concat函式造成的。正常情況下，SQL語句造成資源不足的錯誤多半是臨時表空間無法擴充套件，而這裡的ORA-22813錯誤，顯然是PL/SQL程式中資源不足導致的，而wmsys.wm_concat函式顯然是Oracle透過TYPE型別實現的PL/SQL函式。

雖然導致問題的真正原因是處理的資料量太大所致，但是Oracle的這個錯誤資訊太不明確了，至少應該提示使用者這個限制的具體值是多少。而這種錯誤資訊對於解決問題顯然沒有任何的幫助。

這個問題最終透過修改SQL的方式來解決，由於當前的查詢中wmsys.wm_concat所需要處理的資料量太大，最終將SQL改為先求出所有滿足的記錄，然後對這些記錄進行變形，這樣wmsys.wm_concat函式所處理記錄數縮小了好幾個數量級，因此避免了ORA-22813錯誤的出現。