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

[16 May 19:04] Alisson Oliveira
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]);