LP:1002079 - Server crashes in Item_singlerow_subselect::val_int with constant table, HAVING, UNION in subquery

#2 0x0000000000866c04 in handle_fatal_signal (sig=11) at signal_handler.cc:273
#3 <signal handler called>
#4 0x000000000065795e in Item_singlerow_subselect::val_int (this=0x30d57e0) at item_subselect.cc:1115
#5 0x00000000006123fb in Arg_comparator::compare_int_signed (this=0x30d5a08) at item_cmpfunc.cc:1164
#6 0x00000000005f34b9 in Arg_comparator::compare (this=0x30d5a08) at item_cmpfunc.h:72
#7 0x00000000006144d4 in Item_func_gt::val_int (this=0x30d5948) at item_cmpfunc.cc:1889
#8 0x0000000000613ae5 in Item_in_optimizer::val_int (this=0x30cd0c8) at item_cmpfunc.cc:1649
#9 0x00000000005c3348 in Item::val_bool (this=0x30cd0c8) at item.cc:199
#10 0x000000000060fdef in Item_func_not_all::val_int (this=0x30cbd88) at item_cmpfunc.cc:360
#11 0x00000000005c3348 in Item::val_bool (this=0x30cbd88) at item.cc:199
#12 0x00000000005df1e3 in Item::val_bool_result (this=0x30cbd88) at item.h:855
#13 0x00000000005d4ee9 in Item_ref::val_bool (this=0x30cd6d8) at item.cc:6761
#14 0x000000000061d433 in Item_cond_and::val_int (this=0x30cc160) at item_cmpfunc.cc:4510
#15 0x0000000000766d44 in return_zero_rows (join=0x30cc350, result=0x30cc330, tables=..., fields=..., send_row=true, select_options=2147764736, info=0xdf5368 "no matching row in const table", having=0x30cc160, all_fields=...) at sql_select.cc:10746
#16 0x0000000000750b58 in JOIN::exec (this=0x30cc350) at sql_select.cc:2192
#17 0x0000000000753869 in mysql_select (thd=0x301a958, rref_pointer_array=0x301d598, tables=0x309fb28, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x30cc160, proc_param=0x0, select_options=2147764736, result=0x30cc330, unit=0x301ce40, select_lex=0x301d348) at sql_select.cc:2963
#18 0x000000000074a309 in handle_select (thd=0x301a958, lex=0x301cda0, result=0x30cc330, setup_tables_done_option=0) at sql_select.cc:286
#19 0x00000000006d7126 in execute_sqlcom_select (thd=0x301a958, all_tables=0x309fb28) at sql_parse.cc:5152
#20 0x00000000006cdeee in mysql_execute_command (thd=0x301a958) at sql_parse.cc:2285
#21 0x00000000006d9b8e in mysql_parse (thd=0x301a958, rawbuf=0x309f140 "SELECT SUM(a) AS f1, a AS f2\nFROM ( t1, t2 )\nHAVING f2 >= ALL ( SELECT 4 UNION SELECT 8 ) AND f1 = 1", length=100, found_semicolon=0x7fd3ffa747e8) at sql_parse.cc:6153
#22 0x00000000006cb64d in dispatch_command (command=COM_QUERY, thd=0x301a958, packet=0x3095cd9 "SELECT SUM(a) AS f1, a AS f2\nFROM ( t1, t2 )\nHAVING f2 >= ALL ( SELECT 4 UNION SELECT 8 ) AND f1 = 1 \n", packet_length=102) at sql_parse.cc:1228
#23 0x00000000006ca934 in do_command (thd=0x301a958) at sql_parse.cc:923
#24 0x00000000006c77cc in handle_one_connection (arg=0x301a958) at sql_connect.cc:1218
#25 0x00007fd40927defc in start_thread (arg=0x7fd3ffa75700) at pthread_create.c:304

bzr version-info
revision-id: <email address hidden>
date: 2012-05-20 14:57:29 +0200
revno: 3526

Also reproducible on maria/5.5 revno 3413 and on release build 5.3.7.
Could not reproduce on MySQL 5.1-5.6.
Brackets in FROM clause seem to be important – crash happens if the clause looks like "FROM ( t1, t2 ) ", but I am not getting either a crash or valgrind errors if it's "FROM t1, t2", although there is no visible difference in EXPLAIN.
Reproducible with the default optimizer_switch as well as with all OFF values (except for in_to_exists=on which is required to execute the query).

Minimal optimizer_switch: in_to_exists=on
Full optimizer_switch (default): 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 (with the minimal optimizer_switch):

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 system NULL NULL NULL NULL 0 0.00 const row not found
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 select sum(`test`.`t1`.`a`) AS `f1`,`test`.`t1`.`a` AS `f2` from `test`.`t1` join `test`.`t2` having (<not>(<in_optimizer>(`f2`,(<max>(select 4 union select 8) > <cache>(`f2`)))) and (`f1` = 1))

1. Test case:

SET optimizer_switch = 'in_to_exists=on';

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (7),(0);
CREATE TABLE t2 (b INT);

SELECT SUM(a) AS f1, a AS f2
FROM ( t1, t2 )
HAVING f2 >= ALL ( SELECT 4 UNION SELECT 8 ) AND f1 = 1;

1. End of test case