[sqlite] Record serialization infrastructure of SQLITE

[Deon Brewis]

Inspired by the json encoding thread.

We have a need for multi-dimensional data (like json), and currently use a 
record format for that that is derived from the SQLITE format with Serial 
Type/Size stored in varint Huffman encoding of twos-complement. It is a great 
fast, compact storage format. We added support for arrays and objects on top of 
it, but just recursive processing of blobs would allow for the same. However, 
we had to build our own make/extract/expression extensions from scratch to do 
this.

It would be very helpful if SQLITE can expose its internal record serialization 
infrastructure a bit so that there is a way (using the API's) to create an 
array of sqlite3_value from a blob, and blob from sqlite3_value array. E.g.

Reading:
blob = sqlite3_column_blob(_stmt, 2);
sqlite3_array** arr = sqlite3_deserialize(blob, );
val1 = sqlite3_value_int(arr[0]);
val2 = sqlite3_value_int(arr[1]);

Writing:
sqlite3_value* arr[2];
arr[0] = sqlite3_make_value_int(42);
arr[1] = sqlite3_make_value_int(43);
sqlite3_value blob = sqlite3_serialize(arr, 2);
sqlite3_bind_value(stmt, 2, blob);

It would probably also be useful if there's some limited expression functions 
to interact with it, but those are easy to build yourself. The serialization 
infrastructure however is pretty complex.

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Deon Brewis]

WHERE x IN (?1,?2,?3,?4...,?1000 )

And the IN clause is filled by a list or array that's held inside the calling 
application memory rather than in SQLITE.

The alternate to this is to create a virtual table wrapper over the internal 
datasets of the app. Which is of course better, but harder. (We need an STL for 
SQLite. SqliteTL?).

PS: Doesn't SQLITE internally order an IN list and do a join across it? It 
seems to perform better than I would expect from a flat array.

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Tuesday, February 4, 2020 10:59 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

On 4 Feb 2020, at 6:27pm, Alex Bronstein  wrote:

> In such cases, you can easily end
> up with more than 999 parameters.

I'm curious.  Can you show us some place where using 999 parameters is a 
reasonable way to use SQLite ?

> PostgreSQL and some other databases support a 16 bit parameter count (64K 
> parameters). Given current memory availability, can sqlite's default be 
> raised to something similar to that?

Might help to know that using the 64K'th parameter would cause SQLite to 
maintain a list 64K items long.  It's an array, not a key/value lookup.
___
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] New word to replace "serverless"

[Deon Brewis]

> "What is the word for that programming methodology that existed since the 
> beginning when there were no threads and everything was single-task?"

Real Mode.

- Deon

> On Jan 28, 2020, at 12:37 PM, Roman Fleysher  
> wrote:
> 
> What is the word for that programming methodology that existed since the 
> beginning when there were no threads and everything was single-task?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[Deon Brewis]

BYOT -> Bring Your Own Thread

Put another way: SQLITE is a BYOT Library.

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Richard Hipp
Sent: Monday, January 27, 2020 2:19 PM
To: General Discussion of SQLite Database 
Subject: [sqlite] New word to replace "serverless"

For many years I have described SQLite as being "serverless", as a way to 
distinguish it from the more traditional client/server design of RDBMSes.  
"Serverless" seemed like the natural term to use, as it seems to mean "without 
a server".

But more recently, "serverless" has become a popular buzz-word that means 
"managed by my hosting provider rather than by me."  Many readers have 
internalized this new marketing-driven meaning for "serverless" and are hence 
confused when they see my claim that "SQLite is serverless".

How can I fix this?  What alternative word can I use in place of "serverless" 
to mean "without a server"?

Note that "in-process" and "embedded" are not adequate substitutes for 
"serverless".  An RDBMS might be in-process or embedded but still be running a 
server in a separate thread. In fact, that is how most embedded RDBMSes other 
than SQLite work, if I am not much mistaken.

When I say "serverless" I mean that the application invokes a function, that 
function performs some task on behalf of the application, then the function 
returns, *and that is all*.  No threads are left over, running in the 
background to do housekeeping.  The function does send messages to some other 
thread or process.  The function does not have an event loop.  The function 
does not have its own stack. The function (with its subfunctions) does all the 
work itself, using the callers stack, then returns control to the caller.

So what do I call this, if I can no longer use the word "serverless"
without confusing people?

"no-server"?
"sans-server"?
"stackless"?
"non-client/server"?


--
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] New word to replace "serverless"

[Deon Brewis]

This is a very important design distinction, not just implementation detail, If 
you know and internalize up front that SQLITE will run only on the thread you 
give it, you can architect your application better from the beginning and not 
e.g. go down one path initially and wonder how the get async behavior later.

I work on an old codebase where someone made some bad architectural decisions 
up front based on their misunderstanding of the SQLite execution model. It's 
virtually impossible to undo now without rewriting the app.

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Jens Alfke
Sent: Tuesday, January 28, 2020 9:32 AM
To: SQLite mailing list 
Cc: Rowan Worth 
Subject: Re: [sqlite] New word to replace "serverless"



> On Jan 28, 2020, at 3:18 AM, Richard Hipp  wrote:
> 
> Things like MySQL-embedded and H2 run a "server" as a thread instead 
> of as a separate process.  ...  So this is really the same thing as a 
> server using IPC except that the server runs in the same address space 
> as the client.

I see that as a mere implementation detail, since it doesn't affect the way the 
developer configures or uses the system. Running an engine on a background 
thread(s) is an increasingly common technique as CPUs become more concurrent, 
especially in mobile apps where it's forbidden to 'jank' up the GUI by blocking 
the main thread.

For example, on iOS and macOS the HTTP client library (NSURLSession) and the 2d 
graphics compositing engine (CoreAnimation) do all their heavy lifting on 
background threads, but I wouldn't think of calling either of them server-based.

—Jens
___
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] New word to replace "serverless"

[Deon Brewis]

In C/C++ the closest concept is a Header Only Library.

Except that SQLITE is not only C+++, and it's not header only...

Library Only Implementation?
In-Proc / In-Thread Library?
Self Contained Library?

Looks like I'm on a generally "Library" theme here...

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Richard Hipp
Sent: Monday, January 27, 2020 2:19 PM
To: General Discussion of SQLite Database 
Subject: [sqlite] New word to replace "serverless"

For many years I have described SQLite as being "serverless", as a way to 
distinguish it from the more traditional client/server design of RDBMSes.  
"Serverless" seemed like the natural term to use, as it seems to mean "without 
a server".

But more recently, "serverless" has become a popular buzz-word that means 
"managed by my hosting provider rather than by me."  Many readers have 
internalized this new marketing-driven meaning for "serverless" and are hence 
confused when they see my claim that "SQLite is serverless".

How can I fix this?  What alternative word can I use in place of "serverless" 
to mean "without a server"?

Note that "in-process" and "embedded" are not adequate substitutes for 
"serverless".  An RDBMS might be in-process or embedded but still be running a 
server in a separate thread. In fact, that is how most embedded RDBMSes other 
than SQLite work, if I am not much mistaken.

When I say "serverless" I mean that the application invokes a function, that 
function performs some task on behalf of the application, then the function 
returns, *and that is all*.  No threads are left over, running in the 
background to do housekeeping.  The function does send messages to some other 
thread or process.  The function does not have an event loop.  The function 
does not have its own stack. The function (with its subfunctions) does all the 
work itself, using the callers stack, then returns control to the caller.

So what do I call this, if I can no longer use the word "serverless"
without confusing people?

"no-server"?
"sans-server"?
"stackless"?
"non-client/server"?


--
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] Row length in SQLITE

[Deon Brewis]

No I mean e.g.

row 1 = 500 bytes, 
row 2 = 600 bytes
row 3 = 80 bytes
row 4 = 300 bytes

etc.

Like the info that DBSTAT gives, but per row, not per page. This doesn't need 
to be performant - it's for usage analysis during development time.

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Wednesday, January 22, 2020 5:24 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Row length in SQLITE

On 22 Jan 2020, at 11:44pm, Deon Brewis  wrote:

> Is there any way to get the length of rows in a table / index in sqlite?

Do you mean the count of rows in a table / index ?

   SELECT count(*) FROM MyTable

There's no easy fast way to do this because SQLite doesn't keep that number 
handy anywhere.  It stores the entries in a tree and it would have to manually 
count the leaves of the tree.



Or do you mean the count of columns in a table / index ?

SELECT * FROM MyTable LIMIT 1

and count the number of columns returned.  Or in C

sqlite3_column_count()
___
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] Row length in SQLITE

[Deon Brewis]

Is there any way to get the length of rows in a table / index in sqlite?

DBSTAT/sqlite3_analyzer --stats almost gives me the information I want, but 
it's an aggregate sum & max per page - I need the data per row (cell).

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_interrupt vs. SQLITE_CONFIG_SINGLETHREAD

[Deon Brewis]

Intended use is to cancel long running SQLITE background operations on other 
threads if the user needs UI responsiveness on the main thread. Even though the 
operations are background, we need the CPU & disk back for the user. Once the 
user becomes idle again, the background operations restart.

My concern is that SQLITE_CONFIG_SINGLETHREAD implies no mutexes. I don't know 
if it is possible to correctly implement sqlite3_interrupt() without a mutex on 
all platforms that SQLITE runs on.

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Richard Hipp
Sent: Wednesday, August 14, 2019 6:19 AM
To: SQLite mailing list 
Subject: Re: [sqlite] sqlite3_interrupt vs. SQLITE_CONFIG_SINGLETHREAD

On 8/14/19, Deon Brewis  wrote:
> sqlite3_interrupt is documented as:
> “It is safe to call this routine from a thread different from the 
> thread that is currently running the database operation”
>
> SQLITE_CONFIG_SINGLETHREAD is documented as:
> “puts SQLite into a mode where it can only be used by a single thread”
>
> Which one wins

The sqlite3_interrupt() interface is intending to stop a long-running query, 
usually by a single handler in response to the user pressing Ctrl-C or similar. 
 This works regardless of compile-time options.

What is your intended use of sqlite3_interrupt() that compile-time options 
matter?

--
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

[sqlite] sqlite3_interrupt vs. SQLITE_CONFIG_SINGLETHREAD

[Deon Brewis]

sqlite3_interrupt is documented as:
“It is safe to call this routine from a thread different from the thread that 
is currently running the database operation”

SQLITE_CONFIG_SINGLETHREAD is documented as:
“puts SQLite into a mode where it can only be used by a single thread”

Which one wins ? i.e. Can we call sqlite3_interrupt from a secondary thread in 
a SQLITE_CONFIG_SINGLETHREAD environment? (And can we have a doc clarification 
on this).


Secondly, regardless of the above answer - from a technical perspective, 
sqlite3_interrupt is implemented as:
volatile int isInterrupted; /* True if sqlite3_interrupt has been called */
…
db->u1.isInterrupted = 1;

However, even though it’s a volatile int, it doesn’t have any kind of memory 
fence around it. So reads and writes to it can be re-ordered out of existence 
or into undefined behavior. This is probably undesired.

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is rootPage a reasonable estimate for index age?

[Deon Brewis]

Yeah ok that makes more sense. 

Sorry, that was a bit obvious - late night.

Thanks!
- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Richard Hipp
Sent: Wednesday, August 7, 2019 11:32 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Is rootPage a reasonable estimate for index age?

On 8/7/19, Deon Brewis  wrote:
> If I have 2 indexes 'A' and 'B', can I use rootPage to determine which 
> one of them is older?
>
> This seems to work for me, or is it just because my database has been 
> vacuumed recently and if a database has a lot of empty pages it will 
> add new indexes to smaller pages than older indexes?

It probably only works because your DB is vacuumed.

Seems to me that the rowid of the sqlite_master table entry for your index 
would be a more reliable indicator.  Larger rowids are newer.

--
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

[sqlite] Is rootPage a reasonable estimate for index age?

[Deon Brewis]

If I have 2 indexes 'A' and 'B', can I use rootPage to determine which one of 
them is older?

This is for a dynamic index sweep to decide which one to drop.

It doesn't matter if it's sometimes wrong, if the index is needed again it will 
be re-created. I just don't want to get into a situation where we keep dropping 
and recreating the newest index rather than the oldest one (we use a set of 16 
dynamic indexes).


This seems to work for me, or is it just because my database has been vacuumed 
recently and if a database has a lot of empty pages it will add new indexes to 
smaller pages than older indexes?

- Deon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Does SQLITE ever optimize index creation based on another index?

[Deon Brewis]

Given the SQL below, FooX is a covered index for x on Foo.

I want to create FooXB as a second index on x in Foo. Since 'x' is covered on 
FooX it should be cheaper to build FooXB from index FooX, than from table Foo. 
However, as far as I can tell from the from the opcodes of the index creation 
it doesn't do this (OpenRead uses rootpage=2 instead of 3). Is my understanding 
correct?

And if my understanding is correct, is there any scenarios in which I can 
coerce SQLITE to build a new index based on data in an existing index?


drop table Foo;
create table Foo(x text, y text, z text);

insert into Foo(x) values("elephant");
insert into Foo(x) values("cat");
insert into Foo(x) values("giraffe");
insert into Foo(x) values("dog");
insert into Foo(x) values("zebra");
insert into Foo(x) values("lion");
insert into Foo(x) values("panther");

create index FooX on Foo(x);
create index FooXB on Foo(substr(x,2,1)) where substr(x,2,1) > 'e';




select * from sqlite_master;
RecNo type  name tbl_name rootpage sql
- -    
1 table Foo  Foo 2 CREATE TABLE Foo(x text, y text, z text)
2 index FooX Foo 3 CREATE INDEX FooX on Foo(x)

explain create index FooXB on Foo(substr(x,2,1)) where substr(x,2,1) > 'e';
RecNo addr opcode   p1 p2 p3 p4 
  p5 comment
-   -- -- -- 
 -- 
---
1 0Init 0  37 0 
  00 (null)
2 1Noop 0  36 0 
  00 (null)
3 2CreateBtree  0  1  2 
  00 (null)
4 3OpenWrite0  1  0  5  
  00 (null)
5 4NewRowid 0  2  0 
  00 (null)
6 5String8  0  3  0  index  
  00 (null)
7 6String8  0  4  0  FooXB  
  00 (null)
8 7String8  0  5  0  Foo
  00 (null)
9 8Copy 1  6  0 
  00 (null)
   10 9String8  0  7  0  CREATE INDEX FooXB on Foo(substr(x,2,1)) where 
substr(x,2,1) > 'e' (more...) 00 (null)
   11 10   MakeRecord   3  5  8  BBBDB  
  00 (null)
   12 11   Insert   0  8  2 
  18 (null)
   13 12   SorterOpen   3  0  1  k(2,,) 
  00 (null)
   14 13   OpenRead 1  2  0  3  
  00 (null)
   15 14   Rewind   1  24 0 
  00 (null)
   16 15   Column   1  0  11
  00 (null)
   17 16   PureFunc06  11 10 substr(3)  
  03 (null)
   18 17   Le   14 23 10
  51 (null)
   19 18   Column   1  0  17
  00 (null)
   20 19   PureFunc06  17 15 substr(3)  
  03 (null)
   21 20   Rowid1  16 0 
  00 (null)
   22 21   MakeRecord   15 2  9 
  00 (null)
   23 22   SorterInsert 3  9  0 
  00 (null)
   24 23   Next 1  15 0 
  00 (null)
   25 24   OpenWrite2  1  0  k(2,,) 
  11 (null)
   26 25   SorterSort   3  30 0 
  00 (null)
   27 26   SorterData   3  9  2 
  00 (null)
   28 27   SeekEnd  2  0  0 
  00 (null)
   29 28   IdxInsert2  9  0   

Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY COMPILE

[Deon Brewis]

Right-click on the sqlite3.c file in your Visual Studio project, click on 
properties, then under C/C++ go to Precompiled Headers, and change the setting 
for "Precompiled Header" to "Not using precompiled header".

Make sure to do this for "All Configurations" and "All Platforms" (at the top 
of that dialog).

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Jens Alfke
Sent: Thursday, December 20, 2018 5:59 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY COMPILE



> On Dec 20, 2018, at 5:34 PM, zydeholic  wrote:
> 
> I pulled the two files into a C++ project, deleted the .cpp file, and 
> compiled.
> It came back and said my PCH compiled header was from a previous project, or 
> something like that.  I tried to save the message, but it got lost in the 
> hubbub.
> So, I deleted the PCH.CPP file that was in the project.  I just rebuilt it, 
> and got this message:
> 
> ErrorC1010unexpected end of file while looking for precompiled 
> header. Did you forget to add '#include "pch.h"' to your source?sqlite_c  
>   c:\sqlite\source code\sqlite-amalgamation-326\sqlite3.c220536

You’re having trouble with Visual Studio, not with SQLite. A forum for VS users 
would be an appropriate place to ask for help.

—Jens
___
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] SQLITE gives incorrect results for 'NOT IN' query if partial index exists

[Deon Brewis]

Thanks a lot for the quick fix!

I'm curious how that test that you added works?  i.e. What causes the test to 
fail if the results are wrong? (Sorry, I know almost nothing about the SQLITE 
test framework).
https://www.sqlite.org/src/info/15bc915dd7ae25bf

- Deon

-Original Message-
From: drhsql...@gmail.com  On Behalf Of Richard Hipp
Sent: Saturday, December 8, 2018 5:11 AM
To: SQLite mailing list 
Cc: Deon Brewis ; o...@integral.be
Subject: Re: [sqlite] SQLITE gives incorrect results for 'NOT IN' query if 
partial index exists

Thanks for the bug report and for the repro script.

This problem appears to have been in the code ever since partial indexes were 
first introduced in version 3.8.0 (2013-08-26).  I have some other things I 
have to deal with today.  I will get to this as soon as I can.  There is a 
ticket at
https://www.sqlite.org/src/tktview/1d958d90596593a77420e590a6ab71756484f576


