My view is that the general thinking of the program here is simply: "just don't 
make things worse." It can't help what pragmas (ie ignore_check_constraints, 
writable_schema etc) others may have turned on for their connections, or what 
sort of junk was there when it arrived. In its head it's going to hear its 
Mom's voice saying "if all the other kids were ignoring not null constraints, 
would you?" and simply not let you add new errors. As to the truncated page it 
would be kind of bad for it to say "hey, someone else corrupted 28 bytes, so I 
won't give you info on the other 99% that I can read just fine." All that being 
said though it might be nice to know there was a noticed issue.

Remember that with the select you're asking it to tell you what "is" in there, 
not what "should" be in there. Also, quietly dealing with a different number of 
fields found in the file compared to what it parsed from the schema is how it 
handles "alter table add column" without needing to rewrite the entire table 
when you do that. That might also explain why you can see nulls for fields that 
got truncated.

So I suppose what it comes down to then is whether it should return OK, or some 
other code that says "I did what you told me, but I found a problem that wasn't 
so bad that it keep me from completing my task"

(Apologies for those who won't get the following reference from my psychotic 

create table objects (object_type, object_location,
  check (case object_location when 'out on the wing' then object_type = 
'nothing' else 1 end));

select * from objects where object_location = 'out on the wing';
something|out on the wing

Should that return SQLITE_OK, SQLITE_CORRUPT or 

Or should it have looked at the check constraint and just returned...

nothing|out on the wing

...despite it seeing...

something|out on the wing

...because it just shouldn't exist.

-----Original Message-----
From: sqlite-users [] On 
Behalf Of Rowan Worth
Sent: Thursday, January 12, 2017 10:17 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Null returned from NOT NULL column thanks to undetected 

Hi guys,

Ran into an interesting situation recently where a database was transferred
via FTP and the client somehow managed to truncate the file. As a result
the last page was only 337 bytes rather than the expected 1024.
Surprisingly running a SELECT on the affected table works without sqlite
returning any error!

However several of the returned rows are completely blank, despite 7 out of
8 columns having a NOT NULL constraint. Anyway we came up with a simple

 sqlite3 test.db "CREATE TABLE test_table (A INT4 NOT NULL, B INT4 NOT
 for N in `seq 1 10`
     sqlite3 test.db "INSERT INTO test_table (A, B) VALUES ($N, $N);"

 dd if=test.db of=cropped.db bs=2020 count=1
 sqlite3 cropped.db "SELECT * FROM test_table;"

Which produces results:


test.db is two pages long, so the truncation here is only 28 bytes. I
realise that some types of corruption are too small to notice without some
kind of checksum/error checking code, which feels a bit heavyweight for the
general case. But here it seems like there are some pretty significant red

1. the database contains an incomplete page
2. a NOT NULL column contains a NULL cell

"PRAGMA integrity_check" does flag problems with cropped.db, but because of
these two conditions I wonder if this is something sqlite could catch in
normal operation and return SQLITE_CORRUPT? Or are there reasons/history
which would render this conclusion inaccurate?

I notice that if I modify the database and another page gets added, sqlite
*does* start returning SQLITE_CORRUPT so I wonder if there's something
special about the last page?
sqlite-users mailing list
sqlite-users mailing list

Reply via email to