| Bug #78697 | failed cast of string-returning function to DOUBLE emits no warning | ||
|---|---|---|---|
| Submitted: | 5 Oct 2015 7:17 | Modified: | 4 Nov 2020 14:37 |
| Reporter: | Guilhem Bichot | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.7.8, 5.7.10, 8.0.0, 5.6.28, 5.5.47 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[5 Oct 2015 7:24]
Guilhem Bichot
With INSERT:
create table t1(a double);
insert into t1 values('a');
ERROR 1265 (01000): Data truncated for column 'a' at row 1
insert into t1 values(concat('a',''));
ERROR 1265 (01000): Data truncated for column 'a' at row 1
insert into t1 values(concat('a','')+0);
Query OK, 1 row affected (0,10 sec)
The implicit cast of storing string-returning function into DOUBLE column properly fails; but when using '+', the implicit cast - also from string to DOUBLE - succeeds.
[5 Oct 2015 7:46]
MySQL Verification Team
Hello Guilhem, Thank you for the report and test case. Thanks, Umesh
[5 Oct 2015 7:47]
MySQL Verification Team
// 5.7.10
commit: a814c74e87b4ef1a81c59f10805bdd9f9ff903d4
date: 2015-10-01 15:51:16 +0200
build-date: 2015-10-01 18:03:36 +0200
short: a814c74
branch: mysql-5.7
MySQL source 5.7.10
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------------+
| innodb_version | 5.6.28 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.28-enterprise-commercial-advanced-debug |
| version_comment | MySQL Enterprise Server - Advanced Edition Debug (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+---------------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> drop database if exists test;
Query OK, 2 rows affected (0.02 sec)
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 3 warnings (0.00 sec)
mysql> create table t1(a double);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values('a');
ERROR 1265 (01000): Data truncated for column 'a' at row 1
mysql> insert into t1 values(concat('a',''));
ERROR 1265 (01000): Data truncated for column 'a' at row 1
mysql> insert into t1 values(concat('a','')+0);
Query OK, 1 row affected (0.00 sec)
[5 Oct 2015 7:48]
MySQL Verification Team
// 5.8.0
cat docs/INFO_SRC
commit: f1ca5c4badf22b044ad613b5797338b2ec5ec613
date: 2015-10-01 15:53:30 +0200
build-date: 2015-10-01 17:06:11 +0200
short: f1ca5c4
branch: mysql-trunk
MySQL source 5.8.0
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.8.0 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.8.0-m17-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> drop database if exists test;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql> use test
Database changed
mysql> create table t1(a double);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values('a');
ERROR 1265 (01000): Data truncated for column 'a' at row 1
mysql> insert into t1 values(concat('a',''));
ERROR 1265 (01000): Data truncated for column 'a' at row 1
mysql> insert into t1 values(concat('a','')+0);
Query OK, 1 row affected (0.00 sec)
[5 Oct 2015 7:51]
MySQL Verification Team
// 5.6.28
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.6.28 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.28-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> drop database if exists test;
Query OK, 0 rows affected (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 3 warnings (0.00 sec)
mysql> use test;
Database changed
mysql> create table t1(a double);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values('a');
ERROR 1265 (01000): Data truncated for column 'a' at row 1
mysql> insert into t1 values(concat('a',''));
ERROR 1265 (01000): Data truncated for column 'a' at row 1
mysql> insert into t1 values(concat('a','')+0);
Query OK, 1 row affected (0.00 sec)
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.28: cat docs/INFO_SRC
commit: c258e0be00fd3fe86c51860126fdc8f8377168b6
date: 2015-10-01 13:24:49 +0530
build-date: 2015-10-01 10:06:57 +0200
short: c258e0b
branch: mysql-5.6
MySQL source 5.6.28
[5 Oct 2015 7:51]
MySQL Verification Team
// 5.5.47
cat docs/INFO_SRC
commit: 451de8c7ab5e5fa05ae173f1cdc353a88e535d11
date: 2015-10-01 07:45:27 +0530
build-date: 2015-10-01 23:45:55 +0200
short: 451de8c
branch: mysql-5.5
MySQL source 5.5.47
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.47: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.47 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.5.47 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.47 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux2.6 |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
mysql> drop database if exists test;
Query OK, 0 rows affected (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table t1(a double);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values('a');
ERROR 1265 (01000): Data truncated for column 'a' at row 1
mysql> insert into t1 values(concat('a',''));
ERROR 1265 (01000): Data truncated for column 'a' at row 1
mysql> insert into t1 values(concat('a','')+0);
Query OK, 1 row affected (0.00 sec)
[18 Jun 2016 21:28]
Omer Barnir
Posted by developer: Reported version value updated to reflect release name change from 5.8 to 8.0
[4 Nov 2020 14:37]
Paul DuBois
Posted by developer: Fixed in 8.0.24. Conversion of string-valued user-defined variables or function results to double (for example, using CAST()) did not emit a warning when truncation occurred.
Description: Background: as documented, '+' between string and int converts operands to DOUBLE; conversion of string to DOUBLE is done digit-by-digit (so the string '12' is the DOUBLE value 12). select 'a'+0; +-------+ | 'a'+0 | +-------+ | 0 | +-------+ 1 row in set, 1 warning (29,49 sec) Warning (Code 1292): Truncated incorrect DOUBLE value: 'a' This warning is correct, as 'a' is no decimal digit (conversion has failed and produced 0). The same warning is seen if 'a' is replaced with a CHAR(1) column of a table: create table t1(a char(1)); insert into t1 values('a'); select a+0 from t1; +------+ | a+0 | +------+ | 0 | +------+ Warning (Code 1292): Truncated incorrect DOUBLE value: 'a' The bug is that the following should also warn but it doesn't: select concat('a','')+0; +------------------+ | concat('a','')+0 | +------------------+ | 0 | +------------------+ The same happens for any string-returning function. This is a problem as there is data loss without warning; and if used in INSERT, there's no warning/error either. Verified with 5.7 7ae47c0979e59826782ef665b52b6d9090e4a8de How to repeat: select concat('a','')+0; I am using the default: select @@sql_mode; ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | Suggested fix: For select 'a'+0; Item_string::val_real() is called and it has code for the warning. For select a+0 from t1, Field_string::val_real() is called and it has code for the warning. For select concat('a','')+0; Item_str_func::val_real() is called and it has NO code for the warning.