Bug #84758 Min Max Functionality
Submitted: 31 Jan 2017 22:31 Modified: 3 Feb 2017 18:40
Reporter: John More Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.1.17-log MySql Community Server (GPL) OS:Ubuntu (Ubuntu ("Ubuntu 14.04.3 LTS"))
Assigned to: CPU Architecture:Any

[31 Jan 2017 22:31] John More
Description:
I am having trouble with the max function and I am not sure it is me or the software. I have a table with an id, a value, a timestamp, and a sequence number in each row. 
The following query returns
```
SELECT 
deviceIdText, 
value, 
sequenceNumber, 
timestamp 
FROM 
DevicesTelemetry.TMMeterCount where deviceId = x'0a17216927655931bd40b43971aae394' and day(timestamp) = 10;
```
RESULTS
```
deviceIdText	value sequenceNumber	timestamp 
0A172169-2765-5931-BD40-B43971AAE394	99933	42975	2016-02-10 10:35:45 
0A172169-2765-5931-BD40-B43971AAE394	99940	42980	2016-02-10 10:37:36 
0A172169-2765-5931-BD40-B43971AAE394	99941	42987	2016-02-10 10:38:21 
0A172169-2765-5931-BD40-B43971AAE394	99942	43005	2016-02-10 10:41:20 
0A172169-2765-5931-BD40-B43971AAE394	99946	43006	2016-02-10 10:41:42 
0A172169-2765-5931-BD40-B43971AAE394	99954	43019	2016-02-10 10:42:49 
0A172169-2765-5931-BD40-B43971AAE394	99961	43028	2016-02-10 10:44:18 
0A172169-2765-5931-BD40-B43971AAE394	99970	43033	2016-02-10 10:45:25 
0A172169-2765-5931-BD40-B43971AAE394	99972	43035	2016-02-10 10:45:47 
0A172169-2765-5931-BD40-B43971AAE394	99981	43044	2016-02-10 10:46:54 
0A172169-2765-5931-BD40-B43971AAE394	99989	43054	2016-02-10 10:48:24 
0A172169-2765-5931-BD40-B43971AAE394	99991	43062	2016-02-10 10:49:31 
0A172169-2765-5931-BD40-B43971AAE394	99998	43065	2016-02-10 10:49:53 
0A172169-2765-5931-BD40-B43971AAE394	99990	43083	2016-02-10 10:52:41 
```
With this next query I am expecting to return the min and maximum value for the range returned above. As you can see the maximum value returned is the value from the second-last row of the available data. I have also used the sequence number which is returning the right value.
```
select deviceId, min(sequenceNumber), max(sequenceNumber), min(value), max(value)
from TMMeterCount 
where deviceId = x'0a17216927655931bd40b43971aae394' and day(timestamp) = 10 ;
```
```
deviceId	min(sequenceNumber)	max(sequenceNumber)	min(value)	max(value)
0a17216927655931bd40b43971aae394	42975	43083	99933	99998
```

How to repeat:
Create a table
```
 CREATE TABLE `TMMeterCount` (
  `deviceId` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `value` bigint(20) DEFAULT NULL,
  `sequenceNumber` bigint(20) NOT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`deviceId`,`sequenceNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
```
Insert Data
```
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99933,42975,'2016-02-10 10:35:45');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99940,42980,'2016-02-10 10:37:36'); 
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99941,42987,'2016-02-10 10:38:21');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99942,43005,'2016-02-10 10:41:20'); 
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99946,43006,'2016-02-10 10:41:42'); 
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99954,43019,'2016-02-10 10:42:49'); 
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99961,43028,'2016-02-10 10:44:18'); 
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99970,43033,'2016-02-10 10:45:25'); 
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99972,43035,'2016-02-10 10:45:47'); 
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99981,43044,'2016-02-10 10:46:54'); 
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99989,43054,'2016-02-10 10:48:24'); 
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99991,43062,'2016-02-10 10:49:31'); 
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99998,43065,'2016-02-10 10:49:53'); 
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99990,43083,'2016-02-10 10:52:41');
```
Run the queries.

Suggested fix:
No suggestion.
The table that the data is coming from only has 12000 rows.
[1 Feb 2017 4:39] John More
I must apologise what is a very embarrassing oversight. After fighting with this for hours I must have gone blind. A hand review of the data clearly shows that the software operates correctly but the device generating the input and my brain do not. The device reported a lower value later in sequence. 
Please accept my apology for wasting your time.