SQLITE Query yields different (more) results when adding order clause

Question

Without goin into too much detail about the underlying structure, has anyone ever experienced something like this?

I do a select * on a table which results in 1187 lines I do the same select and add an order clause (on ANY field) and get 1195 lines EDIT: this is not true! Only ordering on displayname and id will bring back the missing results. These are obviously indexed!

i can reproduce the behaviour with this specific database and it messes up some functionality in my software where i run some queries without ordering the result (as the order is not important)

what can be the cause of this? I will provide further info as nessecary, but the underlying structure is rather complex.

EDIT: okay, the table looks like this:

id GUID PRIMARY KEY NOT NULL, 
tag TEXT,
displayname TEXT,
create_user GUID,
create_time INTEGER,
key_date DATETIME,
change_time INTEGER

I have looked at the "missing" lines and seen nothing special about them.

Here ist the result of the integrity_check:

*** in database main ***
On tree page 1974 cell 8: 2nd reference to page 62381
On tree page 1974 cell 8: Child page depth differs
On tree page 1973 cell 1: Child page depth differs
On tree page 1973 cell 2: Child page depth differs
On tree page 8931 cell 5: 2nd reference to page 57003
On tree page 8931 cell 5: Child page depth differs
On tree page 8931 cell 6: Child page depth differs
On tree page 1981 cell 8: 2nd reference to page 62380
On tree page 1981 cell 8: Child page depth differs
On tree page 1976 cell 1: Child page depth differs
On tree page 1976 cell 2: Child page depth differs
On tree page 8927 cell 5: 2nd reference to page 57001
On tree page 8927 cell 5: Child page depth differs
On tree page 8927 cell 6: Child page depth differs
On tree page 62331 cell 5: 2nd reference to page 57915
On tree page 62331 cell 5: Child page depth differs
On page 62331 at right child: 2nd reference to page 57828
On tree page 26340 cell 3: Child page depth differs
On tree page 26340 cell 4: Child page depth differs
On tree page 26341 cell 4: 2nd reference to page 49345
On tree page 26341 cell 4: Child page depth differs
On tree page 26341 cell 5: Child page depth differs
On tree page 58249 cell 0: 2nd reference to page 57717
On tree page 58249 cell 1: 2nd reference to page 57711
On tree page 58249 cell 2: 2nd reference to page 57700
On tree page 58249 cell 3: 2nd reference to page 5941
On tree page 58249 cell 4: Child page depth differs
On tree page 59501 cell 3: 2nd reference to page 56719
On tree page 59501 cell 3: Child page depth differs
On tree page 59501 cell 4: Child page depth differs
On tree page 54755 cell 1: Rowid 11 out of order (max larger than parent max of 10)
On tree page 54852 cell 1: Rowid 11 out of order (min less than parent min of 11)
On tree page 60653 cell 0: 2nd reference to page 58668
On tree page 60653 cell 1: Child page depth differs
On tree page 3834 cell 6: 2nd reference to page 62279
On tree page 3834 cell 6: Child page depth differs
On tree page 3834 cell 7: Child page depth differs
On tree page 60737 cell 4: 2nd reference to page 58694
On tree page 60737 cell 4: Child page depth differs
On tree page 4632 cell 9: Child page depth differs
On tree page 33958 cell 1: Child page depth differs
On tree page 7205 cell 0: 2nd reference to page 7204
On tree page 7205 cell 1: 2nd reference to page 7256
On tree page 7205 cell 2: Child page depth differs
Page 5033 is never used
Page 54385 is never used
Page 54481 is never used
Page 54582 is never used
Page 54639 is never used
Page 54687 is never used
Page 54785 is never used
Page 54848 is never used
Page 54849 is never used
Page 54850 is never used
Page 54873 is never used
Page 55669 is never used
Page 55762 is never used
Page 55871 is never used
Page 55920 is never used
Page 55979 is never used
Page 56178 is never used
Page 56223 is never used
Page 56224 is never used
Page 56227 is never used
Page 56237 is never used
Page 56243 is never used
Page 56245 is never used
Page 56248 is never used
Page 56261 is never used
Page 56377 is never used
Page 56824 is never used
Page 57261 is never used
Page 57266 is never used
Page 57303 is never used
Page 57325 is never used
Page 57393 is never used
Page 57424 is never used
Page 57473 is never used
Page 57835 is never used
Page 57847 is never used
Page 57874 is never used
Page 57875 is never used
Page 57887 is never used
Page 57893 is never used
Page 57917 is never used
Page 57918 is never used
Page 57919 is never used
Page 57923 is never used
Page 57932 is never used
Page 59220 is never used
Page 59224 is never used
Page 59226 is never used
Page 59228 is never used
Page 59229 is never used
Page 59230 is never used
Page 59231 is never used
Page 59232 is never used
Page 59235 is never used
Page 59238 is never used
Page 59249 is never used

Answer 1

The database file has become corrupted.

This can happen in various ways, but a power failure or similarly catastrophic failure with unwritten buffers may do it.

You need to build a new database file.

Follow these steps:

1. Make a backup copy of the current database (so that you can always revert back to the corrupt state if you mess it up worse than what it currently is)

2. Dump the contents of your current database:

   sqlite3 test.db
   > .mode insert
   > .output test.sql
   > .dump
   

   This may take a while

3. Create a new database and import your dump:

   sqlite3 new.db
   > .read test.sql
   

4. Verify the integrity of your new database:

   > pragma integrity_check;
   ok
   

5. Use the new database instead of the old one

Note, you may lose data in this process, but the database is corrupt, you should not continue to use it!