Bug #71923 Incorrect generated keys if ON DUPLICATE KEY UPDATE not exact

• Submitted: 4 Mar 2014 12:23
• Modified: 17 Jul 2014 17:29
• Reporter: Filipe Silva
• Status: Closed
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 5.1.29
• OS: Any
• Assigned to: Filipe Silva
• CPU Architecture: Any

[4 Mar 2014 12:23] Filipe Silva

Description:
Generated keys from an INSERT using ON DUPLICATE KEY UPDATE clause are treated differently but, if "ON DUPLICATE KEY UPDATE" is not exactly written, for example "ON DUPLICATE KEY /* more spaces or comments here */ UPDATE" instead, then the special processing does not occur.

How to repeat:
Class.forName("com.mysql.jdbc.Driver");

Connection conn = DriverManager.getConnection(CONNECTION_URL, USERNAME, PASSWORD);
Statement stmt = conn.createStatement();
ResultSet rs;

for (String query : new String[] {
		"INSERT INTO odkutest (ch, ct) VALUES ('A', 1), ('B', 2) ON DUPLICATE KEY UPDATE ct = ABS(ct) + VALUES(ct)",
		"INSERT INTO odkutest (ch, ct) VALUES ('A', 1), ('B', 2) ON DUPLICATE KEY /* more spaces or comments here */ UPDATE ct = ABS(ct) + VALUES(ct)" }) {

	stmt.execute("DROP TABLE IF EXISTS odkutest");
	stmt.execute("CREATE TABLE IF NOT EXISTS odkutest (id INT AUTO_INCREMENT PRIMARY KEY, ch CHAR(1) UNIQUE KEY, ct INT)");

	stmt.execute(query, Statement.RETURN_GENERATED_KEYS);
	rs = stmt.getGeneratedKeys();
	System.out.print("Generated keys: ");
	while (rs.next()) {
		System.out.print(rs.getString(1) + "; ");
	}
	System.out.println();
	rs.close();
}

stmt.close();
conn.close();

Suggested fix:
None

[17 Jul 2014 17:29] Daniel So

Added the following entry to the Connector/J 5.1.32 changelog:

"The keys generated by INSERT statements using the ON DUPLICATE KEY UPDATE clause were incorrect when the clause “ON DUPLICATE KEY UPDATE” was not written exactly as thus (for example, when spaces or comments were inserted in between the words)."