[MDEV-27212] Crash in Item_equal::sort on second execution of stored procedure Created: 2021-12-09  Updated: 2022-07-01  Resolved: 2022-04-25

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.2.41, 10.5.13, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.2.44, 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4

Type: Bug Priority: Critical
Reporter: Valerii Kravchuk Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-27884 [ERROR] mysqld got signal 11 Closed

 Description   

The following crash happens on a second call to the stored procedure:

211202 20:18:42 [ERROR] mysqld got signal 11 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed,
 
something is definitely wrong and this may fail.
 
 
 
Server version: 10.2.41-MariaDB-log
 
key_buffer_size=33554432
 
read_buffer_size=131072
 
max_used_connections=302
 
max_threads=31002
 
thread_count=313
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68155039 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7f8b54067ad8
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7f8711915d30 thread_stack 0x30000
 
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x561d8c792e8e]
 
/usr/sbin/mysqld(handle_fatal_signal+0x30d)[0x561d8c22af3d]
 
/lib64/libpthread.so.0(+0xf630)[0x7f8bac326630]
 
/usr/sbin/mysqld(+0x53a6c6)[0x561d8c0b96c6]
 
/usr/sbin/mysqld(_ZN10Item_equal4sortEPFiP4ItemS1_PvES2_+0x7d)[0x561d8c26df1d]
 
/usr/sbin/mysqld(+0x54d184)[0x561d8c0cc184]
 
/usr/sbin/mysqld(_ZN4JOIN14optimize_innerEv+0x111b)[0x561d8c0e733b]
 
/usr/sbin/mysqld(_ZN4JOIN8optimizeEv+0x56)[0x561d8c0e9066]
 
/usr/sbin/mysqld(_ZN13st_select_lex31optimize_unflattened_subqueriesEb+0xd8)[0x561d8c0887f8]
 
/usr/sbin/mysqld(_ZN4JOIN14optimize_innerEv+0x1f37)[0x561d8c0e8157]
 
/usr/sbin/mysqld(_ZN4JOIN8optimizeEv+0x56)[0x561d8c0e9066]
 
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x94)[0x561d8c0ea944]
 
/usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x26c)[0x561d8c0eb55c]
 
/usr/sbin/mysqld(+0x43b641)[0x561d8bfba641]
 
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5e04)[0x561d8c097cd4]
 
/usr/sbin/mysqld(_Z17mysql_open_cursorP3THDP13select_resultPP18Server_side_cursor+0x187)[0x561d8c068f97]
 
/usr/sbin/mysqld(_ZN9sp_cursor4openEP3THD+0x1e)[0x561d8c022c9e]
 
/usr/sbin/mysqld(_ZN14sp_instr_copen9exec_coreEP3THDPj+0x27)[0x561d8c017b27]
 
/usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0xab)[0x561d8c01dd5b]
 
/usr/sbin/mysqld(_ZN14sp_instr_copen7executeEP3THDPj+0x48)[0x561d8c01e0f8]
 
/usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0x7d0)[0x561d8c01ab50]
 
/usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x5ef)[0x561d8c01c2ff]
 
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1e65)[0x561d8c093d35]
 
/usr/sbin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x36)[0x561d8c017996]
 
/usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0xab)[0x561d8c01dd5b]
 
/usr/sbin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x205)[0x561d8c01e335]
 
/usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0x7d0)[0x561d8c01ab50]
 
/usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x5ef)[0x561d8c01c2ff]
 
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1e65)[0x561d8c093d35]
 
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x34a)[0x561d8c09a74a]
 
/usr/sbin/mysqld(+0x43b83b)[0x561d8bfba83b]
 
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x2e2f)[0x561d8c09ddaf]
 
/usr/sbin/mysqld(_Z10do_commandP3THD+0x11a)[0x561d8c09e42a]
 
/usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x1d6)[0x561d8c16a346]
 
/usr/sbin/mysqld(handle_one_connection+0x3d)[0x561d8c16a45d]
 
pthread_create.c:0(start_thread)[0x7f8bac31eea5]
 
/lib64/libc.so.6(clone+0x6d)[0x7f8baa6beb0d]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7f8b5437bed0): CALL SP( NAME_CONST(...),  NAME_CONST(...), ...)
 
 
 
Connection ID (thread ID): 94
 
Status: NOT_KILLED



 Comments   
Comment by Sergei Golubchik [ 2022-01-17 ]

valerii, is there any way we can reproduce the bug? unfortunately, a stack trace alone isn't enough to understand what's wrong and fix it.

Comment by Alice Sherepa [ 2022-01-18 ]

I repeated on 10.2-10.7
Please check the initial test case after fixing the bug- because this one might be oversimplified.

--source include/have_innodb.inc
 
 
CREATE TABLE t1 ( id int, id2 int ) engine=innodb;
 
CREATE TABLE t2 ( x3 int , x1 int , x2 int, a1 int) engine=innodb;
 
 
 
create procedure sp1(a int, b int)
 
SELECT id
 
FROM t1 
JOIN
 
 ( SELECT dt2.x1, ifnull(( SELECT sum(a1) FROM t2  WHERE t2.x1 = dt2.x1), 0 ) m
 
  FROM ( SELECT x1, x2 FROM t2 u WHERE  x3 = a ) dt2 ) dt 
 ON t1.id = dt.x1
 
