Bug #84365 INSERT..VALUE with VALUES function lead to a StringIndexOutOfBoundsException
Submitted: 29 Dec 2016 2:44 Modified: 18 Dec 2021 22:54
Reporter: sun sun Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.26 OS:Any
Assigned to: CPU Architecture:Any
Tags: insert, value, values

[29 Dec 2016 2:44] sun sun
Just execute a prepared statment like this:

INSERT  INTO test (id, name) VALUE (?,?) ON DUPLICATE KEY UPDATE id = values(id)

Caused by: java.lang.StringIndexOutOfBoundsException: String index out of range: -35
	at java.lang.String.substring(String.java:1937)
	at com.mysql.jdbc.PreparedStatement$ParseInfo.extractValuesClause(PreparedStatement.java:441)
	at com.mysql.jdbc.PreparedStatement$ParseInfo.buildRewriteBatchedParams(PreparedStatement.java:370)
	at com.mysql.jdbc.PreparedStatement$ParseInfo.<init>(PreparedStatement.java:357)
	at com.mysql.jdbc.PreparedStatement$ParseInfo.<init>(PreparedStatement.java:174)
	at com.mysql.jdbc.PreparedStatement.<init>(PreparedStatement.java:835)
	at com.mysql.jdbc.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:45)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	... 6 more

This is OK:
INSERT  INTO test (id, name) VALUES (?,?) ON DUPLICATE KEY UPDATE id = values(id)

How to repeat:
PreparedStatement stmt = conn
                .prepareStatement("INSERT  INTO test (id, name) VALUE (?,?) ON DUPLICATE KEY UPDATE id = values(id)");
[29 Dec 2016 4:50] MySQL Verification Team
Hello Sun,

Thank you for the report.
Could you please provide exact test case(.java file) to reproduce the issue at our end? I'm not seeing the reported issue with:

[umshastr@hod03]~/bugs: cat Bug84365.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.lang.String;

class Bug84365 {
   public static void main(String[] args) {
        Connection conn;
        try {
            String url = "jdbc:mysql://";
            String userName = "root";
            String userPassword = "";
            conn = DriverManager.getConnection(url, userName, userPassword);
            PreparedStatement stmt = conn.prepareStatement("INSERT  INTO test (id, name) VALUE (?,?) ON DUPLICATE KEY UPDATE id = values(id)");
            stmt.setInt(1, 1);
            stmt.setString(2, "Test");
        } catch (Exception e) {
            System.out.println("Exception: "+e);

[umshastr@hod03]~/bugs: javac Bug84365.java
[umshastr@hod03]~/bugs: java -cp '/home/umshastr/bugs/mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar:.' Bug84365

root@localhost [test]> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
1 row in set (0.00 sec)

-- CLI

root@localhost [test]> select * from test;
| id | name |
|  1 | Test |
1 row in set (0.00 sec)

[30 Jan 2017 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[6 Apr 2017 6:50] Oddbjrn Steen
Above code example fails if adding &rewriteBatchedStatements=true to URL:
String url = "jdbc:mysql://";

The string parsing of the VALUES part fails. Also fails for this SQL:
INSERT INTO test SET id=?, name=? ON DUPLICATE KEY UPDATE id = values(id)
[4 Oct 2021 9:42] Alex Popov
Example to reproduce the error

Attachment: ParsingUpsertWithSetClauseThrowsException.java (text/x-java), 1.20 KiB.

[4 Oct 2021 10:31] MySQL Verification Team
Thank you for the feedback and test case.
Observed that 8.0.26 build is affected.

[18 Dec 2021 22:54] Daniel So
Posted by developer:
Added the following entry to the Connector/J 8.0.28 changelog: 

"Running a PreparedStatement in the form of INSERT INTO ... VALUE ... ON DUPLICATE KEY UPDATE ... VALUES(...) resulted in a StringIndexOutOfBoundsException. It was because Connector/J did not recognize VALUE as an alias for VALUES in an INSERT statement, and mistook the VALUES() function as a VALUES clause of the INSERT statement. With this fix, statements like this are parsed properly."