Bug #34093 with rewriteBatchedStatements, getGeneratedKeys returns them in ascending order
Submitted: 27 Jan 2008 19:11 Modified: 1 Mar 2008 12:33
Reporter: Eric Jensen Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.5 OS:Any
Assigned to: CPU Architecture:Any

[27 Jan 2008 19:11] Eric Jensen
Description:
Without rewriteBatchedStatements turned on, calling getGeneratedKeys() after an executeBatch() will return the keys generated in order of the statements added using addBatch.  

With rewriteBatchedStatements=true, however, getGeneratedKeys() returns the keys from an executeBatch() in ascending key order.  This makes it impossible to map the generated keys to the statements you ran, which effectively makes that call useless at best, and misleading at worst.  

How to repeat:
Turn on rewriteBatchedStatements=true, executeBatch() some statements that follow the pattern:

insert into test (uniqueTextKey) values (?)
on duplicate key UPDATE autoIncId = last_insert_id( autoIncId )

then look at the output of getGeneratedKeys()

Suggested fix:
It would be nice if getGeneratedKeys() with rewriteBatchedStatements=true worked, as it can be a performance improvement over doing them individually.  But it would be preferable for it to fail than to return them in arbitrary order.
[31 Jan 2008 6:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41478
[31 Jan 2008 6:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41479
[1 Mar 2008 12:33] MC Brown
A note has been added to the 5.1.6 changelog: 

Statements with batched values do not return correct values for getGeneratedKeys() when rewriteBatchedStatements is set to true, and the statement has an ON DUPLICATE KEY UPDATE clause.