MySQL Bugs: #38517: min() returns values from unmatched rows when used on unique index

Bug #38517	min() returns values from unmatched rows when used on unique index
Submitted:	1 Aug 2008 8:56	Modified:	1 Aug 2008 13:35
Reporter:	Nico Meyer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Any (Linux 64bit)
Assigned to:		CPU Architecture:	Any

Description:
Hello,

we observe a strange behaviour on 5.0.45 which cannot be reproduced on another server with 5.0.38

The following query returns '2008-08-01' even if there is no row with a date greater than '2008-08-01':
  
  select min(d) from test where d>'2008-08-01';

where test was created with:

 create table test (d date not null default '0000-00-00' primary key);
 insert into test values ('2008-07-31'),('2008-08-01');

On a 5.0.38 server the query returns 'NULL' as expected.

Other aggregate functions like max() and avg() return NULL on both versions. Also, if you remove the unique key from the date column both versions return NULL again.
The kicker is, this only happens if the date in the where clause is the first of august ('2008-08-01','2009-08-01',....) and if this date is actually the highest entry for that column.

The system_time_zone is CEST.

How to repeat:
Version 5.0.45 of the server on Linux 64bit (Debian Lenny)

create table test (d date not null default '0000-00-00' primary key);
insert into test values ('2008-07-31'),('2008-08-01');
select min(d) from test where d>'2008-08-01';

Returns:

2008-08-01

Should Return (as on version 5.0.38):

NULL

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51b at least, and inform about the results.

On 5.0.66a I've got:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> create table test (d date not null default '0000-00-00' primary key);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into test values ('2008-07-31'),('2008-08-01');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select min(d) from test where d>'2008-08-01';
+--------+
| min(d) |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

Hello,

I tried with the lastest official version (5.0.51a), and there the bug is still there (installed the lastest Ubuntu package on my desktop).

In the 5.0.64 snapshot the behaviour seems to be correct again.

Bye,
Nico

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html