|
Thanks!
Repeatable on 10.2-10.6:
-- source include/have_innodb.inc
|
CREATE TABLE t1 (a int) engine=innodb;
|
|
SELECT 1 IN (SELECT NULL FROM t1 WHERE a IS NOT NULL GROUP BY (SELECT NULL from dual WHERE a = 1));
|
|
10.2 768c51880a5aa6d25d4c
|
Version: '10.2.40-MariaDB-debug-log'
|
210630 8:54:33 [ERROR] mysqld got signal 11 ;
|
|
Server version: 10.2.40-MariaDB-debug-log
|
|
sql/signal_handler.cc:221(handle_fatal_signal)[0x55d6c25421b8]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12730)[0x7f1e42715730]
|
sql/item_subselect.cc:6888(Item_subselect::init_expr_cache_tracker(THD*))[0x55d6c27676a6]
|
sql/item_subselect.cc:1316(Item_singlerow_subselect::expr_cache_insert_transformer(THD*, unsigned char*))[0x55d6c27399be]
|
sql/item.cc:733(Item::transform(THD*, Item* (Item::*)(THD*, unsigned char*), unsigned char*))[0x55d6c2581917]
|
sql/sql_select.cc:3187(JOIN::setup_subquery_caches())[0x55d6c1f9aea5]
|
sql/sql_select.cc:2095(JOIN::optimize_inner())[0x55d6c1f8f4e0]
|
sql/sql_select.cc:1127(JOIN::optimize())[0x55d6c1f85318]
|
sql/sql_lex.cc:3868(st_select_lex::optimize_unflattened_subqueries(bool))[0x55d6c1eb68ac]
|
sql/opt_subselect.cc:5360(JOIN::optimize_constant_subqueries())[0x55d6c2360a78]
|
sql/sql_select.cc:1349(JOIN::optimize_inner())[0x55d6c1f874d9]
|
sql/sql_select.cc:1127(JOIN::optimize())[0x55d6c1f85318]
|
sql/sql_select.cc:3835(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55d6c1fa0766]
|
sql/sql_select.cc:361(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55d6c1f7d3fa]
|
sql/sql_parse.cc:6271(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55d6c1ef0d54]
|
sql/sql_parse.cc:3582(mysql_execute_command(THD*))[0x55d6c1edc4db]
|
sql/sql_parse.cc:7793(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55d6c1efa072]
|
sql/sql_parse.cc:1830(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55d6c1ed10bf]
|
sql/sql_parse.cc:1381(do_command(THD*))[0x55d6c1ecdaf0]
|
sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x55d6c227798e]
|
sql/sql_connect.cc:1242(handle_one_connection)[0x55d6c227724f]
|
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55d6c3718f3c]
|
nptl/pthread_create.c:487(start_thread)[0x7f1e4270afa3]
|
x86_64/clone.S:97(clone)[0x7f1e4208e4cf]
|
|
Query (0x62b000000290): SELECT 1 IN (SELECT null FROM t1 WHERE a IS NOT NULL GROUP BY (SELECT NULL from dual WHERE a = 1) )
|
|
this is also as MDEV-24925 regression after 7e9a6b7f09bfb00e781d (MDEV-24779: main.subselect fails in buildbot with --ps-protocol)
Please check the initial test case after the fix.
|
|
SELECT 1 IN (
|
SELECT NULL
|
FROM t1
|
WHERE
|
a IS NOT NULL
|
GROUP BY
|
(SELECT NULL from dual WHERE a = 1)
|
);
|
So, we crash here:
(gdb) print slave
|
$8 = (st_select_lex_node *) 0x0
|
(gdb) up
|
#1 0x0000555555e94eae in Item_subselect::init_expr_cache_tracker (this=0x7fff7c0158a0, thd=0x7fff7c000d50) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:6888
|
(gdb) wher
|
#0 st_select_lex_unit::first_select (this=0x7fff7c014db0) at /home/psergey/dev-git/10.2/sql/sql_lex.h:694
|
#1 0x0000555555e94eae in Item_subselect::init_expr_cache_tracker (this=0x7fff7c0158a0, thd=0x7fff7c000d50) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:6888
|
#2 0x0000555555e852cb in Item_singlerow_subselect::expr_cache_insert_transformer (this=0x7fff7c0158a0, tmp_thd=0x7fff7c000d50, unused=0x0) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:1315
|
#3 0x0000555555dd510c in Item::transform (this=0x7fff7c0158a0, thd=0x7fff7c000d50, transformer=&virtual Item::expr_cache_insert_transformer(THD*, unsigned char*), arg=0x0) at /home/psergey/dev-git/10.2/sql/item.cc:736
|
#4 0x0000555555b8a338 in JOIN::setup_subquery_caches (this=0x7fff7c0164c0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3188
|
#5 0x0000555555b863c2 in JOIN::optimize_inner (this=0x7fff7c0164c0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:2095
|
#6 0x0000555555b82bcc in JOIN::optimize (this=0x7fff7c0164c0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:1127
|
#7 0x0000555555b326cd in st_select_lex::optimize_unflattened_subqueries (this=0x7fff7c005098, const_only=true) at /home/psergey/dev-git/10.2/sql/sql_lex.cc:3868
|
#8 0x0000555555cf24c7 in JOIN::optimize_constant_subqueries (this=0x7fff7c015e40) at /home/psergey/dev-git/10.2/sql/opt_subselect.cc:5360
|
#9 0x0000555555b836e0 in JOIN::optimize_inner (this=0x7fff7c015e40) at /home/psergey/dev-git/10.2/sql/sql_select.cc:1349
|
#10 0x0000555555b82bcc in JOIN::optimize (this=0x7fff7c015e40) at /home/psergey/dev-git/10.2/sql/sql_select.cc:1127
|
#11 0x0000555555b8c11e in mysql_select (thd=0x7fff7c000d50, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff7c015e20, unit=0x7fff7c004948, select_lex=0x7fff7c005098) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3835
|
#12 0x0000555555b80303 in handle_select (thd=0x7fff7c000d50, lex=0x7fff7c004888, result=0x7fff7c015e20, setup_tables_done_option=0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:361
|
#13 0x0000555555b4b5d9 in execute_sqlcom_select (thd=0x7fff7c000d50, all_tables=0x7fff7c0141b0) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:6271
|
#14 0x0000555555b420cb in mysql_execute_command (thd=0x7fff7c000d50) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:3582
|
#15 0x0000555555b4f350 in mysql_parse (thd=0x7fff7c000d50, rawbuf=0x7fff7c0133c8 "SELECT 1 IN (SELECT NULL FROM t1 WHERE a IS NOT NULL GROUP BY (SELECT NULL from dual WHERE a = 1))", length=98, parser_state=0x7ffff41c0630, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:7793
|
|
on this line
Explain_node *node= qw->get_node(unit->first_select()->select_number);
|
because unit->first_select()==NULL.
It looks like this subquery's parent UNION doesn't have SELECTs in it...
unit->first_select() (i.e. st_select_lex_unit::slave) was previously assigned NULL here:
#0 0x0000555555b2fd9c in st_select_lex_node::fast_exclude (this=0x7fff7c014db0) at /home/psergey/dev-git/10.2/sql/sql_lex.cc:2282
|
#1 0x0000555555b2feda in st_select_lex_node::exclude (this=0x7fff7c014db0) at /home/psergey/dev-git/10.2/sql/sql_lex.cc:2360
|
#2 0x0000555555e82ad9 in Item_subselect::eliminate_subselect_processor (this=0x7fff7c0158a0, arg=0x0) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:371
|
#3 0x0000555555e837c9 in Item_subselect::walk (this=0x7fff7c0158a0, processor=&virtual table offset 928, walk_subquery=false, argument=0x0) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:745
|
#4 0x0000555555b80b63 in remove_redundant_subquery_clauses (subq_select_lex=0x7fff7c013598) at /home/psergey/dev-git/10.2/sql/sql_select.cc:597
|
#5 0x0000555555b81814 in JOIN::prepare (this=0x7fff7c0164c0, tables_init=0x7fff7c0141b0, wild_num=0, conds_init=0x7fff7c0148f0, og_num=1, order_init=0x0, skip_order_by=false, group_init=0x7fff7c015a60, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff7c013598, unit_arg=0x7fff7c013990) at /home/psergey/dev-git/10.2/sql/sql_select.cc:834
|
#6 0x0000555555e8e2f8 in subselect_single_select_engine::prepare (this=0x7fff7c015cb8, thd=0x7fff7c000d50) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:3781
|
#7 0x0000555555e8264b in Item_subselect::fix_fields (this=0x7fff7c015ab8, thd_param=0x7fff7c000d50, ref=0x7fff7c015d00) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:269
|
#8 0x0000555555e8d3d8 in Item_in_subselect::fix_fields (this=0x7fff7c015ab8, thd_arg=0x7fff7c000d50, ref=0x7fff7c015d00) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:3444
|
#9 0x0000555555adaa51 in setup_fields (thd=0x7fff7c000d50, ref_pointer_array=..., fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7fff7c016160, pre_fix=0x7fff7c0051d8, allow_sum_func=true) at /home/psergey/dev-git/10.2/sql/sql_base.cc:7263
|
#10 0x0000555555b81641 in JOIN::prepare (this=0x7fff7c015e40, tables_init=0x0, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff7c005098, unit_arg=0x7fff7c004948) at /home/psergey/dev-git/10.2/sql/sql_select.cc:807
|
#11 0x0000555555b8c0fc in mysql_select (thd=0x7fff7c000d50, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff7c015e20, unit=0x7fff7c004948, select_lex=0x7fff7c005098) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3829
|
|
|
that is, in the previous comment:
Stack trace #2 shows: remove_redundant_subquery_clauses() removes the GROUP BY clause from the SELECT inside the IN-subquery. This is Ok.
However, Stack trace #1 shows that we're trying to setup subquery cache. Why do we do it if we have removed the GROUP BY clause?
|
|
If we go to frame #4 of stack trace #1:
(gdb) up
#4 0x0000555555b8a338 in JOIN::setup_subquery_caches (this=0x7fff7c0164c0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3188
we are here:
List_iterator<Item> li(all_fields);
|
Item *item;
|
while ((item= li++))
|
{
|
Item *new_item=
|
item->transform(thd, &Item::expr_cache_insert_transformer,
|
NULL);
|
and the subquery is in the select list:
(gdb) p all_fields.elements
|
$190 = 2
|
(gdb) p all_fields.elem(0)
|
$192 = (Item_singlerow_subselect *) 0x7fff7c0158a0
|
(gdb) p all_fields.elem(1)
|
$194 = (Item_null *) 0x7fff7c0140e0
|
Apparently, remove_redundant_subquery_clauses() did not fully remove the subquery...
|
|
There was a similar fix for MDEV-24925:
https://github.com/MariaDB/server/commit/c72c77ca3bcb9d29903f95bf37c9930224984d29
its logic was "do not remove GROUP BY entries that use the select list".. but in this case, ord->in_field_list==false, despite that the subselect is in the select list.
|
|
The subselect is added into the select list here:
#0 base_list::push_front_impl (this=0x7fff7c0167e0, node=0x7fff7c017290) at /home/psergey/dev-git/10.2/sql/sql_list.h:248
|
#1 0x0000555555a9d913 in base_list::push_front (this=0x7fff7c0167e0, info=0x7fff7c0158a0, mem_root=0x7fff7c005ed8) at /home/psergey/dev-git/10.2/sql/sql_list.h:255
|
#2 0x0000555555bd1549 in List<Item>::push_front (this=0x7fff7c0167e0, a=0x7fff7c0158a0, mem_root=0x7fff7c005ed8) at /home/psergey/dev-git/10.2/sql/sql_list.h:547
|
#3 0x0000555555bbbecb in find_order_in_list (thd=0x7fff7c000d50, ref_pointer_array=..., tables=0x7fff7c0141b0, order=0x7fff7c015a60, fields=..., all_fields=..., is_group_field=true, add_to_all_fields=true, from_window_spec=false) at /home/psergey/dev-git/10.2/sql/sql_select.cc:22708
|
#4 0x0000555555bbc24d in setup_group (thd=0x7fff7c000d50, ref_pointer_array=..., tables=0x7fff7c0141b0, fields=..., all_fields=..., order=0x7fff7c015a60, hidden_group_fields=0x7fff7c0167a7, from_window_spec=false) at /home/psergey/dev-git/10.2/sql/sql_select.cc:22820
|
#5 0x0000555555b80ea1 in setup_without_group (thd=0x7fff7c000d50, ref_pointer_array=..., tables=0x7fff7c0141b0, leaves=..., fields=..., all_fields=..., conds=0x7fff7c0168c8, order=0x0, group=0x7fff7c015a60, win_specs=..., win_funcs=..., hidden_group_fields=0x7fff7c0167a7, reserved=0x7fff7c01385c) at /home/psergey/dev-git/10.2/sql/sql_select.cc:669
|
#6 0x0000555555b81774 in JOIN::prepare (this=0x7fff7c0164c0, tables_init=0x7fff7c0141b0, wild_num=0, conds_init=0x7fff7c0148f0, og_num=1, order_init=0x0, skip_order_by=false, group_init=0x7fff7c015a60, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff7c013598, unit_arg=0x7fff7c013990) at /home/psergey/dev-git/10.2/sql/sql_select.cc:812
|
#7 0x0000555555e8e2f8 in subselect_single_select_engine::prepare (this=0x7fff7c015cb8, thd=0x7fff7c000d50) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:3781
|
#8 0x0000555555e8264b in Item_subselect::fix_fields (this=0x7fff7c015ab8, thd_param=0x7fff7c000d50, ref=0x7fff7c015d00) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:269
|
#9 0x0000555555e8d3d8 in Item_in_subselect::fix_fields (this=0x7fff7c015ab8, thd_arg=0x7fff7c000d50, ref=0x7fff7c015d00) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:3444
|
#10 0x0000555555adaa51 in setup_fields (thd=0x7fff7c000d50, ref_pointer_array=..., fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7fff7c016160, pre_fix=0x7fff7c0051d8, allow_sum_func=true) at /home/psergey/dev-git/10.2/sql/sql_base.cc:7263
|
#11 0x0000555555b81641 in JOIN::prepare (this=0x7fff7c015e40, tables_init=0x0, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff7c005098, unit_arg=0x7fff7c004948) at /home/psergey/dev-git/10.2/sql/sql_select.cc:807
|
#12 0x0000555555b8c0fc in mysql_select (thd=0x7fff7c000d50, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff7c015e20, unit=0x7fff7c004948, select_lex=0x7fff7c005098) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3829
|
#13 0x0000555555b80303 in handle_select (thd=0x7fff7c000d50, lex=0x7fff7c004888, result=0x7fff7c015e20, setup_tables_done_option=0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:361
|
#14 0x0000555555b4b5d9 in execute_sqlcom_select (thd=0x7fff7c000d50, all_tables=0x7fff7c0141b0) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:6271
|
#15 0x0000555555b420cb in mysql_execute_command (thd=0x7fff7c000d50) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:3582
|
The code in find_order_in_list() seems to have left order->in_field_list=false on purpose...
The primary usage of order->in_field_list is in create_distinct_group, it doesn't look like we can set it to true for items that were not originally in the select list.
|
|
Need to discuss this.
|
|
./mtr main.subselect4 --view
|
|
10.2 fb0b28932ce82903f2fcfb6
|
210715 14:04:02 [ERROR] mysqld got signal 11 ;
|
|
Server version: 10.2.40-MariaDB-debug-log
|
|
sql/signal_handler.cc:221(handle_fatal_signal)[0x55bdc217768e]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12730)[0x7fba7c7f8730]
|
sql/item_subselect.cc:6888(Item_subselect::init_expr_cache_tracker(THD*))[0x55bdc239cb7c]
|
sql/item_subselect.cc:1316(Item_singlerow_subselect::expr_cache_insert_transformer(THD*, unsigned char*))[0x55bdc236ee94]
|
sql/item.cc:733(Item::transform(THD*, Item* (Item::*)(THD*, unsigned char*), unsigned char*))[0x55bdc21b6ded]
|
sql/sql_select.cc:3187(JOIN::setup_subquery_caches())[0x55bdc1bcf379]
|
sql/sql_select.cc:2095(JOIN::optimize_inner())[0x55bdc1bc39b4]
|
sql/sql_select.cc:1127(JOIN::optimize())[0x55bdc1bb97ec]
|
sql/sql_lex.cc:3868(st_select_lex::optimize_unflattened_subqueries(bool))[0x55bdc1aeadf0]
|
sql/opt_subselect.cc:5360(JOIN::optimize_constant_subqueries())[0x55bdc1f95f3c]
|
sql/sql_select.cc:1349(JOIN::optimize_inner())[0x55bdc1bbb9ad]
|
sql/sql_select.cc:1127(JOIN::optimize())[0x55bdc1bb97ec]
|
sql/sql_derived.cc:920(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0x55bdc1a91644]
|
sql/sql_derived.cc:192(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x55bdc1a8cd26]
|
sql/sql_select.cc:1413(JOIN::optimize_inner())[0x55bdc1bbc8e6]
|
sql/sql_select.cc:1127(JOIN::optimize())[0x55bdc1bb97ec]
|
sql/sql_select.cc:3835(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55bdc1bd4c3a]
|
sql/sql_select.cc:361(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55bdc1bb18ce]
|
sql/sql_parse.cc:6271(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55bdc1b25228]
|
sql/sql_parse.cc:3582(mysql_execute_command(THD*))[0x55bdc1b109af]
|
sql/sql_parse.cc:7793(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55bdc1b2e546]
|
sql/sql_parse.cc:1830(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55bdc1b05593]
|
sql/sql_parse.cc:1381(do_command(THD*))[0x55bdc1b01fc4]
|
sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x55bdc1eace5c]
|
sql/sql_connect.cc:1242(handle_one_connection)[0x55bdc1eac71d]
|
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55bdc334e63c]
|
nptl/pthread_create.c:487(start_thread)[0x7fba7c7edfa3]
|
x86_64/clone.S:97(clone)[0x7fba7c1714cf]
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x62b000000290): SELECT * FROM mysqltest_tmp_v
|
|
|
test case from MDEV-26164:
-- source include/have_innodb.inc
|
create table t(a int) engine=innodb;
|
|
select 1 from t where not exists
|
(
|
select 1 from t where binary current_time()
|
group by (select a),(select 1)
|
);
|
|
|
Check out Item_subselect::set_fake_select_as_master_processor(). It has this:
An observation: there exists code that "un-eliminates" an item: see Item_subselect::set_fake_select_as_master_processor
/*
|
Undo Item_subselect::eliminate_subselect_processor because at that phase
|
we don't know yet that the ORDER clause will be moved to the fake select.
|
*/
|
unit->item= this;
|
eliminated= FALSE;
|
|
|
A patch: https://github.com/MariaDB/server/commit/c01ee954bf3b10fef85af7b8c77d319ff7bd6b61
|
|
Note that this patch doesn't fix MDEV-27957. for that MDEV, a crash becomes a memory leak (one can see it reported by e.g. my_malloc)
|
|
sanja please review
|
|
Note: for unused fields in select lists, we had intent to fix this in the future versions: MDEV-27201.
|
|
OK to push
|