DatabaseMetaData.ownDeletesAreVisible vs DatabaseMetaData.deletesAreDetected Controversy

Question

I am using JDBC with mysql-connector-java-6.0.2.jar, and, unless I am doing something wrong, I think DatabaseMetaData.ownDeletesAreVisible and DatabaseMetaData.deletesAreDetected implementations are acting somehow inconsistently between each other.

Here's what the JDBC spec says regarding ownDeletesAreVisible:

"...If the deleted row is removed or replaced by an empty row, the method DatabaseMetaData.ownDeletesAreVisible(int type) will return true. It returns false if the ResultSet object still contains the deleted row, which means that the deletion is not visible as a change to ResultSet objects of the given type..."

And regarding deletesAreDetected:

"...The method deletesAreDetected returns false if a row deleted from the ResultSet object is removed from it and true if the deleted row is replaced by an empty or invalid row..."

I added the outputs as comments:

import static java.sql.ResultSet.CONCUR_UPDATABLE;
import static java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE;
import java.sql.*;

public class Deletions {

    public static void main(String[] args) throws SQLException {

        try (Connection conn = DBUtils.getConnection();
                Statement stmt = conn.createStatement(TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE);
                ResultSet rs = stmt.executeQuery("select * from book")) {

            DatabaseMetaData dbmd = conn.getMetaData();

            //prints false
            System.out.println(dbmd.ownDeletesAreVisible(TYPE_SCROLL_INSENSITIVE));

            // prints false. Controversy?
            System.out.println(dbmd.deletesAreDetected(TYPE_SCROLL_INSENSITIVE)); 

            // Prints everything including foo
            printAll(rs); 


            // deletes foo
            while (rs.next()) {
                String title = rs.getString(2);
                if (title.equalsIgnoreCase("foo")) {
                    rs.deleteRow();
                }
            }

            // Prints everything without foo
            printAll(rs);

        }
    }

        private static void printAll(ResultSet rs) throws SQLException {
            rs.beforeFirst();
            while (rs.next()) {
                System.out.println(rs.getString(2));
            }
            rs.beforeFirst();
        }
}

You should really move your comments into the body of the text, that makes it a lot easier to read. — Mark Rotteveel, Jul 06 '16 at 05:37

Mark Rotteveel · Answer 1 · 2016-07-06T06:07:43.877

It is not a contradiction, the JDBC 4.2 specification, section 15.2.4.2 says (emphasis mine):

After the method deleteRow has been called, the current row is deleted in the underlying data source. This deletion is visible as a change in the open ResultSet object if the row is either removed or replaced by an empty or invalid row.

If the deleted row is removed or replaced by an empty row, the method DatabaseMetaData.ownDeletesAreVisible(int type) will return true. It returns false if the ResultSet object still contains the deleted row, which means that the deletion is not visible as a change to ResultSet objects of the given type.

[..]

If a ResultSet object can detect deletions, the ResultSet method rowDeleted returns true when the current row has been deleted and false when it has not. However, rowDeleted also returns false if the ResultSet object cannot detect deletions. The method DatabaseMetaData.deletesAreDetected(int type) can be called to see whether a ResultSet object of the specified type can call the method rowDeleted to detect a deletion that is visible. The method deletesAreDetected returns false if a row deleted from the ResultSet object is removed from it and true if the deleted row is replaced by an empty or invalid row.

It takes some reading between the lines (and looking at the piece of example code in that section), but this means that deletesAreDetected only has meaning if deletes are visible, it discerns how the deletes are visible: either the row has been deleted (false), or replaced with an empty or invalid row (true).

So as ownDeletesAreVisible returns false, the result of deletesAreDetected has no meaning with regard to 'own' deletes (apart from 'deletes are not detected'); it may have meaning with regard to 'others' deletes, but I doubt that other deletes are visible when your own are not.

I see what you mean, except in the first part when it says that DatabaseMetaData.ownDeletesAreVisible(int type) returns false if the ResultSet object still contains the deleted row. In my scenario, it returned false, meaning that resultset still had it without replacing anything. Why then the deleted line was not in the resultset anymore when I printed it again? — Italo Macellone, Jul 11 '16 at 17:25
I had both ownDeletesAreVisible and deletesAreDetected returning false. It says "If the deleted row is removed or replaced by an empty row, the method DatabaseMetaData.ownDeletesAreVisible(int type) will return true." So my row wasn't removed or replaced then. It also says: "The method deletesAreDetected returns false if a row deleted from the ResultSet object is removed from it". Why did I get false? ownDeletesAreVisible said it hadn't been removed. This is the "controversy" I am struggling to understand. — Italo Macellone, Jul 11 '16 at 17:30
@imacellone then I can only suggest that either my interpretation is wrong, or the driver you use doesn't correctly specify its behavior in the DatabaseMetaData. — Mark Rotteveel, Jul 11 '16 at 17:33
Thank you for your reply. I will using another JDBC implementation to compare the results with and post here. — Italo Macellone, Jul 11 '16 at 18:32
Using Apache Derby DB I got both methods returning true. First print included 'foo'. Second print, 'foo' had been replaced with 'null'. — Italo Macellone, Jul 23 '16 at 22:42

score 0 · Accepted Answer · answered Jul 23 '16 at 23:13

My Conclusion:

Using MYSQL:

dbmd.ownDeletesAreVisible(TYPE_SCROLL_INSENSITIVE) RETURNED FALSE: "...It returns false if the ResultSet object still contains the deleted row..."

dbmd.deletesAreDetected(TYPE_SCROLL_INSENSITIVE) RETURNED FALSE: "...returns false if a row deleted from the ResultSet object is removed from it..."

Results:

ResultSet rs = stmt.executeQuery("select * from book");

printAll(rs); // Prints everything including foo

// deletes foo
while (rs.next()) {
    String title = rs.getString(2);
    if (title.equalsIgnoreCase("foo")) {
        rs.deleteRow();
    }
}

printAll(rs); // Prints everything without foo. Makes no sense.

Using Apache Derby DB:

dbmd.ownDeletesAreVisible(TYPE_SCROLL_INSENSITIVE) RETURNED TRUE: "...If the deleted row is removed or replaced by an empty row, the method will return true..."

dbmd.deletesAreDetected(TYPE_SCROLL_INSENSITIVE) RETURNED TRUE: "...The method will return true if the deleted row is replaced by an empty or invalid row..."

Results:

ResultSet rs = stmt.executeQuery("select * from book");

printAll(rs); // Prints everything including foo

// deletes foo
while (rs.next()) {
    String title = rs.getString(2);
    if (title.equalsIgnoreCase("foo")) {
        rs.deleteRow();
    }
}

/* Prints 'null' instead of 'foo' here. Now this makes sense */     
printAll(rs);

Conclusion:

This MySQL implementation did not adhere to the JDBC specification properly. The two aforementioned methods contradict with each other as well as against the outputs.

Apache Derby DB properly implements the JDBC specs.

DatabaseMetaData.ownDeletesAreVisible vs DatabaseMetaData.deletesAreDetected Controversy

2 Answers2