Bug #18618 Joining tables with DATE BETWEEN in WHERE clause gives unexpected results
Submitted: 29 Mar 2006 15:14 Modified: 17 Apr 2006 18:46
Reporter: Nathan Tanner Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.20-BK, 5.0.19 OS:Linux (Linux)
Assigned to: Igor Babaev CPU Architecture:Any

[29 Mar 2006 15:14] Nathan Tanner
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.
[29 Mar 2006 15:18] Nathan Tanner
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.
[29 Mar 2006 15:56] Valeriy Kravchuk
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.
[11 Apr 2006 5:05] Bugs System
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
[11 Apr 2006 17:03] Bugs System
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
[17 Apr 2006 2:08] Igor Babaev
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
[17 Apr 2006 18:46] Paul DuBois
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)