MySQL Bugs: #49902: SELECT returns incorrect results

Bug #49902	SELECT returns incorrect results
Submitted:	23 Dec 2009 22:07	Modified:	18 Jun 2010 2:09
Reporter:	Matt Light	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.1.41,5.5.0-m2	OS:	Any
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any
Tags:	regression

Description:
In certain circumstances MySQL is returning incorrect results on simple SELECT statements. Together with a few other developers, we were able to track down at least part of the issue to another SELECT statement that seems to be causing some sort of cache (index, query, or otherwise) corruption.

We have reproduced the issue as far back as MySQL 5.1.26 (on CentOS) and as most recent as 5.1.41 (Mac OS X). The issue only seems to occur with MyISAM tables. The issue does not seem to occur with InnoDB tables.

How to repeat:
1. Create the table and add the data:
CREATE TABLE `debug` (
 `id` int(10) unsigned NOT NULL DEFAULT '0',
 `number` int(10) unsigned NOT NULL DEFAULT '0',
 KEY `siteid` (`number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `debug` (`id`,`number`)
VALUES
   (1,1),
   (2,1);

2. Run the following queries:
-- A: clear the issue (possibly a cache)
OPTIMIZE TABLE debug;

-- B: see what the results are supposed to be
SELECT id FROM debug WHERE number = 1;

-- C: clear the issue again (just in case)
OPTIMIZE TABLE debug; -- this does not appear to be necessary

-- D: run the query that causes the issue
SELECT 1 AS constant, number
FROM debug
WHERE number IN (1,2)
GROUP BY constant, number;

-- E: attempt to get the same results as B
SELECT id FROM debug WHERE number = 1;

Suggested fix:
After noting that parts B and E are the exact same query, please make parts B and E return the same results.

Currently, part B returns:
id
--
1
2

Part E returns:
id
--
0
0

Thanks :o)

Thank you for the bug report. 5.0 not affected.

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

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

mysql 5.1 >CREATE DATABASE DS;
Query OK, 1 row affected (0.01 sec)

mysql 5.1 >USE DS;
Database changed
mysql 5.1 >CREATE TABLE `debug` (
    ->  `id` int(10) unsigned NOT NULL DEFAULT '0',
    ->  `number` int(10) unsigned NOT NULL DEFAULT '0',
    ->  KEY `siteid` (`number`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)

mysql 5.1 >
mysql 5.1 >INSERT INTO `debug` (`id`,`number`)
    -> VALUES
    ->    (1,1),
    ->    (2,1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.1 >OPTIMIZE TABLE debug;
+----------+----------+----------+----------+
| Table    | Op       | Msg_type | Msg_text |
+----------+----------+----------+----------+
| ds.debug | optimize | status   | OK       |
+----------+----------+----------+----------+
1 row in set (0.01 sec)

mysql 5.1 >SELECT id FROM debug WHERE number = 1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql 5.1 >OPTIMIZE TABLE debug;
+----------+----------+----------+-----------------------------+
| Table    | Op       | Msg_type | Msg_text                    |
+----------+----------+----------+-----------------------------+
| ds.debug | optimize | status   | Table is already up to date |
+----------+----------+----------+-----------------------------+
1 row in set (0.00 sec)

mysql 5.1 >SELECT 1 AS constant, number
    -> FROM debug
    -> WHERE number IN (1,2)
    -> GROUP BY constant, number;
+----------+--------+
| constant | number |
+----------+--------+
|        1 |      1 |
+----------+--------+
1 row in set (0.00 sec)

mysql 5.1 >SELECT id FROM debug WHERE number = 1;
+----+
| id |
+----+
|  0 |
|  0 |
+----+
2 rows in set (0.00 sec)

mysql 5.1 >

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

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

mysql 5.0 > CREATE DATABASE DS;
Query OK, 1 row affected (0.00 sec)

mysql 5.0 > USE DS;
Database changed
mysql 5.0 > CREATE TABLE `debug` (
    ->  `id` int(10) unsigned NOT NULL DEFAULT '0',
    ->  `number` int(10) unsigned NOT NULL DEFAULT '0',
    ->  KEY `siteid` (`number`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql 5.0 >
mysql 5.0 > INSERT INTO `debug` (`id`,`number`)
    -> VALUES
    ->    (1,1),
    ->    (2,1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.0 > OPTIMIZE TABLE debug;
+----------+----------+----------+----------+
| Table    | Op       | Msg_type | Msg_text |
+----------+----------+----------+----------+
| DS.debug | optimize | status   | OK       |
+----------+----------+----------+----------+
1 row in set (0.01 sec)

mysql 5.0 > SELECT id FROM debug WHERE number = 1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.01 sec)

mysql 5.0 > OPTIMIZE TABLE debug;
+----------+----------+----------+-----------------------------+
| Table    | Op       | Msg_type | Msg_text                    |
+----------+----------+----------+-----------------------------+
| DS.debug | optimize | status   | Table is already up to date |
+----------+----------+----------+-----------------------------+
1 row in set (0.00 sec)

mysql 5.0 > SELECT 1 AS constant, number
    -> FROM debug
    -> WHERE number IN (1,2)
    -> GROUP BY constant, number;
+----------+--------+
| constant | number |
+----------+--------+
|        1 |      1 |
+----------+--------+
1 row in set (0.00 sec)

mysql 5.0 > SELECT id FROM debug WHERE number = 1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql 5.0 >

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/97626

3326 Sergey Vojtovich	2010-01-20
      BUG#49902 - SELECT returns incorrect results
      
      Queries optimized with GROUP_MIN_MAX didn't cleanup KEYREAD
      optimization properly. As a result subsequent queries may
      return incomplete rows (fields are initialized to default
      values).
     @ mysql-test/r/group_min_max.result
        A test case for BUG#49902.
     @ mysql-test/t/group_min_max.test
        A test case for BUG#49902.
     @ sql/opt_range.cc
        Whenever KEYREAD optimization is enabled, TABLE::key_read
        must be set to true. It informs cleanup handlers that
        KEYREAD optimization was enabled and must be switched off.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/98443

3336 Sergey Vojtovich	2010-01-28
      BUG#49902 - SELECT returns incorrect results
      
      Queries optimized with GROUP_MIN_MAX didn't cleanup KEYREAD
      optimization properly. As a result subsequent queries may
      return incomplete rows (fields are initialized to default
      values).
     @ mysql-test/r/group_min_max.result
        A test case for BUG#49902.
     @ mysql-test/t/group_min_max.test
        A test case for BUG#49902.
     @ sql/opt_range.cc
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::keyread_[on|off]().
     @ sql/opt_sum.cc
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::keyread_[on|off]().
     @ sql/sql_select.cc
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::keyread_[on|off]().
     @ sql/sql_update.cc
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::keyread_[on|off]().
     @ sql/table.cc
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::keyread_[on|off]().
     @ sql/table.h
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::keyread_[on|off]().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/99658

3336 Sergey Vojtovich	2010-02-09
      BUG#49902 - SELECT returns incorrect results
      
      Queries optimized with GROUP_MIN_MAX didn't cleanup KEYREAD
      optimization properly. As a result subsequent queries may
      return incomplete rows (fields are initialized to default
      values).
     @ mysql-test/r/group_min_max.result
        A test case for BUG#49902.
     @ mysql-test/t/group_min_max.test
        A test case for BUG#49902.
     @ sql/opt_range.cc
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::set_keyread().
     @ sql/opt_sum.cc
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::set_keyread().
     @ sql/sql_select.cc
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::set_keyread().
     @ sql/sql_update.cc
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::set_keyread().
     @ sql/table.cc
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::set_keyread().
     @ sql/table.h
        Refactor of KEYREAD optimization switch so that KEYREAD
        handler state is in sync with st_table::key_read flag.
        
        All SQL code is supposed to switch KEYREAD optimization
        via st_table::set_keyread().

Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:joerg@mysql.com-20100212173307-ph563zr4wmoklgwd) (merge vers: 5.1.45) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100225090938-2j5ybqoau570mytu) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alexey.kopytov@sun.com-20100221213311-xf5nyv391dsw9v6j) (merge vers: 5.5.2-m2) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100224135227-rcqs9pe9b2in80pf) (pib:16)

Noted in 5.1.45, 5.5.3, 6.0.14 changelogs.

Queries optimized with GROUP_MIN_MAX did not clean up KEYREAD
optimizations properly, causing subsequent queries to return
incomplete rows.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)