Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate function, IN subquery

[Elena Stepanova]

#2 0x00000000006e1cc0 in handle_segfault (sig=11) at mysqld.cc:2838
#3 <signal handler called>
#4 0x00000000008a7a20 in do_copy_not_null (copy=0x1cef128) at field_conv.cc:255
#5 0x00000000007ab08e in store_key_field::copy_inner (this=0x1cef100) at sql_select.h:1521
#6 0x000000000068cd2f in store_key::copy (this=0x1cef100) at sql_select.h:1462
#7 0x00000000007a083a in cp_buffer_from_ref (thd=0x1c2cbd8, table=0x1ce0608, ref=0x1cf1000)
    at sql_select.cc:18791
#8 0x0000000000798ee8 in join_read_always_key (tab=0x1cf0df0) at sql_select.cc:15984
#9 0x000000000079747c in sub_select (join=0x1d37350, join_tab=0x1cf0df0, end_of_records=false)
    at sql_select.cc:15301
#10 0x0000000000797c8e in evaluate_join_record (join=0x1d37350, join_tab=0x1cf0ad0, error=0)
    at sql_select.cc:15501
#11 0x00000000007974d8 in sub_select (join=0x1d37350, join_tab=0x1cf0ad0, end_of_records=false)
    at sql_select.cc:15304
#12 0x0000000000797c8e in evaluate_join_record (join=0x1d37350, join_tab=0x1cf07b0, error=0)
    at sql_select.cc:15501
#13 0x00000000007974d8 in sub_select (join=0x1d37350, join_tab=0x1cf07b0, end_of_records=false)
    at sql_select.cc:15304
#14 0x0000000000796ca6 in do_select (join=0x1d37350, fields=0x1d3f2b8, table=0x0, procedure=0x0)
    at sql_select.cc:14965
#15 0x0000000000778872 in JOIN::exec (this=0x1d37350) at sql_select.cc:2704
#16 0x0000000000779109 in mysql_select (thd=0x1c2cbd8, rref_pointer_array=0x1c2f7a8,
    tables=0x1cb1438, wild_num=0, fields=..., conds=0x1ced8b0, og_num=0, order=0x0, group=0x0,
    having=0x0, proc_param=0x0, select_options=2147764736, result=0x1cedad8, unit=0x1c2f070,
    select_lex=0x1c2f558) at sql_select.cc:2925
#17 0x000000000076fd8d in handle_select (thd=0x1c2cbd8, lex=0x1c2efd0, result=0x1cedad8,
    setup_tables_done_option=0) at sql_select.cc:283
#18 0x00000000006feb20 in execute_sqlcom_select (thd=0x1c2cbd8, all_tables=0x1cb1438)
    at sql_parse.cc:5148
#19 0x00000000006f5bdd in mysql_execute_command (thd=0x1c2cbd8) at sql_parse.cc:2281
#20 0x00000000007014fb in mysql_parse (thd=0x1c2cbd8,
    rawbuf=0x1cb0ff0 "SELECT a, COUNT(*) FROM t1\nWHERE a IN ( \nSELECT b FROM t2, t3 \nWHERE c = b AND b = a\n)", length=86, found_semicolon=0x7f8de4905c98) at sql_parse.cc:6149
#21 0x00000000006f33b2 in dispatch_command (command=COM_QUERY, thd=0x1c2cbd8, packet=0x1ca7b89 "",
    packet_length=86) at sql_parse.cc:1227
#22 0x00000000006f26e6 in do_command (thd=0x1c2cbd8) at sql_parse.cc:922
#23 0x00000000006ef60e in handle_one_connection (arg=0x1c2cbd8) at sql_connect.cc:1193
#24 0x00007f8dee0a3a4f in start_thread () from /lib64/libpthread.so.0
#25 0x00007f8ded48d82d in clone () from /lib64/libc.so.6

bzr version-info
revision-id: <email address hidden>
date: 2012-01-02 20:06:36 -0800
build-date: 2012-01-05 15:17:09 +0200
revno: 3376
branch-nick: maria-5.3

Minimal optimizer_switch: firstmatch=on,semijoin=on (current 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

EXPLAIN:

1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
1 PRIMARY t2 ref b b 3 test.t1.a 2 100.00 Using index
1 PRIMARY t3 ref c c 3 test.t1.a 2 100.00 Using index; FirstMatch(t1)
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t1`.`a` AS `a`,count(0) AS `COUNT(*)` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t3`.`c` = `test`.`t1`.`a`))
SELECT a, COUNT(*) FROM t1
WHERE a IN (
SELECT b FROM t2, t3
WHERE c = b AND b = a
);

Reproducible on 5.3.3 release binaries.
Not reproducible on 5.2.10.
Could not reproduce on current 5.5, it chooses a different plan.

Test case:

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

CREATE TABLE t1 ( a VARCHAR(1) NOT NULL );
INSERT INTO t1 VALUES ('k'),('l');

CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) );
INSERT INTO t2 VALUES ('k'),('l');

CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) );
INSERT INTO t3 VALUES ('m'),('n');

SELECT a, COUNT(*) FROM t1
  WHERE a IN (
    SELECT b FROM t2, t3
    WHERE c = b AND b = a
  );

[Elena Stepanova (elenst)]

This bug has been also filed in JIRA as MDEV-67

[Sergey Petrunia (sergefp)]

Analysis was posted at https://mariadb.atlassian.net/browse/MDEV-67