On 12/8/18, Deon Brewis  wrote:
> The 'NOT IN' query at the bottom should not return any results. It 
> returns '2'.
>
> Repro:
> =
> create table foo(y, z);
> insert into foo values(1,1);
> insert into foo values(2,2);
> CREATE INDEX fooZ on Foo(y) where z=1;
>
> create table bar(x);
> insert into bar values(1);
> insert into bar values(2);
>
> select * from bar WHERE x NOT IN (SELECT y from foo); -- this will 
> wrongly return 2.
>
> Issue:
> =
> In the query plan, sqlite chooses to use the FooZ index:
>
> explain query plan select * from bar WHERE x NOT IN (SELECT y from 
> foo); QUERY PLAN
> |--SCAN TABLE bar
> `--USING INDEX fooZ FOR IN-OPERATOR
>
>
> FooZ is not a valid index for this subquery, it is incomplete due to 
> the partial index expression, with the result is doing a NOT IN over 
> it returns incorrect results.
>
> - Deon
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
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] SQLITE gives incorrect results for 'NOT IN' query if partial index exists

[Deon Brewis]

The 'NOT IN' query at the bottom should not return any results. It returns '2'.

Repro:
=
create table foo(y, z);
insert into foo values(1,1);
insert into foo values(2,2);
CREATE INDEX fooZ on Foo(y) where z=1;

create table bar(x);
insert into bar values(1);
insert into bar values(2);

select * from bar WHERE x NOT IN (SELECT y from foo); -- this will wrongly 
return 2.

Issue:
=
In the query plan, sqlite chooses to use the FooZ index:

explain query plan select * from bar WHERE x NOT IN (SELECT y from foo);
QUERY PLAN
|--SCAN TABLE bar
`--USING INDEX fooZ FOR IN-OPERATOR


FooZ is not a valid index for this subquery, it is incomplete due to the 
partial index expression, with the result is doing a NOT IN over it returns 
incorrect results.

- Deon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-column covering index limit clarification

[Deon Brewis]

Yes a non partial index beyond column 64 works as I would expect.

- Deon

> On Oct 18, 2018, at 12:34 PM, Shawn Wagner  wrote:
> 
> Does a normal non-partial index make a difference in the query plan?
> 
>> On Thu, Oct 18, 2018, 12:30 PM Deon Brewis  wrote:
>> 
>> Hi,
>> 
>> I seem to have run into a limit where SQLITE doesn't use an index
>> correctly if an indexed column is over the 64th column in the table. It's a
>> partial index like:
>> 
>> CREATE INDEX idx ON
>>  table(A, B DESC, C, D)
>>  WHERE A > 0
>> 
>> Where A and B are columns 70 and 72 on 'table'.
>> 
>> I know about the 64-column limitation for covering indexes:
>> 
>> http://sqlite.1065341.n5.nabble.com/Max-of-63-columns-for-a-covering-index-to-work-td68945.html
>> 
>> However, this isn't a covering index, it's a partial index. But it seems
>> to run into the same limit. Even if I forced in the index into a query it
>> still does a "USE TEMP B-TREE" at the end to satisfy a simple "ORDER BY A,
>> B DESC" query. After I re-ordered the table, it magically started working.
>> 
>> Is there any better documentation anywhere (other than the archive) of all
>> of the cases to which the 64-column limit applies?
>> 
>> - Deon
>> 
>> ___
>> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 64-column covering index limit clarification

[Deon Brewis]

Hi,

I seem to have run into a limit where SQLITE doesn't use an index correctly if 
an indexed column is over the 64th column in the table. It's a partial index 
like:

CREATE INDEX idx ON
  table(A, B DESC, C, D)
  WHERE A > 0

Where A and B are columns 70 and 72 on 'table'.

I know about the 64-column limitation for covering indexes:
http://sqlite.1065341.n5.nabble.com/Max-of-63-columns-for-a-covering-index-to-work-td68945.html

However, this isn't a covering index, it's a partial index. But it seems to run 
into the same limit. Even if I forced in the index into a query it still does a 
"USE TEMP B-TREE" at the end to satisfy a simple "ORDER BY A, B DESC" query. 
After I re-ordered the table, it magically started working.

Is there any better documentation anywhere (other than the archive) of all of 
the cases to which the 64-column limit applies?

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is SQLITE_DETERMINISTIC ignored on an aggregate?

[Deon Brewis]

Bug I found in my code. Wanted to know if I need to do a recall or just fix it 
in the next release.

Thanks!
- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Richard Hipp
Sent: Thursday, September 27, 2018 10:25 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Is SQLITE_DETERMINISTIC ignored on an aggregate?

On 9/27/18, Deon Brewis  wrote:
> Is there anything that SQLITE_DETERMINISTIC would ever be used for in 
> an aggregate? (Function with xStep/xFinal as opposed to just xFunc).
>
> I assume it's ignored, but just checking.

I believe you are correct.  But why are you asking?

--
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

[sqlite] Calling sqlite3_create_module from a DLL

[Deon Brewis]

I have a DLL that makes a series of sqlite3_create_function_v2 calls. It all 
works fine and the DLL is usable.

I've tried adding a sqlite3_create_module into the same DLL, but I get an 
assert in:

sqlite3_mutex_try
over here:
assert( sqlite3GlobalConfig.mutex.xMutexTry );

xMutexTry (really all the .mutex callbacks) are null.

I'm trying to call create_module during my sqlite3_extension_init export, 
directly after my call to SQLITE_EXTENSION_INIT2(pApi);


The same sqlite3_create_module statement works fine if I add it to my .exe 
(where I'm the one initializing SQLITE) instead of my .dll. Is there anything I 
need to do extra during initialization to get sqlite3_create_module to work?

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is SQLITE_DETERMINISTIC ignored on an aggregate?

[Deon Brewis]

Is there anything that SQLITE_DETERMINISTIC would ever be used for in an 
aggregate? (Function with xStep/xFinal as opposed to just xFunc).

I assume it's ignored, but just checking.

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] integrity_check failure on 3.26 with expression indexes (wrong # of entries)

[Deon Brewis]

Sorry, false alarm. 

That corruption far predates the 3.26 changes (occurred under 3.24).

The tool I was using to test didn't show the errors on the old version of the 
database but a newer tool shows them.

Thanks!
- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Dan Kennedy
Sent: Tuesday, September 25, 2018 11:54 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] integrity_check failure on 3.26 with expression indexes 
(wrong # of entries)

On 09/26/2018 01:14 PM, Deon Brewis wrote:
> I am receiving an integrity check failure on 3.26 on the 9/19/2018 drop. It's 
> not everywhere and it may be coincidental, but it may also be related to the 
> new change where expression indexes aren't updated if their columns aren't 
> modified.
>
> I'm getting these integrity check failures:
>
> wrong # of entries in index A
> wrong # of entries in index B
> wrong # of entries in index C
>
> where all 3 of those indexes use expressions. I have a before database, after 
> database, and an error log from SQLITE.
>
> Can I send this to someone to investigate?

Can you send them to me if you don't want to upload them? "dan AT sqlite.org" 
if you prefer.

Thanks,
Dan.



>
> - Deon
>
>
> ___
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] integrity_check failure on 3.26 with expression indexes (wrong # of entries)

[Deon Brewis]

I am receiving an integrity check failure on 3.26 on the 9/19/2018 drop. It's 
not everywhere and it may be coincidental, but it may also be related to the 
new change where expression indexes aren't updated if their columns aren't 
modified.

I'm getting these integrity check failures:

wrong # of entries in index A
wrong # of entries in index B
wrong # of entries in index C

where all 3 of those indexes use expressions. I have a before database, after 
database, and an error log from SQLITE.

Can I send this to someone to investigate?

- Deon


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another Partial Index optimization opportunity (INSERT)

[Deon Brewis]

You are of course very right. (The UPDATE loaded the existing column value from 
the row before it did the 'Le', which is why it was performing an index 
delete+insert later on. INSERT doesn't do that.).

I was expecting it to be kicked out of the query plan completely with an unused 
column, but as is I'm sure it will be fine with just the runtime checks.

Thanks!
- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
David Raymond
Sent: Thursday, September 20, 2018 9:07 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Another Partial Index optimization opportunity (INSERT)

Could you show us the plans for the version you're looking at? When I'm looking 
at it with 3.25.0 the index inserts are guarded by conditionals such that that 
index is opened, but never actually accessed when run with the values in your 
examples.

They query plan is a *generic one* that gets used for both of your inserts, so 
may look a little weird for a single row. But it's got to consider the default 
for non-provided fields, etc.

Starting at line 6 it sets register 4 to null, then on line 7 checks z against 
42 to see if it needs to make a record to put into the index, skipping the 
index record creation if it doesn't qualify. Line 11 says hey, if there isn't a 
record to insert, then skip past the index insert and go on to dealing with the 
main table.



sqlite> explain insert into foo(x) values (5);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 16000  Start at 16
1 OpenWrite  0 2 0 2  00  root=2 iDb=0; Foo
2 OpenWrite  1 3 0 k(2,,) 00  root=3 iDb=0; 
FooPartialZ
3 NewRowid   0 1 000  r[1]=rowid
4 Integer5 2 000  r[2]=5
5 Noop   0 0 000  uniqueness check for 
FooPartialZ
6 Null   0 4 000  r[4]=NULL
7 Le 8 113 (BINARY)   51  if r[3]<=r[8] goto 11
8 SCopy  3 5 000  r[5]=r[3]; z
9 IntCopy1 6 000  r[6]=r[1]; rowid
10MakeRecord 5 2 400  r[4]=mkrec(r[5..6]); 
for FooPartialZ
11IsNull 4 13000  if r[4]==NULL goto 13
12IdxInsert  1 4 5 2  10  key=r[4]
13MakeRecord 2 2 700  r[7]=mkrec(r[2..3])
14Insert 0 7 1 Foo39  intkey=r[1] data=r[7]
15Halt   0 0 000
16Transaction0 1 2 0  01  usesStmtJournal=0
17Null   0 3 000  r[3]=NULL
18Integer428 000  r[8]=42
19Goto   0 1 000


sqlite> explain insert into foo(z) values (10);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 16000  Start at 16
1 OpenWrite  0 2 0 2  00  root=2 iDb=0; Foo
2 OpenWrite  1 3 0 k(2,,) 00  root=3 iDb=0; 
FooPartialZ
3 NewRowid   0 1 000  r[1]=rowid
4 Integer103 000  r[3]=10
5 Noop   0 0 000  uniqueness check for 
FooPartialZ
6 Null   0 4 000  r[4]=NULL
7 Le 8 113 (BINARY)   51  if r[3]<=r[8] goto 11
8 SCopy  3 5 000  r[5]=r[3]; z
9 IntCopy1 6 000  r[6]=r[1]; rowid
10MakeRecord 5 2 400  r[4]=mkrec(r[5..6]); 
for FooPartialZ
11IsNull 4 13000  if r[4]==NULL goto 13
12IdxInsert  1 4 5 2  10  key=r[4]
13MakeRecord 2 2 700  r[7]=mkrec(r[2..3])
14Insert 0 7 1 Foo39  intkey=r[1] data=r[7]
15Halt   0 0 000
16Transaction0 1 2 0  01  usesStmtJournal=0
17Null   0 2 000  r[2]=NULL
18Integer428 000  r[8]=42
19Goto   0 1 000

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Deon Brewis
Sent: Thursday, September 20, 2018 11:30 AM
To: SQLite mailing list
Subject: [sqlite] Another Partial Index optimization opportunity (INSERT)

Hi, I previou

[sqlite] Another Partial Index optimization opportunity (INSERT)

[Deon Brewis]

Hi, I previously reported that an UPDATE of a table containing an Expression 
and/or Partial Index will unnecessarily touch the expression and/or partial 
index. I see both are now fixed in the 3.26.0 2018-09-19 codebase. Thank you so 
much - it works great!

However, while testing it, I also noticed there is an opportunity for this 
INSERT optimization:

CREATE TABLE Foo(x, z);
CREATE INDEX FooPartialZ on Foo(z) WHERE z > 42;

explain INSERT INTO foo(x) VALUES(5);
explain INSERT INTO foo(z) VALUES(10);

Neither of these statements should affect FooPartialZ, the first is an unused 
column, the second is out of range. But currently they both will access 
FooPartialZ.

These are probably trickier to implement though (especially the second one), 
and not nearly as important to us as the UPDATE optimizations, but it may be 
useful for someone out there.

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE touches unchanged expression indexes on update

[Deon Brewis]

This seems to work perfectly for expression indexes.  Thanks, it makes a HUGE 
difference for us!

Would you perhaps be able to make a similar fix for partial indexes? i.e. this 
scenario:

CREATE TABLE Foo(x, y, z);
CREATE INDEX FooX on Foo(x);
CREATE INDEX FooZ on Foo(z);
CREATE INDEX FooPartialZ on Foo(z) WHERE z > 42; 

explain UPDATE foo SET x=1 WHERE rowid=1;


addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 36000  Start at 36
1 Null   0 7 800  r[7..8]=NULL
2 OpenWrite  0 2 0 3  00  root=2 iDb=0; Foo
3 Integer1 12000  r[12]=1
4 SeekRowid  0 6 12   00  intkey=r[12]
5 Rowid  0 8 000  r[8]=rowid
6 OpenWrite  1 5 0 k(2,,) 00  root=5 iDb=0; 
FooPartialZ   <<<===
7 OpenWrite  3 3 0 k(2,,) 00  root=3 iDb=0; FooX
8 IsNull 8 35000  if r[8]==NULL goto 35
9 Integer1 9 000  r[9]=1
10Column 0 1 10   00  r[10]=Foo.y
11Column 0 2 11   00  r[11]=Foo.z
12Noop   0 0 000  uniqueness check for 
FooPartialZ  <<<===
13Null   0 1 000  r[1]=NULL
14Le 141811(BINARY)   51  if r[11]<=r[14] goto 
18
15SCopy  112 000  r[2]=r[11]; z
16IntCopy8 3 000  r[3]=r[8]; rowid
17MakeRecord 2 2 100  r[1]=mkrec(r[2..3]); 
for FooPartialZ  <<<===
18Noop   0 0 000  uniqueness check for 
FooX
19SCopy  9 5 000  r[5]=r[9]; x
20IntCopy8 6 000  r[6]=r[8]; rowid
21MakeRecord 5 2 400  r[4]=mkrec(r[5..6]); 
for FooX
22Column 0 2 13   00  r[13]=Foo.z
23Le 142713(BINARY)   51  if r[13]<=r[14] goto 
27
24Column 0 2 15   00  r[15]=Foo.z
25Rowid  0 16000  r[16]=rowid
26IdxDelete  1 15200  key=r[15..16]
27Column 0 0 15   00  r[15]=Foo.x
28Rowid  0 16000  r[16]=rowid
29IdxDelete  3 15200  key=r[15..16]
30IsNull 1 32000  if r[1]==NULL goto 32
31IdxInsert  1 1 2 2  00  key=r[1]  
<<<===
32IdxInsert  3 4 5 2  00  key=r[4]
33MakeRecord 9 3 13   00  r[13]=mkrec(r[9..11])
34Insert 0 138 Foo05  intkey=r[8] data=r[13]
35Halt   0 0 000
36Transaction0 1 4 0  01  usesStmtJournal=0
37Integer4214000  r[14]=42
38Goto   0 1 000

-Original Message-
From: drhsql...@gmail.com  On Behalf Of Richard Hipp
Sent: Saturday, September 15, 2018 2:46 PM
To: SQLite mailing list 
Cc: de...@outlook.com
Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on update

On 9/11/18, Deon Brewis  wrote:
> It seems like there is an opportunity for improvement on updates if an 
> index contains expressions.

This enhancement did not make the cutoff for 3.25.0.  But as 3.25.0 is now out, 
I have started the next release cycle and you can find this enhancement on the 
latest trunk version of SQLite.  You'll need to grab a tarball (or clone the 
Fossil repository) and compile it yourself.  If you can, please do this and try 
out the code and let me know whether or not it works, that will be appreciated.
--
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] SQLITE touches unchanged expression indexes on update

[Deon Brewis]

gt;From: sqlite-users [mailto:sqlite-users- 
>>boun...@mailinglists.sqlite.org] On Behalf Of David Raymond
>>Sent: Wednesday, 12 September, 2018 07:54
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on 
>>update
>>
>>Just showing with "explain comments" enabled.
>>
>>
>>sqlite> explain update foo set x = 1 where rowid = 1;
>>addr  opcode p1p2p3p4 p5  comment
>>  -        -  --  --
>-
>>--
>>0 Init   0 33000  Start at
>33
>>1 Null   0 7 800
>>r[7..8]=NULL
>>2 OpenWrite  0 2 0 3  00  root=2
>>iDb=0; Foo
>>3 Explain3 0 0 SEARCH TABLE foo USING
>INTEGER
>>PRIMARY KEY (rowid=?)  00
>>4 Integer1 12000  r[12]=1
>>5 SeekRowid  0 7 12   00
>>intkey=r[12]; pk
>>6 Rowid  0 8 000  r[8]=rowid
>>7 OpenWrite  1 5 0 k(2,,) 00  root=5
>>iDb=0; FooLenZ
>>8 OpenWrite  3 3 0 k(2,,) 00  root=3
>>iDb=0; FooX
>>9 IsNull 8 32000  if
>>r[8]==NULL goto 32
>>10Integer1 9 000  r[9]=1
>>11Column 0 1 10   00
>r[10]=Foo.y
>>12Column 0 2 11   00
>r[11]=Foo.z
>>13Noop   0 0 000  uniqueness
>>check for FooLenZ
>>14Copy   1113000
>r[13]=r[11]
>>15PureFunc0  0 132 length(1)  01  FooLenZ
>>column 0
>>16IntCopy8 3 000  r[3]=r[8];
>>rowid
>>17MakeRecord 2 2 100
>>r[1]=mkrec(r[2..3]); for FooLenZ
>>18Noop   0 0 000  uniqueness
>>check for FooX
>>19SCopy  9 5 000  r[5]=r[9];
>>x
>>20IntCopy8 6 000  r[6]=r[8];
>>rowid
>>21MakeRecord 5 2 400
>>r[4]=mkrec(r[5..6]); for FooX
>>22Copy   1113000
>r[13]=r[11]
>>23PureFunc0  0 1314length(1)  01
>>24Rowid  0 15000
>r[15]=rowid
>>25IdxDelete  1 14200
>>key=r[14..15]
>>26Column 0 0 14   00
>r[14]=Foo.x
>>27IdxDelete  3 14200
>>key=r[14..15]
>>28IdxInsert  1 1 2 2  00  key=r[1]
>>29IdxInsert  3 4 5 2  00  key=r[4]
>>30MakeRecord 9 3 13   00
>>r[13]=mkrec(r[9..11])
>>31Insert 0 138 Foo05
>intkey=r[8]
>>data=r[13]
>>32Halt   0 0 000
>>33Transaction0 1 4 0  01
>>usesStmtJournal=0
>>34Goto   0 1 000
>>
>>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users- 
>>boun...@mailinglists.sqlite.org] On Behalf Of Deon Brewis
>>Sent: Tuesday, September 11, 2018 8:20 PM
>>To: SQLite mailing list
>>Subject: [sqlite] SQLITE touches unchanged expression indexes on 
>>update
>>
>>It seems like there is an opportunity for improvement on updates if an 
>>index contains expressions.
>>
>>In the following example:
>>
>>CREATE TABLE Foo(x, y, z);
>>CREATE INDEX FooX on Foo(x);
>>CREATE INDEX FooZ on Foo(z);
>>CREATE INDEX FooLenZ on Foo(length(z));
>>
>>explain UPDATE foo SET x=1 WHERE rowid=1;
>>
>>I see the plan below. Notice it's updating FooLenZ even though the 
>>'UPDATE foo SET x=1' statement doesn't touch the Z column at all. It 
>>doesn't try to update FooZ, just FooLenZ, but both should be 
>>untouched.
>>
>>RecNo addr opcode  p1 p2 p3 p4p5 comment
>>-  --- -- -- -- - -- ---
>>1 0Init0  30 000 (null)
>>2 1Null0  7  800 (null)
>>3 2OpenWrite   0  2  0  3 00 (null)  // opening
>'Foo'
>>(expected)
>>  

