Server crashes in create_ref_for_key with join_cache_level>=3, materialization+semijoin+semijoin_with_cache+join_cache_hashed, constant table, aggregate, MyISAM or Aria

[Elena Stepanova]

#4 <signal handler called>
#5 0x08363f46 in create_ref_for_key (join=0x94b2d20, j=0x947be78,
    org_keyuse=0x9474de8, allow_full_scan=true, used_tables=4611686018427387919)
    at sql_select.cc:7483
#6 0x083632f8 in get_best_combination (join=0x94b2d20) at sql_select.cc:7198
#7 0x0835b6ef in make_join_statistics (join=0x94b2d20, tables_list=...,
    conds=0x9473ec8, keyuse_array=0x94b91c8) at sql_select.cc:3616
#8 0x08353399 in JOIN::optimize (this=0x94b2d20) at sql_select.cc:1149
#9 0x08359214 in mysql_select (thd=0x93f04c8, rref_pointer_array=0x93f2064,
    tables=0x945ee80, wild_num=0, fields=..., conds=0x9473038, og_num=0, order=0x0,
    group=0x0, having=0x0, proc_param=0x0, select_options=2147764736,
    result=0x94731e8, unit=0x93f1c48, select_lex=0x93f1f28) at sql_select.cc:2937
#10 0x08350f17 in handle_select (thd=0x93f04c8, lex=0x93f1bec, result=0x94731e8,
    setup_tables_done_option=0) at sql_select.cc:284
#11 0x082ec071 in execute_sqlcom_select (thd=0x93f04c8, all_tables=0x945ee80)
    at sql_parse.cc:5151
#12 0x082e344d in mysql_execute_command (thd=0x93f04c8) at sql_parse.cc:2284
#13 0x082ee6ac in mysql_parse (thd=0x93f04c8,
    rawbuf=0x945ec00 "SELECT c FROM t1, t2 \nWHERE ( a, c ) IN ( \nSELECT alias1.b, alias1.c \nFROM t2 AS alias1, t2 AS alias2 \nWHERE alias2.d = alias1.e \nAND alias1.e = ( SELECT MAX(e) FROM t2 ) \n)", length=174, found_semicolon=0xae83a234)
    at sql_parse.cc:6152
#14 0x082e108d in dispatch_command (command=COM_QUERY, thd=0x93f04c8,
    packet=0x9449311 "SELECT c FROM t1, t2 \nWHERE ( a, c ) IN ( \nSELECT alias1.b, alias1.c \nFROM t2 AS alias1, t2 AS alias2 \nWHERE alias2.d = alias1.e \nAND alias1.e = ( SELECT MAX(e) FROM t2 ) \n)", packet_length=174) at sql_parse.cc:1228
#15 0x082e0537 in do_command (thd=0x93f04c8) at sql_parse.cc:923
#16 0x082dd4bd in handle_one_connection (arg=0x93f04c8) at sql_connect.cc:1193
#17 0xb764fb25 in start_thread () from /lib/libpthread.so.0

bzr version-info
revision-id: <email address hidden>
date: 2012-02-29 23:28:16 -0800
build-date: 2012-03-04 07:28:38 +0400
revno: 3451

Also reproducible on MariaDB 5.5 revno 3309.

EXPLAIN also crashes.

Minimal optimizer_switch: materialization=on,semijoin=on,semijoin_with_cache=on,join_cache_hashed=on
Full optimizer_switch (default): index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

Test case:

SET optimizer_switch='materialization=on,semijoin=on,semijoin_with_cache=on,join_cache_hashed=on';
SET join_cache_level=3;

# MyISAM or Aria

CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (
  b INT, c INT, d VARCHAR(1), e VARCHAR(1),
  KEY (c), KEY (d, c)
) ENGINE=MyISAM;

INSERT INTO t2
VALUES (4, 2, 'v', 'v'),
  (6, 1, 'v', 'v'),(0, 5, 'x', 'x'),
  (7, 3, 'i', 'i'),(7, 1, 'e', 'e'),
  (1, 4, 'p', 'p'),(1, 2, 'j', 'j');

SELECT c FROM t1, t2
WHERE ( a, c ) IN (
  SELECT alias1.b, alias1.c
  FROM t2 AS alias1, t2 AS alias2
  WHERE alias2.d = alias1.e
    AND alias1.e = ( SELECT MAX(e) FROM t2 )
);

# End of test case

[Elena Stepanova (elenst)]

Fix released in 5.3.6