Bug #18967 BETWEEN clause returns wrong resultset sometimes

• Submitted: 10 Apr 2006 16:24
• Modified: 20 Apr 2006 13:01
• Reporter: Oliver Gutperl
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.18
• OS: Linux (Linux Debian 2.6.8)
• CPU Architecture: Any

[10 Apr 2006 16:24] Oliver Gutperl

Description:
When using a BETWEEN clause in a SELECT-statement with dates, the correct resultset is returned only in some cases (has to do with the year).

How to repeat:
CREATE TABLE `mysqlbug` (
`somekey` INT NOT NULL ,
`created` DATETIME NOT NULL
) TYPE = innodb;

INSERT INTO `mysqlbug` ( `somekey` , `created` )
VALUES (
'1', '2006-04-01 17:47:00'
);

INSERT INTO `mysqlbug` ( `somekey` , `created` )
VALUES (
'1', '2006-02-01 10:00:00'
);

This statement returns no results (wrong behavior):

SELECT * 
FROM mysqlbug m1, mysqlbug m2
WHERE m2.created 
BETWEEN '2006-01-01 00:00:00'
AND m1.created
AND m1.somekey = m2.somekey;

This statement returns three rows (correct behavior):

SELECT * 
FROM mysqlbug m1, mysqlbug m2
WHERE m2.created 
BETWEEN '2005-12-31 00:00:00'
AND m1.created
AND m1.somekey = m2.somekey;

[20 Apr 2006 11:08] Oliver Gutperl

Do you need any more information?

[20 Apr 2006 12:56] MySQL Verification Team

Thank you for the feedback. I was waiting for the bug:
http://bugs.mysql.com/bug.php?id=14360 to be pushed its
pacth. I will test now.

[20 Apr 2006 13:01] MySQL Verification Team

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

miguel@hegel:~/dbs/5.0> 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 to server version: 5.0.21-debug

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

mysql> CREATE TABLE `mysqlbug` (
    -> `somekey` INT NOT NULL ,
    -> `created` DATETIME NOT NULL
    -> ) TYPE = innodb;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> INSERT INTO `mysqlbug` ( `somekey` , `created` )
    -> VALUES (
    -> '1', '2006-04-01 17:47:00'
    -> );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `mysqlbug` ( `somekey` , `created` )
    -> VALUES (
    -> '1', '2006-02-01 10:00:00'
    -> );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * 
    -> FROM mysqlbug m1, mysqlbug m2
    -> WHERE m2.created 
    -> BETWEEN '2006-01-01 00:00:00'
    -> AND m1.created
    -> AND m1.somekey = m2.somekey;
+---------+---------------------+---------+---------------------+
| somekey | created             | somekey | created             |
+---------+---------------------+---------+---------------------+
|       1 | 2006-04-01 17:47:00 |       1 | 2006-04-01 17:47:00 | 
|       1 | 2006-04-01 17:47:00 |       1 | 2006-02-01 10:00:00 | 
|       1 | 2006-02-01 10:00:00 |       1 | 2006-02-01 10:00:00 | 
+---------+---------------------+---------+---------------------+
3 rows in set (0.02 sec)

mysql> 
mysql> SELECT * 
    -> FROM mysqlbug m1, mysqlbug m2
    -> WHERE m2.created 
    -> BETWEEN '2005-12-31 00:00:00'
    -> AND m1.created
    -> AND m1.somekey = m2.somekey;
+---------+---------------------+---------+---------------------+
| somekey | created             | somekey | created             |
+---------+---------------------+---------+---------------------+
|       1 | 2006-04-01 17:47:00 |       1 | 2006-04-01 17:47:00 | 
|       1 | 2006-04-01 17:47:00 |       1 | 2006-02-01 10:00:00 | 
|       1 | 2006-02-01 10:00:00 |       1 | 2006-02-01 10:00:00 | 
+---------+---------------------+---------+---------------------+
3 rows in set (0.01 sec)

mysql>