Re: [sqlite] SQLITE touches unchanged expression indexes on update

[Deon Brewis]

Thanks a lot!

I've just noticed it also has the same behavior for partial indexes.

Here is an explain with a partial index (and using a later build this time for 
my output...).

CREATE TABLE Foo(x, y, z);
CREATE INDEX FooX on Foo(x);
CREATE INDEX FooZ on Foo(z);
CREATE INDEX FooLenZ on Foo(length(z));
CREATE INDEX FooPartialZ on Foo(z) WHERE z > 42;
explain UPDATE foo SET x=1 WHERE rowid=1;

sqlite> explain UPDATE foo SET x=1 WHERE rowid=1;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 45000  Start at 45
1 Null   0 1011   00  r[10..11]=NULL
2 OpenWrite  0 2 0 3  00  root=2 iDb=0; Foo
3 Integer1 15000  r[15]=1
4 SeekRowid  0 6 15   00  intkey=r[15]; pk
5 Rowid  0 11000  r[11]=rowid
6 OpenWrite  1 6 0 k(2,,) 00  root=6 iDb=0; 
FooPartialZ
7 OpenWrite  2 5 0 k(2,,) 00  root=5 iDb=0; FooLenZ
8 OpenWrite  4 3 0 k(2,,) 00  root=3 iDb=0; FooX
9 IsNull 1144000  if r[11]==NULL goto 44
10Integer1 12000  r[12]=1
11Column 0 1 13   00  r[13]=Foo.y
12Column 0 2 14   00  r[14]=Foo.z
13Noop   0 0 000  uniqueness check for 
FooPartialZ
14Null   0 1 000  r[1]=NULL
15Le 171914(BINARY)   51  if r[14]<=r[17] goto 
19
16SCopy  142 000  r[2]=r[14]; z
17IntCopy113 000  r[3]=r[11]; rowid
18MakeRecord 2 2 100  r[1]=mkrec(r[2..3]); 
for FooPartialZ
19Noop   0 0 000  uniqueness check for 
FooLenZ
20Copy   1416000  r[16]=r[14]
21PureFunc0  0 165 length(1)  01  FooLenZ column 0
22IntCopy116 000  r[6]=r[11]; rowid
23MakeRecord 5 2 400  r[4]=mkrec(r[5..6]); 
for FooLenZ
24Noop   0 0 000  uniqueness check for 
FooX
25SCopy  128 000  r[8]=r[12]; x
26IntCopy119 000  r[9]=r[11]; rowid
27MakeRecord 8 2 700  r[7]=mkrec(r[8..9]); 
for FooX
28Le 173214(BINARY)   51  if r[14]<=r[17] goto 
32
29Column 0 2 18   00  r[18]=Foo.z
30Rowid  0 19000  r[19]=rowid
31IdxDelete  1 18200  key=r[18..19]
32Copy   1416000  r[16]=r[14]
33PureFunc0  0 1618length(1)  01
34Rowid  0 19000  r[19]=rowid
35IdxDelete  2 18200  key=r[18..19]
36Column 0 0 18   00  r[18]=Foo.x
37IdxDelete  4 18200  key=r[18..19]
38IsNull 1 40000  if r[1]==NULL goto 40
39IdxInsert  1 1 2 2  00  key=r[1]
40IdxInsert  2 4 5 2  00  key=r[4]
41IdxInsert  4 7 8 2  00  key=r[7]
42MakeRecord 123 16   00  r[16]=mkrec(r[12..14])
43Insert 0 1611Foo05  intkey=r[11] 
data=r[16]
44Halt   0 0 000
45Transaction0 1 5 0  01  usesStmtJournal=0
46Integer4217000  r[17]=42
47Goto   0 1 000

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Richard Hipp
Sent: Wednesday, September 12, 2018 7:05 AM
To: SQLite mailing list 
Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on update

On 9/11/18, Deon Brewis  wrote:
> It seems like there is an opportunity for improvement on updates if an 
> index contains expressions.

Thanks for bringing this to our attention.

We're on lockdown for the 3.25.0 release (bug fixes only) but I did start 
looking at this to see how feasible it would be.  I found this
comment:

https://www.sqlite.org/src/artifact/345ce35eb133?ln=306

So apparently this  has come up before :-)  That comment was inserted on 
2015-09-04.


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing

[sqlite] SQLITE touches unchanged expression indexes on update

[Deon Brewis]

It seems like there is an opportunity for improvement on updates if an index 
contains expressions.

In the following example:

CREATE TABLE Foo(x, y, z);
CREATE INDEX FooX on Foo(x);
CREATE INDEX FooZ on Foo(z);
CREATE INDEX FooLenZ on Foo(length(z));

explain UPDATE foo SET x=1 WHERE rowid=1;

I see the plan below. Notice it's updating FooLenZ even though the 'UPDATE foo 
SET x=1' statement doesn't touch the Z column at all. It doesn't try to update 
FooZ, just FooLenZ, but both should be untouched.

RecNo addr opcode  p1 p2 p3 p4p5 comment
-  --- -- -- -- - -- ---
1 0Init0  30 000 (null)
2 1Null0  7  800 (null)
3 2OpenWrite   0  2  0  3 00 (null)  // opening 'Foo' (expected)
4 3Integer 1  12 000 (null)
5 4SeekRowid   0  6  12   00 (null)
6 5Rowid   0  8  000 (null)
7 6OpenWrite   1  5  0  k(2,,)00 (null)  // opening 'FooLenZ' (NOT 
expected)
8 7OpenWrite   3  3  0  k(2,,)00 (null)  // opening 'FooX'   
(expected)
9 8IsNull  8  29 000 (null)
   10 9Integer 1  9  000 (null)
   11 10   Column  0  1  10   00 (null)
   12 11   Column  0  2  11   00 (null)
   13 12   Copy11 13 000 (null)
   14 13   Function0   0  13 2  length(1) 01 (null)
   15 14   IntCopy 8  3  000 (null)
   16 15   MakeRecord  2  2  100 (null)
   17 16   SCopy   9  5  000 (null)
   18 17   IntCopy 8  6  000 (null)
   19 18   MakeRecord  5  2  400 (null)
   20 19   Copy11 13 000 (null)
   21 20   Function0   0  13 14 length(1) 01 (null)
   22 21   Rowid   0  15 000 (null)
   23 22   IdxDelete   1  14 200 (null)
   24 23   Column  0  0  14   00 (null)
   25 24   IdxDelete   3  14 200 (null)
   26 25   IdxInsert   1  1  2  2 00 (null)  // updating 'FooLenZ' (NOT 
expected)
   27 26   IdxInsert   3  4  5  2 00 (null)  // updating 'FooX' 
(expected)
   28 27   MakeRecord  9  3  13   00 (null)
   29 28   Insert  0  13 8  Foo   05 (null)
   30 29   Halt0  0  000 (null)
   31 30   Transaction 0  1  42 0 01 (null)
   32 31   Goto0  1  000 (null)

sqlite_master:
RecNo type  nametbl_name rootpage sql
- - ---   --
1 table Foo Foo 2 CREATE TABLE Foo(x, y, z)
2 index FooXFoo 3 CREATE INDEX FooX on Foo(x)
3 index FooZFoo 4 CREATE INDEX FooZ on Foo(z)
4 index FooLenZ Foo 5 CREATE INDEX FooLenZ on Foo(length(z))



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite query planner doesn't seem to know when a partial index is COVERING

[Deon Brewis]

Looks like a missed optimization opportunity here. Well, more than that - there 
doesn't appear to be a way to get SQLITE to automatically use a partial index 
if a similar non-partial index exists.

E.g.
create table Foo(a,b,c,d,e);
create index Foo_inx on Foo(a,b,c);
create index Foo_partial_inx on Foo(b,c) where a = 1;

insert into Foo(a,b,c,d,e) values(1,2,3,4,5);
insert into Foo(a,b,c,d,e) values(1,2,2,4,6);
insert into Foo(a,b,c,d,e) values(1,2,5,1,1);
insert into Foo(a,b,c,d,e) values(2,1,6,1,1);
insert into Foo(a,b,c,d,e) values(6,4,6,1,1);

analyze Foo;
explain query plan select b from Foo where a=1 and b=2 order by c;
> SEARCH TABLE Foo USING COVERING INDEX Foo_inx (a=? AND b=?)


In this case SQLite picks Foo_inx over Foo_partial_inx. I suspect it's because 
it doesn't know Foo_partial_inx is effectively a covering index because if I 
force the index by hand, it doesn't list it as a COVERING index:

explain query plan select b from Foo indexed by Foo_partial_inx where a=1 and 
b=2 order by c;
> SEARCH TABLE Foo USING INDEX Foo_partial_inx (b=?)

And I suspect that's why it picks Foo_inx over Foo_partial_inx.  But otherwise 
this behavior seems to be exactly what I want though (will need to step through 
an 'explain' to make sure it doesn't do main table lookups), but it requires an 
INDEXED BY to get there.


As a workaround, if I repeat the WHERE clause field ('a') in the partial index 
field list, THEN it starts using the partial index automatically:

create index Foo_partial_inx2 on Foo(a,b,c) where a = 1;
analyze Foo;
> SEARCH TABLE Foo USING COVERING INDEX Foo_partial_inx2 (a=? AND b=?)

But that has 2 problems:
a) this makes the index bigger due to the extra (very unnecessary) column
b) the executer doesn't seem to take into the account that this is a partial 
index so it searches for 'a' (minor issue)

Of course the partial index still has less rows than without partial so it's 
still a win, but still - it shouldn't need 'a' to be repeated on every row. 
Either way, though there are issues with the workaround, the bigger issue is 
that it doesn't automatically pick the original Foo_partial_inx in the first 
place.

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database locked on select

[Deon Brewis]

By one connection doing SELECT and UPDATE, do you mean multi-threaded mode and 
using the connection from 2 threads?

- Deon

-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Sunday, May 27, 2018 3:39 PM
To: SQLite mailing list 
Subject: Re: [sqlite] database locked on select

On 27 May 2018, at 7:30pm, Torsten Curdt  wrote:

> I am doing a select, then iterate through the resultset and on each 
> row call update on that row.
> I am using the golang driver and ran into the issue that on the update 
> the database is still locked from the select.

Are you usihg one connection to do both the SELECT and the UPDATE ?  Or are you 
using two different connections, one for the SELECT and one for the UPDATE ?

The second method will not work.  The SELECT connection has the database locked 
until you tell your interface you have finished with the SELECT.

Simon.
___
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] Is there ever a perf advantage to LIMIT outside of a subquery?

[Deon Brewis]

e.g. If you do:

SELECT c1 from t1 ORDER BY c2 LIMIT 5;

vs. just running the query without the "LIMIT" clause and taking the top 5 rows 
programmatically?


Obviously in the case of a subquery this makes a difference:
select count(*) from (select * from t1 limit 5);


But would it ever in a straight query with joins etc.?

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Row count of a partial index ?

[Deon Brewis]

Is there a direct way to query how many rows are in a partial index?

e.g.

select count(*) from table indexed by partial_index

(this does not work - no query solution).


I know I can repeat the WHERE clause expression in the query, but this is for a 
dynamically constructed index and knowing what the current "where" clause is on 
the index has some race conditions attached. Solvable... but if there is a 
direct way, I'd prefer that. Otherwise no big deal.

(I know I can also ANALYZE and check sqlite_stat1 after, but that's not really 
a solution...)

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Header corruption

[Deon Brewis]

Right, that's the bug we found. The statements are in the reverse order.  It's 
not quite that simple as the code below, but it ends up happening in that 
order. 

It's one of the most fundamental things a senior dev asks themselves on every 
call that that differentiates them from a junior dev - "What is the lifetime 
semantics of this thing I'm handing over / getting back?".

You don't even need the documentation for this one after looking at how OpenSSL 
gets the socket handle in the first place - the bug is obvious, I just wasn't 
aware this codepath even existed. (Big product, big team - you know how it 
goes).

- Deon

-Original Message-
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of 
Olivier Mascia
Sent: Tuesday, April 17, 2018 2:28 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Header corruption

> Le 17 avr. 2018 à 22:07, Deon Brewis <de...@outlook.com> a écrit :
> 
> closesocket(_socket); // posix socket
> SSL_shutdown(_ssl); // openssl (_ssl was initialized using the _socket 
> above)

These two statements are inherently wrong, in this order. First you 
SSL_shutdown(), then you closesocket(). Not the reverse. And in Windows code it 
even is good citizenship to call shutdown() properly in between.  AFAIK it 
should also be on Linux, though I'm told it is not commonly seen.  But please 
check the respective OS'es SDK and OpenSSL documentation for the details.

Called in the right order there is no file descriptor re-use syndrome to fear, 
because the closesocket() rightfully comes as the very last step.

--
Best regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia 
(from mobile device)


___
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] Header corruption

[Deon Brewis]

Fair enough. Agreed, just less of a chance (and we haven't seen it) because 
Windows seems to provide more spacing between these values.

- Deon

-Original Message-
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of 
Warren Young
Sent: Tuesday, April 17, 2018 1:54 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Header corruption

On Apr 17, 2018, at 2:07 PM, Deon Brewis <de...@outlook.com> wrote:
> 
> One just has to be in the mindset that on unix based platforms, a socket is a 
> file handle. (Not instinctive if you're coming from a Windows background).

You’re either using Winsock 1.1 or are dragging forward obsolete knowledge from 
the DOS-based versions of Windows:

   https://msdn.microsoft.com/library/windows/desktop/ms740522.aspx

Unless your code accidentally works on Windows because of differing semantics, 
there was a risk of seeing this bug bite on Windows, too.
___
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] Header corruption

[Deon Brewis]

Yes, but the socket values differ by the thousands in Windows from handle 
values returned by CreateFile. 

On MAC they don't differ at all - it immediately gets re-used.

- Deon

-Original Message-
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of 
Peter Da Silva
Sent: Tuesday, April 17, 2018 1:13 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Header corruption

On 4/17/18, 3:08 PM, "sqlite-users on behalf of Deon Brewis" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of de...@outlook.com> 
wrote:
> So this was a special case of re-using the File handle as per the corruption 
> guide. One just has to be in the mindset that on unix based platforms, a 
> socket is a file handle. (Not instinctive if you're coming from a Windows 
> background).

Even on Windows, wouldn't doing an ssl_shutdown on a socket you'd already 
closed still have a risk of unexpected behavior?
 


___
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] Header corruption

[Deon Brewis]

After months, I managed to track this down. I'd like to extend a BIG thanks to 
Richard Hip and Dan Kennedy for their help in helping me instrument and 
understand the SQLITE internal data structures better, as well as giving me a 
way to programmatically do this, as well as to teach me about the showdb tool. 
It has been invaluable to help me understand and categorize the corruption 
which in turn helped me to know what common pattern to look out for.

Turns out, in some shutdown scenarios we would call what ends up being this:

closesocket(_socket); // posix socket
SSL_shutdown(_ssl); // openssl (_ssl was initialized using the _socket above)


If in between those two commands, we opened a new SQLITE connection on another 
thread, SQLITE may get the same file handle value as what the old socket value 
used to be.

SSL_shutdown then sends out a sequence resembling the following, to what used 
to be the socket:
150301002071476f3be1f3fa76f22b9addbe0f520ebbe007fcc1d6536c19ec9d69c5334799

However, since the old socket handle value is now being used as a file handle 
value, the sequence ends up in the database file instead.

