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 mind) 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'; object_type|object_location something|out on the wing Should that return SQLITE_OK, SQLITE_CORRUPT or SQLITE_UHH_SOMETHINGS_NOT_QUITE_RIGHT_HERE ? Or should it have looked at the check constraint and just returned... object_type|object_location nothing|out on the wing ...despite it seeing... object_type|object_location something|out on the wing ...because it just shouldn't exist. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] 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 corruption 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 reproducer: sqlite3 test.db "CREATE TABLE test_table (A INT4 NOT NULL, B INT4 NOT NULL);" for N in `seq 1 10` do sqlite3 test.db "INSERT INTO test_table (A, B) VALUES ($N, $N);" done dd if=test.db of=cropped.db bs=2020 count=1 sqlite3 cropped.db "SELECT * FROM test_table;" Which produces results: | | | | 0|0 6|6 7|7 8|8 9|9 10|10 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 flags: 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