WHERE t1.id2 < m + b;
 
 
 
call sp1(163386,22);
 
call sp1(163386,22);

10.2 746050d02d6b927c597

 
220118 18:01:20 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.2.42-MariaDB-debug-log
 
 
 
sigaction.c:0(__restore_rt)[0x7efc352543c0]
 
sql/sql_select.cc:13934(compare_fields_by_table_order(Item*, Item*, void*))[0x5590f2efd67d]
 
sql/sql_list.h:639(void bubble_sort<Item>(List<Item>*, int (*)(Item*, Item*, void*), void*))[0x5590f352d81f]
 
sql/item_cmpfunc.cc:6670(Item_equal::sort(int (*)(Item*, Item*, void*), void*))[0x5590f351d5ca]
 
sql/sql_select.cc:14408(substitute_for_best_equal_field(THD*, st_join_table*, Item*, COND_EQUAL*, void*))[0x5590f2effbbe]
 
sql/sql_select.cc:1686(JOIN::optimize_inner())[0x5590f2ea2482]
 
sql/sql_select.cc:1127(JOIN::optimize())[0x5590f2e9c6ce]
 
sql/sql_lex.cc:3868(st_select_lex::optimize_unflattened_subqueries(bool))[0x5590f2dd304c]
 
sql/opt_subselect.cc:5327(JOIN::optimize_unflattened_subqueries())[0x5590f325be9f]
 
sql/sql_select.cc:2089(JOIN::optimize_inner())[0x5590f2ea6739]
 
sql/sql_select.cc:1127(JOIN::optimize())[0x5590f2e9c6ce]
 
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*))[0x5590f2eb7dd5]
 
sql/sql_select.cc:361(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5590f2e9499c]
 
sql/sql_parse.cc:6271(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5590f2e0b001]
 
sql/sql_parse.cc:3582(mysql_execute_command(THD*))[0x5590f2df838a]
 
sql/sp_head.cc:3337(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x5590f2c446aa]
 
sql/sp_head.cc:3099(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x5590f2c43176]
 
sql/sp_head.cc:3253(sp_instr_stmt::execute(THD*, unsigned int*))[0x5590f2c43f1e]
 
sql/sp_head.cc:1326(sp_head::execute(THD*, bool))[0x5590f2c38250]
 
sql/sp_head.cc:2202(sp_head::execute_procedure(THD*, List<Item>*))[0x5590f2c3ca1b]
 
sql/sql_parse.cc:2981(do_execute_sp(THD*, sp_head*))[0x5590f2df40c1]
 
sql/sql_parse.cc:5622(mysql_execute_command(THD*))[0x5590f2e062de]
 
sql/sql_parse.cc:7793(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5590f2e14516]
 
sql/sql_parse.cc:1830(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5590f2ded71b]
 
sql/sql_parse.cc:1381(do_command(THD*))[0x5590f2dea4e6]
 
sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x5590f31769a6]
 
sql/sql_connect.cc:1242(handle_one_connection)[0x5590f3176269]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x5590f4517198]
 
nptl/pthread_create.c:478(start_thread)[0x7efc35248609]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7efc34e21293]
 
 
 
Query (0x62500014cab8): SELECT id
 
FROM t1 
JOIN
 
( SELECT dt2.x1, ifnull(( SELECT sum(a1) FROM t2  WHERE t2.x1 = dt2.x1), 0 ) m
 
FROM ( SELECT x1, x2 FROM t2 u WHERE  x3 =  NAME_CONST('a',163386) ) dt2 ) dt 
ON t1.id = dt.x1
 
WHERE t1.id2 < m +  NAME_CONST('b',22)

Comment by Igor Babaev [ 2022-03-30 ]

The problem can be reproduced with a simpler SP

create procedure sp1() 
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;

Comment by Igor Babaev [ 2022-03-30 ]

The problem can be reproduced with the following prepared statement when it is executed for the second time:

prepare stmt from "
 
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;
 
";

Comment by Igor Babaev [ 2022-03-30 ]

The problem can be reproduced when using views v2, v instead of derived tables dt2, dt and stored procedure sp2() using these views:

create view v2 as select x1 from t2 u where  x3 = 1;
 
create view v as 
  select v2.x1, ( select sum(a1) from t2 where t2.x1 = v2.x1 ) m from v2; 
 
 
create procedure sp2()
 
select id  from t1  join  v on t1.id = v.x1 where t1.id2 < v.m;

Comment by Igor Babaev [ 2022-03-30 ]

The problem can be reproduced with MyISAM tables if we fill them with rows:

create table t1 ( id int, id2 int ) engine=myisam;
 
create table t2 ( x3 int , x1 int , x2 int, a1 int) engine=myisam;
 
insert into t1 values (3, 2), (4, 2), (3, 4);
 
insert into t2 values (1, 2, 2, 1), (1, 3, 3, 2), (2, 3, 3, 1);

Comment by Igor Babaev [ 2022-04-18 ]

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

Comment by Igor Babaev [ 2022-04-19 ]

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.

Comment by Igor Babaev [ 2022-04-21 ]

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");

Comment by Oleksandr Byelkin [ 2022-04-22 ]

OK to push

Comment by Igor Babaev [ 2022-04-25 ]

A fix for this bug was pushed into 10.2. It has to be merged upstream as it is.

Generated at Thu Feb 08 09:51:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.