Description:
I found that QueryInfo can only detect "ON DUPLICATE KEY UPDATE" in "INSERT VALUES" statement, not in "INSERT SET" statement. I want this code to handle both cases correctly.
In the "INSERT SET" statement, "VALUES" doesn't appear in the statement, so checking withinValuesClause is not suited to the if clause.
Because of that, getGeneratedKeys() returns multiple rows in the latter case, and the software compatibility is broken between 8.0.28 and 8.0.29.
How to repeat:
@Test
public void testGetGeneratedKeysWithOnDuplicateKeyUpdate() throws SQLException {
createTable("statement_test", "(id int not null primary key auto_increment, strdata1 varchar(255) not null, strdata2 varchar(255))");
this.pstmt = this.conn.prepareStatement("INSERT INTO statement_test (id, strdata1) VALUES (?, ?)");
this.pstmt.setInt(1, 1);
this.pstmt.setString(2, "foo");
this.pstmt.execute();
this.pstmt = this.conn.prepareStatement("INSERT INTO statement_test (id, strdata1) VALUES (?, ?) ON DUPLICATE KEY UPDATE strdata1 = VALUES(strdata1)", Statement.RETURN_GENERATED_KEYS);
this.pstmt.setInt(1, 1);
this.pstmt.setString(2, "bar");
this.pstmt.execute();
this.rs = this.pstmt.getGeneratedKeys();
this.rs.last();
assertEquals(1, this.rs.getRow(), "The number of generatedKeys isn't one.");
this.rs.close();
this.pstmt = this.conn.prepareStatement("INSERT INTO statement_test SET id = ?, strdata1 = ? ON DUPLICATE KEY UPDATE strdata1 = VALUES(strdata1)", Statement.RETURN_GENERATED_KEYS);
this.pstmt.setInt(1, 1);
this.pstmt.setString(2, "hoge");
this.pstmt.execute();
this.rs = this.pstmt.getGeneratedKeys();
this.rs.last();
assertEquals(1, this.rs.getRow(), "The number of generatedKeys isn't one.");
this.rs.close();
}
}
Description: I found that QueryInfo can only detect "ON DUPLICATE KEY UPDATE" in "INSERT VALUES" statement, not in "INSERT SET" statement. I want this code to handle both cases correctly. In the "INSERT SET" statement, "VALUES" doesn't appear in the statement, so checking withinValuesClause is not suited to the if clause. Because of that, getGeneratedKeys() returns multiple rows in the latter case, and the software compatibility is broken between 8.0.28 and 8.0.29. How to repeat: @Test public void testGetGeneratedKeysWithOnDuplicateKeyUpdate() throws SQLException { createTable("statement_test", "(id int not null primary key auto_increment, strdata1 varchar(255) not null, strdata2 varchar(255))"); this.pstmt = this.conn.prepareStatement("INSERT INTO statement_test (id, strdata1) VALUES (?, ?)"); this.pstmt.setInt(1, 1); this.pstmt.setString(2, "foo"); this.pstmt.execute(); this.pstmt = this.conn.prepareStatement("INSERT INTO statement_test (id, strdata1) VALUES (?, ?) ON DUPLICATE KEY UPDATE strdata1 = VALUES(strdata1)", Statement.RETURN_GENERATED_KEYS); this.pstmt.setInt(1, 1); this.pstmt.setString(2, "bar"); this.pstmt.execute(); this.rs = this.pstmt.getGeneratedKeys(); this.rs.last(); assertEquals(1, this.rs.getRow(), "The number of generatedKeys isn't one."); this.rs.close(); this.pstmt = this.conn.prepareStatement("INSERT INTO statement_test SET id = ?, strdata1 = ? ON DUPLICATE KEY UPDATE strdata1 = VALUES(strdata1)", Statement.RETURN_GENERATED_KEYS); this.pstmt.setInt(1, 1); this.pstmt.setString(2, "hoge"); this.pstmt.execute(); this.rs = this.pstmt.getGeneratedKeys(); this.rs.last(); assertEquals(1, this.rs.getRow(), "The number of generatedKeys isn't one."); this.rs.close(); } }