Bug #58243 RQG test optimizer_subquery causes server crash when running with ICP
Submitted: 16 Nov 2010 22:20 Modified: 11 Dec 2010 17:45
Reporter: Olav Sandstå Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.99 OS:Any
Assigned to: Olav Sandstå CPU Architecture:Any
Tags: index_condition_pushdown, optimizer_switch

[16 Nov 2010 22:20] Olav Sandstå
Description:
The random query generator test optimizer_subquery fails when running it with index condition pushdown (ICP) enabled. The server crashes when hitting the following assert in InnoDB:

ut_ad(trx->n_active_thrs == 1);

in the function que_thr_stop_for_mysql_no_error() (in que/que0que.c).

The call stack looks like:

#5  0x0000003723631bf0 in abort () from /lib64/libc.so.6
#6  0x00000000009c89a1 in que_thr_stop_for_mysql_no_error (thr=0x10f34718, trx=0x10ef82c8)
    at /export/home/tmp/olav/opt-back-tmp/storage/innobase/que/que0que.c:1040
#7  0x00000000008ea473 in row_search_for_mysql (buf=0x10f35ce0 "��", mode=1, prebuilt=0x10e48028, 
    match_mode=0, direction=0) at /export/home/tmp/olav/opt-back-tmp/storage/innobase/row/row0sel.c:4788
#8  0x00000000008c633f in ha_innobase::index_read (this=0x10f35a20, buf=0x10f35ce0 "��", key_ptr=0x0, 
    key_len=0, find_flag=HA_READ_AFTER_KEY)
    at /export/home/tmp/olav/opt-back-tmp/storage/innobase/handler/ha_innodb.cc:6067
#9  0x00000000008bc542 in ha_innobase::index_first (this=0x10f35a20, buf=0x10f35ce0 "��")
    at /export/home/tmp/olav/opt-back-tmp/storage/innobase/handler/ha_innodb.cc:6369
#10 0x00000000008c5fe4 in ha_innobase::rnd_next (this=0x10f35a20, buf=0x10f35ce0 "��")
    at /export/home/tmp/olav/opt-back-tmp/storage/innobase/handler/ha_innodb.cc:6466
#11 0x000000000072eea9 in handler::ha_rnd_next (this=0x10f35a20, buf=0x10f35ce0 "��")
    at /export/home/tmp/olav/opt-back-tmp/sql/handler.cc:2197
#12 0x00000000008084f9 in rr_sequential (info=0x10f20120)
    at /export/home/tmp/olav/opt-back-tmp/sql/records.cc:449
#13 0x00000000005f7e0c in join_init_read_record (tab=0x10f20098)
    at /export/home/tmp/olav/opt-back-tmp/sql/sql_select.cc:18212
#14 0x00000000005fad0a in sub_select (join=0x10f40ab8, join_tab=0x10f20098, end_of_records=false)
    at /export/home/tmp/olav/opt-back-tmp/sql/sql_select.cc:17305
#15 0x0000000000609044 in do_select (join=0x10f40ab8, fields=0x10f40dd0, table=0x0, procedure=0x0)
    at /export/home/tmp/olav/opt-back-tmp/sql/sql_select.cc:16854
#16 0x0000000000625b11 in JOIN::exec (this=0x10f40ab8)
    at /export/home/tmp/olav/opt-back-tmp/sql/sql_select.cc:3297
#17 0x00000000007b6573 in subselect_hash_sj_engine::exec (this=0x10efc770)
    at /export/home/tmp/olav/opt-back-tmp/sql/item_subselect.cc:3395
#18 0x00000000007b7377 in Item_subselect::exec (this=0x10f3f878)
    at /export/home/tmp/olav/opt-back-tmp/sql/item_subselect.cc:311
#19 0x00000000007bba3c in Item_in_subselect::exec (this=0x10f3f878)
    at /export/home/tmp/olav/opt-back-tmp/sql/item_subselect.cc:427
