MySQL Bugs: #16069: Using BETWEEN with a VIEW doesn't work correctly

Bug #16069	Using BETWEEN with a VIEW doesn't work correctly
Submitted:	29 Dec 2005 14:03	Modified:	17 Apr 2006 18:43
Reporter:	hajni szab	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.17	OS:	Windows (Windows XP)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
The BETWEEN predicate in a select statement doesn't work correctly, when there is a VIEW in the select statement.

How to repeat:
fdday_status_room: VIEW
today_date: DATETIME field

no record in result set:
select count(*),today_date from fdday_status_room where (today_date between "2005.10.13" and "2005.11.12") group by today_date

but this select work correctly (30 records):
select count(*),today_date from fdday_status_room where (today_date>="2005.10.13") and (today_date<="2005.11.12") group by today_date

These two select must be produce the same result set!

I was unable to repeat this issue with my own data, could you please
provide a complete test case with table/view create query and some
inserts for test.

Thanks in advance.

Complete test

Attachment: test.zip (application/x-zip-compressed, text), 140.26 KiB.

Feedback?

Can You repeat this problem with the sample data?

I was able to reproduce this using the supplied test-case under WinXP/ 5.0.18 and fresh pull under FC4

The bug can be demonstrated with a simple example:

mysql> CREATE TABLE t1 (id int NOT NULL PRIMARY KEY,
    ->                  td date DEFAULT NULL, KEY idx(td));
Query OK, 0 rows affected (0.47 sec)

mysql> INSERT INTO t1 VALUES
    ->  (1, '2005-01-01'), (2, '2005-01-02'), (3, '2005-01-02'),
    ->  (4, '2005-01-03'), (5, '2005-01-04'), (6, '2005-01-05'),
    ->  (7, '2005-01-05'), (8, '2005-01-05'), (9, '2005-01-06');
Query OK, 9 rows affected (0.10 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW v1 AS SELECT * FROM t1;
Query OK, 0 rows affected (0.31 sec)

mysql> SELECT * FROM t1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04';
+----+------------+
| id | td         |
+----+------------+
| 2  | 2005-01-02 |
| 3  | 2005-01-02 |
| 4  | 2005-01-03 |
| 5  | 2005-01-04 |
+----+------------+
4 rows in set (0.33 sec)

mysql> SELECT * FROM v1 WHERE td BETWEEN '2005.01.02' AND '2005.01.04';
Empty set (0.33 sec)

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

ChangeSet
  1.2148 06/04/08 11:42:09 igor@rurik.mysql.com +3 -0
  Fixed bug #16069.
  The function agg_cmp_type in item_cmpfunc.cc neglected the fact that 
  the first argument in a BETWEEN/IN predicate could be a field of a view.
  As a result in the case when the retrieved table was hidden by a view 
  over it and the arguments in the BETWEEN/IN predicates are of 
  the date/time type the function did not perform conversion of 
  the constant arguments to the same format as the first field argument.
  If formats of the arguments differed it caused wrong a evaluation of
  the predicates.

Fix will appear in 5.0.21 and 5.1.10

Noted in 5.0.21, 5.1.10 changelogs.

If the first argument to <literal>BETWEEN</literal> was a
<literal>DATE</literal> or <literal>TIME</literal> column of a
view and the other arguments were constants,
<literal>BETWEEN</literal> did not perform conversion of the
constants to the appropriate temporary type, resulting in
incorrect evaluation. (Bug #16069)