sqlite3 delete does not delete everything?

Question

Whats going on here? I would expect the following delete to delete everything from the table. Is there a fundamental mis-understanding of how sqlite3 behaves on my part?

sqlite> .schema

CREATE TABLE ip_domain_table (ip_domain TEXT, answer TEXT, ttl INTEGER, PRIMARY KEY(ip_domain, answer, ttl));

sqlite> select count(*) from ip_domain_table where ttl < 9999999999 ;

1605343

sqlite> pragma cache_size=100000; delete from ip_domain_table where ttl < 9999999999; 

sqlite> select count(*) from ip_domain_table where ttl < 9999999999 ;

258

Q: Why does the count show "258"? Shouldn't it be 0 instead?

If I do this instead, it deletes all the entries as expected.

sqlite> select count(*) from ip_domain_table;

1605343

sqlite> pragma cache_size=100000; delete from ip_domain_table;

sqlite> select count(*) from ip_domain_table;

0

Answer 1

It's important to remember that SQLite has something called type affinity, which means that each column's datatype is merely recommended and NOT ENFORCED. All column types can store any type of data. This means that your integer column could store numbers greater than 9999999999, or even strings.

I would do the following:

1. Check that all of your ttl values are less than 9999999999.
2. What is the result of "select * from ip_domain_table limit 1", after your first delete? If the ttl column of your result is a string or a number greater than 9999999999, you have your answer.

There's nothing wrong with simply using delete from ip_domain_table;, like you do above.

Good luck!

Answer 2

First of all, please be aware that the ttl column of your table is NOT the primary key. It may contain values bigger than the 9999999999 you have specified.

Then, if you want to delete all entries from the table, try this instead:

DELETE FROM ip_domain_table;

Answer 3

Thanks for the answers. Some follow up questions:

1) The first "select" shows that there are 1605343 entries which have ttl below 9999999999. So after the following delete, shouldn't the number of entries go down to 0? If the TTL corresponding to these entries were something else, why should they be counted for in the select in the first place? The delta of entries between the two selects should be 0.

2) I do not want to delete all the entries from the table. I just want to delete the ones which have expired (below TTL 'X'). The select shows that there are 1605343 entries which have ttl below 9999999999, which is also the number of entries in the table. So after either of the delete the number of entries in the table should be 0.

3) Here is the ouput with the limit after the first delete. sqlite> select * from ip_domain_table where ttl < 9999999999 limit 1;
107.35.138.41|127.2.0.2|1266895619

It is def. less than 9999999999. How do I know whether it is stored as string?