同じデータベースから同じクエリを叩いているのに違う値が返ってくる

Question

PyCharmのデータベース接続機能を利用し、mySQLデータベースにselect *でviewにアクセスしてデータ抽出しています。

このとき、返り値として1000程度にしかならないデータなのに、以下の例のように、もの凄く大きい値が返ってくることがあります。「返ってくることがある」と記載しているのは、同じデータベース、同じクエリーで5回中1回くらいしか発生せず、それ以外は正しい値（1000程度）で返ってきます。当然、クエリーを何回も叩く間にデータ変更をしていません（データはオフライン）。また、例1のようなものすごく大きい値ではなく、正常の値より10倍くらい大きい値で返ってくることもあります。また、発生する列は同じとは限りませんし、異常値が発生した列の中でも、正しく値を返しているデータもあります。

viewで割り算をしている関係上、0に近い値で割ることになっているのでは？と想像できるのですが、いつも起こるわけではなく、再現するデータも作成できず、原因追求に困っています。正解は難しくても、原因追求方法をご教示いただけたら大変助かります。

返ってくる値の例1
3150913154377232707909253057910314340545916254791736874265438475684899423004638913847887802006787813052873194876129411367095039723605185673167738120216770510848.00000

返ってくる値の例2
15000

正常の値
1000

create view view_name as
select 番号,
       sum(分子量 * 添加割合) / sum(添加割合) as 分子量指標,
       sum(有機 * 添加割合) / sum(添加割合)　as 有機指標,
       sum(無機 * 添加割合) / sum(添加割合) as 無機指標
from database as pr
         left join database2 as mt using (名称)
group by 番号;

select * from view_name;

Answer 1

計算の元になっているカラムが厳密値数値型(DECIMAL)でないなら、CASTして計算してみてはどうでしょうか。
12.20.5 高精度計算の例

Comments

[MagMag]

いつもありがとうございます。float型をdecimalに変換することで回避されたようです！

[68user]

レコードの取得順の違いで浮動小数点演算の誤差が蓄積してってことなんですかね。sum(分子量 * 添加割合) / sum(添加割合) というあまり誤差がでなさそうな形に見えるのですが。sum(分子量 * 添加割合) や sum(添加割合) も 5回に1回は変な値が出てくるんでしょうか?

[sazi]

計算式が問題ではなく、扱う型によって誤差が生じるようですね。

[MagMag]

ありがとうございます。もう少し値を追ってみました。

sum(分子量 * 添加割合) ：例1も例2も問題が再現
sum(分子量)：例2のみ再現ぽい
　　※なぜか、全体で例1がほとんど発生しなくなったので、いずれ例1も出るかもしれない
sum(添加割合)：例1も例2の問題も発生しない

そのため、分子量の問題なのかと感じました。分子量と添加割合の違いと言えば、分子量にはNullがあることです(型は共にfloat)。Nullが入ると集計値が不安定になるということはあるのでしょうか？（そもそも使い方の誤り？）。

[68user]

別回答として記載させていただきました。

Answer 2

ベストアンサーのコメント欄のつづきです。コードを含むので別回答として投稿しました。

わたしの知っている浮動小数点誤差って、下記 Python コードのような感じで多少のズレが出てくるようなものなんですが、どうやったら 1000 と 3150913154377232707909253057910314340545916254791736874265438475684899423004638913847887802006787813052873194876129411367095039723605185673167738120216770510848 などという差が出るんだろう、と不思議に思いました。

すごく小さい数で割っているので、答えが大きくなったんですかね?

ちなみに order by 添加割合,分子量 で順序を固定とかすると、常に同じ値が出るんでしょうか。

なお、正解としては decimal を使うということに何ら異存ございません。単に気になっただけです。

#!/usr/bin/env python3

bignum=100000000000000000000.0
smallnum=100.0
loop=1000000

#-------------------------------------
# 最初に大きな数値を sum に入れ、その後小さな数を加算する
# 結果: sum=1e+20
sum=bignum
for i in range(loop):
  sum+=smallnum

print("sum="+str(sum))

#-------------------------------------
# 最初に小さな数を加算し、最後に sum を加算する
# 結果: sum=1.000000000001e+20
sum=0.0
for i in range(loop):
  sum+=smallnum
sum+=bignum
print("sum="+str(sum))

Comments

[MagMag]

ありがとうございます。自分も最初、割り算のせいかな、と思っていました。ただ、以下のコメントのように、sum(分子量 * 添加割合)で発生したので、必ずしも割り算のせいではないのでは？と思っています。

order by 分子量指標 にした場合は問題再現しました。添加割合でorder byする場合、どこに入れたらいいでしょうか？なお、間違えてgroup by 処方番号 descと最後にdescをつけたら問題が発生しなくなりました。

ちなみに、Ver 8.0.19です。

[68user]

ご質問の回答になっていなくて申し訳ないですが、確認観点は下記でどうでしょう。

view を使わず再現するか。再現するなら view は関係なし。

特定の番号のみで結果に違いが出るか。もしそうなら、
　select sum(分子量 * 添加割合), sum(添加割合) from ... where 番号=xxx left join ... (group by なし)
で値のばらつきが再現するか。

また、select max(分子量), min(分子量), max(添加割合), min(添加割合) で桁数が大きく異なる数値が入っているか。

[MagMag]

ありがとうございます。自分も気になるので確認しました。

・viewを使わないで再現するか：再現しない。実はdatabase2もviewですが、view(database2)から分子量を発行しても、そのviewの元のデータベースからクエリーを何度も発行しても再現しませんでした。
・特定の番号のみで結果に違いが出るか：特定番号で出ているようです
・select sum(分子量 * 添加割合), sum(添加割合) from ... where 番号=xxx left join ... (group by なし)
で値のばらつきが再現するか：whereで絞った場合は再現せず。
・select max(分子量), min(分子量), max(添加割合), min(添加割合) で桁数が大きく異なる数値が入っているか：特にありません。値の範囲としては0.1〜1000くらいであり、発散するような値ではありませんでした。

group byの時のみ再現するのもおかしいですね。ちなみに、分子量、添加割合は共にfloatです。

[68user]

謎ですね～。show warnings; でなにか出てたりしませんかね。

[MagMag]

変な値が出た直後にshow warnings;を叩いても何も返ってこないです、、、。