Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
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 |
|
+-----------+---------------------+--------------------+--------------------+--------------------+
|
Attachments
Issue Links
- blocks
-
MDEV-16872 Add CAST(expr AS FLOAT)
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue blocks |
| Description |
{code:sql}
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; {code} {noformat} +----------+--------------------+--------------------+--------------------+ | a | COALESCE(a) | MAX(a) | c | +----------+--------------------+--------------------+--------------------+ | 0.671437 | 0.6714370250701904 | 0.6714370250701904 | 0.6714370250701904 | +----------+--------------------+--------------------+--------------------+ {noformat} 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: {code:sql} 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; {code} {noformat} ERROR 1406 (22001): Data too long for column 'b' at row 1 {noformat} Notice, returning the error is wrong. It should create and populate {{t2}} without any errors. |
{code:sql}
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; {code} {noformat} +----------+--------------------+--------------------+--------------------+ | a | COALESCE(a) | MAX(a) | c | +----------+--------------------+--------------------+--------------------+ | 0.671437 | 0.6714370250701904 | 0.6714370250701904 | 0.6714370250701904 | +----------+--------------------+--------------------+--------------------+ {noformat} 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: {code:sql} 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; {code} {noformat} ERROR 1406 (22001): Data too long for column 'b' at row 1 {noformat} 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: {code:sql} 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; {code} {noformat} +-----------+---------------------+--------------------+--------------------+--------------------+ | CONCAT(a) | CONCAT(COALESCE(a)) | CONCAT(LEAST(a,a)) | CONCAT(MAX(a)) | c | +-----------+---------------------+--------------------+--------------------+--------------------+ | 0.671437 | 0.6714370250701904 | 0.6714370250701904 | 0.6714370250701904 | 0.6714370250701904 | +-----------+---------------------+--------------------+--------------------+--------------------+ {noformat} |
| issue.field.resolutiondate | 2019-05-15 03:35:29.0 | 2019-05-15 03:35:29.069 |
| Component/s | Data types [ 13906 ] | |
| Fix Version/s | 10.3.16 [ 23410 ] | |
| Fix Version/s | 10.4.5 [ 23311 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 96719 ] | MariaDB v4 [ 156187 ] |