So this was a special case of re-using the File handle as per the corruption 
guide. One just has to be in the mindset that on unix based platforms, a socket 
is a file handle. (Not instinctive if you're coming from a Windows background).

Thanks again guys!


I'll leave the following search terms here as well in case someone searches for 
this in the future:
1503010020
15030100
352518400 (decimal version)
OpenSSL

- Deon

-Original Message-
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of 
Deon Brewis
Sent: Tuesday, February 6, 2018 8:57 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: [sqlite] Header corruption

I’m trying to track down SQLITE corruptions that seems to corrupt our databases 
in a consistent way. (Running on 3.20.1).

This isn’t related to a crash or hardware failure. The app is running and 
reading and writing to the database fine, and then suddenly we start getting a 
SQLITE_NOTADB errors. The first error we got was on trying to execute a ‘BEGIN 
TRANSACTION’, and everything thereafter started failing.

When our database headers are fine, I see this:
h: 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 ; SQLite format 3.
0010h: 10 00 02 02 00 40 20 20 00 00 10 9B 00 01 34 42 ; .@  ...›..4B

On corruption #1, I see this:
h: 15 03 01 00 20 01 91 21 85 44 17 2C ED BE 21 FA ;  .‘!…D.,í¾!ú
0010h: 9E 74 E7 EA 9A 0A 15 2C 99 B0 B7 3B C6 F0 35 FB ; žtçêš..,™°·;Æð5û

On corruption #2, I see this:
h: 15 03 01 00 20 0F 1E CB B1 FF 9C 1E D0 D6 BB 22 ;  ..˱ÿœ.ÐÖ»"
0010h: 15 64 D3 F4 DD 38 FB DF A3 E0 47 B8 D7 F6 21 BC ; .dÓôÝ8ûߣàG¸×ö!¼


This bit pattern of “15 03 01 00” for the first 4 characters where it should be 
“SQLi” is a bit too much of a coincidence to ignore or write off as disk error.

The rest of the header and file after the first 25-ish bytes or so, seem fine. 
It’s just these first 25  bytes that gets corrupted. If I restore Offsets 0 to 
Offsets 27 out of a “working” database, I get a usable file.

Under what circumstances would SQLITE even attempt to re-write the header 
string?

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7C%7Cfdf8fd84436048062e6c08d56d82ae47%7C84df9e7fe9f640afb435%7C1%7C0%7C636535330385784749=qjk7S7H6FW%2FaZIBD2XKPULL4v1wH7p2UN4GaKkx1O7I%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What does this assert mean? (sqlite3_mutex_held)

[Deon Brewis]

assert( sqlite3_mutex_held(pPage->pBt->mutex) );

Inside this stack:

#4  0x000103e89384 in releasePageNotNull at sqlite3.c:62589
#5  0x000103e87a94 in btreeReleaseAllCursorPages at sqlite3.c:61069
#6  0x000103e8ee00 in sqlite3BtreeCloseCursor at sqlite3.c:64809
#7  0x000103e8e874 in sqlite3VdbeFreeCursor at sqlite3.c:75079
#8  0x000103e8e4fc in closeCursorsInFrame at sqlite3.c:75105
#9  0x000103e8d2e0 in closeAllCursors at sqlite3.c:75155
#10   0x000103e8ca58 in sqlite3VdbeHalt at sqlite3.c:75642
#11   0x000103e482d8 in sqlite3VdbeReset at sqlite3.c:75890
#12   0x000103e48224 in sqlite3_reset at sqlite3.c:77871

Called from:
int errcode = sqlite3_reset(_stmt);

Specifically - under what circumstances would this fire? (Not blaming SQLITE 
here - trying to debug my code and understand the assert).

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_CONFIG_MULTITHREAD needed for connection pool?

[Deon Brewis]

The model we use for SQLITE is to use a connection pool (connections opened via 
sqlite3_open_v2).

We have many threads, but only one thread can use a connection at a time - a 
thread takes it out of the pool, and when done, returns it to the pool.

The only exception to this is calls to: sqlite3_interrupt(), which can be 
called from any thread.


Under that model, do we need to pass SQLITE_CONFIG_MULTITHREAD ?

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expression goes corrupt from valid SQL

[Deon Brewis]

The expression index functions behave deterministic and are marked as such.

We run clean under both AVRF and Sanitizer. We also allocate our own objects on 
our own separate heap (Windows) or zone (MAC) - so we're unlikely to ever get a 
pointer that points into the SQLITE memory space, even with an allocation/free 
bug. I know this is the most likely cause of corruption, and that's in our own 
code, but so far I can't find it.

Bad hardware is extremely unlikely since we've hit this hundreds of times over 
the last couple of months on 100s of different physical hardware and on 
different hardware platforms (PC, MAC, iOS, Android).

I sleep with "How To Corrupt An SQLite Database File" under my pillow. I'd be 
able to tell if you moved a comma on that page from one day to the next... I've 
looked for every one of those items multiple times over.

PS: I did send an example corrupted file to Richard - I can send it to you as 
well if you like?

- Deon

-Original Message-
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of 
Simon Slavin
Sent: Thursday, March 22, 2018 7:20 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Index on expression goes corrupt from valid SQL

On 23 Mar 2018, at 12:54am, Deon Brewis <de...@outlook.com> wrote:

> However, what we see doesn't generally exhibit like the bug describes. The 
> bug as reported gives errors like this:
> "row 1 missing from index idx1"
> 
> Where we instead see things like:
> "database disk image is malformed"
> 
> Can it still be related to the same issue?

I don't think so.  That particular error was not at all caught by the 
'malformed' error you're seeing.  It was reported as a corrupt index.

> In general, is there anything dangerous that you can do in a custom function 
> inside an indexed expression that we need to watch out for?
> 
> Most of the time when the database gets corrupted, we don't crash, it 
> corrupts midway through valid SQL (no pattern to it - completely unrelated 
> SQL). I was thinking if the expression functions have bugs in them it could 
> cause corruption, but can't really imagine how.

Is the function deterministic ?  Is it marked as deterministic when you define 
it ?

<https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.sqlite.org%2Fc3ref%2Fc_deterministic.html=02%7C01%7C%7C47b2838879894d62813408d59064aa7d%7C84df9e7fe9f640afb435%7C1%7C0%7C636573684383940382=CTO5yW6oacPBwn%2F3xpGn0yKsANWdqVvqsmROq%2Bmbvsk%3D=0>

> We use sqlite3_result_* functions to report results, and even if we read 
> invalid memory or pass invalid memory to SQLITE, it should crash - not 
> corrupt.

None of this should be happening unless -- please excuse my boldness -- the 
fault is in your own code.  Can you run your code under a tool which checks 
memory allocation and release, just to see if memory really is your problem ?

> We use sqlite3_result_* functions to report results, and even if we read 
> invalid memory or pass invalid memory to SQLITE, it should crash - not 
> corrupt.

To keep things fast, SQLite tends to assume that it itself is sane and doesn't 
do all the checking it could do.  But errors of this sort aren't usually 
difficult to track down.

A possibility you haven't mentioned is bad hardware.  It possible you have no 
software problems at all and your corruption is purely a bad sector in your 
storage medium.  Other than that, you might want to read

<https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.sqlite.org%2Fhowtocorrupt.html=02%7C01%7C%7C47b2838879894d62813408d59064aa7d%7C84df9e7fe9f640afb435%7C1%7C0%7C636573684383940382=bGBq77I7HjZW8Yqq%2B1wOOQifc1r9jkN2m1%2F5lSq1H7w%3D=0>

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7C%7C47b2838879894d62813408d59064aa7d%7C84df9e7fe9f640afb435%7C1%7C0%7C636573684383940382=4kf58cJlAUKQJeXAIT7CMXP8mLhhqxYWzoCx%2F7mjCRk%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index on expression goes corrupt from valid SQL

[Deon Brewis]

I was just reading through this issue:
https://www.sqlite.org/src/info/343634942dd54ab

Does this bug have any other symptoms other than as specified in the report 
above?

Reason I'm asking is that we are facing quite a bit of database corruption 
right now. We use a lot of expression indexes (written in custom C++ code), 
which now sounds suspicious.

However, what we see doesn't generally exhibit like the bug describes. The bug 
as reported gives errors like this:
"row 1 missing from index idx1"

Where we instead see things like:
"database disk image is malformed"

Can it still be related to the same issue?


In general, is there anything dangerous that you can do in a custom function 
inside an indexed expression that we need to watch out for?

Most of the time when the database gets corrupted, we don't crash, it corrupts 
midway through valid SQL (no pattern to it - completely unrelated SQL). I was 
thinking if the expression functions have bugs in them it could cause 
corruption, but can't really imagine how. We use sqlite3_result_* functions to 
report results, and even if we read invalid memory or pass invalid memory to 
SQLITE, it should crash - not corrupt.

- Deon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_CANTOPEN_ISDIR and other extended error codes

[Deon Brewis]

How do you actually get a SQLITE_CANTOPEN_ISDIR error?

In order to get an extended result code, we need to pass a sqlite3* connection, 
but you don't have that if the file can't be opened in the first place. Like... 
if it was a directory.

I see how this is implemented internally - it generally masks rc with 
db->errMask but in the case of openDatabase it returns just a hardcoded:
return rc & 0xff;

which truncates the 0x20e (SQLITE_CANTOPEN_ISDIR) error it had earlier into a 
SQLITE_CANTOPEN.

Is there some magic here that I'm missing? Shouldn't the default errMask be a 
DEFINE ?

- Deon


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Programmatically corrupting a database

[Deon Brewis]

Works like a charm. Thanks!

- Deon

-Original Message-
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of 
Richard Hipp
Sent: Friday, March 16, 2018 10:39 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Programmatically corrupting a database

On 3/16/18, Deon Brewis <de...@outlook.com> wrote:
> I am trying to write unit tests for some error detection & recovery 
> scenarios and would like to simulate the following type of corruptions:
>
> 1) Header corruption (easy)
> 2) Corrupting a specific SQL Table/Index so that it can be read, but 
> inserts/updates will fail
> 3) Corrupting a specific SQL Table/Index so that it can neither be 
> read nor written to
>
> Any ideas on how to do the last 2?

First run the deadly "PRAGMA writable_schema=ON;" statement.  Then make UPDATEs 
to the sqlite_master table to do things like change the root page number of the 
table or one of its indexes.


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7C%7Cb52a75ab16a34c1f984b08d58b64d9bb%7C84df9e7fe9f640afb435%7C1%7C0%7C636568187614528868=zKOCIZ7IfDLxHIR6WG98ZfFVtvkuvnG4%2Bqp5HiTCBYc%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Programmatically corrupting a database

[Deon Brewis]

I am trying to write unit tests for some error detection & recovery scenarios 
and would like to simulate the following type of corruptions:

1) Header corruption (easy)
2) Corrupting a specific SQL Table/Index so that it can be read, but 
inserts/updates will fail
3) Corrupting a specific SQL Table/Index so that it can neither be read nor 
written to

Any ideas on how to do the last 2?

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] iOS Watchdog and database corruption

[Deon Brewis]

I do. 

I'll have to request permission from the customer though to share it - who will 
potentially be looking at the file? (Just so I can share names and background 
with the customer to put him at ease).

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jens Alfke
Sent: Wednesday, February 21, 2018 9:19 AM
To: SQLite mailing list 
Subject: Re: [sqlite] iOS Watchdog and database corruption



> On Feb 21, 2018, at 7:45 AM, Simon Slavin  wrote:
> 
> My concern was that it's abnormal for "sqlite3LeaveMutexAndCloseZombie" to 
> take five seconds to execute.

As of a few weeks ago, I know all about this function ;-) It's called when the 
last statement is closed on a "zombie" database connection that's already had 
sqlite3_close_v2 called on it; it performs the actual close that was deferred. 
It's taking a long time because it's calling sqlite3WalCheckpoint.

But it is scary that the database file got corrupted. Deon, do you still have 
the corrupted file(s) available for forensics?

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7C%7C257363c1f5294b83d4c408d5794f3e3c%7C84df9e7fe9f640afb435%7C1%7C0%7C636548303605879185=91ZUzky2NXRMbmdZ70MnTnW%2FT4crgDMfNTwTXPiDsCg%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] iOS Watchdog and database corruption

[Deon Brewis]

Yes, definitely the main thread - we close down the database during 
applicationWillTerminate. It gives us 5 seconds to exit before it triggers the 
watchdog.

Termination Description: SPRINGBOARD, process-exit watchdog transgression: xxx 
exhausted real (wall clock) time allowance of 5.00 seconds |  | 
ProcessVisibility: Foreground | ProcessState: Running | WatchdogEvent: 
process-exit | WatchdogVisibility: Foreground | WatchdogCPUStatistics: ( | 
"Elapsed total CPU time (seconds): 3.740 (user 3.740, system 0.000), 25% CPU", 
| "Elapsed application CPU time (seconds): 0.049, 0% CPU" | )

Triggered by Thread:  0

Thread 0 crashed:
__semwait_signal: external code (libsystem_kernel.dylib)
nanosleep: external code (libsystem_c.dylib)
+[NSThread sleepForTimeInterval:]: external code (Foundation)
Database::signalCloseAndWait()
App::~App()
-[AppDelegate applicationWillTerminate:]: appdelegate.mm @ 377
-[UIApplication _terminateWithStatus:]: external code (UIKit)


"If the offending call really was "sqlite3LeaveMutexAndCloseZombie" then you 
may have some sort of mismanagement in your code"

What do you mean by that? Is it abnormal for sqlite3close to call 
sqlite3LeaveMutexAndCloseZombie?

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Wednesday, February 21, 2018 7:23 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] iOS Watchdog and database corruption

On 21 Feb 2018, at 2:35pm, Deon Brewis <de...@outlook.com> wrote:

> The application got watchdog terminated by iOS because the main thread was 
> taking too long (waiting for the sqlite3close on the worker thread). The 
> resultant force close seems to have aborted SQLITE in such a way that it 
> caused the database to be corrupted.
> 
> Worker thread stack:
> Thread 4:
>ftruncate: external code (libsystem_kernel.dylib)
>unixTruncate: sqlite3.c @ 34036
>sqlite3WalCheckpoint: sqlite3.c @ 56846
>sqlite3WalClose: sqlite3.c @ 56955
>sqlite3PagerClose: sqlite3.c @ 51556
>sqlite3BtreeClose: sqlite3.c @ 62169
>sqlite3LeaveMutexAndCloseZombie: sqlite3.c @ 142752
>sqlite3Close: sqlite3.c @ 0

I'm puzzled by this.  iOS gives applications quite a long time to terminate 
before calling "kill" on them.  Had "applicationWillTerminate" been called ?  
Was it definitely your main thread (via thread 4) which was delaying the 
termination, and not another thread ?  You should find the offending thread 
identified further up in that same report, just before it starts listing the 
call-stacks of each thread.

If the offending call really was "sqlite3LeaveMutexAndCloseZombie" then you may 
have some sort of mismanagement in your code.  Or it might be just a 
once-in-a-blue-moon problem which will never occur again.

The rest I lave up to the devs.  SQLite should not be corrupting a database 
just because it was unexpectedly terminated, no matter what it was doing when 
terminated.  It was written to avoid that and no amount of testing has shown 
such a bug.

> SQLITE3 version is 3.20.1. Database size is around 5 GB.

You have a 5 GB database on a device which may have a 16 GB capacity ?  I 
assume you know what you're doing.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7C%7C84050f4c5810477347f708d5793effe3%7C84df9e7fe9f640afb435%7C1%7C0%7C636548233837936119=ALIGynOvAu4HWcRE3wlBELXyEjC39PDXTYJDSNnJiqc%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] iOS Watchdog and database corruption

[Deon Brewis]

Interesting case of database corruption on iOS here.

Our main thread was waiting for a worker thread to exit. The worker thread was 
doing a sqlite3Close, which in turn did a checkpoint.

The application got watchdog terminated by iOS because the main thread was 
taking too long (waiting for the sqlite3close on the worker thread). The 
resultant force close seems to have aborted SQLITE in such a way that it caused 
the database to be corrupted.

Worker thread stack:
Thread 4:
ftruncate: external code (libsystem_kernel.dylib)
unixTruncate: sqlite3.c @ 34036
sqlite3WalCheckpoint: sqlite3.c @ 56846
sqlite3WalClose: sqlite3.c @ 56955
sqlite3PagerClose: sqlite3.c @ 51556
sqlite3BtreeClose: sqlite3.c @ 62169
sqlite3LeaveMutexAndCloseZombie: sqlite3.c @ 142752
sqlite3Close: sqlite3.c @ 0

SQLITE3 version is 3.20.1. Database size is around 5 GB.

Couple of questions:

a) Is it expected that an app crash / force terminate in the middle of a 
SQLITE3 checkpoint like this can cause corruption?

