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: | |
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 |
[23 Dec 2009 22:07]
Matt Light
[23 Dec 2009 22:51]
MySQL Verification Team
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 >
[20 Jan 2010 16:39]
Bugs System
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.
[28 Jan 2010 10:51]
Bugs System
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]().
[9 Feb 2010 8:55]
Bugs System
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().
[1 Mar 2010 8:43]
Bugs System
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)
[2 Mar 2010 14:36]
Bugs System
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)
[2 Mar 2010 14:41]
Bugs System
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)
[2 Mar 2010 14:46]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100224135227-rcqs9pe9b2in80pf) (pib:16)
[8 Apr 2010 17:48]
Paul DuBois
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.
[17 Jun 2010 11:54]
Bugs System
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)
[17 Jun 2010 12:32]
Bugs System
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)
[17 Jun 2010 13:20]
Bugs System
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)