#20 0x00000000007b365e in Item_in_subselect::val_bool (this=0x10f3f878)
    at /export/home/tmp/olav/opt-back-tmp/sql/item_subselect.cc:1023
#21 0x0000000000553a80 in Item::val_bool_result (this=0x10f3f878)
    at /export/home/tmp/olav/opt-back-tmp/sql/item.h:868
#22 0x0000000000760513 in Item_in_optimizer::val_int (this=0x10f41140)
    at /export/home/tmp/olav/opt-back-tmp/sql/item_cmpfunc.cc:1947
#23 0x000000000074154e in Item::val_bool (this=0x10f41140)
    at /export/home/tmp/olav/opt-back-tmp/sql/item.cc:200
#24 0x000000000075ede9 in Item_cond_and::val_int (this=0x10efc440)
    at /export/home/tmp/olav/opt-back-tmp/sql/item_cmpfunc.cc:4641
#25 0x00000000008b9f32 in innobase_index_cond (file=0x10ea27e0)
    at /export/home/tmp/olav/opt-back-tmp/storage/innobase/handler/ha_innodb.cc:12323
#26 0x00000000008e7cf2 in row_search_idx_cond_check (mysql_rec=0x10ea2aa0 "��[", prebuilt=0x10e4bf08, 
    rec=0x2aaaab4e05ec "\200", offsets=0x49c11e90)
#27 0x00000000008e9abf in row_search_for_mysql (buf=0x10ea2aa0 "��[", mode=2, prebuilt=0x10e4bf08, 
    match_mode=1, direction=0) at /export/home/tmp/olav/opt-back-tmp/storage/innobase/row/row0sel.c:4424
#28 0x00000000008c633f in ha_innobase::index_read (this=0x10ea27e0, buf=0x10ea2aa0 "�
    key_ptr=0x10f6cb00 "\001", key_len=4, find_flag=HA_READ_KEY_EXACT)
    at /export/home/tmp/olav/opt-back-tmp/storage/innobase/handler/ha_innodb.cc:6067
#29 0x000000000073987e in handler::index_read_map (this=0x10ea27e0, buf=0x10ea2aa0 "�
    key=0x10f6cb00 "\001", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
    at /export/home/tmp/olav/opt-back-tmp/sql/handler.h:1805
#30 0x000000000072edb3 in handler::ha_index_read_map (this=0x10ea27e0, buf=0x10ea2aa0 "�
    key=0x10f6cb00 "\001", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
    at /export/home/tmp/olav/opt-back-tmp/sql/handler.cc:2223
#31 0x00000000005f7cef in join_read_always_key (tab=0x10efae78)
    at /export/home/tmp/olav/opt-back-tmp/sql/sql_select.cc:18080
#32 0x00000000005fad0a in sub_select (join=0x10f40060, join_tab=0x10efae78, end_of_records=false)
    at /export/home/tmp/olav/opt-back-tmp/sql/sql_select.cc:17305
#33 0x0000000000609044 in do_select (join=0x10f40060, fields=0x0, table=0x11039a00, procedure=0x0)
    at /export/home/tmp/olav/opt-back-tmp/sql/sql_select.cc:16854
#34 0x00000000006241f7 in JOIN::exec (this=0x10f40060)
    at /export/home/tmp/olav/opt-back-tmp/sql/sql_select.cc:2906
#35 0x000000000062009f in mysql_select (thd=0x10e6e750, rref_pointer_array=0x10e70c70, 
    tables=0x10eebd58, wild_num=0, fields=@0x10e70b90, conds=0x10f3f9f0, og_num=3, order=0x10f3fd80, 
    group=0x10f3fc00, having=0x0, proc_param=0x0, select_options=2147748608, result=0x10f40040, 
    unit=0x10e70450, select_lex=0x10e70a88) at /export/home/tmp/olav/opt-back-tmp/sql/sql_select.cc:3505
