# mysql --version
mysql Ver 14.14 Distrib 5.5.42, for Linux (x86_64) using readline 5.1
(5.6.25でも再現を確認)
EXISTS 句のサブクエリ内で UNION 結合している場合、サブクエリの SELECT 句によっては、
EXISTS 句の動作をオプティマイザが最適化する所為でエラーになる場合がある。
エラーの発生
手元の実装では、A テーブルの削除したデータを A_del といった別テーブルに退避させている。
A テーブル
| id | name |
|---|---|
| 1 | hoge |
| 2 | huga |
| 4 | piyo |
A_del テーブル
| id | name | del_time |
|---|---|---|
| 3 | nya | 1454998143 |
こーいったテーブルを全てのデータを利用する形で UNION で結合する場合、お互いのカラム数を合わせなければエラーになる為、
> SELECT *,NULL as del_time FROM A UNION ALL SELECT * FROM A_del;
といった形で、NULL でカラム数を調整した SQL 文になる。
もし UNION した目的が、WHERE で一致する行が存在するかチェックしたいだけの場合、負荷の高い SQL 実行を避けて EXISTS 句を使う事で存在可否だけを確認する事ができる。
> SELECT EXISTS (SELECT *,NULL as del_time FROM A UNION ALL SELECT * FROM A_del);
しかしコレはエラーになる。
> SELECT EXISTS (SELECT * FROM A UNION ALL SELECT * FROM A_del);
サブクエリ内の SELECT 句を * で統一すると動作するが、逆にこのサブクエリを単体で実行した場合は、当然ながらカラム数が不一致な UNION 結合としてエラーが発生する。
何が起きているのか
タイトルの通り MySQL のオプティマイザが SELECT 句を書き換える為に発生している模様。
EXISTS 句は「サブクエリーが少なくとも 1 行を返す」かどうかを判定する為の構文である。
この時、返される値は TRUE か FALSE しか無い為、SELECT 句や ORDER BY 句の内容が意味を無さない。
その為、SELECT 句が * であった場合は、MySQL のオプティマイザは index が利用可能な値や、定数に書き換えてしまう。
> SELECT 1 FROM A_del;
しかし * 以外の値が設定されている場合は最適化が実行されない。
(集計関数や相関サブクエリがある場合の影響を考慮しているのかもしれない)
これは EXPAIN を指定して EXISTS 句を利用した SQL 文を調査すると分かりやすいが、
SELECT 句にていくつかのカラムを指定している場合、Extra カラムに「Using index」が表示されなくなる。
サブクエリ内の SELECT 句の内容によって、オプティマイザが最適化を諦めている証拠である。
(EXPLAIN EXTENDED してから SHOW WARNINGS すると、オプティマイザが最終的に実行した SQL 文が見れてより分かりやすい)
クエリを時前で記述している場合は、そもそも目的が EXISTS の時点で、サブクエリの SELECT 句を * で統一するなり、定数にしておけばいいのだが、
手製のクエリビルダーにて、全件データの参照に利用する UNION 結合のコードを元に 行の存在可否をチェックするメソッドを通したら、このような問題にあたった。
検索しても同様のケースについての記述が見付からなかったので、記事にしてみました。
オプティマイザの内部挙動についての調査
手元のクエリビルダの実装を書き換えて、 EXISTS が設定されている時のみ、UNION 内の SELECT 句を本来だと実行できない UNION に書き換えるような処理が必要となった。
ある意味でこの挙動はオプティマイザのバグを踏んだと言えるのだが、
最適化時に * でない方のクエリをオプティマイザ側が書き換えないのは、前述したように行数に影響を及ぼす事を考慮してのものなのか確認したくなった。
しかし、原語で記述されているマニュアルにあたっても そこまで細かいオプティマイザの意思決定については言及されていない為、ソースコードを当たった所、最新のコードで以下のような記述を発見した。
Change
SELECT expr1, expr2
to
SELECT 1,1
because EXISTS does not care about the selected expressions, only about
the existence of rows.
If UNION, we have to modify the SELECT list of each SELECT in the
UNION, fortunately this function is indeed called for each SELECT_LEX.
If this is a prepared statement, we must allow the next execution to use
materialization. So, we should back up the original SELECT list. If this
is a UNION, this means backing up the N original SELECT lists. To
avoid this constraint, we change the SELECT list only if this is not a
prepared statement.
mysql-server/item_subselect.cc at 03194df588b13e2f5c9350cf8c93d0c565f0ca06 · mysql/mysql-server
UNION の際の数を合わす為に SELECT 1 FROM A ではなくて、 SELECT 1,1,1,1,1,1,1 FROM A みたいにしてくれる模様。
つまり対策コードが入ったのかと思い、マージされたタイミングを確認して5.6系で試してみたのだが解決せず…
どうやらこの処理は、IN を内部的に EXISTS に書き換える場合の最終処理を行うコードのようで、
単純な EXISTS のサブクエリ変換の際には考慮してくれない模様…
うーん。
EXISTS 絡みのコードの大半がこの IN2EXISTS 絡みのもののようで、さくっとソースから シンプルな EXISTS の最適化コードが見付けられなかったので
そのうち、腰据えて調べてみるか…
参考
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.10.6 EXISTS または NOT EXISTS を使用したサブクエリー
SQLを速くするぞ―お手軽パフォーマンス・チューニング
検索用
ERROR 1222 (21000): The used SELECT statements have a different number of columns