Rex Johnston added a comment - 2023-09-18 04:21 - edited 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().

Igor Babaev (Inactive) added a comment - 2023-12-13 18:50 Ok to push the latest commit into 10.4

Rex Johnston added a comment - 2023-12-13 18:52 Thank you Igor.

Sergei Petrunia added a comment - 2024-01-08 10:49 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.

Igor Babaev (Inactive) added a comment - 2024-01-08 17:17 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'.