Bug #84365 INSERT..VALUE with VALUES function lead to a StringIndexOutOfBoundsException
Submitted: 29 Dec 2016 2:44 Modified: 29 Jan 2017 4:50
Reporter: sun sun Email Updates:
Status: No Feedback Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.40 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)