Bug #100252 PreparedStatements with rewriteBatchedStatements does not translate aliases well

• Submitted: 18 Jul 2020 0:51
• Modified: 19 Jul 2020 5:53
• Reporter: Imad Al-Natsheh
• Status: Duplicate
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 8.0.21
• OS: Ubuntu
• CPU Architecture: x86

[18 Jul 2020 0:51] Imad Al-Natsheh

Description:
When you use PreparedStatement with an "INSERT ... ON DUPLICATE KEY UPDATE" statement, and rewriteBatchedStatements is specified in the configs, the resulting statement does not handle aliases well as introduced and recommended in MySQL 8.0.19

that is, instead of getting the example in the docs:
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

you get something like:
INSERT INTO t1 (a,b,c) VALUES (1,2,3) AS new,(4,5,6) AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

and the statement will fail on executeBatch()

How to repeat:
1-set the property rewriteBatchedStatements to true

2-create any PreparedStatement with "INSERT ... ON DUPLICATE KEY UPDATE" that uses aliases to reinput new values

3-use addBatch to add more than 1 statement to the batch

4-call executeBatch()

[18 Jul 2020 0:53] Imad Al-Natsheh

simple testcase to show the problem

Attachment: BatchedPreparedBugReport.java (text/x-java), 2.07 KiB.

[19 Jul 2020 5:53] MySQL Verification Team

Hello Imad Al-Natsheh,

Thank you for the report and test case.
Imho this is duplicate of Bug #99604, please see Bug #99604

regards,
Umesh