We're updating the issue view to help you get more done. 

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

Description

Stack trace from maria/5.3 revno 3612:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 #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

The problem appeared in maria/5.3 with the following revision:

1 2 3 4 5 6 7 8 9 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.

Reproducible on maria/5.3, maria/5.5.
Minimal optimizer_switch: derived_merge=on,in_to_exists=on
Full optimizer_switch (default):

1 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 (with the default optimizer_switch):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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'))

Test case:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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';

Environment

None

Status

Assignee

Oleksandr Byelkin

Reporter

Elena Stepanova

Fix versions

Affects versions

5.5.29
5.3.12

Priority

Critical