ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ITPUB SQL大赛之BUG(八)

ITPUB SQL大赛之BUG(八)

原创 Linux操作系统 作者:yangtingkun 时间:2011-04-11 23:54:35 0 删除 编辑

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

这篇描述PLS-306错误。

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

ITPUB SQL大赛之BUG(七):http://yangtingkun.itpub.net/post/468/516340

 

 

这篇碰到的问题并不是一个bug,但是却有可能成为一个隐藏很深,很难检查到的问题。

在第三期修改SQL的过程中,有一次碰到了这样一个错误:

SQL> WITH ROUTE_D AS /* get full routes */
  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 /* get every route of any two cities */
 10  (
 11     SELECT R, T, CAST('"' || R || '"' || T || '"' AS VARCHAR2(4000)), DIS /* avoid ora-1489 error */
 12     FROM ROUTE_D
 13     UNION ALL
 14     SELECT A.C1, T, LINES || T || '"', DIS + DISTANCE /* '"' for avoid city name contains other city name */
 15     FROM ROUTE_D R, ROUTE_ALL_D A
 16     WHERE A.C2 = R.R
 17     AND INSTR(LINES, '"' || T || '"', 1, 1) = 0 /* avoit duplicate city */
 18     AND DISTANCE + DIS <= /* filter the distance longer than routes */
 19             NVL
 20             (
 21                     (
 22                             SELECT DISTANCE
 23                             FROM ROUTES RS
 24                             WHERE (A.C1 = RS.CITY1
 25                                     AND R.T = RS.CITY2)
 26                             OR (A.C1 = RS.CITY2
 27                                     AND R.T = RS.CITY1)
 28                     ),
 29                     9.9E38
 30             )
 31  ),
 32  RESULT_HALF AS
 33  (
 34     SELECT C1 R, C2 T, MIN(DISTANCE) DIS
 35     FROM ROUTE_ALL_D
 36     WHERE C1 < C2
 37     GROUP BY C1, C2
 38  ),
 39  RESULT_ALL AS
 40  (
 41     SELECT R, T, DIS
 42     FROM RESULT_HALF
 43     UNION ALL
 44     SELECT T, R, DIS
 45     FROM RESULT_HALF
 46  ),
 47  RESULT AS
 48  (
 49     SELECT R, T,
 50             DISTANCE * 2 * C.MEMBERS COST,
 51             SUM(DISTANCE * 2 * C.MEMBERS) OVER(PARTITION BY R) COST_CITY
 52     FROM RESULT_ALL R, CITIES C
 53     WHERE R.T = C.CITY_NAME(+) /* any city in route can win even the city have no member */
 54  )
 55  SELECT R, NVL(T, 'TOTAL') T, NVL(SUM(COST), 0) COST
 56  FROM
 57  (
 58     SELECT R, T, COST, RANK() OVER(ORDER BY COST_CITY) RN
 59     FROM RESULT
 60  )
 61  WHERE RN = 1
 62  GROUP BY GROUPING SETS ((R, T), R)
 63  ORDER BY R, DECODE(T, 'TOTAL', CHR(0), T);
                SUM(DISTANCE * 2 * C.MEMBERS) OVER(PARTITION BY R) COST_CITY
                    *
51 行出现错误:
ORA-06553: PLS-306:
调用 'OGC_DISTANCE' 时参数个数或类型错误

这个错误很有意思,只是在优化调整SQL,怎么会突然出现一个PLS-306的错误,而且我也没有调用任何什么特别的函数。

仔细检查后发现,由于调整了WITH子句中列的别名,DISTANCE被改成了DIS,而查询这个WITH子句的SQL没有进行对应的修改,导致了这个错误。

从这一点上看,这个DISTANCE还应该是一个PUBLIC的同义词才对,检查数据字典:

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
  2  FROM DBA_OBJECTS
  3  WHERE OBJECT_NAME = 'DISTANCE';

OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -------------------
PUBLIC     DISTANCE                       SYNONYM

SQL> SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
  2  FROM DBA_SYNONYMS
  3  WHERE SYNONYM_NAME = 'DISTANCE';

OWNER      SYNONYM_NAME    TABLE_OWNER     TABLE_NAME
---------- --------------- --------------- ------------------------------
PUBLIC     DISTANCE        MDSYS           OGC_DISTANCE

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
  2  FROM DBA_OBJECTS
  3  WHERE OBJECT_NAME = 'OGC_DISTANCE'
  4  AND WNER = 'MDSYS';

OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -------------------
MDSYS      OGC_DISTANCE                   FUNCTION

到这里已经和错误信息对上了,由于指定的DISTANCE在当前用户的表中找不到,因此OraclePUBLIC对象中进行解析,找到一个同义词后,发现是一个函数,而由于这个函数需要参数才能返回结果,因此SQL运行报错。

分析到这里被吓了一跳,如果这个OGC_DISTANCE函数在没有参数的情况下也可以返回NUMBER类型的数值,那么就彻底杯具了。那么这个bug隐藏的极为隐蔽,很难被检测到。

看来通过表名的列名的组合来明确的指示一个列是有必要的,至少可以大大减少这种隐蔽错误产生的几率。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-692268/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1954
  • 访问量
    10634718