mismatch the warning message and error code with MySQL 5.7

[siddontang]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

Create a table and insert some values, the table schema:

mysql> show create table item1 \G;
*************************** 1. row ***************************
       Table: item1
Create Table: CREATE TABLE `item1` (
  `i_id` int(11) NOT NULL,
  `i_im_id` int(11) DEFAULT NULL,
  `i_name` varchar(24) DEFAULT NULL,
  `i_price` decimal(5,2) DEFAULT NULL,
  `i_data` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

then select

mysql> select count(*) from item1 where i_data ;
+----------+
| count(*) |
+----------+
|    14769 |
+----------+
1 row in set, 65535 warnings (0.05 sec)

2. What did you expect to see?

Like MySQL 5.7, the warning message should be:

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                 |
+---------+------+-----------------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'i9pT0zpXTxFYTxMtHP6h3VVZL5y7Pr2zC6ttY5C5QXMg6t8KY'  |
| Warning | 1292 | Truncated incorrect INTEGER value: 'uOa4vwxAYTZjkRv5K4kyWsSFuctocROdPAW47hw2vnnp'       |
| Warning | 1292 | Truncated incorrect INTEGER value: 'tQabPDFTPjGr5hAkFnAQMlIIPR7C4pBnTAGrmm0a0T8'        |
| Warning | 1292 | Truncated incorrect INTEGER value: 'j3B7JLBhCe5xNmj1KSxL2avY0j6xyyUuemQeR5xCxdgM'       |
| Warning | 1292 | Truncated incorrect INTEGER value: 'O5lYdP5HWrloobWqyiYdp6XvqAZPxuodqFmTB'              |

3. What did you see instead?

But got:

| Warning | 1105 | Data Truncated                                                                                  |
| Warning | 1105 | Data Truncated                                                                                  |
| Warning | 1105 | Data Truncated                                                                                  |
| Warning | 1105 | Data Truncated                                                                                  |
| Warning | 1105 | Data Truncated                                                                                  |
| Warning | 1105 | Data Truncated                                                                                  |
| Warning | 1105 | Data Truncated                                                                                  |

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

[ghost]

I tested this against master. The error code is now correct, but the error message is incorrect (it includes "evaluation failed: "). Here is a pasteable test case + output:

DROP TABLE IF EXISTS t1;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL PRIMARY KEY auto_increment,
  `data` varchar(50) DEFAULT NULL
);
INSERT INTO t1 (data) VALUES ('i9pT0zpXTxFYTxMtHP6h3VVZL5y7Pr2zC6ttY5C5QXMg6t8KY'),('uOa4vwxAYTZjkRv5K4kyWsSFuctocROdPAW47hw2vnnp'),('tQabPDFTPjGr5hAkFnAQMlIIPR7C4pBnTAGrmm0a0T8'),('j3B7JLBhCe5xNmj1KSxL2avY0j6xyyUuemQeR5xCxdgM'),('O5lYdP5HWrloobWqyiYdp6XvqAZPxuodqFmTB');
SELECT count(*) FROM t1 WHERE data;
SHOW WARNINGS;


..

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                   |
+---------+------+-----------------------------------------------------------------------------------------------------------+
| Warning | 1292 | evaluation failed: Truncated incorrect INTEGER value: 'i9pT0zpXTxFYTxMtHP6h3VVZL5y7Pr2zC6ttY5C5QXMg6t8KY' |
| Warning | 1292 | evaluation failed: Truncated incorrect INTEGER value: 'uOa4vwxAYTZjkRv5K4kyWsSFuctocROdPAW47hw2vnnp'      |
| Warning | 1292 | evaluation failed: Truncated incorrect INTEGER value: 'tQabPDFTPjGr5hAkFnAQMlIIPR7C4pBnTAGrmm0a0T8'       |
| Warning | 1292 | evaluation failed: Truncated incorrect INTEGER value: 'j3B7JLBhCe5xNmj1KSxL2avY0j6xyyUuemQeR5xCxdgM'      |
| Warning | 1292 | evaluation failed: Truncated incorrect INTEGER value: 'O5lYdP5HWrloobWqyiYdp6XvqAZPxuodqFmTB'             |
+---------+------+-----------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-769-g2024e4463
Edition: Community
Git Commit Hash: 2024e44636f2fb9798982a1cc0ec22176e07f5c1
Git Branch: master
UTC Build Time: 2020-07-14 09:26:07
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

It looks like an easy fix.

[Defined2014]

Tried in master, it has same results with MySQL 8.0.26 right now. Could you help me close this issue? @tisonkun

TiDB result:

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                               |
+---------+------+---------------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'i9pT0zpXTxFYTxMtHP6h3VVZL5y7Pr2zC6ttY5C5QXMg6t8KY' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'uOa4vwxAYTZjkRv5K4kyWsSFuctocROdPAW47hw2vnnp'      |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'tQabPDFTPjGr5hAkFnAQMlIIPR7C4pBnTAGrmm0a0T8'       |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'j3B7JLBhCe5xNmj1KSxL2avY0j6xyyUuemQeR5xCxdgM'      |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'O5lYdP5HWrloobWqyiYdp6XvqAZPxuodqFmTB'             |
+---------+------+---------------------------------------------------------------------------------------+

MySQL result:

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                               |
+---------+------+---------------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'i9pT0zpXTxFYTxMtHP6h3VVZL5y7Pr2zC6ttY5C5QXMg6t8KY' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'uOa4vwxAYTZjkRv5K4kyWsSFuctocROdPAW47hw2vnnp'      |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'tQabPDFTPjGr5hAkFnAQMlIIPR7C4pBnTAGrmm0a0T8'       |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'j3B7JLBhCe5xNmj1KSxL2avY0j6xyyUuemQeR5xCxdgM'      |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'O5lYdP5HWrloobWqyiYdp6XvqAZPxuodqFmTB'             |
+---------+------+---------------------------------------------------------------------------------------+

[tisonkun]

@Defined2014 it's hard to say whether we are compatible with MySQL 5.7 or 8.0. Sorry I cannot make the decision.

cc @morgo @bb7133

[morgo]

I checked, I agree it's fixed in master. It must have been a recent fix though, because 5.2.1 is still broken. So I will go ahead and close this.

.. it's hard to say whether we are compatible with MySQL 5.7 or 8.0. Sorry I cannot make the decision.

@tisonkun for a while now when there is a discrepancy, we usually pick the 8.0 behavior. It's often 'cleaner' and easier to follow. But officially we are 5.7 compatible, because we are still missing some 8.0 features. See: #7968

[tisonkun]

@morgo get it and thank you!

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.