Assertion `!eliminated_tables || !(((*table)->table && ((*table)->table->map & eliminated_tables)) || ((*table)->nested_join && !((*table)->nested_join->used_tables & ~eliminated_tables)))' fails on EXPLAIN EXTENDED with LEFT JOIN and table_elimination=on

[Elena Stepanova]

mysqld: /home/elenst/maria-5.5/sql/sql_select.cc:21470: void print_join(THD*, table_map, String*, List<TABLE_LIST>*, enum_query_type): Assertion `!eliminated_tables || !(((*table)->table && ((*table)->table->map & eliminated_tables)) || ((*table)->nested_join && !((*table)->nested_join->used_tables & ~eliminated_tables)))' failed.

#8 0xb75c6014 in __assert_fail () from /lib/libc.so.6
#9 0x082b92e6 in print_join (thd=0x965e768, eliminated_tables=2, str=0xad2357f8,
    tables=0x970fb2c, query_type=QT_TO_SYSTEM_CHARSET)
    at /home/elenst/maria-5.5/sql/sql_select.cc:21467
#10 0x082b9df7 in st_select_lex::print (this=0x970fa68, thd=0x965e768,
    str=0xad2357f8, query_type=QT_TO_SYSTEM_CHARSET)
    at /home/elenst/maria-5.5/sql/sql_select.cc:21717
#11 0x08474de8 in subselect_single_select_engine::print (this=0x9711338,
    str=0xad2357f8, query_type=QT_TO_SYSTEM_CHARSET)
    at /home/elenst/maria-5.5/sql/item_subselect.cc:3618
#12 0x0846e046 in Item_subselect::print (this=0x9711238, str=0xad2357f8,
    query_type=QT_TO_SYSTEM_CHARSET)
    at /home/elenst/maria-5.5/sql/item_subselect.cc:808
#13 0x084725b8 in Item_in_subselect::print (this=0x9711238, str=0xad2357f8,
    query_type=QT_TO_SYSTEM_CHARSET)
    at /home/elenst/maria-5.5/sql/item_subselect.cc:2452
#14 0x084394df in Item_func::print_args (this=0x9725598, str=0xad2357f8, from=0,
    query_type=QT_TO_SYSTEM_CHARSET) at /home/elenst/maria-5.5/sql/item_func.cc:480
#15 0x08439455 in Item_func::print (this=0x9725598, str=0xad2357f8,
    query_type=QT_TO_SYSTEM_CHARSET) at /home/elenst/maria-5.5/sql/item_func.cc:469
#16 0x082b9e9d in st_select_lex::print (this=0x96601a4, thd=0x965e768,
    str=0xad2357f8, query_type=QT_TO_SYSTEM_CHARSET)
    at /home/elenst/maria-5.5/sql/sql_select.cc:21736
#17 0x082515d0 in st_select_lex_unit::print (this=0x965fd08, str=0xad2357f8,
    query_type=QT_TO_SYSTEM_CHARSET) at /home/elenst/maria-5.5/sql/sql_lex.cc:2322
#18 0x08262541 in execute_sqlcom_select (thd=0x965e768, all_tables=0x970f5b0)
    at /home/elenst/maria-5.5/sql/sql_parse.cc:4599
#19 0x0825b5f4 in mysql_execute_command (thd=0x965e768)
    at /home/elenst/maria-5.5/sql/sql_parse.cc:2183
#20 0x08264c28 in mysql_parse (thd=0x965e768,
    rawbuf=0x970f3c8 "EXPLAIN EXTENDED \nSELECT * FROM t2 \nWHERE b IN ( \nSELECT SUM(a) FROM t1 LEFT JOIN t2 ON b=a\n)", length=96, parser_state=0xad235db8)
    at /home/elenst/maria-5.5/sql/sql_parse.cc:5728
#21 0x082590be in dispatch_command (command=COM_QUERY, thd=0x965e768,
    packet=0x9709741 "", packet_length=96)
    at /home/elenst/maria-5.5/sql/sql_parse.cc:1054
#22 0x08258583 in do_command (thd=0x965e768)
    at /home/elenst/maria-5.5/sql/sql_parse.cc:793
#23 0x083411b6 in do_handle_one_connection (thd_arg=0x965e768)
    at /home/elenst/maria-5.5/sql/sql_connect.cc:1252
#24 0x08340d04 in handle_one_connection (arg=0x965e768)
    at /home/elenst/maria-5.5/sql/sql_connect.cc:1167
#25 0x08562169 in pfs_spawn_thread (arg=0x9750620)
    at /home/elenst/maria-5.5/storage/perfschema/pfs.cc:1015
#26 0xb78a5b25 in start_thread () from /lib/libpthread.so.0

 bzr version-info
revision-id: <email address hidden>
date: 2012-01-20 22:32:31 +0100
build-date: 2012-01-22 05:48:07 +0400
revno: 3228
branch-nick: maria-5.5

Could not reproduce on MariaDB-5.3 or MySQL-5.5.20.

Minimal optimizer_switch: in_to_exists=on,table_elimination=on
Full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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='in_to_exists=on,table_elimination=on';

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

CREATE TABLE t2
  ( b INT, UNIQUE INDEX(b) );
INSERT INTO t2 VALUES (1),(2);

EXPLAIN EXTENDED
  SELECT * FROM t2
  WHERE b IN (
    SELECT SUM(a) FROM t1 LEFT JOIN t2 ON b=a
  );

[Elena Stepanova (elenst)]

Also filed as JIRA issue MDEV-112

[Elena Stepanova (elenst)]

Fix released with 5.5.20.