b) Is there a way I can do a close without triggering a checkpoint? (In order 
to speed up close, so that it doesn't trigger a watchdog).

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Recommended tool to read SQLITE btree?

[Deon Brewis]

Anybody have a recommendation for a tool that can read/show/interpret a SQLITE 
file at the BTREE level?

Want to be able to decode the links between pages, figure out what all the data 
mean etc. And should be able to work on a corrupted file.

- Deon


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Header corruption

[Deon Brewis]

Oh yeah, I don’t think this is a SQLITE bug or anything. 

I think something in our code is writing to memory after freed. I'm just trying 
to track it down at the point that it happens. We've tried all Profiling tools 
on both OSX and Windows without luck, so my next step is trying to find the 
writing thread at the point of corruption.

Dan Kennedy's suggestion seems like that would we that way to do that.

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Wednesday, February 7, 2018 8:32 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Header corruption

On 7 Feb 2018, at 3:16pm, Deon Brewis <de...@outlook.com> wrote:

> So this looks more like something is overwriting the memory of Page1 before 
> SQLITE writes it back to disk.

That is almost always what people eventually admit to after reporting a problem 
like this.  Some part of their code is stomping on memory or a file handle 
which SQLite thought it had exclusive rights to.  Some of them discover it 
using a runtime profiler tool which looks for use of released memory or 
double-release of file handles, but I don't know enough about Windows to 
suggest anything.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7C%7C883405b8374547bace6508d56e486bb8%7C84df9e7fe9f640afb435%7C1%7C0%7C636536179674888635=ZZtP6DzeVTBCnnWqxu0hWRMVLS6NWTFLrBI0%2Buu6G4I%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Header corruption

[Deon Brewis]

That's an interesting thought, but we see this on Windows as well, and on 
Windows these files are opened with SQLITE_OPEN_EXCLUSIVE. 

So this looks more like something is overwriting the memory of Page1 before 
SQLITE writes it back to disk.

I don't think SQLITE verifies the memory during every write. There's a:
if( memcmp(page1, zMagicHeader, 16)!=0 ){

during lockBtree() but that seems to be on read, not write. 


Is there somewhere I can add a check of the header buffer before the write to 
disk?

Or does SQLITE literally never write this part of the header again once written 
the first time? (Seek past the zMagicHeader?).

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Dan Kennedy
Sent: Tuesday, February 6, 2018 9:08 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Header corruption

On 02/06/2018 11:57 PM, Deon Brewis wrote:
> I’m trying to track down SQLITE corruptions that seems to corrupt our 
> databases in a consistent way. (Running on 3.20.1).
>
> This isn’t related to a crash or hardware failure. The app is running and 
> reading and writing to the database fine, and then suddenly we start getting 
> a SQLITE_NOTADB errors. The first error we got was on trying to execute a 
> ‘BEGIN TRANSACTION’, and everything thereafter started failing.
>
> When our database headers are fine, I see this:
> h: 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 ; SQLite format 3.
> 0010h: 10 00 02 02 00 40 20 20 00 00 10 9B 00 01 34 42 ; .@  
> ...›..4B
>
> On corruption #1, I see this:
> h: 15 03 01 00 20 01 91 21 85 44 17 2C ED BE 21 FA ;  
> .‘!…D.,í¾!ú
> 0010h: 9E 74 E7 EA 9A 0A 15 2C 99 B0 B7 3B C6 F0 35 FB ; 
> žtçêš..,™°·;Æð5û
>
> On corruption #2, I see this:
> h: 15 03 01 00 20 0F 1E CB B1 FF 9C 1E D0 D6 BB 22 ;  ..˱ÿœ.ÐÖ»"
> 0010h: 15 64 D3 F4 DD 38 FB DF A3 E0 47 B8 D7 F6 21 BC ; 
> .dÓôÝ8ûߣàG¸×ö!¼
>
>
> This bit pattern of “15 03 01 00” for the first 4 characters where it should 
> be “SQLi” is a bit too much of a coincidence to ignore or write off as disk 
> error.
>
> The rest of the header and file after the first 25-ish bytes or so, seem 
> fine. It’s just these first 25  bytes that gets corrupted. If I restore 
> Offsets 0 to Offsets 27 out of a “working” database, I get a usable file.
>
> Under what circumstances would SQLITE even attempt to re-write the header 
> string?

Is this on UNIX?

Quite often this sort of thing occurs because some other module is writing to a 
file-descriptor owned by SQLite. Usually because it closed its own fd, then 
SQLite opened the db file and was assigned the same integer fd value, then the 
rogue module wrote to the fd anyway. In other words, some other module is doing:

   close(fd);
   write(fd, "1503010020...", 25);

and between those two calls SQLite is calling open() and is being assigned a 
file-descriptor with the same integer value as fd.

Dan.





>
> - Deon
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmaili
> nglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=
> 02%7C01%7C%7C12190af418bc4a32192b08d56d8432f3%7C84df9e7fe9f640afb435aa
> aa%7C1%7C0%7C636535336909688339=OMCv%2BtfSJ4PvlFTFw8Cqcc
> 1eizCrnIgFnNSJ7x5XUoo%3D=0


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7C%7C12190af418bc4a32192b08d56d8432f3%7C84df9e7fe9f640afb435%7C1%7C0%7C636535336909688339=OMCv%2BtfSJ4PvlFTFw8Cqcc1eizCrnIgFnNSJ7x5XUoo%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Header corruption

[Deon Brewis]

I’m trying to track down SQLITE corruptions that seems to corrupt our databases 
in a consistent way. (Running on 3.20.1).

This isn’t related to a crash or hardware failure. The app is running and 
reading and writing to the database fine, and then suddenly we start getting a 
SQLITE_NOTADB errors. The first error we got was on trying to execute a ‘BEGIN 
TRANSACTION’, and everything thereafter started failing.

When our database headers are fine, I see this:
h: 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 ; SQLite format 3.
0010h: 10 00 02 02 00 40 20 20 00 00 10 9B 00 01 34 42 ; .@  ...›..4B

On corruption #1, I see this:
h: 15 03 01 00 20 01 91 21 85 44 17 2C ED BE 21 FA ;  .‘!…D.,í¾!ú
0010h: 9E 74 E7 EA 9A 0A 15 2C 99 B0 B7 3B C6 F0 35 FB ; žtçêš..,™°·;Æð5û

On corruption #2, I see this:
h: 15 03 01 00 20 0F 1E CB B1 FF 9C 1E D0 D6 BB 22 ;  ..˱ÿœ.ÐÖ»"
0010h: 15 64 D3 F4 DD 38 FB DF A3 E0 47 B8 D7 F6 21 BC ; .dÓôÝ8ûߣàG¸×ö!¼


This bit pattern of “15 03 01 00” for the first 4 characters where it should be 
“SQLi” is a bit too much of a coincidence to ignore or write off as disk error.

The rest of the header and file after the first 25-ish bytes or so, seem fine. 
It’s just these first 25  bytes that gets corrupted. If I restore Offsets 0 to 
Offsets 27 out of a “working” database, I get a usable file.

Under what circumstances would SQLITE even attempt to re-write the header 
string?

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convincing SQLITE to use alternate index for count(*)

[Deon Brewis]

Sorry, wrong table, should be:

CREATE INDEX FooBar ON Foo(Bar(GuidId));

It's an expression-based index. But I doubt that has anything to do with it. 
Can probably just be:

CREATE INDEX FooBar ON Foo(GuidId);

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Friday, January 26, 2018 1:23 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Convincing SQLITE to use alternate index for count(*)

On 1/26/18, Deon Brewis <d...@mylio.com> wrote:
> I have a table with 2 indexes:
>
> CREATE TABLE Foo (
> Id Integer,
> GuidId blob PRIMARY KEY
> ) WITHOUT ROWID;
>
> CREATE UNIQUE INDEX FooId ON Foo(Id);
>
> CREATE INDEX FooBar ON Resource(Bar(GuidId));

I think you mistyped something on that last line, and as a consequence, I have 
having difficulty decoding your problem.

>
> When I do:
> SELECT COUNT(*) FROM Foo;
>
> The query plan always uses the FooBar index.  But the FooBar index is 
> physically bigger on disk than the FooId index. I'd like it to count 
> FooId instead.  How can I coerce SQLITE to count FooId instead of FooBar?
>
> I've tried the following:
>
> SELECT COUNT(*) FROM Foo INDEXED BY FooId;  -- ignores the INDEXED BY
> clause
> SELECT COUNT(Id) FROM Foo INDEXED BY FooId; -- uses the right index, but
> filters nulls, so slower
> SELECT COUNT(1) FROM Foo INDEXED BY FooId;  -- uses the right index, but
> slower for some unknown reason??
>
> It seems the "COUNT(*) FROM Foo INDEXED BY FooId" fails to use FooId, 
> because FooId is a UNIQUE index. If I make FooId  not unique, it 
> correctly uses it.
>
> But surely UNIQUE shouldn't make any difference to this query? Either 
> way, that doesn't matter specifically, but I can't seem to find the 
> syntax to make it use the smaller index. Any ideas?
>
> - Deon
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://eur01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmaili
> nglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=
> 02%7C01%7C%7C72a5754a223d4a7add1e08d565030b31%7C84df9e7fe9f640afb435aa
> aa%7C1%7C0%7C636525986095881144=SOWM0RP1KQbSzYC4LPl8Trku
> MRE9ZGtc8jcijYZpVSo%3D=0
>


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://eur01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7C%7C72a5754a223d4a7add1e08d565030b31%7C84df9e7fe9f640afb435%7C1%7C0%7C636525986095881144=SOWM0RP1KQbSzYC4LPl8TrkuMRE9ZGtc8jcijYZpVSo%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Convincing SQLITE to use alternate index for count(*)

[Deon Brewis]

I have a table with 2 indexes:

CREATE TABLE Foo (
Id Integer,
GuidId blob PRIMARY KEY
) WITHOUT ROWID;

CREATE UNIQUE INDEX FooId ON Foo(Id);

CREATE INDEX FooBar ON Resource(Bar(GuidId));

When I do:
SELECT COUNT(*) FROM Foo;

The query plan always uses the FooBar index.  But the FooBar index is 
physically bigger on disk than the FooId index. I'd like it to count FooId 
instead.  How can I coerce SQLITE to count FooId instead of FooBar?

I've tried the following:

SELECT COUNT(*) FROM Foo INDEXED BY FooId;  -- ignores the INDEXED BY clause
SELECT COUNT(Id) FROM Foo INDEXED BY FooId; -- uses the right index, but 
filters nulls, so slower
SELECT COUNT(1) FROM Foo INDEXED BY FooId;  -- uses the right index, but 
slower for some unknown reason??

It seems the "COUNT(*) FROM Foo INDEXED BY FooId" fails to use FooId, because 
FooId is a UNIQUE index. If I make FooId  not unique, it correctly uses it.

But surely UNIQUE shouldn't make any difference to this query? Either way, that 
doesn't matter specifically, but I can't seem to find the syntax to make it use 
the smaller index. Any ideas?

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL and pragma uncommitted

[Deon Brewis]

What is the level of consistency (or rather inconsistency) for read_uncommitted?

If you start with:
INSERT INTO Woz(Foo, Bar) Values(1,1)

And a (normal) writer thread updates the 2 columns:
UPDATE Woz SET Foo=2, Bar=2

Can a read_uncommitted thread read the value from the row as:
Foo=1, Bar=2 
?


And if so, what about something like:
UPDATE Woz SET Foo=2, Bar=x'12345789'

Can a read_uncommitted thread read:
Foo=x'1234'

i.e. A partially updated column?


I would assume that for a transactional update across 2 rows all bets will be 
off.

What if a row was deleted, can a read_uncommitted read cause a crash?

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Friday, January 19, 2018 8:36 AM
To: SQLite mailing list 
Subject: Re: [sqlite] WAL and pragma uncommitted

On 19 Jan 2018, at 4:26pm, Hannah Massey  wrote:

> Will #pragma uncommitted work in WAL mode and will it have the effect 
> I'm looking for (where the read will be faster because it can ignore 
> the recently written information in the WAL File) and simply use the 
> database file only?

The command you’re talking about is

PRAGMA read_uncommitted = boolean

You may find you don’t need it.  Switch to WAL mode and try it without that 
PRAGMA.  It works more or less the way you described: one connection can write 
while other read, and nothing will block anything else.

Pay a lot of attention to how many connections you’re using.  You might want 
one connection for the writing, and another for all the reading threads.  Or 
you might want separate connections for the reading threads too.  Testing 
various approaches will let you find the solution which best suits your 
programming and timing requirements.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7C%7C7304a7658ee04115fe6508d55f5abc8e%7C84df9e7fe9f640afb435%7C1%7C0%7C636519765662668955=o6sR0H3YrZMdFUm9OwJ3CjyYi3J9s0MapK2%2BBeoiBuY%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I create a stealth index?

[Deon Brewis]

Thanks!

I've send an email with the  .fullschema to your private email below. Not sure 
if there is another better address?

- Deon

-Original Message-
From: drhsql...@gmail.com [mailto:drhsql...@gmail.com] On Behalf Of Richard Hipp
Sent: Tuesday, January 16, 2018 6:45 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Cc: de...@outlook.com
Subject: Re: [sqlite] Can I create a stealth index?

On 1/16/18, Deon Brewis <de...@outlook.com> wrote:
>
> I have seen a few cases where a newly added index would start showing 
> up uninvited in old, previously tested queries and bring performance 
> down by an order of magnitude. ('analyze' doesn't fix it).

We would welcome the opportunity to try to fix such problems.  If you can send 
us (even by private email) the output of ".fullschema" and the query in 
question, identify the offending index, and tell us what kind of timing 
discrepancy you are seeing, that would probably be sufficient to address the 
problem.

>
> The new indexes would only be needed for new queries, so I don't mind 
> forcing an 'indexed by' in for those, but I would like to avoid 
> retrofitting every previous query we've ever written with an 'indexed by'.

The following commands will probably prevent 'newindex' from being used by 
legacy queries:

   DELETE FROM sqlite_stat1 WHERE idx='newidx';
   INSERT INTO sqlite_stat1(tbl,idx,stats)
VALUES('thetable','newindex',100 100 100');

In the second line, there should be N+1 copies of '100' if there are N 
columns in the index.  And, of course, 'thetable' must be the name of the table 
that 'newindex' is indexing.

You will need to either close and reopen the database connection, or else run 
"ANALYZE sqlite_master;" after making the changes above in order for the 
changes to go into effect.

--
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] Can I create a stealth index?

[Deon Brewis]

Can I create an index in SQLITE that is only ever used in an 'indexed by' 
clause and not automatically picked up the query optimizer?

I have seen a few cases where a newly added index would start showing up 
uninvited in old, previously tested queries and bring performance down by an 
order of magnitude. ('analyze' doesn't fix it).

The new indexes would only be needed for new queries, so I don't mind forcing 
an 'indexed by' in for those, but I would like to avoid retrofitting every 
previous query we've ever written with an 'indexed by'.

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ compiler

[Deon Brewis]

Ditto. Large C++ project - no problem using SQLite.

Are you trying to compile sqlite.c as a C++ file? That won't work obviously, 
you have to compile as C and link it in.

The sqlite3.h header however can be pulled into any C++ file.

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Nelson, Erik - 2
Sent: Tuesday, January 2, 2018 9:50 AM
To: SQLite mailing list 
Subject: Re: [sqlite] C++ compiler

Eli Sent: Saturday, December 30, 2017 6:36 AM

>It would be awesome if SQLite could compile as a part of bigger C++ project.
>Right now there is a bunch of pointer casting errors, that can be fixed 
>in a matter of hour IMHO.

I don't have any trouble using it as part of a larger C++ project.

--
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 
https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.bankofamerica.com%2Femaildisclaimer=02%7C01%7C%7C8d0be66235844225ba0508d552094785%7C84df9e7fe9f640afb435%7C1%7C0%7C636505122160488357=O5qNYHgSsFYXspXcltee5v9RKF4SKqIru%2BPY%2FRWyHrY%3D=0.
   If you are not the intended recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7C%7C8d0be66235844225ba0508d552094785%7C84df9e7fe9f640afb435%7C1%7C0%7C636505122160488357=xij9z7ZYvd5j7T5L1Ygo63BNeQPGYPbcx3eLCate8LI%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Deon Brewis]

I would LOVE for this to be a Forum. I'd be willing to be a paid member/sponsor 
to help pay for running the forum software. 

I prefer XenForo for Forum software personally - it supports clipboard image 
copy/paste into the forum, which vBulletin doesn't.
 
- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Tuesday, November 21, 2017 6:31 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Many ML emails going to GMail's SPAM

On 11/21/17, Paul Sanderson  wrote:
> Coincidence!  I have just been in my gmail folder marking a load of 
> SQLite email as 'not spam'

I've been seeing mailing list emails go to spam for a while now.
Nothing has changed with MailMan.  I think what we are seeing is the beginning 
of the end of email as a viable communication medium.

I really need to come up with an alternative to the mailing list.
Perhaps some kind of forum system.  Suggestions are welcomed.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users=02%7C01%7Cdeonb%40outlook.com%7C63e7db9930544a06851808d5318080b7%7C84df9e7fe9f640afb435%7C1%7C0%7C636469350331513693=TlBzqW0Pe0HfIAir0O9QgJgj7uVU%2F7vcb6%2FGSfDv8mg%3D=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Understanding query optimizer choices

[Deon Brewis]

I have a query that joins with a virtual table where the optimizer has a choice 
of picking between 2 indexes for something, and it picks by far the wrong one.

I'm trying to understand how it all works.

I understand the output of both 'explain' and 'explain query plan' but I can't 
see from either of those why it will choose one plan over another. Subsequently 
I don't really know where my estimatedRows and estimatedCost from my virtual 
table comes into play with the decision so it's hard developing an intuitive 
feeling for what the correct values are to return.

Is there a way that I can view the cost metrics that goes into each query plan 
choice to see how it calculates the best index?

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

[Deon Brewis]

> That is a hugely important piece of information, and while I thank you for 
> letting me know, I’m shocked that this hasn’t been conveyed to the SQLite 
> developers (or, apparently, to Apple.) Did you report it anywhere?

I did report it:
http://sqlite.1065341.n5.nabble.com/SQLITE-vs-OSX-mmap-inevitable-catalog-corruption-td85620.html

- Deon 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jens Alfke
Sent: Tuesday, April 18, 2017 10:47 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS


> On Apr 18, 2017, at 1:50 AM, Deon Brewis <de...@outlook.com> wrote:
> 
> Are you by change using memory mapped IO (MMAP_SIZE something other than 0)?
> 
> This does not work on OSX. Not even remotely. I tracked an issue down in 
> November 2015, and was able to trivially corrupt a database 100% of the time 
> using the repro steps below. This happens long after our app gets shut down 
> and SQLITE is flushed.  It got fixed when I set MMAP_SIZE to 0.

That is a hugely important piece of information, and while I thank you for 
letting me know, I’m shocked that this hasn’t been conveyed to the SQLite 
developers (or, apparently, to Apple.) Did you report it anywhere?

I’m the architect of Couchbase Lite and the lead developer for iOS and macOS. I 
enabled SQLite’s memory-mapped I/O at least two years ago. I never considered 
it could be problematic since (a) Brendan is IIRC the only iOS/Mac developer 
who’s reported database corruption, and (b) I assumed scenarios like this would 
be covered as part of SQLite testing, either by SQLite themselves or by Apple.

> “From the OSX documentation:
> Note that while fsync() will flush all data from the host to the drive (i.e. 
> the "permanent storage device"), the drive itself may not physically write 
> the data to the platters for quite some time and it may be written in an 
> out-of-order sequence. Specifically, if the drive loses power or the OS 
> crashes, the application may find that only some or none of their data was 
> written.  The disk drive may also re-order the data so that later writes may 
> be present, while earlier writes are not.

This is a statement about hard disk controller firmware and is true for any OS. 
(I used to work for Apple, and corresponded with filesystem architect Dominic 
Giampaolo about this back in the day.) Some disk controllers don’t flush all 
cached data to the platters when told to flush, apparently to boost benchmark 
scores. Darwin has a FULLFSYNC command (an option to ioctl) that triggers an 
expensive full reset of the disk controller, which does produce a full flush.

The filesystem periodically performs FULLFSYNCs to ensure durability of changes 
to filesystem metadata, so that the filesystem itself can’t become corrupted by 
power loss. So I’m surprised that, in your test, cutting power after 
macroscopic amounts of time (2 minutes) have passed since closing the SQLite 
file still resulted in data being lost.

This doesn’t seem like it would be specific to memory-mapped I/O, though. 
Darwin has a universal buffer cache (like many other kernels) so memory-mapped 
writes and ordinary file writes are treated the same way by the filesystem 
cache.

Of course it’s possible there’s some kind of OS bug involved here; if so, it 
seems pretty serious. I’ll be glad to file a bug report with Apple if it turns 
out to be so.

—Jens
___
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] Database corruption, and PRAGMA fullfsync on macOS

[Deon Brewis]

Yip. Tried that. At some point I had like 6 or 7 debug switches in the app to 
try all manner of FULLSYNC/WAL/SYNCHRONOUS combinations. At the end it was the 
MMAP_SIZE that did it.

It's not like it was subtle - it's a dead on repro. I was able to repro this by 
doing a power cycle 2 hours after shutting the app down. OSX didn't seem to 
have any interest in flushing mmap files until you soft reboot the machine.

The last time I tried this though was on Yosemite and Mavericks and whatever 
version of SQLITE was out at the time, so things may be different now. But it 
would be the first place I would look for corruption on OSX related to power 
cycling.

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Tuesday, April 18, 2017 1:57 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS


On 18 Apr 2017, at 9:50am, Deon Brewis <de...@outlook.com> wrote:

> “From the OSX documentation:
> 
> Note that while fsync() will flush all data from the host to the drive (i.e. 
> the "permanent storage device"),

Deon,

I’m not sure this is related, but have you seen

<http://www.sqlite.org/pragma.html#pragma_fullfsync>

<http://www.sqlite.org/pragma.html#pragma_checkpoint_fullfsync>

?

Simon.
___
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] Database corruption, and PRAGMA fullfsync on macOS

[Deon Brewis]

Are you by change using memory mapped IO (MMAP_SIZE something other than 0)?

This does not work on OSX. Not even remotely. I tracked an issue down in 
November 2015, and was able to trivially corrupt a database 100% of the time 
using the repro steps below. This happens long after our app gets shut down and 
SQLITE is flushed.  It got fixed when I set MMAP_SIZE to 0.

Repro (100%):

1) Run our app
2) Shutdown our app
3) Wait for our app to cleanly shut down – nothing showing in Activity Monitor 
– and app.db-wal deleted from disk (i.e. SQLITE clean close)
4) Wait 2 minutes (so our app isn’t running in this 2 minute period at all)

5) Copy the our db file to a NAS
6) Hard reboot the machine (power cycle).
7) Copy the db file to the NAS again (no reopening the app, just copy the file 
back to the NAS after the reboot).

Observe…

