Bug #109377 rewriteBatchedStatements doesn't work when parenthesis are found in values
Submitted: 14 Dec 2022 13:36 Modified: 20 Mar 16:29
Reporter: Carl Nyströmer Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.30, 8.0.31 OS:Any
Assigned to: CPU Architecture:Any

[14 Dec 2022 13:36] Carl Nyströmer
If a right parenthesis (')') is found within the value clause of any INSERT statement in a prepared statement batch, followed by a bindable parameter ('?'), the batch won't be rewritten.

This is due to com.mysql.cj.QueryInfo not parsing the prepared statement sql string correctly when asserting if a prepared statement is "rewritableAsMultiValues". 

How to repeat:
Execute an arbitrary insert statement with a SQL-function in the values part of the statement, followed by a bindable parameter.

PreparedStatement ps = c.prepareStatement("""
                            INSERT INTO users (
                            ) VALUES(

ps.setString(1, UUID.randomUUID().toString());
ps.setString(2, "foo");
ps.setString(1, UUID.randomUUID().toString());
ps.setString(2, "bar");


Suggested fix:
Here are the problematic parts of the code: 

valueClauseEnd is set if a right parenthesis is found when in the valueClause (regardless if it is the end or not). 


If valueClauseEnd is set, and '?' is reached while parsing,                         rewritableAsMultiValues is set to false, and the batched statement won't be rewritten. 

[15 Dec 2022 13:22] MySQL Verification Team
Hello Carl Nyströmer,

Thank you for the report and feedback.

[20 Mar 16:29] Daniel So
Posted by developer:
Added the following entry to the Connector/J 8.0.33 changelog: 

"Rewriting of batched statements failed when a closing parenthesis was found within a VALUES clause. It was because QueryInfo failed to parse the prepared statement properly in that case. With this fix, the parser of VALUES clauses are improved, so that Connector/J is now able to recognize rewritability of statements that contain function calls or multiple VALUES lists, and it also handles well the cases when the string "value" is part of a table name, a column name, or a keyword."