MySQL Bugs: #26124: BETWEEN in views returns incorrect results

Bug #26124	BETWEEN in views returns incorrect results
Submitted:	6 Feb 2007 20:41	Modified:	21 Feb 2007 3:22
Reporter:	James Dunn	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.27/5.0/5.1BK	OS:	Windows (XP/Linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
SELECT * FROM vw_blah WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';

seems to get run as

SELECT * FROM vw_blah WHERE mydate >= '2007-01-01' AND mydate < '2007-01-31';

rather than

SELECT * FROM vw_blah WHERE mydate >= '2007-01-01' AND mydate <= '2007-01-31';

How to repeat:
CREATE TABLE tbl_blah (mydate DATETIME);
CREATE VIEW vw_blah as SELECT mydate from tbl_blah;
INSERT INTO tbl_blah (mydate) VALUES ('2007-01-01');
INSERT INTO tbl_blah (mydate) VALUES ('2007-01-02');
INSERT INTO tbl_blah (mydate) VALUES ('2007-01-30');
INSERT INTO tbl_blah (mydate) VALUES ('2007-01-31');
SELECT * FROM vw_blah WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.36-debug Source distribution

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

mysql> CREATE TABLE tbl_blah (mydate DATETIME);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE VIEW vw_blah as SELECT mydate from tbl_blah;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tbl_blah (mydate) VALUES ('2007-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tbl_blah (mydate) VALUES ('2007-01-02');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tbl_blah (mydate) VALUES ('2007-01-30');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tbl_blah (mydate) VALUES ('2007-01-31');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM vw_blah WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
+---------------------+
| mydate              |
+---------------------+
| 2007-01-01 00:00:00 |
| 2007-01-02 00:00:00 |
| 2007-01-30 00:00:00 |
+---------------------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM tbl_blah WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
+---------------------+
| mydate              |
+---------------------+
| 2007-01-01 00:00:00 |
| 2007-01-02 00:00:00 |
| 2007-01-30 00:00:00 |
| 2007-01-31 00:00:00 |
+---------------------+
4 rows in set (0.00 sec)

mysql>

Thank you for the bug report. Verified as described.

mysql> SELECT * FROM vw_blah WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
+---------------------+
| mydate              |
+---------------------+
| 2007-01-01 00:00:00 |
| 2007-01-02 00:00:00 |
| 2007-01-30 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM tbl_blah WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
+---------------------+
| mydate              |
+---------------------+
| 2007-01-01 00:00:00 |
| 2007-01-02 00:00:00 |
| 2007-01-30 00:00:00 |
| 2007-01-31 00:00:00 |
+---------------------+
4 rows in set (0.00 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.16-beta-debug |
+-------------------+
1 row in set (0.00 sec)

mysql>

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/19461

ChangeSet@1.2404, 2007-02-07 00:18:36-08:00, igor@olga.mysql.com +3 -0
  Fixed bug #26124: SELECT from a view wrapper over a table 
  with a column of the DATETIME type could return a wrong 
  result set if the WHERE clause included a BETWEEN condition
  on the column.
  Fixed the method Item_func_between::fix_length_and_dec
  where the aggregation type for BETWEEN predicates calculated
  incorrectly if the first argument was a view column of the
  DATETIME type.

The fix has been pushed to 5.0.36 and 5.1.16-beta.

Noted in 5.0.36, 5.1.16 changelogs.

A WHERE clause that used BETWEEN for DATETIME values could be treated
differently for a SELECT and a view defined as that SELECT.