The file from #5 still works fine.
The file from #7 is corrupted.

Note that the app or sqlite is nowhere involved in between #5 and #7


I made this note in the bug when I fixed it - I believe it's related, but don't 
have the exact context:
“From the OSX documentation:

Note that while fsync() will flush all data from the host to the drive (i.e. 
the "permanent storage device"), the drive itself may not physically write the 
data to the platters for quite some time and it may be written in an 
out-of-order sequence. Specifically, if the drive loses power or the OS 
crashes, the application may find that only some or none of their data was 
written.  The disk drive may also re-order the data so that later writes may be 
present, while earlier writes are not.

This is not a theoretical edge case.  This scenario is easily reproduced with 
real world workloads and drive power failures.”

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Brendan Duddridge
Sent: Tuesday, April 18, 2017 1:36 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

Hi Rowan,

My apologies. My sample app does use NSDocument. But my production app doesn't. 
And they both exhibit the same problem.

On Tue, Apr 18, 2017 at 12:29 AM, Rowan Worth  wrote:

> Hey Brendan,
>
> I'm no OSX expert, but from what I've read this afternoon about 
> NSDocument and friends combined with what I know about sqlite I have 
> to say you are completely mad to continue passing sqlite databases to 
> NSDocument,
> *especially* as you don't define your own sub-class to do any of the 
> file management.
>
> Relying on some NSDocument sub-class builtin to the system and then 
> also opening the DB with CouchbaseLite may well violate section 2.2.1 
> of "how to corrupt an sqlite database" (multiple copies of sqlite 
> linked into the same application). Even if not, it sounds like 
> NSDocument has a tendency to copy files around for eg. auto-save purposes.
>
> I'd be very *very* surprised if there's an sqlite bug here. I'd take 
> the advice given to you on bountysource and watch your application's 
> file system operations to begin to understand what is going on behind 
> the scenes.
>
> -Rowan
>
>
>
> On 18 April 2017 at 13:01, Brendan Duddridge  wrote:
>
> > Thanks Richard for your reply.
> >
> > Sorry about the COLLATE problem. That's a Couchbase Lite thing.
> >
> > I find it weird that a different WAL file is getting in there 
> > somehow
> when
> > a power failure occurs. I'm a bit stumped at how it can even write 
> > to the file system the moment the power shuts down so I don't even 
> > understand
> how
> > the database file can get corrupted.  Unless the corruption happens 
> > when the database file is opened up next and tries to use this 
> > improper WAL file.
> >
> > In my main project I'm using SQLCipher as my SQLite layer. But in 
> > the
> demo
> > project I posted, I'm just using the built-in macOS Sierra SQLite
> library.
> > In both cases though I can cause SQLite file corruption by cutting 
> > the power on my MacBook Pro.
> >
> > I'm going to do further investigations to see if there's any misuse 
> > of SQLite that I can find. Although the developers of Couchbase Lite 
> > are far smarter than I and I'm sure they're doing things by the book.
> >
> > Perhaps the only think I can think of that I'm doing that may be
> unorthodox
> > is by storing the SQLite files inside a macOS package. But at the 
> > unix layer that's really just a directory, so I don't know how that 
> > could
> cause
> > a problem. Unless macOS treats the package in a way differently than 
> > a normal folder and is causing things to get moved around or written 
> > when a power failure occurs.
> >
> > This problem has been plaguing me for quite a long time actually. I 
> > hope that I can find a solution somehow.
> >
> > Thanks,
> >
> > Brendan
> >
> > > I worked around the "COLLATE JSON" problem (by writing my own JSON 
> > > collation).  That allows 

Re: [sqlite] Expression Indexes - can I project the expression value from the index?

[Deon Brewis]

Thanks so much for looking into it!

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Wednesday, April 5, 2017 7:30 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Expression Indexes - can I project the expression value 
from the index?

On 4/5/17, Deon Brewis <de...@outlook.com> wrote:
> Hi,
>
> Just following up - is this tracked somewhere in a feature/bug database?
>

We are aware of the feature request and discussed it internally just yesterday. 
 It is non-trivial to implement.
--
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] Expression Indexes - can I project the expression value from the index?

[Deon Brewis]

Hi,

Just following up - is this tracked somewhere in a feature/bug database?


I have to do some interesting gymnastics to work around this all the time, 
since the query optimizer seems to think it can read covered values from an 
expression index, but during execution it doesn't.

e.g. On the example below, a query like:
SELECT func(col), count(*) FROM FOO GROUP BY func(col)

Will try to use FOOINDEX, but that's a really bad index since it then proceeds 
to executes func(col) on every row from the index, but FOOINDEX doesn't 
actually contain col, only the result - func(col).

- Deon

-Original Message-
> On 8/31/16, Deon Brewis <de...@outlook.com> wrote:
>
> Let's say I have an expression index:

> CREATE INDEX FOOINDEX on FOO(func(col))
>
> And then I use that index:
> SELECT bar FROM FOO INDEXED BY FOOINDEX WHERE func(col) = 42;
>
> I can see that it will use the stored value, since if I put a breakpoint on 
> func, it won't hit. Good and well.
>>
> HOWEVER, if I run:
> SELECT func(col) FROM FOO INDEXED BY FOOINDEX WHERE func(col) = 42
>
> It will execute the breakpoint on func(col) for every row in the 
> table, even though FOOINDEX is a covered index wrt. that query.
>
> Is there a way to avoid this? I would just like to return the value that's 
> already stored in the index.


We will take this as an enhancement request.

--
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

[sqlite] Search semantics with a virtual table?

[Deon Brewis]

How do I convince SQLITE to give me SEARCH semantics over a virtual table 
instead of SCAN semantics?

e.g. I have:

explain query plan SELECT * FROM vtable WHERE value = 12345

0 | 0 | 0 | SCAN TABLE vtable VIRTUAL TABLE INDEX 1:

And I'm returning in xBestIndex:
   pIdxInfo->idxNum = 1;
   pIdxInfo->idxFlags = SQLITE_INDEX_SCAN_UNIQUE;
   pIdxInfo->aConstraintUsage[0].argvIndex = 1;
   pIdxInfo->aConstraintUsage[0].omit = true;
   pIdxInfo->estimatedCost = 1;
   pIdxInfo->estimatedRows = 1;


So obviously the explain might just be iffy, but I'm actually getting SCAN 
semantics. Well, somewhere between SEARCH & SCAN.

I can perform a true search for the '12345' item between xBestIndex & xFilter, 
but after I returned the first item, SQLITE keeps calling back my xNext/xEof to 
ask for more and more items. However... I'm specifying SQLITE_INDEX_SCAN_UNIQUE 
so why doesn't it stop after I returned the first one?

Lacking convincing it to call me more than once... I then tried to tell it that 
the results I return are ordered and maybe it will notice once it runs beyond 
the range. Since there's no way to return an unsolicited order in xBestIndex, 
I've tried:

SELECT * FROM vtable WHERE value = 12345 ORDER BY value

And then responded:
  pIdxInfo->orderByConsumed = 1;


to the request. But nada - it still keeps calling me over and over.

I know I can hack around that by mucking around in my xEof call, but is that 
the only way? It really does seem like SQLITE_INDEX_SCAN_UNIQUE (or something 
similar) should achieve SEARCH semantics directly?

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memoization in sqlite json1 functions

[Deon Brewis]

> Page faults aren’t necessarily due to swapping. If you read a memory-mapped 
> file, the first access to any page will fault to disk. Since SQLite supports 
> memory-mapping, I’d assume this can occur during a query — the column data 
> returned by sqlite may point into mmap’ed pages. (Is that correct?)

> In that situation, if a blob value is significantly larger than one page, the 
> difference between scanning everything up to byte offset x, vs. reading a few 
> bytes at the beginning and then jumping directly to x, can be significant.

I don't think SQLITE will return a pointer directly into an mmap page offset, 
and it definitely can't in the case of a column spilled into an overflow page - 
the data will be discontiguous. 

Even something like the blob API's don't help, SQLITE overflow pages link to 
each other at the beginning of each page. So you can't e.g. load the 5th page 
without also loading page 1 through 4. Since SQLITE pages are generally smaller 
than disk pages, it means faulting in all the disk pages on the way.

Anyway, by faulting I meant 'paging back out due to memory pressure'. Sorry, 
should have been more clear - knew that one was going to bite me as soon as I 
hit send... 

How do you take a thread offline here?

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jens Alfke
Sent: Saturday, March 25, 2017 10:35 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Memoization in sqlite json1 functions


> On Mar 24, 2017, at 4:48 PM, Deon Brewis <de...@outlook.com> wrote:
> 
> Yeah ok, if you take I/O hits then things like memory pre-fetching makes zero 
> difference. We're more in the business of "You take a page fault" == "You buy 
> more memory". Different level of performance requirements. (And glad that 
> SQLITE works well for both of us).

Page faults aren’t necessarily due to swapping. If you read a memory-mapped 
file, the first access to any page will fault to disk. Since SQLite supports 
memory-mapping, I’d assume this can occur during a query — the column data 
returned by sqlite may point into mmap’ed pages. (Is that correct?)

In that situation, if a blob value is significantly larger than one page, the 
difference between scanning everything up to byte offset x, vs. reading a few 
bytes at the beginning and then jumping directly to x, can be significant.

>> In Fleece I put a lot of effort into making the C++ API nice to use, so that 
>> I don’t have to have any other data structure. That's worked well so far.
> 
> Strong typing?

That only happens at higher levels in our stack. At the level I mostly work on, 
this is a document-oriented, schemaless data store. Up above there’s the option 
to use a data-modeling layer that binds document properties to platform object 
properties with strong typing (though we don’t support C++.) But even those 
bindings can be considerably more efficient than the typical JSON object tree — 
you’re not allocating string objects for dictionary keys, nor number objects 
for numeric/boolean values. It’s also generally faster to populate instance 
variables of objects (usually at fixed offsets) vs. adding key/value pairs to a 
hash table.

[I think we’re getting off topic, but I’ll be happy to continue offline.]

—Jens
___
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] Memoization in sqlite json1 functions

[Deon Brewis]

> It could be; my knowledge of optimization gets tenuous when it comes to 
> down-to-the-metal areas like CPU caching. But for large data, you run the 
> risk of blowing out the cache traversing it. And if the data is 
> memory-mapped, it becomes hugely faster to skip right to the relevant page 
> instead of faulting in every page ahead of it.

Yeah ok, if you take I/O hits then things like memory pre-fetching makes zero 
difference. We're more in the business of "You take a page fault" == "You buy 
more memory". Different level of performance requirements. (And glad that 
SQLITE works well for both of us).


> In Fleece I put a lot of effort into making the C++ API nice to use, so that 
> I don’t have to have any other data structure. That's worked well so far.

Strong typing?


- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jens Alfke
Sent: Thursday, March 23, 2017 6:09 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Memoization in sqlite json1 functions


> On Mar 23, 2017, at 3:17 PM, Deon Brewis <de...@outlook.com> wrote:
> 
> If you however can use a forward-only push or pull parser like a SAX or StAX 
> parse, it's a different story. I'm using a StAX-like pull parser for a binary 
> json-ish internal format we have, and reading & parsing through it is on par 
> with the performance of reading equivalent SQLITE columns directly

I agree that’s a lot faster, but you’re still looking at O(n) lookup time in an 
array or dictionary. And the proportion constant gets worse the bigger the 
document is, since jumping to the next item involves parsing through all of the 
nested items in that collection.

> That obviously implies if you do random-access into a structure you have to 
> keep reparsing it (which is where Memoization would be nice). However, CPU 
> caches are better at reading continues data streams in forward-only fashion 
> than they are with pointers, so forward-only pull parsers, even when you have 
> to repeat the entire parse, are often faster than the math behind it suggests.

It could be; my knowledge of optimization gets tenuous when it comes to 
down-to-the-metal areas like CPU caching. But for large data, you run the risk 
of blowing out the cache traversing it. And if the data is memory-mapped, it 
becomes hugely faster to skip right to the relevant page instead of faulting in 
every page ahead of it.

> Besides, in 99% of cases my users take the outcome from a json parse and just 
> store the results into a C++ data structure anyway. In that case the 
> intermediary object tree is just a throwaway and you may as well have built 
> the C++ structure up using a pull or push parser.

In Fleece I put a lot of effort into making the C++ API nice to use, so that I 
don’t have to have any other data structure. That's worked well so far.

—Jens

___
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] Memoization in sqlite json1 functions

[Deon Brewis]

It has more to do with how you parse JSON - if you want to build it into an 
object tree, sure, then you're obviously dead in the water with any kind of 
json.

If you however can use a forward-only push or pull parser like a SAX or StAX 
parse, it's a different story. I'm using a StAX-like pull parser for a binary 
json-ish internal format we have, and reading & parsing through it is on par 
with the performance of reading equivalent SQLITE columns directly (apart from 
the expression indexed covered value lookup scenario... grumble... grumble...).

So binary-json like formats can perform well - you just can't use an object 
tree to parse them.

That obviously implies if you do random-access into a structure you have to 
keep reparsing it (which is where Memoization would be nice). However, CPU 
caches are better at reading continues data streams in forward-only fashion 
than they are with pointers, so forward-only pull parsers, even when you have 
to repeat the entire parse, are often faster than the math behind it suggests. 
(In the way that scanning an unsorted vector in O(n) is often times faster than 
searching O(log n) through a map).

Besides, in 99% of cases my users take the outcome from a json parse and just 
store the results into a C++ data structure anyway. In that case the 
intermediary object tree is just a throwaway and you may as well have built the 
C++ structure up using a pull or push parser. It's very like extra work, and 
it's way... way... faster. 

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jens Alfke
Sent: Thursday, March 23, 2017 11:05 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Memoization in sqlite json1 functions


> On Mar 23, 2017, at 4:30 AM, Richard Hipp  wrote:
> 
> BLOBs are reserved for a future enhancement in which BLOBs will store the 
> binary encoding for JSON.

I’ve been down this road. There are a number of JSON-compatible binary encoding 
formats, but most of them don’t save much time, because (a) they’re not _that_ 
much faster to parse, (b) reading JSON tends to be dominated by allocating an 
object tree, not by the actual parsing, and (c) usually you have to parse the 
entire data even if you only want to use one piece of it [as in a query].

I ended up designing and implementing a new format, called Fleece*. Its 
advantage is that it doesn’t require parsing or even memory allocation. The 
internal structure is already an object tree, except that it uses compressed 
relative offsets instead of pointers. This means that internal pointers into 
Fleece data can be used directly as the data objects.

In my current project** we’re storing Fleece in SQLite instead of JSON, with a 
modified version of the json1 extension to make it accessible in queries. It 
works very well. The Fleece equivalent of json_extract( ) just does some 
pointer manipulation to find the root object in the blob, then further lookups 
to jump to each nested object in the path.

—Jens

* https://github.com/couchbaselabs/fleece 

** https://github.com/couchbase/couchbase-lite-core
___
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] Memoization in sqlite json1 functions

[Deon Brewis]

That would be nice.

I've resorted to a few horrible hacks like this:

SELECT parse(data), extract("name"), extract("address"), release(data) FROM 
some_table;

It works, but it relies on LTR parsing of arguments (which it does now, but I 
seriously doubt is a guarantee), as well as global/thread-local variables.

Though I can still live with that one - it works.

What I would like to see - is if you have an indexed expression like so:
create index some_index on some_table( json_extract(json, '$.name') );

And you run:
select json_extract(json, '$.name') from some_table indexed by some_index;

That it returns the resultant value that is already stored in the index, rather 
than re-executing the expression.
 
- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Domingo Alvarez Duarte
Sent: Wednesday, March 22, 2017 5:04 PM
To: SQLite mailing list 
Subject: [sqlite] Memoization in sqlite json1 functions

Hello Richard !

I noticed that sqlite do not use any memoization in json1 functions.

For example jsonExtractFunc and others parse the json string every time it's 
called even when the json string is the same.

minimal example : "select json_extract(json, '$.name') name, json_extract(json, 
'$.address') name from some_table;"

Could be possible to have some kind of memoization as a general option for any 
sqlite functions ?

Cheers !


___
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] SQLite does not support ARM platform?

[Deon Brewis]

Ahh, ok, you're not actually trying to target Windows RT - you're trying to 
target Windows CE. That makes (a LOT) more sense.

The technical problem you have is that you're running into a Windows RT error 
there. You'll get the same behavior simply if you do this:

C:\test>copy con test.c
#include 
^Z
1 file(s) copied.

C:\test>cl test.c
test.c
C:\Program Files (x86)\Windows 
Kits\10\include\10.0.10240.0\ucrt\corecrt.h(205): fatal error C1189: #error:  
Compiling Desktop applications for the ARM platform is not supported.


The way to get around that for WinRT is to specify:
/DWINAPI_FAMILY=WINAPI_FAMILY_APP

So e.g.
cl test.c /DWINAPI_FAMILY=WINAPI_FAMILY_APP



However, this is probably not what you want... you want the SQLITE build for 
Windows CE - it's up on the site.

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jaime Stuardo
Sent: Monday, March 20, 2017 3:39 PM
To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] SQLite does not support ARM platform?

When I build my own program using Visual Studio 2008  IDE  to be run in the 
device, it compiles perfectly and also it runs perfectly in the machine. So 
there is nothing wrong with that.

I tried to compile sqlite3.c using the same command line parameters I saw in 
project properties in VS2008 but without success. Last try was this command 
line:

"cl sqlite3.c /O2 /Os /D "NDEBUG" /D "WIN32_PLATFORM_PSPC" /D "WINCE" /D "ARM" 
/D "_ARM_" /D "POCKETPC2003_UI_MODEL" /D "_UNICODE" /D "UNICODE" /FD /EHsc /MT 
/fp:fast /GR- /link /dll /machine:THUMB"

But the error is: "sqlite3.obj : fatal error LNK1112: module machine type 'X86' 
conflicts with target machine type 'THUMB'"

I use THUMB as the machine, because my own C++ project uses THUMB as the 
machine type and that works.

That is why I suspect about SQLite3 to be really multiplatform. Why can my own 
C++ program, that is not small, be successfully compiled and then run in the 
machine? Why cannot SQLite source code? I only need to generate the LIB file to 
add it to my project and the DLL file to copy to the machine. What command line 
do you use for your target machine?

Regards
Jaime

-----Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Deon Brewis
Sent: lunes, 20 de marzo de 2017 19:25
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] SQLite does not support ARM platform?

"That suggests me that the code is prepared for MSVC also, but it is not 
actually."

The SQLITE code works just fine on an MSVC build. Have been using it for many 
years.

What platform are you targeting? The MSVC ARM compiler is only meant for 
Windows RT devices - is that really what you're trying to build for?

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jaime Stuardo
Sent: Monday, March 20, 2017 3:17 PM
To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] SQLite does not support ARM platform?

