ISNUMERIC と型変換エラー

Question

下記のSQLが型変換エラーで通りません。

field1はnvarcharで、数値だけの値もあれば、文字列もあります。

field1が1600以上を抽出したいのですが、「データ型 int に変換できませんでした。」となってしまいます。

原因が分かりませんので、教えていただけ無いでしょうか。(SQLServer2008)

SELECT * 
FROM (
         SELECT cast(field1 as int) as NMSYO3 
         FROM table1
         where ISNUMERIC(field1) = 1 
) as query1
where field1 > 1600

Answer 1

cast(field1 as int) を　str(cast(field1 as int)) とすると上手くいきました。

str()としているのにintとして抽出されています。

下記のサイトで解説されているようですが、理論が理解できません。

分かりやすく解説頂けないでしょうか。 

http://www.hits-net.com/blog/?p=585

Answer 2

where field1 > 1600 はおかしいかと。

SELECT NMSYO3
FROM (
  SELECT (CASE ISNUMERIC(field1) WHEN 1 THEN CAST(field1 AS int) ELSE 0 END) AS NMSYO3
  FROM table1
)
WHERE NMSYO3 > 1600;

とか

SELECT (CASE ISNUMERIC(field1) WHEN 1 THEN CAST(field1 AS int) ELSE 0 END) AS NMSYO3
FROM table1
WHERE NMSYO3 > 1600;

とかですか？

Answer 3

御回答ありがとう御座います。

ご指摘の通り、

where field1 > 1600　

ではなく　

where NMSYO3 > 1600

でした。

下記SQLでやってみましたが、ダメでした。

SELECT *
FROM (
  SELECT (CASE ISNUMERIC(field1) WHEN 1 THEN CAST(field1 AS int) ELSE 0 END) AS NMSYO3
  FROM table1
) as ddd
WHERE NMSYO3 > 1600;

Answer 4

実際どのような文字列が対象で、どの文字列でエラーになっているか確認できないでしょうか？

ちなみにISNUMERICはかなり変な文字列に対しても 1 を返すそうです。もし整数のみを想定しているのであればいっそ WHERE field1 NOT LIKE '[^0-9]' （0-9以外を含まない、つまり0-9のみで構成される）とかの方が確実かもしれません。

Answer 5

分かりやすく解説頂けないでしょうか。 

http://www.hits-net.com/blog/?p=585

Result列は元々のテーブルにはありません。全ての列には型がありますので、この列の型を決める必要があります。
最初に読み込む値が数値に変換できる場合、Result列の型は数値型に設定されます。そうでない場合は文字列型に設定されます。
この場合、最初に読み込んだ値が数値だったのでResult列の型は数値型になります。
その後に文字列である例えばAがやってくると、それをそのままResult列の値として返そうとしますが、Result列は数値型に決定されているので、Aを数値型に変更できず、エラーとなるのです。

これを避けるために、Result列はどのような値が来ても文字列型になるようにstrを使っています。文字列型であればAを返すことができます。

さて、candywhite0327さんのSQLでも同様なことが起きます。一見するとquery1というインラインビューの中には数値しかなく、エラーが起きるのはおかしいように思えます。実際、インラインビューには数値しかありません。
しかし、where field1 > 1600 （where NMSYO3 > 1600 の誤りですね）
が付加されると状況が変わります。
インラインビューを作ってから1600以上の値を探すのは２段階の処理を含むことになり、効率が悪くなります。
そこでオプティマイザーはインラインビューを作る条件と1600以上の条件で同時に探そうとするのでしょう。
その際に、ご提示されたページと同じことが起きるのです。

---

★良い回答には質問者は回答済みマークを、閲覧者は投票を！

Answer 6

trapemiya様

詳しく御回答頂き、ありがとう御座います。

内側と外側の両方のWHEREで同時に絞り込んでいるということでしょうか。

わざわざ()で内側のクエリを作ったのは、内側の結果に対して外側で絞り込みたいからです。

このような仕様では、サブクエリの意味を成さないように思うのですが、どう納得したらよいでしょうか。

もしよろしければですが、trapemiya様はこの場合、どのようなSQLをお書きになられますか。

