MySQL Bugs: #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122

Bug #32036	EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
Submitted:	1 Nov 2007 18:45	Modified:	9 Jan 2008 14:24
Reporter:	Chris Bacon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.23,5.0.50	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	crash, exists, subquery, UNION

Description:
When the following SQL statement is executed, MySQL crashes:

SELECT t0.`__ID__`
FROM patient AS t0
WHERE EXISTS((SELECT * FROM `Contract` AS t1
WHERE t1.`Patient`=t0.`__ID__`));

It does not crash if the double brackets around the EXISTS clause is not present.

This is using an InnoDB database.

How to repeat:
CREATE TABLE `contract` (
  `__ID__` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `Patient` binary(16) DEFAULT NULL,
  PRIMARY KEY (`__ID__`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `patient` (
  `__ID__` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  PRIMARY KEY (`__ID__`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

SELECT t0.`__ID__`
FROM patient AS t0
WHERE EXISTS((SELECT * FROM `Contract` AS t1
WHERE t1.`Patient`=t0.`__ID__`));

Thank you for a bug report.  Verified.

0x82018a5 handle_segfault + 541
0x81588d5 Item_field::fix_outer_field(THD*, Field**, Item**) + 147
0x81593b6 Item_field::fix_fields(THD*, Item**) + 606
0x8171bbb Item_func::fix_fields(THD*, Item**) + 205
0x8251408 setup_conds(THD*, TABLE_LIST*, TABLE_LIST*, Item**) + 400
0x825d554 JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*) + 840
0x81c2765 subselect_single_select_engine::prepare() + 345
0x81bdc36 Item_subselect::fix_fields(THD*, Item**) + 140
0x8251408 setup_conds(THD*, TABLE_LIST*, TABLE_LIST*, Item**) + 400
0x825d554 JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*) + 840
0x8262421 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select + 529
0x825cfad handle_select(THD*, st_lex*, select_result*, unsigned long) + 365
0x8216324 execute_sqlcom_select(THD*, TABLE_LIST*) + 772
0x820f09b mysql_execute_command(THD*) + 1709
0x8217d80 mysql_parse(THD*, char const*, unsigned int, char const**) + 372
0x820d4bb dispatch_command(enum_server_command, THD*, char*, unsigned int) + 2383
0x820cb60 do_command(THD*) + 658
0x820b525 handle_one_connection + 255
0x40038aa7 _end + 931796887
0x4017ec2e _end + 933132574

testcase:
----------------------

drop table if exists `t1`;
create table `t1` (`a` int)engine=myisam;
drop table if exists `t2`;
create table `t2` (`a` int)engine=myisam;
select 1 from `t1` where exists ((select 1 from `t2` where `t1`.`a`=t2.`a`));
explain select 1 from `t1` where exists ((select 1 from `t2` where `t1`.`a`=t2.`a`));
explain extended select 1 from `t1` where exists ((select 1 from `t2` where `t1`.`a`=t2.`a`));

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

ChangeSet@1.2561, 2007-11-06 12:42:20+02:00, gkodinov@magare.gmz +4 -0
  Bug #32036: EXISTS within a WHERE clause with a UNION 
    crashes MySQL 5.122
  When the parser processes extra parenthesis in sub-queries, 
  e.g. SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2))
  it makes an additional derived table :
  SELECT 2 FROM t1 WHERE EXISTS (SELECT * FROM (SELECT 1 FROM t2) x);
  instead of just removing the extra set of parenthesis.
  This is done because the parser generates code. And it needs to
  know in advance that it needs to make the derived table transformation
  in the above case.
  But it may know if it really needs this transformation only at the end
  of parsing : when it parses the UNION clauses.
  This was causing code (resolving the outer references in sub-queries) 
  that doesn't expect to have these derived tables without a cause to 
  crash.
  Fixed by extending the name resolution code to account for such 
  derived tables.
  Added a TODO item in the parser to remove these.

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

ChangeSet@1.2561, 2007-11-15 15:31:39+02:00, gkodinov@magare.gmz +3 -0
  Bug #32036: EXISTS within a WHERE clause with a UNION 
    crashes MySQL 5.122
  There was a difference in how UNIONs are handled
  on top level and when in sub-query.
  Because the rules for sub-queries were syntactically
  allowing cases that are not currently supported by
  the server we had crashes (this bug) or wrong results
  (bug 32051).
  Fixed by making the syntax rules for UNIONs match the 
  ones at top level.
  
  These rules however do not support nesting UNIONs, e.g.
  (SELECT a FROM t1 UNION ALL SELECT b FROM t2) 
   UNION
  (SELECT c FROM t3 UNION ALL SELECT d FROM t4)
  Supports for statements with nested UNIONs will be
  added in a future version.

Patch is OK, but I'd rename select_init2 to select_init.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.

Within a subquery, UNION was handled differently than at the top 
level, which could result in incorrect results or a server crash.