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
Triage: Triaged: D2 (Serious)

[12 Feb 2009 10:12] Yu Hang
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
[12 Feb 2009 10:47] Miguel Solorzano
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 >
[12 Feb 2009 10:54] Miguel Solorzano
Thank you for the bug report. Verified as described even on older version like 5.0.22.
[16 Jan 2013 15:48] Paul Dubois
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.