MySQL Bugs: #36011: server crash with explain extended on query with dependent subqueries

Bug #36011	server crash with explain extended on query with dependent subqueries
Submitted:	12 Apr 2008 1:16	Modified:	22 May 2008 15:45
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.0.58-debug, 5.1.24-debug	OS:	Any (Windows XP SP2)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
explain with the 'extended' keyword causes a crash with stack trace:

mysqld.exe!Item_field::print
mysqld.exe!st_select_lex::print_order
mysqld.exe!st_select_lex::print
mysqld.exe!Item_subselect::print
mysqld.exe!Item_func::print_args
mysqld.exe!Item_func::print
mysqld.exe!Item_func::print_args
mysqld.exe!Item_func::print
mysqld.exe!Item_func_not::print
mysqld.exe!Item_cond::print
mysqld.exe!st_select_lex::print
mysqld.exe!st_select_lex_unit::print
mysqld.exe!execute_sqlcom_select
mysqld.exe!mysql_execute_command
mysqld.exe!mysql_parse
mysqld.exe!dispatch_command
mysqld.exe!do_command
mysqld.exe!handle_one_connection
mysqld.exe!pthread_start
mysqld.exe!_callthreadstart
mysqld.exe!_threadstart
kernel32.dll!BaseThreadStart

How to repeat:
drop table if exists `t1`;
create table `t1` (`a` int,`b` int) engine=myisam;
insert `t1` values (),();
explain extended select 1 from t1 where 1 not in 
(select 1 from t1 where a=(select 1)group by b)
and a=(select 1);

this reminded me of bug #28728

May be a regression, as 5.0.56 works:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.56-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.23 sec)

mysql> create table `t1` (`a` int,`b` int) engine=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> insert `t1` values (),();
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> explain extended select 1 from t1 where 1 not in
    -> (select 1 from t1 where a=(select 1)group by b)
    -> and a=(select 1);
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+----------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref
 | rows | Extra                                        |
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+----------------------------------------------+
|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL
 |    2 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | t1    | ALL  | NULL          | NULL | NULL    | NULL
 |    2 | Using where; Using temporary; Using filesort |
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+----------------------------------------------+
2 rows in set, 3 warnings (0.03 sec)