#36 0x0000000000625e27 in handle_select (thd=0x10e6e750, lex=0x10e703a0, result=0x10f40040, 
    setup_tables_done_option=0) at /export/home/tmp/olav/opt-back-tmp/sql/sql_select.cc:331
#37 0x00000000005c767a in execute_sqlcom_select (thd=0x10e6e750, all_tables=0x10eebd58)
    at /export/home/tmp/olav/opt-back-tmp/sql/sql_parse.cc:4494
#38 0x00000000005c86af in mysql_execute_command (thd=0x10e6e750)
    at /export/home/tmp/olav/opt-back-tmp/sql/sql_parse.cc:2092
#39 0x00000000005cf554 in mysql_parse (thd=0x10e6e750, 
    rawbuf=0x10eeaca0 "SELECT    table1 . `col_varchar_key` AS field1 FROM ( D AS table1 RIGHT  JOIN ( ( C AS table2 INNER JOIN CC AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key`  ) ) ) ON (table3 . `c"..., length=1059, parser_state=0x49c14990)
    at /export/home/tmp/olav/opt-back-tmp/sql/sql_parse.cc:5537
#40 0x00000000005d076c in dispatch_command (command=COM_QUERY, thd=0x10e6e750, 
    packet=0x10e75c41 "  SELECT    table1 . `col_varchar_key` AS field1 FROM ( D AS table1 RIGHT  JOIN ( ( C AS table2 INNER JOIN CC AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key`  ) ) ) ON (table3 . "..., packet_length=1061) at /export/home/tmp/olav/opt-back-tmp/sql/sql_parse.cc:1056
#41 0x00000000005d1bd6 in do_command (thd=0x10e6e750)
    at /export/home/tmp/olav/opt-back-tmp/sql/sql_parse.cc:796
#42 0x00000000006a417d in do_handle_one_connection (thd_arg=0x10e6e750)
    at /export/home/tmp/olav/opt-back-tmp/sql/sql_connect.cc:745
#43 0x00000000006a4279 in handle_one_connection (arg=0x10e6e750)
    at /export/home/tmp/olav/opt-back-tmp/sql/sql_connect.cc:684
#44 0x00000037242062f7 in start_thread () from /lib64/libpthread.so.0
#45 0x00000037236d1b6d in clone () from /lib64/libc.so.6

How to repeat:
1. Use the latest source from mysql-next-mr-opt-backporting

2. Run the optimizer_subquery test:

   perl runall.pl --basedir=... --grammar=conf/optimizer/optimizer_subquery.yy --seed=1 --threads=1 --queries=75000

Suggested fix:
This crash looks very similar to the crash in Bug#56529. The fix for this was to prevent that index conditions containing a subquery (or more exactly an item of type Item::SUBSELECT_ITEM) was pushed down to the handler/InnoDB. Since this looks like subqueries are still pushed down there seems to be more cases where we need to check that a subquery is not part of a pushed index condition.
[16 Nov 2010 22:25] Olav Sandstå
The query that causes this crash is:

SELECT    table1 . `col_varchar_key` AS field1 FROM ( D AS table1 RIGHT  JOIN ( ( C AS table2 INNER JOIN CC AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key`  ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_key`  ) ) WHERE (   EXISTS ( ( SELECT   SUBQUERY1_t2 . `pk` AS SUBQUERY1_field1 FROM ( C AS SUBQUERY1_t1 RIGHT OUTER JOIN D AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `pk` = SUBQUERY1_t1 . `col_int_key`  ) )    ) ) ) AND table1 . `pk` > 80 AND table1 . `pk` < ( 80 + 237 ) AND ( table1 . `col_varchar_key` IS  NULL AND  ( 1, 2 )  IN ( SELECT   SUBQUERY2_t1 . `pk` AS SUBQUERY2_field1 , COUNT( DISTINCT SUBQUERY2_t1 . `col_int_nokey` ) AS SUBQUERY2_field2 FROM ( CC AS SUBQUERY2_t1 INNER JOIN ( C AS SUBQUERY2_t2 INNER JOIN A AS SUBQUERY2_t3 ON (SUBQUERY2_t3 . `col_int_nokey` = SUBQUERY2_t2 . `pk`  ) ) ON (SUBQUERY2_t3 . `col_int_nokey` = SUBQUERY2_t2 . `col_int_key`  ) ) WHERE SUBQUERY2_t3 . `col_int_key` = SUBQUERY2_t2 . `col_int_key`   ) )  GROUP BY field1  ORDER BY table1 . `col_datetime_key`  , field1 LIMIT 1 OFFSET 7

