LP:872735 - Assertion `tab->ref.use_count' failed in join_read_key_unlock_row() with derived_merge + InnoDB + DISTINCT

Query:

SELECT *
FROM t1 , t2
WHERE ( t2.a , t1.b ) NOT IN (
SELECT DISTINCT c , a
FROM ( SELECT * FROM t3 ) AS SQ1_alias1
);

asserts as follows:

mysqld: sql_select.cc:15736: void join_read_key_unlock_row(st_join_table*): Assertion `tab->ref.use_count' failed.

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x0833ee94 in join_read_key_unlock_row (tab=0xb85a2e0) at sql_select.cc:15736
#10 0x0833e093 in evaluate_join_record (join=0xb81cfe8, join_tab=0xb85a2e0, error=0) at sql_select.cc:15331
#11 0x0833d8dc in sub_select (join=0xb81cfe8, join_tab=0xb85a2e0, end_of_records=false) at sql_select.cc:15107
#12 0x0833d12f in do_select (join=0xb81cfe8, fields=0xb82218c, table=0x0, procedure=0x0) at sql_select.cc:14770
#13 0x08321a61 in JOIN::exec (this=0xb81cfe8) at sql_select.cc:2679
#14 0x0824c7ed in subselect_single_select_engine::exec (this=0xb804bb0) at item_subselect.cc:2982
#15 0x08246539 in Item_subselect::exec (this=0xb804aa8) at item_subselect.cc:586
#16 0x082469e0 in Item_in_subselect::exec (this=0xb804aa8) at item_subselect.cc:739
#17 0x08248534 in Item_in_subselect::val_bool (this=0xb804aa8) at item_subselect.cc:1452
#18 0x081e21e8 in Item::val_bool_result (this=0xb804aa8) at item.h:842
#19 0x0820e62e in Item_in_optimizer::val_int (this=0xb816670) at item_cmpfunc.cc:1717
#20 0x081ca44c in Item::val_bool (this=0xb816670) at item.cc:197
#21 0x0820b1dc in Item_func_not::val_int (this=0xb815d78) at item_cmpfunc.cc:333
#22 0x0833dc71 in evaluate_join_record (join=0xb817d60, join_tab=0xb81712c, error=0) at sql_select.cc:15202
#23 0x0833d8dc in sub_select (join=0xb817d60, join_tab=0xb81712c, end_of_records=false) at sql_select.cc:15107
#24 0x0833d12f in do_select (join=0xb817d60, fields=0xb795cdc, table=0x0, procedure=0x0) at sql_select.cc:14770
#25 0x08321a61 in JOIN::exec (this=0xb817d60) at sql_select.cc:2679
#26 0x0832228e in mysql_select (thd=0xb794208, rref_pointer_array=0xb795d84, tables=0xb802e48, wild_num=1, fields=..., conds=0xb815d78, og_num=0, order=0x0,
group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xb815e10, unit=0xb795968, select_lex=0xb795c48) at sql_select.cc:2900
#27 0x0831a05f in handle_select (thd=0xb794208, lex=0xb79590c, result=0xb815e10, setup_tables_done_option=0) at sql_select.cc:283
#28 0x082b482c in execute_sqlcom_select (thd=0xb794208, all_tables=0xb802e48) at sql_parse.cc:5112
#29 0x082ab5e9 in mysql_execute_command (thd=0xb794208) at sql_parse.cc:2250
#30 0x082b6e5b in mysql_parse (thd=0xb794208,
rawbuf=0xb802c40 "SELECT *\nFROM t1 , t2 \nWHERE ( t2.a , t1.b ) NOT IN (\nSELECT DISTINCT c , a \nFROM ( SELECT * FROM t3 ) AS SQ1_alias1\n)",
length=119, found_semicolon=0xa0afe228) at sql_parse.cc:6112
#31 0x082a9238 in dispatch_command (command=COM_QUERY, thd=0xb794208, packet=0xb830529 "", packet_length=119) at sql_parse.cc:1221
#32 0x082a8693 in do_command (thd=0xb794208) at sql_parse.cc:916
#33 0x082a5677 in handle_one_connection (arg=0xb794208) at sql_connect.cc:1191
#34 0x00821919 in start_thread () from /lib/libpthread.so.0
#35 0x0076acce in clone () from /lib/libc.so.6

explain:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00 Using where
2 DEPENDENT SUBQUERY t3 index PRIMARY,c c 4 NULL 1 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`b` AS `b`,1 AS `a` from `test`.`t1` join `test`.`t2` where (not(<in_optimizer>((1,`test`.`t1`.`b`),<exists>(select distinct `test`.`t3`.`c`,`test`.`t3`.`a` from `test`.`t3` where (((<cache>(1) = `test`.`t3`.`c`) or isnull(`test`.`t3`.`c`)) and ((<cache>(`test`.`t1`.`b`) = `test`.`t3`.`a`) or isnull(`test`.`t3`.`a`))) having (<is_not_null_test>(`test`.`t3`.`c`) and <is_not_null_test>(`test`.`t3`.`a`))))))

minimal optimizer_switch: derived_merge=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,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

bzr version-info:

revision-id: <email address hidden>
date: 2011-10-12 02:04:03 +0400
build-date: 2011-10-12 11:49:39 +0300
revno: 3224
branch-nick: maria-5.3

test case:

--source include/have_innodb.inc
CREATE TABLE t1 ( b int NOT NULL) ENGINE=InnoDB;
INSERT IGNORE INTO t1 VALUES (9);

CREATE TABLE t2 ( a int NOT NULL ) ;
INSERT IGNORE INTO t2 VALUES (1);

CREATE TABLE t3 ( a int NOT NULL , c int NOT NULL, d varchar(1) NOT NULL, KEY (c) , PRIMARY KEY (a)) ENGINE=InnoDB;
INSERT IGNORE INTO t3 VALUES (14,4,'a'),(15,7,'b'),(16,4,'c'),(17,1,'d'),(18,9,'e'),(19,4,'f'),(20,8,'g');

SET SESSION optimizer_switch='derived_merge=ON,subquery_cache=off';

SELECT *
FROM t1 , t2
WHERE ( t2.a , t1.b ) NOT IN (
SELECT DISTINCT c , a
FROM ( SELECT * FROM t3 ) AS SQ1_alias1
);