MySQL Bugs: #55800: max() sometimes returns wrong results

Bug #55800	max() sometimes returns wrong results
Submitted:	6 Aug 2010 12:42	Modified:	6 Aug 2010 12:50
Reporter:	Jan Kirchhoff	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.5.5-m3-log	OS:	Linux (2.6.32)
Assigned to:		CPU Architecture:	Any
Tags:	max()

Description:
select max() on a datetime field sometimes shows wrong results. 
It took us some time to find a set of queries that reproduce the error reliable.

Happens on innodb tables as well as myisam.

How to repeat:
drop table if exists tbl2;
drop table if exists tbl1;

CREATE TABLE `tbl2` (
  `my_id` char(20) NOT NULL DEFAULT '',
  `datetime_field` datetime DEFAULT NULL,
  PRIMARY KEY (`my_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tbl2` (`my_id`, `datetime_field`) VALUES
('060960', '2010-07-15 13:45:24'),('060963', '2010-07-30 13:30:28'),('062510', '2010-08-04 00:00:00'),
('062513', '2010-07-30 00:00:00'),('062550', '2010-07-16 13:30:14'),('062553', '2010-07-29 13:30:17'),
('064055', '2010-08-02 13:30:06'),('069230', '2010-07-30 00:00:00'),('072360', '2010-07-30 00:00:00'),
('073483', '2010-08-03 00:00:00'),('074130', '2010-08-04 00:00:00'),('075800', '2010-07-29 00:00:00'),
('075803', '2010-08-02 00:00:00'),('077906', '2010-08-04 00:00:00'),('077907', '2010-07-30 00:00:00'),
('079730', '2010-08-03 00:00:00'),('081540', '2010-07-30 00:00:00'),('082450', '2010-08-03 00:00:00'),
('082470', '2010-08-04 00:00:00'),('B00569', '2010-07-28 13:30:12');

CREATE TABLE `tbl1` (
  `my_id` varchar(20) CHARACTER SET latin1 NOT NULL DEFAULT '',
  PRIMARY KEY (`my_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

INSERT INTO `tbl1` (`my_id`) VALUES
('060960'),('060963'),('062510'),('062513'),('062550'),('062553'),('064055'),('069230'),('072360'),('073483'),('074130'),
('075800'),('075803'),('077906'),('077907'),('079730'),('081540'),('082450'),('082470'),('B00569');

select @@version;
select max(f.datetime_field) from tbl1 as i left join tbl2 as f on i.my_id=f.my_id;
select f.datetime_field from tbl1 as i left join tbl2 as f on i.my_id=f.my_id order by datetime_field desc limit 1; 

quit
;

Results should always be 2010-08-04 00:00:00:

@@version
5.0.32-Debian_7etch1-log
max(f.datetime_field)
2010-08-04 00:00:00
datetime_field
2010-08-04 00:00:00

@@version
5.5.4-m3-log
max(f.datetime_field)
2010-07-28 13:30:12
datetime_field
2010-08-04 00:00:00

@@version
5.5.5-m3-log
max(f.datetime_field)
2010-07-28 13:30:12
datetime_field
2010-08-04 00:00:00

I see that this is already fixed somehow in current mysql-5.5 tree:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.6-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists tbl2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists tbl1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 
mysql> CREATE TABLE `tbl2` (
    ->   `my_id` char(20) NOT NULL DEFAULT '',
    ->   `datetime_field` datetime DEFAULT NULL,
    ->   PRIMARY KEY (`my_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tbl2` (`my_id`, `datetime_field`) VALUES
('060960', '2010-07-15 13:45:24'),('060963', '2010-07-30 13:30:28'),('062510',
'2010-08-04 00:00:00'),
('062513', '2010-07-30 00:00:00'),('062550', '2010-07-16 13:30:14'),('062553',
'2010-07-29 13:30:17'),
('064055', '2010-08-02 13:30:06'),('069230', '2010-07-30 00:00:00'),('072360',
'2010-07-30 00:00:00'),
('073483', '2010-08-03 00:00:00'),('074130', '2010-08-04 00:00:00'),('075800',
'2010-07-29 00:00:00'),
('075803', '2010-08-02 00:00:00'),('077906', '2010-08-04 00:00:00'),('077907',
'2010-07-30 00:00:00'),
('079730', '2010-08-03 00:00:00'),('081540', '2010-07-30 00:00:00'),('082450',
'2010-08-03 00:00:00'),
('082470', '2010-08-04 00:00:00'),('B00569', '2010-07-28 13:30:12');

CREATE TABLE `tbl1` (
  `my_id` varchar(20) CHARACTER SET latin1 NOT NULL DEFAULT '',
  PRIMARY KEY (`my_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

INSERT INTO `tbl1` (`my_id`) VALUES
('060960'),('060963'),('062510'),('062513'),('Query OK, 0 rows affected (0.06 sec)

mysql> 
mysql> INSERT INTO `tbl2` (`my_id`, `datetime_field`) VALUES
    -> ('060960', '2010-07-15 13:45:24'),('060963', '2010-07-30 13:30:28'),('062510',
    -> '2010-08-04 00:00:00'),
    -> ('062513', '2010-07-30 00:00:00'),('062550', '2010-07-16 13:30:14'),('062553',
    -> '2010-07-29 13:30:17'),
    -> ('064055', '2010-08-02 13:30:06'),('069230', '2010-07-30 00:00:00'),('072360',
    -> '2010-07-30 00:00:00'),
    -> ('073483', '2010-08-03 00:00:00'),('074130', '2010-08-04 00:00:00'),('075800',
    -> '2010-07-29 00:00:00'),
    -> ('075803', '2010-08-02 00:00:00'),('077906', '2010-08-04 00:00:00'),('077907',
    -> '2010-07-30 00:00:00'),
    -> ('079730', '2010-08-03 00:00:00'),('081540', '2010-07-30 00:00:00'),('082450',
    -> '2010-08-03 00:00:00'),
    -> ('082470', '2010-08-04 00:00:00'),('B00569', '2010-07-28 13:30:12');
Query OK, 20 rows affected (0.00 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> 
mysql> CREATE TABLE `tbl1` (
    ->   `my_id` varchar(20) CHARACTER SET latin1 NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`my_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
Query OK, 0 rows affected (0.12 sec)

mysql> 
mysql> INSERT INTO `tbl1` (`my_id`) VALUES
    -> ('060960'),('060963'),('062510'),('062513'),('062550'),('062553'),('064055'),('069230'),('072360'),('073483'),('074130'),
    -> ('075800'),('075803'),('077906'),('077907'),('079730'),('081540'),('082450'),('082470'),('B00569');
Query OK, 20 rows affected (0.00 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> 
mysql> select @@version;
+----------------+
| @@version      |
+----------------+
| 5.5.6-m3-debug |
+----------------+
1 row in set (0.01 sec)

mysql> select max(f.datetime_field) from tbl1 as i left join tbl2 as f on i.my_id=f.my_id;
+-----------------------+
| max(f.datetime_field) |
+-----------------------+
| 2010-08-04 00:00:00   |
+-----------------------+
1 row in set (0.00 sec)

mysql> select f.datetime_field from tbl1 as i left join tbl2 as f on i.my_id=f.my_id order by
    -> datetime_field desc limit 1; 
+---------------------+
| datetime_field      |
+---------------------+
| 2010-08-04 00:00:00 |
+---------------------+
1 row in set (0.01 sec)

good to know, would be interested if that bug has some bug-# here as I also see some strange max()/min() behaviour in subselects in views that could be related.

when can we expect a new test-release?