MySQL Bugs: #84365: INSERT..VALUE with VALUES function lead to a StringIndexOutOfBoundsException

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:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.26	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	insert, value, values

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();

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

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".

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)

Example to reproduce the error

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

Thank you for the feedback and test case.
Observed that 8.0.26 build is affected.

regards,
Umesh

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."