« 標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです | トップページ | 標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派 »

2020年12月 4日 (金)

標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の4日目です。

モルダー、あなた疲れてるのよ! 的なタイトルになってしまったw

エラーにはならないけど、この手の差異もなかなか嫌らしい差異ですよね。

結論から言うと、この差異の影響を避けるためにはデフォルトの挙動任せは危険かな。と言うことになるのですが、まあ、色々ありますよね。長いこと同じエンジン使って他ところへ乗り換えるとなると。

Oracle Database

なお、SQL*plusのnumformatパラメータは未設定の状態(デフォルト)です。

SQL> SELECT CAST((1.0/6.0) AS NUMBER(38,37)) FROM DUAL;

リテラル値の除算結果は小数点以下1桁の状態でデフォルトのキャストを利用した場合、内部的に小数点以下37桁精度。
実は、表示されている桁数は、SQL*Plusのnumformatの影響を受け、numformat(デフォルト小数点以下7桁)で丸る

一見 小数点以下8桁に見えるが、

SQL> SELECT CAST((9/7) AS NUMBER(38,37)) FROM DUAL;

CAST((9/7)ASNUMBER(38,37))
--------------------------
1.28571429


numformatの桁数を大きくすると内部的は小数点以下37桁精度!

SQL> set numformat "0.0999999999999999999999999999999999999"
SQL> SELECT CAST((9/7) AS NUMBER(38,37)) FROM DUAL;

CAST((9/7)ASNUMBER(38,37))
----------------------------------------
1.2857142857142857142857142857142857143

ね!

SQL> SELECT CAST((9.0/7.0) AS NUMBER(38,37)) FROM DUAL;

CAST((9.0/7.0)ASNUMBER(38,37))
----------------------------------------
1.2857142857142857142857142857142857143

SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,37)) FROM DUAL;

CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,37))
----------------------------------------------------------------------
1.2857142857142857142857142857142857143

SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,37)) FROM DUAL;

CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,37))
----------------------------------------------------------------------
1.2857142857142857142857142857142857143

Oracleの場合、返却時の精度が全ての計算精度に影響

SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,8))/CAST(7.0 AS NUMBER(38,8)) AS NUMBER(38,37)) FROM DUAL;

CAST(CAST(9.0ASNUMBER(38,8))/CAST(7.0ASNUMBER(38,8))ASNUMBER(38,37))
--------------------------------------------------------------------
1.2857142857142857142857142857142857143

SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,8))/CAST(7.0 AS NUMBER(38,8)) AS NUMBER(38,8)) FROM DUAL;

CAST(CAST(9.0ASNUMBER(38,8))/CAST(7.0ASNUMBER(38,8))ASNUMBER(38,8))
-------------------------------------------------------------------
1.2857142900000000000000000000000000000

SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,8)) FROM DUAL;

CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,8))
---------------------------------------------------------------------
1.2857142900000000000000000000000000000

SQL> set numformat "0.09999999999999999999999999999999999999999999999"
SQL> SELECT CAST((9/7) AS NUMBER(38,37)) FROM DUAL;

CAST((9/7)ASNUMBER(38,37))
--------------------------------------------------
1.28571428571428571428571428571428571430000000000

SQL> SELECT CAST((9.0/7.0) AS NUMBER(38,37)) FROM DUAL;

CAST((9.0/7.0)ASNUMBER(38,37))
--------------------------------------------------
1.28571428571428571428571428571428571430000000000

SQL> SELECT CAST(CAST(9.0 AS NUMBER(38,37))/CAST(7.0 AS NUMBER(38,37)) AS NUMBER(38,37)) FROM DUAL;

CAST(CAST(9.0ASNUMBER(38,37))/CAST(7.0ASNUMBER(38,37))ASNUMBER(38,37))
----------------------------------------------------------------------
1.28571428571428571428571428571428571430000000000

PostgreSQLだとどうなるかと言うと、


整数として扱われ、除算結果もinteger型!

sql=> SELECT 9/7;

?column?
----------
1

pg_typeof
-----------
integer


リテラル値は小数点以下1桁の精度だが、除算結果は小数点以下16桁精度のnumeric型で返されるようだ。
PostgreSQLのマニュアルには記載されていないようだが、どこかに記載されてるのだろうか。。。知ってる方がいたらコメントお待ちしております。:)

sql=> SELECT 9.0/7.0;

?column?
--------------------
1.2857142857142857

pg_typeof
-----------
numeric

小数点以下の精度0でnumeric型にキャスト**した場合も、除算結果は小数点以下16桁精度のnumeric型となるようだが、これもPostgreSQLのマニュアルには記載されていないようだ。
なお、PostgreSQLの場合numeric型へのキャストで指定できる最大精度は1000と記載され内部的にはそれ以上の精度となっている
詳細は 

8.1.2. 任意の精度を持つ数
https://www.postgresql.jp/document/11/html/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

表8.2 数値データ型
https://www.postgresql.jp/document/11/html/datatype-numeric.html#DATATYPE-NUMERIC-TABLE
を参照のこと

sql=> SELECT 9::NUMERIC(100,0)/7::NUMERIC(100,0);

?column?
--------------------
1.2857142857142857

pg_typeof
-----------
numeric

小数点以下精度1桁でnumeric型へキャスト**した場合も同様に、除算結果は小数点以下16桁のnumeric型になるのな。これ

sql=> SELECT 9::NUMERIC(100,1)/7::NUMERIC(100,1);

?column?
--------------------
1.2857142857142857

pg_typeof
-----------
numeric

