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;
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.055 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.