Description:
When the TIMESTAMP column has no constraints, an invalid comparison (e.g., WHERE 1 IN (timestamp_column)) correctly fails with ERROR 1525 (HY000): Incorrect TIMESTAMP value. This is the expected behavior.
When the TIMESTAMP column has a UNIQUE constraint, the same query incorrectly succeeds. It produces warnings about an invalid datetime value but proceeds to return a logically incorrect result set (a row is returned when the WHERE condition should be false).
How to repeat:
mysql> CREATE TABLE t7 (c1 TIMESTAMP);
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT IGNORE INTO t7 (c1) VALUES ('2039-10-04 13:47:08');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> SELECT c1 AS ca1, c1 AS ca2, c1 AS ca3 FROM t7 WHERE (((BINARY EXISTS (SELECT c1 AS ca4 FROM t7)) IN (SELECT c1 AS ca5 FROM t7)))
ORDER BY c1, c1;
ERROR 1525 (HY000): Incorrect TIMESTAMP value: '1'
mysql> drop table t7;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE t7 (c1 TIMESTAMP UNIQUE) PACK_KEYS = 0;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT IGNORE INTO t7 (c1) VALUES ('2039-10-04 13:47:08');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SELECT c1 AS ca1, c1 AS ca2, c1 AS ca3 FROM t7 WHERE (((BINARY EXISTS (SELECT c1 AS ca4 FROM t7)) IN (SELECT c1 AS ca5 FROM t7)))
ORDER BY c1, c1;
+---------------------+---------------------+---------------------+
| ca1 | ca2 | ca3 |
+---------------------+---------------------+---------------------+
| 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+---------------------+---------------------+---------------------+
1 row in set, 3 warnings (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------+
| Warning | 1287 | 'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead |
| Warning | 1292 | Incorrect datetime value: '1' for column 'c1' at row 2 |
| Warning | 1292 | Incorrect datetime value: '1' for column 'ca5' at row 2 |
+---------+------+----------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
Description: When the TIMESTAMP column has no constraints, an invalid comparison (e.g., WHERE 1 IN (timestamp_column)) correctly fails with ERROR 1525 (HY000): Incorrect TIMESTAMP value. This is the expected behavior. When the TIMESTAMP column has a UNIQUE constraint, the same query incorrectly succeeds. It produces warnings about an invalid datetime value but proceeds to return a logically incorrect result set (a row is returned when the WHERE condition should be false). How to repeat: mysql> CREATE TABLE t7 (c1 TIMESTAMP); Query OK, 0 rows affected (0.04 sec) mysql> INSERT IGNORE INTO t7 (c1) VALUES ('2039-10-04 13:47:08'); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SELECT c1 AS ca1, c1 AS ca2, c1 AS ca3 FROM t7 WHERE (((BINARY EXISTS (SELECT c1 AS ca4 FROM t7)) IN (SELECT c1 AS ca5 FROM t7))) ORDER BY c1, c1; ERROR 1525 (HY000): Incorrect TIMESTAMP value: '1' mysql> drop table t7; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t7 (c1 TIMESTAMP UNIQUE) PACK_KEYS = 0; Query OK, 0 rows affected (0.05 sec) mysql> INSERT IGNORE INTO t7 (c1) VALUES ('2039-10-04 13:47:08'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SELECT c1 AS ca1, c1 AS ca2, c1 AS ca3 FROM t7 WHERE (((BINARY EXISTS (SELECT c1 AS ca4 FROM t7)) IN (SELECT c1 AS ca5 FROM t7))) ORDER BY c1, c1; +---------------------+---------------------+---------------------+ | ca1 | ca2 | ca3 | +---------------------+---------------------+---------------------+ | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | +---------------------+---------------------+---------------------+ 1 row in set, 3 warnings (0.00 sec) mysql> SHOW WARNINGS; +---------+------+----------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------+ | Warning | 1287 | 'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead | | Warning | 1292 | Incorrect datetime value: '1' for column 'c1' at row 2 | | Warning | 1292 | Incorrect datetime value: '1' for column 'ca5' at row 2 | +---------+------+----------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)