宜しくお願い致します。

Answer 7

SQLは仕様書です。データベースの動作を記述する言語ではないのです。SQLという仕様書を渡されたデータベースがどのようにデータを持ってくるかはデータベースに任されています。
例えば、新宿駅から東京駅に荷物を届けて欲しいというのが仕様書であり、それをどのように実現するかはその仕様書を受け取った配送業者に任されているようなものです。配送業者さんにいちいちどのような交通手段で届けてと指示しませんよね。最適な配送手段を選ぶことは、データベースのオプティマイザーに相当します。
一般的に人は、データベースからどのような手順（実行プラン）でデータを取ってくるのかが高速であるかを判断し、それを指示することはできません。つまり、人間が良い実行プランを作成し、それを指示するのは難しいことです。それもあって、SQLは制御言語ではなく、仕様書なのです。
なお、オプティマイザ―に我々は全く手が出せないわけではなく、ヒント等を使うことによってオプティマイザーを制御することができます。

今回の場合、外側で絞って内側で絞りたいのはわかりますし、そのように動作してくれないことに違和感を覚えられるのは最もだと思います。しかし、オプティマイザーは外側で絞ってから内側で絞るよりも、同時にこれら２つの条件で絞った方が早いと判断し、このような動作をします。特にインデックスがない場合、テーブルを順に操作することになりますから、２つの条件で絞った方が１回の走査で済み、早いことは明らかです。

この辺りは慣れるしかないのですが、特にSQLはC#やVBのように動作を指示する言語ではなく、データベースにお願いしたいことを伝えるための言語であるという感覚が必要です。つまり、SQLという言語で欲しいデータをデータベースにお願いしているのです。

さて、私ならどのようなSQLを書くかですが、今、SQL Serverを扱える環境にいませんので宿題とさせていただきますが、サブクエリを使わなくても２つの条件を同時に指定して抽出できるんじゃないかなぁと思ってはいます。
最終的にはSQLから生成される実行プランが全てですので、それが良くなるようにSQLの書き方を変えたり、ヒントなどを使うことになります。インデックスがあるかないかでも大きく違いますので、必要に応じてインデックスを検討することも重要です。

（追記）
candywhite0327さんが書かれたSQLをオプティマイザーが見て、「どのようなデータが欲しいかはわかった。じゃあ、できるだけ高速に取ってくるように実行プランを作るよ」って言って、作成した実行プランを実行します。この実行プランはある意味、candywhite0327さんのSQLを添削してより良いSQLを生成したという見方もできます。
実際、私がSQLを作るにしてもそのようなSQLを作ることになるでしょうから、基本的にはオプティマイザーの実行プランがもう答えを出しているようなものです。順次走査は時間がかかりますから、できるだけそこを無くす意識が必要です。

---

★良い回答には質問者は回答済みマークを、閲覧者は投票を！

Answer 8

trapemiyaさんの見解も気になったので調べ直しました。

1. 質問のクエリでエラーが出る理由

SQLの言語仕様では式の評価順序は規定されておらず実装依存とされています。つまりSQL Serverなどの各実装者は高速化のためなら何をしても良いそうです。（ちょっと文脈が異なりますが、WHERE句内での評価順序、それによりどのようなエラーが出てもいいことについて言及されているようです。）

2. STR関数を追加することでエラーを回避できる理由

前述の通り、式の評価順序は自由です。STR関数を追加することでエラーを回避できたのはSQL Serverが「STR関数の処理は重いのでこの部分の評価は後回しにしよう」と判断したからであり、全くの偶然です。より重たいと判断される処理があればSTR関数の方が先に評価され、再びエラーが発生する可能性は十分にあります。しかもクエリを変更していなくても、対象となるテーブルの統計情報などによりいつでも評価順序が変更されてしまう危険性をはらんでいます。

3. ではどうするのがよいのか？

一般的な解決策はありません。今回のクエリに関して言えば、CASE句のWHEN句が使えます。WHEN句だけは

In the order specified, evaluates input_expression = when_expression for each WHEN clause.
Evaluates, in the order specified, Boolean_expression for each WHEN clause.

