EXPLAIN EXTENDED crashes in TABLE_LIST::print with limited max_join_size, materialization=ON,semijoin=ON

[Elena Stepanova]

#4 <signal handler called>
#5 0x0838396a in TABLE_LIST::print (this=0x9470e70, thd=0x93eaa98,
    eliminated_tables=0, str=0x4894e718, query_type=QT_ORDINARY)
    at sql_select.cc:21380
#6 0x083832a8 in print_table_array (thd=0x93eaa98, eliminated_tables=0,
    str=0x4894e718, table=0x9472068, end=0x9472074, query_type=QT_ORDINARY)
    at sql_select.cc:21218
#7 0x083836bc in print_join (thd=0x93eaa98, eliminated_tables=0,
    str=0x4894e718, tables=0x93ec594, query_type=QT_ORDINARY)
    at sql_select.cc:21312
#8 0x0838408f in st_select_lex::print (this=0x93ec4d0, thd=0x93eaa98,
    str=0x4894e718, query_type=QT_ORDINARY) at sql_select.cc:21531
#9 0x081e5a24 in st_select_lex_unit::print (this=0x93ec1f0, str=0x4894e718,
    query_type=QT_ORDINARY) at sql_lex.cc:2099
#10 0x082ebd1e in execute_sqlcom_select (thd=0x93eaa98, all_tables=0x9459ec8)
    at sql_parse.cc:5133
#11 0x082e3204 in mysql_execute_command (thd=0x93eaa98) at sql_parse.cc:2281
#12 0x082ee463 in mysql_parse (thd=0x93eaa98,
    rawbuf=0x9459be8 "EXPLAIN EXTENDED \nSELECT COUNT(*) FROM t1 \nWHERE a IN \n( SELECT b FROM t2 GROUP BY b ) \nAND ( 6 ) IN \n( SELECT MIN( t2.b ) FROM t2 alias1, t2 )", length=146, found_semicolon=0x4894f234) at sql_parse.cc:6149
#13 0x082e0e66 in dispatch_command (command=COM_QUERY, thd=0x93eaa98,
    packet=0x9444311 "", packet_length=146) at sql_parse.cc:1227
#14 0x082e0310 in do_command (thd=0x93eaa98) at sql_parse.cc:922
#15 0x082dd2d5 in handle_one_connection (arg=0x93eaa98) at sql_connect.cc:1193
#16 0x400c2b25 in start_thread () from /lib/libpthread.so.0

Apparently it's a leftover from bug#901506.
Now the additional condition is that max_join_size is set to a non-default value. In the test case it's unrealistically small, but in fact the threshold depends on the number of rows in t2. 20 rows was as low as I could reach still getting the crash, and it requires the max size value of ~ 80. A more complicated test case with ~300 rows in the table was still causing the crash with max_join_size=1,000,000.

The SELECT itself fails with ER_TOO_BIG_SELECT.

bzr version-info
revision-id: <email address hidden>
date: 2011-12-16 08:05:14 -0800
build-date: 2011-12-19 19:54:00 +0400
revno: 3358
branch-nick: maria-5.3

Not reproducible on revno 3250.

Minimal optimizer_switch: materialization=on,semijoin=on (both are defaults)

Full optimizer_switch: 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:

CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT );

INSERT INTO t1 VALUES (1),(2);
INSERT INTO t2 VALUES
  (1),(2),(3),(4),(5),
  (6),(7),(8),(9),(10),
  (11),(12),(13),(14),(15),
  (16),(17),(18),(19),(20);

SET optimizer_switch = 'materialization=on,semijoin=on';
SET max_join_size = 80;

EXPLAIN EXTENDED
SELECT COUNT(*) FROM t1
WHERE a IN
      ( SELECT b FROM t2 GROUP BY b )
  AND ( 6 ) IN
  ( SELECT MIN( t2.b ) FROM t2 alias1, t2 );

[Elena Stepanova (elenst)]

Fix released with 5.3.3-rc.