Bug #36133 Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &&
Submitted: 16 Apr 2008 11:31 Modified: 22 Nov 2010 1:13
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0.4-nt, 6.0.5-bk OS:Any
Assigned to: Sergey Petrunya CPU Architecture:Any

[16 Apr 2008 11:31] Shane Bester
Description:
failing assertion with subquery, repeated only with 6.0 tree:

Version: '6.0.5-alpha-debug'  socket: '/tmp/mysql.sock'  port: 3306  yes
mysqld: item_subselect.cc:300: virtual bool Item_in_subselect::exec(): Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION && engine->engine_type() == subselect_engine::HASH_SJ_ENGINE)' failed.
mysqld(print_stacktrace
mysqld(handle_segfault
[0xcce420]
/lib/i686/nosegneg/libc.so.6(abort
/lib/i686/nosegneg/libc.so.6(__assert_fail
mysqld(Item_in_subselect::exec
mysqld(Item_in_subselect::val_bool
mysqld(Item::val_bool_result
mysqld(Item_in_optimizer::val_int
mysqld[0x830d28e]
mysqld[0x830d18d]
mysqld[0x830d215]
mysqld(SQL_SELECT::test_quick_select
mysqld[0x828de47]
mysqld(JOIN::optimize
mysqld(mysql_select
mysqld(handle_select
mysqld[0x82262d7]
mysqld(mysql_execute_command
mysqld(mysql_parse
mysqld(dispatch_command
mysqld(do_command
mysqld(handle_one_connection
/lib/i686/nosegneg/libpthread.so.0[0xde0402]
/lib/i686/nosegneg/libc.so.6(clone+0x5e)[0x20c2ae]
080414 20:36:35 - mysqld got signal 6 ;

How to repeat:
drop table if exists `t2`;
create table `t2` ( `a` int,`b` int,key (`a`),key (`b`)) engine=myisam;
insert into `t2` values (3,3),(3,3),(3,3);

select 1 from t2 where  
t2.a > (select a from t2 limit 1) or  
t2.a= (select a from t2 limit 1) and 
t2.a <= (select a from t2 limit 1) and not  
t2.a not in (select t2.b from t2);
[16 Apr 2008 12:14] MySQL Verification Team
Verified on Suse 10.3 X64. I will test 5.0/5.1.

080416  9:04:59 [Note] Event Scheduler: Loaded 0 events
080416  9:04:59 [Note] /home/miguel/dbs/6.0/libexec/mysqld: ready for connections.
Version: '6.0.5-alpha-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 0x49093950 (LWP 18280)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x49093950 (LWP 18280)]
0x000000000079c4d7 in free_tmp_table (thd=0x1f143e8, entry=0x1f9d7c8) at sql_select.cc:12801
12801         entry->file->ha_drop_table(entry->s->table_name.str);
(gdb) bt full
#0  0x000000000079c4d7 in free_tmp_table (thd=0x1f143e8, entry=0x1f9d7c8) at sql_select.cc:12801
        own_root = {free = 0x8f8f8f8f8f8f8f8f, used = 0x8f8f8f8f8f8f8f8f, pre_alloc = 0x8f8f8f8f8f8f8f8f, 
  min_malloc = 10344644715844964239, block_size = 10344644715844964239, block_num = 2408550287, first_block_usage = 2408550287, 
  error_handler = 0x8f8f8f8f8f8f8f8f}
        save_proc_info = 0xe242b6 "Sending data"
        _db_func_ = 0xe2384e "JOIN::cleanup"
        _db_file_ = 0xe22bb7 "sql_select.cc"
        _db_level_ = 19
        _db_framep_ = (char **) 0x4908c958
        __FUNCTION__ = "free_tmp_table"
<cut>
[16 Apr 2008 12:20] MySQL Verification Team
Thank you for the bug report. Only 6.0BK is affected.
[24 Apr 2008 8:40] Sergey Petrunya
A somewhat reduced test query:

select 1 from t2 where  
   t2.a > 1 
 or 
   t2.a= (select a from t2 limit 1) and 
   not t2.a not in (select t2.b from t2);

attempt to remove any remaining part of the WHERE will cause the crash to go away.
[24 Apr 2008 8:58] Sergey Petrunya
Even more reduced query that still causes the crash:

select 1 from t2 where  
    t2.a > 1 
  or 
    t2.a = 3 and 
    not t2.a not in (select t2.b from t2);

The stacktrace is:
  mysqld: item_subselect.cc:300: virtual bool Item_in_subselect::exec(): Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION && engine->engine_type() == subselect_engine::HASH_SJ_ENGINE)' failed.
  
  Program received signal SIGABRT, Aborted.
  [Switching to Thread 0xad167b90 (LWP 11695)]
  0xffffe410 in __kernel_vsyscall ()
(gdb) where
  #0  0xffffe410 in __kernel_vsyscall ()
  #1  0xb7d02101 in raise () from /lib/libc.so.6
  #2  0xb7d038e8 in abort () from /lib/libc.so.6
  #3  0xb7cfb7a5 in __assert_fail () from /lib/libc.so.6
  #4  0x082d1981 in Item_in_subselect::exec (this=0x93a0c78) at item_subselect.cc:298
  #5  0x082ca7b0 in Item_in_subselect::val_bool (this=0x93a0c78) at item_subselect.cc:899
  #6  0x082633b8 in Item::val_bool_result (this=0x93a0c78) at ../item.h:742
  #7  0x08292933 in Item_in_optimizer::val_int (this=0x93a1088) at item_cmpfunc.cc:1596
  #8  0x084311a1 in get_mm_tree (param=0xad162ef0, cond=0x93a1088) at opt_range.cc:5342
  #9  0x08430fa2 in get_mm_tree (param=0xad162ef0, cond=0x93a0de0) at opt_range.cc:5302
  #10 0x08431091 in get_mm_tree (param=0xad162ef0, cond=0x93a0e98) at opt_range.cc:5319
  #11 0x08437e8e in SQL_SELECT::test_quick_select (this=0x93a1878, thd=0x9360290, keys_to_use={map = 1}, prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false) at opt_range.cc:2305
  #12 0x083a6e71 in get_quick_record_count (thd=0x9360290, select=0x93a1878, table=0x9373290, keys=0x93a1538, limit=18446744073709551615) at sql_select.cc:3706
  #13 0x083ab9a4 in make_join_statistics (join=0x93a7228, tables=0x0, conds=0x93a0e98, keyuse_array=0x93a8520) at sql_select.cc:4125
  #14 0x083ae65a in JOIN::optimize (this=0x93a7228) at sql_select.cc:1522
  #15 0x083b234e in mysql_select (thd=0x9360290, rref_pointer_array=0x9361830, tables=0x939fed8, wild_num=0, fields=@0x93617c0, conds=0x93a0e98, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x93a0fa0, unit=0x93614a4, select_lex=0x936172c) at sql_select.cc:2946
  #16 0x083b74ac in handle_select (thd=0x9360290, lex=0x9361448, result=0x93a0fa0, setup_tables_done_option=0) at sql_select.cc:289
  #17 0x0832a67b in execute_sqlcom_select (thd=0x9360290, all_tables=0x939fed8) at sql_parse.cc:4806
  #18 0x0832bb3a in mysql_execute_command (thd=0x9360290) at sql_parse.cc:1994
  #19 0x083349b0 in mysql_parse (thd=0x9360290, inBuf=0x939fd30 "select 1 from t2 where  \n   t2.a > 1 \n or \n   t2.a = 3 and \n   not t2.a not in (select t2b.b from t2b)", length=102, found_semicolon=0xad167260) at sql_parse.cc:5767
[24 Apr 2008 9:25] Sergey Petrunya
Analysis:

The problem affects only subqueries that are executed using Materialization
strategy.

The problem is caused by this scenario:

> parent_join->prepare()
| > Item_subselect::fix_fields()
| |  > child_join->prepare()
| |  |  subquery is slated for materialization, IN->EXISTS rewrite is not
| |  |  performed
| |  < child_join->prepare()
| < Item_subselect::fix_fields()
< parent_join->prepare()

  // irrelevant, but note location of this call
  parent_join->flatten_subqueries()     // ------ (1)

> parent_join->optimize()
| > invoke the range optimizer
| | > get_mm_parts()
| | | if (cond->const_item())
| | | {
| | |    // evaluate the subquery predicate ------ (2)
| | |    This fails the assert because setup_subquery_materialization()
| | |    hasn't been called yet.
| | | }
| | < get_mm_parts()
| < invoke the range optimizer
|
| > parent_join->setup_subquery_materialization()
| |  
| |  it's too late now, we needed the setup to be done before location (2).
| |  btw, semi-join subqueries don't have this problem because they are
| |  handled at earlier stage at location (1)
| < parent_join->setup_subquery_materialization()
|
> parent_join->optimize()

I see two solutions:

1. Make IN-subquery predicates that use materialization survive such handling.
   Let materialized IN-subquery predicate call setup_engine() for itself it
   hasn't yet been done for him by join::setup_subquery_materialization()

2. Make the range optimizer to never evaluate subqueries. Use the
     Item::is_expensive()
   function to detect predicates that must not be evaluated by the optimizer.
   This should cover subqueries.

I'm leaning towards Solution #2 as it solves the "long running EXPLAIN" problem.  we might miss some good QEPs if there are cheap single-lookup subqueries, though.
[24 Apr 2008 22: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/45976

ChangeSet@1.2626, 2008-04-25 02:06:49+04:00, sergefp@mysql.com +5 -0
  BUG#36133 "Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &"
  - The problem was that the range optimizer evaluated constant expressions, 
    and among them it would try to evaluate IN-subquery predicates slated for
    handling with materialization strategy. However, these predicates require
    that parent_join->setup_subquery_materialization() is invoked before one
    attempts to evaluate them.
  
  - Fixed by making the range optimizer not to evaluate expressions that have
    item->is_expensive() == TRUE (these are materialization subqueries and 
    stored function calls). This should also resolve the problem that EXPLAIN 
    may be too long. 
    This change cuts off some opportunities for range optimizer, but this is 
    the price we're willing to pay for separation of query optimization and
    execution.
[25 Apr 2008 0:00] 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/45978

ChangeSet@1.2626, 2008-04-25 03:59:38+04:00, sergefp@mysql.com +5 -0
  BUG#36133 "Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &"
  - The problem was that the range optimizer evaluated constant expressions, 
    and among them it would try to evaluate IN-subquery predicates slated for
    handling with materialization strategy. However, these predicates require
    that parent_join->setup_subquery_materialization() is invoked before one
    attempts to evaluate them.
  
  - Fixed by making the range optimizer not to evaluate expressions that have
    item->is_expensive() == TRUE (these are materialization subqueries and 
    stored function calls). This should also resolve the problem that EXPLAIN 
    may be too long. 
    This change cuts off some opportunities for range optimizer, but this is 
    the price we're willing to pay for separation of query optimization and
    execution.
[28 May 2008 10:01] Bugs System
Pushed into 6.0.6-alpha
[30 May 2008 18:15] Paul DuBois
Noted in 6.0.6 changelog.

Range optimizer evaluation of IN subqueries to be handled with the 
materialization strategy could lead to assertion failure.
[16 Aug 2010 6:37] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:08] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[22 Nov 2010 1:13] Paul DuBois
Noted in 5.6.1 changelog.
[23 Nov 2010 2:28] Paul DuBois
Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.