Eric Scouten wrote:
Earlier today I posted a question about SQLite compiled into my
application generating a "database disk image is corrupted" on a
particular query, but the command-line application does not (even using
the same database file). I haven't seen that message posted yet, but I
have some additional information that may help in understanding the
problem.
The query that's running is essentially this: (table names obfuscated
since this is from an unannounced product)
SELECT foo.rowid FROM foo WHERE foo.x == ?;
When run from the command-line application (either version 3.1.3 or
3.2.8), this query runs without error.
sqlite> SELECT foo.rowid FROM foo WHERE foo.x == 25;
61 // expected result
sqlite> explain SELECT foo.rowid FROM foo WHERE foo.x == ?;
0|Goto|0|20|
1|Integer|0|0|
2|OpenRead|1|117|keyinfo(1,BINARY)
3|SetNumColumns|1|2|
4|Variable|1|0|
5|NotNull|-1|8|
6|Pop|1|0|
7|Goto|0|18|
8|MakeRecord|1|0|o
9|MemStore|0|0|
10|MoveGe|1|18|
11|MemLoad|0|0|
12|IdxGE|1|18|+
13|RowKey|1|0|
14|IdxIsNull|1|17|
15|IdxRowid|1|0|
16|Callback|1|0|
17|Next|1|11|
18|Close|1|0|
19|Halt|0|0|
20|Transaction|0|0|
21|VerifyCookie|0|181|
22|Goto|0|1|
23|Noop|0|0|
When run from inside our application (using version 3.2.7 -- sorry,
haven't upgraded yet), however, we get different VDBE instructions:
2005-12-22 15:30:33.478 Got error: database disk image is malformed
Statement: SELECT foo.rowid FROM foo WHERE foo.x == ?;
addr | opcode | p1 | p2 | p3
--------------------------------------------------------------------------
0 | Goto | 0 | 20 |
1 | Integer | 0 | 0 | # (index for value x on table foo)
2 | OpenRead | 1 | 100 | keyinfo(1,BINARY)
3 | SetNumColumns | 1 | 2 |
4 | Variable | 1 | 0 |
5 | NotNull | -1 | 8 |
6 | Pop | 1 | 0 |
7 | Goto | 0 | 18 |
8 | MakeRecord | 1 | 0 | o
9 | MemStore | 0 | 0 |
10 | MoveGe | 1 | 18 |
11 | MemLoad | 0 | 0 |
12 | IdxGE | 1 | 18 | +
13 | RowKey | 1 | 0 |
14 | IdxIsNull | 1 | 17 |
15 | IdxRowid | 1 | 0 |
16 | Callback | 1 | 0 |
17 | Next | 1 | 11 |
18 | Close | 1 | 0 |
19 | Halt | 0 | 0 |
20 | Transaction | 0 | 0 |
21 | VerifyCookie | 0 | 199 |
22 | Goto | 0 | 1 |
23 | Noop | 0 | 0 |
For the record, our application and the SQLite command-line application
have been pointed to exactly the same database file. No other changes
have been made to the file, so they should be expected to behave
identically. But they don't. At address 2, the P2 parameter (which
should be the root page # for the index of field "x" on table "foo") is
100 in the embedded case and 117 in the command-line case. When I
inspect the sqlite_master table, it is obvious that 117 is the correct
answer. Page 100 is the root page of an unrelated table (not an index).
Has anyone seen anything like this before, and if so, can you help us
understand what would cause SQLite to generate this incorrect VDBE?
-Eric
Don't know if it's related but I have (under 2.8.17 and 2.8.16 also)
simple update query
UPDATE PRACOWNICY SET IMIE='Jacek',NAZWISKO='Placek' WHERE NR_PRACOWNIKA=12
which runs fine on sqlite.exe but in my application it simply do nothing
(return SQLITE_OK but do not update row)
More to say; this query runs correctly on both sqlite.exe and my
application:
UPDATE PRACOWNICY SET IMIE='Jacek' where NAZWISKO='Kowalski'
Regards
Boguslaw Brandys
