| Bug #37526 | asymertic operator <=> in trigger | ||
|---|---|---|---|
| Submitted: | 19 Jun 2008 15:11 | Modified: | 17 Oct 2008 17:35 |
| Reporter: | Adam Charzewski | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.0.51a, 5.0, 5.1, 6.0 | OS: | Any (MS Windows, Linux) |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
| Tags: | opearator, regression, trigger | ||
[19 Jun 2008 16:46]
Sveta Smirnova
Thank you for the report. Verified as described.
[19 Jun 2008 16:51]
Sveta Smirnova
Bug was introduced in verion 5.0.42
[30 Jun 2008 9:58]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/48725 2642 Ramil Kalimullin 2008-06-30 Fix for bug#37526: asymertic operator <=> in trigger Problem: <=> operator may return wrong results comparing NULL and a DATE/DATETIME/TIME value. Fix: properly check NULLs.
[9 Sep 2008 15:06]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/53619 2684 Ramil Kalimullin 2008-09-09 Fix for bug#37526: asymertic operator <=> in trigger Problem: <=> operator may return wrong results comparing NULL and a DATE/DATETIME/TIME value. Fix: properly check NULLs.
[9 Sep 2008 16:10]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/53629 2684 Ramil Kalimullin 2008-09-09 Fix for bug#37526: asymertic operator <=> in trigger Problem: <=> operator may return wrong results comparing NULL and a DATE/DATETIME/TIME value. Fix: properly check NULLs.
[15 Sep 2008 8:09]
Bugs System
Pushed into 5.0.70 (revid:ramil@mysql.com-20080909150527-3lqet6tvnqbingq5) (version source revid:kgeorge@mysql.com-20080910094058-fygie2nur8py7y8j) (pib:3)
[15 Sep 2008 8:18]
Bugs System
Pushed into 5.1.29 (revid:ramil@mysql.com-20080909150527-3lqet6tvnqbingq5) (version source revid:kgeorge@mysql.com-20080910094421-1i1kxv3n1bxskiqa) (pib:3)
[15 Sep 2008 18:36]
Paul DuBois
Noted in 5.0.70, 5.1.29 changelogs. The <=> operator could return incorrect results when comparing NULL to DATE, TIME, or DATETIME values. Setting report to NDI pending push into 6.0.x.
[1 Oct 2008 15:54]
Bugs System
Pushed into 5.1.29 (revid:ramil@mysql.com-20080909150527-3lqet6tvnqbingq5) (version source revid:kgeorge@mysql.com-20080910094421-1i1kxv3n1bxskiqa) (pib:4)
[1 Oct 2008 17:12]
Paul DuBois
Setting report to NDI pending push into 6.0.x.
[17 Oct 2008 16:41]
Bugs System
Pushed into 6.0.8-alpha (revid:ramil@mysql.com-20080909150527-3lqet6tvnqbingq5) (version source revid:kpettersson@mysql.com-20080911114255-81pt7q1uvl1fkojq) (pib:5)
[17 Oct 2008 17:35]
Paul DuBois
Noted in 6.0.8 changelog.
[28 Oct 2008 21:01]
Bugs System
Pushed into 5.1.29-ndb-6.2.17 (revid:ramil@mysql.com-20080909150527-3lqet6tvnqbingq5) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:20]
Bugs System
Pushed into 5.1.29-ndb-6.3.19 (revid:ramil@mysql.com-20080909150527-3lqet6tvnqbingq5) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[30 Oct 2008 8:15]
Ramil Kalimullin
Bug #39423: "comparison of NULL datetime column side-effected in trigger body are incorrect" marked as a duplicate of this one.
[1 Nov 2008 9:45]
Bugs System
Pushed into 5.1.29-ndb-6.4.0 (revid:ramil@mysql.com-20080909150527-3lqet6tvnqbingq5) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)
Description: When using operator <=> in a trigger the result is different according to operand order. e.g. OLD.x<=>NEW.x have different result then NEW.x<=>OLD.x How to repeat: Table to test trigger: CREATE TABLE `t` ( `ID` int(10) unsigned NOT NULL auto_increment, `TestDate` date default NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin2 Table to raport result: CREATE TABLE `tchange` ( `ID` int(10) unsigned NOT NULL auto_increment, `From` date default NULL, `To` date default NULL, `Dsc` varchar(45) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin2 Trigger that log chaanges in field TestData and insert result into table tchange: DELIMITER | CREATE TRIGGER ModyfiT AFTER UPDATE ON t FOR EACH ROW BEGIN IF (not(OLD.TestDate<=>NEW.TestDate)) THEN INSERT INTO tchange (`From`,`To`,`Dsc`) VALUES (OLD.TestDate,NEW.TestDate,'Old<=>New'); END IF; IF (not(NEW.TestDate<=>OLD.TestDate)) THEN INSERT INTO tchange (`From`,`To`,`Dsc`) VALUES (OLD.TestDate,NEW.TestDate,'New<=>Old'); END IF; END; | DELIMITER ; Record for modyfi: INSERT INTO t (`TestDate`) VALUES (null); -------------------- tchange is empty. Change TestDate from null to '2008-01-01': mysql> UPDATE t SET TestDate='2008-01-01' where ID=1; Query OK, 1 row affected (0.25 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * FROM tchange; +----+------+------------+-----------+ | ID | From | To | Dsc | +----+------+------------+-----------+ | 1 | NULL | 2008-01-01 | Old<=>New | +----+------+------------+-----------+ 1 row in set (0.00 sec) So we have only one record in result table instead of two. So OLD.TestDate<=>NEW.TestDate was false and NEW.TestDate<=>OLD.TestDate must be true. To confirm we could do following: mysql> UPDATE t SET TestDate=null where ID=1; Query OK, 1 row affected (0.20 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * FROM tchange; +----+------------+------------+-----------+ | ID | From | To | Dsc | +----+------------+------------+-----------+ | 1 | NULL | 2008-01-01 | Old<=>New | | 2 | 2008-01-01 | NULL | New<=>Old | +----+------------+------------+-----------+ 2 rows in set (0.00 sec) Suggested fix: Use: not(NEW.TestDate<=>OLD.TestDate) or not(OLD.TestDate<=>NEW.TestDate) Instead of not(NEW.TestDate<=>OLD.TestDate)