ITPUB SQL大賽之BUG（八）

這篇碰到的問題並不是一個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在當前使用者的表中找不到，因此Oracle從PUBLIC物件中進行解析，找到一個同義詞後，發現是一個函式，而由於這個函式需要引數才能返回結果，因此SQL執行報錯。

分析到這裡被嚇了一跳，如果這個OGC_DISTANCE函式在沒有引數的情況下也可以返回NUMBER型別的數值，那麼就徹底杯具了。那麼這個bug隱藏的極為隱蔽，很難被檢測到。

看來透過表名的列名的組合來明確的指示一個列是有必要的，至少可以大大減少這種隱蔽錯誤產生的機率。