Bug #108379 Recognize "ON DUPLICATE KEY UPDATE" in "INSERT SET" Statement
Submitted: 5 Sep 2022 7:58 Modified: 20 Sep 2022 22:29
Reporter: Yamasaki Tadashi (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.29, 8.0.30 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[5 Sep 2022 7:58] Yamasaki Tadashi
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();
    }
}
[12 Sep 2022 12:26] MySQL Verification Team
Hello Yamasaki,

Thank you for the report and feedback.

regards,
Umesh
[20 Sep 2022 22:29] Filipe Silva
Duplicate of Bug#108419