Bug #72731 "equal" returns a result that is not returned by "greater than or equal"
Submitted: 23 May 2014 19:51 Modified: 23 Jun 2014 19:57
Reporter: Luis Fernando Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.47 OS:Linux
Assigned to: CPU Architecture:Any

[23 May 2014 19:51] Luis Fernando
Description:
mysql> select * from consolidation where id_cr=2733 and date_start >= '2014-05-01 00:00:00.0' and date_start <= '2014-05-01 00:00:05' and id_product=21;
Empty set (0.00 sec)

mysql> select * from consolidation where id_cr=2733 and date_start >= '2014-05-01 00:00:00.0' and date_start <= '2014-05-01 00:00:05' and id_product>=21;
Empty set (0.00 sec)

mysql> select * from consolidation where id_cr=2733 and date_start >= '2014-05-01 00:00:00.0' and date_start <= '2014-05-01 00:00:05' and id_product>20;
+--------+-------+------------+---------------------+---------------------+-----------+----------+-----------+
| id     | id_cr | id_product | date_start          | date_end            | cons      | charged  | total     |
+--------+-------+------------+---------------------+---------------------+-----------+----------+-----------+
| 660712 |  2733 |         21 | 2014-05-01 00:00:00 | 2014-05-01 01:00:00 |  2.100000 | 3.000000 | 15.000000 |
+--------+-------+------------+---------------------+---------------------+-----------+----------+-----------+
1 row in set (0.00 sec)

mysql> select * from consolidation where id_cr=2733 and date_start >= '2014-05-01 00:00:00' and date_start <= '2014-05-01 00:00:05' and id_product=21;
+--------+-------+------------+---------------------+---------------------+-----------+----------+-----------+
| id     | id_cr | id_product | date_start          | date_end            | cons      | charged  | total     |
+--------+-------+------------+---------------------+---------------------+-----------+----------+-----------+
| 660712 |  2733 |         21 | 2014-05-01 00:00:00 | 2014-05-01 01:00:00 |  2.100000 | 3.000000 | 15.000000 |
+--------+-------+------------+---------------------+---------------------+-----------+----------+-----------+
1 row in set (0.00 sec)

mysql> select * from consolidation where id_cr=2733 and date_start >= '2014-05-01 00:00:00' and date_start <= '2014-05-01 00:00:05' and id_product>=21;
+--------+-------+------------+---------------------+---------------------+-----------+----------+-----------+
| id     | id_cr | id_product | date_start          | date_end            | cons      | charged  | total     |
+--------+-------+------------+---------------------+---------------------+-----------+----------+-----------+
| 660712 |  2733 |         21 | 2014-05-01 00:00:00 | 2014-05-01 01:00:00 |  2.100000 | 3.000000 | 15.000000 |
+--------+-------+------------+---------------------+---------------------+-----------+----------+-----------+
1 row in set (0.01 sec)

mysql> select * from consolidation where id_cr=2733 and date_start >= '2014-05-01 00:00:00' and date_start <= '2014-05-01 00:00:05' and id_product>20;
+--------+-------+------------+---------------------+---------------------+-----------+----------+-----------+
| id     | id_cr | id_product | date_start          | date_end            | cons      | charged  | total     |
+--------+-------+------------+---------------------+---------------------+-----------+----------+-----------+
| 660712 |  2733 |         21 | 2014-05-01 00:00:00 | 2014-05-01 01:00:00 |  2.100000 | 3.000000 | 15.000000 |
+--------+-------+------------+---------------------+---------------------+-----------+----------+-----------+
1 row in set (0.02 sec)

mysql> describe consolidation;
+------------+---------------+------+-----+---------------------+----------------+
| Field      | Type          | Null | Key | Default             | Extra          |
+------------+---------------+------+-----+---------------------+----------------+
| id         | int(11)       | NO   | PRI | NULL                | auto_increment |
| id_cr      | int(11)       | NO   | MUL | NULL                |                |
| id_product | int(11)       | NO   | MUL | NULL                |                |
| date_start | timestamp     | NO   |     | 1971-01-01 03:00:00 |                |
| date_end   | timestamp     | YES  |     | NULL                |                |
| cons       | decimal(18,6) | NO   |     | NULL                |                |
| charged    | decimal(18,6) | NO   |     | NULL                |                |
| total      | decimal(18,6) | YES  |     | NULL                |                |
+------------+---------------+------+-----+---------------------+----------------+

How to repeat:
Unfortunatelly I couldn't produce a self-contained-example. Hopefully the provided info can help.

Suggested fix:
I'd bet this problem is related to index.
[23 May 2014 19:57] MySQL Verification Team
Thank you for the bug report. Your server is quite older the latest release of
5.1 is 5.1.72 please try it but better to test 5.5/5.6 last release. If still
repeatable please provide a complete test case. Thanks.
[24 Jun 2014 1:00] Bugs System
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".