Bug #58555 Crash in heap_scan_init at line 30 in hp_scan.c on nested subqueries in FROM

• Submitted: 29 Nov 2010 10:38
• Modified: 5 Jul 2011 18:26
• Reporter: John Embretsen
• Status: Can't repeat
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: bzr_WL5274
• OS: Any
• Assigned to: Evgeny Potemkin
• CPU Architecture: Any
• Tags: wl5274

[29 Nov 2010 10:38] John Embretsen

Description:
Running the Random Query Generator with a grammar which works with views created with ALGORITHM=TEMPTABLE resulted in a crash when run against code implementing WL#5274 (Postpone materialization of views/subqueries in FROM clause.):

---- called from signal handler with signal 11 (SIGSEGV) ------
  [8] heap_scan_init(info = (nil)), line 30 in "hp_scan.c"
  [9] ha_heap::rnd_init(this = 0x65cd438, scan = true), line 372 in "ha_heap.cc"
  [10] handler::ha_rnd_init(this = 0x65cd438, scan = true), line 1284 in "handler.h"
  [11] subselect_uniquesubquery_engine::scan_table(this = 0x662b5f0), line 2049 in "item_subselect.cc"
  [12] subselect_indexsubquery_engine::exec(this = 0x662b5f0), line 2346 in "item_subselect.cc"
  [13] Item_subselect::exec(this = 0x65c7aa0), line 288 in "item_subselect.cc"
  [14] Item_subselect::exec(this = 0x65c7aa0), line 293 in "item_subselect.cc"
  [15] Item_in_subselect::val_bool(this = 0x65c7aa0), line 888 in "item_subselect.cc"
  [16] Item::val_bool_result(this = 0x65c7aa0), line 849 in "item.h"
  [17] Item_in_optimizer::val_int(this = 0x65de050), line 1833 in "item_cmpfunc.cc"
  [18] Item::val_bool(this = 0x65de050), line 199 in "item.cc"
  [19] Item_func_not::val_int(this = 0x65c7b98), line 289 in "item_cmpfunc.cc"
  [20] Item::val_bool(this = 0x65c7b98), line 199 in "item.cc"
  [21] Item_cond_and::val_int(this = 0x65eb3f8), line 4418 in "item_cmpfunc.cc"
  [22] evaluate_join_record(join = 0x65e0980, join_tab = 0x65ea1d8, error = 0), line 11820 in "sql_select.cc"
  [23] sub_select(join = 0x65e0980, join_tab = 0x65ea1d8, end_of_records = false), line 11771 in "sql_select.cc"
  [24] do_select(join = 0x65e0980, fields = (nil), table = 0x660dbc0, procedure = (nil)), line 11523 in "sql_select.cc"
  [25] JOIN::exec(this = 0x65e0980), line 1965 in "sql_select.cc"
  [26] mysql_select(thd = 0x650ed60, rref_pointer_array = 0x6510e70, tables = 0x65c3ac0, wild_num = 0, fields = CLASS, conds = 0x65c8e00, og_num = 2U, order = 0x65c9328, group = (nil), having = 0x65c9088, proc_param = (nil), select_options = 2147748617ULL, result = 0x65ddbf0, unit = 0x6510670, select_lex = 0x6510ca0), line 2572 in "sql_select.cc"
  [27] handle_select(thd = 0x650ed60, lex = 0x65105c8, result = 0x65ddbf0, setup_tables_done_option = 0), line 280 in "sql_select.cc"
  [28] execute_sqlcom_select(thd = 0x650ed60, all_tables = 0x65c3ac0), line 4872 in "sql_parse.cc"
  [29] mysql_execute_command(thd = 0x650ed60), line 2318 in "sql_parse.cc"
  [30] mysql_parse(thd = 0x650ed60, inBuf = 0x6551a10 "SELECT DISTINCT  SQL_SMALL_RESULT table1 . `pk` AS field1 \nFROM \n( \n(  SELECT   SUBQUERY1_t1 . * FROM \n( vBB_0 AS SUBQUERY1_t1 \nRIGHT OUTER JOIN C AS SUBQUERY1_t2 \nON (SUBQUERY1_t2 . `pk` = SUBQUERY1_t1 . `col_int_key`  \nOR SUBQUERY1_t2 . `pk` <= ALL ( \nSELECT   CHILD_SUBQUERY1_t1 . `col_int_key` AS CHILD_SUBQUERY1_field1 \nFROM CC AS CHILD_SUBQUERY1_t1 \nWHERE ( \nCHILD_SUBQUERY1_t1 . `pk` >= 4 \nOR CHILD_SUBQUERY1_t1 . `pk` != CHILD_SUBQUERY1_t1 . `col_int_key` \n)\n) \n) \n)\n) AS table1 \nSTRAIGHT_JOIN ( \n( D AS " ..., length = 1061U, parser_state = 0xfffffd7fff07da58), line 5902 in "sql_parse.cc"
  [31] dispatch_command(command = COM_QUERY, thd = 0x650ed60, packet = 0x658d8b1 "SELECT DISTINCT  SQL_SMALL_RESULT table1 . `pk` AS field1 \nFROM \n( \n(  SELECT   SUBQUERY1_t1 . * FROM \n( vBB_0 AS SUBQUERY1_t1 \nRIGHT OUTER JOIN C AS SUBQUERY1_t2 \nON (SUBQUERY1_t2 . `pk` = SUBQUERY1_t1 . `col_int_key`  \nOR SUBQUERY1_t2 . `pk` <= ALL ( \nSELECT   CHILD_SUBQUERY1_t1 . `col_int_key` AS CHILD_SUBQUERY1_field1 \nFROM CC AS CHILD_SUBQUERY1_t1 \nWHERE ( \nCHILD_SUBQUERY1_t1 . `pk` >= 4 \nOR CHILD_SUBQUERY1_t1 . `pk` != CHILD_SUBQUERY1_t1 . `col_int_key` \n)\n) \n) \n)\n) AS table1 \nSTRAIGHT_JOIN ( \n( D AS " ..., packet_length = 1061U), line 1135 in "sql_parse.cc"
  [32] do_command(thd = 0x650ed60), line 807 in "sql_parse.cc"
  [33] do_handle_one_connection(thd_arg = 0x650ed60), line 1191 in "sql_connect.cc"
  [34] handle_one_connection(arg = 0x650ed60), line 1130 in "sql_connect.cc"
  [35] pfs_spawn_thread(arg = 0x65a03d0), line 1015 in "pfs.cc"
  [36] _thrp_setup(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff27acf5
  [37] _lwp_start(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff27afb0

This issue is so far only observed with the implementation for WL#5274:

branch-nick: mysql-next-mr-wl5274
revision-id: [e-mail]-20101028105959-s1n1cxq5iskoag3h
revno: 2986

How to repeat:
A reproducible test case will be provided later.

[29 Nov 2010 15:28] John Embretsen

How to repeat (InnoDB is required):

CREATE TABLE t1 (
  pk INT NOT NULL,
  col_int_key INT,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key)
) ENGINE=InnoDB;

CREATE TABLE t2 (
  pk INT NOT NULL,
  col_int_key INT,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key)
) ENGINE=InnoDB;

