Bug #20686 wrong date vs values(date) compare in on duplicate key

• Submitted: 25 Jun 2006 15:20
• Modified: 4 Dec 2006 14:42
• Reporter: Martin Friebe (Gold Quality Contributor) (OCA)
• Status: Duplicate
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.20 and 5.0.22
• OS: Linux (Linux)
• Assigned to: Assigned Account
• CPU Architecture: Any

[25 Jun 2006 15:20] Martin Friebe

Description:
If "insert .. on duplicate key" updates an existing row, it sometimes fails to copmpare the current date value to the new value returned by the values() function.
It seems to fail on the first call of a query, it works on repeated calls of the same query...

This applies to date, time and datetime columns.

As a workaround it is possible to use "cast as date" on the old value from the existing column.(casting the new value, makes no difference)

Here is the sql with DATE column (same results for datetime or time) (for result see how to repeat). The field x is only included to show that the rest of the row get updated correctly (and the select iis not cached).

create table tt1 ( a int not null primary key, b int, d date, x int );
insert into tt1 values (1,0,"2006-06-25",0);

insert into tt1 values (1,2,"2006-06-25",0 ) on duplicate key update b= d=values(d), x=x+1 ; select * from tt1; # wrong
insert into tt1 values (1,2,"2006-06-25",0 ) on duplicate key update b= d=values(d), x=x+1 ; select * from tt1; # good
insert into tt1 values (1,2,"2006-06-25",0 ) on duplicate key update b= d=values(d), x=x+1 ; select * from tt1; # good

insert into tt1 values (1,2,"2006-06-26",0 ) on duplicate key update b= d=values(d), x=x+1 ; select * from tt1; # wrong 
insert into tt1 values (1,2,"2006-06-26",0 ) on duplicate key update b= d=values(d), x=x+1 ; select * from tt1; # good
insert into tt1 values (1,2,"2006-06-26",0 ) on duplicate key update b= d=values(d), x=x+1 ; select * from tt1; # good

insert into tt1 values (1,2,cast("2006-06-25" as date),0) on duplicate key update b= d=values(d) ; select * from tt1; # wrong
insert into tt1 values (1,2,cast("2006-06-25" as date),0) on duplicate key update b= d=values(d) ; select * from tt1; # good

insert into tt1 values (1,2,cast("2006-06-26" as date),0) on duplicate key update b= d=values(d) ; select * from tt1; # wrong
insert into tt1 values (1,2,cast("2006-06-26" as date),0) on duplicate key update b= d=values(d) ; select * from tt1; # good

insert into tt1 values (1,2,"2006-06-25",0 ) on duplicate key update b= d=cast(values(d) as date) ; select * from tt1; # wrong
insert into tt1 values (1,2,"2006-06-25",0 ) on duplicate key update b= d=cast(values(d) as date) ; select * from tt1; # good

# with workaround
insert into tt1 values (1,2,"2006-06-25",0 ) on duplicate key update b= cast(d as date)=values(d) ; select * from tt1; # good
insert into tt1 values (1,2,"2006-06-25",0 ) on duplicate key update b= cast(d as date)=values(d) ; select * from tt1; # good

insert into tt1 values (1,2,"2006-06-26",0 ) on duplicate key update b= cast(d as date)=values(d) ; select * from tt1; # good
insert into tt1 values (1,2,"2006-06-26",0 ) on duplicate key update b= cast(d as date)=values(d) ; select * from tt1; # good

drop table tt1;

How to repeat:
mysql> create table tt1 ( a int not null primary key, b int, d date, x int );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tt1 values (1,0,"2006-06-25",0);
Query OK, 1 row affected (0.01 sec)

