MySQL Bugs: #73825: Wrong result (extra row) with group by, multi-part key

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:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The problem looks similar to bug #70359, yet somewhat different. 

Actual result of the test case from 'How to repeat':
MAX(f1)	f2
4	c
4	c

Expected result:
MAX(f1)	f2
4	c

How to repeat:
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');

SELECT MAX(f1), f2 FROM t1 WHERE f2 LIKE 'c%' AND f1 <> 9 GROUP BY f2;

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 >

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)

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)

Thank you for the bug report. Probably this bug is duplicate however unlucky I
wasn't able to find it.

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;