Bug #107447 Delete-select not working with temporary tables
Submitted: 1 Jun 15:25 Modified: 2 Jun 16:04
Reporter: Davide D'Alto Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.29 OS:Any
Assigned to: CPU Architecture:Any

[1 Jun 15:25] Davide D'Alto
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.
[1 Jun 22:23] Davide D'Alto
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.
[2 Jun 13:37] MySQL Verification Team
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.
[2 Jun 16:04] MySQL Verification Team
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