sqlite3 delete does not delete everything?

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_domai开发者_运维问答n_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

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!

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;