Bug #103417 jdbc . have an error in your SQL syntax

• Submitted: 22 Apr 2021 4:22
• Modified: 25 Nov 2022 23:34
• Reporter: icentury i
• Status: Not a Bug
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 8.0.24
• OS: MacOS (big sur)
• CPU Architecture: Any
• Tags: as alias on duplicate key update, batch, syntax error

[22 Apr 2021 4:22] icentury i

Description:

CREATE TABLE `b_tt` (
  `id` varchar(50) NOT NULL ,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);

String sql = "insert into b_tt(id, name) values(?, ?) AS newdata on duplicate key update name = newdata.name";

PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, 2);
ps.setObject(2, "2");
ps.addBatch();

ps.setObject(1, 1);
ps.setObject(2, "3");
ps.addBatch();

ps.executeBatch();

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 ',(1, '3') AS newdata  on duplicate key update name = newdata.name' at line 1

when
String sql = "insert into b_tt(id, name) values(?, ?) on duplicate key update name = values(name)";
there is no error.

How to repeat:
executeBatch

[22 Apr 2021 7:06] MySQL Verification Team

Hello!

Thank you for the report and feedback.

regards,
Umesh

[25 Nov 2022 23:34] Filipe Silva

Posted by developer:
 
This bug report doesn't mention what MySQL Server version was used. If it was any version below MySQL 8.0.19, then yes, this is the expected result. After all, new row aliases in ON DUPLICATE KEY UPDATE is only supported since MySQL 8.0.19 (https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html).

Closing as not a bug.

If this is not the case, then please re-open or file a new bug and provide all details.