[MDEV-25631] Crash executing query with VIEW, aggregate and subquery Created: 2021-05-10  Updated: 2022-11-29  Resolved: 2022-01-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.5.9, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.42, 10.3.33, 10.4.23, 10.5.14, 10.6.6, 10.7.2

Type: Bug Priority: Major
Reporter: Zuming Jiang Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: crash, fuzzer
Environment:

Ubuntu 18.04
MariaDB 10.5.9


Attachments: Text File abortion_report.txt     File fuzz.sql    
Issue Links:
Duplicate
duplicates MDEV-24454 Crash at change_item_tree Closed
Problem/Incident
causes MDEV-28206 SIGSEGV in Item_field::fix_fields wh... Closed
Relates
relates to MDEV-20325 Assertion `outer_context || !*from_fi... Closed

 Description   

I used my fuzzing tool to test Mariadb , and found a bug that can result in an abortion.

Mariadb installation:
1) cd mariadb-10.5.9
2) mkdir build; cd build
3) cmake -DWITH_ASAN=ON -DWITH_ASAN_SCOPE=ON -DWITH_DEBUG=ON ../
4) make -j8 && sudo make install

How to Repeat:
export ASAN_OPTIONS=detect_leaks=0
/usr/local/mysql/bin/mysqld_safe &
/usr/local/mysql/bin/mysql -uroot -p123456(your password)
MariaDB> drop database if exists test_db;
MariaDB> create database test_db;
MariaDB> source fuzz.sql;

I have simplified the content of fuzz.sql, and I hope fuzz.sql can help you reproduce the bug and fix it. In addition, I attach the abortion report (which has its stack trace).



 Comments   
Comment by Alice Sherepa [ 2021-05-10 ]

Thanks!
Repeatable on 5.5-10.5:

CREATE TABLE t1 (i1 int);
insert into t1 values (1),(2),(3); #not important
CREATE VIEW v1 AS
	SELECT t1.i1 FROM (t1 a JOIN t1 ON (t1.i1 = (SELECT t1.i1 FROM t1 b)));
 
SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ;

10.2 d0785f773188b5f0eebb313

#3  <signal handler called>
#4  st_select_lex::mark_as_dependent (this=0x7f32f0012888, thd=0x7f32f0000d90, last=0x0, dependency=0x0) at /10.2/src/sql/sql_lex.cc:2502
#5  0x0000560812c7cfe2 in Item_sum::register_sum_func (this=0x7f32f0014838, thd=0x7f32f0000d90, ref=0x7f32f0014970) at /10.2/src/sql/item_sum.cc:403
#6  0x0000560812c7c9b0 in Item_sum::check_sum_func (this=0x7f32f0014838, thd=0x7f32f0000d90, ref=0x7f32f0014970) at /10.2/src/sql/item_sum.cc:198
#7  0x0000560812c7f2a9 in Item_sum_num::fix_fields (this=0x7f32f0014838, thd=0x7f32f0000d90, ref=0x7f32f0014970) at /10.2/src/sql/item_sum.cc:1137
#8  0x00005608128b2a31 in setup_fields (thd=0x7f32f0000d90, ref_pointer_array=..., fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7f32f0177628, pre_fix=0x7f32f00129c8, allow_sum_func=true) at /10.2/src/sql/sql_base.cc:7288
#9  0x000056081295b5a5 in JOIN::prepare (this=0x7f32f0177308, tables_init=0x7f32f0014a08, 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=0x7f32f0012888, unit_arg=0x7f32f0012c80) at /10.2/src/sql/sql_select.cc:807
#10 0x0000560812a0a268 in st_select_lex_unit::prepare (this=0x7f32f0012c80, thd_arg=0x7f32f0000d90, sel_result=0x7f32f0177238, additional_options=0) at /10.2/src/sql/sql_union.cc:596
#11 0x00005608128eb16a in mysql_derived_prepare (thd=0x7f32f0000d90, lex=0x7f32f00048c8, derived=0x7f32f0015078) at /10.2/src/sql/sql_derived.cc:764
#12 0x00005608128e9f35 in mysql_handle_single_derived (lex=0x7f32f00048c8, derived=0x7f32f0015078, phases=2) at /10.2/src/sql/sql_derived.cc:192
#13 0x0000560812a348dc in TABLE_LIST::handle_derived (this=0x7f32f0015078, lex=0x7f32f00048c8, phases=2) at /10.2/src/sql/table.cc:8124
#14 0x0000560812900e42 in LEX::handle_list_of_derived (this=0x7f32f00048c8, table_list=0x7f32f0015078, phases=2) at /10.2/src/sql/sql_lex.h:3205
#15 0x000056081290b7e8 in st_select_lex::handle_derived (this=0x7f32f00050c8, lex=0x7f32f00048c8, phases=2) at /10.2/src/sql/sql_lex.cc:3924
#16 0x000056081295b0b7 in JOIN::prepare (this=0x7f32f009d208, tables_init=0x7f32f0015078, 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=0x7f32f00050c8, unit_arg=0x7f32f0004988) at /10.2/src/sql/sql_select.cc:725
#17 0x0000560812966056 in mysql_select (thd=0x7f32f0000d90, tables=0x7f32f0015078, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f32f009d1e8, unit=0x7f32f0004988, select_lex=0x7f32f00050c8) at /10.2/src/sql/sql_select.cc:3827
#18 0x000056081295a25c in handle_select (thd=0x7f32f0000d90, lex=0x7f32f00048c8, result=0x7f32f009d1e8, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361
#19 0x0000560812924771 in execute_sqlcom_select (thd=0x7f32f0000d90, all_tables=0x7f32f0015078) at /10.2/src/sql/sql_parse.cc:6274
#20 0x000056081291b2e5 in mysql_execute_command (thd=0x7f32f0000d90) at /10.2/src/sql/sql_parse.cc:3585
#21 0x000056081292852c in mysql_parse (thd=0x7f32f0000d90, rawbuf=0x7f32f00126f8 "SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt", length=62, parser_state=0x7f33466a4570, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7796
#22 0x0000560812916756 in dispatch_command (command=COM_QUERY, thd=0x7f32f0000d90, packet=0x7f32f0008b51 "SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ", packet_length=63, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
#23 0x0000560812915251 in do_command (thd=0x7f32f0000d90) at /10.2/src/sql/sql_parse.cc:1381
#24 0x0000560812a7088e in do_handle_one_connection (connect=0x560816b917c0) at /10.2/src/sql/sql_connect.cc:1336
#25 0x0000560812a705f3 in handle_one_connection (arg=0x560816b917c0) at /10.2/src/sql/sql_connect.cc:1241
#26 0x000056081329c1a8 in pfs_spawn_thread (arg=0x560816b74bc0) at /10.2/src/storage/perfschema/pfs.cc:1869
#27 0x00007f334c8a9609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#28 0x00007f334c483293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Comment by Sergei Petrunia [ 2021-05-15 ]

So, it is crashing when trying to find which select the COUNT() function
should be aggregated in.

View v1 has column i1, so

count(((SELECT i1 FROM v1)))

should be aggregated locally.
but in Item_sum::check_sum_func() we get into this:

    else if (max_arg_level >= 0 ||
             !(allow_sum_func.is_set(nest_level)))
    {
      /*
        The set function can be aggregated only in outer subqueries.
        Try to find a subquery where it can be aggregated;
        If we fail to find such a subquery report an error.
      */
=>    if (register_sum_func(thd, ref))

(gdb) print max_arg_level
  $33 = 0 '\000'
(gdb) print nest_level
  $35 = 1 '\001'
(gdb) print allow_sum_func
  $38 = {..., buffer = {2}}

nest_level=1 looks to be correct.
max_arg_level=0 seems to be incorrect.

Comment by Sergei Petrunia [ 2021-05-15 ]

Looking where Item_sum::max_arg_level is changed. It happens here:

  Old value = -1 '\377'
  New value = 0 '\000'

https://gist.github.com/spetrunia/50ca57bb94867ee1ce8a2ec4557303cc

            /*
              A reference is resolved to a nest level that's outer or the same as
              the nest level of the enclosing set function : adjust the value of
              max_arg_level for the function if it's needed.
            */
            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);
=>            set_field(*from_field); 

The code is doing fix_fields for the "t1.i1" of the subquery in the ON
expression of the view definition:

CREATE VIEW v1 AS
	SELECT t1.i1 FROM (t1 a JOIN t1 ON (t1.i1 = (SELECT t1.i1 FROM t1 b)));
 
SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ;

It has located the field in the right select:

(gdb) p select->select_number
  $46 = 4

But the select has

(gdb) p select->nest_level
  $47 = 0

I think this is depth value that's "local to the view we are in".

Another odd thing, why is it touching the IN subquery? the reference is made
from the scalar-context subquery t1.i1 = (SELECT ... ).

But the code is looking at thd->lex->in_sum_func and that one indeed is
pointing to the COUNT function:

(gdb) p thd->lex->in_sum_func
  $48 = (Item_sum_count *) 0x7fff98018b28

Comment by Sergei Petrunia [ 2021-05-16 ]

I think this is depth value that's "local to the view we are in".

Right, SELECT_LEX has nest_level_base pointer to indicate which "base" the nest_level variable refers to.

Comment by Sergei Petrunia [ 2021-05-16 ]

A patch demonstrating the approach this can be fixed: http://lists.askmonty.org/pipermail/commits/2021-May/014606.html . Need to discuss it with sanja and [igor.

Comment by Oleksandr Byelkin [ 2021-06-28 ]

What do you think about such change?

diff --git a/sql/item.cc b/sql/item.cc
index 6e45c6a8086..305f4f2c0ec 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5494,9 +5494,10 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
     subselects may contain columns with the same names. The subselects
     are searched starting from the innermost.
   */
+  LEX *lex= context->select_lex->parent_lex;
   Name_resolution_context *last_checked_context= context;
   Item **ref= (Item **) not_found_item;
-  SELECT_LEX *current_sel= thd->lex->current_select;
+  SELECT_LEX *current_sel=  context->select_lex;
   Name_resolution_context *outer_context= 0;
   SELECT_LEX *select= 0;
   /* Currently derived tables cannot be correlated */
@@ -5598,18 +5599,18 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
               return -1;
             thd->change_item_tree(reference, rf);
             select->inner_refs_list.push_back(rf, thd->mem_root);
-            rf->in_sum_func= thd->lex->in_sum_func;
+            rf->in_sum_func= lex->in_sum_func;
           }
           /*
             A reference is resolved to a nest level that's outer or the same as
             the nest level of the enclosing set function : adjust the value of
             max_arg_level for the function if it's needed.
           */
-          if (thd->lex->in_sum_func &&
-              thd->lex->in_sum_func->nest_level >= select->nest_level)
+          if (lex->in_sum_func &&
+              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,
+            set_if_bigger(lex->in_sum_func->max_arg_level,
                           select->nest_level);
             set_field(*from_field);
             fixed= 1;
@@ -5630,10 +5631,10 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
                             ((ref_type == REF_ITEM || ref_type == FIELD_ITEM) ?
                              (Item_ident*) (*reference) :
                              0), false);
-          if (thd->lex->in_sum_func &&
-              thd->lex->in_sum_func->nest_level >= select->nest_level)
+          if (lex->in_sum_func &&
+              lex->in_sum_func->nest_level >= select->nest_level)
           {
-            set_if_bigger(thd->lex->in_sum_func->max_arg_level,
+            set_if_bigger(lex->in_sum_func->max_arg_level,
                           select->nest_level);
           }
           /*
@@ -5725,7 +5726,7 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
     {
       outer_context->select_lex->inner_refs_list.push_back((Item_outer_ref*)rf,
                                                            thd->mem_root);
-      ((Item_outer_ref*)rf)->in_sum_func= thd->lex->in_sum_func;
+      ((Item_outer_ref*)rf)->in_sum_func= lex->in_sum_func;
     }
     thd->change_item_tree(reference, rf);
     /*

(as well in other places where in_sum_func used. All other places shoud be not affected, bacuse only thd->lex break borders of name resolution contexts chains.

Comment by Oleksandr Byelkin [ 2021-06-28 ]

also why it is 10.5 not 10.2?

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:39:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.