Thanks all who answered.

Yes... it seems a MSVC limitation, however, it is very strange. I am using 
Visual Studio 2008 which does support ARM compilation.  I did that, adding some 
compiler switches for target WIN32_CE but I got million of warnings, of this 
type:

sqlite3.c(166) : warning C4005: 'OS_VXWORKS' : macro redefinition
sqlite3.c(158) : see previous definition of 'OS_VXWORKS'
sqlite3.c(216) : warning C4005: 'GCC_VERSION' : macro redefinition
sqlite3.c(214) : see previous definition of 'GCC_VERSION'
sqlite3.c(222) : warning C4005: 'CLANG_VERSION' : macro redefinition
sqlite3.c(220) : see previous definition of 'CLANG_VERSION'
sqlite3.c(227) : warning C4005: 'MSVC_VERSION' : macro redefinition
sqlite3.c(225) : see previous definition of 'MSVC_VERSION'
sqlite3.c(10924) : warning C4005: 'SQLITE_DEFAULT_PAGE_SIZE' : macro 
redefinition
sqlite3.c(10920) : see previous definition of 'SQLITE_DEFAULT_PAGE_SIZE'
sqlite3.c(10939) : warning C4005: 'SQLITE_MAX_DEFAULT_PAGE_SIZE' : macro 
redefinition
sqlite3.c(10935) : see previous definition of 
'SQLITE_MAX_DEFAULT_PAGE_SIZE'
sqlite3.c(11015) : warning C4005: 'SQLITE_INT_TO_PTR' : macro redefinition
sqlite3.c(11012) : see previous definition of 'SQLITE_INT_TO_PTR'
sqlite3.c(11016) : warning C4005: 'SQLITE_PTR_TO_INT' : macro redefinition
sqlite3.c(11013) : see previous definition of 'SQLITE_PTR_TO_INT'
sqlite3.c(11018) : warning C4005: 'SQLITE_INT_TO_PTR' : macro redefinition
sqlite3.c(11015) : see previous definition of 'SQLITE_INT_TO_PTR'
sqlite3.c(11019) : warning C4005: 'SQLITE_PTR_TO_INT' : macro redefinition
sqlite3.c(11016) : see pr

Re: [sqlite] SQLite does not support ARM platform?

[Deon Brewis]

"That suggests me that the code is prepared for MSVC also, but it is not 
actually."

The SQLITE code works just fine on an MSVC build. Have been using it for many 
years.

What platform are you targeting? The MSVC ARM compiler is only meant for 
Windows RT devices - is that really what you're trying to build for?

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jaime Stuardo
Sent: Monday, March 20, 2017 3:17 PM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] SQLite does not support ARM platform?

Thanks all who answered.

Yes... it seems a MSVC limitation, however, it is very strange. I am using 
Visual Studio 2008 which does support ARM compilation.  I did that, adding some 
compiler switches for target WIN32_CE but I got million of warnings, of this 
type:

sqlite3.c(166) : warning C4005: 'OS_VXWORKS' : macro redefinition
sqlite3.c(158) : see previous definition of 'OS_VXWORKS'
sqlite3.c(216) : warning C4005: 'GCC_VERSION' : macro redefinition
sqlite3.c(214) : see previous definition of 'GCC_VERSION'
sqlite3.c(222) : warning C4005: 'CLANG_VERSION' : macro redefinition
sqlite3.c(220) : see previous definition of 'CLANG_VERSION'
sqlite3.c(227) : warning C4005: 'MSVC_VERSION' : macro redefinition
sqlite3.c(225) : see previous definition of 'MSVC_VERSION'
sqlite3.c(10924) : warning C4005: 'SQLITE_DEFAULT_PAGE_SIZE' : macro 
redefinition
sqlite3.c(10920) : see previous definition of 'SQLITE_DEFAULT_PAGE_SIZE'
sqlite3.c(10939) : warning C4005: 'SQLITE_MAX_DEFAULT_PAGE_SIZE' : macro 
redefinition
sqlite3.c(10935) : see previous definition of 
'SQLITE_MAX_DEFAULT_PAGE_SIZE'
sqlite3.c(11015) : warning C4005: 'SQLITE_INT_TO_PTR' : macro redefinition
sqlite3.c(11012) : see previous definition of 'SQLITE_INT_TO_PTR'
sqlite3.c(11016) : warning C4005: 'SQLITE_PTR_TO_INT' : macro redefinition
sqlite3.c(11013) : see previous definition of 'SQLITE_PTR_TO_INT'
sqlite3.c(11018) : warning C4005: 'SQLITE_INT_TO_PTR' : macro redefinition
sqlite3.c(11015) : see previous definition of 'SQLITE_INT_TO_PTR'
sqlite3.c(11019) : warning C4005: 'SQLITE_PTR_TO_INT' : macro redefinition
sqlite3.c(11016) : see previous definition of 'SQLITE_PTR_TO_INT'
sqlite3.c(11021) : warning C4005: 'SQLITE_INT_TO_PTR' : macro redefinition

I need to compile this for Windows Mobile ARM device. Is there another way to 
do it by mean of other compiler that works in Windows? I need to use Windows 
since the output file should be a DLL.

By opening sqlite3.c I found directives such as this:

if defined(_MSC_VER) && _MSC_VER>=1400


That suggests me that the code is prepared for MSVC also, but it is not 
actually.


Does somebody have experience compiling this in MSVC?

Regards
Jaime

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Joseph Stockman
Sent: lunes, 20 de marzo de 2017 18:34
To: SQLite mailing list 
Subject: Re: [sqlite] SQLite does not support ARM platform?

This is a Visual Studio/Microsoft issue. We have been building SQLite on ARM 7 
and 9 for almost ten years across a wide variety of (non-Microsoft) operating 
systems.

V.


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Jaime Stuardo [jstua...@desytec.com]
Sent: Monday, March 20, 2017 4:24 PM
To: 'SQLite mailing list'
Subject: [sqlite] SQLite does not support ARM platform?

Hello,

I have finally given up. After almost all day trying to compile SQLite 
amalgamated source code files for ARM platform I got the conclusion that SQLite 
is not for ARM platform, even when documentation says the contrary. I have VS 
2008 and I tried to compile some C++ wrappers out there that contains 
amalgamated files but with no success

I tried several command line parameters, but nothing.

Last try was, by using only amalgamated files (shell.c, sqlite3.c, sqlite3.h, 
sqlite3ext.h). There is no science with this. I open a VS2008 command prompt 
and VS2012 ARM Command prompt and compile with:

"cl sqlite3.c -link -dll -out:sqlite3.dll -machine:ARM"

In both cases, I got this error:

"crtdefs.h(338) : fatal error C1189: #error :  Compiling Desktop applications 
for the ARM platform is not supported."

That took  me to the conclusion about SQLite is only for Desktop applications, 
which is very bad. Is there another alternative or trick to make it compile for 
ARM platform?

Thanks
Jaime

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

 This message is for the addressee's use only. It may contain confidential 
information. If you receive this message in error, please delete it and 

Re: [sqlite] Massively multithreaded SQLite queries

[Deon Brewis]

Can you give an example of an Intel Processor SKU with half-assed cores?

There's HyperThreading of course, but I don't think anybody has ever considered 
HyperThreading to be separate cores.

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf
Sent: Friday, March 3, 2017 4:52 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Massively multithreaded SQLite queries


No, the good rule of thumb is to allocate one thread per CPU.  Depending on the 
particular multi-core CPU you "may" be able to use all the cores simultaneously 
but in many cases diminishing returns will set in long before you can execute 
one thread per core.  If this is an Intel processor that claims it has more 
than one thread per core be *extremely* careful as that will give you one 
thread and one half-assed thread per core.  Sometimes, half-assed cores are 
presented as real cores when they lack a separate execution unit.  Be vary wary.

I presume you are forcing separate threads to separate cores by setting 
processor affinity on the threads and not just blindly hoping that the OS 
scheduler does "the right thing"?

> -Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jens Alfke
> Sent: Friday, 3 March, 2017 17:19
> To: SQLite mailing list
> Subject: Re: [sqlite] Massively multithreaded SQLite queries
> 
> 
> > On Mar 3, 2017, at 3:51 PM, Simon Slavin  wrote:
> >
> > Then mess with the '5' until you find a good value.
> 
> A common rule of thumb with thread pools is to allocate one thread per 
> CPU core.
> 
> —Jens
> ___
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Non-unique columns in unique index

[Deon Brewis]

I live my life one "indexed by" at a time.

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, March 2, 2017 7:10 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Non-unique columns in unique index


On 2017/03/02 4:44 PM, Keith Medcalf wrote:
> On Thursday, 2 March, 2017 06:04, Hick Gunter  wrote:

I think what Hick tried to show was that if you have a covering Index on fields 
F1 and F2 (Unique or not) and then have another index (Automatic or not, but 
Unique) on just F1, and you then do a Query of the form:

SELECT F1,F2 FROM T WHERE F1 = x AND F2 = y

SQLite will use the covering Index (as expected), but if you drop one WHERE 
term so as to end up with the form:

SELECT F1,F2 FROM T WHERE F1 = x

Then SQLite will use the other Index when the covering Index is really better 
(for read-speed) because it contains all the fields referenced and we "know" 
that F1 is Unique so the Covering Index must still be Unique for F1. So an 
Optimization would be that if we "know" F1 to be Unique, and all the fields 
required (in the SELECT) are found in the covering Index, then using the 
covering Index will be better.

While I follow the suggestion, I'd like to point out that a covering Index 
might not be the best to use. More than one column could be Unique or the 
covering Index may contain a lot more fields than is referenced, it may not 
always be faster. Imagine these tables:

CREATE TABLE T (ID INTEGER PRIMARY KEY, F1, F2, F3, F4, F5, F6, F7, F8, F9); 
CREATE UNIQUE INDEX TU_1 ON T(F1, F2); CREATE INDEX TC_2 ON T(F1, F2, F3, F4, 
F5, F6, F7, F8, F9);

The query:
SELECT F1, F2 FROM T WHERE F1 = x AND F2 = y; will surely be much faster when 
using the implied Unique index, and further:

SELECT F3 FROM T WHERE F1 = x AND F2 = y; should still be faster using the 
(much smaller) Unique Index and reading
F3 after a lookup. If you are in doubt, imagine the same example with Fields 
going up to F999. At some field-count the Unique Index will be faster for any 
selection of field types. It's hard to imagine a safe tweak for the QP here, 
and having to "assess" whether all referenced fields are ALSO in Unique 
indices... sounds like an expensive step, but now I'm just guessing.


___
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] Non-unique columns in unique index

[Deon Brewis]

"Any Index that starts with a Unique column will by definition be Unique."
Sorry, yet, I admit the title of the thread is confusing. What I meant to say 
is that it's TOO unique :). Adding the additional columns will allow duplicates 
on the columns where duplicates should not be allowed.


"This Query (for instance) will be exceedingly fast:
SELECT ExtraCol FROM Foo WHERE UniqueCol > range_start AND UniqueCol < 
range_end"

No, that's not covered. I've tried that before, that query is too slow when it 
isn't covered - the table is many GB's and the result is needed in low 
milliseconds. Also, I don't need an index directly on ExtraCol like specified 
below for the ( WHERE ExtraCol = ...) case . It's not useful a useful lookup by 
itself - only a useful result. So the only reason to include it in an index is 
to make it covered.


"You cannot optimize for everything, pick your favourite thing and optimize for 
that."

This is a bit of a call to support INCLUDE columns in indexes. This 
optimization is very easy in SQL Server using that.

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, March 2, 2017 2:50 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Non-unique columns in unique index

Any Index that starts with a Unique column will by definition be Unique. 
Of course in your case you want the Uniqueness of only the first column to be 
enforced, but you want to lookup also using the second column (either or both). 
Why insist on having it in a covering Index though?

Why not just make one Unique index, and one other Index on the other column by 
itself? Like so:

+CREATE TABLE Blah(
 Id Integer Primary Key,
 UniqueCol blob, // 20 bytes fixed
 ExtraCol blob, // 12 bytes fixed
 UNIQUE(UniqueCol)
);  // ~36 bytes/row

CREATE INDEX Blah_ExtraIdx on Blah(ExtraCol);


This will be close to the same size of one covering Index.
The Query planner is clever enough to figure out it can use only the second 
Index to look up some queries, it can also use the Unique Index if needed for a 
query and it will use it to enforce uniqueness.

You get all the speed and a little extra overhead space, but much less than a 
covering Index.

This Query (for instance) will be exceedingly fast:
SELECT ExtraCol FROM Foo WHERE UniqueCol > range_start AND UniqueCol < range_end

So would this:
SELECT * FROM Foo WHERE UniqueCol > range_start AND UniqueCol < range_end AND 
ExtraCol = xxx;

And this:
SELECT * FROM Foo WHERE ExtraCol = xxx;

All of these will be super fast and your Indices will take up the least 
possible space.

There is some saving in cycles if you can read the bytes directly out of a 
covering Index rather than a big table, but this is no big table, it should be 
real quick. If you really really really need those few cycles saved, invest the 
MBs and make the covering Index additional. If space is a problem, use only the 
single Unique index.

You cannot optimize for everything, pick your favourite thing and optimize for 
that.

Cheers,
Ryan

On 2017/03/01 7:00 PM, Deon Brewis wrote:
> Is there way to add non-unique columns in a unique index?
>
> I would like to use the same index to enforce unique constraints, as well as 
> giving a covered result for other queries.
>
> Something like an 'INCLUDE' would also work (actually even better). E.g.
>
> CREATE UNIQUE INDEX indx  ON Foo(UniqueCol) INCLUDE (ExtraCol)
>
>
> If not, is there a way to efficiently implement a UNIQUE constraint in 
> a different way? (Trigger maybe?)
>
> -Deon
>   
> ___
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Non-unique columns in unique index

[Deon Brewis]

Right, you need to use the birthday attack algorithm for determining collision 
risk.

I wouldn't mind hashing - but there is an additional complication - the query I 
want the covered field in the index for is this:

SELECT ExtraCol FROM Foo WHERE UniqueCol > range_start AND UniqueCol < range_end

So I would need a hashing algorithm that's usably small and doesn't collide, 
yet preserves the properties that if  a < b then  hash(a) < hash(b).

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Paul Sanderson
Sent: Wednesday, March 1, 2017 2:19 PM
To: p...@sandersonforensics.com
Cc: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Non-unique columns in unique index

Hmm - a bit of google foo and refreshing of chances of collions means my
1:9,223,372,036,854,775,808 is way off

That is the chance of any two hashes colliding - you'll have lot sof hashes.

The basic idea might be sound though even if you stick with a full MD5 it 
should save a chunk of storage

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 1 March 2017 at 22:13, Paul Sanderson <sandersonforens...@gmail.com>
wrote:

> As a bit of an off the wall suggestion you could try an MD5 (or even a 
> partial MD5 - half of the bytes)
>
> CREATE table hashes (
> hash integer primary key; // just the first 64 bits of the hash of 
> uniquecol and extracol
> )
>
> as an integer primary key the hash would be an alias of the rowid and 
> so storage would be 8 bytes plus admin
>
> the chance of a random colliison based on a 64 bit hash would be (I 
> think)
> 1:9,223,372,036,854,775,808
>
> MD5 is broken but would work OK for this
>
> use a trigger to abort the insert into blah if the insert into hashes 
> fails.
>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786 <+44%201326%20572786>
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit -Forensic Toolkit for SQLite email from a work 
> address for a fully functional demo licence
>
> On 1 March 2017 at 21:54, Simon Slavin <slav...@bigfraud.org> wrote:
>
>>
>> On 1 Mar 2017, at 9:41pm, Deon Brewis <de...@outlook.com> wrote:
>>
>> > Yeah ok, but that is paltry compared with the gb's of diskspace 
>> > that
>> the actual second index takes up. But thanks for clarifying.
>>
>> Ah.  If it’s really GBs of disk space then I can see why you’d look 
>> for alternative solutions.
>>
>> But I have a 43 GB database file which could be 20 GB without an 
>> extra index.  I could have written that extra check in code, and 
>> reduced the file size, but I decided not to.  Because once I had 
>> developed procedures to handle a 20 GB file, I might was well be dealing 
>> with a 43 GB file anyway.
>>
>> Simon.
>> ___
>> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Non-unique columns in unique index

[Deon Brewis]

Yeah ok, but that is paltry compared with the gb's of diskspace that the actual 
second index takes up. But thanks for clarifying.

-Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Wednesday, March 1, 2017 1:38 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Non-unique columns in unique index


On 1 Mar 2017, at 9:11pm, Deon Brewis <de...@outlook.com> wrote:

> "But look how much space and processing time it would take up"
> 
> Can you clarify what you mean by "space" ?

Your triggers have to be stored.  Every time they’re activated (each time you 
insert a row) they have to be run.  That requires SQLite to grab enough memory 
and/or disk space to run the triggers and to construct any temporary indexes 
they require.

Also, it’s a nightmare to debug in comparison with just creating one extra 
index.

Simon.
___
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] Non-unique columns in unique index

[Deon Brewis]

"But look how much space and processing time it would take up"

Can you clarify what you mean by "space" ?

The processing time argument I understand. 


I think this is one of those things that if the database engine doesn't 
internally support it, it can't really be emulated. 

Sure would be nice to have INCLUDE columns support (here and in other places).

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Wednesday, March 1, 2017 12:57 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Non-unique columns in unique index


On 1 Mar 2017, at 8:21pm, David Raymond  wrote:

> The trigger version you asked about would look something like the below I 
> believe. More risky than having the two indexes, but should work. (Famous 
> last words)

I have no doubt it would work.  But look how much space and processing time it 
would take up.  Far simpler and clearer just to create the two indexes.

Simon.
___
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] Non-unique columns in unique index

[Deon Brewis]

Thanks, thought about it. But I have a ROWID requirement as well due to foreign 
keys referring back to this table via the ROWID.

I could in theory put that just the RowId in an additional index, but then the 
RowId index will just carry the entire UniqueCol again - back to the same 
problem, just reversed.

To throw the specific design out there:

This is a frequent read, infrequent update table:

CREATE TABLE Blah(
Id Integer Primary Key,
UniqueCol blob, // 20 bytes fixed
ExtraCol blob, // 12 bytes fixed
UNIQUE(UniqueCol)
);  // ~36 bytes/row

CREATE INDEX sqlite_autoindex_Resource_1 on Blah  ( // implicit index
UniqueCol,   // 20 bytes
 // Id -- implicit
)  // ~24 bytes/row

