Bug #85918 NO_ZERO_DATE prevents use of zero date even in WHERE clause of UPDATE
Submitted: 12 Apr 2017 16:33 Modified: 18 Apr 2017 15:04
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] Miguel Solorzano
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.