[MDEV-31279] Crash when lateral derived is guaranteed to return no rows Created: 2023-05-15  Updated: 2024-01-09  Resolved: 2023-12-13

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3, 11.3.2, 11.4.1

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Rex Johnston
Resolution: Fixed Votes: 1
Labels: None


 Description   

I only found MDEV-26835 as possibly related, but it's still too different and there is no tentative patch to check if it fixes both. If it turns out to be the same issue, feel free to close this one as a duplicate.

--source include/have_sequence.inc
 
 
 
CREATE TABLE t1 (a CHAR(1)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES ('1'),('2');
 
 
 
CREATE TABLE t2 (b INT, KEY(b)) ENGINE=MyISAM;
 
ALTER TABLE t2 DISABLE KEYS;
 
INSERT INTO t2 VALUES (1),(2),(3);
 
ALTER TABLE t2 ENABLE KEYS;
 
 
 
CREATE TABLE t3 (c INT) ENGINE=MyISAM;
 
INSERT INTO t3 (c) SELECT seq FROM seq_1_to_101;
 
 
 
SELECT * FROM t1 WHERE t1.a IN (SELECT b FROM (SELECT t2.b FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3) GROUP BY b) sq);
 
 
 
# Cleanup
 
DROP TABLE t1, t2, t3;

10.3 ca001cf2

 
#3  <signal handler called>
 
#4  0x00005589cdb37545 in heap_clear (info=0x0) at /data/src/10.3/storage/heap/hp_clear.c:27
 
#5  0x00005589cdb1d426 in ha_heap::delete_all_rows (this=0x61a0000198a8) at /data/src/10.3/storage/heap/ha_heap.cc:404
 
#6  0x00005589cd604e5d in handler::ha_delete_all_rows (this=0x61a0000198a8) at /data/src/10.3/sql/handler.cc:4451
 
#7  0x00005589ccdc5693 in mysql_derived_fill (thd=0x62a0000ba208, lex=0x62a0000be000, derived=0x62b0000047e8) at /data/src/10.3/sql/sql_derived.cc:1151
 
#8  0x00005589ccdbf3e7 in mysql_handle_single_derived (lex=0x62a0000be000, derived=0x62b0000047e8, phases=96) at /data/src/10.3/sql/sql_derived.cc:193
 
#9  0x00005589ccf877f6 in st_join_table::preread_init (this=0x6290001393d8) at /data/src/10.3/sql/sql_select.cc:12982
 
#10 0x00005589ccfb6a10 in sub_select (join=0x62b000005250, join_tab=0x6290001393d8, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:19920
 
#11 0x00005589ccfb86a7 in evaluate_join_record (join=0x62b000005250, join_tab=0x629000139048, error=0) at /data/src/10.3/sql/sql_select.cc:20179
 
#12 0x00005589ccfb75fe in sub_select (join=0x62b000005250, join_tab=0x629000139048, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:19991
 
#13 0x00005589ccfb4fe9 in do_select (join=0x62b000005250, procedure=0x0) at /data/src/10.3/sql/sql_select.cc:19490
 
#14 0x00005589ccf48da5 in JOIN::exec_inner (this=0x62b000005250) at /data/src/10.3/sql/sql_select.cc:4190
 
#15 0x00005589ccf46750 in JOIN::exec (this=0x62b000005250) at /data/src/10.3/sql/sql_select.cc:3984
 
#16 0x00005589ccf4a0e2 in mysql_select (thd=0x62a0000ba208, tables=0x62b000000518, wild_num=1, fields=..., conds=0x62b000004e90, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x62b000005220, unit=0x62a0000be0c0, select_lex=0x62a0000be880) at /data/src/10.3/sql/sql_select.cc:4393
 
#17 0x00005589ccf2005b in handle_select (thd=0x62a0000ba208, lex=0x62a0000be000, result=0x62b000005220, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:372
 
