Bug #40439 Error using batched prepared statements

Description:
When using rewriteBatchedStatements=true with:
insert into table_name_values (...) values (...) 

Query rewriting will fail because 'values' at the end of the table name is mistaken for the reserved keyword.

How to repeat:
            conn.setAutoCommit(false);

            final PreparedStatement ps = conn
                    .prepareStatement("INSERT INTO PRIMI_VALUES (ID, ELEMENT, ORDR) VALUES (?, ?, ?)");
            for (int k = 0; k < 10; k++) {
                ps.setInt(1, k);
                ps.setString(2, "toto");
                ps.setInt(3, k);
                ps.addBatch();
            }
            conn.commit();

Table name must be terminated by "VALUES" and followed by an empty space

Suggested fix:
        final static Pattern REGEXP = Pattern.compile("^.*?[ \\)]VALUES *\\(", Pattern.CASE_INSENSITIVE);
	private String extractValuesClause() throws SQLException {
		if (this.batchedValuesClause == null) {
			final Matcher matcher = REGEXP.matcher(this.originalSql);
	
			if (!matcher.find()) {
				return null;
			}
	
			int indexOfFirstParen = matcher.group().length()-1;
			assert indexOfFirstParen >= "INSERT INTO x VALUES(".length()-1;
			assert this.originalSql.charAt(indexOfFirstParen) == '(';
			
			if (indexOfFirstParen == -1) {
				return null;
			}
	
			int indexOfLastParen = this.originalSql.lastIndexOf(')');
	
			if (indexOfLastParen == -1) {
				return null;
			}
	
			this.batchedValuesClause = this.originalSql.substring(indexOfFirstParen,
					indexOfLastParen + 1);
		}
			
		return this.batchedValuesClause;
	}

Test case

Attachment: TestBug40439.java (text/java), 1.87 KiB.

Hello Stephane.

Verified as described using latest c/J sources and attached test case. If we do not use "rewriteBatchedStatements" everything is just fine. When "rewriteBatchedStatements=true" we have an error:

Connected to 5.0.68-pb10-log
java.vm.version         : 1.5.0_12-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_12-b04
os.name                 : Windows XP
os.version              : null
sun.management.compiler : HotSpot Client Compiler
E
Time: 0,407
There was 1 error:
1) testBug40439(testsuite.simple.TestBug40439)java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values (2,'toto',2),(id,data, ordr) values (3,'toto',3),(id,data, ordr) values (' at line 1
	at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1495)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1097)
	at testsuite.simple.TestBug40439.testBug40439(TestBug40439.java:42)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at testsuite.simple.TestBug40439.main(TestBug40439.java:57)

FAILURES!!!
Tests run: 1,  Failures: 0,  Errors: 1

And on server:
61 Query  INSERT INTO bug40439_values (id,data, ordr) values (1,'toto',1),(id,data, ordr) values (2,'toto',2),(id,data, ordr) values (3,'toto',3),(id,data, ordr) values (4,'toto',4),(id,data, ordr) values (5,'toto',5),(id,data, ordr) values (6,'toto',6),(id,data, ordr) values (7,'toto',7),(id,data, ordr) values (8,'toto',8),(id,data, ordr) values (9,'toto',9),(id,data, ordr) values (10,'toto',10)

fix + test

Attachment: bug40439.diff (text/x-diff), 3.05 KiB.

Pushed for release in 5.1.8

An entry was added to the 5.1.8 changelog:

When using rewriteBatchedStatements=true with:

INSERT INTO table_name_values (...) VALUES (...)

Query rewriting failed because “values” at the end of the table name was mistaken for the reserved keyword. The error generated was as follows:

testBug40439(testsuite.simple.TestBug40439)java.sql.BatchUpdateException: You have an
error in your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'values (2,'toto',2),(id,data, ordr) values
(3,'toto',3),(id,data, ordr) values (' at line 1
at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1495)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1097)
at testsuite.simple.TestBug40439.testBug40439(TestBug40439.java:42)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at testsuite.simple.TestBug40439.main(TestBug40439.java:57)