トップ回答者
ISNUMERIC と型変換エラー

質問
-
下記の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
回答
-
分かりやすく解説頂けないでしょうか。
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以上の値を探すのは2段階の処理を含むことになり、効率が悪くなります。
そこでオプティマイザーはインラインビューを作る条件と1600以上の条件で同時に探そうとするのでしょう。
その際に、ご提示されたページと同じことが起きるのです。★良い回答には質問者は回答済みマークを、閲覧者は投票を!
- 回答としてマーク candywhite0327 2018年3月2日 9:36
すべての返信
-
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;
とかですか? -
分かりやすく解説頂けないでしょうか。
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以上の値を探すのは2段階の処理を含むことになり、効率が悪くなります。
そこでオプティマイザーはインラインビューを作る条件と1600以上の条件で同時に探そうとするのでしょう。
その際に、ご提示されたページと同じことが起きるのです。★良い回答には質問者は回答済みマークを、閲覧者は投票を!
- 回答としてマーク candywhite0327 2018年3月2日 9:36
-
SQLは仕様書です。データベースの動作を記述する言語ではないのです。SQLという仕様書を渡されたデータベースがどのようにデータを持ってくるかはデータベースに任されています。
例えば、新宿駅から東京駅に荷物を届けて欲しいというのが仕様書であり、それをどのように実現するかはその仕様書を受け取った配送業者に任されているようなものです。配送業者さんにいちいちどのような交通手段で届けてと指示しませんよね。最適な配送手段を選ぶことは、データベースのオプティマイザーに相当します。
一般的に人は、データベースからどのような手順(実行プラン)でデータを取ってくるのかが高速であるかを判断し、それを指示することはできません。つまり、人間が良い実行プランを作成し、それを指示するのは難しいことです。それもあって、SQLは制御言語ではなく、仕様書なのです。
なお、オプティマイザ―に我々は全く手が出せないわけではなく、ヒント等を使うことによってオプティマイザーを制御することができます。今回の場合、外側で絞って内側で絞りたいのはわかりますし、そのように動作してくれないことに違和感を覚えられるのは最もだと思います。しかし、オプティマイザーは外側で絞ってから内側で絞るよりも、同時にこれら2つの条件で絞った方が早いと判断し、このような動作をします。特にインデックスがない場合、テーブルを順に操作することになりますから、2つの条件で絞った方が1回の走査で済み、早いことは明らかです。
この辺りは慣れるしかないのですが、特にSQLはC#やVBのように動作を指示する言語ではなく、データベースにお願いしたいことを伝えるための言語であるという感覚が必要です。つまり、SQLという言語で欲しいデータをデータベースにお願いしているのです。
さて、私ならどのようなSQLを書くかですが、今、SQL Serverを扱える環境にいませんので宿題とさせていただきますが、サブクエリを使わなくても2つの条件を同時に指定して抽出できるんじゃないかなぁと思ってはいます。
最終的にはSQLから生成される実行プランが全てですので、それが良くなるようにSQLの書き方を変えたり、ヒントなどを使うことになります。インデックスがあるかないかでも大きく違いますので、必要に応じてインデックスを検討することも重要です。(追記)
candywhite0327さんが書かれたSQLをオプティマイザーが見て、「どのようなデータが欲しいかはわかった。じゃあ、できるだけ高速に取ってくるように実行プランを作るよ」って言って、作成した実行プランを実行します。この実行プランはある意味、candywhite0327さんのSQLを添削してより良いSQLを生成したという見方もできます。
実際、私がSQLを作るにしてもそのようなSQLを作ることになるでしょうから、基本的にはオプティマイザーの実行プランがもう答えを出しているようなものです。順次走査は時間がかかりますから、できるだけそこを無くす意識が必要です。
★良い回答には質問者は回答済みマークを、閲覧者は投票を!
- 編集済み trapemiya 2018年3月2日 11:44 追記
-
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が使えて簡単なんですけどね。
-
佐祐理さん、ありががとうございます。
まず、私も経験とcandywhite0327さんが提示されたリンク先のページの内容から、strによるNMSYO3の型が文字列型に設定されるのが理由と思い込んでいました。確かに何かの文献で裏を取っているわけではないので、書き方に問題がありました。お詫びさせていただきます。ところで、佐祐理さんは、
>SQL Serverが「STR関数の処理は重いのでこの部分の評価は後回しにしよう」と判断したからであり、全くの偶然です。
と書かれていますが、これについては何か文献がありますでしょうか? 後回しにしたからうまく行くというのは大変説得力のある説明だと思いました。
★良い回答には質問者は回答済みマークを、閲覧者は投票を!
- 編集済み trapemiya 2018年3月5日 2:09 一部訂正
-
ところで、佐祐理さんは、
>SQL Serverが「STR関数の処理は重いのでこの部分の評価は後回しにしよう」と判断したからであり、全くの偶然です。
と書かれていますが、これについては何か文献がありますでしょうか? 後回しにしたからうまく行くというのは大変説得力のある説明だと思いました。
-
佐祐理さん、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より大きいもの)