Bug #18618 Joining tables with DATE BETWEEN in WHERE clause gives unexpected results

Description:
If you join two tables in a query, and have a WHERE clause containing a BETWEEN with DATE values, you can get unexpected results, specifically, no rows returned when there should be rows returned.

How to repeat:
CREATE TABLE test (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Day DATE);

INSERT INTO test (Day) VALUES (CURDATE()-INTERVAL (RAND()*365) DAY), (null), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (null), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (null), (CURDATE()-INTERVAL (RAND()*365) DAY);

CREATE TABLE test2 (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Day DATE);

INSERT INTO test2 (Day) VALUES (CURDATE()-INTERVAL (RAND()*365) DAY), (null), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (null), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (CURDATE()-INTERVAL (RAND()*365) DAY), (null), (CURDATE()-INTERVAL (RAND()*365) DAY);

SELECT * FROM test, test2 WHERE test.Day BETWEEN CURDATE() - INTERVAL 6 MONTH AND test2.Day; 

You will notice the SELECT query returns 0 rows. However, if you change it to:

SELECT * FROM test, test2 WHERE CAST(test.Day AS DATE) BETWEEN CURDATE() - INTERVAL 6 MONTH AND test2.Day;

it will return several rows, which is odd because test.Day is already a DATE data type.

Suggested fix:
Make it return rows as would be expected.

FYI, I did some more searching, and this bug may be the same as bug 14360, but there are some differences. I will leave that to your discretion.

Thank you for a problem report. Sorry, but I was not able to repeat exactly the behaviour you described on today's 5.0.20-BK (with your test case):

mysql> SELECT * FROM test, test2 WHERE test.Day BETWEEN CURDATE() - INTERVAL 6 MONTH
    -> AND test2.Day;
+----+------------+----+------------+
| ID | Day        | ID | Day        |
+----+------------+----+------------+
| 7  | 2006-02-16 | 5  | 2006-03-16 |
| 13 | 2006-02-10 | 5  | 2006-03-16 |
+----+------------+----+------------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM test, test2 WHERE CAST(test.Day AS DATE) BETWEEN CURDATE() -
    -> INTERVAL 6 MONTH AND test2.Day;
+----+------------+----+------------+
| ID | Day        | ID | Day        |
+----+------------+----+------------+
| 3  | 2005-12-11 | 3  | 2005-12-26 |
| 5  | 2005-10-12 | 3  | 2005-12-26 |
| 6  | 2005-12-10 | 3  | 2005-12-26 |
| 12 | 2005-11-27 | 3  | 2005-12-26 |
| 3  | 2005-12-11 | 5  | 2006-03-16 |
| 5  | 2005-10-12 | 5  | 2006-03-16 |
| 6  | 2005-12-10 | 5  | 2006-03-16 |
| 7  | 2006-02-16 | 5  | 2006-03-16 |
| 12 | 2005-11-27 | 5  | 2006-03-16 |
| 13 | 2006-02-10 | 5  | 2006-03-16 |
| 5  | 2005-10-12 | 7  | 2005-11-16 |
| 3  | 2005-12-11 | 8  | 2006-02-06 |
| 5  | 2005-10-12 | 8  | 2006-02-06 |
| 6  | 2005-12-10 | 8  | 2006-02-06 |
| 12 | 2005-11-27 | 8  | 2006-02-06 |
| 3  | 2005-12-11 | 12 | 2005-12-25 |
| 5  | 2005-10-12 | 12 | 2005-12-25 |
| 6  | 2005-12-10 | 12 | 2005-12-25 |
| 12 | 2005-11-27 | 12 | 2005-12-25 |
| 5  | 2005-10-12 | 13 | 2005-10-20 |
| 5  | 2005-10-12 | 15 | 2005-10-24 |
+----+------------+----+------------+
21 rows in set (0.00 sec)

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

So, it may not be exactly related to that bug #14360. Patch for that bug should be included into 5.0.20-BK already, but I am not sure.

The real problem now is:

mysql> explain extended SELECT * FROM test, test2 WHERE test.Day BETWEEN CURDATE() - INTERVAL 6 MONTH AND test2.Day\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: test2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
        Extra: Using where
2 rows in set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select sql_no_cache `test`.`test`.`ID` AS `ID`,`test`.`test`.`Day` AS `Day`,`test`.`test2`.`ID` AS `ID`,`test`.`test2`.`Day` AS `Day` from `test`.`test
` join `test`.`test2` where (`test`.`test`.`Day` between 20050929 and `test`.`test2`.`Day`)
1 row in set (0.01 sec)

mysql> explain extended SELECT * FROM test, test2 WHERE CAST(test.Day AS DATE)
BETWEEN CURDATE() - INTERVAL 6 MONTH AND test2.Day\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: test2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
        Extra: Using where
2 rows in set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select sql_no_cache `test`.`test`.`ID` AS `ID`,`test`.`test`.`Day` AS `Day`,`test`.`test2`.`ID` AS `ID`,`test`.`test2`.`Day` AS `Day` from `test`.`test
` join `test`.`test2` where (cast(`test`.`test`.`Day` as date) between (curdate() - interval 6 month) and `test`.`test2`.`Day`)
1 row in set (0.01 sec)

The difference in optimization (why "curdate() - interval 6 month"  is converted  to 20050929 in the first case, but not in the second), and, surely, different results.

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

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

ChangeSet
  1.2151 06/04/10 22:05:13 igor@rurik.mysql.com +2 -0
  Fixed bug #18618.
  If the second or the third argument of a BETWEEN predicate was
  a constant expression, like '2005.09.01' - INTERVAL 6 MONTH,
  while the other two arguments were fields then the predicate 
  was evaluated incorrectly and the query returned a wrong
  result set.
  The bug was introduced in 5.0.17 by the fix for 12612.

The fix will appear in 5.0.21 and 5.1.10

Noted in 5.0.21, 5.1.10 changelogs.

If the second or third argument to <literal>BETWEEN</literal>
was a constant expression such as <literal>'2005-09-01 -
INTERVAL 6 MONTH</literal> and the other two arguments were
columns, <literal>BETWEEN</literal> was evaluated incorrectly.
(Bug #18618)