MySQL Bugs: #27431: ResultSet.deleteRow()

Bug #27431	ResultSet.deleteRow() - incorrect behaviour
Submitted:	26 Mar 2007 6:27	Modified:	16 Sep 2009 13:46
Reporter:	Lachezar Balev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.12	OS:	Linux (Linux FC5)
Assigned to:		CPU Architecture:	Any

Description:
Hello!

We noticed a problem with deleteRow() functionality. Step by step description:

1. DB tables:

CREATE TABLE `test` 
(
  `ID` int(20) NOT NULL auto_increment,
  `Name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

INSERT INTO `test` (`ID`, `Name`) VALUES 
(1, 'Lucho'),
(2, 'Lily'),
(3, 'Kiro');

2. Java code:

Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rSet = stmt.executeQuery("SELECT ID, Name FROM test");

while(rSet.next())
{
   System.out.println("ID: " + rSet.getInt("ID") + 
                        " Name: " + rSet.getString("Name"));
   rSet.deleteRow();
}

rSet.close();
stmt.close();

3. Output:

ID: 1 Name: Lucho
ID: 3 Name: Kiro

4. Problem:

ID 2 - Lily remained in DB. I suppose that this is not correct.

5. Version information:

MySQL 4.1.20
Connector/J 3.1.12
OS Linux FC5

How to repeat:
It is always reproducable.

Suggested fix:
No suggestions.

Sorry I did make a mistake in my example, because I tried few different pieces of code. The correct example is:

rSet.next();
while(!rSet.isAfterLast())
{
  System.out.println("ID: " + rSet.getInt("ID") + " Name: " + rSet.getString("Name"));
  rSet.deleteRow();
}

The output is:
ID: 2 Name: Lily
ID: 2 Name: Lily
ID: 2 Name: Lily

In DB only the first and third row remain.
Obviously the deleteRow() method does move the cursor, but we stay at row 2.

Hi Luchezar and thanks for your report.

What happens if:
 - You try with latest version of c/J (3.1.14)?
 - If you remove TYPE_FORWARD_ONLY?

What is JDK/JRE you're using?

deleteRow

void deleteRow()
    throws SQLException

    Deletes the current row from this ResultSet object and from the underlying database. This method cannot be called when the cursor is on the insert row.

    Throws:
        SQLException - if a database access error occurs or if this method is called when the cursor is on the insert row
    Since:
        1.2

I don't think your definition "does not move cursor" is correct. I would say "there is no current row after delete ...".

Hello, Tonci!

Sorry for my late reply. I switched to Connector/J v3.1.14 yesterday. I used v3.1.12 for the previous experiment. I tried the example above:

Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rSet = stmt.executeQuery("SELECT ID, Name FROM test");

rSet.next();
while(!rSet.isAfterLast())
{
   System.out.println("ID: " + rSet.getInt("ID") + " Name: " + rSet.getString("Name"));
  rSet.deleteRow();
}

rSet.close();
stmt.close();

I tried it both with JDK 1.5.0_03 and with JDK 1.4.02_11.
The result was the same, this time:

ID: 1 Name: Lucho
ID: 1 Name: Lucho
ID: 1 Name: Lucho

In DB remained the rows: [2, Lily], [3, Kiro].

OK! Maybe "there is no CURrent Set Of Rows after delete".

I tried it with:

ResultSet.TYPE_FORWARD_ONLY
ResultSet.TYPE_SCROLL_INSENSITIVE
ResultSet.TYPE_SCROLL_SENSITIVE

Same result.

Best regards,
Luchezar.

Luchezar, we've given this a lot of thought and I'll keep it in "Analyzing" until proper quotes from JDBC specs are found.

Stil I don't see a bug here. 

Consider this. Let's say there's no "ORDER BY" on the result set, and the database actually supports cursors. What's the "next" row once you've removed one and is it always the same? Fishing out "no ORDER BY" situation in driver would require full parser and make driver too slow and complicated so that's not an option.

Dear developers,
   Reading the above report, I would be glad if someone can post here a working piece of code which deletes rows from a table using the ResultSet.deleteRow() call.
   From the examples which Luchezar has posted I could see that the first one:

while(rSet.next())
{
   System.out.println("ID: " + rSet.getInt("ID") + 
                        " Name: " + rSet.getString("Name"));
   rSet.deleteRow();
}

skips rows as if the call to deleteRow() moves the cursor the same way like the call to next() does. That is why the second row from the example table remains:

1. first call to next() moves on the first row
2. deleteRow() drops the row and moves to the second one
3. next() forwards to the third row 
4. and deleteRow() drops the third row, leaving row 2 in the table.

But if we DO rely on the assumption that deleteRow() moves the cursor to the next row and use the second example:

rSet.next(); //position on the first row
while(!rSet.isAfterLast()) //are we still on a valid row?
{
  System.out.println("ID: " + rSet.getInt("ID") + " Name: " + rSet.getString("Name")); //print the content of the row
  rSet.deleteRow(); //delete the row and move on the next one in the resset
}

we can see that deleteRow() behaves completely strange - the first time it is called it moves the cursor to row 2, but that is all - the result set stays there - on row 2 - and only row 1 is deleted.

So, please assume you have the table like the one in Luchezar's example; you execute a SELECT query and get a result set with the data; you want to delete (some of) the rows in the resultset using the deleteRow() call. How the Java code should look like?

Thank you in advance for your support on the confusing issue!

Best regards,
George

Luchezar, George, I have brought this problem to c/J dev team and waiting for their reply.

I'm seeing the same behavior in 5.1.5
Will it be addressed in the 5.x codebase or just the 3.1.x one?

I found a solution posted by Thomas Mayr
http://lists.mysql.com/java/7177

The solution is:
int deletedRow = m_resultSet.getRow();
m_resultSet.deleteRow();
if (deletedRow == m_resultSet.getRow())
{
   m_resultSet.previous();
}

Additional comment:
I checked the solution with resultset type TYPE_SCROLL_INSENSITIVE

Fixed (finally!) for 5.1.9. The driver now places the cursor on the prior row in the result set, or before the start of the result set if the result set is empty after the deletion.

An entry was added to the 5.1.9 changelog:

Attempting to delete rows using ResultSet.deleteRow() did not delete rows correctly.

As a follow-up, we've been using the following code for years due to this strangeness in the way Connector/J operated:

    /**
     * Deletes the current row from the specified ResultSet.
     *
     * This method exists because it contains a hack for MySQL Connector/J
     * which behaves oddly. That hack requires more than a single line of
     * code (rs.deleteRow) and should be properly documented, which takes
     * up space within the method that wants to delete that row.
     *
     * This method exists to help remove extraneous stuff from methods
     * that shouldn't have to duplicate this code.
     */
    static void deleteCurrentRow(ResultSet rs)
        throws SQLException
    {
        // HACK: This trick with the getRow() and previous()
        // calls gets is around the behavior of some JDBC
        // drivers, most notably Connector/J for MySQL.
        int rowNum = rs.getRow();
        rs.deleteRow();

        if(rowNum == rs.getRow())
            rs.previous();
    }

Fortunately for us, this update will not cause any adverse reaction, since the methods checks to see if the rewind is necessary.