| Bug #27807 | Server crash when executing subquery with EXPLAIN | ||
|---|---|---|---|
| Submitted: | 13 Apr 2007 11:30 | Modified: | 10 May 2007 17:40 |
| Reporter: | Manuel Matonin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.37-community-nt-log, 5.1, 5.2 | OS: | Any |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[13 Apr 2007 11:42]
Sveta Smirnova
Thank for the report. Verified as described on Linux using last BK sources. All 5.x versions are affected. Version 4.1 works fine.
[13 Apr 2007 14:12]
MySQL Verification Team
full stack trace from 5.0.40
Attachment: bug27807_stack_5.0.40_windows.txt (text/plain), 2.71 KiB.
[16 Apr 2007 9:35]
Timour Katchaounov
Changed priority to P1 as this is a crashing bug.
[17 Apr 2007 14:50]
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/24675 ChangeSet@1.2456, 2007-04-17 17:50:24+03:00, gkodinov@magare.gmz +5 -0 Bug #27807: When we execute EXPLAIN no query or subquery should actually be executed. Thus we must not consider as sargable equality predicates the predicates that contain subqueries in their right hand operator while doing EXPLAIN. Note that these predicates will be in effect when actually executing the statement. We don't have similar problem with either stored procedure or prepare because none sargable predicate selection (as part of JOIN::optimize) is not done during preparation phase.
[25 Apr 2007 13:05]
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/25401 ChangeSet@1.2440, 2007-04-25 16:05:09+03:00, gkodinov@magare.gmz +3 -0 Bug #27807: When creating the temp table structure to materialize a scalar subquery one of its members is not set. This causes GPF in EXPLAIN. Fixed by seting the pos_in_table_list to empty and displaying the proper text in select_describe().
[26 Apr 2007 14: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/25516 ChangeSet@1.2440, 2007-04-26 17:39:22+03:00, gkodinov@magare.gmz +5 -0 Bug #27807. Non-correlated scalar subqueries may get executed in EXPLAIN at the optimization phase if they are part of a right hand sargable expression. If the scalar subquery uses a temp table to materialize its results it will replace the subquery structure from the parser with a simple select from the materialization table. As a result the EXPLAIN will crash as the temporary materialization table is not to be shown in EXPLAIN at all. Fixed by preserving the original query structure right after calling optimize() for scalar subqueries with temp tables executed during EXPLAIN.
[4 May 2007 7:49]
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/26070 ChangeSet@1.2440, 2007-05-04 10:48:51+03:00, gkodinov@magare.gmz +5 -0 Bug #27807. Non-correlated scalar subqueries may get executed in EXPLAIN at the optimization phase if they are part of a right hand sargable expression. If the scalar subquery uses a temp table to materialize its results it will replace the subquery structure from the parser with a simple select from the materialization table. As a result the EXPLAIN will crash as the temporary materialization table is not to be shown in EXPLAIN at all. Fixed by preserving the original query structure right after calling optimize() for scalar subqueries with temp tables executed during EXPLAIN.
[7 May 2007 18:15]
Bugs System
Pushed into 5.1.18-beta
[8 May 2007 0:16]
Paul DuBois
Noted in 5.1.18 changelog. Certain queries that used non-correlated scalar subqueries caused EXPLAIN to to crash. Setting report to Patch Queued; will this be pushed into 5.0.x?
[10 May 2007 6:52]
Bugs System
Pushed into 5.0.42
[10 May 2007 17:40]
Paul DuBois
Noted in 5.0.42 changelog.
Description: Server cuases AV when executing subquery with EXPLAIN How to repeat: c:\mysql\bin>mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 5.0.37-community-nt-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use logi; Database changed mysql> CREATE TABLE `ud` ( -> `Server` char(50) default NULL, -> `Updatepacket` int(10) unsigned default NULL, -> `Name` char(255) default NULL, -> `Id` bigint(20) unsigned NOT NULL auto_increment, -> PRIMARY KEY (`id`), -> UNIQUE KEY `updatepacket_2` (`updatepacket`,`Server`), -> KEY `updatepacket` (`updatepacket`) -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO `ud` VALUES ('a60882f92b65448d054d3058c8fa1ff0',403,NULL,15000); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `ud` VALUES ('6b6a5f466001109fdad29e52fff2a647',251,NULL,15002); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `ud` VALUES ('T2R9C-A4F8D-CBFHW-OIMD2',32,'TEST',15003); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `ud` VALUES ('T2R9C-A4F8D-CBFHW-OIMD2',46,NULL,15004); Query OK, 1 row affected (0.00 sec) mysql> EXPLAIN -> SELECT -> Name -> FROM ud WHERE id = ( -> SELECT MAX(Id) -> FROM ud -> WHERE -> Name IS NOT NULL AND -> Server="T2R9C-A4F8D-CBFHW-OIMD2" -> GROUP BY Name -> ); ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> Suggested fix: Minor query parser changes. Crash is caused by NULL pointer at line 14835 in sql_select.cpp.