Bug #84365 INSERT..VALUE with VALUES function lead to a StringIndexOutOfBoundsException
Submitted: 29 Dec 2016 2:44 Modified: 4 Oct 10:31
Reporter: sun sun Email Updates:
Status: Verified Impact on me:
None 
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
Description:
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)");
stmt.executeUpdate();
[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 {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false";
            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");
            stmt.execute();
        } catch (Exception e) {
            System.out.println("Exception: "+e);
            e.printStackTrace();
        }
    }
}

[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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
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)

Thanks,
Umesh
[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://127.0.0.1:3306/test?useSSL=false&rewriteBatchedStatements=true";

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 9:42] Alex Popov
Example to reproduce the error

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

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

regards,
Umesh