[MDEV-23384] Server crashes in subselect_indexsubquery_engine::print with optimizer trace enabled Created: 2020-08-03  Updated: 2024-01-25

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer, Optimizer - CTE
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None


 Description    

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (1),(2);
 
 
 
CREATE TABLE t2 (b INT) ENGINE=MyISAM;
 
INSERT INTO t2 VALUES (3),(4);
 
 
 
CREATE TABLE t3 (c INT) ENGINE=MyISAM;
 
 
 
CREATE TABLE t4 (d INT) ENGINE=MyISAM;
 
INSERT INTO t4 VALUES (5),(6);
 
 
 
CREATE TABLE t9_InnoDB (col_int INT) ENGINE=MyISAM;
 
INSERT INTO t9_InnoDB VALUES (1),(2);
 
 
 
SET SESSION optimizer_trace= 'enabled=on';
 
 
 
UPDATE t1, t2 SET t2.b = 1 WHERE (WITH cte1 AS (SELECT MAX(c) FROM t3 WHERE c = 0 OR c IN (WITH cte2 AS (SELECT d FROM t4) SELECT * FROM cte2)) SELECT * FROM cte1) IS NULL;
 
 
 
# Cleanup
 
DROP TABLE t1, t2, t3, t4;

10.4 00f964ab

 
#3  <signal handler called>
 
#4  0x00005622123d5e22 in subselect_indexsubquery_engine::print (this=0x7ff3fc1c50a8, str=0x7ff44a20b5d0, query_type=1545) at /data/src/10.4/sql/item_subselect.cc:4508
 
#5  0x00005622123c890d in Item_subselect::print (this=0x7ff3fc0181e8, str=0x7ff44a20b5d0, query_type=1545) at /data/src/10.4/sql/item_subselect.cc:1002
 
#6  0x00005622123d2908 in Item_in_subselect::print (this=0x7ff3fc0181e8, str=0x7ff44a20b5d0, query_type=1545) at /data/src/10.4/sql/item_subselect.cc:3311
 
#7  0x00005622123721c8 in Item_func::print_args (this=0x7ff3fc1a94a8, str=0x7ff44a20b5d0, from=0, query_type=1545) at /data/src/10.4/sql/item_func.cc:610
 
#8  0x0000562212372127 in Item_func::print (this=0x7ff3fc1a94a8, str=0x7ff44a20b5d0, query_type=1545) at /data/src/10.4/sql/item_func.cc:599
 
#9  0x000056221233276d in Item_in_optimizer::print (this=0x7ff3fc1a94a8, str=0x7ff44a20b5d0, query_type=1545) at /data/src/10.4/sql/item_cmpfunc.cc:1240
 
#10 0x0000562212021cf5 in st_select_lex::print (this=0x7ff3fc0143f0, thd=0x7ff3fc000af0, str=0x7ff44a20b5d0, query_type=1545) at /data/src/10.4/sql/sql_select.cc:27542
 
#11 0x0000562211f5b136 in st_select_lex_unit::print (this=0x7ff3fc018568, str=0x7ff44a20b5d0, query_type=1545) at /data/src/10.4/sql/sql_lex.cc:3027
 
#12 0x00005622121d4180 in With_element::print (this=0x7ff3fc018d60, str=0x7ff44a20b5d0, query_type=1545) at /data/src/10.4/sql/sql_cte.cc:1462
 
#13 0x00005622121d4054 in With_clause::print (this=0x7ff3fc014360, str=0x7ff44a20b5d0, query_type=1545) at /data/src/10.4/sql/sql_cte.cc:1424
 
#14 0x00005622123d5bac in subselect_single_select_engine::print (this=0x7ff3fc1a79e8, str=0x7ff44a20b5d0, query_type=1033) at /data/src/10.4/sql/item_subselect.cc:4440
 
#15 0x00005622123c890d in Item_subselect::print (this=0x7ff3fc018ff8, str=0x7ff44a20b5d0, query_type=1033) at /data/src/10.4/sql/item_subselect.cc:1002
 
#16 0x00005622123050ad in Item::print_parenthesised (this=0x7ff3fc018ff8, str=0x7ff44a20b5d0, query_type=1033, parent_prec=CMP_PRECEDENCE) at /data/src/10.4/sql/item.cc:487
 
#17 0x0000562212340eae in Item_func_isnull::print (this=0x7ff3fc1a7a28, str=0x7ff44a20b5d0, query_type=1033) at /data/src/10.4/sql/item_cmpfunc.cc:5399
 
#18 0x00005622121e2981 in Json_writer::add_str (this=0x7ff3fc1aa6b0, item=0x7ff3fc1a7a28) at /data/src/10.4/sql/opt_trace.cc:696
 
#19 0x000056221202d26e in Json_value_helper::add_str (this=0x7ff44a20b7d8, item=0x7ff3fc1a7a28) at /data/src/10.4/sql/my_json_writer.h:275
 
#20 0x000056221202d78a in Json_writer_object::add (this=0x7ff44a20b7d0, name=0x562212c9d3bf "original_condition", value=0x7ff3fc1a7a28) at /data/src/10.4/sql/my_json_writer.h:454
 
