Bug #101082 const value of type DATE was truncated when compared to YEAR column
Submitted: 7 Oct 2020 13:08 Modified: 8 Oct 2020 12:15
Reporter: Cheng Zhou Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[7 Oct 2020 13:08] Cheng Zhou
Description:
When compared to a YEAR column, a const value of type DATE/TIMESTAMP would be truncatedd, and the result is incorrect. Also, there no warnings or errors.

How to repeat:
1. CREATE TABLE t1(a DATE, b YEAR, primary KEY(a));
2. INSERT INTO t1 VALUES ('2011-01-01',2011);
3.
mysql> select b='2011-01-01' from t1;
+----------------+
| b='2011-01-01' |
+----------------+
|              1 |
+----------------+

mysql> select b='2011-x' from t1;
+------------+
| b='2011-x' |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> select b='2011xxxxx' from t1;
+---------------+
| b='2011xxxxx' |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

Suggested fix:
At least, report a warning when the value is truncated.
[7 Oct 2020 14:15] MySQL Verification Team
Hi Mr. Zhou,

Thank you for your bug report.

First of all, you could enable STRICT SQL mode and see the behaviour.

Second, after each SQL statement you can run SHOW WARNINGS and see what those say.

Both of these should be attempted.

We are waiting on your feedback.
[7 Oct 2020 15:46] Cheng Zhou
mysql> select * from t1;
+------------+------+
| a          | b    |
+------------+------+
| 2011-01-01 | 2011 |
+------------+------+
1 row in set (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.00 sec)

mysql> select b='2011xxxx' from t1;
+--------------+
| b='2011xxxx' |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)
[7 Oct 2020 15:54] Cheng Zhou
mysql> select * from t1;
+------------+------+
| a          | b    |
+------------+------+
| 2011-01-01 | 2011 |
+------------+------+
1 row in set (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| sql_mode      | STRICT_ALL_TABLES |
+---------------+-------------------+
1 row in set (0.01 sec)

mysql> select b='2011xxxx' from t1;
+--------------+
| b='2011xxxx' |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> select b='2011-03-04' from t1;
+----------------+
| b='2011-03-04' |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)
[7 Oct 2020 15:58] Cheng Zhou
mysql> show variables like 'sql_mode';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| sql_mode      | STRICT_ALL_TABLES |
+---------------+-------------------+
1 row in set (0.00 sec)

mysql> select b=cast('2011-03-04' as date) from t1;
+------------------------------+
| b=cast('2011-03-04' as date) |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)
[8 Oct 2020 12:15] MySQL Verification Team
Hi Mr. Zhou,

We think that you are correct and that this is quite useful feature request.

Verified as a feature request.
[8 Oct 2020 12:32] MySQL Verification Team
Just setting a correct category.