Search code examples
androidsqlitesql-deletepragma

SQLite changes() function reports 0 rows changed in certain delete statements

I've recently found the CHANGES() function available inside of SQLite. I'm doing something like the following inside of my Android code:

db.execSQL(sqlStatement, argumentArray);
int result;
SQLiteStatement stmt = db.compileStatement("SELECT CHANGES()");
try {
    return stmt.simpleQueryForLong();
} finally {
    stmt.close();
}

What I'm seeing that I get good data for all statements such as:

UPDATE `footable` SET `stuff` = 'fepojefpjo'   (returns 1 row updated)
DROP TABLE `footable`                          (returns 2 rows dropped)
DELETE FROM `footable` WHERE `id` IN (?,?)     (returns 2 rows deleted)

But I always get 0 rows changed when I issue the following statement -- even if there are definitely rows that are deleted:

DELETE FROM `footable`                         (always returns 0)

Since I'm deleting everything from the table, I wonder if SQLite is doing some sort of truncation operation underneath the covers. If I just adding a junk WHERE 1 to the end of the statement it returns the right number of rows.

DELETE FROM `footable` WHERE 1                 (works)

Questions:

  1. Can anyone confirm this behavior? Is this to be expected?
  2. Is SQLite truncating the table here?
  3. Aside from counting the rows before the DELETE, is there any way to work around this?
Solution

  • I think this is most likely due to the so-called Truncate Optimization (I say most likely because I'm not sure which version of SQLite you are using). When there is no WHERE clause, SQLite uses an optimization to erase the entire table content without having to visit each row of the table individually. See the reference docs for DELETE.

    Note that this behavior is fixed since 3.6.5.