Details
Major Description
CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; |
|
|
CREATE TABLE t2 (c int, d int) ENGINE=MyISAM; |
INSERT INTO t2 VALUES (1,10); |
|
|
CREATE TABLE t3 (e int, key (e)) ENGINE=MyISAM; |
INSERT INTO t3 VALUES (2),(3); |
|
|
EXPLAIN
|
SELECT * FROM t1 |
WHERE a > b OR a IN ( |
SELECT c FROM t2 WHERE EXISTS ( |
SELECT * FROM t3 t3a JOIN t3 t3b WHERE t3a.e < d |
)
|
);
|
|
10.4 589a1235 |
#3 <signal handler called>
|
#4 __memmove_avx_unaligned_erms () at ../sysdeps/x86_64/multiarch/memmove-vec-unaligned-erms.S:268
|
#5 0x00005601ae862f52 in JOIN_CACHE::write_record_data (this=0x7f82bc19d010, link=0x0, is_full=0x7f82d8093347) at /data/src/10.4/sql/sql_join_cache.cc:1361
|
#6 0x00005601ae863ce2 in JOIN_CACHE::put_record (this=0x7f82bc19d010) at /data/src/10.4/sql/sql_join_cache.cc:1568
|
#7 0x00005601ae714331 in sub_select_cache (join=0x7f82bc07b9b8, join_tab=0x7f82bc19c6f0, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:19978
|
#8 0x00005601ae714dea in evaluate_join_record (join=0x7f82bc07b9b8, join_tab=0x7f82bc19c348, error=0) at /data/src/10.4/sql/sql_select.cc:20407
|
#9 0x00005601ae7146d0 in sub_select (join=0x7f82bc07b9b8, join_tab=0x7f82bc19c348, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:20187
|
#10 0x00005601ae713b5f in do_select (join=0x7f82bc07b9b8, procedure=0x0) at /data/src/10.4/sql/sql_select.cc:19725
|
#11 0x00005601ae6e8d69 in JOIN::exec_inner (this=0x7f82bc07b9b8) at /data/src/10.4/sql/sql_select.cc:4386
|
#12 0x00005601ae6e7ea6 in JOIN::exec (this=0x7f82bc07b9b8) at /data/src/10.4/sql/sql_select.cc:4168
|
#13 0x00005601aeadd632 in subselect_single_select_engine::exec (this=0x7f82bc017a40) at /data/src/10.4/sql/item_subselect.cc:3935
|
#14 0x00005601aead08fd in Item_subselect::exec (this=0x7f82bc0178a0) at /data/src/10.4/sql/item_subselect.cc:746
|
#15 0x00005601aead3cb1 in Item_exists_subselect::val_int (this=0x7f82bc0178a0) at /data/src/10.4/sql/item_subselect.cc:1652
|
#16 0x00005601aea3d0ee in Item_in_optimizer::val_int (this=0x7f82bc07c160) at /data/src/10.4/sql/item_cmpfunc.cc:1561
|
#17 0x00005601ae8b3113 in Type_handler_int_result::Item_val_bool (this=0x5601afe30b70 <type_handler_bool>, item=0x7f82bc07c160) at /data/src/10.4/sql/sql_type.cc:4416
|
#18 0x00005601ae57294e in Item::val_bool (this=0x7f82bc07c160) at /data/src/10.4/sql/item.h:1458
|
#19 0x00005601ae72d869 in Item::eval_const_cond (this=0x7f82bc07c160) at /data/src/10.4/sql/item.h:1466
|
#20 0x00005601aea492aa in Item_cond::eval_not_null_tables (this=0x7f82bc199848, opt_arg=0x0) at /data/src/10.4/sql/item_cmpfunc.cc:4914
|
#21 0x00005601ae604003 in Item_func_or_sum::walk (this=0x7f82bc199848, processor=&virtual table offset 976, walk_subquery=false, arg=0x0) at /data/src/10.4/sql/item.h:5095
|
#22 0x00005601aea49597 in Item_cond::walk (this=0x7f82bc199848, processor=&virtual table offset 976, walk_subquery=false, arg=0x0) at /data/src/10.4/sql/item_cmpfunc.cc:4986
|
#23 0x00005601ae663835 in st_select_lex::update_used_tables (this=0x7f82bc0144b0) at /data/src/10.4/sql/sql_lex.cc:4586
|
#24 0x00005601ae662b12 in st_select_lex::optimize_unflattened_subqueries (this=0x7f82bc0132a8, const_only=false) at /data/src/10.4/sql/sql_lex.cc:4189
|
#25 0x00005601ae88206e in JOIN::optimize_unflattened_subqueries (this=0x7f82bc07a908) at /data/src/10.4/sql/opt_subselect.cc:5512
|
#26 0x00005601ae6e324f in JOIN::optimize_stage2 (this=0x7f82bc07a908) at /data/src/10.4/sql/sql_select.cc:2962
|
#27 0x00005601ae6e08b0 in JOIN::optimize_inner (this=0x7f82bc07a908) at /data/src/10.4/sql/sql_select.cc:2214
|
#28 0x00005601ae6de3a2 in JOIN::optimize (this=0x7f82bc07a908) at /data/src/10.4/sql/sql_select.cc:1557
|
#29 0x00005601ae6e9522 in mysql_select (thd=0x7f82bc000af0, tables=0x7f82bc013868, wild_num=1, fields=..., conds=0x7f82bc0184d8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7f82bc018fa0, unit=0x7f82bc004a20, select_lex=0x7f82bc0132a8) at /data/src/10.4/sql/sql_select.cc:4586
|
#30 0x00005601ae726761 in mysql_explain_union (thd=0x7f82bc000af0, unit=0x7f82bc004a20, result=0x7f82bc018fa0) at /data/src/10.4/sql/sql_select.cc:26763
|
#31 0x00005601ae69f872 in execute_sqlcom_select (thd=0x7f82bc000af0, all_tables=0x7f82bc013868) at /data/src/10.4/sql/sql_parse.cc:6296
|
#32 0x00005601ae695389 in mysql_execute_command (thd=0x7f82bc000af0) at /data/src/10.4/sql/sql_parse.cc:3899
|
#33 0x00005601ae6a3daf in mysql_parse (thd=0x7f82bc000af0, rawbuf=0x7f82bc013138 "EXPLAIN\nSELECT * FROM t1\nWHERE a > b OR a IN (\nSELECT c FROM t2 WHERE EXISTS (\nSELECT * FROM t3 t3a JOIN t3 t3b WHERE t3a.e < d\n)\n)", length=131, parser_state=0x7f82d8095170, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7898
|
#34 0x00005601ae68ef8c in dispatch_command (command=COM_QUERY, thd=0x7f82bc000af0, packet=0x7f82bc008341 "", packet_length=131, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1842
|
#35 0x00005601ae68d619 in do_command (thd=0x7f82bc000af0) at /data/src/10.4/sql/sql_parse.cc:1360
|
#36 0x00005601ae8158d1 in do_handle_one_connection (connect=0x5601b0e1fed0) at /data/src/10.4/sql/sql_connect.cc:1412
|
#37 0x00005601ae815620 in handle_one_connection (arg=0x5601b0e1fed0) at /data/src/10.4/sql/sql_connect.cc:1316
|
#38 0x00005601af217137 in pfs_spawn_thread (arg=0x5601b0d44730) at /data/src/10.4/storage/perfschema/pfs.cc:1862
|
#39 0x00007f82dea934a4 in start_thread (arg=0x7f82d8096700) at pthread_create.c:456
|
#40 0x00007f82dcfdad0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97
|
All of debug, non-debug and ASAN builds crash the same way.
Reproducible on 10.1-10.5. Not reproducible on 5.5.
Couldn't reproduce with non-constant tables.
The query itself doesn't crash.
Attachments
Activity
I've got a complaint that crash with this (or similar?) stack trace
was getting in the way of testing another fix.
Investigation.
We crash when putting a record into a JOIN_CACHE_BNL object. That object was inialized by calling JOIN_CACHE::init(for_explain=true), so it's not really initialized for execution.
We evaluate the subquery here:
#2 0x00005555560f75fa in JOIN_CACHE::put_record (this=0x7fff280dc3d0) at /home/psergey/dev-git2/10.6-vg/sql/sql_join_cache.cc:1595
|
#3 0x0000555555f5f318 in sub_select_cache (join=0x7fff280ddcc0, join_tab=0x7fff280db2e0, end_of_records=false) at /home/psergey/dev-git2/10.6-vg/sql/sql_select.cc:21642
|
#4 0x0000555555f60063 in evaluate_join_record (join=0x7fff280ddcc0, join_tab=0x7fff280daf20, error=0) at /home/psergey/dev-git2/10.6-vg/sql/sql_select.cc:22104
|
#5 0x0000555555f5f845 in sub_select (join=0x7fff280ddcc0, join_tab=0x7fff280daf20, end_of_records=false) at /home/psergey/dev-git2/10.6-vg/sql/sql_select.cc:21874
|
#6 0x0000555555f5eb02 in do_select (join=0x7fff280ddcc0, procedure=0x0) at /home/psergey/dev-git2/10.6-vg/sql/sql_select.cc:21397
|
#7 0x0000555555f30140 in JOIN::exec_inner (this=0x7fff280ddcc0) at /home/psergey/dev-git2/10.6-vg/sql/sql_select.cc:4884
|
#8 0x0000555555f2f1bb in JOIN::exec (this=0x7fff280ddcc0) at /home/psergey/dev-git2/10.6-vg/sql/sql_select.cc:4662
|
#9 0x000055555638b29e in subselect_single_select_engine::exec (this=0x7fff2801b480) at /home/psergey/dev-git2/10.6-vg/sql/item_subselect.cc:4110
|
#10 0x000055555637db9f in Item_subselect::exec (this=0x7fff2801a700) at /home/psergey/dev-git2/10.6-vg/sql/item_subselect.cc:811
|
#11 0x000055555638154f in Item_exists_subselect::val_int (this=0x7fff2801a700) at /home/psergey/dev-git2/10.6-vg/sql/item_subselect.cc:1808
|
#12 0x00005555562dc705 in Item_in_optimizer::val_int (this=0x7fff280d30d0) at /home/psergey/dev-git2/10.6-vg/sql/item_cmpfunc.cc:1653
|
#13 0x00005555561667e1 in Type_handler_int_result::Item_val_bool (this=0x555557b4a4e0 <type_handler_bool>, item=0x7fff280d30d0) at /home/psergey/dev-git2/10.6-vg/sql/sql_type. cc:5156
|
#14 0x0000555555d99b10 in Item::val_bool (this=0x7fff280d30d0) at /home/psergey/dev-git2/10.6-vg/sql/item.h:1701
|
#15 0x0000555555f7cd01 in Item::eval_const_cond (this=0x7fff280d30d0) at /home/psergey/dev-git2/10.6-vg/sql/item.h:1708
|
#16 0x00005555562e8b9c in Item_cond::eval_not_null_tables (this=0x7fff280d76c0, opt_arg=0x0) at /home/psergey/dev-git2/10.6-vg/sql/item_cmpfunc.cc:5162
|
#17 0x0000555555e344cf in Item_func_or_sum::walk (this=0x7fff280d76c0, processor=&virtual table offset 1016, walk_subquery=false, arg=0x0) at /home/psergey/dev-git2/10.6-vg/ sql/item.h:5523
|
#18 0x00005555562e9163 in Item_cond::walk (this=0x7fff280d76c0, processor=&virtual table offset 1016, walk_subquery=false, arg=0x0) at /home/psergey/dev-git2/10.6-vg/sql/ item_cmpfunc.cc:5315
|
#19 0x0000555555e9ddcc in st_select_lex::update_used_tables (this=0x7fff280e4320) at /home/psergey/dev-git2/10.6-vg/sql/sql_lex.cc:5452
|
#20 0x0000555555e9cf34 in st_select_lex::optimize_unflattened_subqueries (this=0x7fff28014c00, const_only=false) at /home/psergey/dev-git2/10.6-vg/sql/sql_lex.cc:5017
|
#21 0x0000555556117454 in JOIN::optimize_unflattened_subqueries (this=0x7fff280d6a50) at /home/psergey/dev-git2/10.6-vg/sql/opt_subselect.cc:5683
|
#22 0x0000555555f2a151 in JOIN::optimize_stage2 (this=0x7fff280d6a50) at /home/psergey/dev-git2/10.6-vg/sql/sql_select.cc:3408
|
#23 0x0000555555f2745a in JOIN::optimize_inner (this=0x7fff280d6a50) at /home/psergey/dev-git2/10.6-vg/sql/sql_select.cc:2590
|
#24 0x0000555555f24a6e in JOIN::optimize (this=0x7fff280d6a50) at /home/psergey/dev-git2/10.6-vg/sql/sql_select.cc:1888
|
#25 0x0000555555f30a59 in mysql_select (thd=0x7fff28000d08, tables=0x7fff28027310, fields=@0x7fff28014eb8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fff280e6760, last = 0x7fff280d3080, elements = 2}, <No data fields>}, conds=0x7fff2801b500, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7fff280e36e0, unit=0x7fff280050d8, select_lex=0x7fff28014c00) at /home/psergey/dev-git2/10.6-vg/sql/sql_select.cc:5127
|
#26 0x0000555555f72d01 in mysql_explain_union (thd=0x7fff28000d08, unit=0x7fff280050d8, result=0x7fff280e36e0) at /home/psergey/dev-git2/10.6-vg/sql/sql_select.cc:28915 #27 0x0000555555edcff9 in execute_sqlcom_select (thd=0x7fff28000d08, all_tables=0x7fff28027310) at /home/psergey/dev-git2/10.6-vg/sql/sql_parse.cc:6311
|
#28 0x0000555555ed433f in mysql_execute_command (thd=0x7fff28000d08, is_called_from_prepared_stmt=false) at /home/psergey/dev-git2/10.6-vg/sql/sql_parse.cc:3980
|
#29 0x0000555555ee2258 in mysql_parse (thd=0x7fff28000d08, rawbuf=0x7fff28014a00 "EXPLAIN SELECT * FROM t1 WHERE a > b OR a IN ( SELECT c FROM t2 WHERE EXISTS ( SELECT * FROM t3 t3a JOIN t3 t3b WHERE t3a.e < d ) )", length=147, parser_state=0x7ffff4072360) at /home/psergey/dev-git2/10.6-vg/sql/sql_parse.cc:8139
|
That is, we're somewhere here:
if (item->can_eval_in_optimize() && |
!item->with_sp_var() && !item->with_param() &&
|
!cond_has_datetime_is_null(item) && top_level())
|
{
|
=> if (item->eval_const_cond() == is_and_cond) |
So item->can_eval_in_optimize() has returned true for the subquery even if it hasn't been initialized for execution.
Notes for the changelog: EXPLAIN statement that uses a subquery which has a query plan that A) will examine less than @@expensive_subquery_limit rows and B) will use join buffer could cause a crash.
also on 10.10:
220715 12:00:29 [ERROR] mysqld got signal 11 ;Server version: 10.10.0-MariaDB-debug-logsql/signal_handler.cc:236(handle_fatal_signal)[0x5566ab844552]sigaction.c:0(__restore_rt)[0x7fd8dc582420]sql/sql_join_cache.cc:1363(JOIN_CACHE::write_record_data(unsigned char*, bool*))[0x5566ab4cc0f8]sql/sql_join_cache.cc:1571(JOIN_CACHE::put_record())[0x5566ab4ce42c]sql/sql_select.cc:21065(sub_select_cache(JOIN*, st_join_table*, bool))[0x5566ab0f9b7c]sql/sql_select.cc:21508(evaluate_join_record(JOIN*, st_join_table*, int))[0x5566ab0fbf62]sql/sql_select.cc:21278(sub_select(JOIN*, st_join_table*, bool))[0x5566ab0fa827]sql/sql_select.cc:20823(do_select(JOIN*, Procedure*))[0x5566ab0f883a]sql/sql_select.cc:4787(JOIN::exec_inner())[0x5566ab0846ea]sql/sql_select.cc:4566(JOIN::exec())[0x5566ab081bea]sql/item_subselect.cc:4147(subselect_single_select_engine::exec())[0x5566abaf7436]sql/item_subselect.cc:854(Item_subselect::exec())[0x5566abad35cb]sql/item_subselect.cc:1838(Item_exists_subselect::val_int())[0x5566abaddabd]sql/item_cmpfunc.cc:1555(Item_in_optimizer::val_int())[0x5566ab920127]sql/sql_type.cc:5100(Type_handler_int_result::Item_val_bool(Item*) const)[0x5566ab5e9400]sql/item.h:1688(Item::val_bool())[0x5566aabbfa56]sql/item.h:1695(Item::eval_const_cond())[0x5566ab143ea0]sql/item_cmpfunc.cc:4957(Item_cond::eval_not_null_tables(void*))[0x5566ab94079f]sql/item.h:5447(Item_func_or_sum::walk(bool (Item::*)(void*), bool, void*))[0x5566aac4dc9c]sql/item_cmpfunc.cc:5105(Item_cond::walk(bool (Item::*)(void*), bool, void*))[0x5566ab941816]sql/sql_lex.cc:5356(st_select_lex::update_used_tables())[0x5566aaef1c66]sql/sql_lex.cc:4920(st_select_lex::optimize_unflattened_subqueries(bool))[0x5566aaeef366]sql/opt_subselect.cc:5656(JOIN::optimize_unflattened_subqueries())[0x5566ab52cc17]sql/sql_select.cc:3324(JOIN::optimize_stage2())[0x5566ab073c76]sql/sql_select.cc:2530(JOIN::optimize_inner())[0x5566ab06bb93]sql/sql_select.cc:1846(JOIN::optimize())[0x5566ab064834]sql/sql_select.cc:5031(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5566ab085f68]sql/sql_select.cc:28007(mysql_explain_union(THD*, st_select_lex_unit*, select_result*))[0x5566ab12f010]sql/sql_parse.cc:6198(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5566aaf7a4ff]sql/sql_parse.cc:3944(mysql_execute_command(THD*, bool))[0x5566aaf69860]sql/sql_parse.cc:8036(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x5566aaf86115]sql/sql_parse.cc:1896(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x5566aaf5c0f0]sql/sql_parse.cc:1407(do_command(THD*, bool))[0x5566aaf58e71]sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x5566ab40c744]sql/sql_connect.cc:1314(handle_one_connection)[0x5566ab40bfd0]