[MDEV-24925] Server crashes in Item_subselect::init_expr_cache_tracker Created: 2021-02-19  Updated: 2021-06-30  Resolved: 2021-04-25

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.38, 10.3.29, 10.4.19, 10.5.10, 10.6.1

Type: Bug Priority: Blocker
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: regression

Issue Links:
Problem/Incident
is caused by MDEV-24779 main.subselect fails in buildbot with... Closed
Relates
relates to MDEV-26047 MariaDB server crash at Item_subselec... Closed

 Description    

CREATE TABLE t1 (id INT PRIMARY KEY);
 
INSERT INTO t1 VALUES (1),(2);
 
 
 
SELECT 1 IN (SELECT (SELECT COUNT(id) FROM t1 WHERE t1_outer.id <> id) AS f FROM t1 AS t1_outer GROUP BY f);
 
 
 
# Cleanup
 
DROP TABLE t1;

10.2 5ecaf52d

 
#3  <signal handler called>
 
#4  Item_subselect::init_expr_cache_tracker (this=0x7faab0014b98, thd=0x7faab0000d90) at /data/src/10.2/sql/item_subselect.cc:6828
 
#5  0x000055d279095647 in Item_singlerow_subselect::expr_cache_insert_transformer (this=0x7faab0014b98, tmp_thd=0x7faab0000d90, unused=0x0) at /data/src/10.2/sql/item_subselect.cc:1287
 
#6  0x000055d278fe14ce in Item::transform (this=0x7faab0014b98, thd=0x7faab0000d90, transformer=&virtual Item::expr_cache_insert_transformer(THD*, unsigned char*), arg=0x0) at /data/src/10.2/sql/item.cc:736
 
#7  0x000055d278d90bdc in JOIN::setup_subquery_caches (this=0x7faab0016098) at /data/src/10.2/sql/sql_select.cc:3178
 
#8  0x000055d278d8cc63 in JOIN::optimize_inner (this=0x7faab0016098) at /data/src/10.2/sql/sql_select.cc:2086
 
#9  0x000055d278d89496 in JOIN::optimize (this=0x7faab0016098) at /data/src/10.2/sql/sql_select.cc:1118
 
#10 0x000055d278d38301 in st_select_lex::optimize_unflattened_subqueries (this=0x7faab00050c8, const_only=true) at /data/src/10.2/sql/sql_lex.cc:3871
 
#11 0x000055d278efbcc9 in JOIN::optimize_constant_subqueries (this=0x7faab00159b0) at /data/src/10.2/sql/opt_subselect.cc:5360
 
#12 0x000055d278d89fb2 in JOIN::optimize_inner (this=0x7faab00159b0) at /data/src/10.2/sql/sql_select.cc:1340
 
#13 0x000055d278d89496 in JOIN::optimize (this=0x7faab00159b0) at /data/src/10.2/sql/sql_select.cc:1118
 
#14 0x000055d278d929d4 in mysql_select (thd=0x7faab0000d90, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7faab0015990, unit=0x7faab0004988, select_lex=0x7faab00050c8) at /data/src/10.2/sql/sql_select.cc:3823
 
#15 0x000055d278d86bde in handle_select (thd=0x7faab0000d90, lex=0x7faab00048c8, result=0x7faab0015990, setup_tables_done_option=0) at /data/src/10.2/sql/sql_select.cc:361
 
#16 0x000055d278d512e8 in execute_sqlcom_select (thd=0x7faab0000d90, all_tables=0x7faab00141e8) at /data/src/10.2/sql/sql_parse.cc:6248
 
#17 0x000055d278d47c97 in mysql_execute_command (thd=0x7faab0000d90) at /data/src/10.2/sql/sql_parse.cc:3559
 
#18 0x000055d278d55091 in mysql_parse (thd=0x7faab0000d90, rawbuf=0x7faab00126f8 "SELECT 1 IN (SELECT (SELECT COUNT(id) FROM t1 WHERE t1_outer.id <> id) AS f FROM t1 AS t1_outer GROUP BY f)", length=107, parser_state=0x7faac19315f0, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7763
 
#19 0x000055d278d4336a in dispatch_command (command=COM_QUERY, thd=0x7faab0000d90, packet=0x7faab0008b51 "SELECT 1 IN (SELECT (SELECT COUNT(id) FROM t1 WHERE t1_outer.id <> id) AS f FROM t1 AS t1_outer GROUP BY f)", packet_length=107, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1827
 
