Some more database info

[dveeden]

A bug I filed about IEEE 754 documentation a while ago for MySQL:
https://bugs.mysql.com/bug.php?id=57519

Support for NaN differs between MySQL and PostgreSQL. I didn't see any information about the 'special' values on http://0.30000000000000004.com

Note that there are different behaviors within one database system:
http://sqlfiddle.com/#!6/12e70/3

CREATE TABLE t1 (f FLOAT);
SELECT 0.1 + 0.2;
INSERT INTO t1 VALUES(0.1),(0.2);
SELECT SUM(f) FROM t1;

With Microsoft SQL Server 2014 this has the following output:

0.3
0.30000000000000004

With MySQL 5.6:

0.3
0.30000000447034836

http://sqlfiddle.com/#!9/2e75e/1

Oracle 11gR2 returns 0.3 in both cases:
http://sqlfiddle.com/#!4/0509e6/4

Note that Oracle has FLOAT and BINARY_FLOAT
http://sqlfiddle.com/#!4/cf223/1

[dveeden]

SQLite always returns 0.30000000000000004
http://sqlfiddle.com/#!5/0509e/2

[erikwiffin]

If you send me a PR on the subject I'd be happy to merge it.

[sir-sigurd]

@dveeden

Note that there are different behaviors within one database system:
http://sqlfiddle.com/#!6/12e70/3

CREATE TABLE t1 (f FLOAT);
SELECT 0.1 + 0.2;
INSERT INTO t1 VALUES(0.1),(0.2);
SELECT SUM(f) FROM t1;

IMO that's more correct:

mysql> CREATE TABLE t1 (f DOUBLE);
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO t1 VALUES(0.1),(0.2);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT 0.1e0 + 0.2e0;
+---------------------+
| 0.1e0 + 0.2e0       |
+---------------------+
| 0.30000000000000004 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(f) FROM t1;
+---------------------+
| SUM(f)              |
+---------------------+
| 0.30000000000000004 |
+---------------------+
1 row in set (0.00 sec)

Probably it's the same for MS SQL: https://docs.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql?view=sql-server-2017#float-and-real-constants.