Description:
When i use MAX() and GROUP BY to return the record with the highest value for a field, the only thing that is returned correctly is the 'value' of the field that i MAX()'d.
Yet it didn't select the correct record.
I've added a test project in the How to Repeat.
It's a boiled down version of the real thing, it's the most simple version of the table and the view that still show the same bug.
How to repeat:
Steps to reproduce:
- Take a look at the backup below.
You will notice that for the view maxview also a table maxview is in the
backup. Even though it gets dropped once the view itself is created it's
no good to put this in the backup.
- Restore the test database using the backup pasted below.
- Run the view, and take a look at the data it returns.
You will notice that the value of MAXDate that is returned is correct.
But you will also notice that all the other fields are the wrong values.
In other words, the wrong record is returned.
Add more records to the table using different values for AKey give a result that's more or less unpredictable.
-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version 5.0.24a-community-nt
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
--
-- Create schema test
--
CREATE DATABASE IF NOT EXISTS test;
USE test;
--
-- Temporary table structure for view `test`.`maxview`
--
DROP TABLE IF EXISTS `test`.`maxview`;
DROP VIEW IF EXISTS `test`.`maxview`;
CREATE TABLE `test`.`maxview` (
`MAX(ADate)` datetime,
`AKey` varchar(8),
`ADate` datetime,
`AType` bigint(20) unsigned,
`AAction` tinyint(1)
);
--
-- Definition of table `test`.`mytesttable`
--
DROP TABLE IF EXISTS `test`.`mytesttable`;
CREATE TABLE `test`.`mytesttable` (
`AKey` varchar(8) NOT NULL,
`ADate` datetime NOT NULL,
`AType` bigint(20) unsigned NOT NULL,
`AAction` tinyint(1) NOT NULL,
PRIMARY KEY (`AKey`,`ADate`,`AType`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table for testing the MAX() bug';
--
-- Dumping data for table `test`.`mytesttable`
--
/*!40000 ALTER TABLE `mytesttable` DISABLE KEYS */;
INSERT INTO `test`.`mytesttable` (`AKey`,`ADate`,`AType`,`AAction`) VALUES
('19210000','2006-08-24 00:00:00',0,1),
('19210000','2006-08-24 00:00:00',1,0),
('19210000','2007-08-24 00:00:00',1,0);
/*!40000 ALTER TABLE `mytesttable` ENABLE KEYS */;
--
-- Definition of view `test`.`maxview`
--
DROP TABLE IF EXISTS `test`.`maxview`;
DROP VIEW IF EXISTS `test`.`maxview`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`maxview` AS select max(`mytesttable`.`ADate`) AS `MAXDate`,`mytesttable`.`AKey` AS `AKey`,`mytesttable`.`ADate` AS `ADate`,`mytesttable`.`AType` AS `AType`,`mytesttable`.`AAction` AS `AAction` from `mytesttable` group by `mytesttable`.`AKey`;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
Suggested fix:
Fix the MAX() function so that it selects the correct record as well as returning the correct value.
Description: When i use MAX() and GROUP BY to return the record with the highest value for a field, the only thing that is returned correctly is the 'value' of the field that i MAX()'d. Yet it didn't select the correct record. I've added a test project in the How to Repeat. It's a boiled down version of the real thing, it's the most simple version of the table and the view that still show the same bug. How to repeat: Steps to reproduce: - Take a look at the backup below. You will notice that for the view maxview also a table maxview is in the backup. Even though it gets dropped once the view itself is created it's no good to put this in the backup. - Restore the test database using the backup pasted below. - Run the view, and take a look at the data it returns. You will notice that the value of MAXDate that is returned is correct. But you will also notice that all the other fields are the wrong values. In other words, the wrong record is returned. Add more records to the table using different values for AKey give a result that's more or less unpredictable. -- MySQL Administrator dump 1.4 -- -- ------------------------------------------------------ -- Server version 5.0.24a-community-nt /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -- -- Create schema test -- CREATE DATABASE IF NOT EXISTS test; USE test; -- -- Temporary table structure for view `test`.`maxview` -- DROP TABLE IF EXISTS `test`.`maxview`; DROP VIEW IF EXISTS `test`.`maxview`; CREATE TABLE `test`.`maxview` ( `MAX(ADate)` datetime, `AKey` varchar(8), `ADate` datetime, `AType` bigint(20) unsigned, `AAction` tinyint(1) ); -- -- Definition of table `test`.`mytesttable` -- DROP TABLE IF EXISTS `test`.`mytesttable`; CREATE TABLE `test`.`mytesttable` ( `AKey` varchar(8) NOT NULL, `ADate` datetime NOT NULL, `AType` bigint(20) unsigned NOT NULL, `AAction` tinyint(1) NOT NULL, PRIMARY KEY (`AKey`,`ADate`,`AType`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table for testing the MAX() bug'; -- -- Dumping data for table `test`.`mytesttable` -- /*!40000 ALTER TABLE `mytesttable` DISABLE KEYS */; INSERT INTO `test`.`mytesttable` (`AKey`,`ADate`,`AType`,`AAction`) VALUES ('19210000','2006-08-24 00:00:00',0,1), ('19210000','2006-08-24 00:00:00',1,0), ('19210000','2007-08-24 00:00:00',1,0); /*!40000 ALTER TABLE `mytesttable` ENABLE KEYS */; -- -- Definition of view `test`.`maxview` -- DROP TABLE IF EXISTS `test`.`maxview`; DROP VIEW IF EXISTS `test`.`maxview`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`maxview` AS select max(`mytesttable`.`ADate`) AS `MAXDate`,`mytesttable`.`AKey` AS `AKey`,`mytesttable`.`ADate` AS `ADate`,`mytesttable`.`AType` AS `AType`,`mytesttable`.`AAction` AS `AAction` from `mytesttable` group by `mytesttable`.`AKey`; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; Suggested fix: Fix the MAX() function so that it selects the correct record as well as returning the correct value.