MySQL Bugs: #90688: Statement.getGeneratedKeys() returns too many rows on INSERT .. ON DUPLICATE KEY

Bug #90688	Statement.getGeneratedKeys() returns too many rows on INSERT .. ON DUPLICATE KEY
Submitted:	30 Apr 2018 10:37	Modified:	3 May 2018 14:41
Reporter:	Lukas Eder	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.11	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When running an INSERT .. ON DUPLICATE KEY UPDATE Statement, the Statement.getGeneratedKeys() method returns too many rows in case the rows are updated (not inserted).

How to repeat:
Run this program:

// --------------------------------------------------------------------
try (Connection c = new com.mysql.cj.jdbc.Driver().connect(url, properties);
     Statement s = c.createStatement();
     PreparedStatement p = c.prepareStatement("INSERT INTO x (j) VALUES (?) ON DUPLICATE KEY UPDATE j = 3", Statement.RETURN_GENERATED_KEYS)) {
    try {
        s.execute("CREATE TABLE IF NOT EXISTS x (i INT AUTO_INCREMENT PRIMARY KEY, j INT UNIQUE)");

        p.setInt(1, 1);
        System.out.println("Statement 1:");
        System.out.println("------------");
        System.out.println("Rows: " + p.executeUpdate());
        try (ResultSet r = p.getGeneratedKeys()) {
            while (r.next())
                System.out.println("Key : " + r.getInt(1));
        }

        p.setInt(1, 2);
        System.out.println();
        System.out.println("Statement 2:");
        System.out.println("------------");
        System.out.println("Rows: " + p.executeUpdate());
        try (ResultSet r = p.getGeneratedKeys()) {
            while (r.next())
                System.out.println("Key : " + r.getInt(1));
        }

        p.setInt(1, 1);
        System.out.println();
        System.out.println("Statement 3:");
        System.out.println("------------");
        System.out.println("Rows: " + p.executeUpdate());
        try (ResultSet r = p.getGeneratedKeys()) {
            while (r.next())
                System.out.println("Key : " + r.getInt(1));
        }
    }
    finally {
        s.execute("DROP TABLE IF EXISTS x");
    }
}
// --------------------------------------------------------------------

The output is:

Statement 1:
------------
Rows: 1
Key : 1

Statement 2:
------------
Rows: 1
Key : 2

Statement 3:
------------
Rows: 2 -- Weird but expected *
Key : 1
Key : 2 -- Problematic row here. ID 2 is not updated, but still returned

* (With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values, https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html)

Suggested fix:
I've tracked this down through the internals. Apparently, com.mysql.cj.jdbc.StatementImpl.lastQueryIsOnDupKeyUpdate is set to false when it should be set to true. Setting it to true using a debugger solves the issue for me.

The flag is set to true in various methods, including e.g. com.mysql.cj.jdbc.StatementImpl.executeInternal(), but it seems the flag is only ever set for static statements, not prepared statements

Hello Lukas Eder,

Thank you for the bug report and testcase.
Verified this behavior on MySQL Connector / J 8.0.11.

Thanks,
Chiranjeevi.

Output:
Statement 1:
------------
Rows: 1
Key : 1

Statement 2:
------------
Rows: 1
Key : 2

Statement 3:
------------
Rows: 2
Key : 1
Key : 2

Hi Lukas,

Correct, the last row is wrong. But what is your expectation about previous row? I'd say the last result set should be empty because no keys are generated by statement 3.

That's a good question. My hopes for any JDBC driver is to produce all the affected rows, regardless if any keys were generated, akin to PostgreSQL's

INSERT INTO x (j)
VALUES (?)
ON CONFLICT (j) DO UPDATE SET j = 3
RETURNING *

I mean, keys aren't the only generated values of interest. Triggers could generate values, or DEFAULT expressions, too.

But the JDBC specs aren't explicit about this. I guess the behaviour should be consistent with an ordinary UPDATE statement, though.

Even with `PreparedStatement`, the issue does not reproduce if you call `execute()` instead of `executeUpdate()`.
So, I guess we just need to copy the following code to somewhere in the `executeUpdate()` call stack, maybe?

```java
this.lastQueryIsOnDupKeyUpdate = false;
if (this.retrieveGeneratedKeys) {
  this.lastQueryIsOnDupKeyUpdate = containsOnDuplicateKeyUpdateInSQL();
}
```

And regarding the behavior when an existing row is updated, the spec [1] says:

> If this Statement object did not generate any keys, an empty ResultSet object is returned.

[1] https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Statement.html#getGen...)