同様に小数点以下2桁でnumeric型へキャストし、除算した結果も小数点以下16桁精度のnumeric型として返される模様。

sql=> SELECT 9::NUMERIC(100,2)/7::NUMERIC(100,2);

?column?
--------------------
1.2857142857142857

pg_typeof
-----------
numeric

小数点以下15桁のnumeric型にキャストした場合も結果は小数点以下16桁精度のnumeric型!

sql=> SELECT 9::NUMERIC(100,15)/7::NUMERIC(100,15);

?column?
--------------------
1.2857142857142857

pg_typeof
-----------
numeric

小数点以下16桁のnumber型にキャストした場合は、指定したscaleで返された。

sql=> SELECT 9::NUMERIC(100,16)/7::NUMERIC(100,16);

?column?
--------------------
1.2857142857142857

pg_typeof
--------------------
numeric

小数点以下17桁精度でnumeric型へキャストすると、指定した小数点以下17桁で除算結果が返された。
(どうやら、小数点以下16桁未満は小数点以下の精度を16桁として扱っているように見える

sql=> SELECT 9::NUMERIC(100,17)/7::NUMERIC(100,17);

?column?
---------------------
1.28571428571428571

pg_typeof
-----------
numeric


表示される桁数が16桁なので内部も16桁かと思ったら、少々多めに持っているらしい。このあたりマニュアルに記載がない....
内部でも小数点以下16精度なのか? という疑問を確認するための確認の結果、小数点以下20桁なので多少大きめの精度で持っているだが、この辺りもPostgreSQLのマニュアルを見る限り記載されていないようだ。

sql=> SELECT CAST((1.0/6.0) AS NUMERIC(38,37));

numeric
-----------------------------------------
0.1666666666666666666700000000000000000

しかし、小数点以下1桁のリテラル値については、小数点以下16桁の精度で計算されてしまう。(ルールはあるようだがドキュメントに明確な記載が見当たらないのは辛いとこと。

sql=> SELECT CAST((9.0/7.0) AS NUMERIC (38,37));

numeric
-----------------------------------------
1.2857142857142857000000000000000000000

結局のところ、デフォルトの挙動に任せず、必要な精度にキャストすることで必要な精度を持たせるようにすることでOracleのデフォルトの挙動と同様の精度になりそう。これが無難な対応なんだろうね。

sql=> SELECT CAST((9.0::NUMERIC(38,37)/7.0::NUMERIC(38,37)) AS NUMERIC(38,37));

numeric
-----------------------------------------
1.2857142857142857142857142857142857143


PostgreSQLでは可能な精度の検証。Oracleからの移行ではここまでの精度は必要としない、よね。多分。

sql=> SELECT 9::NUMERIC(100,99)/7::NUMERIC(100,99);

?column?
-------------------------------------------------------------------------------------------------------
1.285714285714285714285714285714285714285714285714285714285714285714285714285714285714285714285714286

pg_typeof
-----------
numeric

PostgreSQLで指定可能な最大精度(内部的な精度は1000を超えるがして可能な精度は1000)  

試して! Oracler皆びっくり! な感じ

sql=> SELECT 9::NUMERIC(1000,999)/7::NUMERIC(1000,999);

そして、
計算結果とそれぞれのリテラル値の精度ではどちらが優先されるのか確認したところ、Oracleとは異なり、最大精度ではなく、リテラル値の制度が優先される結果となった。この点、Oracleからの移行では注意が必要かな。

以下は、小数点以下の精度を37桁に統一した結果だが、この場合Oracleと同様の精度の結果が得られる

sql=> SELECT (9.0::NUMERIC(38,37)/7.0::NUMERIC(38,37))::NUMERIC(38,37);

numeric
-----------------------------------------
1.2857142857142857142857142857142857143

しかし、リテラル側の精度を落とし8桁にすると、16桁以下は全て16桁の精度になるという挙動が見られ流・

sql=> SELECT (9.0::NUMERIC(38,8)/7.0::NUMERIC(38,8))::NUMERIC(38,37);

numeric
-----------------------------------------
1.2857142857142857000000000000000000000

sql=> SELECT (9.0::NUMERIC(38,16)/7.0::NUMERIC(38,16))::NUMERIC(38,37);

numeric
-----------------------------------------
1.2857142857142857000000000000000000000

リテラル値の精度を17桁にすると、指定精度で結果を返すが、やはり計算結果の精度にはならない

sql=> SELECT (9.0::NUMERIC(38,17)/7.0::NUMERIC(38,17))::NUMERIC(38,37);

numeric
-----------------------------------------
1.2857142857142857100000000000000000000

さらに、17桁の精度で計算し結果を16桁精度で返す例。

sql=> SELECT (9.0::NUMERIC(38,17)/7.0::NUMERIC(38,17))::NUMERIC(38,16);

numeric
--------------------
1.2857142857142857

おまけ

Redshiftのnumeric型はPostgreSQL由来ではあるが、デフォルトの精度に関する挙動はPostgreSQLに近い。ただし、多少異なる部分もある。以下の例はPostgreSQL 11.xでは、小数点以下 20桁精度だったが、Redshiftでは16桁精度となっている。
微妙な違いだが違いがあるのは確かなので注意した方が良さげ

sql=# select cast((1.0/6.0) as numeric(38,37));

numeric
-----------------------------------------
0.1666666666666667000000000000000000000

噂のプログラム発動中だったのか。そんなの気にしてなかった。全くwwww



実行計画は、SQL文のレントゲン写真だ! Oracle Database編 (全部俺) Advent Calendar 2019

標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠!
標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです

|

コメント

コメントを書く