Crash in mark_as_null_row with materialization+semijoin, aggregate functions, constant MyISAM or Aria table

[Elena Stepanova]

#3 0x083e4684 in handle_fatal_signal (sig=11)
    at /home/elenst/maria-5.5/sql/signal_handler.cc:271
#4 <signal handler called>
#5 0x082bbf9e in mark_as_null_row (table=0x9730ee0)
    at /home/elenst/maria-5.5/sql/table.h:2374
#6 0x082a0077 in return_zero_rows (join=0x97222b0, result=0x97101b0, tables=...,
    fields=..., send_row=true, select_options=2147748608,
    info=0x89075cc "no matching row in const table", having=0x0)
    at /home/elenst/maria-5.5/sql/sql_select.cc:10557
#7 0x0828c028 in JOIN::exec (this=0x97222b0)
    at /home/elenst/maria-5.5/sql/sql_select.cc:2197
#8 0x0828e69c in mysql_select (thd=0x965f8e0, rref_pointer_array=0x9661458,
    tables=0x970ef18, wild_num=0, fields=..., conds=0x9710018, og_num=0, order=0x0,
    group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x97101b0,
    unit=0x9660e80, select_lex=0x966131c) at /home/elenst/maria-5.5/sql/sql_select.cc:2951
#9 0x082864ba in handle_select (thd=0x965f8e0, lex=0x9660e1c, result=0x97101b0,
    setup_tables_done_option=0) at /home/elenst/maria-5.5/sql/sql_select.cc:308
#10 0x082626cd in execute_sqlcom_select (thd=0x965f8e0, all_tables=0x970ef18)
    at /home/elenst/maria-5.5/sql/sql_parse.cc:4614
#11 0x0825b674 in mysql_execute_command (thd=0x965f8e0)
    at /home/elenst/maria-5.5/sql/sql_parse.cc:2183
#12 0x08264ca8 in mysql_parse (thd=0x965f8e0,
    rawbuf=0x970ec90 "SELECT MAX(a) FROM t1 \nWHERE a IN ( SELECT MAX(b) FROM t2 )",
    length=60, parser_state=0xad213db8) at /home/elenst/maria-5.5/sql/sql_parse.cc:5728
#13 0x0825913e in dispatch_command (command=COM_QUERY, thd=0x965f8e0,
    packet=0x9709009 "SELECT MAX(a) FROM t1 \nWHERE a IN ( SELECT MAX(b) FROM t2 )",
    packet_length=60) at /home/elenst/maria-5.5/sql/sql_parse.cc:1054
#14 0x08258603 in do_command (thd=0x965f8e0) at /home/elenst/maria-5.5/sql/sql_parse.cc:793
#15 0x08341236 in do_handle_one_connection (thd_arg=0x965f8e0)
    at /home/elenst/maria-5.5/sql/sql_connect.cc:1252
#16 0x08340d84 in handle_one_connection (arg=0x965f8e0)
    at /home/elenst/maria-5.5/sql/sql_connect.cc:1167
#17 0x085621e9 in pfs_spawn_thread (arg=0x974e318)
    at /home/elenst/maria-5.5/storage/perfschema/pfs.cc:1015
#18 0xb7883b25 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 08:43:43 +0400
revno: 3228
branch-nick: maria-5.5

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

Minimal optimizer_switch: materialization=on,semijoin=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

EXPLAIN EXTENDED SELECT MAX(a) FROM t1
WHERE a IN ( SELECT MAX(b) FROM t2 );
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select max(NULL) AS `MAX(a)` from <materialize> (select max(`test`.`t2`.`b`) from `test`.`t2`) join `test`.`t1` where 0
SELECT MAX(a) FROM t1
WHERE a IN ( SELECT MAX(b) FROM t2 );

Test case:

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

# The first table has to be MyISAM or Aria,
# but not InnoDB
CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
CREATE TABLE t2 ( b INT ) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1),(2);

SELECT MAX(a) FROM t1
WHERE a IN ( SELECT MAX(b) FROM t2 );

[Elena Stepanova (elenst)]

Also filed as JIRA issue MDEV-113

[Sergey Petrunia (sergefp)]

This is fixed by BUG#933407. Fix for that bug has been pushed into 5.3, so it's only a question of merging.

[Sergey Petrunia (sergefp)]

(if you can't wait, get the fix from <email address hidden> list and apply manually, that also works)

[Sergey Petrunia (sergefp)]

Marking as Duplicate