[sqlite] Database corruption using zipvfs on android 64 bits (x86_64)
Alexandre Mainville wrote: > Hi, > > I am experiencing database corruption using zipvfs on android 64 > bits (x86_64). > The same program executing on 32 bits runs without problems. > > The program creates a new database on every run and does a series of > inserts and updates (always the same series of operations). > > Both programs produce the same SQLITE_DEBUG trace up until the point the 64 > bit version outputs: > zipvfs database is corrupt. Line 1100 of [9d6c1880fb] > because of a zero size payload in a slot header. > > I am waiting on a device to test behavior with arm64-v8a. > > I am using sqlite 3.8.8.3 and zipvfs 3.8.8. > > If anyone has experienced the same or has some insight on this, it would be > greatly appreciated. > > Thanks > > Alex I'm not sure whether it's the same problem, but on our project we had memory corruptions on Android with zipvfs. The bug happened only on Android. Valgrind and asan on Linux was showing no bug. The bug turned out not to be in SQLite, but in zlib. Android was using zlib-1.2.7. After using zlib-1.2.8 instead, corruption did not happen anymore. Reading the changes in zlib-1.2.8 at http://www.zlib.net I saw: "Version 1.2.8 fixes a very rare bug in decompression. All users are encouraged to upgrade immediately." Can you check whether you use zlib-1.2.7 or zlib-1.2.8? Dominique
[sqlite] Simple Math Question
> On Thursday, 22 October, 2015, at 13:45, Rousselot, Richard A
> said:
> Doing the following math, why is it that the results are not all returning
> "yes"?
>
> SELECT
> (9.2+7.9+0+4.0+2.6+1.3),
> case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else
> "no" end,
> (9.2+7.8+0+3.0+1.3+1.7),
> case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else
> "no" end,
> (9.2+7.9+0+1.0+1.3+1.6),
> case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else
> "no" end
> FROM
> sometable;
>
> Result
> 25.0 no 23.0 yes 21.0 no
I know this has been discussed to death. I wrote a function called "ulps"
which can be used as an extension to SQLite3 and is defined as follows
(compiles in MSVC and GCC , should work on all compilers I think):
SQLITE_PRIVATE void _ulps(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
register double x = sqlite3_value_double(argv[0]);
register double y = sqlite3_value_double(argv[1]);
register double u1 = fabs(_nextafter(x, 1.7976931348623157e+308) - x);
register double u2 = fabs(_nextafter(x, -1.7976931348623157e+308) - x);
register double au = (u1 + u2) / 2.0;
sqlite3_result_double(context, (x - y) / au);
}
It computes the absolute distance between x and the next representable double
towards +Inf, and the absolute distance between x and the next representable
double towards -Inf. It then averages these distances and returns the number
of times this interval occurs in the difference between x and y.
Running the original problem:
SQLite version 3.10.0 2015-11-07 01:19:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode column
sqlite> .header on
sqlite> create table test (x real, y real);
sqlite> insert into test values (9.2 + 7.9 + 0 + 4.0 + 2.6 + 1.3, 25.0);
sqlite> insert into test values (9.2 + 7.8 + 0 + 3.0 + 1.3 + 1.7, 23.0);
sqlite> insert into test values (9.2 + 7.9 + 0 + 1.0 + 1.3 + 1.6, 21.0);
sqlite> select x, y, case when x == y then 'yes' else 'no' end as equal,
ulps(x, y) as ulps from test;
x y equal ulps
-- -- -- --
25.025.0no 1.0
23.023.0yes 0.0
21.021.0no 1.0
sqlite>
This shows that although the x and y may not be exactly equal, they are merely
1 ULP different from each other using the representation precision of x.
This works for comparing numbers of any scale ... of course if the numbers are
not "relatively equal" the number of ULPS between them may huge.
So, instead of "x == y" using "abs(ulps(x - y)) < 5" is true if x and y are
within 5 ULPS of each other.
[sqlite] FTS5 rank configuration option
On 11/06/2015 02:26 PM, chromedout64 at yahoo.com wrote:
> The documentation says that a persistent rank can be set with
> INSERT INTO ft(ft, rank) VALUES('rank', 'bm25(10.0, 5.0)');
> However, I can only get it to work on the same database connection as the
> query. Subsequent database connections seem to use the standard default
> ranking. Is this by design?
Thanks for reporting this. Might be fixed here:
http://sqlite.org/src/info/33e6606f5e497e81
Dan.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] field name in UDF
On Fri, Nov 6, 2015 at 7:04 PM, Nelson, Erik - 2 < erik.l.nelson at bankofamerica.com> wrote: > select quarter(t1.a) from t1 ; > > I might hope to get 'a' or 't1.a'. Any ideas? > UDFs (in any scripting environment) don't get that level of info. They only get passed the values resolved by the surrounding evaluation engine. By the same token (no pun intended), in JavaScript: var x = 3; quarter(x); gets only the number 3, not any information about where that 3 comes from or how it was derived. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] field name in UDF
On Fri, Nov 6, 2015 at 6:50 PM, Nelson, Erik - 2 < erik.l.nelson at bankofamerica.com> wrote: > I have a user-defined function something like > > void quarter_sqlite3(sqlite3_context *context, int argc, sqlite3_value > **argv); > > for each sqlite3_value being passed in, it would sometimes be helpful to > have the associated field (if any) that the value is associated with. > > Is there any way to retrieve that? > UDFs receive expanded/evaluated values, not fields: select quarter(t1.a+t2.a+t3.a+3.0) from t1, t2, t3...; what field would you expect to get there? -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] field name in UDF
Stephan Beal wrote on Friday, November 06, 2015 1:00 PM > On Fri, Nov 6, 2015 at 6:50 PM, Nelson, Erik - 2 wrote: > > > I have a user-defined function something like > > > > void quarter_sqlite3(sqlite3_context *context, int argc, > sqlite3_value > > **argv); > > > > for each sqlite3_value being passed in, it would sometimes be helpful > > to have the associated field (if any) that the value is associated > with. > > > > Is there any way to retrieve that? > > > > UDFs receive expanded/evaluated values, not fields: > > select quarter(t1.a+t2.a+t3.a+3.0) from t1, t2, t3...; > > what field would you expect to get there? Hence the "(if any)". For select quarter(t1.a) from t1 ; I might hope to get 'a' or 't1.a'. Any ideas? -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message.
[sqlite] field name in UDF
I have a user-defined function something like void quarter_sqlite3(sqlite3_context *context, int argc, sqlite3_value **argv); for each sqlite3_value being passed in, it would sometimes be helpful to have the associated field (if any) that the value is associated with. Is there any way to retrieve that? -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message.
[sqlite] Non-transitive numeric equality
On 6 Nov 2015, at 2:20pm, Wade, William wrote: > I have an engineering application, where double can be part of a key. So do I, using Longitude and Latitude in GPS coordinates. A lot of things done inside GPS devices amount to "List everything in this rectangle". This is the problem. Theoretically we are saying "You cannot test for equality in REAL so we should remove COLLATE for REAL numbers.". But there are thousands of apps out there which use REALs in keys, get useful results fast, and don't care how edge cases are handled. So we carry on as before, occasionally telling someone new why the carpet's a bit lumpy over by the coat rack. Simon.
[sqlite] Non-transitive numeric equality
I have an engineering application, where double can be part of a key. In cases where I do a comparison that implies a test for equality, I don't necessarily care what happens in the really close cases. SELECT x,y FROM mytable WHERE x >= 1234 AND x < 5678 There may be about a 1 records in that time range, and for engineering purposes when I write that I don't particularly care whether I am getting 1, 10001 or 10002 entries. However I do depend on getting consistent answers, and on (x >= 1234) being disjoint from (x < 1234) and that those two ranges cover the number line. I suspect that sqlite makes that kind of guarantee, at least if I explicitly cast values to double whenever I do an INSERT or UPDATE or WHERE, although that seems like an easy thing to forget to do. However, nothing in the sqlite documentation promises that sqlite won't internally perform an equality test on primary keys, so it seems that I have to worry that if equality is not transitive, my database can be in arbitrarily bad shape (select gives answers that aren't even close to looking right). Is the answer for this kind of thing to cast all of my values to double when feeding them to sqlite, if I want it to do math using "double" rules? I suspect sqlite could get the "correct" answers when doing comparisons between two types where one type is not a superset of the other by converting both values to bigint rationals and comparing those (I think the sqlite numeric types are all representable as bigint rationals). That may be overkill (and not all that lite). Regards -Original Message- From: Richard Hipp [mailto:d...@sqlite.org] Sent: Thursday, November 05, 2015 8:55 AM To: SQLite mailing list Subject: Re: [sqlite] Non-transitive numeric equality On 11/5/15, Zsb?n Ambrus wrote: >... It seems that equality of numeric values isn't transitive, when both > integers and reals are involved... You should not compare floating-point numbers for equality. Floating-point numbers are, by definition, approximations. When you compare floating-point numbers, therefore, you get an approximate answer. -- D. Richard Hipp drh at sqlite.org ** This e-mail and any attachments thereto may contain confidential information and/or information protected by intellectual property rights for the exclusive attention of the intended addressees named above. If you have received this transmission in error, please immediately notify the sender by return e-mail and delete this message and its attachments. Unauthorized use, copying or further full or partial distribution of this e-mail or its contents is prohibited. **
[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database
On 11/06/2015 12:36 PM, chromedout64 at yahoo.com wrote: > I noticed that this update is not included in the official 3.9.2 release. How > is it determined when an update such as this one gets included in a release? > Thanks for any info. Most likely as part of 3.10.0. You can always download the SQLite trunk, either via fossil or via a link like: http://www.sqlite.org/src/tarball/SQLite-trunk.tgz?uuid=trunk Then run [./configure && make amalgamation-tarball] to build an autoconf package. Regards, Dan. > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] field name in UDF
On 11/6/2015 12:50 PM, Nelson, Erik - 2 wrote: > I have a user-defined function something like > > void quarter_sqlite3(sqlite3_context *context, int argc, sqlite3_value > **argv); > > for each sqlite3_value being passed in, it would sometimes be helpful to have > the associated field (if any) that the value is associated with. > > Is there any way to retrieve that? None that I know of. -- Igor Tandetnik
[sqlite] Non-transitive numeric equality
On 11/6/2015 9:20 AM, Wade, William wrote: > However I do depend on getting consistent answers, and on (x >= 1234) being > disjoint from (x < 1234) and that those two ranges cover the number line. I > suspect that sqlite makes that kind of guarantee, at least if I explicitly > cast values to double whenever I do an INSERT or UPDATE or WHERE, although > that seems like an easy thing to forget to do. Declare your columns with REAL affinity ( http://www.sqlite.org/datatype3.html ). Such columns never contain integers, only floats (any attempt to insert an integer coerces it to a float, possibly losing precision). One of the problems in the OPs example is that columns have no declared type, and thus BLOB affinity (which pretty much means, anything goes and no conversions are performed). -- Igor Tandetnik
[sqlite] Database corruption using zipvfs on android 64 bits (x86_64)
Hi, I am experiencing database corruption using zipvfs on android 64 bits (x86_64). The same program executing on 32 bits runs without problems. The program creates a new database on every run and does a series of inserts and updates (always the same series of operations). Both programs produce the same SQLITE_DEBUG trace up until the point the 64 bit version outputs: zipvfs database is corrupt. Line 1100 of [9d6c1880fb] because of a zero size payload in a slot header. I am waiting on a device to test behavior with arm64-v8a. I am using sqlite 3.8.8.3 and zipvfs 3.8.8. If anyone has experienced the same or has some insight on this, it would be greatly appreciated. Thanks Alex Here is the trace: write page[1] to (456,127,0) write page[2] to (589,20,17) read page[1] from (24,127) read page[1] from (24,127) write page[1] to (632,126,0) write page[2] to (764,33,4) read page[1] from (24,126) read page[1] from (24,126) write page[1] to (807,509,0) write page[3] to (1322,20,17) write page[4] to (1365,20,17) write page[5] to (1408,20,17) write page[6] to (1451,20,17) write page[7] to (1494,390,0) write page[8] to (1890,383,0) write page[9] to (2279,20,17) write page[10] to (2322,20,17) write page[11] to (2365,20,17) write page[12] to (2408,20,17) write page[13] to (2451,319,0) write page[14] to (2776,20,17) write page[15] to (2819,20,17) write page[16] to (2862,20,17) write page[17] to (2905,20,17) write page[18] to (2948,379,0) write page[19] to (,20,17) write page[20] to (3376,20,17) write page[21] to (3419,20,17) write page[22] to (3462,20,17) write page[23] to (3505,410,0) write page[24] to (3921,20,17) write page[25] to (3964,412,0) write page[26] to (4382,20,17) write page[27] to (4425,281,0) write page[28] to (4712,20,17) write page[29] to (4755,20,17) write page[30] to (4798,384,0) write page[31] to (5188,153,0) write page[32] to (5347,20,17) write page[33] to (5390,20,17) write page[34] to (5433,20,17) write page[35] to (5476,20,17) write page[36] to (5519,427,0) write page[37] to (5952,20,17) write page[38] to (5995,20,17) write page[39] to (6038,20,17) write page[40] to (6081,20,17) write page[41] to (6124,20,17) write page[42] to (6167,363,0) write page[43] to (6536,20,17) write page[44] to (6579,20,17) write page[45] to (6622,399,0) write page[46] to (7027,20,17) write page[47] to (7070,20,17) write page[48] to (7113,20,17) write page[49] to (7156,20,17) write page[50] to (7199,370,0) write page[51] to (7575,20,17) write page[52] to (7618,20,17) write page[53] to (7661,20,17) write page[54] to (7704,385,0) write page[55] to (8095,20,17) write page[56] to (8138,20,17) write page[57] to (8181,20,17) write page[58] to (8224,20,17) write page[59] to (8267,416,0) write page[60] to (8689,20,17) write page[61] to (8732,20,17) write page[62] to (8775,20,17) write page[63] to (8818,355,0) write page[64] to (9179,20,17) write page[65] to (9222,20,17) write page[66] to (9265,20,17) write page[67] to (9308,412,0) write page[68] to (9726,20,17) write page[69] to (9769,20,17) write page[70] to (9812,432,0) moving page[2] from (764,33,4) to (10250,33,0) write page[71] to (10293,20,17) write page[72] to (10336,20,17) write page[73] to (10379,20,17) write page[74] to (10422,20,17) write page[75] to (10465,442,0) moving page[1] from (807,509,0) to (10913,509,0) write page[76] to (11428,20,17) write page[77] to (11471,20,17) write page[78] to (11514,275,0) write page[79] to (11795,20,17) write page[80] to (11838,20,17) write page[81] to (11881,20,17) read page[1] from (24,509) read page[1] from (24,509) read page[1] from (24,509) write page[1] to (10913,509,0) write page[11] to (11924,199,0) write page[82] to (12129,806,0) write page[83] to (12941,803,0) write page[84] to (13750,804,0) write page[85] to (14560,804,0) read page[1] from (24,509) read page[1] from (24,509) read page[1] from (24,509) write page[1] to (10913,509,0) write page[11] to (15370,202,0) write page[86] to (15578,804,0) write page[87] to (16388,806,0) write page[88] to (17200,808,0) write page[89] to (18014,807,0) write page[90] to (11924,199,0) write page[91] to (18827,710,0) read page[1] from (24,509) read page[1] from (24,509) read page[1] from (24,509) write page[1] to (10913,509,0) write page[11] to (19543,203,0) write page[92] to (19752,808,0) write page[93] to (20566,810,0) write page[94] to (21382,808,0) write page[95] to (22196,808,0) write page[96] to (23010,633,0) read page[1] from (24,509) read page[1] from (24,509) read page[1] from (24,509) write page[1] to (10913,509,0) write page[96] to (23649,751,0) write page[97] to (24406,804,0) write page[98] to (25216,808,0) write page[99] to (26030,806,0) write page[100] to (26842,805,0) write page[101] to (27653,808,0) read page[1] from (24,509) read page[1] from (24,509) read page[1] from (24,509) write page[1] to (28467,510,0) write page[11] to (19543,203,0) write page[102] to (28983,806,0) write page[103] to (29795,804,0) write page[104] to (30605,807,0) write page[105] to (314
[sqlite] FTS5 rank configuration option
The documentation says that a persistent rank can be set with
INSERT INTO ft(ft, rank) VALUES('rank', 'bm25(10.0, 5.0)');
However, I can only get it to work on the same database connection as the
query. Subsequent database connections seem to use the standard default
ranking. Is this by design?
[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database
Great, that's exactly what I need, I'll give it a try.
[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database
I noticed that this update is not included in the official 3.9.2 release. How is it determined when an update such as this one gets included in a release? Thanks for any info.
