| Bug #39423 | comparison of NULL datetime column side-effected in trigger body are incorrect | ||
|---|---|---|---|
| Submitted: | 12 Sep 2008 16:35 | Modified: | 30 Oct 2008 8:14 |
| Reporter: | Malcolm Cook | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.0.67-community, 5.0, 5.1, 6.0 bzr | OS: | Linux (x86_64 GNU/Linux CentOS) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | datetime, null, regression, trigger | ||
[15 Sep 2008 6:41]
Sveta Smirnova
Thank you for the report. Verified as described: .... select t.*, @c2info, @c3info from t;// c1 c2 c3 @c2info @c3info x 2008-09-15 09:38:19 NEW TRUE: ??? <=> 2008-09-15 09:38:19 FALSE: ??? <=> NEW Problem was introduced in Community version since 5.0.45 and seems to be side effect of fix for bug #28261
[15 Sep 2008 13:07]
Malcolm Cook
I don't think it was introduced in 5.0.45 since I first characterized the issue in 5.0.22 and then upgraded to 5.0.45 prior to reporting it in the hopes that it was fixed possibly as side effect of other reported fixes having to do with comparing datetime variables.
[29 Sep 2008 13:50]
Georgi Kodinov
Probably related to Bug #37526 ?
[8 Oct 2008 19:01]
Sveta Smirnova
Bug #39909 was marked as duplicate of this one.
[30 Oct 2008 8:14]
Ramil Kalimullin
See bug #37526: "asymertic operator <=> in trigger"
Description: In a BEFORE UPDATE trigger the comparison of a column's NEW and OLD value which has been declared as a NULLable datetime and has been set earlier in a trigger as a side-effect does not return the correct value A similar comparison made when the datatype is varchar does NOT exhibit the same semantics. In the "How To Repeat" section, we see that the SAME logic yields different results for varchar as for datetime. This issue appears possibly related to issues addressed in #28261 How to repeat: drop table t;// create table t ( c1 varchar(10), c2 datetime NULL, c3 varchar(10) NULL );// insert t values ('a', NULL, NULL);// drop trigger T_T_BU;// CREATE TRIGGER T_T_BU BEFORE UPDATE on t FOR EACH ROW begin set new.c2 = now(); set new.c3 = 'NEW'; set @c2info = concat(IFNULL(old.c2,'???'), ' <=> ', IFNULL(new.c2,'???')); set @c3info = concat(IFNULL(old.c3,'???'), ' <=> ', IFNULL(new.c3,'???')); if (new.c2 <=> old.c2) then set @c2info = concat("TRUE: ", @c2info); else set @c2info = concat("FALSE: ", @c2info); end if; if (new.c3 <=> old.c3) then set @c3info = concat("TRUE: ", @c3info); else set @c3info = concat("FALSE: ", @c3info); end if; end;// update t set c1 = 'x';// select t.*, @c2info, @c3info from t;// +------+---------------------+------+-----------------------------------+--------------------+ | c1 | c2 | c3 | @c2info | @c3info | +------+---------------------+------+-----------------------------------+--------------------+ | x | 2008-09-12 11:00:19 | NEW | TRUE: ??? <=> 2008-09-12 11:00:19 | FALSE: ??? <=> NEW | +------+---------------------+------+-----------------------------------+--------------------+ Suggested fix: Make the semantics between the datatypes the same and document which it should be.