Hi *, while working on a database upgrade on our application I found a bug in SQLite. I was adding a not null constraint by updating the sqlite_master table and to see the check fail used pragma integrity_check to find the offending rows that I know are there.
However, the integrity check just passes. This in contrast to the documentation: > The integrity_check pragma looks for out-of-order records, missing > pages, malformed records, missing index entries, and UNIQUE and NOT > NULL constraint errors. Source: https://www.sqlite.org/pragma.html#pragma_integrity_check Here is a trivial demonstration to illustrate this problem: * Create a table with NOT NULL constraints > torsten.landschoff@horatio:~/report$ /opt/sqlite-dev/bin/sqlite3 demo.db > SQLite version 3.14.2 2016-09-12 18:50:49 > Enter ".help" for usage hints. > sqlite> create table example (name varchar not null, address varchar not > null); > sqlite> * Observe: Pragma integrity check does not report NULL errors > torsten.landschoff@horatio:~/report$ (echo ".explain"; echo "explain pragma > integrity_check;" ) | /opt/sqlite-dev/bin/sqlite3 demo.db | grep NULL > 5 IsNull 2 10 0 00 if r[2]==NULL goto > 10 > 13 IsNull 2 18 0 00 if r[2]==NULL goto > 18 Interestingly it worked for other tables. Looking at the SQLite source code and the DDL of the other tables, it seems like checks are skipped for tables without indices. * Create an index > torsten.landschoff@horatio:~/report$ /opt/sqlite-dev/bin/sqlite3 demo.db > SQLite version 3.14.2 2016-09-12 18:50:49 > Enter ".help" for usage hints. > sqlite> create index ix_demo on example(name); > sqlite> * Interestingly, not null constraints are checked now > torsten.landschoff@horatio:~/report$ (echo ".explain"; echo "explain pragma > integrity_check;" ) | /opt/sqlite-dev/bin/sqlite3 demo.db | grep NULL > 5 IsNull 2 10 0 00 if r[2]==NULL goto > 10 > 19 NotNull 3 25 0 00 if r[3]!=NULL > goto 25 > 21 String8 0 3 0 NULL value in example.name 00 > r[3]='NULL value in example.name' > 26 NotNull 3 32 0 00 if r[3]!=NULL > goto 32 > 28 String8 0 3 0 NULL value in example.address 00 > r[3]='NULL value in example.address' > 59 IsNull 2 64 0 00 if r[2]==NULL goto > 64 Greetings, Torsten -- $---+----1----+----2----+----3----+----4----+----5----+----6----+ SCALE GmbH Niederlassung Dresden Torsten Landschoff Pohlandstraße 19 01309 Dresden Tel: +49-351-312002-10 Fax: +49-351-312002-29 SCALE GmbH Registergericht und Sitz: Ingolstadt, HRB 6384 Geschäftsführer: Dr.-Ing. Heiner Müllerschön, Dipl.-Math. Ulrich Franz _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
