Description:
on a query that contains a WITH clause (CTE), where the CTE body uses a row value
constructor in an IN subquery (e.g., WHERE (col1, col2, col3) IN (SELECT ...)).
The crash occurs inside TABLE_LIST::query_block_id_for_explain(), which is invoked
during the EXPLAIN print phase traversing the WITH clause. The likely cause is a
NULL pointer dereference: at the time EXPLAIN generates its output, the CTE's
TABLE_LIST object has not been fully initialized or its query_block pointer is
already invalid.
The same query executes successfully without EXPLAIN. The crash is 100%
reproducible when EXPLAIN is prepended to the affected query.
Environment:
- Aurora MySQL 3.04.4 (compatible with MySQL 8.0.28)
- Client: DBeaver 22.0.3
- Crash time: 05:45:51 UTC
Stack trace at crash point:
TABLE_LIST::query_block_id_for_explain() const
PT_common_table_expr::print(THD const*, String*, enum_query_type)
PT_with_clause::print(THD const*, String*, enum_query_type)
Query_expression::print(THD const*, String*, enum_query_type)
explain_query(THD*, THD const*, Query_expression*)
Sql_cmd_dml::execute(THD*)
mysql_execute_command_internal(THD*, bool)
dispatch_sql_command(THD*, Parser_state*)
dispatch_command(THD*, COM_DATA const*, enum_server_command)
do_command(THD*)
This crash appears to share the same root cause as Bug #84566
(TABLE_LIST::query_block_id_for_explain crash during EXPLAIN FOR CONNECTION).
However, Bug #84566 was reportedly fixed in MySQL 8.0.1 (dev), and this crash
is observed on MySQL 8.0.28 / Aurora MySQL 3.04.4, suggesting either a regression
or a separate code path with the same symptom.
Questions to the MySQL team:
1. Is this the same root cause as Bug #84566, or a separate regression?
2. What is the minimum MySQL version in which this crash is confirmed fixed?
How to repeat:
The exact reproduction case cannot be shared due to confidentiality of
production schema and data. However, the crash was confirmed to occur
consistently under the following conditions:
== Confirmed Environment ==
- Aurora MySQL 3.04.4 (compatible with MySQL 8.0.28)
- Client: DBeaver 22.0.3
== Query Pattern That Triggers the Crash ==
The crash occurs when ALL of the following conditions are met:
1. Query uses a WITH clause (CTE)
2. The CTE body contains a row value constructor IN subquery:
WHERE (col1, col2, col3) IN (SELECT ...)
3. The IN subquery involves multi-table JOINs (3+ tables)
4. An optimizer hint is present inside the CTE: /*+ JOIN_FIXED_ORDER() */
5. The statement is prefixed with EXPLAIN
== Structural Pattern ==
EXPLAIN
WITH cte AS (
SELECT /*+ JOIN_FIXED_ORDER() */ DISTINCT t.col_a
FROM table_map t
WHERE (t.col1, t.col2, t.col3) IN (
SELECT DISTINCT a.col1, b.col2, c.col3
FROM table_a a
JOIN table_b b ON a.col1 = b.col1
JOIN table_c c ON b.col1 = c.col1 AND b.col2 = c.col2
WHERE a.type = 'X'
AND b.type = 'Y'
AND c.type = 'Z'
AND a.col1 IN (...)
)
)
SELECT * FROM cte;
== Observed Behavior ==
- Without EXPLAIN: query executes successfully
- With EXPLAIN : mysqld crashes immediately with Signal 11 (SIGSEGV)
- Aurora automatic failover is triggered upon crash
== Note ==
A simplified test case without production-scale statistics and indexes
may not reliably trigger the crash, as the optimizer must select a
specific execution path to reach the crashing code in
TABLE_LIST::query_block_id_for_explain().
The full stack trace is provided in the Description section.
