ITPUB SQL大赛之BUG（七）

最新推荐文章于 2021-07-13 15:01:10 发布

ckawt40802

最新推荐文章于 2021-07-13 15:01:10 发布

阅读量97

点赞数

文章标签：数据库

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

这篇给出ORA-1489错误的原因和解决方法。

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

ITPUB SQL大赛之BUG（二）：

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-692194/