and the pushed index condition that causes the assert in InnoDB:

((`test`.`table1`.`pk` > 80) and (`test`.`table1`.
`pk` < (80 + 237)) and isnull(`test`.`table1`.`col_varchar_key`) and <in_optimizer>((1,2),(1,2) in (select
 `test`.`SUBQUERY2_t1`.`pk` AS `SUBQUERY2_field1`,count(distinct `test`.`SUBQUERY2_t1`.`col_int_nokey`) AS
 `SUBQUERY2_field2` from (`test`.`CC` `SUBQUERY2_t1` join (`test`.`C` `SUBQUERY2_t2` join `test`.`A` `SUBQ
UERY2_t3` on((`test`.`SUBQUERY2_t3`.`col_int_nokey` = `test`.`SUBQUERY2_t2`.`pk`))) on((`test`.`SUBQUERY2_
t3`.`col_int_nokey` = `test`.`SUBQUERY2_t2`.`col_int_key`))) where (`test`.`SUBQUERY2_t3`.`col_int_key` = 
`test`.`SUBQUERY2_t2`.`col_int_key`))))
[17 Nov 2010 13:29] Olav Sandstå
Simplified version of the test case that creates the same crash:

CREATE TABLE t1 (
  pk INTEGER NOT NULL,
  c1 INTEGER NOT NULL,
  c2 INTEGER NOT NULL,

  PRIMARY KEY (pk)
) ENGINE=InnoDB;

INSERT INTO t1 VALUES (1,6,7);

CREATE TABLE t2 (
  c1 INTEGER NOT NULL
) ENGINE=InnoDB;

SELECT t1.c1
FROM t1
WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1)
                            FROM t2)
ORDER BY t1.c2;
[17 Nov 2010 13:31] Olav Sandstå
Explain output from running the simplified version of test case:

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       PRIMARY t1      range   PRIMARY PRIMARY 4       NULL    1       Using index condition; Using filesort
2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    1
[17 Nov 2010 13:48] Olav Sandstå
The index condition we push down to InnoDb on table t1 on the primary key looks like:

((`test`.`t1`.`pk` < 317) and <in_optimizer>(2,2 in (select count(`test`.`t2`.`c1`) from `test`.`t2`)))
[17 Nov 2010 21:29] Olav Sandstå
The subselect gets pushed down to InnoDB for the following reason:

1. In uses_index_fields_only() the following item get evaluated:

     <in_optimizer>(2,2 in (select count(`test`.`t2`.`c1`) from `test`.`t2`))

2. The top level item object representing this item has the following type:

    class Item_in_optimizer

3. The first check that is done in uses_index_fields_only() is the following code:

     if (item->const_item())
       return TRUE;

4. The implementation of const_item() is the following:

       virtual bool Item_func::const_item() const { return const_item_cache; }

    (see sql/item_func.h)

5. In this situation the const_item_cache member is true. So the const_item() method will return true and thus uses_index_fields_only() will return TRUE for the entire item subtree. Because of this is a const item it then will be included in the index condition that can be "safely" pushed down to the storage engine,
[18 Nov 2010 9:07] Olav Sandstå
Adding the following test for whether the item tree contains a subselect or not as the very first check in uses_index_fields_only() (in sql_select.cc):

  if (item->with_subselect)
    return false;

