MySQL Bugs: #115212: duplicates_removal weedout semijoin crashes mysql8

Bug #115212	duplicates_removal weedout semijoin crashes mysql8
Submitted:	4 Jun 2024 13:19	Modified:	5 Jun 2024 8:14
Reporter:	Bastiaan Peters	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.36	OS:	Debian
Assigned to:		CPU Architecture:	Any
Tags:	semijoin weedout exists

Description:
When we run a complex query that has a subquery like

EXISTS ( SELECT 1 FROM TABLE_X ) 

this sometimes triggers a query plan that involves

          "duplicates_removal": {
            "using_temporary_table": true,
            "nested_loop": [

This crashes mysql8 with this stackstrace:

	
/usr/sbin/mysqld(temptable::Handler::rnd_pos(unsigned char*, unsigned char*)+0x5f) [0x55b332964cdf]
/usr/sbin/mysqld(handler::ha_rnd_pos(unsigned char*, unsigned char*)+0x1b4) [0x55b331201d24]
/usr/sbin/mysqld(SortBufferIndirectIterator::Read()+0xa4) [0x55b3313498d4]
/usr/sbin/mysqld(NestedLoopIterator::Read()+0xb7) [0x55b3314af857]
[.. repeated NestedLoopIterator::Read ..]
/usr/sbin/mysqld(NestedLoopIterator::Read()+0xb7) [0x55b3314af857]
/usr/sbin/mysqld(WeedoutIterator::Read()+0x1b) [0x55b3314afd5b]

We could not devise a straight-forward way to reproduce it. Running the entire testsuite against a local docker system running phpunit and mysql8 docker image will start crashing mysql. Whever it crashes, it was using the mentioned query plan. When it does not crash it uses a different plan.

If we change the EXISTS construct to the functionally equivalent:

		AND (
            SELECT COUNT(*)
            FROM ...
          ) > 0

The problem goes away.
 
Another workaround is to disable the duplicateweedout semijoin strategy. This seems undesirable in terms of performance.

How to repeat:
We could not devise a straight-forward way to reproduce it. Running the entire testsuite against a local docker system running phpunit and mysql8 docker image will start crashing mysql. Whever it crashes, it was using the mentioned query plan. When it does not crash it uses a different plan.

Stacktrace

Attachment: stacktrace (application/octet-stream, text), 2.54 KiB.

The offending query

Attachment: offending query (application/octet-stream, text), 2.87 KiB.

The JSON query plan for the offending query, as it was before crashing the server

Attachment: offending json plan (application/octet-stream, text), 10.29 KiB.

Hi Mr. Peters,

Thank you so much for your bug report.

However, we have to inform you that this is strictly a forum for the reports with fully reproducible test cases.

Such a test case should always consist of the set of SQL statements that always leads to the bug that you are reporting.

We can not verify a report without the fully reproducible test case, for the simple reason that only such a test case would give us a chance to find the. cause of the bug.

We are eagerly waiting for your test case, as described above.

Can't repeat.

I expected this. I'm putting in the bugreport regardless so people that are hit by the same issue find it and might come up with a reproduction their instance of it.

I'm not sure I agree with S3 though, after all this is crashing mysql 8.0.36 on legal SQL input.

Hi Mr. Peters,

We can not find a cause of the bug without a fully reproducible test case.

We are very interested in solving this bug.

But, truly how can we solve it without being able to run a test case and get a crash. Then we shall know where to set breakpoints and watchpoints and then proceed to fixing the bug.

Without the test case, we have our hands tied.

If you provide a repeatable test case and we reproduce it, not only that this will become S2, but it will also become a Security Vulnerability bug. Those bugs are fixed faster then the other ones.

Can't repeat.