expression: short-cut expressions may get more warnings than needed

[gengliqi]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> create table test.t(f1 char(1));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test.t values ('a'),('b'),('1');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select (cast(f1 as float) = 1) or (cast(f1 as float) = 2) from test.t;
+----------------------------------------------------+
| (cast(f1 as float) = 1) or (cast(f1 as float) = 2) |
+----------------------------------------------------+
|                                                  0 |
|                                                  0 |
|                                                  1 |
+----------------------------------------------------+
3 rows in set, 6 warnings (0.00 sec)

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

mysql> select (cast(f1 as float) != 1) and (cast(f1 as float) != 2) from test.t;
+-------------------------------------------------------+
| (cast(f1 as float) != 1) and (cast(f1 as float) != 2) |
+-------------------------------------------------------+
|                                                     1 |
|                                                     1 |
|                                                     0 |
+-------------------------------------------------------+
3 rows in set, 6 warnings (0.00 sec)

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

2. What did you expect to see? (Required)

MySQL 8.0
mysql> create table test.t(f1 char(1));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test.t values ('a'),('b'),('1');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select (cast(f1 as float) = 1) or (cast(f1 as float) = 2) from test.t;
+----------------------------------------------------+
| (cast(f1 as float) = 1) or (cast(f1 as float) = 2) |
+----------------------------------------------------+
|                                                  0 |
|                                                  0 |
|                                                  1 |
+----------------------------------------------------+
3 rows in set, 4 warnings (0.02 sec)

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

mysql> select (cast(f1 as float) != 1) and (cast(f1 as float) != 2) from test.t;
+-------------------------------------------------------+
| (cast(f1 as float) != 1) and (cast(f1 as float) != 2) |
+-------------------------------------------------------+
|                                                     1 |
|                                                     1 |
|                                                     0 |
+-------------------------------------------------------+
3 rows in set, 4 warnings (0.00 sec)

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

3. What did you see instead (Required)

6 warnings other than 4 warnings.

4. What is your TiDB version? (Required)

master

[gengliqi]

/severity moderate