MySQL Bugs: #61399: Wrong result with GROUP BY and indexes

Bug #61399	Wrong result with GROUP BY and indexes
Submitted:	3 Jun 2011 15:03	Modified:	6 Feb 2018 23:11
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5/5.0/5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The query below returns no rows even though it does return rows if the GROUP BY is removed or if DISABLE KEYS is issued against the table.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (  col_int_key int(11) DEFAULT NULL,  col_varchar_key varchar(1) DEFAULT NULL,  KEY col_int_key (col_int_key),  KEY col_varchar_key (col_varchar_key,col_int_key)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (0,NULL),(9,NULL),(8,'c'),(4,'d'),(7,'d'),(NULL,'f'),(7,'f'),(8,'g'),(NULL,'j'),(1,'m'),(2,'m'),(2,'o'),(0,'p'),(4,'q'),(1,'r'),(NULL,'u'),(9,'w'),(NULL,'x'),(8,'x'),(9,'y');

SELECT col_varchar_key AS field1
FROM t1
WHERE col_int_key = 146 AND col_varchar_key = 'x' OR col_varchar_key < 'vp'
GROUP BY field1 ;

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.14-log Source distribution

Copyright (c) 2000, 2010, 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.5 >use db1
Database changed
mysql 5.5 >DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.11 sec)

mysql 5.5 >CREATE TABLE t1 (  col_int_key int(11) DEFAULT NULL,  col_varchar_key varchar(1
    -> NULL,  KEY col_int_key (col_int_key),  KEY col_varchar_key (col_varchar_key,col_int
    -> ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.07 sec)

mysql 5.5 >INSERT INTO t1 VALUES
    -> (0,NULL),(9,NULL),(8,'c'),(4,'d'),(7,'d'),(NULL,'f'),(7,'f'),(8,'g'),(NULL,'j'),(1,
Query OK, 20 rows affected (0.02 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql 5.5 >
mysql 5.5 >SELECT col_varchar_key AS field1
    -> FROM t1
    -> WHERE col_int_key = 146 AND col_varchar_key = 'x' OR col_varchar_key < 'vp'
    -> GROUP BY field1 ;
Empty set (0.00 sec)

mysql 5.5 >SELECT col_varchar_key AS field1
    -> FROM t1
    -> WHERE col_int_key = 146 AND col_varchar_key = 'x' OR col_varchar_key < 'vp';
+--------+
| field1 |
+--------+
| c      |
| d      |
| d      |
| f      |
| f      |
| g      |
| j      |
| m      |
| m      |
| o      |
| p      |
| q      |
| r      |
| u      |
+--------+
14 rows in set (0.00 sec)

Thank you for the bug report. 5.0/5.1 affected too.

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.58-Win X64-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >use test
Database changed
mysql 5.1 >DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.20 sec)

mysql 5.1 >CREATE TABLE t1 (  col_int_key int(11) DEFAULT NULL,  col_varchar_key varchar(1) DEFAULT
    -> NULL,  KEY col_int_key (col_int_key),  KEY col_varchar_key (col_varchar_key,col_int_key))
    -> ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.13 sec)

mysql 5.1 >INSERT INTO t1 VALUES
    -> (0,NULL),(9,NULL),(8,'c'),(4,'d'),(7,'d'),(NULL,'f'),(7,'f'),(8,'g'),(NULL,'j'),(1,'m'),(2,'m'),(2,'o'),(0,'p'),(4,'q'),(1,'r'),(NULL,'u'),(9,'w'),(NULL,'x'),(8,'x'),(9,'y');
Query OK, 20 rows affected (0.02 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql 5.1 >
mysql 5.1 >SELECT col_varchar_key AS field1
    -> FROM t1
    -> WHERE col_int_key = 146 AND col_varchar_key = 'x' OR col_varchar_key < 'vp'
    -> GROUP BY field1 ;
Empty set (0.00 sec)

mysql 5.1 >exit
Bye

C:\DBS>50

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.93-Win X64 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > use test
Database changed
mysql 5.0 > DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.11 sec)

mysql 5.0 > CREATE TABLE t1 (  col_int_key int(11) DEFAULT NULL,  col_varchar_key varchar(1) DEFAULT
    -> NULL,  KEY col_int_key (col_int_key),  KEY col_varchar_key (col_varchar_key,col_int_key))
    -> ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

mysql 5.0 > INSERT INTO t1 VALUES
    -> (0,NULL),(9,NULL),(8,'c'),(4,'d'),(7,'d'),(NULL,'f'),(7,'f'),(8,'g'),(NULL,'j'),(1,'m'),(2,'m'),(2,'o'),(0,'p'),(4,'q'),(1,'r'),(NULL,'u'),(9,'w'),(NULL,'x'),(8,'x'),(9,'y');
Query OK, 20 rows affected (0.00 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql 5.0 >
mysql 5.0 > SELECT col_varchar_key AS field1
    -> FROM t1
    -> WHERE col_int_key = 146 AND col_varchar_key = 'x' OR col_varchar_key < 'vp'
    -> GROUP BY field1 ;
Empty set (0.01 sec)

mysql 5.0 >

How is it possible that this is not a critical bug?

Bug #63609 was marked as duplicate of this one.

Works on 5.6.13:
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1 (  col_int_key int(11) DEFAULT NULL,  col_varchar_key varchar(1) DEFAULT NULL,  KEY col_int_key (col_int_key),  KEY col_varchar_key (col_varchar_key,col_int_key)) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SELECT col_varchar_key AS field1
FROM t1
WHERE col_int_key = 146 AND col_varchar_key = 'x' OR col_varchar_key < 'vp'
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (0,NULL),(9,NULL),(8,'c'),(4,'d'),(7,'d'),(NULL,'f'),(7,'f'),(8,'g'),(NULL,'j'),(1,'m'),(2,'m'),(2,'o'),(0,'p'),(4,'q'),(1,'r'),(NULL,'u'),(9,'w'),(NULL,'x'),(8,'x'),(9,'y');
GROUP BY field1 ;Query OK, 20 rows affected (0.00 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT col_varchar_key AS field1
    -> FROM t1
    -> WHERE col_int_key = 146 AND col_varchar_key = 'x' OR col_varchar_key < 'vp'
    -> GROUP BY field1 ;
+--------+
| field1 |
+--------+
| c      |
| d      |
| f      |
| g      |
| j      |
| m      |
| o      |
| p      |
| q      |
| r      |
| u      |
+--------+
11 rows in set (0.00 sec)

Seems to be fixed in 5.7:

mysql> SELECT col_varchar_key AS field1
    -> FROM t1
    -> WHERE col_int_key = 146 AND col_varchar_key = 'x' OR col_varchar_key < 'vp'
    -> GROUP BY field1 ;
+--------+
| field1 |
+--------+
| c      |
| d      |
| f      |
| g      |
| j      |
| m      |
| o      |
| p      |
| q      |
| r      |
| u      |
+--------+
11 rows in set (0.00 sec)

Posted by developer:
 
Fixed in 5.6 and up.