Bug #78697 failed cast of string-returning function to DOUBLE emits no warning
Submitted: 5 Oct 2015 7:17 Modified: 5 Oct 2015 7:46
Reporter: Guilhem Bichot Email Updates:
Status: Verified Impact on me:
None 
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:17] Guilhem Bichot
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.
[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] Umesh Shastry
Hello Guilhem,

Thank you for the report and test case.

Thanks,
Umesh
[5 Oct 2015 7:47] Umesh Shastry
// 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] Umesh Shastry
// 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] Umesh Shastry
// 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] Umesh Shastry
// 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