Igor Babaev (Inactive) added a comment - 2022-04-18 20:05 - edited Let's look at the query: select id from t1 join ( select dt2.x1, ( select sum (a1) from t2 where t2.x1 = dt2.x1 ) m from ( select x1 from t2 u where x3 = 1 ) dt2 ) dt on t1.id = dt.x1 where t1.id2 < dt.m; Both derived tables of this query dt2 and dt are mergeable. First dt2 is merged into dt and then dt is merged into the main query. The merges are performed at the first execution of the query in the function JOIN::optimize. After these merges the query looks like this: (gdb) p dbug_print_select(select_lex) $11 = 0x555557158c40 <dbug_item_print_buf> "select t1.`id` AS `id` from test.t1 join test.t2 u where t1.id2 < (subquery#3) and t1.`id` = u.x1 and u.x3 = 1" where subquery#3 looks like this: (gdb) p dbug_print_select(select_lex) $25 = 0x555557158c40 <dbug_item_print_buf> "select sum(t2.a1) from test.t2 where t2.x1 = u.x1" Note that we have two references to the field u.x1: one in the subquery (t2.x1 = u.x1), the other in in the main query (t1.`id` = u.x1). For the first reference we have (gdb) p right_item->type() == Item::REF_ITEM && ((Item_ref*)right_item)->ref_type() == Item_ref::VIEW_REF $32 = true (gdb) p ((Item_direct_view_ref*)right_item)->orig_table_name $33 = 0x7fff840394a0 "dt2" (gdb) p ((Item_ref*)right_item)->get_depended_from() $35 = (st_select_lex *) 0x7fff84033530 (gdb) p (*((Item_direct_view_ref*)right_item)->ref)->type() $34 = Item::FIELD_ITEM (gdb) p *((Item_direct_view_ref*)right_item)->ref $50 = (Item *) 0x7fff84033990 For the second reference we have: (gdb) p right_item->type() == Item::REF_ITEM && ((Item_ref*)right_item)->ref_type() == Item_ref::VIEW_REF $39 = true (gdb) p ((Item_direct_view_ref*)right_item)->orig_table_name $41 = 0x7fff84034e10 "dt" (gdb) p ((Item_ref*)right_item)->get_depended_from() $51 = (st_select_lex *) 0x0 p (*((Item_direct_view_ref*)right_item)->ref)->type() $42 = Item::REF_ITEM (gdb) p ((Item_ref *)(*((Item_direct_view_ref*)right_item)->ref))->ref_type() $43 = Item_ref::VIEW_REF (gdb) p ((Item_direct_view_ref *)(*((Item_direct_view_ref*)right_item)->ref))->orig_table_name $45 = 0x7fff840346a0 "dt2" (gdb) p ((Item_direct_view_ref *)(*((Item_direct_view_ref*)right_item)->ref))->get_depended_from() $53 = (st_select_lex *) 0x0 (gdb) p ((Item_direct_view_ref *)(*((Item_direct_view_ref*)right_item)->ref))->get_depended_from() $53 = (st_select_lex *) 0x0 ((gdb) p (*((Item_direct_view_ref *)(*((Item_direct_view_ref*)right_item)->ref))->ref)->type() $46 = Item::FIELD_ITEM (gdb) p *((Item_direct_view_ref *)(*((Item_direct_view_ref*)right_item)->ref))->ref $47 = (Item *) 0x7fff84033990 We see that both references ultimately refer to the same Item_field and this is correct because the field belongs to the same instance of dt. We also see that the first reference has one Item_direct_view_ref wrapper whose depend_from field is not NULL, while the second reference has two Item_direct_view_ref wrappers and for both of them depend_from is NULL. It is important to know how Item_direct_view_ref wrappers appear in the query tree and in what memory they are allocated. When JOIN::prepare() starts it first calls mysql_derived_prepare() for the used derived tables. The derived tables are processed by mysql_derived_prepare() starting with most inner. So first mysql_derived_prepare() is called for dt2. It resolves all field references with JOIN::prepare() invoked for dt2. It can do it as any derived table cannot contain any outer references in our current implementation. After this mysql_derived_prepare() creates the field translation for dt2. A field translation contains pairs of column names of the derived table and the corresponding expressions for the columns taken from the select list of the specification of the derived table. The derived table dt2 contains only one column named 'x1'. The corresponding expression is (gdb) p dbug_print_item(item) $181 = 0x555557158c40 <dbug_item_print_buf> "u.x1" Note that field translations are allocated in the statement memory. Done with mysql_derived_prepare() for dt2 we move to mysql_derived_prepare() for dt. JOIN::prepare() is called for (gdb) p dbug_print_select(select_lex) $184 = 0x555557158c40 <dbug_item_print_buf> "select dt2.x1 AS x1,(subquery#3) AS m from (select u.x1 AS x1 from test.t2 u where u.x3 = 1) dt2" JOIN::prepare() for dt calls setup_fields() that resolves field references in the select list. It starts from name resolution for (gdb) p dbug_print_item(item) $185 = 0x555557158c40 <dbug_item_print_buf> "dt2.x1" The function fix_fields is called for this reference. This is a reference to a column of the derived table dt2. The pair with column name 'x1' is found in the field translator for dt2 and the corresponding expression is taken from this pair: (gdb) p dbug_print_item(ptr->item) $187 = 0x555557158c40 <dbug_item_print_buf> "u.x1" create_view_field() is called for u.x1. Here we have: (gdb) p dbug_print_select(thd->lex->current_select) $188 = 0x555557158c40 <dbug_item_print_buf> "select dt2.x1 AS x1,(subquery#3) AS m from (select u.x1 AS x1 from test.t2 u where u.x3 = 1) dt2" (gdb) p dbug_print_select(&thd->lex->select_lex) $189 = 0x555557158c40 <dbug_item_print_buf> "select `id` AS `id` from (test.t1 join (select dt2.x1 AS x1,(subquery#3) AS m from (select u.x1 AS x1 from test.t2 u where u.x3 = 1) dt2) dt on(t1.`id` = dt.x1)) where t1.id2 < dt.m" (gdb) p save_wrapper $190 = false thd->lex->current_select->no_wrap_view_item is set to TRUE. fix_fields() is not called for u.x1 as it has been already fixed thd->lex->current_select->no_wrap_view_item is reset to save_wrapper (that is false) and the item u.x1 is wrapped into an Item_direct_view_ref wrapper for dt2. As this is the first execution the wrapper is allocated in the statement memory. Note that after we return from create_view_field() the expression for x1 in the select list of of dt specification is replaced with a Item_direct_view_ref reference item to u.x1. Now we come to the call of fix_fields() for the second element of the select lex named m. This is the subquery (gdb) p dbug_print_item(item) $201 = 0x555557158c40 <dbug_item_print_buf> "(subquery#3)" JOIN::prepare() is called for this subquery whose specification looks like this $203 = 0x555557158c40 <dbug_item_print_buf> "select sum(a1) from test.t2 where t2.x1 = dt2.x1" setup_conds() is called for the where condition "t2.x1 = dt2.x1". fix_fields() is called for dt2.x1 to resolve this field reference. Item_field::fix_outer_field() is called for this field reference invoking find_field_in_view() for it. The view is dt2. create_view_field() is called for the item (gdb) p dbug_print_item(*field_ref) $205 = 0x555557158c40 <dbug_item_print_buf> "u.x1" (gdb) p (*field_ref)->type() $206 = Item::FIELD_ITEM Here we have: (gdb) p dbug_print_select(thd->lex->current_select) $208 = 0x555557158c40 <dbug_item_print_buf> "select sum(t2.a1) from test.t2 where t2.x1 = dt2.x1" (gdb) p dbug_print_select(&thd->lex->select_lex) $209 = 0x555557158c40 <dbug_item_print_buf> "select `id` AS `id` from (test.t1 join (select u.x1 AS x1,(subquery#3) AS m from (select u.x1 AS x1 from test.t2 u where u.x3 = 1) dt2) dt on(t1.`id` = dt.x1)) where t1.id2 < dt.m" (gdb) p save_wrapper $210 = false fix_fields() is not called for dt2.x1 as this item is already fixed. As save_wrapper == false the item is wrapped into a Item_direct_view_ref wrapper. The wrapper is allocated in the statement memory. Within the above mentioned call of Item_field::fix_outer_field() the function mark_as_dependent() is called with the following item for the parameter mark_item: (gdb) p dbug_print_item(mark_item) $334 = 0x555557158c40 <dbug_item_print_buf> "u.x1" (gdb) p mark_item->type() $335 = Item::REF_ITEM (gdb) p (*((Item_direct_view_ref*)mark_item)->ref)->type() $337 = Item::FIELD_ITEM (gdb) p mark_item->can_be_depended $338 = true The function sets dependency of mark_item from the select $339 = 0x555557158c40 <dbug_item_print_buf> "select u.x1 AS x1,(subquery#3) AS m from (select u.x1 AS x1 from test.t2 u where u.x3 = 1) dt2" TABLE_LIST::create_field_translation() is called for the derived table dt. It adds two elements: for column x1 with item u.x1 wrapped into Item_direct_view_ref and for column m with item for (subquery#3). The field translations are allocated in the statement memory

