| Bug #77480 | Wrong results for truncated column and aggregation | ||
|---|---|---|---|
| Submitted: | 25 Jun 2015 9:14 | Modified: | 2 Nov 2015 16:32 |
| Reporter: | Daniël van Eeden (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.7.7, 5.6.25 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[25 Jun 2015 9:19]
Daniël van Eeden
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)
[25 Jun 2015 9:40]
Shlomi Noach
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.
[25 Jun 2015 9:46]
Xin Wu
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 |
+-------------+
[25 Jun 2015 10:07]
MySQL Verification Team
Hello Daniel, Thank you for the report and test case. Thanks, Umesh
[26 Jun 2015 8:22]
Daniël van Eeden
Fixed typo in Synopsis
[29 Jun 2015 11:24]
MySQL Verification Team
--- 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 ---
[2 Nov 2015 16:32]
Paul DuBois
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.
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)