ITPUB SQL大賽之BUG（七）
由於SQL大賽題目相對比較困難，不但需要使用大量的特性，且SQL實現十分複雜，一般執行時間也會比較長，這些因素導致碰到bug的機率直線上升。這裡介紹SQL大賽期間碰到的幾個bug。
這篇給出ORA-1489錯誤的原因和解決方法。
ITPUB SQL大賽之BUG（一）：http://yangtingkun.itpub.net/post/468/515815
ITPUB SQL大賽之BUG（二）：http://yangtingkun.itpub.net/post/468/515926
ITPUB SQL大賽之BUG（三）：http://yangtingkun.itpub.net/post/468/515982
ITPUB SQL大賽之BUG（四）：http://yangtingkun.itpub.net/post/468/516023
ITPUB SQL大賽之BUG（五）：http://yangtingkun.itpub.net/post/468/516219
ITPUB SQL大賽之BUG（六）：http://yangtingkun.itpub.net/post/468/516307
由於篇幅所限，前兩篇只是描述了現象，並沒有解釋問題產生的真正原因。
其實導致問題的關鍵是遞迴子查詢中的UNION ALL語句，當遞迴WITH第一次執行時，LINES列的資料型別已經確定下來，而隨後的執行過程中，如果列返回的長度超過了列的定義，則會導致ORA-1489錯誤。
SQL> CREATE TABLE T_UNION_ALL AS
2
SELECT '1' C FROM DUAL
3
UNION ALL
4
SELECT '1234' FROM DUAL;
表已建立。
SQL> DESC T_UNION_ALL
名稱
是否為空? 型別
----------------------------------- --------
--------------------------------------------
C VARCHAR2(4)
可以看得，對於UNION ALL語句，資料型別長度的確定由各個子句共同決定。
不過遞迴WITH子句則更復雜一些：
SQL> CREATE TABLE T_RES_WITH AS
2
WITH A (N, C) AS
3
(
4
SELECT 1, '1'
5
FROM DUAL
6
UNION ALL
7
SELECT N + 1, C || '1'
8
FROM A R, DUAL A
9
WHERE N <= 2
10
)
11 SELECT * FROM A;
表已建立。
SQL> DESC T_RES_WITH
名稱
是否為空? 型別
----------------------------------- --------
--------------------------------------------
N NUMBER
C VARCHAR2(3)
對於遞迴WITH而言，情況就要複雜一些，首先UNION ALL的第二個子句中的C，其實就是UNION ALL第一個子句中的’1’,這個長度是1，而當前表示式的長度則是2。也就是說新的C列的長度是2，而遞迴WITH語句中出現的最大長度是C || ‘1’,因此，遞迴WITH語句的最終長度是3。
為了更好的說明這個問題，看一個和大賽SQL有關的例子：
SQL> CREATE TABLE T_RES_WITH2 AS
2
WITH ROUTE_ALL_S (C1, C2, LINES, DIS) AS
3
(
4
SELECT CITY1, CITY2, CITY1 || CITY2, DISTANCE
5
FROM ROUTES
6
UNION ALL
7
SELECT SUBSTR(LINES, 1, 1), CITY2, LINES || '-' || CITY1 || CITY2, DISTANCE
+ DIS
8
FROM ROUTES R, ROUTE_ALL_S A
9
WHERE A.C2 = R.CITY1
10
)
11
SELECT LINES
12
FROM ROUTE_ALL_S
13 WHERE ROWNUM < 10;
表已建立。
SQL> DESC T_RES_WITH2
名稱
是否為空? 型別
----------------------------------- --------
--------------------------------------------
LINES VARCHAR2(62)
由於CITY1和CITY2的長度都是10，因此LINES || ‘-’ || CITY1 || CITY2的總長度就是20 + 1 + 10 + 10 = 41，而遞迴WITH最終字串長度就是41 + 1 + 10 + 10 = 62。
下面看看CONCAT和||有什麼區別：
SQL> CREATE TABLE T_RES_WITH3 AS
2
WITH ROUTE_ALL_S (C1, C2, LINES, DIS) AS
3
(
4
SELECT CITY1, CITY2, CITY1 || CITY2, DISTANCE
5
FROM ROUTES
6
UNION ALL
7
SELECT SUBSTR(LINES, 1, 1), CITY2, CONCAT(LINES, '-' || CITY1 || CITY2),
DISTANCE + DIS
8
FROM ROUTES R, ROUTE_ALL_S A
9
WHERE A.C2 = R.CITY1
10
)
11
SELECT LINES
12
FROM ROUTE_ALL_S
13 WHERE ROWNUM < 10;
表已建立。
SQL> DESC T_RES_WITH3
名稱
是否為空? 型別
----------------------------------- --------
--------------------------------------------
LINES VARCHAR2(62)
可以看得，無論是||還是CONCAT，在生成字串長度的時候並沒有區別，不過執行下面的SQL就會看到區別：
SQL> WITH ROUTE_D AS
2
(
3
SELECT CITY1 R, CITY2 T, DISTANCE DIS
4
FROM ROUTES
5
UNION ALL
6
SELECT CITY2, CITY1, DISTANCE
7
FROM ROUTES
8
),
9
ROUTE_ALL_D (C1, C2, LINES) AS
10
(
11
SELECT R, T, R || T
12
FROM ROUTE_D
13
WHERE R = 'E'
14
UNION ALL
15
SELECT A.C1, T, LINES || RPAD('-', 20, '-') || R || T || RPAD('-', 20, '-')
16
FROM ROUTE_D R, ROUTE_ALL_D A
17
WHERE A.C2 = R.R
18
)
19
CYCLE LINES SET DUP_LINES TO 'Y' DEFAULT 'N'
20
SELECT LENGTH(LINES)
21 FROM ROUTE_ALL_D;
LENGTH(LINES)
-------------
2
2
44
44
44
44
44
44
44
44
44
44
86
86
86
.
.
.
86
86
ERROR:
ORA-01489: 字串連線的結果過長
已選擇51行。
這個SQL從SQL大賽結果中抽取了部分內容並進行了簡單的變形後得到的，為了是儘快出現ORA-1489錯誤。
根據前面計算遞迴WITH的字串長度，這裡允許的最大長度應為：20 + 60 + 60 = 140，而遞迴到下一層的實際長度只有86 + 20 + 1 + 1 + 20 = 128，小於列的最大長度。
看一下使用CONCAT函式的情況：
SQL> WITH ROUTE_D AS
2
(
3
SELECT CITY1 R, CITY2 T, DISTANCE DIS
4
FROM ROUTES
5
UNION ALL
6
SELECT CITY2, CITY1, DISTANCE
7
FROM ROUTES
8
),
9
ROUTE_ALL_D (C1, C2, LINES) AS
10
(
11
SELECT R, T, R || T
12
FROM ROUTE_D
13
WHERE R = 'E'
14
UNION ALL
15
SELECT A.C1, T, CONCAT(LINES, RPAD('-', 20, '-') || R || T || RPAD('-', 20,
'-'))
16
FROM ROUTE_D R, ROUTE_ALL_D A
17
WHERE A.C2 = R.R
18
)
19
CYCLE LINES SET DUP_LINES TO 'Y' DEFAULT 'N'
20
SELECT LENGTH(LINES)
21 FROM ROUTE_ALL_D;
LENGTH(LINES)
-------------
2
2
44
44
44
44
44
44
44
44
44
44
86
86
86
.
.
.
86
86
128
128
.
.
.
128
128
128
ERROR:
ORA-01489: 字串連線的結果過長
已選擇211行。
可以看到，使用CONCAT函式，則遞迴WITH的長度可以達到剛才計算的值128，這比使用||遞迴深了一層，可以處理更多的資料，這也是為什麼上一篇文章中使用||會報錯，而使用CONCAT可以順利執行的原因。使用||時，Oracle允許的最大的長度也是140，為什麼在遞迴WITH執行的時候，字串長度到了86後，再次迭加就會報錯，懷疑||操作這裡存在bug，Oracle計算長度的時候使用了列的定義，而不是實際的長度。Oracle可能認為86的下一層長度會達到86 + 20 + 10 + 10 + 20 ＝ 146，超過了最大的長度，因此報錯。
下面再看第五篇文章的問題：當使用C1、C2作為ROUTE_S的列，則會出現ORA-1489的錯誤，而如果使用SUBSTR的方式，則不會導致錯誤。
其實這個道理很簡單，由於使用了SUBSTR，使得列的長度發生了變化：
SQL> CREATE TABLE T_WITH_SUBSTR AS
2
WITH ROUTE_ALL_S (C1, C2, LINES, DIS) AS
3
(
4
SELECT CITY1, CITY2, CITY1 || CITY2, DISTANCE
5
FROM ROUTES
6
UNION ALL
7
SELECT SUBSTR(LINES, 1, 1), CITY2, LINES || '-' || CITY1 || CITY2, DISTANCE
+ DIS
8
FROM ROUTES R, ROUTE_ALL_S A
9
WHERE A.C2 = R.CITY1
10
),
11
ROUTE_S AS
12
(
13
SELECT SUBSTR(LINES, 1, 1) R, SUBSTR(LINES, LENGTH(LINES)) T, MIN(DIS) DIS
14
FROM ROUTE_ALL_S
15
GROUP BY SUBSTR(LINES, 1, 1), SUBSTR(LINES, LENGTH(LINES))
16
),
17
ROUTE_D AS
18
(
19
SELECT R, T, DIS
20
FROM ROUTE_S
21
UNION ALL
22
SELECT T, R, DIS
23
FROM ROUTE_S
24
),
25
ROUTE_ALL_D (C1, C2, LINES, DISTANCE) AS
26
(
27
SELECT R, T, R || T, DIS
28
FROM ROUTE_D
29
UNION ALL
30
SELECT SUBSTR(LINES, 1, 1), T, LINES || '-' || R || T, DIS + DISTANCE
31
FROM ROUTE_D R, ROUTE_ALL_D A
32
WHERE A.C2 = R.R
33
AND INSTR(LINES, R || T) = 0
34
AND INSTR(LINES, T || R) = 0
35
AND C1 != T
36
AND INSTR(LINES, R, 1, 2) = 0
37
AND INSTR(LINES, T, 1, 2) = 0
38
AND DISTANCE + DIS <= NVL((SELECT DISTANCE FROM ROUTE_D RS WHERE C1 =
RS.R AND R.T = RS.T), 9.9E38)
39
)
40
SELECT LINES
41
FROM ROUTE_ALL_D
42
WHERE ROWNUM < 1;
表已建立。
SQL> DESC T_WITH_SUBSTR
名稱
是否為空? 型別
----------------------------------- --------
--------------------------------------------
LINES VARCHAR2(497)
SQL> CREATE TABLE T_WITH_SUBSTR2 AS
2
WITH ROUTE_ALL_S (C1, C2, LINES, DIS) AS
3
(
4
SELECT CITY1, CITY2, CITY1 || CITY2, DISTANCE
5
FROM ROUTES
6
UNION ALL
7
SELECT SUBSTR(LINES, 1, 1), CITY2, LINES || '-' || CITY1 || CITY2, DISTANCE
+ DIS
8
FROM ROUTES R, ROUTE_ALL_S A
9
WHERE A.C2 = R.CITY1
10
),
11
ROUTE_S AS
12
(
13
SELECT SUBSTR(LINES, 1, 1) R, SUBSTR(LINES, LENGTH(LINES)) T, MIN(DIS) DIS
14
FROM ROUTE_ALL_S
15
GROUP BY SUBSTR(LINES, 1, 1), SUBSTR(LINES, LENGTH(LINES))
16
)
17
SELECT * FROM ROUTE_S;
表已建立。
SQL> DESC T_WITH_SUBSTR2
名稱
是否為空? 型別
----------------------------------- --------
--------------------------------------------
R VARCHAR2(2)
T VARCHAR2(124)
DIS NUMBER
SQL> CREATE TABLE T_WITH_SUBSTR3 AS
2
WITH ROUTE_ALL_S (C1, C2, LINES, DIS) AS
3
(
4
SELECT CITY1, CITY2, CITY1 || CITY2, DISTANCE
5
FROM ROUTES
6
UNION ALL
7
SELECT SUBSTR(LINES, 1, 1), CITY2, LINES || '-' || CITY1 || CITY2, DISTANCE
+ DIS
8
FROM ROUTES R, ROUTE_ALL_S A
9
WHERE A.C2 = R.CITY1
10
),
11
ROUTE_S AS
12
(
13
SELECT SUBSTR(LINES, 1, 1) R, SUBSTR(LINES, LENGTH(LINES)) T, MIN(DIS) DIS
14
FROM ROUTE_ALL_S
15
GROUP BY SUBSTR(LINES, 1, 1), SUBSTR(LINES, LENGTH(LINES))
16
),
17
ROUTE_D AS
18
(
19
SELECT R, T, DIS
20
FROM ROUTE_S
21
UNION ALL
22
SELECT T, R, DIS
23
FROM ROUTE_S
24
)
25
SELECT * FROM ROUTE_D;
表已建立。
SQL> DESC T_WITH_SUBSTR3
名稱
是否為空? 型別
----------------------------------- --------
--------------------------------------------
R VARCHAR2(124)
T VARCHAR2(124)
DIS NUMBER
對於SUBSTR(LINES, 1, 1)而言，考慮到雙位元組字符集的因素，因此最大長度是2，而對於SUBSTR(LINES, LENGTH(LINES))而言，這個最大長度雖然也應該是2，但是對於Oracle而言是未知的，因為不是標量，無法在計算之前獲取，因此Oracle認為最大長度是LINES長度的2倍（考慮雙位元組字符集的原因），而LINES的長度可以透過上面的方法來進行計算，是62，所以R和T的長度分別變成了2和124，而再經過UNION ALL的操作，這兩個值的長度都變成了124。
這時Oracle計算的LINES值達到了124 + 124 + 1 + 124 + 124 = 497。注意，可能是由於字串已經達到了一定的長度，這裡Oracle沒有在這個長度的基礎上增加一次遞迴的長度。不過即使是497，對於當前的問題也是足夠，這就是第五篇文章問題中使用SUBSTR可以得到結果，而直接使用C1、C2列會報錯的原因。
說了這麼多似乎還沒有給出解決方法，其實最穩妥的方法是利用CAST來指定列的長度，從而避免錯誤的產生:
SQL> WITH ROUTE_D AS
2
(
3
SELECT CITY1 R, CITY2 T, DISTANCE DIS
4
FROM ROUTES
5
UNION ALL
6
SELECT CITY2, CITY1, DISTANCE
7
FROM ROUTES
8
),
9
ROUTE_ALL_D (C1, C2, LINES, DISTANCE) AS
10
(
11
SELECT R, T, CAST('"' || R || '"' || T || '"' AS VARCHAR2(4000)),
DIS
12
FROM ROUTE_D
13
UNION ALL
14
SELECT A.C1, T, LINES || T || '"', DIS + DISTANCE
15
FROM ROUTE_D R, ROUTE_ALL_D A
16
WHERE A.C2 = R.R
17
AND INSTR(LINES, '"' || T || '"', 1, 1) = 0
18
AND DISTANCE + DIS <= NVL((SELECT DISTANCE FROM ROUTES RS WHERE (A.C1
= RS.CITY1 AND R.T = RS.CITY2) OR (A.C1 = RS.CITY2 AND R.T = RS.CITY1)), 9.9E38)
19
),
20
RESULT AS
21
(
22
SELECT C1 R, C2 T,
23 SUM(MIN(DISTANCE) * 2 * MAX(C.MEMBERS))
OVER(PARTITION BY C1, C2) COST,
24 SUM(MIN(DISTANCE) * 2 * MAX(C.MEMBERS))
OVER(PARTITION BY C1) COST_CITY
25
FROM ROUTE_ALL_D R, CITIES C
26
WHERE R.C2 = C.CITY_NAME(+)
27
GROUP BY R.C1, R.C2
28
)
29
SELECT R, NVL(T, 'TOTAL') T, NVL(SUM(COST), 0) COST
30
FROM RESULT
31
WHERE COST_CITY = (SELECT MIN(COST_CITY) FROM RESULT)
32
GROUP BY GROUPING SETS ((R, T), R)
33
ORDER BY R, DECODE(T, 'TOTAL', CHR(0), T);
R T COST
---------- ---------- ----------
D TOTAL 68356
D A 3200
D B 3224
D C 3634
D E 7300
D F 7598
D G 3840
D H 14580
D I 4400
D J 12352
D K 8228
D L 0
已選擇12行。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-692194/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ITPUB SQL大賽之BUG（八）SQL
- ITPUB SQL大賽之BUG（六）SQL
- ITPUB SQL大賽之BUG（五）SQL
- ITPUB SQL大賽之BUG（四）SQL
- ITPUB SQL大賽之BUG（三）SQL
- ITPUB SQL大賽之BUG（二）SQL
- ITPUB SQL大賽之BUG（一）SQL
- ITPUB SQL大賽第三期SQL
- ITPUB SQL大賽第一期SQL
- ITPUB SQL大賽第二期SQL
- ITPUB SQL大賽第四期SQL
- ITPUB SQL大賽第三期（二）SQL
- ITPUB SQL大賽第二期（二）SQL
- ITPUB SQL大賽第二期（一）SQL
- sql大賽總結SQL
- sql大賽第四期SQL
- 第七屆藍橋杯大賽個人賽省賽（軟體類）真題 C語言A組 1C語言
- 過年七天樂，三大遊戲賽道“預言”今年走勢？遊戲
- SQL資料庫程式設計大賽隨感SQL資料庫程式設計
- SQL資料庫程式設計大賽開幕SQL資料庫程式設計
- 七天帶你玩轉MySQL之SQL語句MySql
- “天府杯”超級網安大賽集結|攻防實戰七大看點全爆料
- 第七章：四大元件之Service元件
- SQL菜鳥筆記之第七篇 SQL單行函式 （上）SQL筆記函式
- itpub 復活節最短sql（未完成程式碼）SQL
- Introduction to SQL Tuning Advisor zt自ITPUBSQL
- SQL經典練習題48道之七(41-48)SQL
- MyBatis(七) 動態SQLMyBatisSQL
- itpub
- 【ITPUB】ITPUB十週年感言 - Secooler
- 物件導向之七大基本原則（javaScript）物件JavaScript
- 一路走來之“網路技術大賽”
- 「雜文」演算法競賽之黑話大賞演算法
- upyun開發者大賽
- 積木大賽
- 適用於SQL Server生產環境DBA的七大技巧SQLServer
- oracle之BUG 7497640Oracle
- Python學習系列之七大資料型別Python大資料資料型別