Bug #46629 Item_in_subselect::val_int(): Assertion `0' on subquery inside a SP

Description:
When calling a SP containing a subquery, 5.1 asserted as follows:

mysqld: item_subselect.cc:813: virtual longlong Item_in_subselect::val_int(): Assertion `0' failed.

#6  0x000000315a42bec9 in __assert_fail () from /lib64/libc.so.6
#7  0x00000000005ee0d9 in Item_in_subselect::val_int (this=0x1f250d8) at item_subselect.cc:813
#8  0x0000000000594e0e in eval_const_cond (cond=0x1f250d8) at item_func.cc:63
#9  0x00000000006cf4f7 in remove_eq_conds (thd=0x1eaf718, cond=0x1f250d8, cond_value=0x1f29f68) at sql_select.cc:9124
#10 0x00000000006d4883 in optimize_cond (join=0x1f28990, conds=0x1f250d8, join_list=0x1f423f0, cond_value=0x1f29f68) at sql_select.cc:8972
#11 0x00000000006ddbc8 in JOIN::optimize (this=0x1f28990) at sql_select.cc:830
#12 0x00000000006e23d3 in mysql_select (thd=0x1eaf718, rref_pointer_array=0x1f42458, tables=0x1f42eb0, wild_num=0, fields=@0x1f42390, conds=0x1f250d8,
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147765760, result=0x1f28970, unit=0x1f41e60, select_lex=0x1f42288)
    at sql_select.cc:2384
#13 0x00000000006e79c7 in handle_select (thd=0x1eaf718, lex=0x1f41dc0, result=0x1f28970, setup_tables_done_option=0) at sql_select.cc:268
#14 0x0000000000652657 in execute_sqlcom_select (thd=0x1eaf718, all_tables=0x1f42eb0) at sql_parse.cc:5011
#15 0x0000000000654528 in mysql_execute_command (thd=0x1eaf718) at sql_parse.cc:2206
#16 0x00000000008152df in sp_instr_stmt::exec_core (this=0x1f25230, thd=0x1eaf718, nextp=0x7fd11256d1c8) at sp_head.cc:2905
#17 0x0000000000815517 in sp_lex_keeper::reset_lex_and_exec_core (this=0x1f25270, thd=0x1eaf718, nextp=0x7fd11256d1c8, open_tables=false, instr=0x1f25230)
    at sp_head.cc:2734
#18 0x000000000081b6b7 in sp_instr_stmt::execute (this=0x1f25230, thd=0x1eaf718, nextp=0x7fd11256d1c8) at sp_head.cc:2848
#19 0x00000000008177ef in sp_head::execute (this=0x1f41608, thd=0x1eaf718) at sp_head.cc:1252
#20 0x000000000081857e in sp_head::execute_procedure (this=0x1f41608, thd=0x1eaf718, args=0x1eb1a88) at sp_head.cc:1982
#21 0x000000000065b0a4 in mysql_execute_command (thd=0x1eaf718) at sql_parse.cc:4352
#22 0x000000000065d3eb in mysql_parse (thd=0x1eaf718, inBuf=0x1f17698 "CALL  p1_4_A", length=12, found_semicolon=0x7fd11256eed0) at sql_parse.cc:5931
#23 0x000000000065e215 in dispatch_command (command=COM_QUERY, thd=0x1eaf718, packet=0x1f03c59 "CALL  p1_4_A", packet_length=12) at sql_parse.cc:1213
#24 0x000000000065f5e4 in do_command (thd=0x1eaf718) at sql_parse.cc:854
#25 0x000000000064bf15 in handle_one_connection (arg=0x1eaf718) at sql_connect.cc:1127
#26 0x000000315b0073da in start_thread () from /lib64/libpthread.so.0
#27 0x000000315a4e627d in clone () from /lib64/libc.so.6

For some reason, Azalea is not affected.

How to repeat:
--disable_abort_on_error

DELIMITER |;
CREATE PROCEDURE p1_4_A  () BEGIN SELECT  `int_key` FROM  t1_part_3_A  A WHERE A.`pk` IN ( SELECT `pk` FROM  t1_base_0_A  AS B ) ; END|
DELIMITER ;|

CREATE TABLE IF NOT EXISTS t1_part_3_A  (`int_key` INTEGER);
CREATE TABLE IF NOT EXISTS t1_base_0_A  (`int_key` INTEGER, `pk` integer primary key);

CALL  p1_4_A;
CALL  p1_4_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/82414

3108 Ramil Kalimullin	2009-09-04
      Fix for bug#46629: Item_in_subselect::val_int(): Assertion `0' 
      on subquery inside a SP 
      
      Problem: repeated call of a SP containing an incorrect query with a 
      subselect may lead to failed ASSERT().
      
      Fix: set proper sublelect's state in case of error occured during 
      subquery transformation.
     @ mysql-test/r/sp.result
        Fix for bug#46629: Item_in_subselect::val_int(): Assertion `0' 
        on subquery inside a SP 
          - test result.
     @ mysql-test/t/sp.test
        Fix for bug#46629: Item_in_subselect::val_int(): Assertion `0' 
        on subquery inside a SP 
          - test case.
     @ sql/item_subselect.cc
        Fix for bug#46629: Item_in_subselect::val_int(): Assertion `0' 
        on subquery inside a SP 
          - don't set Item_subselect::changed in the Item_subselect::fix_fields()
        if an error occured during subquery transformation.
        That prevents us of further processing incorrect subqueries after 
        Item_in_subselect::select_in_like_transformer().

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)

Noted in 5.4.4 changelog.

Assertion failure could result from repeated execution of a stored
procedure containing an incorrect query with a subselect. 

Setting report to NDI pending push into 5.1.x.

Moved 5.4 changelog entry from 5.4.4 to 5.4.3.

Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:mikael@mysql.com-20090907102257-fflwgm6rp5m5kmfw) (merge vers: 5.1.39) (pib:11)

Noted in 5.1.40 changelog.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

Already documented in 5.1.40

this assert still happens with another testcase, in bug #48294