It is also NOT repetable with latest 5.0.60-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.60 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `t1` (`a` int,`b` int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> insert `t1` values (),();
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> explain extended select 1 from t1 where 1 not in
    -> (select 1 from t1 where a=(select 1)group by b)
    -> and a=(select 1);
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; Using temporary; Using filesort |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
2 rows in set, 3 warnings (0.02 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1249
Message: Select 3 was reduced during optimization
*************************** 2. row ***************************
  Level: Note
   Code: 1249
Message: Select 4 was reduced during optimization
*************************** 3. row ***************************
  Level: Note
   Code: 1003
Message: select 1 AS `1` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (not(<in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` = 1) group by `test`.`t1`.`b` having (<cache>(1) = <ref_null_helper>(1)))))))
3 rows in set (0.00 sec)

Same results on 5.1.25-BK on Linux... So, looks like either one has to use debug binaries or it is Windows-specific (build-specific) bug only. Please, check.

No crash with 5.0.58 non-debug:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.58-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.03 sec)

mysql> create table `t1` (`a` int,`b` int) engine=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> insert `t1` values (),();
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> explain extended select 1 from t1 where 1 not in
    -> (select 1 from t1 where a=(select 1)group by b)
    -> and a=(select 1);
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+----------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref
 | rows | Extra                                        |
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+----------------------------------------------+
|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL
 |    2 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | t1    | ALL  | NULL          | NULL | NULL    | NULL
 |    2 | Using where; Using temporary; Using filesort |
+----+--------------------+-------+------+---------------+------+---------+-----
-+------+----------------------------------------------+
2 rows in set, 3 warnings (0.06 sec)

5.0.58 x64 crashed on windows for me. I'll upload a larger testcase more guaranteed to crash later.

Debug binary of 5.0.58 crashes with a bit different stack trace:

 	mysqld-debug.exe!Item_field::print(String * str=0x0565f1b8)  Line 5100 + 0x12 bytes	C++
 	mysqld-debug.exe!st_select_lex::print_order(String * str=0x0565f1b8, st_order * order=0x03daaff8)  Line 1662 + 0x1e bytes	C++
 	mysqld-debug.exe!st_select_lex::print(THD * thd=0x03d7c868, String * str=0x0565f1b8)  Line 15802 + 0x13 bytes	C++
 	mysqld-debug.exe!subselect_single_select_engine::print(String * str=0x0565f1b8)  Line 2332	C++
 	mysqld-debug.exe!Item_subselect::print(String * str=0x0565f1b8)  Line 266 + 0x17 bytes	C++
 	mysqld-debug.exe!Item_in_subselect::print(String * str=0x0565f1b8)  Line 1510	C++
 	mysqld-debug.exe!Item_func::print_args(String * str=0x0565f1b8, unsigned int from=0)  Line 386 + 0x26 bytes	C++
 	mysqld-debug.exe!Item_func::print(String * str=0x0565f1b8)  Line 376	C++
 	mysqld-debug.exe!Item_func::print_args(String * str=0x0565f1b8, unsigned int from=0)  Line 386 + 0x26 bytes	C++
 	mysqld-debug.exe!Item_func::print(String * str=0x0565f1b8)  Line 376	C++
 	mysqld-debug.exe!Item_func_not::print(String * str=0x0565f1b8)  Line 253	C++
 	mysqld-debug.exe!Item_cond::print(String * str=0x0565f1b8)  Line 3985 + 0x14 bytes	C++
 	mysqld-debug.exe!st_select_lex::print(THD * thd=0x03d7c868, String * str=0x0565f1b8)  Line 15793 + 0x14 bytes	C++
 	mysqld-debug.exe!st_select_lex_unit::print(String * str=0x0565f1b8)  Line 1634	C++
 	mysqld-debug.exe!mysql_execute_command(THD * thd=0x03d7c868)  Line 2708	C++
 	mysqld-debug.exe!mysql_parse(THD * thd=0x03d7c868, const char * inBuf=0x03da9dc0, unsigned int length=114, const char * * found_semicolon=0x0565fd74)  Line 6158 + 0x9 bytes	C++
 	mysqld-debug.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x03d7c868, char * packet=0x03da1d11, unsigned int packet_length=115)  Line 1874 + 0x1e bytes	C++
 	mysqld-debug.exe!do_command(THD * thd=0x03d7c868)  Line 1580 + 0x31 bytes	C++
 	mysqld-debug.exe!handle_one_connection(void * arg=0x03d7c868)  Line 1186 + 0x9 bytes	C++
 	mysqld-debug.exe!pthread_start(void * param=0x03d81ea0)  Line 85 + 0x9 bytes	C
>	mysqld-debug.exe!_threadstart(void * ptd=0x03d6feb0)  Line 196 + 0xd bytes	C

5,1.24-debug also crashed (no crash with non-debug binary on my laptop):

 	mysqld-debug.exe!Item_field::print(String * str=0x0578e1ac, enum_query_type query_type=QT_ORDINARY)  Line 5336 + 0x12 bytes	C++
 	mysqld-debug.exe!st_select_lex::print_order(String * str=0x0578e1ac, st_order * order=0x046b1bb8, enum_query_type query_type=QT_ORDINARY)  Line 1996 + 0x24 bytes	C++
 	mysqld-debug.exe!st_select_lex::print(THD * thd=0x04644cc8, String * str=0x0578e1ac, enum_query_type query_type=QT_ORDINARY)  Line 16525 + 0x17 bytes	C++
 	mysqld-debug.exe!subselect_single_select_engine::print(String * str=0x0578e1ac, enum_query_type query_type=QT_ORDINARY)  Line 2413	C++
 	mysqld-debug.exe!Item_subselect::print(String * str=0x0578e1ac, enum_query_type query_type=QT_ORDINARY)  Line 312 + 0x1b bytes	C++
 	mysqld-debug.exe!Item_in_subselect::print(String * str=0x0578e1ac, enum_query_type query_type=QT_ORDINARY)  Line 1588	C++
 	mysqld-debug.exe!Item_func::print_args(String * str=0x0578e1ac, unsigned int from=0, enum_query_type query_type=QT_ORDINARY)  Line 393 + 0x2c bytes	C++
 	mysqld-debug.exe!Item_func::print(String * str=0x0578e1ac, enum_query_type query_type=QT_ORDINARY)  Line 383	C++
 	mysqld-debug.exe!Item_func::print_args(String * str=0x0578e1ac, unsigned int from=0, enum_query_type query_type=QT_ORDINARY)  Line 393 + 0x2c bytes	C++
 	mysqld-debug.exe!Item_func::print(String * str=0x0578e1ac, enum_query_type query_type=QT_ORDINARY)  Line 383	C++
 	mysqld-debug.exe!Item_func_not::print(String * str=0x0578e1ac, enum_query_type query_type=QT_ORDINARY)  Line 293	C++
 	mysqld-debug.exe!Item_cond::print(String * str=0x0578e1ac, enum_query_type query_type=QT_ORDINARY)  Line 4102 + 0x18 bytes	C++
 	mysqld-debug.exe!st_select_lex::print(THD * thd=0x04644cc8, String * str=0x0578e1ac, enum_query_type query_type=QT_ORDINARY)  Line 16516 + 0x18 bytes	C++
 	mysqld-debug.exe!st_select_lex_unit::print(String * str=0x0578e1ac, enum_query_type query_type=QT_ORDINARY)  Line 1965	C++
 	mysqld-debug.exe!execute_sqlcom_select(THD * thd=0x04644cc8, TABLE_LIST * all_tables=0x046b0b98)  Line 4737	C++
 	mysqld-debug.exe!mysql_execute_command(THD * thd=0x04644cc8)  Line 2052 + 0xd bytes	C++
 	mysqld-debug.exe!mysql_parse(THD * thd=0x04644cc8, const char * inBuf=0x046b09d0, unsigned int length=114, const char * * found_semicolon=0x0578fd00)  Line 5630 + 0x9 bytes	C++
 	mysqld-debug.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x04644cc8, char * packet=0x046a8921, unsigned int packet_length=114)  Line 1121 + 0x1e bytes	C++
 	mysqld-debug.exe!do_command(THD * thd=0x04644cc8)  Line 781 + 0x1b bytes	C++
 	mysqld-debug.exe!handle_one_connection(void * arg=0x04644cc8)  Line 1115 + 0x9 bytes	C++
 	mysqld-debug.exe!pthread_start(void * param=0x04642c68)  Line 85 + 0x9 bytes	C
>	mysqld-debug.exe!_threadstart(void * ptd=0x046a4088)  Line 196 + 0xd bytes	C

5.0.58 and 5.1.24 release binaries crashed on windows 64-bit. so, it looks like a memory corruption.

This is reproducible by even a simpler query : 
explain extended select 1 from t1 where 1 in (select 1 from t1 t1_inner group by a);

What happens here is that because the IN predicate does not depend on the outer table it's being evaluated even for EXPLAIN.
And because the inner select makes a temporary table to calculate the GROUP BY the fields get substituted by references to the temp table by JOIN::exec() and this is never reversed.
So after JOIN::exec for the inner select is done the fields in EXPLAIN are a reference to free and EXPLAIN tries to dereference this.

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/46765

ChangeSet@1.2626, 2008-05-16 16:07:34+03:00, gkodinov@magare.gmz +3 -0
  Bug #36011: server crash with explain extended on query 
     with dependent subqueries
  An IN subquery is executed on EXPLAIN when it's not correlated.
  But if the subquery contained a derived table the internal 
  structures were not restored.
  Fixed by restoring the ref array when a temp tables were used in
  executing the IN subquery.

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/46770

ChangeSet@1.2626, 2008-05-16 16:28:22+03:00, gkodinov@magare.gmz +3 -0
  Bug #36011: server crash with explain extended on query 
     with dependent subqueries
  An IN subquery is executed on EXPLAIN when it's not correlated.
  But if the subquery contained a derived table the internal 
  structures were not restored.
  Fixed by restoring the ref array when a temp tables were used in
  executing the IN subquery.

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/46773

ChangeSet@1.2626, 2008-05-16 17:05:55+03:00, gkodinov@magare.gmz +3 -0
  Bug #36011: server crash with explain extended on query 
     with dependent subqueries
  An IN subquery is executed on EXPLAIN when it's not correlated.
  If the subquery required a temporary table for its execution
  not all the internal structures were restored from pointing to
  the items of the temporary table to point back to the items of
  the subquery.
  Fixed by restoring the ref array when a temp tables were used in
  executing the IN subquery during EXPLAIN EXTENDED.

Pushed into 5.0.64

Pushed into 5.1.25-rc

Noted in 5.0.64, 5.1.25 changelogs.

For EXPLAIN EXTENDED, execution of an uncorrelated IN subquery caused
a crash if the subquery required a temporary table for its execution.

Setting report to Need Doc Info pending push into 6.0.x.

Pushed into 6.0.6-alpha

Noted in 6.0.6 changelog.

Pushed into 5.0.64  (revid:sp1r-gkodinov/kgeorge@magare.gmz-20080516155643-24622) (pib:2)
(Retry automatic marking, to ensure nothing is missed. cm01)

Pushed into 5.1.25-rc  (revid:sp1r-gkodinov/kgeorge@magare.gmz-20080516160350-23334) (version source revid:sp1r-gkodinov/kgeorge@magare.gmz-20080516160350-23334) (pib:3)