| Bug #48639 | NULL data in Timestamp column causes incorrect results | ||
|---|---|---|---|
| Submitted: | 9 Nov 2009 14:46 | Modified: | 12 Nov 2009 7:49 |
| Reporter: | Yagnesh Chawda | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S1 (Critical) |
| Version: | 5.0.45 | OS: | Linux (RHEL 5-64 Bit) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Empty Result Set, INDEX, null, timestamp | ||
[9 Nov 2009 15:31]
Yagnesh Chawda
Forgot to add create script for table, here it is:
CREATE TABLE `test_bug` (
`ID` varchar(36) character set ascii NOT NULL default '',
`NAME` varchar(64) default NULL,
`JOINED_DATE` timestamp NULL default NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[9 Nov 2009 15:39]
Valeriy Kravchuk
Thank you for the problem report. Please, try to repeat with a newer version, 5.0.86 at least, and inform about the results. Look:
77-52-7-73:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.88-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `test_bug` (
-> `ID` varchar(36) character set ascii NOT NULL default '',
-> `NAME` varchar(64) default NULL,
-> `JOINED_DATE` timestamp NULL default NULL,
-> PRIMARY KEY (`ID`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into `test_bug`(`ID`,`NAME`,`JOINED_DATE`) values
-> ('1','Yagnesh','2009-11-02 13:49:00'),
-> ('2','Ankur','2009-11-09 13:49:00'),
-> ('3','Sonal',NULL),
-> ('4','Roshan',NULL),
-> ('5','Ambi','2009-11-02 13:49:00'),
-> ('6','Jigar',NULL);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select id , name, date_format(joined_date, '%d-%b-%y') from test_bug where
-> date(joined_date) between '2009-10-01' and '2009-11-09' order by joined_date asc;
+----+---------+--------------------------------------+
| id | name | date_format(joined_date, '%d-%b-%y') |
+----+---------+--------------------------------------+
| 1 | Yagnesh | 02-Nov-09 |
| 5 | Ambi | 02-Nov-09 |
| 2 | Ankur | 09-Nov-09 |
+----+---------+--------------------------------------+
3 rows in set (0.00 sec)
mysql> select id , name, date_format(joined_date, '%d-%b-%y') from test_bug where
-> date(joined_date) between '2009-10-01' and '2009-11-09' order by joined_date desc;
+----+---------+--------------------------------------+
| id | name | date_format(joined_date, '%d-%b-%y') |
+----+---------+--------------------------------------+
| 2 | Ankur | 09-Nov-09 |
| 1 | Yagnesh | 02-Nov-09 |
| 5 | Ambi | 02-Nov-09 |
+----+---------+--------------------------------------+
3 rows in set (0.00 sec)
So, I suspect that this problem is already fixed.
[11 Nov 2009 20:38]
Yagnesh Chawda
Hi, Thanks for the quick response. Its working fine with 5.0.84 as well ;) Did not get chance to test on 5.0.86 or later as per ur suggestion.
[12 Nov 2009 7:49]
Valeriy Kravchuk
Not repeatable with current versions.
Description: Overall, it seems like to be an issue when we have some NULL values in "timestamp" column in a table. I am running RHEL 5, 64 Bit, Intel Quad core processor, with MySql server 5.0.45- 64 Bit version, total 8 GB Ram, with 3GB reserved for InnoDB. Strange part is that, some of the issues I could reproduce in my test environment, some I could not. But one thing is sure that its some serious issue with how indexing, order by and limit are working with each other. You can even try some more combination, and surely you will find more new cases not listed here. How to repeat: Queries Ran : 1. select id , name, date_format(joined_date, '%d-%b-%y') from test_bug where date(joined_date) between '2009-10-01' and '2009-11-09' order by joined_date asc; 2. select id , name, date_format(joined_date, '%d-%b-%y') from test_bug where date(joined_date) between '2009-10-01' and '2009-11-09' order by joined_date desc; Scenarios: 1. Data set #1: delete from test_bug; insert into `test_bug`(`ID`,`NAME`,`JOINED_DATE`) values ('1','Yagnesh','2009-11-02 13:49:00'),('2','Ankur','2009-11-09 13:49:00'),('3','Sonal',NULL),('4','Roshan',NULL),('5','Ambi','2009-11-02 13:49:00'),('6','Jigar',NULL); Results: Query 1: Returned First 2 records only for 'Yagnesh' and 'Ankur' Query 2: Returned First 2 records only for 'Yagnesh' and 'Ankur' 2. Data set #2: delete from test_bug; insert into `test_bug`(`ID`,`NAME`,`JOINED_DATE`) values ('1','Yagnesh','2009-11-02 13:49:00'),('2','Ankur',NULL),('3','Sonal','2009-11-09 13:49:00'),('4','Roshan',NULL),('5','Ambi','2009-11-02 13:49:00'),('6','Jigar',NULL); Results: Query 1: Returned First record only for 'Yagnesh' Query 2: Returned First record only for 'Yagnesh' 3. Data set #3: delete from test_bug; insert into `test_bug`(`ID`,`NAME`,`JOINED_DATE`) values ('1','Yagnesh',NULL),('2','Ankur','2009-11-02 13:49:00'),('3','Sonal','2009-11-09 13:49:00'),('4','Roshan',NULL),('5','Ambi','2009-11-02 13:49:00'),('6','Jigar',NULL); Results: Query 1: Returned Empty set Query 2: Returned Empty set -------------------------------------------------------------------------------------------------------------- With Indexing: just create index on 'joined_date' as create index test_bug_JOINED_DATE on test_bug ( JOINED_DATE ); We use same Queries yet. 1. Now with the same data as in Data set #1, : Results: Query 1: Returned Correct result with 3 records Query 2: Returned Empty set 2. Now with the same data as in Data set #2 : Results: Query 1: Returned Correct result with 3 records Query 2: Returned Empty set 3. Now with the same data as in Data set #3, : Results: Query 1: Returned Correct result with 3 records Query 2: Returned Empty set Run the same queries again, with adding extra null check condition for joined_date as below, you will NOTICE different results than just above: Query 3. select id , name, date_format(joined_date, '%d-%b-%y') from test_bug where joined_date is not null and date(joined_date) between '2009-10-01' and '2009-11-09' order by joined_date asc; Query 4. select id , name, date_format(joined_date, '%d-%b-%y') from test_bug where joined_date is not null and date(joined_date) between '2009-10-01' and '2009-11-09' order by joined_date desc; -------------------------------------------------------------------------------------------------------------- One more Problem: In my production Table with data, having almost same structure and index, (it has some more columns and more data of 1.75 lacs records), almost all above issues are same except one thing. => If I run similar query on that table, following are the results which I could NOT reproduce with the smaller data set i have here: 1: with "order by joined_date desc limit 20" : Returns empty set 2: with "order by joined_date desc " : Returns Correct result 3: with "order by joined_date asc" : Returns empty set 4: with "order by joined_date asc limit 20" : Returns empty set => If I run similar query with 1: with "where joined_date is not null order by joined_date desc limit 20" : Returns Correct result 2: with "where joined_date is not order by joined_date desc " : Returns Correct result 3: with "where joined_date is not order by joined_date asc" : Returns Correct result 4: with "where joined_date is not order by joined_date asc limit 20" : Returns Correct result -- Thanks And Regards, Yagnesh Chawda. email: yagnesh.chawda@gmail.com Mob. +91-9930821649 Res. +91-22-28920883 *************************************************** Good planets are hard to find. Print less! Save energy to keep your future bright !! ***************************************************