Wrong Resultバグに遭遇したのでDBMS_SQLDIAGで解決策を探る (Oracle)

## はじめに

先日再帰クエリでゴニョゴニョやってるときに「バグじゃないのこれ？」って感じの結果が出力されました。ま、再帰クエリのバグ（というかOracleのバグ）は結構多いのでバグとの遭遇自体にはさほどおどろきもないのですが、今回はせっかくなので(?) DBMS_SQLDIAGでSQL PATCHをつくったりヒントで解決してみようというお話です。

> 追記: よく考えたらOS書いてなかったですね。Linux上での現象なので、Windowsだとまた違った動きかもしれません。

## 正しい値を返さない再帰クエリ

対象となるクエリは以下です。簡単な再帰クエリですが、再帰に対して直積の外部結合しています。正しい結果は、nが偶数のときにのみ'Even'が表示されるはずですが、オラクルバージョン「11.2/12.1」と「12.2/18c/19c」では実行結果が異なります。

```sql
WITH nums (n, msg) AS (
         SELECT 1, '' FROM DUAL
         UNION ALL
         SELECT n + 1 ,even
         FROM   nums
                LEFT JOIN (SELECT 'Even' even FROM DUAL)
                           ON MOD (n + 1, 2) = 0
         WHERE  n < 6)
SELECT * FROM   nums;
```

まずは正しい結果です。12.1,11.2で実行すると偶数のときにのみ'Even'が表示されます。

```
# 11gR2 / 12cR1
         N MSG
---------- ----
         1
         2 Even
         3
         4 Even
         5
         6 Even
```

しかし同じクエリを12.2以降で実行すると、余計な行にも'Even'が現れます。

```
# 12cR2 / 18c / 19c
         N MSG
---------- ----
         1
         2 Even
         3 Even
         4 Even
         5 Even
         6 Even
```

う～ん、これ仕様じゃないですよね？(^^) 一応再帰クエリ内では再帰クエリへのRIGHT OUTER JOINは禁止となってますが、LEFT OUTER JOINは問題ないはずですし、なによりも11.2/12.1では意図した結果が帰ってきてるのでバグと見做しても差し支えないかと思います。