# same data / expect b=1
mysql> insert into tt1 values (1,2,"2006-06-25",0 ) on duplicate key update b= d=values(d), x=x+1 ; select * from tt1; # wrong
Query OK, 2 rows affected (0.01 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    0 | 2006-06-25 |    1 |
+---+------+------------+------+
1 row in set (0.00 sec)

mysql> insert into tt1 values (1,2,"2006-06-25",0 ) on duplicate key update b= d=values(d), x=x+1 ; select * from tt1; # good
Query OK, 2 rows affected (0.00 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    1 | 2006-06-25 |    2 |
+---+------+------------+------+
1 row in set (0.00 sec)

mysql> insert into tt1 values (1,2,"2006-06-25",0 ) on duplicate key update b= d=values(d), x=x+1 ; select * from tt1; # good
Query OK, 2 rows affected (0.00 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    1 | 2006-06-25 |    3 |
+---+------+------------+------+
1 row in set (0.00 sec)

# differnt data / expect b=0
mysql> insert into tt1 values (1,2,"2006-06-26",0 ) on duplicate key update b= d=values(d), x=x+1 ; select * from tt1; # wrong
Query OK, 2 rows affected (0.00 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    1 | 2006-06-25 |    4 |
+---+------+------------+------+
1 row in set (0.00 sec)

mysql> insert into tt1 values (1,2,"2006-06-26",0 ) on duplicate key update b= d=values(d), x=x+1 ; select * from tt1; # good
Query OK, 2 rows affected (0.00 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    0 | 2006-06-25 |    5 |
+---+------+------------+------+
1 row in set (0.00 sec)

mysql> insert into tt1 values (1,2,"2006-06-26",0 ) on duplicate key update b= d=values(d), x=x+1 ; select * from tt1; # good
Query OK, 2 rows affected (0.09 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    0 | 2006-06-25 |    6 |
+---+------+------------+------+
1 row in set (0.00 sec)

# make sure the new value is a date, rather than a string
mysql> insert into tt1 values (1,2,cast("2006-06-25" as date),0) on duplicate key update b= d=values(d) ; select * from tt1; # wrong
Query OK, 2 rows affected (0.01 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    0 | 2006-06-25 |    6 |
+---+------+------------+------+
1 row in set (0.00 sec)

mysql> insert into tt1 values (1,2,cast("2006-06-25" as date),0) on duplicate key update b= d=values(d) ; select * from tt1; # good
Query OK, 2 rows affected (0.00 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    1 | 2006-06-25 |    6 |
+---+------+------------+------+
1 row in set (0.00 sec)

mysql>
mysql> insert into tt1 values (1,2,cast("2006-06-26" as date),0) on duplicate key update b= d=values(d) ; select * from tt1; # wrong
Query OK, 2 rows affected (0.00 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    1 | 2006-06-25 |    6 |
+---+------+------------+------+
1 row in set (0.01 sec)

mysql> insert into tt1 values (1,2,cast("2006-06-26" as date),0) on duplicate key update b= d=values(d) ; select * from tt1; # good
Query OK, 2 rows affected (0.00 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    0 | 2006-06-25 |    6 |
+---+------+------------+------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> insert into tt1 values (1,2,"2006-06-25",0 ) on duplicate key update b= d=cast(values(d) as date) ; select * from tt1; # wrong
Query OK, 2 rows affected (0.00 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    0 | 2006-06-25 |    6 |
+---+------+------------+------+
1 row in set (0.00 sec)

mysql> insert into tt1 values (1,2,"2006-06-25",0 ) on duplicate key update b= d=cast(values(d) as date) ; select * from tt1; # good
Query OK, 2 rows affected (0.00 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    1 | 2006-06-25 |    6 |
+---+------+------------+------+
1 row in set (0.00 sec)

# with Workaround
mysql> insert into tt1 values (1,2,"2006-06-25",0 ) on duplicate key update b= cast(d as date)=values(d) ; select * from tt1; # good
Query OK, 2 rows affected (0.00 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    1 | 2006-06-25 |    6 |
+---+------+------------+------+
1 row in set (0.00 sec)

mysql> insert into tt1 values (1,2,"2006-06-25",0 ) on duplicate key update b= cast(d as date)=values(d) ; select * from tt1; # good
Query OK, 2 rows affected (0.00 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    1 | 2006-06-25 |    6 |
+---+------+------------+------+
1 row in set (0.00 sec)

mysql>
mysql> insert into tt1 values (1,2,"2006-06-26",0 ) on duplicate key update b= cast(d as date)=values(d) ; select * from tt1; # good
Query OK, 2 rows affected (0.00 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    0 | 2006-06-25 |    6 |
+---+------+------------+------+
1 row in set (0.00 sec)

mysql> insert into tt1 values (1,2,"2006-06-26",0 ) on duplicate key update b= cast(d as date)=values(d) ; select * from tt1; # good
Query OK, 2 rows affected (0.00 sec)

+---+------+------------+------+
| a | b    | d          | x    |
+---+------+------------+------+
| 1 |    0 | 2006-06-25 |    6 |
+---+------+------------+------+
1 row in set (0.00 sec)

mysql> drop table tt1;
Query OK, 0 rows affected (0.01 sec)

Suggested fix:
-

[26 Jun 2006 12:47] Sveta Smirnova

Thank you for the report. I've changed OS version, because it is multi-platform bug.

Additional info:
I can repeat it on Solaris 10 and MySQL 5.0.23 built from last bk sources; Linux and MySQL 4.1.21, MySQL 5.0.23 built from last bk sources. But can not repeat on Windows XP SP2 and MySQL 5.0.23 built from last bk sources.

[4 Dec 2006 14:42] Alexey Kopytov

This bug is a duplicate of bug #21555, so was fixed in 4.1.22, 5.0.26 and 5.1.12.