Bug #56367 Assertion exec_method != EXEC_MATERIALIZATION... on subquery in FROM
Submitted: 30 Aug 2010 13:26 Modified: 22 Nov 2010 1:22
Reporter: John Embretsen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:bzr_mysql-next-mr-opt-backporting OS:Any
Assigned to: Øystein Grøvlen CPU Architecture:Any
Tags: materialization, optimizer_switch, subquery

[30 Aug 2010 13:26] John Embretsen
Description:
Assertion failed: exec_method != EXEC_MATERIALIZATION || (exec_method == EXEC_MATERIALIZATION && engine->engine_type() == subselect_engine::HA
SH_SJ_ENGINE), file mysql-next-mr-opt-backporting/sql/item_subselect.cc, line 331

when executing the query

SELECT col_datetime_key
FROM (
  SELECT A.*
  FROM A LEFT JOIN B ON A.pk > 3 OR B.pk  =  ANY (
    SELECT pk
    FROM A  
  )  
) table1;

against a debug build of the mysql-next-mr-opt-backporting branch as of 2010-08-30.

Stacktrace:

  [12] _assert(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff1fdc95
  [13] Item_in_subselect::exec(this = 0x48ef250), line 331 in "item_subselect.cc"
  [14] Item_in_subselect::val_bool(this = 0x48ef250), line 960 in "item_subselect.cc"
  [15] Item::val_bool_result(this = 0x48ef250), line 860 in "item.h"
  [16] Item_in_optimizer::val_int(this = 0x48f0ca8), line 1845 in "item_cmpfunc.cc"
  [17] Item::val_bool(this = 0x48f0ca8), line 200 in "item.cc"
  [18] Item_cond_or::val_int(this = 0x48ef3d8), line 4547 in "item_cmpfunc.cc"
  [19] join_read_const_table(tab = 0x48f1338, pos = 0x48f9ef0), line 17478 in "sql_select.cc"
  [20] make_join_statistics(join = 0x48f6f10, tables_arg = 0x48c00e8, conds = (nil), keyuse_array = 0x48fce68), line 4608 in "sql_select.cc"
  [21] JOIN::optimize(this = 0x48f6f10), line 1828 in "sql_select.cc"
  [22] mysql_select(thd = 0x48738e0, rref_pointer_array = 0x48bf8b8, tables = 0x48c00e8, wild_num = 0, fields = CLASS, conds = (nil), og_num =
 0, order = (nil), group = (nil), having = (nil), proc_param = (nil), select_options = 2416184064ULL, result = 0x48f07e0, unit = 0x48bf998, se
lect_lex = 0x48bf6d0), line 3345 in "sql_select.cc"
  [23] mysql_derived_filling(thd = 0x48738e0, lex = 0x48750e0, orig_table_list = 0x48efbb0), line 288 in "sql_derived.cc"
  [24] mysql_handle_derived(lex = 0x48750e0, processor = 0xb98cd0 = &mysql_derived_filling(THD*,LEX*,TABLE_LIST*)), line 60 in "sql_derived.cc
"
  [25] open_and_lock_tables(thd = 0x48738e0, tables = 0x48efbb0, derived = true, flags = 0, prelocking_strategy = 0xfffffd7fff06a5d0), line 53
77 in "sql_base.cc"
  [26] open_and_lock_tables(thd = 0x48738e0, tables = 0x48efbb0, derived = true, flags = 0), line 454 in "sql_base.h"
  [27] execute_sqlcom_select(thd = 0x48738e0, all_tables = 0x48efbb0), line 4523 in "sql_parse.cc"
  [28] mysql_execute_command(thd = 0x48738e0), line 2166 in "sql_parse.cc"
  [29] mysql_parse(thd = 0x48738e0, rawbuf = 0x48bee10 "SELECT col_datetime_key\nFROM (\nSELECT A.*\nFROM A LEFT JOIN B ON A.pk > 3 OR B.pk  =  ANY (\nSELECT pk\nFROM A  \n)  \n) table1", length = 122U, parser_state = 0xfffffd7fff06da50), line 5591 in "sql_parse.cc"
  [30] dispatch_command(command = COM_QUERY, thd = 0x48738e0, packet = 0x48b6dc1 "SELECT col_datetime_key\nFROM (\nSELECT A.*\nFROM A LEFT JOIN B ON A.pk > 3 OR B.pk  =  ANY (\nSELECT pk\nFROM A  \n)  \n) table1", packet_length = 122U), line 1130 in "sql_parse.cc"
  [31] do_command(thd = 0x48738e0), line 802 in "sql_parse.cc"
  [32] do_handle_one_connection(thd_arg = 0x48738e0), line 1191 in "sql_connect.cc"
  [33] handle_one_connection(arg = 0x48738e0), line 1130 in "sql_connect.cc"
  [34] pfs_spawn_thread(arg = 0x48a8dd0), line 1061 in "pfs.cc"
  [35] _thrp_setup(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff27acf5
  [36] _lwp_start(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff27afb0

The issue goes away when disabling the materialization optimization, which is enabled by default in mysql-next-mr-opt-backporting:

--optimizer_switch='materialization=OFF'

Hence, the issue is not present in upstream branches (trunk, next-mr, next-mr-opt-team), where this feature is disabled or non-existent.

This seems to be a highly problematic area of the optimizer code, as several similar bugs have been filed and fixed before (see e.g. Bug#36133, Bug#37896, Bug#46680).

How to repeat:
DROP TABLE IF EXISTS A;
DROP TABLE IF EXISTS B;

CREATE TABLE A (
  pk INT NOT NULL,
  col_datetime_key DATETIME DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_datetime_key (col_datetime_key)
) ENGINE=MyISAM;

CREATE TABLE `B` (
  pk INT NOT NULL AUTO_INCREMENT,
  col_datetime_key DATETIME DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_datetime_key (col_datetime_key)
) ENGINE=MyISAM;

INSERT INTO B VALUES (1,'2005-02-05 00:00:00');

SELECT col_datetime_key
FROM (
  SELECT A.*
  FROM A LEFT JOIN B ON A.pk > 3 OR B.pk  =  ANY (
    SELECT pk
    FROM A
  )
) table1;

Note that using the MyISAM engine is required for this particular repro (no assert with InnoDB engine).
[30 Aug 2010 13:29] John Embretsen
Verified using mysql-next-mr-opt-backporting revid epotemkin@mysql.com-20100824080223-j2pp25kguvkpyir7.

With an optimized build the same query results in a crash in Item_in_subselect::init_left_expr_cache:

---- called from signal handler with signal 11 (SIGSEGV) ------
  [8] Item_in_subselect::init_left_expr_cache(this = ???) (optimized), at 0xcd25cd (line ~1963) in "item_subselect.cc"
  [9] Item_in_subselect::exec(this = ???) (optimized), at 0xccf286 (line ~345) in "item_subselect.cc"
  [10] Item_in_subselect::val_bool(this = ???) (optimized), at 0xcd0553 (line ~960) in "item_subselect.cc"
  [11] Item::val_bool_result(this = ???) (optimized), at 0xb2a140 (line ~860) in "item.h"
  [12] Item_in_optimizer::val_int(this = ???) (optimized), at 0xc8dae0 (line ~1845) in "item_cmpfunc.cc"
  [13] Item::val_bool(this = ???) (optimized), at 0xc75d0f (line ~200) in "item.cc"
  [14] Item_cond_or::val_int(this = ???) (optimized), at 0xc93ba6 (line ~4547) in "item_cmpfunc.cc"
  [15] join_read_const_table(tab = ???, pos = ???) (optimized), at 0xbaa078 (line ~17478) in "sql_select.cc"
  [16] make_join_statistics(join = ???, tables_arg = ???, conds = ???, keyuse_array = ???) (optimized), at 0xb959b9 (line ~4608) in "sql_select.cc"
  [17] JOIN::optimize(this = ???) (optimized), at 0xb90909 (line ~1828) in "sql_select.cc"
  [18] mysql_select(thd = ???, rref_pointer_array = ???, tables = ???, wild_num = ???, fields = CLASS, conds = ???, og_num = ???, order = ???, group = ???, having = ???, proc_param = ???, select_options = ???, result = ???, unit = ???, select_lex = ???) (optimized), at 0xb93afd (line ~3345) in "sql_select.cc"
  [19] mysql_derived_filling(thd = ???, lex = ???, orig_table_list = ???) (optimized), at 0xb630fa (line ~288) in "sql_derived.cc"
  [20] mysql_handle_derived(lex = ???, processor = ???) (optimized), at 0xb62c92 (line ~60) in "sql_derived.cc"
  [21] open_and_lock_tables(thd = ???, tables = ???, derived = ???, flags = ???, prelocking_strategy = ???) (optimized), at 0xb49a36 (line ~5377) in "sql_base.cc"
  [22] open_and_lock_tables(thd = ???, tables = ???, derived = ???, flags = ???) (optimized), at 0xb31e3f (line ~454) in "sql_base.h"
  [23] execute_sqlcom_select(thd = ???, all_tables = ???) (optimized), at 0xb7b764 (line ~4523) in "sql_parse.cc"
  [24] mysql_execute_command(thd = ???) (optimized), at 0xb7a8e1 (line ~2166) in "sql_parse.cc"
  [25] mysql_parse(thd = ???, rawbuf = ???, length = ???, parser_state = ???) (optimized), at 0xb7cda7 (line ~5591) in "sql_parse.cc"
  [26] dispatch_command(command = ???, thd = ???, packet = ???, packet_length = ???) (optimized), at 0xb75d3c (line ~1130) in "sql_parse.cc"
  [27] do_command(thd = ???) (optimized), at 0xb7513c (line ~802) in "sql_parse.cc"
  [28] do_handle_one_connection(thd_arg = ???) (optimized), at 0xc0cbf4 (line ~1191) in "sql_connect.cc"
  [29] handle_one_connection(arg = ???) (optimized), at 0xc0cad2 (line ~1130) in "sql_connect.cc"
[8 Sep 2010 13:36] Øystein Grøvlen
This is a duplicate of Bug#52344 that was accidentally not pushed when it was supposed to.  I will create a test case for the reported query too, since it is pretty different from the test case for Bug#52344.
[8 Sep 2010 14:44] 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/117773

3239 oystein.grovlen@sun.com	2010-09-08
      Bug#56367 - Assertion exec_method != EXEC_MATERIALIZATION... 
                  on subquery in FROM
      
      The reported bug was fixed by 52344.  This patch only contains a reduced 
      test case for the scenario reported in Bug#56367.
     @ mysql-test/include/subquery_mat.inc
        Added test case for Bug#56367.
     @ mysql-test/r/subquery_mat.result
        Added test case for Bug#56367.
     @ mysql-test/r/subquery_mat_all.result
        Added test case for Bug#56367.
     @ mysql-test/r/subquery_mat_none.result
        Added test case for Bug#56367.
[9 Sep 2010 11:32] Øystein Grøvlen
Pushed into mysql-next-mr-opt-backporting with revision id
oystein.grovlen@sun.com-20100908143938-xx2f95rgjgk8rpxx
[2 Oct 2010 18:15] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101002181053-6iotvl26uurcoryp) (version source revid:alexander.nozdrin@oracle.com-20101002180917-h0n62akupm3z20nt) (pib:21)
[13 Nov 2010 16:26] 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:22] Paul DuBois
Bug is not in any released 5.6.x version. No changelog entry needed.