| 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: | |
| 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 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.
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 !