Bug #85918 NO_ZERO_DATE prevents use of zero date even in WHERE clause of UPDATE
Submitted: 12 Apr 2017 16:33 Modified: 29 Aug 2023 18:20
Reporter: John Flatness Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.7.17 OS:Any
Assigned to: CPU Architecture:Any

[12 Apr 2017 16:33] John Flatness
Description:
With the NO_ZERO_DATE mode enabled, executing an UPDATE that merely uses a zero date in the WHERE clause results in an error. With "added" as a TIMESTAMP column, the following query:

UPDATE sometable SET added = NOW() WHERE added = '0000-00-00 00:00:00'

results in the following error:

#1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'added' at row 1

None of the actual values stored in the table are the zero date, and the UPDATE is not setting any zero dates. Even if there are no rows in the table, the error occurs. The mere inclusion of the zero date in the query is enough to raise the error. Changing NOW() to any valid explicit date has no effect. A SELECT containing a zero date works correctly.

Adding IGNORE to the query allows it to execute, and it does correctly select the proper rows and update them.

The error is confirmed to occur on a 5.7.17 server, but not on a 5.6.35 server, even sharing the same mode. 

How to repeat:
CREATE TABLE `zerodate` (
 `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
UPDATE zerodate SET added = NOW() WHERE added = '0000-00-00 00:00:00'

Suggested fix:
Zero dates should be valid in the WHERE clause in the same way that they are valid in a SELECT, regardless of the mode.
[12 Apr 2017 19:30] MySQL Verification Team
Thank you for the bug report.
[18 Apr 2017 13:26] Ståle Deraas
Posted by developer:
 
Hi John,

We agree that this is a change in behaviour from 5.6 to 5.7. We did basically reimplement the strict handling for 5.7. But we wonder if the current behaviour, where the WHERE clause is affected, is actually more intuitive for the user? So going forward we might choose to be "more strict", and in that respect it makes sense to keep this change.
[18 Apr 2017 15:04] John Flatness
My specific concern is that this makes it more difficult to take existing data (from a prior server or from an insert/update under a different mode) and "fix" it, as you can't simply update the bad dates as you normally could with other data.

I can't speak for others, but it's also very unintuitive to me that this (or anything) would be forbidden in the WHERE of an UPDATE but not the WHERE of a SELECT.

If it's a change that will be maintained, is it documented somewhere? I was unable to find any reference to this specific type of change, only the other travails of zero/date handling in the 5.7 series.
[14 Jun 2023 15:29] Matthew Boehm
This 6 year old bug is still an issue and is preventing simple upgrades along with migrations from PGSQL to MySQL.

mysql [localhost:8028] {root} (world) > SHOW CREATE TABLE cantUpdate\G
*************************** 1. row ***************************
       Table: cantUpdate
Create Table: CREATE TABLE `cantUpdate` (
  `id` int unsigned NOT NULL,
  `myDate` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql [localhost:8028] {root} (world) > SELECT * FROM cantUpdate;
+----+------------+
| id | myDate     |
+----+------------+
|  1 | 0000-00-00 |
|  2 | 0000-00-00 |
|  3 | 2023-06-10 |
+----+------------+
3 rows in set (0.00 sec)

mysql [localhost:8028] {root} (world) > UPDATE cantUpdate SET myDate = '2021-03-22' WHERE myDate = '0000-00-00';
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'myDate' at row 1
[14 Jun 2023 15:30] Matthew Boehm
Affects 8.0.28
[29 Aug 2023 18:15] John Flatness
In 8 (8.0.34 specifically I'm looking at), it's actually worse now: UPDATE IGNORE no longer works.

UPDATE IGNORE zerodate set added = NOW() where added = '0000-00-00 00:00:00';

---

#1525 - Incorrect TIMESTAMP value: '0000-00-00 00:00:00'

As far as I can tell, it's now just impossible to select for zero dates with NO_ZERO_DATE mode on: both selects and updates don't allow them in the WHERE, regardless of if strict mode is enabled.

INSERT IGNORE and UPDATE IGNORE still allow you to *insert* zero dates but you can't query for them at all.
[29 Aug 2023 18:20] John Flatness
The only option to be able to query for these dates now seems to be to set the mode to exclude NO_ZERO_DATE.