#21 0x00005622120041f4 in optimize_cond (join=0x7ff3fc1a9a18, conds=0x7ff3fc1a7a28, join_list=0x7ff3fc0053d0, ignore_on_conds=false, cond_value=0x7ff3fc1a9d28, cond_equal=0x7ff3fc1a9e50, flags=1) at /data/src/10.4/sql/sql_select.cc:16968
 
#22 0x0000562211fd8eaa in JOIN::optimize_inner (this=0x7ff3fc1a9a18) at /data/src/10.4/sql/sql_select.cc:2008
 
#23 0x0000562211fd7748 in JOIN::optimize (this=0x7ff3fc1a9a18) at /data/src/10.4/sql/sql_select.cc:1610
 
#24 0x0000562211fe2bf0 in mysql_select (thd=0x7ff3fc000af0, tables=0x7ff3fc0133a0, wild_num=0, fields=..., conds=0x7ff3fc1a7a28, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0x7ff3fc1a9940, unit=0x7ff3fc004a18, select_lex=0x7ff3fc005210) at /data/src/10.4/sql/sql_select.cc:4673
 
#25 0x00005622120a3704 in mysql_multi_update (thd=0x7ff3fc000af0, table_list=0x7ff3fc0133a0, fields=0x7ff3fc005358, values=0x7ff3fc0058e0, conds=0x7ff3fc1a7a28, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x7ff3fc004a18, select_lex=0x7ff3fc005210, result=0x7ff44a20bf00) at /data/src/10.4/sql/sql_update.cc:1927
 
#26 0x0000562211f911f3 in mysql_execute_command (thd=0x7ff3fc000af0) at /data/src/10.4/sql/sql_parse.cc:4436
 
#27 0x0000562211f9cf33 in mysql_parse (thd=0x7ff3fc000af0, rawbuf=0x7ff3fc013198 "UPDATE t1, t2 SET t2.b = 1 WHERE (WITH cte1 AS (SELECT MAX(c) FROM t3 WHERE c = 0 OR c IN (WITH cte2 AS (SELECT d FROM t4) SELECT * FROM cte2)) SELECT * FROM cte1) IS NULL", length=171, parser_state=0x7ff44a20c570, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7896
 
#28 0x0000562211f89468 in dispatch_command (command=COM_QUERY, thd=0x7ff3fc000af0, packet=0x7ff3fc0083a1 "UPDATE t1, t2 SET t2.b = 1 WHERE (WITH cte1 AS (SELECT MAX(c) FROM t3 WHERE c = 0 OR c IN (WITH cte2 AS (SELECT d FROM t4) SELECT * FROM cte2)) SELECT * FROM cte1) IS NULL", packet_length=171, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1835
 
#29 0x0000562211f87c0a in do_command (thd=0x7ff3fc000af0) at /data/src/10.4/sql/sql_parse.cc:1353
 
#30 0x0000562212110fa0 in do_handle_one_connection (connect=0x56221547a8e0) at /data/src/10.4/sql/sql_connect.cc:1412
 
#31 0x0000562212110cef in handle_one_connection (arg=0x56221547a8e0) at /data/src/10.4/sql/sql_connect.cc:1316
 
#32 0x0000562212b130d5 in pfs_spawn_thread (arg=0x56221539d2a0) at /data/src/10.4/storage/perfschema/pfs.cc:1869
 
#33 0x00007ff456df04a4 in start_thread (arg=0x7ff44a20d700) at pthread_create.c:456
 
#34 0x00007ff454f24d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Reproducible the same way on 10.4-10.5, debug, non-debug and ASAN builds, with MyISAM and Aria.
Couldn't reproduce with the provided test case with InnoDB instead of MyISAM.
Not applicable to earlier versions of CS due to optimizer trace.

 Comments   
Comment by Sergei Petrunia [ 2023-02-22 ]

Another testcase:

set optimizer_trace=1;
 
create table t10 (a int, b int) engine=myisam;
 
create table t11 (a int, b int, key(a));
 
insert into t11 select seq, seq from seq_1_to_10;
 
 
 
create table t12 (a int, key(a));
 
insert into t12 select seq from seq_1_to_1000;
 
 
 
set optimizer_trace=1;
 
select * from t12 where t12.a < (
 
select b from  (
 
  select b from t10
 
  where 
    a <3 or a in (select a from t11 where t11.b<t10.b)
 
  limit 2
 
) T);

slightly less convoluted, shows we don't need CTEs or UPDATE to get the crash.

Comment by Sergei Petrunia [ 2023-02-22 ]

Analysis

So, it crashes when printing an Item_subselect.
Item_subselect uses (or rather, used) an subselect_indexsubquery_engine for execution.
It tries to print its contents, which crashes because the underlying join object has already been deleted (tab->join->have_query_plan == JOIN::QEP_DELETED)

One must have Optimizer Trace enabled, because we crash when trying to print an item into the trace.

Circumstances leading to the crash

