Bug #16249 Different results for query with and without indexes

• Submitted: 6 Jan 2006 12:05
• Modified: 19 Sep 2006 16:38
• Reporter: Gleb Paharenko
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.0.18/5.0.19 BK
• OS: Linux (Linux)
• Assigned to: Igor Babaev
• CPU Architecture: Any

[6 Jan 2006 12:05] Gleb Paharenko

Description:
See how to repeat. First reported at:
 http://lists.mysql.com/mysql/193576

How to repeat:
CREATE TABLE `price_log` (                                      
  `Item` char(20) NOT NULL default '',                          
  `Started` datetime NOT NULL default '0000-00-00 00:00:00', 
  `Price` decimal(16,3) NOT NULL default '0.000',                 
  PRIMARY KEY  (`Item`,`Started`)                     
) ENGINE=MyISAM;   

insert into price_log values
("A1","2005-11-01 08:00:00",1000),
("A1","2005-11-15 00:00:00",2000),
("A1","2005-12-12 08:00:00",3000),
("A2","2005-12-01 08:00:00",1000);

mysql> select *
    -> from price_log
    -> where
    -> item="A1"
    -> and started<="2005-12-01 24:00:00";
Empty set, 2 warnings (0.00 sec)

mysql> drop index `PRIMARY` on price_log;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from price_log where item="A1" and started<="2005-12-01 24:00:00";
+------+---------------------+----------+
| Item | Started             | Price    |
+------+---------------------+----------+
| A1   | 2005-11-01 08:00:00 | 1000.000 |
| A1   | 2005-11-15 00:00:00 | 2000.000 |
+------+---------------------+----------+
2 rows in set, 1 warning (0.00 sec)

After dropping the index we're getting results, while with index we're getting an empty set!

Suggested fix:
Fix this, so resutls with index and without index are the same.

[6 Jan 2006 12:52] MySQL Verification Team

Thank you for the bug report.

Possibly related to bug: http://bugs.mysql.com/bug.php?id=16168

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `price_log` (                                      
    ->   `Item` char(20) NOT NULL default '',                          
    ->   `Started` datetime NOT NULL default '0000-00-00 00:00:00', 
    ->   `Price` decimal(16,3) NOT NULL default '0.000',                 
    ->   PRIMARY KEY  (`Item`,`Started`)                     
    -> ) ENGINE=MyISAM;   
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> insert into price_log values
    -> ("A1","2005-11-01 08:00:00",1000),
    -> ("A1","2005-11-15 00:00:00",2000),
    -> ("A1","2005-12-12 08:00:00",3000),
    -> ("A2","2005-12-01 08:00:00",1000);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select *
    -> from price_log
    -> where
    -> item="A1"
    -> and started<="2005-12-01 24:00:00";
Empty set, 2 warnings (0.02 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------+
| Level   | Code | Message                                                                       |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2005-12-01 24:00:00' for column 'Started' at row 1 |
| Warning | 1292 | Incorrect datetime value: '2005-12-01 24:00:00' for column 'Started' at row 1 |
+---------+------+-------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select *
    -> from price_log
    -> where
    -> item="A1"
    -> and started<="2005-12-01 23:59:00";
+------+---------------------+----------+
| Item | Started             | Price    |
+------+---------------------+----------+
| A1   | 2005-11-01 08:00:00 | 1000.000 |
| A1   | 2005-11-15 00:00:00 | 2000.000 |
+------+---------------------+----------+
2 rows in set (0.00 sec)

mysql> drop index `PRIMARY` on price_log;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select *
    -> from price_log
    -> where
    -> item="A1"
    -> and started<="2005-12-01 24:00:00";
+------+---------------------+----------+
| Item | Started             | Price    |
+------+---------------------+----------+
| A1   | 2005-11-01 08:00:00 | 1000.000 |
| A1   | 2005-11-15 00:00:00 | 2000.000 |
+------+---------------------+----------+
2 rows in set, 1 warning (0.01 sec)

mysql>

[31 Aug 2006 14:27] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11167

ChangeSet@1.2272, 2006-08-31 07:27:34-07:00, [e-mail] +4 -0
  Fixed bug #16249: different results for a range with an without index 
  when a range condition use an invalid DATETIME constant.
  Now we do not use invalid DATETIME constants to form end keys for
  range intervals: range analysis just ignores predicates with such
  constants.

[4 Sep 2006 14:28] Magnus Blåudd

Pushed to 5.0.25

[13 Sep 2006 2:44] Paul DuBois

Noted in 5.0.25 changelog.

[18 Sep 2006 21:49] Timothy Smith

Pushed to 5.1.12

[19 Sep 2006 16:38] Paul DuBois

Noted in 5.1.12 changelog.