MySQL Bugs: #121514: Server crash with derived table, NOT IN, and temporal subtraction in WHERE clause

Bug #121514	Server crash with derived table, NOT IN, and temporal subtraction in WHERE clause
Submitted:	15 Oct 7:21	Modified:	16 Oct 7:51
Reporter:	Hao Wang	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	8.0.36	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A SELECT query involving nested subqueries with `NOT IN`, a derived table, and a temporal `INTERVAL` expression in the WHERE clause triggers a segmentation fault. The crash occurs during the query optimization phase, specifically in `Item_ref::result_type()`, called from `types_allow_materialization()`. I tried executing it on an older version 8.0.31 and no crash occurred, but it can be reproduced on 8.0.36.

How to repeat:
mysql> create database d1;
Query OK, 1 row affected (0.02 sec)

mysql> use d1;
Database changed

mysql> CREATE TABLE `t1` (  `c2` tinytext NOT NULL,  PRIMARY KEY (`c2`(3)),  UNIQUE KEY `c1` (`c2`(3))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT c2 FROM t1 WHERE (c2 IS NOT NULL) NOT IN (SELECT c2 FROM (SELECT c2 FROM t1) AS ta2 WHERE c2 IN (SELECT c2 FROM t1 WHERE ('19 20' - INTERVAL '09 15:00' DAY_MINUTE)) && (c2 IS NOT NULL));
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3307' (111)
ERROR: 
Can't connect to the server

Backtrace (from error log):
stack_bottom = 722c50110bb0 thread_stack 0x100000
/usr/local/mysql8.0.36/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x4c) [0x5bf884b2796c]
/usr/local/mysql8.0.36/bin/mysqld(print_fatal_signal(int)+0x391) [0x5bf882edc261]
/usr/local/mysql8.0.36/bin/mysqld(handle_fatal_signal+0x61) [0x5bf882edc421]
/lib/x86_64-linux-gnu/libc.so.6(+0x42520) [0x722c77c42520]
/usr/local/mysql8.0.36/bin/mysqld(Item_ref::result_type() const+0x2b) [0x5bf88312514b]
/usr/local/mysql8.0.36/bin/mysqld(types_allow_materialization(Item*, Item*)+0x46) [0x5bf882cf07c6]
/usr/local/mysql8.0.36/bin/mysqld(+0x120620f) [0x5bf882c2520f]
/usr/local/mysql8.0.36/bin/mysqld(JOIN::make_join_plan()+0x1f70) [0x5bf882c0adf0]
/usr/local/mysql8.0.36/bin/mysqld(JOIN::optimize(bool)+0x137a) [0x5bf882c045ea]
/usr/local/mysql8.0.36/bin/mysqld(Query_block::optimize(THD*, bool)+0x1e5) [0x5bf882cf3565]
/usr/local/mysql8.0.36/bin/mysqld(Query_expression::optimize(THD*, TABLE*, bool, bool)+0xfe) [0x5bf882dc8bce]
/usr/local/mysql8.0.36/bin/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x4c) [0x5bf882cef46c]
/usr/local/mysql8.0.36/bin/mysqld(Sql_cmd_dml::execute(THD*)+0x651) [0x5bf882cee361]
/usr/local/mysql8.0.36/bin/mysqld(mysql_execute_command(THD*, bool)+0x46ba) [0x5bf882c484ca]
/usr/local/mysql8.0.36/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x88c) [0x5bf882c4102c]
/usr/local/mysql8.0.36/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x3466) [0x5bf882c3e5e6]
/usr/local/mysql8.0.36/bin/mysqld(do_command(THD*)+0x37c) [0x5bf882c3f82c]
/usr/local/mysql8.0.36/bin/mysqld(+0x149cc14) [0x5bf882ebbc14]
/usr/local/mysql8.0.36/bin/mysqld(+0x38e023b) [0x5bf8852ff23b]
/lib/x86_64-linux-gnu/libc.so.6(+0x94a83) [0x722c77c94a83]
/lib/x86_64-linux-gnu/libc.so.6(+0x1268e0) [0x722c77d268e0]

Thank you for the bug report.
However, this problem is not reproducible in any our supported versions, including the latest 8.0 release.