Igor Babaev (Inactive) added a comment - 2022-04-19 06:54 - edited Let's come to the second execution of the query select id from t1 join ( select dt2.x1, ( select sum (a1) from t2 where t2.x1 = dt2.x1 ) m from ( select x1 from t2 u where x3 = 1 ) dt2 ) dt on t1.id = dt.x1 where t1.id2 < dt.m; When JOIN::prepare() starts for the query it looks like: (gdb) p dbug_print_select(select_lex) $129 = 0x555557158c40 <dbug_item_print_buf> "select `id` AS `id` from test.t1 join test.t2 u where t1.id2 < dt.m and t1.`id` = dt.x1 and x3 = 1" At the moment there are no Item_direct_ref_wrappers in the tree representation for this query except those reflected in field translations. They were removed at the end of the previous execution. JOIN::prepare is supposed to restore them. It is supposed to be done by calling the function create_view_field() when references to the fields of the query is resolved. Let's come to the call of create_view_field() for dt.m. Here we have (gdb) p dbug_print_select(thd->lex->current_select) $130 = 0x555557158c40 <dbug_item_print_buf> "select t1.`id` AS `id` from test.t1 join test.t2 u where t1.id2 < dt.m and t1.`id` = dt.x1 and x3 = 1" (gdb) p dbug_print_select(&thd->lex->select_lex) $131 = 0x555557158c40 <dbug_item_print_buf> "select t1.`id` AS `id` from test.t1 join test.t2 u where t1.id2 < dt.m and t1.`id` = dt.x1 and x3 = 1" (gdb) p save_wrapper $132 = false Then the code thd->lex->current_select->no_wrap_view_item= TRUE; is executed. The code actually sets thd->lex->select_lex.no_wrap_view_item to TRUE. After this fix_fields() is called for (gdb) p dbug_print_item(field) $133 = 0x555557158c40 <dbug_item_print_buf> "(subquery#3)" and we come to JOIN::prepare() for (gdb) p dbug_print_select(select_lex) $134 = 0x555557158c40 <dbug_item_print_buf> "select sum(a1) from test.t2 where t2.x1 = dt2.x1" Name resolution for dt2.x1 brings us to the call of find_field_in_view() that invokes indirectly create_view_field() for this field. Here we have (gdb) p dbug_print_select(thd->lex->current_select) $135 = 0x555557158c40 <dbug_item_print_buf> "select sum(t2.a1) from test.t2 where t2.x1 = dt2.x1" (gdb) p dbug_print_select(&thd->lex->select_lex) $136 = 0x555557158c40 <dbug_item_print_buf> "select t1.`id` AS `id` from test.t1 join test.t2 u where t1.id2 < dt.m and t1.`id` = dt.x1 and x3 = 1" (gdb) p save_wrapper $137 = true After thd->lex->current_select->no_wrap_view_item is set to TRUE fix_fields() is called for the item (gdb) p dbug_print_item(this) $351 = 0x555557158c40 <dbug_item_print_buf> "x1" (gdb) p this->type() $352 = Item::FIELD_ITEM The call returns the item (gdb) p dbug_print_item(*field_ref) $139 = 0x555557158c40 <dbug_item_print_buf> "u.x1" as the out parameter. After thd->lex->current_select->no_wrap_view_item is set to save_wrapper that is true and this item is returned as the result of the create_view_field() call. Note that the item is not wrapped into a Item_direct_view_ref wrapper (gdb) p field->type() $140 = Item::FIELD_ITEM Also note that thd->lex->select_lex.no_wrap_view_item remains true. (gdb) p thd->lex->select_lex.no_wrap_view_item $141 = true Yet in the the call of create_view_field() for dt2.m it is reset to false. Item_field::fix_outer_field() is called for the item (gdb) p dbug_print_item(this) $359 = 0x555557158c40 <dbug_item_print_buf> "dt2.x1" (gdb) p this->type() $360 = Item::FIELD_ITEM . This function invokes mark_as_dependent() with the following value of the parameter mark_item (gdb) p dbug_print_item(mark_item) $361 = 0x555557158c40 <dbug_item_print_buf> "u.x1" (gdb) p mark_item->type() $362 = Item::FIELD_ITEM As mark_item->can_be_depended was set to false at the very end of the first execution the item is not marked as dependent. As a result in JOIN::optimize() for (gdb) p dbug_print_select(select_lex) $364 = 0x555557158c40 <dbug_item_print_buf> "select sum(t2.a1) from test.t2 where t2.x1 = u.x1" we have (gdb) p dbug_print_item(right_item) $365 = 0x555557158c40 <dbug_item_print_buf> "u.x1" (gdb) p/x right_item->used_tables() $366 = 0x2 This is of course not correct as the select has only one table. This brings us to the reported crash.

Igor Babaev (Inactive) added a comment - 2022-04-21 06:15 The problem appears because the Item_direct_view_ref wrapper is missing for the outer reference dt2.x1 at the second execution. It happens due to the following mistake in the legacy code of the function create_view_field() Item *create_view_field(THD *thd, TABLE_LIST *view, Item **field_ref, const char *name) { - bool save_wrapper= thd->lex->select_lex.no_wrap_view_item; + bool save_wrapper= thd->lex->current_select->no_wrap_view_item; Item *field= *field_ref; DBUG_ENTER("create_view_field");

Oleksandr Byelkin added a comment - 2022-04-22 10:30 OK to push

Igor Babaev (Inactive) added a comment - 2022-04-25 22:28 A fix for this bug was pushed into 10.2. It has to be merged upstream as it is.