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: | |
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
[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.