#18 0x00005589cce946e6 in execute_sqlcom_select (thd=0x62a0000ba208, all_tables=0x62b000000518) at /data/src/10.3/sql/sql_parse.cc:6340
 
#19 0x00005589cce824b7 in mysql_execute_command (thd=0x62a0000ba208) at /data/src/10.3/sql/sql_parse.cc:3871
 
#20 0x00005589cce9e180 in mysql_parse (thd=0x62a0000ba208, rawbuf=0x62b000000228 "SELECT * FROM t1 WHERE t1.a IN (SELECT b FROM (SELECT t2.b FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3) GROUP BY b) sq)", length=118, parser_state=0x7fa4a35699b0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7855
 
#21 0x00005589cce74faf in dispatch_command (command=COM_QUERY, thd=0x62a0000ba208, packet=0x629000127209 "", packet_length=118, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1852
 
#22 0x00005589cce71b47 in do_command (thd=0x62a0000ba208) at /data/src/10.3/sql/sql_parse.cc:1398
 
#23 0x00005589cd239f5c in do_handle_one_connection (connect=0x6080000008a8) at /data/src/10.3/sql/sql_connect.cc:1404
 
#24 0x00005589cd239889 in handle_one_connection (arg=0x6080000008a8) at /data/src/10.3/sql/sql_connect.cc:1309
 
#25 0x00005589ce820bd5 in pfs_spawn_thread (arg=0x615000005808) at /data/src/10.3/storage/perfschema/pfs.cc:1869
 
#26 0x00007fa4b80a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#27 0x00007fa4b81285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

Reproducible with MyISAM and Aria, but not with InnoDB (possibly because of DISABLE/ENABLE KEYS which is somehow important there).
Reproducible on all existing versions, including earlier minor releases, debug- and non-debug alike.

Plan:

EXPLAIN EXTENDED
 
SELECT * FROM t1 WHERE t1.a IN (SELECT b FROM (SELECT t2.b FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3) GROUP BY b) sq);
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 
1	PRIMARY	<derived3>	ref	key0	key0	5	test.t1.a	2	100.00	Using where; FirstMatch(t1)
 
3	LATERAL DERIVED	t2	ref	b	b	5	test.t1.a	1	100.00	Using where; Using index; Using temporary; Using filesort
 
4	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	101	100.00	
Warnings:
 
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <cache>(!<in_optimizer>(1,exists(/* select#4 */ select 1 from `test`.`t3` limit 1))) and `test`.`t2`.`b` = `test`.`t1`.`a` group by `test`.`t2`.`b`) `sq`) where `test`.`t1`.`a` = `sq`.`b`



 Comments   
Comment by Rex Johnston [ 2023-09-18 ]

The problem query 

SELECT * FROM t1 WHERE t1.a IN (SELECT b FROM (SELECT t2.b FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3) GROUP BY b) sq)

can (and under normal circumstances, is) rewritten to

SELECT t1.* FROM t1, (SELECT t2.b FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3) GROUP BY b) sq where sq.b = t1.a;

This is a candidate for lateral derived optimization (for the materialized derived table sq).

explain SELECT t1.* FROM t1, (SELECT t2.b FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3) GROUP BY b) sq where sq.b = t1.a;
 
+------+-----------------+------------+------+---------------+------+---------+-----------+------+-----------------------------------------------------------+
 
| id   | select_type     | table      | type | possible_keys | key  | key_len | ref       | rows | Extra                                                     |
 
+------+-----------------+------------+------+---------------+------+---------+-----------+------+-----------------------------------------------------------+
 
|    1 | PRIMARY         | t1         | ALL  | NULL          | NULL | NULL    | NULL      | 2    | Using where                                               |
 
|    1 | PRIMARY         | <derived2> | ref  | key0          | key0 | 5       | test.t1.a | 2    | Using where                                               |
 
|    2 | LATERAL DERIVED | t2         | ref  | b             | b    | 5       | test.t1.a | 1    | Using where; Using index; Using temporary; Using filesort |
 
