MySQL Bugs: #25219: SELECT with subqueries causes MySQL server crash

Bug #25219	SELECT with subqueries causes MySQL server crash
Submitted:	20 Dec 2006 15:04	Modified:	1 Feb 2007 1:55
Reporter:	Victoria Reznichenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0bk	OS:	Any (any)
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	bfsm_2007_01_18

Description:
SELECT with 2 subqueries crashes MySQL server.

backtrace:
(gdb) bt
#0  0x08197879 in List<Cached_item>::delete_elements (this=0x8d9c95c) at sql_list.h:367
#1  0x0827f24a in JOIN::cleanup (this=0x8d9bd30, full=true) at sql_select.cc:6188
#2  0x0827f381 in JOIN::join_free (this=0x8d9bd30) at sql_select.cc:6092
#3  0x0827f903 in do_select (join=0x8d9bd30, fields=0x8d9cab0, table=0x0, procedure=0x0) at sql_select.cc:10031
#4  0x08294333 in JOIN::exec (this=0x8d9bd30) at sql_select.cc:1879
#5  0x08380476 in st_select_lex_unit::exec (this=0x8d6d478) at sql_union.cc:472
#6  0x081db2e1 in subselect_union_engine::exec (this=0x8d70e80, full_scan=false) at item_subselect.cc:1779
#7  0x081d9530 in Item_subselect::exec (this=0x8d70df0, full_scan=false) at item_subselect.cc:199
#8  0x081d9fc1 in Item_exists_subselect::val_int (this=0x8d70df0) at item_subselect.cc:626
#9  0x0827f760 in do_select (join=0x8d77458, fields=0x8d230fc, table=0x0, procedure=0x0) at sql_select.cc:9993
#10 0x08294333 in JOIN::exec (this=0x8d77458) at sql_select.cc:1879
#11 0x082904c5 in mysql_select (thd=0x8d22d98, rref_pointer_array=0x8d23198, tables=0x8d5ebe8, wild_num=0, fields=@0x8d230fc, conds=0x8d70df0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2156153344, result=0x8d77448, unit=0x8d22e3c, select_lex=0x8d23070)
    at sql_select.cc:2046
#12 0x082945f1 in handle_select (thd=0x8d22d98, lex=0x8d22dd8, result=0x8d77448, setup_tables_done_option=0) at sql_select.cc:257
#13 0x0822ddf6 in mysql_execute_command (thd=0x8d22d98) at sql_parse.cc:2614
#14 0x082362c0 in mysql_parse (thd=0x8d22d98,
    inBuf=0x8d5d018 "select user_id from\n(SELECT user_id\nfrom bob_events be\njoin bob_events_people_map using (event_id)\njoin bob_people using (person_id)\nwhere action_id=45174\nand role_id in (63521,61387)\n) hosted\nwhere e"..., length=820) at sql_parse.cc:5831
#15 0x08238c88 in dispatch_command (command=COM_QUERY, thd=0x8d22d98, packet=0x8d54fb9 "", packet_length=821) at sql_parse.cc:1775
#16 0x0823a237 in do_command (thd=0x8d22d98) at sql_parse.cc:1557
#17 0x0823b405 in handle_one_connection (arg=0x8d22d98) at sql_parse.cc:1188
#18 0xb7edf34b in start_thread () from /lib/libpthread.so.0
#19 0xb7e0965e in clone () from /lib/libc.so.6

How to repeat:
1. Restore tables from the dump file
2. run SELECT

Another testcase (based on first one), but produces a debug assertion, and very slightly different stack trace.

-------------
DROP TABLE IF EXISTS `t4`;
CREATE TABLE `t4` (`c1` int);
DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (`c1` int,`c2` int );
DROP TABLE IF EXISTS `t6`;
CREATE TABLE `t6` (`c2` int,`c3` int);
DROP TABLE IF EXISTS `t7`;
CREATE TABLE `t7` (`c3` int);
INSERT INTO `t4` VALUES (1);
INSERT INTO `t3` VALUES (1,1);
INSERT INTO `t6` VALUES (1,1);
INSERT INTO `t7` VALUES (1);
select 1
from 
(
	SELECT c3 
	from t4 join t3 using (c1) join t6 using (c2)
) d1
where 
	exists (
	select c3 from t7 where c3=d1.c3
	UNION 
	select c3 from t7 
	);
------------------

At the very first glance this bug and its fixes are not sub/super-sets of fixes for BUG#24127, BUG#22930, BUG#24085.

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

ChangeSet@1.2382, 2007-01-18 11:10:53+03:00, ted@ted.mysql.internal +1 -0
  BUG#25219: SELECT with subqueries causes MySQL server crash
  
  fix: subselect joins should be just partially cleaned up if they are known
       to be uncacheable

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

ChangeSet@1.2382, 2007-01-18 11:31:14+03:00, ted@ted.mysql.internal +1 -0
  BUG#25219: SELECT with subqueries causes MySQL server crash <QUITE UNRELATED!>
  
  fix: make_cond_for_table() cleaned up; hacks to workaround bug with quick selects avoided; 
       see rejects against current test results attached to the bug page

I had the same assertion abort with a simpler query that used no derived tables:

mysql> CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
Query OK, 0 rows affected (0.46 sec)

mysql> CREATE TABLE t2 (c int);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES ('aa', 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1
    ->   WHERE EXISTS (SELECT c FROM t2 WHERE c=1
    ->                 UNION
    ->                 SELECT c from t2 WHERE c=t1.c);
ERROR 2013 (HY000): Lost connection to MySQL server during query

Making t1 not to be a single-row (constant) table did not help:

mysql> INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1
    ->   WHERE EXISTS (SELECT c FROM t2 WHERE c=1
    ->                 UNION
    ->                 SELECT c from t2 WHERE c=t1.c);
ERROR 2013 (HY000): Lost connection to MySQL server during query

Making t2 not to be a single-row (constant) table does not help either with a slightly more complicated non-correlated SELECT:

mysql> CREATE TABLE t3 (c int);
ERROR 1050 (42S01): Table 't3' already exists
mysql> INSERT INTO t3 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1
    ->   WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
    ->                 UNION
    ->                 SELECT c from t2 WHERE c=t1.c);
ERROR 2013 (HY000): Lost connection to MySQL server during query

In all these cases I had a failure with the same assertion abort:

mysqld: sql_select.cc:10057: int do_select(JOIN*, List<Item>*, TABLE*, Procedure*): Assertion `join->tables' failed.

Program received signal SIGABRT, Aborted.

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

ChangeSet@1.2386, 2007-01-19 00:17:28-08:00, igor@olga.mysql.com +4 -0
  Fixed bug #25219: crash for a query that contains an EXIST subquery with
  UNION over correlated and uncorrelated SELECTS.
  In such subqueries each uncorrelated SELECT should be considered as
  uncacheable. Otherwise join_free is called for it and in many cases
  it causes some problems.

The fix has been pushed into 5.0.36, 5.1.16-beta main trees.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.36 and 5.1.16 changelogs.