Bug #99604 Add support to row alias on INSERT... ON DUPLICATE KEY UPDATE on batch mode
Submitted: 16 May 2020 19:04 Modified: 13 Jul 2020 11:13
Reporter: Alisson Oliveira Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.20 OS:Any
Assigned to: CPU Architecture:Any

[16 May 2020 19:04] Alisson Oliveira
Description:
The new row alias is being considered part of valuesClause on ParseInfo class. 

When executing an Insert with ON DUPLICATE KEY UPDATE in batch mode, the row alias is repeated alongside the values. The execution fails with:

Caused by: java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',(32970,2,5,0) AS new ,(32970,3,2,0) AS new ,(32970,4,3,0) AS new ,(32970,5,1,0)' at line 1

How to repeat:
1. mysql-connector-java: 8.0.20

2. Table structure:

create table costumes
(
    owner_id  int unsigned  not null,
    id        smallint      not null,
    amount    smallint      default 0 not null,
    ready     tinyint(1)    default 0 not null,
);

2. Execution Code:

var collection = List.of(new int[]{ 32970,2,5,0 }, new int[]{ 32970,3,2,0 }, new int[]{ 32970,4,3,0 });

var sql = "INSERT INTO costumes (owner_id, id, amount, ready) VALUES (?, ?, ?, ?) AS new ON DUPLICATE KEY UPDATE amount = amount + new.amount";
var statement = con.prepareStatement(sql);

for (var obj : collection) {
   statment.setInt(1, obj[0]);
   statment.setInt(2, obj[1]);
   statment.setInt(3, obj[2]);
   statment.setInt(4, obj[3]);
   statement.addBatch();
}

statement.executeBatch();
[13 Jul 2020 11:13] MySQL Verification Team
Hello Alisson Oliveira,

Thank you for the report and test case.
Verified as described.

regards,
Umesh
[13 Jul 2020 11:15] MySQL Verification Team
-table structure
create table costumes
(
    owner_id  int unsigned  not null,
    id        smallint      not null,
    amount    smallint      default 0 not null,
    ready     tinyint(1)    default 0 not null, primary key(id)
);
- 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class Bug99604 {

        public static void main(String[] args) throws ClassNotFoundException, SQLException {
        
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password=mysql123!&serverTimezone=UTC&rewriteBatchedStatements=true");
        
        Runtime.Version version = Runtime.version();
        System.out.println("JDK version: " + version);
        DatabaseMetaData meta = connection.getMetaData();
        System.out.println("MySQL Server: " + meta.getDatabaseProductVersion());
        System.out.println("Driver Name & Ver: " + meta.getDriverName() + meta.getDriverVersion());

        try{
                var collection = List.of(new int[]{ 32970,2,5,0 }, new int[]{ 32970,3,2,0 }, new int[]{ 32970,4,3,0 });

                var sql = "INSERT INTO costumes (owner_id, id, amount, ready) VALUES (?, ?, ?, ?) AS T ON DUPLICATE KEY UPDATE costumes.amount = costumes.amount + T.amount";
                var statement = connection.prepareStatement(sql);

                for (var obj : collection) {
                   statement.setInt(1, obj[0]);
                   statement.setInt(2, obj[1]);
                   statement.setInt(3, obj[2]);
                   statement.setInt(4, obj[3]);
                   statement.addBatch();
                }
                System.out.println(statement);
                statement.executeBatch();        

        }catch(SQLException e){
			e.printStackTrace();
            }
        }    
}
--
ant -f C:\\Work\\MySQLJava\\Bug99604 -Dnb.internal.action.name=run run
init:
Deleting: C:\Work\MySQLJava\Bug99604\build\built-jar.properties
deps-jar:
Updating property file: C:\Work\MySQLJava\Bug99604\build\built-jar.properties
Compiling 1 source file to C:\Work\MySQLJava\Bug99604\build\classes
compile:
run:
JDK version: 11.0.3+12-LTS
MySQL Server: 8.0.20
Driver Name & Ver: MySQL Connector/Jmysql-connector-java-8.0.20 (Revision: afc0a13cd3c5a0bf57eaa809ee0ee6df1fd5ac9b)
com.mysql.cj.jdbc.ClientPreparedStatement: INSERT INTO costumes (owner_id, id, amount, ready) VALUES (32970, 4, 3, 0) AS T ON DUPLICATE KEY UPDATE costumes.amount = costumes.amount + T.amount
java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',(32970, 3, 2, 0) AS T ,(32970, 4, 3, 0) AS T  ON DUPLICATE KEY UPDATE costumes.' at line 1
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
	at com.mysql.cj.util.Util.handleNewInstance(Util.java:192)
	at com.mysql.cj.util.Util.getInstance(Util.java:167)
	at com.mysql.cj.util.Util.getInstance(Util.java:174)
	at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchedInserts(ClientPreparedStatement.java:755)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:426)
	at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:796)
	at Bug99604.main(Bug99604.java:33)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',(32970, 3, 2, 0) AS T ,(32970, 4, 3, 0) AS T  ON DUPLICATE KEY UPDATE costumes.' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchedInserts(ClientPreparedStatement.java:716)
	... 3 more
BUILD SUCCESSFUL (total time: 2 seconds)
[19 Jul 2020 5:54] MySQL Verification Team
Bug #100252 marked as duplicate of this one