#20 0x000055d278d41e65 in do_command (thd=0x7faab0000d90) at /data/src/10.2/sql/sql_parse.cc:1381
 
#21 0x000055d278e9c972 in do_handle_one_connection (connect=0x55d27c7d1930) at /data/src/10.2/sql/sql_connect.cc:1336
 
#22 0x000055d278e9c6d7 in handle_one_connection (arg=0x55d27c7d1930) at /data/src/10.2/sql/sql_connect.cc:1241
 
#23 0x000055d2796c4eb8 in pfs_spawn_thread (arg=0x55d27c7b4c80) at /data/src/10.2/storage/perfschema/pfs.cc:1869
 
#24 0x00007faac7ce7609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#25 0x00007faac78c3293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Reproducible on debug and non-debug builds alike.
The failure appeared in 10.2 after this commit:

Author: Varun Gupta
 
Date:   Mon Feb 15 16:28:44 2021 +0530
 
 
    MDEV-24779: main.subselect fails in buildbot with --ps-protocol

Other versions are not affected so far, since the patch hasn't been merged up yet.

Likely to be related to MDEV-24898.

 Comments   
Comment by Sergei Petrunia [ 2021-04-23 ]

Formatted query:

SELECT 
  1 IN (
 
    SELECT 
      (SELECT COUNT(id) 
       FROM t1 
       WHERE t1_outer.id <> id
 
       ) AS f 
    FROM
 
      t1 AS t1_outer 
    GROUP BY f
 
  );

The crash happens when we try to create an expression cache
for the single-row subquery.
We crash, because subquery's unit->first_select()==NULL.

Looking why this is so, I see that the subquery is "eliminated" by this
code:

    /*      
      Remove GROUP BY if there are no aggregate functions and no HAVING
 
      clause
 
    */  
    if (subq_select_lex->group_list.elements &&
 
        !subq_select_lex->with_sum_func && !subq_select_lex->join->having)
 
    {   
      for (ORDER *ord= subq_select_lex->group_list.first; ord; ord= ord->next)
 
      {
 
=>      (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL);
 
      }

which removes the subquery's select from the SELECT_LEX tree and sets
SELE_LEX_UNIT's slave=NULL.

Comment by Sergei Petrunia [ 2021-04-23 ]

The removal of the subquery seems to be the wrong thing to do.
The subquery is present in the select list of the "SELECT .. FROM t1 as t1_outer".

If I work around the subquery cache initialization problem, the execution will
crash in:

  0x0000555555c1c9bb in st_select_lex::get_offset (this=0x0) at /home/psergey/dev-git/10.3/sql/sql_lex.cc:2795
 
  #1  0x0000555555c1e2a0 in st_select_lex_unit::set_limit (this=0x7fff7c014b80, sl=0x0) at /home/psergey/dev-git/10.3/sql/sql_lex.cc:3471
 
  #2  0x000055555604f8f7 in subselect_single_select_engine::exec (this=0x7fff7c016148) at /home/psergey/dev-git/10.3/sql/item_subselect.cc:3923

Comment by Sergei Petrunia [ 2021-04-23 ]

Apparently the same Item_singlerow_subselect is precent in the select list and
in the group list:

(gdb) p this->fields_list.elem(0)
 
  $122 = (Item_singlerow_subselect *) 0x7fff74015fc0
 
 
 
(gdb) p subq_select_lex->group_list.first->item[0]
 
  $129 = (Item_singlerow_subselect *) 0x7fff74015fc0

How can one distinguish between the cases like

select ... from ... group by (subquery )

and

select (subquery) as f ... from ... grouop by f

Comment by Sergei Petrunia [ 2021-04-23 ]

http://lists.askmonty.org/pipermail/commits/2021-April/014571.html

Comment by Sergei Petrunia [ 2021-04-23 ]

bb-10.3-mdev24925

Comment by Sergei Petrunia [ 2021-04-23 ]

Sanja, please review

Comment by Sergei Petrunia [ 2021-04-23 ]

The fix also fixes MDEV-24898. Will add a testcase.

Comment by Oleksandr Byelkin [ 2021-04-23 ]

OK to push

Comment by Sergei Petrunia [ 2021-04-26 ]

Cherry-picked the fix to 10.2

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