とあり、記述した順に評価されます。もちろんTHEN句の評価順序は関係ないことに気を付けなければなりません。

SELECT field1 AS NMSYO3
FROM table1
WHERE
  CASE
  WHEN ISNUMERIC(field1) <> 1 THEN 0
  WHEN CAST(field1 AS int) > 1600 THEN 1
  ELSE 0
  END = 1

でどうでしょうか？ なお、既に指摘しているようにISNUMERICが数値とみなす文字列とCASTがintに変換可能な文字列とには乖離がある点にも気をつけてください。

P.S. SQL Server 2012以降であればTRY_CASTが使えて簡単なんですけどね。

Answer 9

佐祐理さん、ありががとうございます。
まず、私も経験とcandywhite0327さんが提示されたリンク先のページの内容から、strによるNMSYO3の型が文字列型に設定されるのが理由と思い込んでいました。確かに何かの文献で裏を取っているわけではないので、書き方に問題がありました。お詫びさせていただきます。

ところで、佐祐理さんは、

＞SQL Serverが「STR関数の処理は重いのでこの部分の評価は後回しにしよう」と判断したからであり、全くの偶然です。

と書かれていますが、これについては何か文献がありますでしょうか？　後回しにしたからうまく行くというのは大変説得力のある説明だと思いました。

---

★良い回答には質問者は回答済みマークを、閲覧者は投票を！

Answer 10

ところで、佐祐理さんは、

＞SQL Serverが「STR関数の処理は重いのでこの部分の評価は後回しにしよう」と判断したからであり、全くの偶然です。

と書かれていますが、これについては何か文献がありますでしょうか？　後回しにしたからうまく行くというのは大変説得力のある説明だと思いました。

根拠となる文献は特に見つけられませんでした。ただ、STR関数による変換コストが生じる点、STR関数の戻り値はvarchar（nvarcharではありません）であり可変長文字を扱う必要が出る点、その後の 1600 との大小比較においてデータ型の優先順位により再度varchar → intの変換が発生する点を考慮すると、「この式が重い」と判断される可能性はそれなりにあるのかな、と考えました。

Answer 11

なるほど。了解いたしました。私も佐祐理さんがおっしゃるのが正しいように思えます。
いずれにしても、偶然性が完全に否定できないようなSQLを書くべきではなく、ちゃんと裏打ちされたSQLを書くべきだということを肝に銘じたいと思います。そうしないと、ある日当然SQLが動かなくなるということがあり得るわけですから。
ありがとうございました。大変勉強になり、かつ参考になりました。

---

★良い回答には質問者は回答済みマークを、閲覧者は投票を！

Answer 12

佐祐理さん、trapemiyaさん

ご解説ありがとう御座います。

大変興味深い内容であり、SQLは仕様書であり、データベースの動作を記述する言語ではない。
ということについて、ご理解させて頂きました。

field1についてですが、nvarchar(42)型のNULL許容です。
しかし実際にNULL値となるレコードはありません。

データの内容としては、''(空文字)、文字列、数値がそれぞれ複数あります。

佐祐理さんがご提案くださった、以下のSQLを実行したところ、

SELECT field1 AS NMSYO3
FROM table1
WHERE
  CASE
  WHEN ISNUMERIC(field1) <> 1 THEN 0
  WHEN CAST(field1 AS int) > 1600 THEN 1
  ELSE 0
  END = 1

結果はエラーで正常にクエリを完了することができません。

・エラー内容
nvarchar の値 '　　　　　　　　　　　　　　　　　　　　' をデータ型 int に変換できませんでした。

最終的に、目的の結果にたどり着くSQLをご教授いただく事は可能でしょうか。

(field3が数値であり　かつ　1600より大きいもの)

Answer 13

> 実際どのような文字列が対象で、どの文字列でエラーになっているか確認できないでしょうか？

> ちなみにISNUMERICはかなり変な文字列に対しても 1 を返すそうです。

> なお、既に指摘しているようにISNUMERICが数値とみなす文字列とCASTがintに変換可能な文字列とには乖離がある点にも気をつけてください。

と既に回答済みですが？