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();
        }
}

Answer 1

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.