Bug #48172 Batch rewrite requires space immediately after "VALUES"
Submitted: 19 Oct 2009 19:55 Modified: 23 Oct 2009 10:52
Reporter: Todd Farmer (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.10 OS:Any
Assigned to: Todd Farmer CPU Architecture:Any

[19 Oct 2009 19:55] Todd Farmer 
Description:
The batch rewrite functionality currently attempts to identify the start of the VALUES list by looking for "VALUES " (with trailing space).  Valid MySQL syntax allows for VALUES to be followed by whitespace or an opening parenthesis:

INSERT INTO tbl VALUES
(1);

INSERT INTO tbl VALUES(1);

Queries so-written will unfortunately bypass the performance benefits of the batch rewrite.

How to repeat:
Use a PreparedStatement where the opening parenthesis immediately follows the "VALUES" keyword:

"INSERT INTO testBatchInsert VALUES(?)"

Note that the query isn't rewritten.

Suggested fix:
Allow for whitespace or opening parenthesis immediately following "VALUES"
[19 Oct 2009 20:11] Todd Farmer 
patch for feature and test case.

Attachment: diff.txt (text/plain), 4.80 KiB.
[19 Oct 2009 20:13] Todd Farmer 
Patch pushed to 5.1 branch.
[23 Oct 2009 10:52] Tony Bedford 
An entry was added to the 5.1.11 changelog:

The batch rewrite functionality attempted to identify the start of the VALUES list by looking for “VALUES ” (with trailing space). However, valid MySQL syntax allows for VALUES to be followed by whitespace or an opening parenthesis:

INSERT INTO tbl VALUES
(1);

INSERT INTO tbl VALUES(1);

Queries written with the above formats did not therefore gain the performance benefits of the batch rewrite.