Bug #109818 rewritableAsMultiValues wrongfully set to false if columnname contains "value"

• Submitted: 27 Jan 2023 7:33
• Modified: 30 Nov 2023 12:10
• Reporter: Gustaf Sörensen
• Status: Duplicate
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any

[27 Jan 2023 7:33] Gustaf Sörensen

Description:
After bumping the version of mysql-connector-j to 8.0.32 we noticed that batch inserts made by prepared statements took much longer time to execute. After a bit of investigation we found that this only happens on some of our inserts. Namely inserts where we have column names that have the word "value" in them and where the query takes placeholders "?".

This bug seems to have been introduced in 8.0.29.

How to repeat:
Test reproducing the bug:

import static org.junit.jupiter.api.Assertions.assertTrue;

import com.mysql.cj.jdbc.ClientPreparedStatement;
import com.mysql.cj.jdbc.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.jupiter.api.Test;

class RewritableAsMultiValuesTest {
  @Test
  void rewritableAsMultiValuesBug() throws SQLException {
    MysqlDataSource dataSource = new MysqlDataSource();
    String url = "DB CONNECTION STRING HERE";
    dataSource.setURL(url);
    dataSource.setRewriteBatchedStatements(true);
    Connection connection = dataSource.getConnection();

    //QUERY THAT IS EVALUATED CORRECT
    PreparedStatement preparedStatement = connection.prepareStatement(
        "INSERT INTO test (a, b) VALUES (?, ?)"
    );
    ClientPreparedStatement clientPreparedStatement = (ClientPreparedStatement) preparedStatement;
    boolean isRewritable = clientPreparedStatement.getQueryInfo()
        .isRewritableWithMultiValuesClause();
    assertTrue(isRewritable);
    connection.close();

    //QUERY THAT IS EVALUATED INCORRECTLY
    connection = dataSource.getConnection();
    PreparedStatement preparedStatementBug = connection.prepareStatement(
        "INSERT INTO test (a_value, bvalue) VALUES (?, ?)"
    );
    ClientPreparedStatement clientPreparedStatementBug = (ClientPreparedStatement) preparedStatementBug;
    boolean isRewritableBug = clientPreparedStatementBug.getQueryInfo()
        .isRewritableWithMultiValuesClause();
    assertTrue(isRewritableBug);

  }

}

Suggested fix:
The problem lies in QueryInfo.class. The valuesClauseEnd gets set too early in the query parsing.

[30 Nov 2023 12:10] MySQL Verification Team

Hello Gustaf Sörensen,

Thank you for the report and feedback.
Confirmed internally that this is duplicate of Bug #107577 and per change log this is fixed in 8.0.33. Thank you.

Quoting from 8.0.33 change log:

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 is 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. (Bug #109377, Bug #34900156, Bug #107577, Bug #34325361)

Please refer https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-33.html

regards,
Umesh