Description:
A single SELECT statement over a very small dataset causes mysqld memory usage to grow rapidly on MySQL 8.0.46. The query does not finish within the observation window and eventually hits the OS/cgroup memory limit. No server crash stack is printed; this is a memory blow-up / non-termination issue rather than a SIGSEGV.
The reproducer has been reduced from an original 12,812-byte query to a 1,770-byte query. The current reduced reproducer still requires a CTE plus duplicated correlated scalar subqueries. Removing either member of the duplicated correlated scalar subquery pair makes the problem disappear.
Impact:
A single SELECT query over a small schema can consume many GB of mysqld memory. Without an external memory limit, it can put the server or host at risk of OOM. This is particularly problematic because the issue does not require large input data.
How to repeat:
Use a disposable MySQL instance. Running with an OS/cgroup memory limit is strongly recommended.
unzip mysql-8046-memory-blowup-repro.zip
cd mysql-8046-memory-blowup-repro
MYSQL="mysql -uroot" ./run_repro.sh
Equivalent manual steps:
mysql -uroot -e "DROP DATABASE IF EXISTS bug8046_memory_blowup; CREATE DATABASE bug8046_memory_blowup;"
mysql -uroot bug8046_memory_blowup < schema.sql
mysql -uroot bug8046_memory_blowup < trigger.sql
Expected result:
The query should either finish, be optimized into a reasonable execution plan, or fail with a controlled diagnostic. It should not consume multiple GB of memory on a small dataset.
Actual result:
On MySQL 8.0.46, mysqld memory usage grows rapidly and the query does not finish. In one recent confirmation run, mysqld memory usage grew from roughly 456 MB to more than 7.5 GB almost immediately after executing the reduced query. Previous runs with the original query reached a 12 GB memory cap, and runs without a strict cap reached tens of GB before the process was killed by the OS.
No "mysqld got signal" stack was printed in the error log. The process is killed only when the OS/cgroup memory limit is reached.
Suggested fix:
Please investigate the optimizer/executor memory accounting and plan generation path for CTEs containing duplicated correlated scalar subqueries. The reduced trigger pattern is:
1. A CTE whose FROM clause contains a derived table.
2. The derived table select list contains a scalar subquery with ORDER BY ... LIMIT.
3. The CTE WHERE clause compares two identical correlated scalar subqueries with =.
4. The outer query references the same CTE again via derived/lateral joins.
5. Another nested WHERE clause repeats the same duplicated correlated scalar-subquery pattern against the CTE.
© 2026, Oracle Corporation and/or its affiliates