Server crashes in Item_func_trig_cond::val_int with FROM and NOT IN subqueries, LEFT JOIN, derived_merge+in_to_exists

#3  <signal handler called>

#4  0x000000000061ec55 in Item_func_trig_cond::val_int (this=0x3c2cd48) at item_cmpfunc.h:480

#5  0x000000000057bce6 in Item::val_bool (this=0x3c2cd48) at item.cc:199

#6  0x00000000005d726b in Item_cond_and::val_int (this=0x3c563a8) at item_cmpfunc.cc:4550

#7  0x0000000000735cdf in end_send_group (join=0x3c24088, join_tab=0x3c56b78, end_of_records=true) at sql_select.cc:16886

#8  0x0000000000732b12 in sub_select (join=0x3c24088, join_tab=0x3c56858, end_of_records=true) at sql_select.cc:15662

#9  0x00000000007325e7 in do_select (join=0x3c24088, fields=0x3c24468, table=0x0, procedure=0x0) at sql_select.cc:15384

#10 0x000000000071303d in JOIN::exec (this=0x3c24088) at sql_select.cc:2764

#11 0x0000000000618b9e in subselect_single_select_engine::exec (this=0x3c1f9f8) at item_subselect.cc:3141

#12 0x00000000006112f5 in Item_subselect::exec (this=0x3c1f7f8) at item_subselect.cc:588

#13 0x0000000000611837 in Item_in_subselect::exec (this=0x3c1f7f8) at item_subselect.cc:756

#14 0x0000000000613da9 in Item_in_subselect::val_bool (this=0x3c1f7f8) at item_subselect.cc:1577

#15 0x0000000000597dcd in Item::val_bool_result (this=0x3c1f7f8) at item.h:856

#16 0x00000000005cd863 in Item_in_optimizer::val_int (this=0x3c24c38) at item_cmpfunc.cc:1731

#17 0x000000000057bce6 in Item::val_bool (this=0x3c24c38) at item.cc:199

#18 0x00000000005c97d7 in Item_func_not::val_int (this=0x3c19058) at item_cmpfunc.cc:332

#19 0x000000000057bce6 in Item::val_bool (this=0x3c19058) at item.cc:199

#20 0x00000000005d726b in Item_cond_and::val_int (this=0x3c2b4d8) at item_cmpfunc.cc:4550

#21 0x000000000061ec89 in Item_func_trig_cond::val_int (this=0x3c2b6a8) at item_cmpfunc.h:480

#22 0x000000000057bce6 in Item::val_bool (this=0x3c2b6a8) at item.cc:199

#23 0x00000000005d726b in Item_cond_and::val_int (this=0x3c2b798) at item_cmpfunc.cc:4550

#24 0x00000000006df0a4 in SQL_SELECT::skip_record (this=0x3c2af38, thd=0x3b9d670) at opt_range.h:924

#25 0x00000000006e00d1 in JOIN_CACHE::check_match (this=0x3c2b968, rec_ptr=0x3c353b1 "") at sql_join_cache.cc:2445

#26 0x00000000006db980 in JOIN_CACHE::generate_full_extensions (this=0x3c2b968, rec_ptr=0x3c353b1 "") at sql_join_cache.cc:2388

#27 0x00000000006db75b in JOIN_CACHE::join_matching_records (this=0x3c2b968, skip_last=false) at sql_join_cache.cc:2288

#28 0x00000000006db182 in JOIN_CACHE::join_records (this=0x3c2b968, skip_last=false) at sql_join_cache.cc:2085

#29 0x00000000007328fd in sub_select_cache (join=0x3c22c68, join_tab=0x3c2a298, end_of_records=true) at sql_select.cc:15500

#30 0x0000000000732b12 in sub_select (join=0x3c22c68, join_tab=0x3c29f78, end_of_records=true) at sql_select.cc:15662

#31 0x00000000007325e7 in do_select (join=0x3c22c68, fields=0x3ba0248, table=0x0, procedure=0x0) at sql_select.cc:15384

#32 0x000000000071303d in JOIN::exec (this=0x3c22c68) at sql_select.cc:2764

#33 0x00000000007138c9 in mysql_select (thd=0x3b9d670, rref_pointer_array=0x3ba03d8, tables=0x3c1cd38, wild_num=1, fields=..., conds=0x3c212a8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x3c22c18, unit=0x3b9fc08, select_lex=0x3ba0130) at sql_select.cc:2985

