P2-[4.0 Bug Hunting]-[Strange Warnings]-Truncated incorrect FLOAT value

[zhangysh1995]

Bug Report

1. What did you do?

create table tmp (id int, type varchar(10));

insert into tmp values (1, 'foo');
insert into tmp values (2, 'foo');
insert into tmp values (3, 'foo');
insert into tmp values (4, 'foo');
insert into tmp values (5, 'foo');

insert into tmp values (6, 'bar');
insert into tmp values (7, 'bar');
insert into tmp values (8, 'bar');
insert into tmp values (9, 'bar');
insert into tmp values (10, 'bar');

create table tmp_properties (id int, title varchar(25));
insert into tmp_properties values (1, 'foo_title');

select * from tmp left join tmp_properties tp on tp.id = tmp.type;    

2. What did you expect to see?

No warnings.

3. What did you see instead?

There is no column with FLOAT from the tables.

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect FLOAT value: 'foo' |
| Warning | 1292 | Truncated incorrect FLOAT value: 'foo' |
| Warning | 1292 | Truncated incorrect FLOAT value: 'foo' |
| Warning | 1292 | Truncated incorrect FLOAT value: 'foo' |
| Warning | 1292 | Truncated incorrect FLOAT value: 'foo' |
| Warning | 1292 | Truncated incorrect FLOAT value: 'bar' |
| Warning | 1292 | Truncated incorrect FLOAT value: 'bar' |
| Warning | 1292 | Truncated incorrect FLOAT value: 'bar' |
| Warning | 1292 | Truncated incorrect FLOAT value: 'bar' |
| Warning | 1292 | Truncated incorrect FLOAT value: 'bar' |
+---------+------+----------------------------------------+
10 rows in set (0.00 sec)

4. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

Git log:

commit f06fc3553187208df30e63abdc7c91f88ffbcab7 (HEAD -> master, origin/master, origin/HEAD)
Author: Yiding Cui <winoros@gmail.com>
Date:   Mon May 18 15:41:45 2020 +0800

With select tidb_version();:

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: None
Edition: None
Git Commit Hash: None
Git Branch: None
UTC Build Time: None
GoVersion: go1.13.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[wjhuang2016]

I think it's expected behavior. In MySQL:

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'bar' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'bar' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'bar' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'bar' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'bar' |
+---------+------+-----------------------------------------+
10 rows in set (0.00 sec)

[zhangysh1995]

But where does the 'FLOAT' come from? There isn't any column with such types. I would also consider it as an undesired behavior.

[wjhuang2016]

https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html FYI

In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.

[zhangysh1995]

I think there is a similar issue here https://bugs.mysql.com/bug.php?id=46641&error=un It is more like a feature than a bug.

[shuke987]

/bug no bug

[sre-bot]

More parameters are required.

[shuke987]

/bug not bug

[sre-bot]

More parameters are required.

[sre-bot]

More parameters are required.

[shuke987]

/bug non-bug