| Bug #81970 | INSERT/UPDATE/WHERE date-only fields do not work cause of eroneous SQL statement | ||
|---|---|---|---|
| Submitted: | 22 Jun 2016 10:08 | Modified: | 11 Nov 2016 22:11 |
| Reporter: | Mark Jonckheere | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL for Windows: MySQL for Excel | Severity: | S1 (Critical) |
| Version: | 1.3.6 | OS: | Windows (Windows 10) |
| Assigned to: | CPU Architecture: | Any | |
[22 Jun 2016 11:19]
Chiranjeevi Battula
Hello Mark Jonckheere, Thank you for the bug report . Verified this behavior on MS Excel 2013 with MySQL for Excel 1.3.6. Thanks, Chiranjeevi
[22 Jun 2016 11:19]
Chiranjeevi Battula
Screenshot.
Attachment: 81970.JPG (image/jpeg, text), 202.58 KiB.
[30 Jun 2016 6:37]
Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=82064 marked as duplicate of this one.
[12 Aug 2016 16:25]
Léonard Maguin
Hey Mark have you found any workaround? It's a very annoying issue :-(
[20 Sep 2016 11:57]
Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=83042 marked as duplicate of this one.
[11 Nov 2016 22:11]
Javier Treviño
Treating this as a bug of bug 80079.
Description: If the mysql table contains a field "Date-only-field-name" Then the sql statement generated is wrong Eg : UPDATE <table> SET <Date-only-field-name>=2016-01-01 00:00:00 does not work - Fix : UPDATE <table> SET <Date-only-field-name>='2016-01-01' Impossible to modify date-only fields How to repeat: >describe programma; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Datum | date | YES | | NULL | | | Programma | varchar(2) | YES | | NULL | | | Info | varchar(32) | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ INSERT INTO `xibo`.`programma` (`ID`,`Datum`,`Programma`,`Info`) VALUES (9,2016-06-27 00:00:00,'P3','Test'); UPDATE `xibo`.`programma` SET `Datum`=2016-06-28 00:00:00 WHERE `ID`=9 AND ((2016-06-27 00:00:00 IS NULL AND `Datum` IS NULL) OR `Datum`=2016-06-27 00:00:00) AND (('P3' IS NULL AND `Programma` IS NULL) OR `Programma`='P3') AND (('Test' IS NULL AND `Info` IS NULL) OR `Info`='Test'); Suggested fix: INSERT INTO `xibo`.`programma` (`ID`,`Datum`,`Programma`,`Info`) VALUES (9,2016-06-27 00:00:00,'P3','Test'); S.H.O.U.L.D Become --> INSERT INTO `xibo`.`programma` (`ID`,`Datum`,`Programma`,`Info`) VALUES (9,'2016-06-27','P3','Test'); UPDATE `xibo`.`programma` SET `Datum`=2016-06-28 00:00:00 WHERE `ID`=9 AND ((2016-06-27 00:00:00 IS NULL AND `Datum` IS NULL) OR `Datum`=2016-06-27 00:00:00) AND (('P3' IS NULL AND `Programma` IS NULL) OR `Programma`='P3') AND (('Test' IS NULL AND `Info` IS NULL) OR `Info`='Test'); S.H.O.U.L.D Become --> UPDATE `xibo`.`programma` SET `Datum`='2016-06-28' WHERE `ID`=9 AND (('2016-06-27' IS NULL AND `Datum` IS NULL) OR `Datum`='2016-06-27') AND (('P3' IS NULL AND `Programma` IS NULL) OR `Programma`='P3') AND (('Test' IS NULL AND `Info` IS NULL) OR `Info`='Test');