MySQL Bugs: #107447: Delete-select not working with temporary tables

Bug #107447	Delete-select not working with temporary tables
Submitted:	1 Jun 2022 15:25	Modified:	25 Oct 2023 0:25
Reporter:	Davide D'Alto	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.29	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any

Description:
When running an SQL query of the this kind:
<pre><code>
DELETE FROM Book WHERE (id) IN (SELECT id FROM ht_Book)
</code></pre>

And ht_Book is a temporary table, the delete query is not going to match any id even if the table has been populated.
This works with MySQL 8.0.28 but it doesn't work with MySQL 8.0.29 when that cache is enabled: connectionProps.put("cachePrepStmts", "true");.

How to repeat:
I've created a testcase on my GitHub repository: https://github.com/DavideD/mysql-issue

The test uses testcontainers, docker and the mysql JDBC driver 8.0.29.
It runs the same tests with MySQL 8.0.28 and 8.0.29 with the cache enabled/disabled.

Suggested fix:
I don't have a fix but disabling the cache (connectionProps.put("cachePrepStmts", "false");) will work as a workaround.

Actually, it seems the delete fails because the temporary table is empty after the SQL insert.

Anyway, the test I've attached should help you figure out what's going on.

Hi Mr. Alto,

We need a fully repeatable test case, not just description. A test case should consist of the series of SQL statement, that can be run in the batch. Please, read further on .....

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php 

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.  

Thank you for your interest in MySQL.

Hi,

We are aware of the issue. It is related with the new server code and how connector/j use server prepared statements.

changing:
 connectionProps.put("useServerPrepStmts", "true");

to
 connectionProps.put("useServerPrepStmts", "false");

should solve your problem

At the moment there is no known path for this to be fixed in the near future so proper status of this bug might be "unsupported" but I will verify it for now.

Thank you for your report

This was caused by the MySQL Server Bug#107230, that is fixed in the version 8.0.30, as such, this behavior can't be reproduced anymore.