[MDEV-25086] Stored Procedure Crashes Server Created: 2021-03-08  Updated: 2022-04-13  Resolved: 2022-01-11

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.5.9, 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.2.42, 10.3.33, 10.4.23, 10.5.14, 10.6.6, 10.7.2

Type: Bug Priority: Critical
Reporter: Maria M Pflaum Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None
Environment:

Red Hat 8


 Description   

The following procedure crashes the server if it is called twice, even with no data, it succeeds if you add a limit:

CREATE TABLE `TEST` (
 
  `objectid` int(11) NOT NULL,
 
  `submissionid` int(11) DEFAULT NULL,
 
  `objectname` varchar(255) DEFAULT NULL,
 
  `objecturl` varchar(1000) DEFAULT NULL,
 
  `reviewercomment` varchar(1000) DEFAULT NULL,
 
  `isactive` tinyint(1) DEFAULT NULL,
 
  `createdon` datetime DEFAULT NULL,
 
  `createdby` varchar(255) DEFAULT NULL,
 
  `updatedon` datetime DEFAULT NULL,
 
  `updatedby` varchar(255) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
MariaDB [mytest]> DELIMITER $$
 
MariaDB [mytest]> CREATE OR REPLACE PROCEDURE `testsp_doesnot_works`()
 
     BEGIN
 
     SET SESSION group_concat_max_len = 100000000000;
 
     
     SELECT JSON_EXTRACT(
 
       IFNULL(
 
         (
 
           SELECT CONCAT(
 
             '[',
 
             GROUP_CONCAT(
 
               JSON_OBJECT(
 
                 'objectId', objectId,
 
                 'objectName', objectName,'objectUrl', objectUrl,'reviewerComment', reviewerComment,'createdOn', DATE_FORMAT(createdOn,"%Y-%m-%dT%H:%i:%sZ")
 
               )
 
             ),
 
             ']'
 
           )     
         ),
 
         '[]'
 
       ),
 
       '$'
 
     ) AS comments
 
     FROM(select objectId, objectName, objectUrl, reviewerComment, createdOn FROM `TEST`) AS body;
 
     END$$
 
Query OK, 0 rows affected (0.004 sec)
 
 
 
MariaDB [mytest]> DELIMITER ;

It succeeds if you replace:

 FROM(select objectId, objectName, objectUrl, reviewerComment, createdOn FROM `TEST`) AS body;
 
 
 
with 
 
 
{code:sql}
 
   FROM(select objectId, objectName, objectUrl, reviewerComment, createdOn FROM `TEST` LIMIT 50 ) AS body;



 Comments   
Comment by Elena Stepanova [ 2021-03-08 ]

Reproducible as described.
Here is an MTR edition of the very same test case from the description:

--source include/have_innodb.inc
 
 
 
CREATE TABLE `TEST` (
 
  `objectid` int(11) NOT NULL,
 
  `submissionid` int(11) DEFAULT NULL,
 
  `objectname` varchar(255) DEFAULT NULL,
 
  `objecturl` varchar(1000) DEFAULT NULL,
 
  `reviewercomment` varchar(1000) DEFAULT NULL,
 
  `isactive` tinyint(1) DEFAULT NULL,
 
  `createdon` datetime DEFAULT NULL,
 
  `createdby` varchar(255) DEFAULT NULL,
 
  `updatedon` datetime DEFAULT NULL,
 
  `updatedby` varchar(255) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
--delimiter $$
 
CREATE OR REPLACE PROCEDURE `testsp_doesnot_works`()
 
BEGIN
 
  SET SESSION group_concat_max_len = 100000000000;
 
 
 
  SELECT JSON_EXTRACT(
 
  IFNULL(
 
  (
 
  SELECT CONCAT(
 
  '[',
 
  GROUP_CONCAT(
 
  JSON_OBJECT(
 
  'objectId', objectId,
 
  'objectName', objectName,'objectUrl', objectUrl,'reviewerComment', reviewerComment,'createdOn', DATE_FORMAT(createdOn,"%Y-%m-%dT%H:%i:%sZ")
 
  )
 
  ),
 
  ']'
 
  )
 
  ),
 
  '[]'
 
  ),
 
  '$'
 
  ) AS comments
 
  FROM(select objectId, objectName, objectUrl, reviewerComment, createdOn FROM `TEST`) AS body;
 
END $$
 
--delimiter ;
 
 
 
CALL testsp_doesnot_works();
 
CALL testsp_doesnot_works();

10.2 545cba13

 
#3  <signal handler called>
 
#4  0x00005645bc0b362c in THD::change_item_tree (this=0x7f5c88000d90, place=0x0, new_value=0x7f5c88087f18) at /data/src/10.2/sql/sql_class.h:3672
 
#5  0x00005645bc3b15b7 in Item::split_sum_func2 (this=0x7f5c88190910, thd=0x7f5c88000d90, ref_pointer_array=..., fields=..., ref=0x0, split_flags=0) at /data/src/10.2/sql/item.cc:2056
 
#6  0x00005645bc154d83 in JOIN::prepare (this=0x7f5c880864a8, tables_init=0x7f5c880a4ac0, 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=0x7f5c8818f778, unit_arg=0x7f5c8818f038) at /data/src/10.2/sql/sql_select.cc:955
 
#7  0x00005645bc15ec26 in mysql_select (thd=0x7f5c88000d90, tables=0x7f5c880a4ac0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147749632, result=0x7f5c88086488, unit=0x7f5c8818f038, select_lex=0x7f5c8818f778) at /data/src/10.2/sql/sql_select.cc:3818
 
#8  0x00005645bc152e3a in handle_select (thd=0x7f5c88000d90, lex=0x7f5c8818ef78, result=0x7f5c88086488, setup_tables_done_option=0) at /data/src/10.2/sql/sql_select.cc:361
 
#9  0x00005645bc11d4e5 in execute_sqlcom_select (thd=0x7f5c88000d90, all_tables=0x7f5c880a4ac0) at /data/src/10.2/sql/sql_parse.cc:6275
 
#10 0x00005645bc114059 in mysql_execute_command (thd=0x7f5c88000d90) at /data/src/10.2/sql/sql_parse.cc:3586
 
#11 0x00005645bc065662 in sp_instr_stmt::exec_core (this=0x7f5c8809a918, thd=0x7f5c88000d90, nextp=0x7f5ce03c963c) at /data/src/10.2/sql/sp_head.cc:3332
 
#12 0x00005645bc064cc3 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7f5c8809a958, thd=0x7f5c88000d90, nextp=0x7f5ce03c963c, open_tables=false, instr=0x7f5c8809a918) at /data/src/10.2/sql/sp_head.cc:3095
 
#13 0x00005645bc065307 in sp_instr_stmt::execute (this=0x7f5c8809a918, thd=0x7f5c88000d90, nextp=0x7f5ce03c963c) at /data/src/10.2/sql/sp_head.cc:3248
 
#14 0x00005645bc0602db in sp_head::execute (this=0x7f5c88034a28, thd=0x7f5c88000d90, merge_da_on_success=true) at /data/src/10.2/sql/sp_head.cc:1326
 
#15 0x00005645bc0624f9 in sp_head::execute_procedure (this=0x7f5c88034a28, thd=0x7f5c88000d90, args=0x7f5c880056d0) at /data/src/10.2/sql/sp_head.cc:2202
 
#16 0x00005645bc112401 in do_execute_sp (thd=0x7f5c88000d90, sp=0x7f5c88034a28) at /data/src/10.2/sql/sql_parse.cc:2981
 
#17 0x00005645bc11adde in mysql_execute_command (thd=0x7f5c88000d90) at /data/src/10.2/sql/sql_parse.cc:5626
 
#18 0x00005645bc12128d in mysql_parse (thd=0x7f5c88000d90, rawbuf=0x7f5c880126f8 "CALL testsp_doesnot_works()", length=27, parser_state=0x7f5ce03ca5f0, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7790
 
#19 0x00005645bc10f4ca in dispatch_command (command=COM_QUERY, thd=0x7f5c88000d90, packet=0x7f5c88008b51 "CALL testsp_doesnot_works()", packet_length=27, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1827
 
#20 0x00005645bc10dfc5 in do_command (thd=0x7f5c88000d90) at /data/src/10.2/sql/sql_parse.cc:1381
 
#21 0x00005645bc268a54 in do_handle_one_connection (connect=0x5645beeb9810) at /data/src/10.2/sql/sql_connect.cc:1336
 
#22 0x00005645bc2687b9 in handle_one_connection (arg=0x5645beeb9810) at /data/src/10.2/sql/sql_connect.cc:1241
 
#23 0x00005645bca91a26 in pfs_spawn_thread (arg=0x5645bee9cc00) at /data/src/10.2/storage/perfschema/pfs.cc:1869
 
#24 0x00007f5ce6474609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#25 0x00007f5ce604e293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Fails the same way on debug, ASAN and release builds, 10.2-10.6.

Comment by Oleksandr Byelkin [ 2021-09-15 ]

It crashes group_concat Item has ref_by NULL.

Comment by Oleksandr Byelkin [ 2021-09-15 ]

Item_sum::register_sum_func called on first call and assign the ref_by, but not second time.

Comment by Oleksandr Byelkin [ 2021-09-15 ]

ref_by reset by fix_fields to NULL.

On first execution of Item_sum::check_sum_func nest_level is 1 and max_arg_level 0 and it go to register_sum_func and assign ref_by. on the second execution both (nest_level and max_arg_level) are 1 and so no register_sum_func call.

Comment by Igor Babaev [ 2021-12-30 ]

The following test case brings us to the same crash in THD::change_item_tree() as the reported test case:

create table t1 (a int);
 
create table t2 (b int);
 
insert into t1 values (3), (1), (3);
 
insert into t2 values (70), (30), (70);
 
 
 
prepare stmt from "
 
select (select sum(b) from t1 where a=1) from (select * from t2) dt;
 
";
 
execute stmt;
 
execute stmt;

As for the reported test we have a crash at the second execution of the prepared statement for a query that uses a subquery with set function whose argument refers to a field of a mergeable derived table from the FROM clause of the main query.

Executing EXPLAIN EXTENDED for the query shows that the merge of the derived table is done correctly:

MariaDB [test]> explain extended select (select sum(b) from t1 where a=1) from (select * from t2) dt;
 
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
| id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
|    1 | PRIMARY            | t2    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 |             |
 
|    2 | DEPENDENT SUBQUERY | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
 
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
2 rows in set, 3 warnings (0.00 sec)
 
 
 
MariaDB [test]> show warnings;
 
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                                                                                                          |
 
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1276 | Field or reference 'b' of SELECT #2 was resolved in SELECT #1                                                                                                                                                    |
 
| Note  | 1981 | Aggregate function 'sum()' of SELECT #2 belongs to SELECT #1                                                                                                                                                     |
 
| Note  | 1003 | select <expr_cache><`test`.`t2`.`b`,sum(`test`.`t2`.`b`),sum(`test`.`t2`.`b`)>((select sum(`test`.`t2`.`b`) from `test`.`t1` where `test`.`t1`.`a` = 1)) AS `(select sum(b) from t1 where a=1)` from `test`.`t2` |
 
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
3 rows in set (0.00 sec)

Comment by Igor Babaev [ 2021-12-30 ]

Manual merge of the derived table does not cause any problem:

MariaDB [test]> prepare stmt from "
 
    "> select (select sum(b) from t1 where a=1) as sq from t2;
 
    "> ";
 
Query OK, 0 rows affected (0.00 sec)
 
Statement prepared
 
 
 
MariaDB [test]> execute stmt;
 
+------+
 
| sq   |
 
+------+
 
|  170 |
 
+------+
 
1 row in set (0.01 sec)
 
 
 
MariaDB [test]> execute stmt;
 
+------+
 
| sq   |
 
+------+
 
|  170 |
 
+------+
 
1 row in set (0.00 sec)

Comment by Igor Babaev [ 2021-12-30 ]

In MySQL 8.0.18 I do not see such problem.

Comment by Igor Babaev [ 2021-12-31 ]

Interesting that if we use the view v1

create view v1 as select * from t2;

instead of the derived table dt
the second execution of the corresponding prepared statement works fine:

MariaDB [test]> prepare stmt from "                
select (select sum(b) from t1 where a=1) from v1;                   
";
 
Query OK, 0 rows affected (0.00 sec)
 
Statement prepared
 
 
 
MariaDB [test]> execute stmt;
 
+-----------------------------------+
 
| (select sum(b) from t1 where a=1) |
 
+-----------------------------------+
 
|                               170 |
 
+-----------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> execute stmt;
 
+-----------------------------------+
 
| (select sum(b) from t1 where a=1) |
 
+-----------------------------------+
 
|                               170 |
 
+-----------------------------------+
 
1 row in set (0.00 sec)

Comment by Igor Babaev [ 2021-12-31 ]

When at the second execution of the prepared statement for the query 

select (select sum(b) from t1 where a=1) from (select * from t2) dt;

the reference to the field b from the expression sum(b) is resolved in Item_field::fix_fields() the function find_field_in_tables() is called and it sees that the field cached_table points to the table dt and that this table is a merged derived table. That why the function find_field_in_table_ref() is called. This function calls the function find_field_in_view() as a field translation is defined for dt. The function find_field_in_view() creates the field translation item for b and this is t2.b. Item_field::fix_fields() is called for t2.b. Here find_field_in_tables() is called for t2.b that first looks for a t2.b in in the table t2. It successfully finds it and comes to the code

    if (lex_s &&
 
        lex_s->in_sum_func &&
 
        lex_s->in_sum_func->nest_level ==
 
        select->nest_level)
 
      set_if_bigger(lex_s->in_sum_func->max_arg_level,
 
                    select->nest_level);

where lex_s is the LEX structure for the whole query, select is select for dt, lex_s->in_sun_func points to the item for sum(b). As select->nest_level == 1 and lex_s->in_sum_func->nest_level == 1 the value of lex_s->in_sum_func->max_arg_level is set to 1 and this is incorrect.

When the prepared statement for the query using the view v1 is executed for the second time the resolution of b from sum(b) goes through the same sequence of calls and comes to the above code. However here lex_s is the LEX structure for the view instance, not for the whole query and lex_s->in_sum_func == NULL. So thd->lex->in_sum_func->max_arg_level is not updated here remaining equal to (-1). This value is updated when we return after the call of find_field_in_tables() within the invocation of Item_field::fix_fields() for b .
The function Item_field::fix_outer_field is called and it set the correct value of max_arg_level for sum(b).

Comment by Igor Babaev [ 2022-01-01 ]

The above code was added in the commit:

commit 3690c549c6e72646ba74f6b4c83813ee4ac3aea4
 
Author:	Oleksandr Byelkin <sanja@mariadb.com>  Fri Jul 23 02:14:13 2021
 
Committer:	Oleksandr Byelkin <sanja@mariadb.com>  Mon Sep 27 02:00:51 2021
 
MDEV-24454 Crash at change_item_tree

If we replace the above code with the code

    if (!thd->lex->current_select->no_wrap_view_item &&
 
        thd->lex->in_sum_func &&
 
        thd->lex->in_sum_func->nest_level == select->nest_level)
 
      set_if_bigger(thd->lex->in_sum_func->max_arg_level,
 
                    select->nest_level);

all tests from the main test suite pass (without and with --ps-protocol).
Also we have no problems with the second executions of the prepared statements for the following queries

select (select sum(b) from t1 where a=1) from (select * from t2) dt;
 
select (select sum(b) from t1 where a=1) from (select * from (select * from t2) dt1 where b > 50) dt;
 
select (select sum(b) from t1 where a=1) from (select * from t2 group by b) dt;
 
select (select sum(b) from t1 where a=1) from (select * from (select * from t2) dt1 group by b) dt;
 
select (select sum(b) from t1 where a=1) from v1;
 
select (select sum(b) from t1 where a=1) from (select * from v1 where b > 50) dt;
 
select (select sum(b) from t1 where a=1) from (select * from v1 group by b) dt;

Also after this change we have no problems with the second call of the following stored procedure that causes a crash with the current code:

create procedure sp1()
 
begin
 
select (select sum(b) from t1 where a=1) from (select * from t2) dt;
 
end


MariaDB [test]> call sp1();
 
+-----------------------------------+
 
| (select sum(b) from t1 where a=1) |
 
+-----------------------------------+
 
|                               170 |
 
+-----------------------------------+
 
1 row in set (0.01 sec)
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [test]> call sp1();
 
+-----------------------------------+
 
| (select sum(b) from t1 where a=1) |
 
+-----------------------------------+
 
|                               170 |
 
+-----------------------------------+
 
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)

Comment by Igor Babaev [ 2022-01-01 ]

If we return to 10.2 tree just before the commit 3690c549c6e72646ba74f6b4c83813ee4ac3aea4 (see above)
we get the same crash for the second execution of the prepared statement for the query

select (select sum(b) from t1 where a=1) from v1;

as for the second execution of the prepared statement for the query

select (select sum(b) from t1 where a=1) from (select * from t2) dt;

Comment by Igor Babaev [ 2022-01-05 ]

The change in Item_field::fix_fields() suggested above fixes both bugs MDEV-24454 and MDEV-25086. However it cannot fix the bug MDEV-25631 that was also fixed by the commit for MDEV-24454. Indeed the bugs MDEV-24454/MDEV-25086 and MDEV-25631 have nothing in common. The first two bug concern second executions of queries with a set function whose only argument is an outer reference to a field from a mergeable view or from a mergeable derived table. The bug MDEV-25631 crashes the server when executing a query with derived table specified as aggregating select from a view. Besides this view has to be specified as select with a subquery containing an outer reference. The following provides such crashing query:

create table t3 (c int);
 
insert into t3 values (1);
 
create view v3 as select c from t3 where (select t3.c from t3 t) = 1;
 
select * from (select sum((select * from v3)) as r) dt;

Note that if in the above query we use the derived table dt1 specified exactly as v3 we have no problems with the execution of the query:

MariaDB [test]> select * from (select sum((select * from (select c from t3 where (select t3.c from t3 t) = 1) dt1)) as r) dt;
 
+------+
 
| r    |
 
+------+
 
|    1 |
 
+------+
 
1 row in set (0.01 sec)

We also have no problems if we use CTE cte instead of dt1:

MariaDB [test]> with cte as (select c from t3 where (select t3.c from t3 t) = 1) select * from (select sum((select * from cte)) as r) dt;
 
+------+
 
| r    |
 
+------+
 
|    1 |
 
+------+

Yet we immediately have the same kind of crash if we use cte twice like in the query:

with cte as (select c from t3 where (select t3.c from t3 t) = 1)
 
select * from (select sum((select * from cte)) as r) dt1
 
union
 
select * from (select sum((select * from cte)) as r) dt2;

Comment by Igor Babaev [ 2022-01-06 ]

Analysis of the bug MDEV-25631 shows that max_arg_level for sum() is set to 0, while it should be 1. It happens in this piece of code:

          if (thd->lex->in_sum_func &&
 
              thd->lex->in_sum_func->nest_level >= select->nest_level)
 
          {
 
            Item::Type ref_type= (*reference)->type();
 
            set_if_bigger(thd->lex->in_sum_func->max_arg_level,
 
                          select->nest_level);
 
...

'select' above is the select for the specification of v3. Each view specification is parsed separately with its own LEX and the top select has level 0. Meanwhile the set function sum() is used in the main query and in a select whose nest level is 1. Comparing nest levels of selects belonging to different LEX structures does not make sense. Besides we know that an argument of any set function cannot belong to a view specification.
Because of this a possible update of thd->lex->in_sum_func->max_arg_level may be done only in the case
when 

              thd->lex == context->select_lex->parent_lex

Thus instead of the above code we should use the code:

          if (thd->lex->in_sum_func &&
 
              thd->lex == context->select_lex->parent_lex &&
 
              thd->lex->in_sum_func->nest_level >= select->nest_level)
 
          {
 
            Item::Type ref_type= (*reference)->type();
 
            set_if_bigger(thd->lex->in_sum_func->max_arg_level,
 
                          select->nest_level);
 
...

Similar changes should applied to all other pieces of code where thd->lex->in_sum_func->max_arg_level is updated.

Comment by Oleksandr Byelkin [ 2022-01-10 ]

OK to push (with other 2 commits)

Comment by Igor Babaev [ 2022-01-11 ]

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

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