MySQL Bugs: #95466: ERROR 1525 (HY000): Incorrect TIMESTAMP value: 'CURRENT

Bug #95466	ERROR 1525 (HY000): Incorrect TIMESTAMP value: 'CURRENT_TIMESTAMP'
Submitted:	22 May 2019 3:55	Modified:	12 Jun 2019 15:03
Reporter:	Franz Eisenkölbl	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.16	OS:	Linux
Assigned to:		CPU Architecture:	x86
Tags:	current_timestamp, ERROR 1525, regression

Description:
Up to, and including, Version 8.0.15, using a string, which couldn't be converted to a timestamp in a WHERE clause to compare with a timestamp field, caused a Warning only, now its ERROR 1525.
With 8.0.16 it throws:
ERROR 1525 (HY000): Incorrect TIMESTAMP value: 'CURRENT_TIMESTAMP'
Because I could not find anything for that in the "incompatible changes" section of 8.0.16, it seems to be a bug.

How to repeat:
Field `time` is of type timestamp:

8.0.15:
mysql> SELECT count(*) FROM `config_log` WHERE `time`<='CURRENT_TIMESTAMP';
+----------+
| count(*) |
+----------+
|      246 |
+----------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------+
| Level   | Code | Message                                                                  |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: 'CURRENT_TIMESTAMP' for column 'time' at row 1 |
| Warning | 1292 | Incorrect datetime value: 'CURRENT_TIMESTAMP' for column 'time' at row 1 |
+---------+------+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

8.0.16:
mysql> SELECT count(*) FROM `config_log` WHERE `time`<='CURRENT_TIMESTAMP';
ERROR 1525 (HY000): Incorrect TIMESTAMP value: 'CURRENT_TIMESTAMP'

Suggested fix:
Document this in the "incompatible changes" section.

Hello Franz Eisenkölbl,

Thank you for the report.

regards,
Umesh

Posted by developer:
 
Fix should be to use the correct DATETIME values: in this case CURRENT_TIMESTMAP without quotes as the quoted version leads to string comparison between two strings: the STRING "CURRENT_TIMESTAMP" and the string resulted from converting the datetime column to string.
The behavior is documented already in 8.0.16 change log as following:

When comparing DATE values with constant strings, MySQL first tries to convert the string to a DATE and then to perform the comparison. When the conversion failed, MySQL executed the comparison treating the DATE as a string, which could lead to unpredictable behavior. Now in such cases, if the conversion of the string to a DATE fails, the comparison fails with ER_WRONG_VALUE. (Bug #29025656)

Hi,

This is NOT a regression. 

Here's an example of a bad thing that could happen prior to the fix for BUG#93513:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.15    |
+-----------+
1 row in set (0.02 sec)

mysql> CREATE TABLE t (a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, b TIMESTAMP);             
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO t VALUES (NULL, NOW());                  
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO t VALUES (NULL, NOW());
Query OK, 1 row affected (0.03 sec)

# Insert a date well into the future
mysql> INSERT INTO t VALUES (NULL, DATE_ADD(NOW(), INTERVAL 1 YEAR));
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO t VALUES (NULL, NOW());
Query OK, 1 row affected (0.03 sec)
 
mysql> INSERT INTO t VALUES (NULL, NOW());
Query OK, 1 row affected (0.03 sec)

# No quotes - correct result
mysql> SELECT * FROM t WHERE b <= CURRENT_TIMESTAMP;
+---+---------------------+
| a | b                   |
+---+---------------------+
| 1 | 2019-06-12 10:49:02 |
| 2 | 2019-06-12 10:49:08 |
| 4 | 2019-06-12 10:50:13 |
| 5 | 2019-06-12 10:50:16 |
+---+---------------------+
4 rows in set (0.00 sec)

# With quotes - result is clearly not correct
mysql> SELECT * FROM t WHERE b <= 'CURRENT_TIMESTAMP';
+---+---------------------+
| a | b                   |
+---+---------------------+
| 1 | 2019-06-12 10:49:02 |
| 2 | 2019-06-12 10:49:08 |
| 3 | 2020-06-12 10:50:08 |
| 4 | 2019-06-12 10:50:13 |
| 5 | 2019-06-12 10:50:16 |
+---+---------------------+
5 rows in set, 1 warning (0.01 sec)

cheers

jon.