Bug #73825 | Wrong result (extra row) with group by, multi-part key | ||
---|---|---|---|
Submitted: | 6 Sep 2014 16:45 | Modified: | 6 Sep 2014 21:04 |
Reporter: | Elena Stepanova | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.1, 5.5, 5.6, 5.7 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[6 Sep 2014 16:45]
Elena Stepanova
[6 Sep 2014 20:38]
MySQL Verification Team
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.21 Source distribution Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.6 > CREATE DATABASE yu; Query OK, 1 row affected (0.02 sec) mysql 5.6 > USE yu Database changed mysql 5.6 > CREATE TABLE t1 (f1 INT, f2 VARCHAR(1), KEY(f2,f1)) ENGINE=MyISAM; Query OK, 0 rows affected (0.08 sec) mysql 5.6 > INSERT INTO t1 VALUES -> (7,'v'),(0,'s'),(9,'l'),(4,'c'),(2,'i'),(5,'h'),(3,'q'),(1,'a'),(3,'v'),(3,'y'); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql 5.6 > mysql 5.6 > SELECT MAX(f1), f2 FROM t1 WHERE f2 LIKE 'c%' AND f1 <> 9 GROUP BY f2; +---------+------+ | MAX(f1) | f2 | +---------+------+ | 4 | c | | 4 | c | +---------+------+ 2 rows in set (0.00 sec) mysql 5.6 > SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `f1` int(11) DEFAULT NULL, `f2` varchar(1) DEFAULT NULL, KEY `f2` (`f2`,`f1`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql 5.6 > ALTER TABLE t1 DROP INDEX f2; Query OK, 10 rows affected (0.13 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql 5.6 > SELECT MAX(f1), f2 FROM t1 WHERE f2 LIKE 'c%' AND f1 <> 9 GROUP BY f2; +---------+------+ | MAX(f1) | f2 | +---------+------+ | 4 | c | +---------+------+ 1 row in set (0.02 sec) mysql 5.6 >
[6 Sep 2014 20:55]
MySQL Verification Team
Older bug exists since 5.0: C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.97-Win X64 Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.0 > CREATE DATABASE yu; Query OK, 1 row affected (0.02 sec) mysql 5.0 > USE yu Database changed mysql 5.0 > CREATE TABLE t1 (f1 INT, f2 VARCHAR(1), KEY(f2,f1)) ENGINE=MyISAM; Query OK, 0 rows affected (0.07 sec) mysql 5.0 > INSERT INTO t1 VALUES -> (7,'v'),(0,'s'),(9,'l'),(4,'c'),(2,'i'),(5,'h'),(3,'q'),(1,'a'),(3,'v'),(3,'y'); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql 5.0 > mysql 5.0 > SELECT MAX(f1), f2 FROM t1 WHERE f2 LIKE 'c%' AND f1 <> 9 GROUP BY f2; +---------+------+ | MAX(f1) | f2 | +---------+------+ | 4 | c | | 4 | c | +---------+------+ 2 rows in set (0.00 sec)
[6 Sep 2014 20:58]
MySQL Verification Team
Already fixed on 5.7 branch: C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 --prompt="mysql 5.7 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.7.5-m15 Source distribution Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.7 > CREATE DATABASE yu; Query OK, 1 row affected (0.01 sec) mysql 5.7 > USE yu Database changed mysql 5.7 > CREATE TABLE t1 (f1 INT, f2 VARCHAR(1), KEY(f2,f1)) ENGINE=MyISAM; Query OK, 0 rows affected (0.07 sec) mysql 5.7 > INSERT INTO t1 VALUES -> (7,'v'),(0,'s'),(9,'l'),(4,'c'),(2,'i'),(5,'h'),(3,'q'),(1,'a'),(3,'v'),(3,'y'); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql 5.7 > mysql 5.7 > SELECT MAX(f1), f2 FROM t1 WHERE f2 LIKE 'c%' AND f1 <> 9 GROUP BY f2; +---------+------+ | MAX(f1) | f2 | +---------+------+ | 4 | c | +---------+------+ 1 row in set (0.00 sec)
[6 Sep 2014 21:04]
MySQL Verification Team
Thank you for the bug report. Probably this bug is duplicate however unlucky I wasn't able to find it.
[6 Nov 2014 11:39]
Øystein Grøvlen
By adding more records to the table, it is possible to reproduce this error in 5.7: CREATE TABLE t1 (f1 INT, f2 VARCHAR(1), KEY(f2,f1)) ENGINE=MyISAM; INSERT INTO t1 VALUES (7,'v'),(0,'s'),(9,'l'),(4,'c'),(2,'i'),(5,'h'),(3,'q'),(1,'a'),(3,'v'),(3,'y'),(1,'c'),(2,'c'),(3,'c'),(1,'c'),(2,'c'),(3,'c'),(1,'c'),(2,'c'),(3,'c'),(1,'c'),(2,'c'),(3,'c'); SELECT MAX(f1), f2 FROM t1 WHERE f2 LIKE 'c%' AND f1 <> 9 GROUP BY f2; DROP TABLE t1;