LIKE演算子の謎

こんにちは、naginoです。

とりあえず SQL Server 2005 SP2（9.0.3068） で確認しました。

原因が理解できていないので、とりあえずです。

SP3 が出ているので、SP3 でどうなるかは近いうちに確認する予定です。

元ネタはこちら。

http://forums.microsoft.com/msdn-ja/ShowPost.aspx?PostID=3837833&SiteID=7

色々な情報が錯綜していますが、インデックスが張られた nchar 型の列で、LIKE 演算が怪しい動きをするということです。

下準備のクエリは以下です。

CREATE TABLE T(P CHAR(3) COLLATE Japanese_CI_AS)
CREATE INDEX I ON T(P)
INSERT T VALUES(N'2/7')
INSERT T VALUES(N'207')
INSERT T VALUES(N'217')

テーブル T が作成されます。

そのテーブルに対して以下のクエリを実行すると、妙なことが起こります。

SELECT * FROM T WHERE convert(NCHAR(3), P) like N'20_' COLLATE Japanese_CI_AS
⇒結果が0件！

一方インデックスを削除すると、ちゃんと 1件帰ってきます。

DROP INDEX I on T
SELECT * FROM T WHERE convert(NCHAR(3), P) like N'20_' COLLATE Japanese_CI_AS

さて、どういうことが起きているか、内部に少し立ち入ってみます。

実行プランを見比べてみるとわかるのですが、インデックスが張られている場合はなにやら妙な処理が多数入っています。

どうもこれが原因のようです。

おそらくですが、インデックスを使おうとして、検索範囲の上限値と下限値をまず計算しているようなのですが、これが妙なのです。

SET SHOWPLAN_TEXT ON

を実行して、さらにもぐってみると、以下のようになります。

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1011], [Expr1012], [Expr1013]))
|--Merge Interval
| |--Concatenation
| |--Compute Scalar(DEFINE:(([Expr1006],[Expr1007],[Expr1005])=GetRangeThroughConvert(N'20',NULL,(22))))
| | |--Constant Scan
| |--Compute Scalar(DEFINE:(([Expr1009],[Expr1010],[Expr1008])=GetRangeThroughConvert(NULL,N'2¼',(10))))
| |--Constant Scan
|--Index Seek(OBJECT:([test].[dbo].[T].[I]), SEEK:([test].[dbo].[T].[P] > [Expr1011] AND [test].[dbo].[T].[P] < [Expr1012]), WHERE:(CONVERT(nchar(3),[test].[dbo].[T].[P],0) like N'20_') ORDERED FORWARD)

「¼」が見えます。

ちなみに以下のクエリだと、次のような結果になります。

SELECT * FROM T WHERE convert(NCHAR(3), P) like N'21_' COLLATE Japanese_CI_AS

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1011], [Expr1012], [Expr1013]))
|--Merge Interval
| |--Concatenation
| |--Compute Scalar(DEFINE:(([Expr1006],[Expr1007],[Expr1005])=GetRangeThroughConvert(N'21',NULL,(22))))
| | |--Constant Scan
| |--Compute Scalar(DEFINE:(([Expr1009],[Expr1010],[Expr1008])=GetRangeThroughConvert(NULL,N'22',(10))))
| |--Constant Scan
|--Index Seek(OBJECT:([test].[dbo].[T].[I]), SEEK:([test].[dbo].[T].[P] > [Expr1011] AND [test].[dbo].[T].[P] < [Expr1012]), WHERE:(CONVERT(nchar(3),[test].[dbo].[T].[P],0) like N'21_') ORDERED FORWARD)

どうやら、検索範囲の下限値を求めているようです。

でも「¼」とは違和感があります。

どういうことかというと、以下のクエリの実行結果が false になるのです。

select case when N'¼' < N'0' COLLATE Japanese_CI_AS then 'true' else 'false' end

ということは、実行プランで下限値を求めているはずが、'0' より大きいので、結果 0件になるようです。

・・・謎です。

ちなみに、以下のクエリは true になります。

select case when '¼' < '0' COLLATE Japanese_CI_AS then 'true' else 'false' end

・・・Unicode の処理が怪しい気がするのですが・・・。

ちなみに以下のクエリの実行結果は、以下のとおり。

select
convert(varbinary, N'1'),
convert(varbinary, '1'),
convert(varbinary, N'0'),
convert(varbinary, '0'),
convert(varbinary, N'¼'),
convert(varbinary, '¼'),
convert(varbinary, N'/'),
convert(varbinary, '/')

⇒0x3100 0x31 0x3000 0x30 0xBC00 0x3F 0x2F00 0x2F

うーん、「¼」が「/」になるのが正しそうなのですが・・・これ以上は 1ユーザに過ぎない私には手が出ないところです。

いずれにせよ、LIKE演算で末尾にワイルドカードを使った範囲検索で、且つ最後の文字が半角数字の「0」の場合、照合順序によっては謎な動きをすることがあるようです。

ただ、以下のクエリの場合は期待通りに動作します。

SELECT * FROM T WHERE convert(NCHAR(3), P) like N'%20_' COLLATE Japanese_CI_AS

実行プランを見るとわかりますし、クエリを見てもわかるように、上限下限が決められない検索ですので、今回のような問題は起きないわけです。

部分一致でシステムを作っている限り目にしないかもしれませんが、なんとなく気持ち悪いですね。

識者にご教授いただきたいところです。

で、部分一致だと上記のような、内部的に範囲検索に置き換える最適化が行えないため、インデックスが使えず、速度の問題が出てくるわけです。

その際に、さて何か対策が無いかという話がありまして、それはまた別の機会に調査する予定です。

まあ、SP3 での上記現象の検証が先になるとは思います。