solves this problem and the part of the table´s where condition containing the subselect will no longer get pushed down.

With this change the reproduction test above passes and the optimizer_subquery test is able to proceed much longer.
[18 Nov 2010 10:29] Olav Sandstå
Even with the extra test show above the optimizer_subquery test eventually is able to find a query that hits the same assert in InnoDB. The new query looks like:

SELECT  STRAIGHT_JOIN  table1 . `col_datetime_key` AS field1 FROM ( C AS table1 INNER JOIN BB AS table2 ON (table2 . `pk` = table1 . `pk`  ) ) WHERE (  NOT EXISTS ( SELECT DISTINCT  SUBQUERY1_t1 . `col_varchar_key` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 INNER JOIN CC AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_varchar_nokey` = SUBQUERY1_t1 . `col_varchar_key`  ) ) WHERE SUBQUERY1_t1 . `col_varchar_nokey` != table1 . `col_varchar_key` AND  SUBQUERY1_t1 . `col_varchar_nokey` = ( SELECT   MIN(  CHILD_SUBQUERY1_t1 . `col_varchar_nokey` ) AS CHILD_SUBQUERY1_field1 FROM ( CC AS CHILD_SUBQUERY1_t1 INNER JOIN C AS CHILD_SUBQUERY1_t2 ON (CHILD_SUBQUERY1_t2 . `col_int_nokey` = CHILD_SUBQUERY1_t1 . `pk` ) )  ) ) ) AND table1 . `col_varchar_key` >= 's'  GROUP BY field1 HAVING (field1 > 'f' AND field1 != 9) ORDER BY table1 . `col_date_key` ASC , field1 LIMIT 100
[18 Nov 2010 11:32] Olav Sandstå
Simplified version of the second RQG generated SQL statement (see previous comment):

CREATE TABLE t1 (
  i1 INTEGER NOT NULL,
  c1 VARCHAR(1) NOT NULL
) ENGINE=InnoDB;

INSERT INTO t1 VALUES (2,'w');

CREATE TABLE t2 (
  i1 INTEGER NOT NULL,
  c1 VARCHAR(1) NOT NULL,
  c2 VARCHAR(1) NOT NULL,
  KEY (c1, i1)
) ENGINE=InnoDB;

INSERT INTO t2 VALUES (8,'d','d');
INSERT INTO t2 VALUES (4,'v','v');

CREATE TABLE t3 (
  c1 VARCHAR(1) NOT NULL
) ENGINE=InnoDB;

INSERT INTO t3 VALUES ('v');

SELECT i1
FROM t1
WHERE EXISTS (SELECT t2.c1
              FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 
              WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1)
                                                FROM t3));
[18 Nov 2010 11:54] Olav Sandstå
The explain for the simplified version of the second SQL query:

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    1       Using where
2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    1       
2       DEPENDENT SUBQUERY      t2      ref     c1      c1      3       test.t3.c1      1       Using index condition; Using where
3       SUBQUERY        t3      ALL     NULL    NULL    NULL    NULL    1       

and the index condition that is pushed down on t2's index is:

((select min(`test`.`t3`.`c1`) from `test`.`t3`) <> `test`.`t1`.`c1`)
[22 Nov 2010 22:39] Olav Sandstå
The second case hitting the assert is occurring after uses_index_fields_only() has evaluated if the following condition can be pushed down on the index for t2:

((select min(`test`.`t3`.`c1`) from `test`.`t3`) <> `test`.`t1`.`c1`)

The following code in uses_index_fields_only():

  if (!(item->used_tables() & tbl->map))
    return other_tbls_ok;

returns TRUE in this case (which leads to the subselect being pushed down to InnoDB).

This is caused by item->used_tables() gives a "wrong" information about which tables that are used by to evaluate this item. In this case item->used_tables() returns that no tables are used.

The item above has also not the "with_subselect" member set so the proposed code for detecting whether it includes a subselect or not (see above) does not detect this situation.
[23 Nov 2010 15:08] 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/124743

3287 Olav Sandstaa	2010-11-23
      Fix for Bug#58243 RQG test optimizer_subquery causes server 
      crash when running with ICP
      
      The crash was due to hitting an assert in InnoDB that checked that the
      same transaction could only have one active data access to InnoDB. In
      these cases there were two ongoing data accesses by the same
      transaction. This happened because the server was pushing down an
      index condition that contained a subquery. When InnoDB executed the
      index condition call back function this would result in a second call
      to InnoDB due to executing the subquery from within the index conditon
      function.
      
      To avoid this problem we should not push down index conditions that
      contains an subquery. The existing code for determining which part of
      a table's where condition that can be pushed down already had code for
      handling this but due to inconsistent data in the condition's item
      tree (or wrong use of methods on it) this code did not work as
      expected. Two cases where this happened (see the code in
      uses_index_fields_only()):
      
      1. Test for constant items:
      
          if (item->const_item())
             return TRUE;
      
         If the item tree contains a subquery the call to const_item() 
         could in some cases return true even when the subquery has to
         be executed later. In this case we would include the subquery
         in the pushed index condition.
      
         Fix for this problem: Check the item's with_subselect field.
         If this is true then do not include it, other let the item
         tree be included.
      
      2. Test for which tables the item tree contains:
      
          if (!(item->used_tables() & tbl->map))
             return other_tbls_ok;
      
         This code would accept to include an item tree if it did not
         use the table we are using for push down. If the item tree
         contained a subquery then the used_tables() method can return
         the wrong set of tables and this could result in that the item
         was wrongly included in the condition that was pushed down.
      
         Fix for this problem: Remove this test. This will result in that we
         will potentially recursively traverse more of the item tree.  The
         evaluation will be done by the main switch statement and the
         decision about whether to accept accesses to data from other tables
         will be done on the field item.
      
      Two test cases are included that would trigger the two cases.
     @ mysql-test/include/icp_tests.inc
        Test case for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
        
        Two test cases are added that covers each of the two situations
        where a subquery wrongly could be included in the pushed down
        index condition.
     @ mysql-test/r/innodb_icp.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/innodb_icp_all.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/innodb_icp_none.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/myisam_icp.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/myisam_icp_all.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/myisam_icp_none.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/subquery_all.result
        After the fix for Bug#58243: Change in explain due to the subquery 
        of the following statement no longer get pushed down to the storage 
        engine as part of index condition pushdown:
        
        SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
     @ mysql-test/r/subquery_all_jcl6.result
        After the fix for Bug#58243: Change in explain due to the subquery 
        of the following statement no longer get pushed down to the storage 
        engine as part of index condition pushdown:
        
        SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
     @ mysql-test/r/subquery_nomat_nosj.result
        After the fix for Bug#58243: Change in explain due to the subquery 
        of the following statement no longer get pushed down to the storage 
        engine as part of index condition pushdown:
        
        SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
     @ mysql-test/r/subquery_nomat_nosj_jcl6.result
        After the fix for Bug#58243: Change in explain due to the subquery 
        of the following statement no longer get pushed down to the storage 
        engine as part of index condition pushdown:
        
        SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
     @ sql/sql_select.cc
        Changes how uses_index_fields_only() handles conditions containing 
        a subquery:
        
        1. Before accepting to include a query that is const: also check
           that it does not contain a sub query by checking the item's
           with_subselect flag.
        
        2. Do not use the item->used_tables() to determine if this 
           query will only access "other tables" than the current table.
           This method could return wrong (or missing data) about the
           actual tables needed to execute the item tree. Instead we let
           the main switch handle this.
[24 Nov 2010 22:21] 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/124947

3288 Olav Sandstaa	2010-11-24
      Fix for Bug#58243 RQG test optimizer_subquery causes server 
      crash when running with ICP
      
      The crash was due to hitting an assert in InnoDB that checked that the
      same transaction could only have one active data access to InnoDB. In
      these cases there were two ongoing data accesses by the same
      transaction. This happened because the server was pushing down an
      index condition that contained a subquery. When InnoDB executed the
      index condition call back function this would result in a second call
      to InnoDB due to executing the subquery from within the index conditon
      function.
      
      To avoid this problem we should not push down index conditions that
      contains an subquery. The existing code for determining which part of
      a table's where condition that can be pushed down already had code for
      handling this but due to inconsistent data in the condition's item
      tree (or wrong use of methods on it) this code did not work as
      expected. Two cases where this happened (see the code in
      uses_index_fields_only()):
      
      1. Test for constant items:
      
          if (item->const_item())
             return TRUE;
      
         If the item tree contains a subquery the call to const_item() 
         could in some cases return true even when the subquery has to
         be executed later. In this case we would include the subquery
         in the pushed index condition.
      
         Fix for this problem: Check the item's with_subselect field.
         If this is true then do not include it, other let the item
         tree be included.
      
      2. Test for which tables the item tree contains:
      
          if (!(item->used_tables() & tbl->map))
             return other_tbls_ok;
      
         This code would accept to include an item tree if it did not
         use the table we are using for push down. If the item tree
         contained a subquery then the used_tables() method can return
         the wrong set of tables and this could result in that the item
         was wrongly included in the condition that was pushed down.
      
         Fix for this problem: Remove this test. This will result in that we
         will potentially recursively traverse more of the item tree.  The
         evaluation will be done by the main switch statement and the
         decision about whether to accept accesses to data from other tables
         will be done on the field item.
      
      Two test cases are included that would trigger the two cases.
     @ mysql-test/include/icp_tests.inc
        Test case for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
        
        Two test cases are added that covers each of the two situations
        where a subquery wrongly could be included in the pushed down
        index condition.
     @ mysql-test/r/innodb_icp.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/innodb_icp_all.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/innodb_icp_none.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/myisam_icp.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/myisam_icp_all.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/myisam_icp_none.result
        Result file for Bug#58243 RQG test optimizer_subquery causes server 
        crash when running with ICP.
     @ mysql-test/r/subquery_all.result
        After the fix for Bug#58243: Change in explain due to the subquery 
        of the following statement no longer get pushed down to the storage 
        engine as part of index condition pushdown:
        
        SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
     @ mysql-test/r/subquery_all_jcl6.result
        After the fix for Bug#58243: Change in explain due to the subquery 
        of the following statement no longer get pushed down to the storage 
        engine as part of index condition pushdown:
        
        SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
     @ mysql-test/r/subquery_nomat_nosj.result
        After the fix for Bug#58243: Change in explain due to the subquery 
        of the following statement no longer get pushed down to the storage 
        engine as part of index condition pushdown:
        
        SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
     @ mysql-test/r/subquery_nomat_nosj_jcl6.result
        After the fix for Bug#58243: Change in explain due to the subquery 
        of the following statement no longer get pushed down to the storage 
        engine as part of index condition pushdown:
        
        SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
     @ sql/sql_select.cc
        Changes how uses_index_fields_only() handles conditions containing 
        a subquery:
        
        1. Before accepting to include a query that is const: also check
           that it does not contain a sub query by checking the item's
           with_subselect flag.
        
        2. Do not use the item->used_tables() to determine if this 
           query will only access "other tables" than the current table.
           This method could return wrong (or missing data) about the
           actual tables needed to execute the item tree. Instead we let
           the main switch handle this.
[24 Nov 2010 22:30] Olav Sandstå
Patch pushed to mysql-next-mr-opt-backporting with revision id:
olav.sandstaa@oracle.com-20101124222027-11hi9hwgbg6ut2kb .
[5 Dec 2010 12:39] Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)
[11 Dec 2010 17:45] Paul DuBois
Bug does not appear in any released 5.6.x version. No changelog entry needed.