The subquery's JOIN is cleaned here:

  #0  JOIN::cleanup (this=0x7fff74026d78, full=true) at /home/psergey/dev-git2/10.4-review3/sql/sql_select.cc:14033
 
  #1  0x0000555555dfb05b in JOIN::destroy (this=0x7fff74026d78) at /home/psergey/dev-git2/10.4-review3/sql/sql_select.cc:4603
 
  #2  0x0000555555eb9aa5 in st_select_lex::cleanup (this=0x7fff74016d28) at /home/psergey/dev-git2/10.4-review3/sql/sql_union.cc:2160
 
  #3  0x0000555555eb91d6 in st_select_lex_unit::cleanup (this=0x7fff74019c48) at /home/psergey/dev-git2/10.4-review3/sql/sql_union.cc:1
 
  #4  0x0000555555d4ded4 in mysql_derived_fill (thd=0x7fff74000d50, lex=0x7fff74004bc8, derived=0x7fff7401a580) at /home/psergey/dev-gi
 
  #5  0x0000555555d4d46b in mysql_derived_optimize (thd=0x7fff74000d50, lex=0x7fff74004bc8, derived=0x7fff7401a580) at /home/psergey/de
 
  #6  0x0000555555d4ad3f in mysql_handle_single_derived (lex=0x7fff74004bc8, derived=0x7fff7401a580, phases=4) at /home/psergey/dev-git
 
  #7  0x0000555555df1c1a in JOIN::optimize_inner (this=0x7fff74026720) at /home/psergey/dev-git2/10.4-review3/sql/sql_select.cc:2175
 
  #8  0x0000555555defe90 in JOIN::optimize (this=0x7fff74026720) at /home/psergey/dev-git2/10.4-review3/sql/sql_select.cc:1685
 
  #9  0x0000555555d72c91 in st_select_lex::optimize_unflattened_subqueries (this=0x7fff74015970, const_only=true) at /home/psergey/dev-
 
  #10 0x0000555555f9e32d in JOIN::optimize_constant_subqueries (this=0x7fff74025cc8) at /home/psergey/dev-git2/10.4-review3/sql/opt_sub
 
  #11 0x0000555555df0f94 in JOIN::optimize_inner (this=0x7fff74025cc8) at /home/psergey/dev-git2/10.4-review3/sql/sql_select.cc:2022
 
  #12 0x0000555555defe90 in JOIN::optimize (this=0x7fff74025cc8) at /home/psergey/dev-git2/10.4-review3/sql/sql_select.cc:1685
 
  #13 0x0000555555dfb6b3 in mysql_select (thd=0x7fff74000d50, tables=0x7fff74015f50, wild_num=1, fields=@0x7fff74015ab8: {<base_list> =
 
  #14 0x0000555555deabaa in handle_select (thd=0x7fff74000d50, lex=0x7fff74004bc8, result=0x7fff7401b7b0, setup_tables_done_option=0)

A few frames up in mysql_derived_fill():

  err:
 
6   if (res || (!derived_is_recursive && !lex->describe && !unit->uncacheable))                                                        
=>    unit->cleanup();

So, we need an uncorrelated subquery.
The statement also should not be an EXPLAIN (DONT-FREE-IN-EXPLAIN)

one more frame up in mysql_derived_optimize: 

    /*  
      Materialize derived tables/views of the "SELECT a_constant" type.
 
      Such tables should be materialized at the optimization phase for
 
      correct constant evaluation.
 
    */    
    if (!res && derived->fill_me && !derived->merged_for_insert)
 
    {       
  ...
 
=>    if ((res= mysql_derived_fill(thd, lex, derived)))

So, the uncorrelated subquery must be inside a derived table that's constant.

Comment by Sergei Petrunia [ 2023-02-22 ]

Note the DONT-FREE-IN-EXPLAIN. EXPLAIN EXTENDED does print the query on statement end, but it avoids this kind of crash because it delays freeing the subqueries.

Comment by Sergei Petrunia [ 2023-02-22 ]

An obvious case which Johnston's patch doesn't fix: make the index unique: 

set optimizer_trace=1;
 
create table t10 (a int, b int) engine=myisam;
 
create table t11 (a int, b int, unique(a));
 
insert into t11 select seq, seq from seq_1_to_10;
 
 
 
create table t12 (a int, key(a));
 
insert into t12 select seq from seq_1_to_1000;
 
 
 
set optimizer_trace=1;
 
select * from t12 where t12.a < (
 
select b from  (
 
  select b from t10
 
  where 
    a <3 or a in (select a from t11 where t11.b<t10.b)
 
  limit 2
 
) T);

and now the crash will be in subselect_uniquesubquery_engine::print() instead...

Comment by Sergei Petrunia [ 2023-02-22 ]

... what about other kinds of subselect*engine ?
single select engine (the most common) doesn't seem to depend on the query plan:

  void subselect_single_select_engine::print(String *str,
 
                                             enum_query_type query_type)
 
  {
 
    With_clause* with_clause= select_lex->get_with_clause();
 
    if (with_clause)
 
      with_clause->print(str, query_type);
 
    select_lex->print(get_thd(), str, query_type);
 
  }

Comment by Sergei Petrunia [ 2023-02-22 ]

Review input provided in https://github.com/MariaDB/server/pull/2469

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