#34 0x000000000070a283 in handle_select (thd=0x3b9d670, lex=0x3b9fb68, result=0x3c22c18, setup_tables_done_option=0) at sql_select.cc:286

#35 0x00000000006957a3 in execute_sqlcom_select (thd=0x3b9d670, all_tables=0x3c1cd38) at sql_parse.cc:5157

#36 0x000000000068c562 in mysql_execute_command (thd=0x3b9d670) at sql_parse.cc:2290

#37 0x00000000006981fb in mysql_parse (thd=0x3b9d670, rawbuf=0x3c0e738 "SELECT * FROM t1 LEFT JOIN (  \nSELECT * FROM t2 WHERE ( pk, pk ) NOT IN ( \nSELECT MIN(b), SUM(pk) FROM t1\n) \n) AS alias1 ON (a = c) \nWHERE b IS NULL OR a < 'u'", length=159, found_semicolon=0x7f190a76e7e0) at sql_parse.cc:6158

#38 0x0000000000689d08 in dispatch_command (command=COM_QUERY, thd=0x3b9d670, packet=0x3be6c31 "", packet_length=159) at sql_parse.cc:1228

#39 0x000000000068901c in do_command (thd=0x3b9d670) at sql_parse.cc:923

#40 0x0000000000685a77 in handle_one_connection (arg=0x3b9d670) at sql_connect.cc:1231

#41 0x00007f1913f79efc in start_thread (arg=0x7f190a76f700) at pthread_create.c:304

#42 0x00007f1913520f4d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

revno: 3608

revision-id: sanja@askmonty.org-20121228124146-bk5tzqdvi4yukdpj

parent: timour@askmonty.org-20121219135657-mbje1blpgw2qsw7j

committer: sanja@askmonty.org

branch nick: work-maria-5.3-MDEV-3873

timestamp: Fri 2012-12-28 14:41:46 +0200

message:

  MDEV-3873 & MDEV-3876 & MDEV-3912 : Wrong result (extra rows) with ALL subquery

  from a MERGE view.

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,extended_keys=off

EXPLAIN EXTENDED

SELECT * FROM t1 LEFT JOIN (  

SELECT * FROM t2 WHERE ( pk, pk ) NOT IN ( 

SELECT MIN(b), SUM(pk) FROM t1

) 

) AS alias1 ON (a = c) 

WHERE b IS NULL OR a < 'u';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	

1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)

3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	

Warnings:

Note	1276	Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2

Note	1276	Field or reference 'test.t2.pk' of SELECT #3 was resolved in SELECT #2

Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` left join (`test`.`t2`) on(((not(<in_optimizer>((`test`.`t2`.`pk`,`test`.`t2`.`pk`),<exists>(select min(`test`.`t2`.`b`),sum(`test`.`t2`.`pk`) from `test`.`t1` having (trigcond(((<cache>(`test`.`t2`.`pk`) = min(`test`.`t2`.`b`)) or isnull(min(`test`.`t2`.`b`)))) and trigcond(((<cache>(`test`.`t2`.`pk`) = sum(`test`.`t2`.`pk`)) or isnull(sum(`test`.`t2`.`pk`)))) and trigcond(<is_not_null_test>(min(`test`.`t2`.`b`))) and trigcond(<is_not_null_test>(sum(`test`.`t2`.`pk`)))))))) and (`test`.`t2`.`c` = `test`.`t1`.`a`))) where (isnull(`test`.`t2`.`b`) or (`test`.`t1`.`a` < 'u'))

SET optimizer_switch = 'derived_merge=on,in_to_exists=on';

CREATE TABLE t1 (a VARCHAR(1)) ENGINE=MyISAM;

INSERT INTO t1 VALUES ('x'),('d');

CREATE TABLE t2 (pk INT PRIMARY KEY, b INT, c VARCHAR(1))  ENGINE=MyISAM;

INSERT INTO t2 VALUES (1,2,'v'),(2,150,'v');

SELECT * FROM t1 LEFT JOIN (  

  SELECT * FROM t2 WHERE ( pk, pk ) NOT IN ( 

    SELECT MIN(b), SUM(pk) FROM t1

  ) 

) AS alias1 ON (a = c) 

WHERE b IS NULL OR a < 'u';