CREATE TABLE t3 (
  pk INT NOT NULL,
  col_int_key INT,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key)
) ENGINE=InnoDB;

INSERT INTO t3 VALUES (13,NULL);
INSERT INTO t3 VALUES (14,4);

CREATE ALGORITHM=TEMPTABLE VIEW viewt1 AS SELECT * FROM t1;
CREATE ALGORITHM=TEMPTABLE VIEW viewt2 AS SELECT * FROM t2;

SELECT table1.pk AS field1
FROM
  ( SELECT viewt2.*
     FROM
       ( viewt2
         RIGHT OUTER JOIN t3
         ON t3.pk = viewt2.col_int_key
       )
  ) AS table1
WHERE ( table1.col_int_key NOT IN (
  SELECT viewt1.pk
  FROM viewt1
  WHERE viewt1.pk >= viewt1.pk ) 
) 
ORDER BY field1
LIMIT 10;

[29 Nov 2010 15:30] John Embretsen

EXPLAIN output for the crashing query:

EXPLAIN SELECT table1.pk AS field1
FROM
  ( SELECT viewt2.*
     FROM
       ( viewt2
         RIGHT OUTER JOIN t3
         ON t3.pk = viewt2.col_int_key
       )
  ) AS table1
WHERE ( table1.col_int_key NOT IN (
  SELECT viewt1.pk
  FROM viewt1
  WHERE viewt1.pk >= viewt1.pk ) 
) 
ORDER BY field1
LIMIT 10;

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    4       Using where; Using filesort
3       DEPENDENT SUBQUERY      <derived5>      index_subquery  auto_key0       auto_key0       4       func    2       Using where; Full scan on NULL key
5       DERIVED t1      index   NULL    col_int_key     5       NULL    1       Using index
2       DERIVED t3      index   NULL    col_int_key     5       NULL    2       Using index
2       DERIVED <derived4>      ref     auto_key0       auto_key0       5       test.t3.pk      2       
4       DERIVED t2      index   NULL    col_int_key     5       NULL    1       Using index