|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a FLOAT);
|
INSERT INTO t1 VALUES (0.671437);
|
SELECT a, COALESCE(a), MAX(a), (SELECT a FROM t1) AS c FROM t1;
|
+----------+--------------------+--------------------+--------------------+
|
| a | COALESCE(a) | MAX(a) | c |
|
+----------+--------------------+--------------------+--------------------+
|
| 0.671437 | 0.6714370250701904 | 0.6714370250701904 | 0.6714370250701904 |
|
+----------+--------------------+--------------------+--------------------+
|
Notice, the value of the FLOAT type column is limited to 6 significant digits, while values of FLOAT type expressions return more digits.
The same problem is repeatable in this script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a FLOAT);
|
INSERT INTO t1 VALUES (0.671437);
|
CREATE OR REPLACE TABLE t2 AS SELECT a, CONCAT(COALESCE(a)) AS b FROM t1;
|
ERROR 1406 (22001): Data too long for column 'b' at row 1
|
Notice, returning the error is wrong. It should create and populate t2 without any errors.
This happens because CONCAT() with a FLOAT type expression returns too long values:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a FLOAT);
|
INSERT INTO t1 VALUES (0.671437);
|
SELECT CONCAT(a), CONCAT(COALESCE(a)), CONCAT(LEAST(a,a)), CONCAT(MAX(a)), CONCAT((SELECT a FROM t1)) AS c FROM t1;
|
+-----------+---------------------+--------------------+--------------------+--------------------+
|
| CONCAT(a) | CONCAT(COALESCE(a)) | CONCAT(LEAST(a,a)) | CONCAT(MAX(a)) | c |
|
+-----------+---------------------+--------------------+--------------------+--------------------+
|
| 0.671437 | 0.6714370250701904 | 0.6714370250701904 | 0.6714370250701904 | 0.6714370250701904 |
|
+-----------+---------------------+--------------------+--------------------+--------------------+
|
|