MySQL Bugs: #46521: can't find rows when date column with primary key

Bug #46521	can't find rows when date column with primary key
Submitted:	3 Aug 2009 6:50	Modified:	3 Sep 2009 7:12
Reporter:	alex tang	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	date primary key update

Description:
### os : centos 5.3 (x86_64) ,version mysql  Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) 

####create table
mysql> create table t (f1 date primary key);
Query OK, 0 rows affected (0.03 sec)

####insert values
mysql> insert into t values ('2009-08-04 00:00:00.0');
Query OK, 1 row affected, 1 warning (0.00 sec)

#####select
mysql> select * from t where f1='2009-08-04 00:00:00.0';
+------------+
| f1         |
+------------+
| 2009-08-04 |
+------------+
1 row in set (0.00 sec)

####update ,ERROR!
mysql> update t set f1='2009-08-05 00:00:00.0' where f1='2009-08-04 00:00:00.0';
Query OK, 0 rows affected (0.00 sec)

#### but the "update t set f1='2009-08-05 00:00:00.0' where f1='2009-08-04'" is corect. 
#### if f1 is not primary key , "update t set f1='2009-08-04 00:00:00.0' where f1='2009-08-05 00:00:00.0';" is also correct!

The same statement execute under 5.0.22 is normal.

How to repeat:
1. date column
2. primary key
3. 'yyyy-mm-dd 00:00:00.0' format

Thank you for the problem report. Please, try to repeat with a newer version and inform about the results. 5.0.84 is relesed already and we do not fix bugs in older versions.

Look:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.79-enterprise-gpl-nt-log MySQL Enterprise Server - Pro Editi
on (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t;
ERROR 1051 (42S02): Unknown table 't'
mysql> create table t (f1 date primary key);
Query OK, 0 rows affected (0.45 sec)

mysql> insert into t values ('2009-08-04 00:00:00.0');
Query OK, 1 row affected (0.14 sec)

mysql> select * from t where f1='2009-08-04 00:00:00.0';
+------------+
| f1         |
+------------+
| 2009-08-04 |
+------------+
1 row in set (0.08 sec)

mysql> update t set f1='2009-08-05 00:00:00.0' where f1='2009-08-04 00:00:00.0';

Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+------------+
| f1         |
+------------+
| 2009-08-05 |
+------------+
1 row in set (0.00 sec)

So in 5.0.79 it already works as expected it seems.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".