|    3 | SUBQUERY        | t3         | ALL  | NULL          | NULL | NULL    | NULL      | 101  |                                                           |
 
+------+-----------------+------------+------+---------------+------+---------+-----------+------+-----------------------------------------------------------+

Note that if rows in the subquery are estimated to be <= thd->variables.expensive_subquery_limit (100), then this subquery is deemed not 'expensive' and directly materialized.
Direct materialization does not induce this issue. Any subquery deemed expensive, producing zero rows, but still a candidate for lateral derivation will cause this issue.

The select_lex for the subquery sq is now 

select t2.b AS b from t2 where <cache>(!<in_optimizer>(1,exists(subquery#4))) and t2.b = t1.a group by t2.b

During the first evaluation of this table in mysql_derived_fill() / mysql_select() / JOIN::exec / JOIN::exec_inner,the exec_const_cond (set it JOIN::choose_tableless_subquery_plan() during query optimization)
flag, causes the subselect in the item tree to be evaluated for the first time.

  /*
 
    Evaluate expensive constant conditions that were not evaluated during
 
    optimization. Do not evaluate them for EXPLAIN statements as these
 
    condtions may be arbitrarily costly, and because the optimize phase
 
    might not have produced a complete executable plan for EXPLAINs.
 
  */
 
  if (!zero_result_cause &&
 
      exec_const_cond && !(select_options & SELECT_DESCRIBE) &&
 
      !exec_const_cond->val_int())
 
    zero_result_cause= "Impossible WHERE noticed after reading const tables";

It is noted that exec_const_cond->val_int(), which represents the expression 

WHERE NOT EXISTS (SELECT 1 FROM t3)

is zero, setting zero_result_cause for this join to "Impossible WHERE noticed after reading const tables".

This bypasses the rest of JOIN::exec_inner(), which would normally open the handler in sending result sets to the relevant handler

do_select/sub_select/evaluate_join_record/sub_select_postjoin_aggr/AGGR_OP::put_record/AGGR_OP::put_record/AGGR_OP::prepare_tmp_table/instantiate_tmp_table/open_tmp_table/handler::ha_open

Thus initializing the heap table->file for the derived table isn't done.

Being a lateral derived table, mysql_derived_fill() is called for the next result group, and as part of this operation, results from the previous result set are cleared out.

  if (unit->executed && !derived_is_recursive &&
 
      (unit->uncacheable & UNCACHEABLE_DEPENDENT))
 
  {
 
    if ((res= derived->table->file->ha_delete_all_rows()))
 
      goto err;
 
    JOIN *join= unit->first_select()->join;
 
    join->first_record= false;
 
    for (uint i= join->top_join_tab_count;
 
         i < join->top_join_tab_count + join->aggr_tables;
 
         i++)
 
    { 
      if ((res= join->join_tab[i].table->file->ha_delete_all_rows()))
 
        goto err;
 
    }   
  }

all is OK until ha_delete_all_rows() is called on the join for the derived table sq attempting to tidy up that which was never materialized in the first place.
The derived table join is in this joins jointab array.

We could either test for the handler actually being opened, via table->file->is_open() or checking the string/flag zero_result_cause, which caused this chain of events.
The latter, being earlier in the execution path is used to detect whether we should try and clear result set rows in mysql_derived_fill().

Comment by Igor Babaev [ 2023-12-13 ]

Ok to push the latest commit into 10.4

Comment by Rex Johnston [ 2023-12-13 ]

Thank you Igor.

Comment by Sergei Petrunia [ 2024-01-08 ]

Note for the changelog:

A query with derived table which A) has a GROUP BY clause and B) produces no rows can cause a crash if the optimizer uses Lateral Derived Optimization for it.

Comment by Igor Babaev [ 2024-01-08 ]

Note for the changelog:

A query with derived table which A) has a GROUP BY clause and B) produces no rows can cause a crash if the optimizer uses the default setting optimizer_switch='split_materialized=on'.

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