none
SQL Server 2005の既定の照合順序によるlike句検索の不具合 RRS feed

  • 質問

  • まず適当に作ったDBに以下の文を実行します。

    Code Snippet

    BEGIN TRAN
    --SQL Server 2005では既定でJapanese_CI_ASらしい
    --Japanese_BINだと動作する
    --VARCHARでもいい。確認した限りでは3文字以上
    CREATE TABLE T(P CHAR(3)COLLATE Japanese_CI_AS)
    CREATE INDEX I ON T(P)--重複有り
    --確認した限りでは文字目は'0'か'9'で1,3文字目は何でもいい。
    --重複有り索引に同一値を行入れる]
    INSERT T VALUES('207')
    INSERT T VALUES('207')
    --確認した限りではNVARCHAR,NCHARどちらでもよい。3文字以上
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3))like'20%'--COLLATE Japanese_BIN--をつければ動作する
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3))like'207'
    ROLLBACK


    最初のSELECT文は0を返し、2つ目のSELECT文は2を返します。
    これは文字の照合順序が影響していることがわかりました。
    しかし大抵日本語の「ー」や「゛」の問題であって半角数字でこのような現象が出るとは思いませんでした。

    この現象は照合順序の使用とは思えません。バグでしょうか?
    2008年9月4日 2:55

回答

  • こんにちは、naginoです。

     

    このケースは、データが重複しているのは関係なく、「GetRangeThroughConvert」に不具合があることが原因のようです。

    期待通りのデータが帰ってこないクエリでは、「SET SHOWPLAN_TEXT ON」を使用するとわかるように、「GetRangeThroughConvert」が使用されています。

     

    この件は、以下にあるように SQL Server 2005 の SP3 で修正されています。

    http://support.microsoft.com/kb/959019/ja

     

    SP3 を適用した SQL Server 2005(9.0.4035) 環境上で、実際に以下のように解消していることを確認しました。

    Code Snippet

    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2%' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2__' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20_' COLLATE Japanese_CI_AS --2 ←解消
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20%' COLLATE Japanese_CI_AS --2 ←解消
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '207' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '2%' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '2__' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '20_' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '20%' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '207' COLLATE Japanese_CI_AS --2

     

    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2%' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2__' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20_' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20%' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '207' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '2%' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '2__' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '20_' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '20%' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '207' COLLATE Japanese_90_CI_AS --2

     

    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2__' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20_' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '207' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '2%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '2__' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '20_' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '20%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '207' COLLATE Japanese_BIN --2

     

    以上、先ずはご参考まで。

    • 回答としてマーク sk7474 2009年2月5日 2:20
    2009年1月24日 13:51

すべての返信

  • こんにちは、naginoです。

     

    興味深かったので、手元の環境で確認してみました。

     

    SQL Server 2005 x86(9.00.2047.00 SP1 Standard Edition) で確認したところ、同様でした。

    Code Snippet

    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2%' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2__' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20_' COLLATE Japanese_CI_AS --0
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20%' COLLATE Japanese_CI_AS --0
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '207' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '2%' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '2__' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '20_' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '20%' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '207' COLLATE Japanese_CI_AS --2

     

    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2%' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2__' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20_' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20%' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '207' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '2%' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '2__' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '20_' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '20%' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '207' COLLATE Japanese_90_CI_AS --2

     

    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2__' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20_' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '207' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '2%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '2__' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '20_' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '20%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '207' COLLATE Japanese_BIN --2

    インデックスを削除すると、いずれも2と返ってきました。

     

    SQL Server 2000 x86(8.00.2050 SP4 Standard Edition)でも試してみましたが、常に2と返ってきました。

    Code Snippet

    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2%' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2__' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20_' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20%' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '207' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '2%' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '2__' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '20_' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '20%' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '207' COLLATE Japanese_CI_AS --2

     

    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2__' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20_' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '207' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '2%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '2__' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '20_' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '20%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '207' COLLATE Japanese_BIN --2

    インデックスを削除しても、いずれも2と返ってきました。

     

    少なくとも 2005 と 2000 で挙動が異なるようです。

    2008年9月4日 3:35
  • naginoさん、SQL Server2000での検証ありがとうございました。

    どうもこれはバグっぽい気がします。

    SQLでバージョンを確認しました。

    SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY('edition')
    9.00.3175.00    SP2    Standard Edition



    2008年9月5日 5:10
  • こんにちは、naginoです。

     

    バージョンを記載忘れていました。

     

    先に記載した環境は、以下のとおりです。

    先の記述にも追記しておきます。

     ● SQL Server 2005 ⇒ 9.00.2047.00 SP1 Standard Edition

     ● SQL Server 2000 ⇒ 8.00.2050 SP4 Standard Edition

    # 今手元にある開発環境は、2005側は SP2 が未適用なのです・・・。

     

    半角数字であるのに挙動が異なるというのは、理解に苦しむところですね。

    2008年9月5日 5:33
  • こんにちは、naginoです。

     

    このケースは、データが重複しているのは関係なく、「GetRangeThroughConvert」に不具合があることが原因のようです。

    期待通りのデータが帰ってこないクエリでは、「SET SHOWPLAN_TEXT ON」を使用するとわかるように、「GetRangeThroughConvert」が使用されています。

     

    この件は、以下にあるように SQL Server 2005 の SP3 で修正されています。

    http://support.microsoft.com/kb/959019/ja

     

    SP3 を適用した SQL Server 2005(9.0.4035) 環境上で、実際に以下のように解消していることを確認しました。

    Code Snippet

    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2%' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2__' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20_' COLLATE Japanese_CI_AS --2 ←解消
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20%' COLLATE Japanese_CI_AS --2 ←解消
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '207' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '2%' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '2__' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '20_' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '20%' COLLATE Japanese_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '207' COLLATE Japanese_CI_AS --2

     

    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2%' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2__' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20_' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20%' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '207' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '2%' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '2__' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '20_' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '20%' COLLATE Japanese_90_CI_AS --2
    SELECT COUNT(*)FROM T WHERE P like '207' COLLATE Japanese_90_CI_AS --2

     

    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '2__' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20_' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '20%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE CAST(P AS NCHAR(3)) like '207' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '2%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '2__' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '20_' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '20%' COLLATE Japanese_BIN --2
    SELECT COUNT(*)FROM T WHERE P like '207' COLLATE Japanese_BIN --2

     

    以上、先ずはご参考まで。

    • 回答としてマーク sk7474 2009年2月5日 2:20
    2009年1月24日 13:51
  • こんにちは。中川俊輔 です。

    naginoさん、検証までしていただき、ありがとうございます。

    和和和さん、フォーラムのご利用ありがとうございます。
    少々昔のスレッドですが、有用な情報を有効活用するため、naginoさんの回答へ回答済みチェックをつけさせていただきました。

    今後ともフォーラムをよろしくお願いします。
    それでは!


    マイクロソフト株式会社 フォーラム オペレータ 中川 俊輔
    2009年2月5日 2:24