Bug #68852 Wrong behavior with aggregate functions, dates and between

• Submitted: 3 Apr 2013 9:27
• Modified: 3 Apr 2013 9:40
• Reporter: Paul Kramer
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.5.28
• OS: Any
• CPU Architecture: Any
• Tags: date between

[3 Apr 2013 9:27] Paul Kramer

Description:
When using dates with between and aggregation, the output is not what is expected. When I replace the between by simple >= and <= comparisons, it does show the correct result.

How to repeat:
> CREATE TABLE `days` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `day` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `time` (`day`)
);

> insert into days values (1,'2011-11-15'),(2,'2011-11-16'),(3,'2011-11-17'),(4,'2011-11-18'),(5,'2011-11-19'),(6,'2011-11-20');

> select min(id), max(id) from days where day between date('2011-11-16') and date('2011-11-18'); #This gives correct output
+---------+---------+
| min(id) | max(id) |
+---------+---------+
|       2 |       4 |
+---------+---------+
1 row in set (0.00 sec)

> select min(id), max(id), min(tag) from tage where tag between date('2011-11-16') and date('2011-11-18'); #This does not give the correct output
+---------+---------+----------+
| min(id) | max(id) | min(tag) |
+---------+---------+----------+
|    NULL |    NULL | NULL     |
+---------+---------+----------+
1 row in set (0.00 sec)

> select min(id), max(id), min(day) from days where day >= date('2011-11-16') and day <= date('2011-11-18'); #this works as expected.
+---------+---------+------------+
| min(id) | max(id) | min(tag)   |
+---------+---------+------------+
|       2 |       4 | 2011-11-16 |
+---------+---------+------------+
1 row in set (0.00 sec)

[3 Apr 2013 9:40] MySQL Verification Team

I'll verify it using the correct query.  5.6.10 returns good result, but 5.5 doesn't.

mysql> select min(id), max(id), min(day) from days where day between date('2011-11-16') and date('2011-11-18'); #This does not give the correct output
+---------+---------+------------+
| min(id) | max(id) | min(day)   |
+---------+---------+------------+
|       2 |       4 | 2011-11-16 |
+---------+---------+------------+
1 row in set (0.00 sec)

mysql> select min(id), max(id), min(day) from days where day >= date('2011-11-16') and day <= date('2011-11-18'); #this works as expected.
+---------+---------+------------+
| min(id) | max(id) | min(day)   |
+---------+---------+------------+
|       2 |       4 | 2011-11-16 |
+---------+---------+------------+
1 row in set (0.00 sec)

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

-----------
mysql> select min(id), max(id), min(day) from days where day between date('2011-11-16') and date('2011-11-18'); #This does not give the correct output
+---------+---------+----------+
| min(id) | max(id) | min(day) |
+---------+---------+----------+
|    NULL |    NULL | NULL     |
+---------+---------+----------+
1 row in set (0.00 sec)

mysql> select min(id), max(id), min(day) from days where day >= date('2011-11-16') and day <= date('2011-11-18'); #this works as expected.
+---------+---------+------------+
| min(id) | max(id) | min(day)   |
+---------+---------+------------+
|       2 |       4 | 2011-11-16 |
+---------+---------+------------+
1 row in set (0.00 sec)

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