MySQL Bugs: #46641: Unclear warning message 'Warning: 1292: Truncated incorrect DOUBLE value: __

Bug #46641	Unclear warning message 'Warning: 1292: Truncated incorrect DOUBLE value: ___'
Submitted:	11 Aug 2009 0:54	Modified:	17 Aug 2009 8:22
Reporter:	Roel Van de Paar	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.1.36/5.4	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
-----
mysql> select * from test5 join test6 on f1 where f1='test';
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'test ' |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
-----

Though the syntax is somewhat 'badly written', and though a product may be generated as a result of this join, ('on f1' is always true), a similar instruction does not give a warning:

-----
mysql> select * from test5 join test6 where f1='test';
+------+-------+
| f1   | f2    |
+------+-------+
| test | test  |
| test | test2 |
+------+-------+
2 rows in set (0.00 sec)
-----

Also, a similar test with int data type instead of char does not fail:

-----
mysql> select * from test7 join test8 on f1 where f1=1;
+------+------+
| f1   | f2   |
+------+------+
|    1 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)
----

How to repeat:
mysql> create table test5 (f1 char(5));
Query OK, 0 rows affected (0.04 sec)

mysql> create table test6 (f2 char(5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test5 values ('test'),('test2');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into test6 values ('test'),('test2');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test5 join test6 on f1 where f1='test';
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'test ' |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test5 join test6 on f1=f2 where f1='test'; /* Correct syntax */
+------+------+
| f1   | f2   |
+------+------+
| test | test |
+------+------+
1 row in set (0.00 sec)

mysql> select * from test5 join test6 where f1='test'; /* A similar instruction */
+------+-------+
| f1   | f2    |
+------+-------+
| test | test  |
| test | test2 |
+------+-------+
2 rows in set (0.00 sec)

mysql> create table test7 (f1 int);
Query OK, 0 rows affected (0.04 sec)

mysql> create table test8 (f2 int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test7 values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into test8 values (1),(2);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test7 join test8 on f1 where f1=1; /* A similar instruction using int data type */
+------+------+
| f1   | f2   |
+------+------+
|    1 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

Suggested fix:
Show a clearer warning message, or none at all (i.e. return the same result as without the 'on f1')

Thank you for the bug report.

Also see bug #19398

> It is not clear why the above is wrong.

I am not sure that it is necessarily an 'incorrect' warning message. However, it seems like a very 'unclear' warning message;

When a user reads the following: "Truncated incorrect DOUBLE value: 'test '" for this query:

  select * from test5 join test6 on f1 where f1='test';

It could mean a lot of things. To me I read this as follows:

'Truncated' means shortened
'Incorrect ... value' means there is some sort of incorrect value
'DOUBLE' points to a data type
'test ' is the input that was used in the query, though with a space added?

Without further research, none of these makes sense to me:

'Truncated': i.e. What was truncated? Truncated to what? Why was something truncated? Why is there a space in 'test '?
'Incorrect ... value': Why was the value incorrect? A similar query without the 'on f1' does not yield an error:

mysql> select * from test5 join test6 where f1='test';

'DOUBLE': Why a DOUBLE value while the query was referencing character strings?
'test ': why was the space added? Why was it truncated?

> Strings are converted to floats in these type of comparisons. It is true that the numeric value of the string is 'zero' and as such, the truncation of it from double to integer has no practical consequences - i.e. there is no actual change in the value, but still, a trancation is taking place and there are cases where a warning is warented even if the value is not changing.

Based on the linked bug, and some further research (given the EXPLAIN EXTENDED+SHOW WARNINGS output below), I think I understand this. 

The query is really evaluated as: select `testfor`.`test5`.`f1` AS `f1`,`testfor`.`test6`.`f2` AS `f2` from `testfor`.`test5` join `testfor`.`test6` where ((`testfor`.`test5`.`f1` = 'test') and `testfor`.`test5`.`f1`)

I think the problem is that the following syntax is allowed:

mysql> select * from test5 where f1;
Empty set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'test ' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'test2' |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)

I assume it is trying to convert this to an integer because it is trying to an 'IS TRUE' comparison;

mysql> create table test9(id boolean);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test9 values (0),(1),(0);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test9 where id;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

How about an invalid syntax warning (or even error) for this instead;

-----
Error/Warning: 'WHERE comparisons between numerical and non-numerical columns are not allowed'
-----

This does not fully explain why it is using a FLOAT in the warning above instead though.

As a sidenote, the space(s) seem to have something to do with the defined column length:

mysql> create table test5(f1 char(10));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test5 values('test'),('test123'),('test12345');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test5 where f1;
Empty set, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'test      ' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'test123   ' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'test12345 ' |
+---------+------+-------------------------------------------------+
3 rows in set (0.00 sec)

Set back to verified because of last comment of Roel.