CREATE INDEX blahIndex on Blah ( // actual needed index for workload
UniqueCol, // 20 bytes
ExtraCol,  // 12 bytes
 // Id -- implicit
)  // ~36 bytes/row

So this 3rd index is exactly as big as the original table. I would love to get 
rid of at least the 2 vs. 3 redundancy here. 

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Clemens Ladisch
Sent: Wednesday, March 1, 2017 9:58 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Non-unique columns in unique index

Deon Brewis wrote:
> Is there way to add non-unique columns in a unique index?
>
> I would like to use the same index to enforce unique constraints, as well as 
> giving a covered result for other queries.
>
> CREATE UNIQUE INDEX indx  ON Foo(UniqueCol) INCLUDE (ExtraCol)

CREATE TABLE Foo (
  UniqueCol PRIMARY KEY,
  ExtraCol,
  [...]
) WITHOUT ROWID;


Regards,
Clemens
___
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] Thread safety of serialized mode

[Deon Brewis]

Actually C# await on at least the file async IO operations will complete the 
await on the I/O completion port callback.
 
It's absolutely sad that there is no C++ support for that as of yet, and the 
standards committee is nowhere near it. It got kicked out of C++ 17... again. 
MAYBE we'll have language support in 2020, and then it's still going to be 1 or 
2 cycles after that before there will be standard library support for common 
async IO operations.

PS: The work that Microsoft did in Midori & M# on concurrency was also very 
promising. This is worth a read:
http://joeduffyblog.com/2016/11/30/15-years-of-concurrency/

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of James K. Lowden
Sent: Friday, February 17, 2017 9:17 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Thread safety of serialized mode

On Fri, 17 Feb 2017 04:10:09 +0000
Deon Brewis <de...@outlook.com> wrote:

> If you look at the original underlying NT I/O architecture that Cutler 
> implemented - it is a thing of beauty that's based in async patterns, 
> and not threads.
...
> If instead NT initially only exposed the Nt API's and not the Win32 
> layers, we would have had languages that simplified async a long time 
> ago - and multi-threaded would be the domain of a few applications 
> that actually need compute, and not just non-blocking IO.

That's very interesting, Deon, thanks.  I'm happy to cross out Cutler's name 
from my list of those who Ruined Computing During My Lifetime.  

I'm not as optimistic as you about what would have happened languagewise.  
Language support for OS features is pretty rare.  Threads happened, and no 
language corralled them. Windows has completion ports, which are very useful 
for asynchronous control, but afaik no Microsoft language supports them.  

It's why I like Go: it's the first language in 30 years to incorporate 
concurrency in its design, and finally support a theoretically sound model.  

--jkl
___
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] Thread safety of serialized mode

[Deon Brewis]

If you look at the original underlying NT I/O architecture that Cutler 
implemented - it is a thing of beauty that's based in async patterns, and not 
threads.

It was the Win32 wrappers over the NT subsystem that tried to make things 
"easier" for developers to deal with, which forced synchronous blocking code on 
top of the async Zw/Nt layers. This made the only practical way to deal with 
"blocking" I/O to be multi-threaded.

Today even junior-ish developers can deal with async code in node.js, and not 
bat an eyelid about it - the language makes async interaction simple enough - 
even in a single threaded environment. It wasn't the underlying technology was 
wrong, it was the simplifying abstraction on top of it that was.

If instead NT initially only exposed the Nt API's and not the Win32 layers, we 
would have had languages that simplified async a long time ago - and 
multi-threaded would be the domain of a few applications that actually need 
compute, and not just non-blocking IO. This wasn't due to Cutler's architecture 
though - more market driven decisions trying to maintain API compatibility with 
Win95, which was in turn driven by API compatibility with 16 bit API's, which 
long predated Cutler.

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of James K. Lowden
Sent: Thursday, February 16, 2017 6:26 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Thread safety of serialized mode

On Thu, 16 Feb 2017 21:49 +
Tim Streater  wrote:

> > What's inherently wrong with threads in principle is that there is 
> > no logic that describes them, and consequently no compiler to 
> > control that logic.
> 
> [snip remainder of long whinge about threads]
> 
> Sounds, then, like I'd better eliminate threads from my app. In which 
> case when the user initiates some action that may take some minutes to 
> complete, he can just lump it when the GUI becomes unresponsive.

[snip chest thumping]

You didn't refute my assertion, and facts refute yours.  

There has been a GUI in use for some 30 years, dating back to your VMS days, 
that is single-threaded.  I'm sure you've heard of it, the X Window System?  

If your particular GUI system is based on threads, like, say, Microsoft 
Windows, then, yes, you're pretty much cornered into using threads.  But that 
doesn't change the fact that you have no compiler support to verify the 
correctness of memory access over the time domain.  It doesn't change the fact 
that the OS has subverted the guarantees your language would otherwise provide, 
such as the atomicity of ++i noted elsewhere in this thread.  

WR Stevens describes 4 models for managing concurrency:

1.  Mutilplexing: select(2)
2.  Multiprocessing
3.  Asynchronous callbacks
4.  Signal-driven

None of those subvert the semantics of the programming language.  In each case, 
at any one moment there is only one thread of control over any given section of 
logic.  

Hoare had already published "Communicating Sequential Processes"  
(http://www.usingcsp.com/cspbook.pdf) when it hired David Cutler to design 
Windows NT.  It's too bad they adopted threads as their concurrency-management 
medium.  If they'd chosen CSP instead, maybe they wouldn't have set computing 
back two decades.  

--jkl


___
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] Complicated join

[Deon Brewis]

OVER PARTITION BY ...


One can dream...

- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Raymond
Sent: Thursday, September 15, 2016 1:47 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Complicated join

Can it be done in SQL? Yes.

In any sort of pretty or efficient manner? Ehhh, maybe?

I came up with something that seems to work with the small sample cases that I 
came up with to try and cover your requirements there, but it's got a couple 
levels of CTE's with long "where not exists..." clauses etc, and I've probably 
missed something. If you could provide a sample set of insert statements to 
paste in along with "here's what I hope to see at the end from this" that would 
help out.

Also, when you ask "Can this be done in SQL?" are you asking...
-in a single statement?
-in only SQL, but multiple statements are ok (such as using intermediate temp 
tables)?
-with an initial SQL query, but then the ability to muck about with the 
returned results in the language of your choice thereafter?
-something else?

Thanks,

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Bicking
Sent: Thursday, September 15, 2016 11:53 AM
To: SQLite Mailing List
Subject: [sqlite] Complicated join

I have two tables:
CREATE TABLE M ( CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY 
KEY(CombinedKeyFields, EvtNbr, TransDate));CREATE TABLE E  ( CombineKeyFields, 
EvtNbr, TransDate, OtherFields, PRIMARY KEY(CombinedKeyFields, EvtNbr, 
TransDate)); "CombinedKeyFields" is shorthand for a combination of about a half 
dozen fields in the primary key."TransDate" is an integer storing a proprietary 
date sequence number, where an older date is always less than a newer date Now, 
I want to do E LEFT JOIN M
(1) The CombinedKeyFields must always match in each table

(2) Match using the EvtNbr, but if no match, use the lowest M.EvtNbr that 
matches the CombinedKeyFields

(3) Match using the TransDate but if no exact match, match on the M.TransDate 
that is less than the E.TransDate but greater than the prior E.TransDate For 
M.TransDate = 94E.TransDate = 96 will match to 94but E.TransDate = 98 will have 
no match because 94 is less than the prior trans at 96..The idea is to find the 
closest date that matches that couldn't be matched to another record.
All this data is coming from upstream data, so this is the data we have on 
hand, though the schema for this reporting package can still be changed, but I 
would have to justify the change by saying the report can only be done with the 
change...
Can this join be done in SQL?
If this were an inner join, I believe I could use CASE statements in the WHERE 
clause, but I'm not sure that would work moving it to the JOIN... ON clause.
Any advice or help is much appreciated.
Thanks,David
Saying a prayer that the email gods won't wrap all these lines together in to 
an unreadable mess like the last time I asked for advice here...
___
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Expression Indexes - can I project the expression value from the index?

[Deon Brewis]

Sorry! My bad.

I work in one of those shops where everything from a feature request, to a 
task, to a customer support issue, to an actual software defect is referred to 
as a "bug". I forget it doesn't always translate.

Thanks for looking into it!
- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Wednesday, August 31, 2016 4:15 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Expression Indexes - can I project the expression value 
from the index?

On 8/31/16, Deon Brewis <de...@outlook.com> wrote:
> Thanks! I'll be willing to test this beta once it's available.
>
> One more thing - I think there is a bug here in the query planner

I'm pedantic about this because it is important:  What you describe is not a 
bug.  A bug means it gets the wrong answer.  Getting the correct answer more 
slowly than you would like is not a bug - that's an optimization opportunity.  
So what you describe (if I can verify it, which seems likely, but which I have 
not yet attempted to do) is an optimization opportunity, not a bug.

Thanks for reporting it.  I will look into it when I get a chance.

--
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] Expression Indexes - can I project the expression value from the index?

[Deon Brewis]

Thanks! I'll be willing to test this beta once it's available.

One more thing - I think there is a bug here in the query planner if there is 
an indexed expression in an index. It doesn't seem to think an index is ever 
covering if the inputs to the expression isn't in the index as well.

E.g.
CREATE TABLE Foo
(
   col1 int,   
   col2 int
);

CREATE INDEX FOOINDEX on FOO(abs(col1), col2);

SELECT col2 from Foo indexed by FOOINDEX where abs(col1) = 42;

In my opinion the index is covering the query above - even without col1 in the 
index. col1 isn't used in the select - only abs(col1) is used. It can't 
possibly be using col1 anywhere because it would hit a breakpoint I have. 
However, the query isn't executed as covered:


explain query plan SELECT col2 from Foo indexed by FOOINDEX where abs(col1) = 
42 ;
RecNo selectid order from detail   
-  -   
1 00 0SEARCH TABLE Foo USING INDEX FOOINDEX (=?)

-- NOTE: You can also see in the opcodes it's actually doing an OpenRead cursor 
to the main table as well, but then not ever using it:

explain SELECT col2 from Foo indexed by FOOINDEX where abs(col1) = 42;
RecNo addr opcode  p1 p2 p3 p4   p5 comment 
-  --- -- -- --  -- --- 
1 0Init0  14 0   00 (null)  
2 1OpenRead0  2  0  200 (null)  
3 2OpenRead1  3  0  k(3,nil,nil,nil) 02 (null)  
4 3Integer 42 1  0   00 (null)  
5 4SeekGE  1  11 1  100 (null)  
6 5IdxGT   1  11 1  100 (null)  
7 6IdxRowid1  2  0   00 (null)  
8 7Seek0  2  0   00 (null)  
9 8Column  1  1  3   00 (null)  
   10 9ResultRow   3  1  0   00 (null)  
   11 10   Next1  5  0   00 (null)  
   12 11   Close   0  0  0   00 (null)  
   13 12   Close   1  0  0   00 (null)  
   14 13   Halt0  0  0   00 (null)  
   15 14   Transaction 0  0  16 001 (null)  
   16 15   TableLock   0  2  0  Foo  00 (null)  
   17 16   Goto0  1  0   00 (null)  


If I however change the index to:
CREATE INDEX FOOINDEX on FOO(abs(col1), col1, col2)

Then it becomes a covering index:

explain query plan SELECT col2 from Foo indexed by FOOINDEX where abs(col1) = 42
RecNo selectid order from detail

-  -  
- 
1 00 0SEARCH TABLE Foo USING COVERING INDEX FOOINDEX 
(=?)


- Deon

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Wednesday, August 31, 2016 7:34 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Expression Indexes - can I project the expression value 
from the index?

On 8/31/16, Deon Brewis <de...@outlook.com> wrote:
>
> CREATE INDEX FOOINDEX on FOO(func(col))
>
> HOWEVER, if I run:
> SELECT func(col) FROM FOO INDEXED BY FOOINDEX WHERE func(col) = 42;
>
> It will execute the breakpoint on func(col) for every row in the 
> table, even though FOOINDEX is a covered index wrt. that query.

We will take this as an enhancement request.

--
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

[sqlite] Expression Indexes - can I project the expression value from the index?

[Deon Brewis]

Let's say I have an expression index:

CREATE INDEX FOOINDEX on FOO(func(col))

And then I use that index:
SELECT bar FROM FOO INDEXED BY FOOINDEX WHERE func(col) = 42;

I can see that it will use the stored value, since if I put a breakpoint on 
func, it won't hit. Good and well.


HOWEVER, if I run:
SELECT func(col) FROM FOO INDEXED BY FOOINDEX WHERE func(col) = 42;


It will execute the breakpoint on func(col) for every row in the table, even 
though FOOINDEX is a covered index wrt. that query.

Is there a way to avoid this? I would just like to return the value that's 
already stored in the index.

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index on computed value?

[Deon Brewis]

Is it possible to have an index on a computer value?


E.g. I have a 40 byte value in one of my columns. I only want an index over the 
first 4 bytes of it.


However, I don't really want to repeat those 4 bytes inside another column on 
the main table.


Is there any way to accomplish that?

- Deon

[sqlite] SQLITE vs. OSX mmap == inevitable catalog corruption?

[Deon Brewis]

We found that using SQLITE memory mapped I/O (mmap_size > 0) in OSX isn't 
usable in any way shape or form. It will inevitably lead to catalog corruption 
if you hard-reboot OSX, even without the database or application open.

We've tried FULLSYNC and F_FULLFSYNC, but it makes no difference.

Repro steps:
a) Run our application and write stuff to the database
b) Close our app & wait for the .wal file to disappear AND the app to disappear 
from activity monitor
c) Wait another 2 minutes
d) Copy your SQLITE db file to a network share
e) Hard reboot OSX
f) After the reboot, copy the same SQLITE db to a second network share
g) File compare (d) vs. (f)

Observe: The files in (c) and (e) are virtually never identical. NOTE: There is 
no application or SQLITE is involved here. The app is closed. It (should have) 
checkpointed and flushed properly (FULLSYNC + FULLFSYNC). However, the MAC 
doesn't seem to write the database to disk. So once you hard-reboot you don't 
have the same file anymore. SQLITE detects this as catalog corruption around 
25% of the time, but a file compare shows differences pretty much 100% of the 
time. Not just benign differences in unused pages - the header is more often 
than not different as well.

Without the Hard reboot our database always survives a close. You can 
gracefully shutdown the application, pkill it, force terminate, crash it, soft 
reboot - it all survives. However, once you hard reboot OSX - even AFTER the 
app is closed - it has a very high probability of corrupting our database.  
I've seen a worse case scenario where the hard reboot followed an app graceful 
shutdown by 12 hours, and it still corrupted the database.

This is so easy to reproduce I'm not sure why this isn't reported as a 
large-scale problem? It also only reproduces on OSX (both El Capitan and 
Mavericks) - Android, iOS, PC all work fine.


Anyway, it's not a big deal for us to set mmap_size to 0 to work around this.

The big problem I have however is the .wal.  We use SQLITE from multiple 
threads, and as such it's using shared memory to read/write the .wal. However, 
if persisting memory mapped files on OSX is so unreliable, then how can the 
.wal be expected to survive a hard reboot... So I'd also like to have a way to 
not use memory mapped I/O for .wal files, but I don't think there is a way 
unless I change the architecture of my app to have single-threaded access to 
SQLITE? Or is there another way?

- Deon

[sqlite] Bug 993556 - SQLite crash in walIndexTryHdr due to Windows EXCEPTION_IN_PAGE_ERROR exception

[Deon Brewis]

I'm trying to follow Richard's advise to work around this issue, which is:

"Is that database ever used by more than a single process.  (Use by multiple
threads using separate connections does not count - I mean really used by
multiple processes with their own address space.)  If not (and I think the
answer is "no") then FF could set "PRAGMA locking_mode=EXCLUSIVE"
immediately after opening the database and before doing anything else.  If
that is done, then SQLite will use heap memory for the WAL-index, instead of
mmapped shared memory, and this problem will never come up."


However, I'm unable to do so. I'm using multiple threads using separate
connections, like mentioned, but when I try to use PRAGMA
locking_mode=EXCLUSIVE, the next thread that tries to open a connection will
block indefinitely on the open.

So how can I go about using PRAGMA locking_mode=EXCLUSIVE while still using
multiple threads with connections?




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Bug-993556-SQLite-crash-in-walIndexTryHdr-due-to-Windows-EXCEPTION-IN-PAGE-ERROR-exception-tp78695.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to use multiple connections with locking_mode=exclusive

[Deon Brewis]

Richard implied in this bug that you can use locking_mode=exclusive when you
have a single process using that database, but that process has multiple
threads & connections:

https://bugzilla.mozilla.org/show_bug.cgi?id=993556


However, I've tried this and can't get it to work - the second open call
that comes around will block.

Am I misunderstanding the post, or just not getting the configuration right? 

I'm running:
WAL / NORMAL.

And my 2 sqlite3_open_v2 calls uses:
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX |
SQLITE_OPEN_PRIVATECACHE





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-use-multiple-connections-with-locking-mode-exclusive-tp78528.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Forcing a manual fsync in WAL/NORMAL mode

[Deon Brewis]

Simon Slavin-3 wrote
> When you lose data are you ever losing part of a transaction ?  SQLite is
> allowed to lose entire transactions, back to some END/COMMIT statement. 
> It should not be losing parts of transactions.  Or, at least, when you
> reopen the database using the SQLite library it should restore the data to
> an END/COMMIT statement.

Thanks a lot guys! This really helps. I'll try out the user_version trick.

To answer your question, Simon - no, I've never seen it losing part of a
transaction - only whole ones. 

This is just durability loss - the COMMIT call returned, but after crash &
restart, an entire transaction is reversed and it's back to the point of the
COMMIT before that.

I still much rather make the tradeoff for occasional whole transaction
losses instead of running WAL/FULL, as long as I have a way to manually
force a sync at critical times.




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Forcing-a-manual-fsync-in-WAL-NORMAL-mode-tp78260p78299.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Forcing a manual fsync in WAL/NORMAL mode

[Deon Brewis]

In the majority of my application, I'm fine running in WAL/NORMAL and lose
some committed transactions if the application crashes. (Which actually just
happened to me on iOS - I thought that can only happen on a full O/S crash -
not just app. But oh well).

However, every now and again I need to communicate state with an external
entity and to do that, I need to ensure local durability before that.

Is there any way to force a single manual fsync? (Or is it really just a
matter of calling the underlying O/S API?)

I know I can checkpoint, but I:

1) Don't need a full checkpoint - syncing to the WAL is fine.
2) Can't tell for sure whether doing a checkpoint while in WAL/NORMAL mode
will perform a sync before returning.





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Forcing-a-manual-fsync-in-WAL-NORMAL-mode-tp78260.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users