ちなみに、[Oracle SQL Live](https://livesql.oracle.com/) (現在のバージョンは19.8)でも正しくない結果が帰ってくるので、まだパッチは出てないんじゃないかな。しらんけど。



## DBMS_SQLDIAGでクエリを解析

では、ここからDBMS_SQLDIGAを利用して解決策をさぐってみたいと思います。流れとしては以下のとおりです。

- DBMS_SQLDIAGやSQL PATCHの実行に必要な権限を付与する
- 診断タスクを作る(create_diagnosis_task)
- 診断タスクを実行する(execute_diagnosis_task)
- 診断結果を参照する(report_diagnosis_task)
- SQL PATCHを作成する(accept_sql_patch)
- SQL PATCHの内容を確認する(sys.sqlobj$data)
- SQL PATCHから得られたヒントをつけて実行してみる



### 権限の付与

DBMS_SQLDIAGを使うには、ADVISOR権限が必要です。さらにSQL PATCHの作成、削除にはCREATE ANY SQL PROFILE、DROP ANY SQL PROFILEが必要なので、ユーザに付与しておきます。

```sql
SQL> GRANT ADVISOR, CREATE ANY SQL PROFILE, DROP ANY SQL PROFILE TO testuser;
```

### SQL IDの取得

次に診断タスクを作成するためにクエリを実行してSQL_IDを取得します。SQL_IDがなくてもSQLテキストで直接タスクをつくることもできますが、SQL_IDがあったほうが後々楽なので。

```sql
SQL> SET SERVEROUT OFF
SQL> WITH nums (n, msg) AS (SELECT 1, '' FROM DUAL UNION ALL SELECT n + 1 ,even FROM nums LEFT JOIN (SELECT 'Even' even FROM DUAL) ON MOD (n + 1, 2) = 0 WHERE n < 6) SELECT * FROM nums;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
SQL_ID  3hg5v4avacn33, child number 0
-------------------------------------
 WITH nums (n, msg) AS (SELECT 1, '' FROM DUAL UNION ALL SELECT n + 1
,even FROM nums LEFT JOIN (SELECT 'Even' even FROM DUAL) ON MOD (n + 1,
2) = 0 WHERE n < 6) SELECT * FROM nums

(省略)
```

### 診断タスクを作成して実行

DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASKで診断タスクを作成します。診断の種類には、以下の５つがありますが、今回はクエリ結果異常なので、PROBLEM_TYPE_WRONG_RESULTSを使用します。

| 診断種類                       | 診断対象                 |
| ------------------------------ | ------------------------ |
| PROBLEM_TYPE_PERFORMANCE       | パフォーマンス問題       |
| PROBLEM_TYPE_WRONG_RESULTS     | クエリ結果の異常         |
| PROBLEM_TYPE_COMPILATION_ERROR | コンパイル時のクラッシュ |
| PROBLEM_TYPE_EXECUTION_ERROR   | 実行時のクラッシュ       |
| PROBLEM_TYPE_ALT_PLAN_GEN      | 代替実行計画の取得       |

SQL_IDパラメータ使用しています。クエリ文字列を使う場合はSQL_TEXTパラメータです。

```sql
SQL> VAR task_id VARCHAR2(50)

SQL> EXEC :task_id := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK (
                SQL_ID       => '3hg5v4avacn33',
                PROBLEM_TYPE => DBMS_SQLDIAG.PROBLEM_TYPE_WRONG_RESULTS,
                TASK_NAME    => 'recursive_wrong_result');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK (:task_id);

PL/SQL procedure successfully completed.
```

### 診断結果の取得とSQL PATCHの作成

診断が終了したら診断結果を取得します。以下の通り解決策が一つ見つかりました。

```sql
SQL> SET LINESIZE 200 PAGESIZE 0 LONG 200000

SQL> SELECT DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK (:task_id) FROM DUAL;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : recursive_wrong_result
Tuning Task Owner  : TESTUSER
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
(略)
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Patch Finding (see explain plans section below)
------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation
  --------------
  - Consider accepting the recommended SQL patch.
    execute dbms_sqldiag.accept_sql_patch(task_name =>
            'recursive_wrong_result', task_owner => 'TESTUSER', replace =>
            TRUE);
(略)
```

診断結果の解決策にそってDBMS_SQLDIAG.ACCEPT_SQL_PATCHを実行しSQL PATCHを作成しますが、上記には最低限のパラメータしか提示されていません。ここでは、SQL PATCH名、説明を追加し、さらにリテラル値が変更されてもSQL PATCHが働くようFORCE_MATCHを有効にします。

```sql
SQL> EXEC DBMS_SQLDIAG.ACCEPT_SQL_PATCH(
                TASK_NAME   => 'recursive_wrong_result',
                NAME        => 'recursive_bug_fix',
                DESCRIPTION => 'wrong result bug fix for recursive with outer join',
                TASK_OWNER  => 'TESTUSER',
                REPLACE     =>TRUE,
                FORCE_MATCH =>TRUE);
```

### SQL PATCHの確認とクエリの実行

SQL PATCHが作成されていることを確認して、問題のクエリを実行します。

```sql
SQL> SELECT NAME, STATUS, FORCE_MATCHING FROM DBA_SQL_PATCHES where name = 'recursive_bug_fix';

NAME                 STATUS   FOR
-------------------- -------- ---
recursive_bug_fix    ENABLED  YES


SQL> WITH nums (n, msg) AS (SELECT 1, '' FROM DUAL UNION ALL SELECT n + 1 ,even FROM nums LEFT JOIN (SELECT 'Even' even FROM DUAL) ON MOD (n + 1, 2) = 0 WHERE n < 6) SELECT * FROM nums;

         N MSG
---------- ----
         1
         2 Even
         3
         4 Even
         5
         6 Even
```

正しい結果を取得することができました！

さらにそのまま実行計画を表示して、NoteセクションでSQL PATCHが使用されていることを確認します。

```sql
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID  3hg5v4avacn33, child number 1
-------------------------------------
(略)

Note
-----
   - SQL patch "recursive_bug_fix" used for this statement
```

ついでにもう一つ確認です。SQL PATCHのFORCE_MATCHを有効にしたので、リテラル値を変えて実行してみます。以下、``'Even'``を``'Odd'``に、``MOD (n + 1, 2) = 0``を``MOD (n + 1, 2) = 1``に、さらに``n < 6``を``n < 7``に変更しましたが、正しく動いていますね。

```sql
SQL> WITH nums (n, msg) AS (SELECT 1, 'Odd' FROM DUAL UNION ALL SELECT n + 1 ,even FROM nums LEFT JOIN (SELECT 'Odd' even FROM DUAL) ON MOD (n + 1, 2) = 1 WHERE n < 7) SELECT * FROM nums;

         N MSG
---------- ---
         1 Odd
         2
         3 Odd
         4
         5 Odd
         6
         7 Odd

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
(略)
Note
-----
   - SQL patch "recursive_bug_fix" used for this statement
```

### SQL PATCHの内容確認

さて、ではSQL PATCHがどのようにバグを回避したのかみてみます。SQL PATCHは、SQL PROFILEの派生というか一種のようでSQL PROFILEと同じくOUTLINEヒントが``sys.sqlobj$data``に保存されています。COMP_DATAコラムには、データがXMLで格納されているためxmltableを使いヒント部分を抽出します。また対象行を特定するためにシグネチャが必要ですが、これはv$sqlにあるのでSQL_IDで取得します。FORCE_MATCHを有効にしている場合はFORCE_MATCHING_SIGNATURE、無効の場合はEXACT_MATCHING_SIGNATUREとなりますが、面倒なので両方一度に対象にしてます。

```sql
SELECT outline_hint
FROM   sys.sqlobj$data,
       XMLTABLE('//hint'
                PASSING XMLTYPE(comp_data)
                COLUMNS outline_hint VARCHAR2(100) path 'text()')
WHERE  signature IN (SELECT n
                     FROM   v$sql
                     UNPIVOT (n FOR s IN (FORCE_MATCHING_SIGNATURE,
                                          EXACT_MATCHING_SIGNATURE))
                     WHERE sql_id = '3hg5v4avacn33');

OUTLINE_HINT
-------------------------------------------------------------
NO_QUERY_TRANSFORMATION

```

ということで、なんとSQL PATCHの内容は単なる``NO_QUERY_TRANSFORMATION``ヒントでした。(^^)

では、実際に``NO_QUERY_TRANSFORMATION``ヒントで正しい答えが得られるが試してみましょう。まずは、SQL PATCHを削除してバグ状態に戻します。

```sql
SQL> EXEC DBMS_SQLDIAG.DROP_SQL_PATCH(name => 'recursive_bug_fix');

PL/SQL procedure successfully completed.
```

ヒントなしとヒントありで結果を比べてみます。

```sql
SQL> WITH nums (n, msg) AS (SELECT 1, '' FROM DUAL UNION ALL SELECT n + 1 ,even FROM nums LEFT JOIN (SELECT 'Even' even FROM DUAL) ON MOD (n + 1, 2) = 0 WHERE n < 6)
SELECT * FROM nums;

         N MSG
---------- ----
         1
         2 Even
         3 Even
         4 Even
         5 Even
         6 Even


SQL> WITH nums (n, msg) AS (SELECT 1, '' FROM DUAL UNION ALL SELECT n + 1 ,even FROM nums LEFT JOIN (SELECT 'Even' even FROM DUAL) ON MOD (n + 1, 2) = 0 WHERE n < 6)
SELECT /*+ NO_QUERY_TRANSFORMATION */ * FROM nums;

         N MSG
---------- ----
         1
         2 Even
         3
         4 Even
         5
         6 Even

```

``NO_QUERY_TRANSFORMATION``ヒントで正しい答えが得られることが確認できました。とどのつまり、オプティマイザのクエリ置換におけるバグ、ということになろうかと思います。しかし12.2からだすると結構息の長いバグですね。単純なクエリなんだけどなー。そのうちfixが出ることを期待しましょう。

### 診断タクスの削除

最後に診断タクスを削除してクリーンナップしておきます。

```sql
SQL> EXEC DBMS_SQLDIAG.DROP_DIAGNOSIS_TASK (task_name => 'recursive_wrong_result');

PL/SQL procedure successfully completed.
```



## おわりに

たまたまバグに遭遇してたまたまSQL診断で解決策が提示されたので記事にしてみました。まぁそうそう上手くいくとは思えないですけど、問題タイプもいくつか提示されている様ですし、なんか動きがおかしいときはとりあえず診断してみるのも悪くないのかもしれませんね。

以上です。