Re: [sqlite] Series of statements results in a malformed database disk image
Thanks for the fix! Best, Manuel On Thu, May 9, 2019 at 7:12 PM Richard Hipp wrote: > On 5/9/19, Manuel Rigger wrote: > > > > I discovered a sequence of statements that results in a malformed > database > > disk image: > > > > Should be fixed now. > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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
Re: [sqlite] Series of statements results in a malformed database disk image
On 5/9/19, Manuel Rigger wrote: > > I discovered a sequence of statements that results in a malformed database > disk image: > Should be fixed now. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Series of statements results in a malformed database disk image
On 5/9/19, David Raymond 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Series of statements results in a malformed database disk image
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:sqlite-users-boun...@mailinglists.sqlite.org] 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 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 d...@sqlite.org ___ 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
Re: [sqlite] Series of statements results in a malformed database disk image
On 5/9/19, Manuel Rigger 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Series of statements results in a malformed database disk image
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 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 > 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 >> 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
Re: [sqlite] Series of statements results in a malformed database disk image
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 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 > 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
[sqlite] Series of statements results in a malformed database disk image
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
