Bug #48743 Incomplete data (date) set while querying InnoDB table
Submitted: 12 Nov 2009 20:43 Modified: 13 Nov 2009 21:14
Reporter: Suresh Shanmughom Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.1.39, 5.1.40 OS:Linux (Incomplete data (date) set while querying InnoDB table)
Assigned to: CPU Architecture:Any
Tags: Incomplete data (date) set while querying InnoDB table

[12 Nov 2009 20:43] Suresh Shanmughom
Description:
Getting incomplete data set while querying InnoDB table. Looks good with MyIsam on the same version.

We tried only with Version: 5.1.39 and 5.1.40 and both produce the same incompleted data (date) set.

To make it more clear:
mysql> select * from bugtest;
+------------+
| dt         |
+------------+
| 2009-10-25 |
| 2009-10-26 |
| 2009-10-27 |
+------------+
3 rows in set (0.00 sec)

mysql> select * from bugtest where dt >= '20091025';
+------------+
| dt         |
+------------+
| 2009-10-26 |
| 2009-10-27 |
+------------+

**** NOTE: 2009-10-25 is missing from the above query ****

2 rows in set (0.00 sec)

mysql> select * from bugtest where dt >= '2009-10-25';
+------------+
| dt         |
+------------+
| 2009-10-25 |
| 2009-10-26 |
| 2009-10-27 |
+------------+
3 rows in set (0.00 sec)

We have another server with MySQL 5.0.45, no problems with that innodb table.

Please let us know how to resolve this.

How to repeat:
mysql> create table bugtest (dt date not null, primary key (dt) ) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table bugtest\G
*************************** 1. row ***************************
       Table: bugtest
Create Table: CREATE TABLE `bugtest` (
  `dt` date NOT NULL,
  PRIMARY KEY (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into bugtest values ('20091025'),('20091026'),('20091027');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from bugtest;
+------------+
| dt         |
+------------+
| 2009-10-25 |
| 2009-10-26 |
| 2009-10-27 |
+------------+
3 rows in set (0.00 sec)

mysql> select * from bugtest where dt >= '20091025';
+------------+
| dt         |
+------------+
| 2009-10-26 |
| 2009-10-27 |
+------------+
2 rows in set (0.00 sec)

mysql> select * from bugtest where dt >= '2009-10-25';
+------------+
| dt         |
+------------+
| 2009-10-25 |
| 2009-10-26 |
| 2009-10-27 |
+------------+
3 rows in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.40-community |
+------------------+
1 row in set (0.01 sec)

Suggested fix:
Don't Know !
[12 Nov 2009 21:06] Miguel Solorzano
Thank you for the bug report. I couldn't repeat with server built from current source. Please wait next release:

miguel@quetzal3:~/dbs$ 5.0/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2                            
Server version: 5.1.42-debug Source distribution         

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

mysql> CREATE TABLE `bugtest` (
    ->   `dt` date NOT NULL,   
    ->   PRIMARY KEY (`dt`)    
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)        

mysql> show create table bugtest\G
*************************** 1. row ***************************
       Table: bugtest                                         
Create Table: CREATE TABLE `bugtest` (                        
  `dt` date NOT NULL,                                         
  PRIMARY KEY (`dt`)                                          
) ENGINE=InnoDB DEFAULT CHARSET=utf8                          
1 row in set (0.00 sec)                                       

mysql> insert into bugtest values ('20091025'),('20091026'),('20091027');
Query OK, 3 rows affected (0.00 sec)                                     
Records: 3  Duplicates: 0  Warnings: 0                                   

mysql> select * from bugtest;
+------------+               
| dt         |               
+------------+               
| 2009-10-25 |               
| 2009-10-26 |               
| 2009-10-27 |               
+------------+               
3 rows in set (0.00 sec)     

mysql> select * from bugtest where dt >= '20091025';
+------------+                                      
| dt         |                                      
+------------+                                      
| 2009-10-25 |                                      
| 2009-10-26 |                                      
| 2009-10-27 |
+------------+
3 rows in set (0.00 sec)

mysql> select * from bugtest where dt >= '2009-10-25';
+------------+
| dt         |
+------------+
| 2009-10-25 |
| 2009-10-26 |
| 2009-10-27 |
+------------+
3 rows in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.42-debug |
+--------------+
1 row in set (0.00 sec)

mysql>
[12 Nov 2009 21:21] Shane Bester
duplicate of bug #47925
[13 Nov 2009 21:14] Suresh Shanmughom
Thanks for the prompt reply.