This may be a version-specific issue that only occurs in 8.0.36, as I was unable to reproduce it on the older version 8.0.31 or the newer version 8.0.46. For MySQL 8.0.36, I also tried to reproduce it on Windows, and the result was the same as on Ubuntu — it triggered a mysqld service crash in both cases. The crash log is as follows:
2026-10-15T06:37:07Z UTC - mysqld got exception 0xc0000005 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x28a2501f280
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff61643ad2a    mysqld.exe!?result_type@Item_ref@@UEBA?AW4Item_result@@XZ()
7ff61664931d    mysqld.exe!?types_allow_materialization@@YA_NPEAVItem@@0@Z()
7ff6166ca101    mysqld.exe!?replace_index_subquery@JOIN@@AEAAHXZ()
7ff6166c7daa    mysqld.exe!?optimize_rollup@JOIN@@QEAA_NXZ()
7ff6166c4119    mysqld.exe!?make_join_plan@JOIN@@AEAA_NXZ()
7ff6166c63b4    mysqld.exe!?optimize@JOIN@@QEAA_N_N@Z()
7ff6166460b9    mysqld.exe!?optimize@Query_block@@QEAA_NPEAVTHD@@_N@Z()
7ff616668a23    mysqld.exe!?optimize@Query_expression@@QEAA_NPEAVTHD@@PEAUTABLE@@_N2@Z()
7ff616642f80    mysqld.exe!?execute_inner@Sql_cmd_dml@@MEAA_NPEAVTHD@@@Z()
7ff616642e6e    mysqld.exe!?execute@Sql_cmd_dml@@UEAA_NPEAVTHD@@@Z()
7ff616596d5a    mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
7ff616591b1f    mysqld.exe!?dispatch_sql_command@@YAXPEAVTHD@@PEAVParser_state@@@Z()
7ff61659071a    mysqld.exe!?dispatch_command@@YA_NPEAVTHD@@PEBTCOM_DATA@@W4enum_server_command@@@Z()
7ff616591ed6    mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
7ff616399d98    mysqld.exe!?thread_id@THD@@QEBAIXZ()
7ff6179ed119    mysqld.exe!?my_init_dynamic_array@@YA_NPEAUDYNAMIC_ARRAY@@IIPEAXII@Z()
7ff61745ac7c    mysqld.exe!?my_thread_self_setname@@YAXPEBD@Z()
7ffc71011bb2    ucrtbase.dll!_configthreadlocale()
7ffc72e07374    KERNEL32.DLL!BaseThreadInitThunk()
7ffc738fcc91    ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (28a47f82830): SELECT c2 FROM t1 WHERE (c2 IS NOT NULL) NOT IN (SELECT c2 FROM (SELECT c2 FROM t1) AS ta2 WHERE c2 IN (SELECT c2 FROM t1 WHERE ('19 20' - INTERVAL '09 15:00' DAY_MINUTE)) && (c2 IS NOT NULL))
Connection ID (thread ID): 42
Status: NOT_KILLED

Thank you for your response and for looking into this report. I would like to provide some additional information regarding the version scope of this issue.

From MySQL 8.0.32 to 8.0.37, executing the reproduction steps I provided on Ubuntu 22.04 consistently caused the server to crash.

mysql> SELECT c2 FROM t1 WHERE (c2 IS NOT NULL) NOT IN (SELECT c2 FROM (SELECT c2 FROM t1) AS ta2 WHERE c2 IN (SELECT c2 FROM t1 WHERE ('19 20' - INTERVAL '09 15:00' DAY_MINUTE)) && (c2 IS NOT NULL));
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...

On MySQL 8.0.31, the result is as follows, with no crash occurring.

mysql> SELECT c2 FROM t1 WHERE (c2 IS NOT NULL) NOT IN (SELECT c2 FROM (SELECT c2 FROM t1) AS ta2 WHERE c2 IN (SELECT c2 FROM t1 WHERE ('19 20' - INTERVAL '09 15:00' DAY_MINUTE)) && (c2 IS NOT NULL));
Empty set, 2 warnings (0.00 sec)

The crash can be reproduced on 8.0.32 through 8.0.37, but not on 8.0.31. I also tested 8.0.46, and the issue does not occur there either.

I would appreciate it if you could re-evaluate this report or let me know if there is any additional information I can provide to help.

Thank you for your time and support.

Hi Hao,

there is actually nothing left for me to do.

Release 8.0 is no longer supported by us, and the problem is not present in supported versions, ie. 8.4, 9.7 and 26.x.

The problem is not even present in the most recent 8.0 release that we have provided, thus the advice for any users on an affected old 8.0 version would be to upgrade to the most recent 8.0 release. Or, preferably, to a newer major version.

> Status: Unsupported

Above is probably sub-optimal, but I understand why it was set like this.

> This may be a version-specific issue that only occurs in 8.0.36, as I was unable to reproduce it on the older version 8.0.31 or the newer version 8.0.46.
>
> From MySQL 8.0.32 to 8.0.37, executing the reproduction steps I provided on Ubuntu 22.04 consistently caused the server to crash.

From above, I am guessing it is not possible to reproduce in 8.0.38 (or 8.0.39 as .38 was retired).  So it looks like something in 8.0.38 fixed this.

The best Status would probably be Duplicate with pointing to the bug number fixing this in 8.0.38, but I understand Oracle not wanting to spend more time on this and setting to Unsupported.

Hao Wang: maybe you can check the Release Notes of 8.0.38 for a note about the fix (link in [1] below).  If you do not find anything, doing a "git bisect" might allow you to find the commit fixing this (Release Notes are know to sometimes omit things, see [2] below).  Once you have the commit and assuming you are stuck on 8.0.36 and you want this bug fixed, you can evaluate if it is worth back-porting the fix yourself in a custom version you would build yourself.

[1]: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-38.html

[2]: https://www.linkedin.com/posts/jfg956_mysql-mysql-share-7494013493344391169-IrqZ/

> there is actually nothing left for me to do

I agree with this: Oracle should not spend time on something not reproducible in 8.4-latest.  They even tested with 8.0-latest, which is more than what was expected from them.

> I would appreciate it if you could re-evaluate this report or let me know if there is any additional information I can provide to help.

Hao Wang: unclear what re-evaluation you would like to happen here.  If you want to discuss this, feel free to ping me in MySQL Community Slack [3] (I am @jfgagne in there) or contact me via another method in [4].  We probably should not exchange more in here as this report is not affecting latest.

[3]: https://slack.lefred.be/

[4]: https://linktr.ee/jfg.mysql