MySQL Bugs: #42785: mysql gives the wrong result with some special usage

Bug #42785	mysql gives the wrong result with some special usage
Submitted:	12 Feb 2009 10:12	Modified:	16 Jan 2013 15:48
Reporter:	Yu Hang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0/5.1/6.0	OS:	Linux (Ubuntu Intrepid)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
I don't know how to describe it well. see the example below.

expected result:
+----+
| c2 |
+----+
|  1 | 
+----+

actual result:
Empty set

How to repeat:
CREATE TABLE `bug` (
  `c1` varchar( 20 ) NOT NULL ,
  `c2` int NOT NULL ,
  KEY `k` ( `c2` , `c1` ) # required
) ENGINE = MYISAM ;

INSERT INTO `bug` (
  `c1` ,
  `c2`
)
VALUES ( # all of the three values are required
  'a', '0'
), (
  'b', '1'
), (
  'c', '2'
);

SELECT c2 # something like max(c2) or count(c1) will prevent the bug, but max(c1) doesn't work
FROM `bug`
WHERE (
  c1 = 'x' # anything except a and b
  AND c2 =0
)
OR (
  c1 = 'b'
  AND c2 =1
)
GROUP BY `c2` ;  # required

Repeatable with current source trees:

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 2
Server version: 5.0.78-Win x64 bzr revno 2737-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.0 > use test
Database changed
mysql 5.0 > CREATE TABLE `bug` (
    ->   `c1` varchar( 20 ) NOT NULL ,
    ->   `c2` int NOT NULL ,
    ->   KEY `k` ( `c2` , `c1` ) # required
    -> ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.04 sec)

mysql 5.0 >
mysql 5.0 > INSERT INTO `bug` (
    ->   `c1` ,
    ->   `c2`
    -> )
    -> VALUES ( # all of the three values are required
    ->   'a', '0'
    -> ), (
    ->   'b', '1'
    -> ), (
    ->   'c', '2'
    -> );
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.0 >
mysql 5.0 > SELECT c2
    -> FROM `bug`
    -> WHERE (
    ->   c1 = 'x' # anything except a and b
    ->   AND c2 =0
    -> )
    -> OR (
    ->   c1 = 'b'
    ->   AND c2 =1
    -> )
    -> GROUP BY `c2` ;
Empty set (0.00 sec)

mysql 5.0 > alter table bug drop key k;
Query OK, 3 rows affected (0.16 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.0 > SELECT c2
    -> FROM `bug`
    -> WHERE (
    ->   c1 = 'x' # anything except a and b
    ->   AND c2 =0
    -> )
    -> OR (
    ->   c1 = 'b'
    ->   AND c2 =1
    -> )
    -> GROUP BY `c2` ;
+----+
| c2 |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql 5.0 >

Thank you for the bug report. Verified as described even on older version like 5.0.22.

Noted in 5.1.69, 5.5.31, 5.6.11, 5.7.1 changelogs.

The optimizer used loose index scan for some queries for which this
access method is inapplicable.