Considering the following test code :
CREATE TABLE binary_test (bin_float BINARY_FLOAT, bin_double BINARY_DOUBLE, NUM NUMBER);
INSERT INTO binary_test VALUES (4356267548.32345E 100, 4356267548.32345E 2 300, 4356267548.32345E 100);
SELECT CASE WHEN bin_double>to_binary_double(num) THEN 'Greater'
WHEN bin_double=to_binary_double(num) THEN 'Equal'
WHEN bin_double<to_binary_double(num) THEN 'Lower'
ELSE 'Unknown' END comparison,
A.*
FROM binary_test A;
I've tried to see which one stores higher values. If I try to add E 300 for the number and binary_float columns, it returns numeric overflow error. So, I thought I could store a greater value with the binary_float. However, when I tried to check it, it shows a lower value, and with the case comparison it says it is lower too. Could you please elaborate this situation?
CodePudding user response：
You are inserting the value 4356267548.32345E 2 300 into the binary double column. That evaluates to 4356267548.32345E 2, which is 435626754832.345, plus 300 - which is 435626755132.345 (or 4.35626755132345E 011, which becomes 4.3562675513234497E 011 when converted to binary double). That is clearly lower than 4356267548.32345E 100 (or 4.35626754832345E 109, which becomes 4.3562675483234496E 109 when converted to binary double).
Not directly relevant, but you should also be aware that you're providing a decimal number literal, which will be implicitly converted to binary double during insert. So you can't use 4356267548.32345E 300, as that is too large for the number data type. If you want to specify a binary double literal then you need to append a d to it, i.e. 4356267548.32345E 300d; but that is still too large.
The highest you can go with that numeric part is 4356267548.32345E 298d, which evaluates to 4.3562675483234498E 307 - just below the data type limit of 1.79769313486231E 308; and note the loss of precision.