Series of statements results in a malformed database disk image

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Series of statements results in a malformed database disk image

Manuel Rigger
Hi,

I discovered a sequence of statements that results in a malformed database
disk image:

CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY);
INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0);
UPDATE t1 SET c0 = NULL;
UPDATE OR REPLACE t1 SET c1 = 1;
SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL);

The last statement returns the following:
|1.0
Error: near line 5: database disk image is malformed

Unlike some of my previous test cases, this actually looks like something
that could happen in practice, or what do you think?

Best,
Manuel
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Series of statements results in a malformed database disk image

Chris Locke-3
Are you using a new database when you create your table, or using an
existing database?
Are you writing your database locally?
What operating system / sqlite version are you using?

The above test works for me...

> Execution finished without errors.

> Result: 1 rows returned in 62ms

> At line 4:

> SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL);




On Thu, May 9, 2019 at 3:47 PM Manuel Rigger <[hidden email]>
wrote:

> Hi,
>
> I discovered a sequence of statements that results in a malformed database
> disk image:
>
> CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY);
> INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0);
> UPDATE t1 SET c0 = NULL;
> UPDATE OR REPLACE t1 SET c1 = 1;
> SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL);
>
> The last statement returns the following:
> |1.0
> Error: near line 5: database disk image is malformed
>
> Unlike some of my previous test cases, this actually looks like something
> that could happen in practice, or what do you think?
>
> Best,
> Manuel
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Series of statements results in a malformed database disk image

Chris Locke-3
Edit: HOWEVER, just ran an integrity check, and that did fail.
"wrong # of entries in index sqlite_autoindex_t1_1"



On Thu, May 9, 2019 at 3:52 PM Chris Locke <[hidden email]> wrote:

> Are you using a new database when you create your table, or using an
> existing database?
> Are you writing your database locally?
> What operating system / sqlite version are you using?
>
> The above test works for me...
>
> > Execution finished without errors.
>
> > Result: 1 rows returned in 62ms
>
> > At line 4:
>
> > SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL);
>
>
>
>
> On Thu, May 9, 2019 at 3:47 PM Manuel Rigger <[hidden email]>
> wrote:
>
>> Hi,
>>
>> I discovered a sequence of statements that results in a malformed database
>> disk image:
>>
>> CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY);
>> INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0);
>> UPDATE t1 SET c0 = NULL;
>> UPDATE OR REPLACE t1 SET c1 = 1;
>> SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL);
>>
>> The last statement returns the following:
>> |1.0
>> Error: near line 5: database disk image is malformed
>>
>> Unlike some of my previous test cases, this actually looks like something
>> that could happen in practice, or what do you think?
>>
>> Best,
>> Manuel
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Series of statements results in a malformed database disk image

Richard Hipp-3
In reply to this post by Manuel Rigger
On 5/9/19, Manuel Rigger <[hidden email]> wrote:

>
> I discovered a sequence of statements that results in a malformed database
> disk image:
>
> CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY);
> INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0);
> UPDATE t1 SET c0 = NULL;
> UPDATE OR REPLACE t1 SET c1 = 1;
> SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL);
>
> The last statement returns the following:
> |1.0
> Error: near line 5: database disk image is malformed

Ticket here: https://www.sqlite.org/src/tktview/6c1d3febc00b22d457c7

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Series of statements results in a malformed database disk image

David Raymond
So it happens _before_ the update or replace? That is weird indeed.

Using "indexed by" still returns 2 rows from the index, but integrity check reports 1 missing, so I'm curious as to what part of the integrity got broken.



SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table t1 (c0, c1 real primary key);
QUERY PLAN
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)

sqlite> insert into t1 (c0, c1) values (0, 9223372036854775807), (0, 0);
QUERY PLAN
`--SCAN 2 CONSTANT ROWS

sqlite> select * from t1;
QUERY PLAN
`--SCAN TABLE t1
c0|c1
0|9.22337203685478e+18
0|0.0

sqlite> select c1 from t1 indexed by sqlite_autoindex_t1_1;
QUERY PLAN
`--SCAN TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1
c1
0.0
9.22337203685478e+18

sqlite> pragma integrity_check;
integrity_check
ok

sqlite> update t1 set c0 = null;
QUERY PLAN
`--SCAN TABLE t1

sqlite> select * from t1;
QUERY PLAN
`--SCAN TABLE t1
c0|c1
|9.22337203685478e+18
|0.0

sqlite> select c1 from t1 indexed by sqlite_autoindex_t1_1;
QUERY PLAN
`--SCAN TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1
c1
0.0
9.22337203685478e+18

sqlite> pragma integrity_check;
integrity_check
row 1 missing from index sqlite_autoindex_t1_1

sqlite>



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Thursday, May 09, 2019 11:08 AM
To: SQLite mailing list
Subject: Re: [sqlite] Series of statements results in a malformed database disk image

On 5/9/19, Manuel Rigger <[hidden email]> wrote:

>
> I discovered a sequence of statements that results in a malformed database
> disk image:
>
> CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY);
> INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0);
> UPDATE t1 SET c0 = NULL;
> UPDATE OR REPLACE t1 SET c1 = 1;
> SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL);
>
> The last statement returns the following:
> |1.0
> Error: near line 5: database disk image is malformed

Ticket here: https://www.sqlite.org/src/tktview/6c1d3febc00b22d457c7

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Series of statements results in a malformed database disk image

Richard Hipp-3
On 5/9/19, David Raymond <[hidden email]> wrote:
> I'm curious as to what part of the integrity got
> broken.

There is an index on a REAL value.  Maintaining such an index requires
doing equality comparisons on floating-point values.  The dangers of
doing equality comparisons on floating-point values are well known.
This is appears to be an instance where SQLite is not handling this
inherently risky operation quite correctly.  My initial guess is that
the problem is somehow related to SQLite's attempts to store floating
point values as integers in order to safe disk space, when the
floating point value can be represented by an integer.  That
optimization works well when storing floating point values like 1.0
and 0.0, but might be running into round-off error problems when
storing 9223372036854775807.0.  Still looking.....
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Series of statements results in a malformed database disk image

Richard Hipp-3
In reply to this post by Manuel Rigger
On 5/9/19, Manuel Rigger <[hidden email]> wrote:
>
> I discovered a sequence of statements that results in a malformed database
> disk image:
>

Should be fixed now.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Series of statements results in a malformed database disk image

Manuel Rigger
Thanks for the fix!

Best,
Manuel

On Thu, May 9, 2019 at 7:12 PM Richard Hipp <[hidden email]> wrote:

> On 5/9/19, Manuel Rigger <[hidden email]> wrote:
> >
> > I discovered a sequence of statements that results in a malformed
> database
> > disk image:
> >
>
> Should be fixed now.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users