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:
None 
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:30] Manuel Matonin
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.
[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] Shane Bester
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.