Bug #77480 Wrong results for truncated column and aggregation

Description:
Aggregated results with a c1='something' can return wrong results if 'something' is longer that c1.

How to repeat:
mysql> CREATE TEMPORARY TABLE `PerformanceX` (
    ->   `method` varchar(64),
    ->   `service` varchar(16),
    ->   `period` datetime NOT NULL,
    ->   `count` int unsigned NOT NULL,
    ->   PRIMARY KEY (`service`, `period`, `method`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected (0.02 sec)

mysql> SET SESSION sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO PerformanceX VALUES ('usersettings', 'useraccountserver', NOW() - INTERVAL 10 MINUTE, 1);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'service' at row 1 |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(period) FROM PerformanceX WHERE service = 'useraccountserver';
+---------------------+
| MAX(period)         |
+---------------------+
| 2015-06-25 10:57:11 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(period), service FROM PerformanceX WHERE service =  'useraccountserver';
+-------------+---------+
| MAX(period) | service |
+-------------+---------+
| NULL        | NULL    |
+-------------+---------+
1 row in set (0.01 sec)

mysql> SELECT MAX(period) FROM PerformanceX WHERE service = 'useraccountserveXXXXX';
+---------------------+
| MAX(period)         |
+---------------------+
| 2015-06-25 10:57:11 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM PerformanceX WHERE service = 'useraccountserveXXXXX';
Empty set (0.00 sec)

Here the truncation is also shown:

mysql> SELECT MAX(period),MAX(service) FROM PerformanceX WHERE service = 'useraccountserveXXXXX';
+---------------------+------------------+
| MAX(period)         | MAX(service)     |
+---------------------+------------------+
| 2015-06-25 10:57:11 | useraccountserve |
+---------------------+------------------+
1 row in set (0.00 sec)

To simplify the queries: this is not an issue of sql_mode which adds confusion to the problem. Here are two queries that do not rely on sql_mode and which present the same effect:

mysql> SELECT MAX(period) FROM PerformanceX WHERE service = 'useraccountserver';
+---------------------+
| MAX(period)         |
+---------------------+
| 2015-06-25 11:26:50 |
+---------------------+
1 row in set (0,00 sec)

mysql> SELECT MAX(period) FROM PerformanceX IGNORE INDEX(PRIMARY) WHERE service = 'useraccountserver';
+-------------+
| MAX(period) |
+-------------+
| NULL        |
+-------------+

This shows that the value is being picked up during iteration on the PRIMARY KEY.

just a bit extra info, only happens it is the first column of combined index
root@> show create table PerformanceX\G
*************************** 1. row ***************************
       Table: PerformanceX
Create Table: CREATE TABLE `PerformanceX` (
  `method` varchar(64) NOT NULL DEFAULT '',
  `service` varchar(16) NOT NULL DEFAULT '',
  `period` datetime NOT NULL,
  `count` int(10) unsigned NOT NULL,
  PRIMARY KEY (`service`,`period`,`method`),
  KEY `service` (`service`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

root@> SELECT MAX(period) FROM PerformanceX WHERE service = 'useraccountserver';
+---------------------+
| MAX(period)         |
+---------------------+
| 2015-06-25 11:13:30 |
+---------------------+
1 row in set (0.00 sec)

root@> alter table PerformanceX drop primary key ;
Query OK, 1 row affected (0.21 sec)
Records: 1  Duplicates: 0  Warnings: 0

root@> SELECT MAX(period) FROM PerformanceX WHERE service = 'useraccountserver';
+-------------+
| MAX(period) |
+-------------+
| NULL        |
+-------------+
1 row in set (0.00 sec)

root@> alter table PerformanceX add index (service) ;
Query OK, 1 row affected, 0 (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

root@> SELECT MAX(period) FROM PerformanceX WHERE service = 'useraccountserver';
+-------------+
| MAX(period) |
+-------------+
| NULL        |
+-------------+
1 row in set (0.01 sec)

root@> alter table PerformanceX drop index service ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@> alter table PerformanceX add index (service, `period`,`method`) ;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@> SELECT MAX(period) FROM PerformanceX WHERE service = 'useraccountserver';
+---------------------+
| MAX(period)         |
+---------------------+
| 2015-06-25 11:13:30 |
+---------------------+
1 row in set (0.00 sec)

root@ alter table PerformanceX drop index service;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@> alter table PerformanceX add index (`period`,`method`, service) ;Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@> SELECT MAX(period) FROM PerformanceX WHERE service = 'useraccountserver';
+-------------+
| MAX(period) |
+-------------+
| NULL        |
+-------------+

Hello Daniel,

Thank you for the report and test case.

Thanks,
Umesh

Fixed typo in Synopsis

---
drop table if exists t;
create table t(a int, b char(1), primary key(b,a))engine=innodb;
insert into t(a,b) values(1,'a'),(3,'c');
select max(a) from t force  index(primary) where b='aa';  <---returns 1
select max(a) from t ignore index(primary) where b='aa';  <---returns NULL
---

Noted in 5.7.10, 5.8.0 changelogs.

For queries with implicit grouping; an index with a string column as
its first part; and a WHERE clause with an equality comparison
comparing the column to a string with trailing characters in addition
to the column value, an aggregate function that should return NULL
returned non-NULL.