Bug #107577 rewriteBatchedStatements not work when table column name contains 'value' string
Submitted: 16 Jun 2022 8:38 Modified: 20 Jun 2022 4:19
Reporter: Charles Yang Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:8.0.29 OS:Any
Assigned to: CPU Architecture:Any
Tags: REWRITEBATCHEDSTATEMENTS

[16 Jun 2022 8:38] Charles Yang
Description:
setting rewriteBatchedStatements to true, and create a table with column name contains 'value', such like 'p_value' and using insert sql `insert into tableName(p_value) values (?)` to insert data into table, rewriteBatchedStatements not working correct.

How to repeat:
1. create a table with any column name contains 'value', such like 'p_value',
2. setting rewriteBatchedStatements to true
3. use preparedstatement to insert data, such like `insert into tableName(p_value) values (?)`

Suggested fix:
in com.mysql.cj.QueryInfo, strInspector.matchesIgnoreCase(VALUE_CLAUSE) should skip column names
[16 Jun 2022 20:40] MySQL Verification Team
Hi,

Are you getting the same result with server side prepared statement set to true and to false ?

thanks
[20 Jun 2022 1:40] Charles Yang
thanks for your replay.
we tested these cases:
1. column name not contains 'value',
2. on column name contains 'value'
and check the server side log, the final sql is:
for case 1: insert into table_name ('p_val') values ('1'),('2'),('3')
for case 2: insert into table_name ('p_value') values ('1');insert into table_name ('p_value') values ('2');insert into table_name ('p_value') values ('3');
so i don't think the the bug is cased by the server side parameter setting.

according by the comments of https://github.com/mysql/mysql-connector-j/blob/8.0.29/src/main/core-api/java/com/mysql/cj..., 

if (valuesClauseBegin == -1 && strInspector.matchesIgnoreCase(VALUE_CLAUSE) != -1) { // VALUE(S) clause found.
    strInspector.incrementPosition(VALUE_CLAUSE.length()); // Advance to the end of "VALUE".
    if (strInspector.matchesIgnoreCase("S") != -1) { // Check for the "S" in "VALUE(S)" and advance 1 more character if needed.
        strInspector.incrementPosition();
    }
    withinValuesClause = true;
    strInspector.indexOfNextChar(); // Position on the first values list character.
    valuesClauseBegin = strInspector.getPosition();

    if (rewritableAsMultiValues) {
        valuesEndpointStart = valuesClauseBegin;
    }

}

if a sql like `insert into table_name(id, p_value) values(?,?)`, the if block will be executed, i think it is not correct, am i wrong?
[20 Jun 2022 4:19] MySQL Verification Team
Hi,

That part is supposed to be ok as com.mysql.cj.util.* should know to search outside `` and outside /* */ and before -- and only between whitespaces etc etc .. but looks someone forgot something as I can reproduce the issue so it is def. there :( 

Thank you for your report
[13 Feb 17:06] Filipe Silva
Related to Bug#109864.