Bug #23244 UPDATE on table with multiple datetime column fails unless other column updated
Submitted: 13 Oct 2006 6:03 Modified: 23 Oct 2006 7:41
Reporter: Michael Furdyk Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.26 OS:
Assigned to: CPU Architecture:Any

[13 Oct 2006 6:03] Michael Furdyk
Description:
As simple as this:

This works:

UPDATE tig.TIGedAssign SET StartDate = '2006-10-13 24:00', Title = 'blue2022' WHERE AssignID = '343' AND ClassID = '36'

This fails:

UPDATE tig.TIGedAssign SET StartDate = '2006-10-13 24:00' WHERE AssignID = '343' AND ClassID = '36'

How to repeat:
CREATE TABLE `TIGedAssign` (
  `AssignID` int(11) NOT NULL auto_increment,
  `SpecialID` int(11) NOT NULL default '0',
  `ClassID` int(11) NOT NULL default '0',
  `ActivityID` int(11) NOT NULL default '0',
  `Title` varchar(255) NOT NULL default '',
  `Description` text NOT NULL,
  `StartDate` datetime default NULL,
  `DueDate` datetime default NULL,
  `flTeacherOnly` tinyint(4) NOT NULL default '0',
  `flClosed` tinyint(1) NOT NULL default '0',
  `GameID` int(3) NOT NULL,
  `DateAdded` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`AssignID`)
) ENGINE=MyISAM AUTO_INCREMENT=348 DEFAULT CHARSET=utf8;
[13 Oct 2006 6:04] Michael Furdyk
I think this is a serious bug (updating severity)
[13 Oct 2006 10:17] Sveta Smirnova
Thank you for the report.

Please provide exact error message you get after issuing UPDATE statement which fails and output of SELECT @@sql_mode; statement.
[16 Oct 2006 20:11] Michael Furdyk
As requested:

1) There is no error message. It just says '0 rows affected'

2)  SELECT @@sql_mode; outputs nothing?
[17 Oct 2006 6:41] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read carefully at http://dev.mysql.com/doc/refman/5.0/en/update.html quantity of which rows returns UPDATE statement
[18 Oct 2006 2:48] Michael Furdyk
The issue isn't the number of rows affected, but that changing the fields to be updated (but not the WHERE...) changes whether or not the query works.

The query should work in both cases. It does not work in both cases. The where statement is the same in both!
[18 Oct 2006 6:29] Sveta Smirnova
Could you please issues next statements in specified order and provide output of each of them:

SELECT StartDate FROM tig.TIGedAssign WHERE AssignID = '343'
AND ClassID = '36';

UPDATE tig.TIGedAssign SET StartDate = '2006-10-13 24:00' WHERE AssignID = '343'
AND ClassID = '36';
[23 Oct 2006 5:17] Michael Furdyk
1) SELECT StartDate FROM tig.TIGedAssign WHERE AssignID = '343'
AND ClassID = '36';

returns:
0000-00-00 00:00:00

2) UPDATE tig.TIGedAssign SET StartDate = '2006-10-13 24:00' WHERE AssignID =
'343'
AND ClassID = '36';

returns:
Affected rows: 0 (Query took 0.0005 sec)

3) SELECT StartDate FROM tig.TIGedAssign WHERE AssignID = '343'
AND ClassID = '36';

returns:
0000-00-00 00:00:00
[23 Oct 2006 6:58] Sveta Smirnova
There is nothing to update in your output. It is not a bug as I said above.
[23 Oct 2006 7:41] Michael Furdyk
How is there nothing to update? I clearly selected the DATETIME field, issued an update to it, and it did not change. Why does this work if I add something else to the update, but not if it's just the DATETIME field?
[23 Oct 2006 9:15] Sveta Smirnova
Please read again carefully http://dev.mysql.com/doc/refman/5.0/en/update.html ("UPDATE returns the number of rows that were actually changed.") and http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html ("DATETIME ... The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.")