[sqlite] How to programmatically determine if trace is enabled for a db connection ?

[Howard Kapustein]

I can set a trace hook and I can clear a trace hook, but I don't see any way to 
tell if a sqlite3* has a trace hook registered

If not then consider this a feature request :P e.g. int sqlite3_db_config(db, 
SQLITE_DBCONFIG_TRACE) returning an OR'd combination of SQLITE_TRACE_* as you'd 
set via sqlite3_trace_v2


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

Re: [sqlite] Does prepare do arithmetic?

[Simon Slavin]

On 29 May 2017, at 5:48pm, Jeffrey Mattox  wrote:

> Why does a complex computation (but still consisting of only constants) make 
> a difference as to whether the computation is performed once or many times?  
> What's the dividing line between "simple" and "complex"?

Your program has a task to do.  You can do it once up-front and save the 
result, or you can do it a hundred times.  If that’s all you know, then you’d 
choose to do it once up-front, right ?

Then you find out that allocating, then releasing, the memory needed to save 
the result takes as much time and effort as doing a really simple calculation 
200 times, but a really complicated calculation only 5 times.

So sometimes it’s better to chose one option, other times it’s better to choose 
the other.  Since you are fiendishly passionate about execution speed you 
diligently write both routines into your program and have it figure out which 
one to use.  So now you have the best of both worlds: your program always picks 
whichever the best solution is.

And then you find out that the test your program has to do to figure out which 
the best solution would be takes as much time and memory as doing a simple 
calculation 50 times.  So a lot of the time it’s better just to make an 
assumption.

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

Re: [sqlite] Does prepare do arithmetic?

[Richard Hipp]

On 5/29/17, Jeffrey Mattox  wrote:
> Why does a complex computation (but still consisting of only constants) make
> a difference as to whether the computation is performed once or many times?
> What's the dividing line between "simple" and "complex"?

The dividing line is the number of opcode need to implement the
computation.  If the number is 1, then the computation is "simple".
Two are more opcodes and the computation is "complex".

The query planner factors all but the very last opcode or the
expression out of the inner loop.  The very last opcode of the
expression is retained in the inner loop in order to refresh the
result.

-- 
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] Does prepare do arithmetic?

[Jeffrey Mattox]

Why does a complex computation (but still consisting of only constants) make a 
difference as to whether the computation is performed once or many times?  
What's the dividing line between "simple" and "complex"?

Jeff


> On May 29, 2017, at 8:51 AM, Richard Hipp  wrote:
> 
> It is true that SQLite could compute 2+2 and store the result then
> copy the result into the result register for each result row, but
> doing the copy is not measurably faster than simply redoing the 2+2
> computation.
> 
> You will notice that in a more complex computation like:
> 
>SELECT 2*15-5+11/5<<3*1 FROM tbl
> 
> That the "2*15-5+11/5" and "3*1" subexpressions are computed just
> once, and only the final "<<" operator is repeated for each row.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does prepare do arithmetic?

[x]

I’m fairly sure the gains won’t be worth the effort RS as any arithmetic will 
mostly be in the WHERE clause. Thanks.

From: R Smith
Sent: 29 May 2017 15:18
To: 
sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Does prepare do arithmetic?


On 2017/05/29 4:05 PM, x wrote:
> Thanks Richard. I can’t claim to fully understand the part about the app 
> calling sqlit3_column_text but I’m reassured that leaving the arithmetic to 
> sqlite is the way to go.

Actually what Richard is saying is that you SHOULD do the arithmetic
yourself when you can, because you can boldly make assumptions about the
code that you write, but he, or the SQLite engine to be precise, cannot
be so bold, it may not be accurate in accepting that nothing about the
database will change from one evaluation to the next, so it has to
recompute - but you may optimise that computation out because you KNOW
your system and you KNOW you won't be changing column collations or type
affinities etc. in between steps.

He is also saying that the effort is probably not worth all that many
CPU cycles, unless the computation is really heavy, and even then... So
before you embark on that road, make sure the gains are well worth the
effort.


>
> From: Richard Hipp
> Sent: 29 May 2017 14:52
> To: SQLite mailing list
> Subject: Re: [sqlite] Does prepare do arithmetic?
>
> On 5/29/17, x  wrote:
>> Thanks Clemens, that clears that up.
>>
>> I’m still left wondering though why it calculates 2+2 every step in
>>
>> ‘select 2+2 from Tbl;’
>>
> All result values must be recomputed on every step because the
> application can change the value by (for example) calling
> sqlite3_column_text and thus causing the integer result to be
> converted into a string result.  The result needs to be reset back to
> an integer for the next row.
>
> It is true that SQLite could compute 2+2 and store the result then
> copy the result into the result register for each result row, but
> doing the copy is not measurably faster than simply redoing the 2+2
> computation.
>
> You will notice that in a more complex computation like:
>
>  SELECT 2*15-5+11/5<<3*1 FROM tbl
>
> That the "2*15-5+11/5" and "3*1" subexpressions are computed just
> once, and only the final "<<" operator is repeated for each row.
>
> --
> 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-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] Does prepare do arithmetic?

[R Smith]

On 2017/05/29 4:05 PM, x wrote:

Thanks Richard. I can’t claim to fully understand the part about the app 
calling sqlit3_column_text but I’m reassured that leaving the arithmetic to 
sqlite is the way to go.


Actually what Richard is saying is that you SHOULD do the arithmetic 
yourself when you can, because you can boldly make assumptions about the 
code that you write, but he, or the SQLite engine to be precise, cannot 
be so bold, it may not be accurate in accepting that nothing about the 
database will change from one evaluation to the next, so it has to 
recompute - but you may optimise that computation out because you KNOW 
your system and you KNOW you won't be changing column collations or type 
affinities etc. in between steps.


He is also saying that the effort is probably not worth all that many 
CPU cycles, unless the computation is really heavy, and even then... So 
before you embark on that road, make sure the gains are well worth the 
effort.





From: Richard Hipp
Sent: 29 May 2017 14:52
To: SQLite mailing list
Subject: Re: [sqlite] Does prepare do arithmetic?

On 5/29/17, x  wrote:

Thanks Clemens, that clears that up.

I’m still left wondering though why it calculates 2+2 every step in

‘select 2+2 from Tbl;’


All result values must be recomputed on every step because the
application can change the value by (for example) calling
sqlite3_column_text and thus causing the integer result to be
converted into a string result.  The result needs to be reset back to
an integer for the next row.

It is true that SQLite could compute 2+2 and store the result then
copy the result into the result register for each result row, but
doing the copy is not measurably faster than simply redoing the 2+2
computation.

You will notice that in a more complex computation like:

 SELECT 2*15-5+11/5<<3*1 FROM tbl

That the "2*15-5+11/5" and "3*1" subexpressions are computed just
once, and only the final "<<" operator is repeated for each row.

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

Re: [sqlite] Does prepare do arithmetic?

[x]

Thanks Richard. I can’t claim to fully understand the part about the app 
calling sqlit3_column_text but I’m reassured that leaving the arithmetic to 
sqlite is the way to go.

From: Richard Hipp
Sent: 29 May 2017 14:52
To: SQLite mailing list
Subject: Re: [sqlite] Does prepare do arithmetic?

On 5/29/17, x  wrote:
> Thanks Clemens, that clears that up.
>
> I’m still left wondering though why it calculates 2+2 every step in
>
> ‘select 2+2 from Tbl;’
>

All result values must be recomputed on every step because the
application can change the value by (for example) calling
sqlite3_column_text and thus causing the integer result to be
converted into a string result.  The result needs to be reset back to
an integer for the next row.

It is true that SQLite could compute 2+2 and store the result then
copy the result into the result register for each result row, but
doing the copy is not measurably faster than simply redoing the 2+2
computation.

You will notice that in a more complex computation like:

SELECT 2*15-5+11/5<<3*1 FROM tbl

That the "2*15-5+11/5" and "3*1" subexpressions are computed just
once, and only the final "<<" operator is repeated for each row.

--
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] Does prepare do arithmetic?

[Richard Hipp]

On 5/29/17, x  wrote:
> Thanks Clemens, that clears that up.
>
> I’m still left wondering though why it calculates 2+2 every step in
>
> ‘select 2+2 from Tbl;’
>

All result values must be recomputed on every step because the
application can change the value by (for example) calling
sqlite3_column_text and thus causing the integer result to be
converted into a string result.  The result needs to be reset back to
an integer for the next row.

It is true that SQLite could compute 2+2 and store the result then
copy the result into the result register for each result row, but
doing the copy is not measurably faster than simply redoing the 2+2
computation.

You will notice that in a more complex computation like:

SELECT 2*15-5+11/5<<3*1 FROM tbl

That the "2*15-5+11/5" and "3*1" subexpressions are computed just
once, and only the final "<<" operator is repeated for each row.

-- 
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] Does prepare do arithmetic?

[Hick Gunter]

The expression is calculated as given in each case. The difference being that a 
constant constraint is recognized in the query planner (and thus calculated 
once with the result being saved), whereas a constant expression as a return 
value is not.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Montag, 29. Mai 2017 14:58
An: SQLite mailing list 
Betreff: Re: [sqlite] Does prepare do arithmetic?

Thanks Clemens, that clears that up.

I’m still left wondering though why it calculates 2+2 every step in

‘select 2+2 from Tbl;’

but only once in

‘select * from Tbl where Col=2+2;’

I’m writing code that makes certain changes to the SQL before it submits it to 
sqlite3_prepare. Initially I assumed prepare took care of any arithmetic but 
now I’m wondering if I my code should do so before submitting to prepare.

From: Clemens Ladisch
Sent: 29 May 2017 12:54
To: 
sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Does prepare do arithmetic?

x wrote:
> The explain for
>
> Select * from Tbl where Col = (select min(OtherCol) from OtherTbl);
>
> I’m having problems with.

EXPLAIN Select * from Tbl where Col = (select min(OtherCol) from OtherTbl);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 23000  Start at 23
1 OpenRead   0 2 0 2  00  root=2 iDb=0; tbl
2 Rewind 0 22000
3   Column 0 0 100  r[1]=tbl.col
4   Once   0 17000
5   Null   0 3 300  r[3..3]=NULL; Init 
subquery result
6   Integer1 4 000  r[4]=1; LIMIT 
counter
7   Null   0 5 600  r[5..6]=NULL
8   OpenRead   1 3 0 1  00  root=3 iDb=0; 
OtherTbl
9   Rewind 1 14000
10Column 1 0 700  
r[7]=OtherTbl.OtherCol
11CollSeq0 0 0 (BINARY)   00
12AggStep0   0 7 5 min(1) 01  accum=r[5] 
step(r[7])
13  Next   1 10001
14  AggFinal   5 1 0 min(1) 00  accum=r[5] N=1
15  Copy   5 3 000  r[3]=r[5]
16  DecrJumpZero   4 17000  if (--r[4])==0 goto 
17
17  Ne 3 211 (BINARY)   51  if r[1]!=r[3] goto 
21
18  Column 0 0 800  r[8]=tbl.col
19  Column 0 1 900  r[9]=tbl.y
20  ResultRow  8 2 000  output=r[8..9]
21Next   0 3 001
22Halt   0 0 000
23Transaction0 0 2 0  01  usesStmtJournal=0
24Goto   0 1 000

There are two nested loops; the outer one scans Tbl, the inner one scans 
OtherTbl.  However, the database has determined that the result of the inner 
loop does not depend on the current row in the outer loop, so the inner loop is 
executed only once (the Once opcode in line 4 jumps to line 17 when it is 
encountered the second time).

> I’m even having problems with explain query plan (shown below) for that one.
>
> Scan table Tbl
> Execute scalar subquery 1
> Scan table OtherTbl
>
> Why would it scan Tbl first?

It doesn't; it _begins_ scanning Tbl first.  If Tbl does not happen to have any 
rows, the inner loop would never by executed.

With nesting like this, it would be hard to define which (sub)query is executed 
first.  (And then there are queries that use coroutines ...)


Regards,
Clemens


> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.

This e-mail contains public information intended for any subscriber of this 
mailing list and for anybody else who bothers to read it; it will be copied, 
disclosed and distributed to the public.  If you think you are not the intended 
recipient, please commit suicide immediately.
These terms apply also to any e-mails quoted in, referenced from, or answering 
this e-mail, and supersede any 

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

[Thomas Flemming]

Ok, here is a sample to try these queries:

http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
(825mb, 12 mio records)

Before I change my app-logic to do the styleid-query on the app-side, I would 
like to know, if there might be a chance to get this fast on the sqlite-side.



very fast, 77 records, 49ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
   AND x0 < 30.46203 AND  x1 > 30.00074766
   AND 18 BETWEEN z0 AND z1
   AND Pois_bb.Id = Pois.Id;


fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);


very slow: 55 records, 3ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
   AND x0 < 30.46203 AND  x1 > 30.00074766
   AND 18 BETWEEN z0 AND z1
   AND Pois_bb.Id = Pois.Id
   AND styleid IN (9,48,73,200,142,31,219);

Thanks,
Tom

Am 27.05.2017 um 12:04 schrieb Thomas Flemming:

Hi,

I have a table Pois with points of interest (geogr. coordinate, label, 
styleid) where I do regional querys using a rtree-index:


SELECT Pois.* FROM Pois_bb, Pois WHERE  y0 < -14.8600 AND  y1 > -15.12862
   AND   x0 < 30.46203 AND  x1 > 30.00074766
   AND   18 BETWEEN z0 AND z1
   AND   Pois_bb.Id = Pois.Id
Thats very fast, 50ms.

The problem is, when I add a second condition to get certain poi-types only in 
the area:


AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762

The query becomes really slow, 800ms.
There is of course also an index on styleid.

I also realized, just this query:

SELECT * FROM Pois WHERE styleid IN 
1351,1362,1371,1374,1376,1542,1595,1597,1643,1762


is also slow for the first call. The second call is fast.

(Using SQLite Expert Professional 3.5 for testing).

Any ideas, how to speed this up?

Thanks
Tom




--
/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does prepare do arithmetic?

[x]

Thanks Clemens, that clears that up.

I’m still left wondering though why it calculates 2+2 every step in

‘select 2+2 from Tbl;’

but only once in

‘select * from Tbl where Col=2+2;’

I’m writing code that makes certain changes to the SQL before it submits it to 
sqlite3_prepare. Initially I assumed prepare took care of any arithmetic but 
now I’m wondering if I my code should do so before submitting to prepare.

From: Clemens Ladisch
Sent: 29 May 2017 12:54
To: 
sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Does prepare do arithmetic?

x wrote:
> The explain for
>
> Select * from Tbl where Col = (select min(OtherCol) from OtherTbl);
>
> I’m having problems with.

EXPLAIN Select * from Tbl where Col = (select min(OtherCol) from OtherTbl);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 23000  Start at 23
1 OpenRead   0 2 0 2  00  root=2 iDb=0; tbl
2 Rewind 0 22000
3   Column 0 0 100  r[1]=tbl.col
4   Once   0 17000
5   Null   0 3 300  r[3..3]=NULL; Init 
subquery result
6   Integer1 4 000  r[4]=1; LIMIT 
counter
7   Null   0 5 600  r[5..6]=NULL
8   OpenRead   1 3 0 1  00  root=3 iDb=0; 
OtherTbl
9   Rewind 1 14000
10Column 1 0 700  
r[7]=OtherTbl.OtherCol
11CollSeq0 0 0 (BINARY)   00
12AggStep0   0 7 5 min(1) 01  accum=r[5] 
step(r[7])
13  Next   1 10001
14  AggFinal   5 1 0 min(1) 00  accum=r[5] N=1
15  Copy   5 3 000  r[3]=r[5]
16  DecrJumpZero   4 17000  if (--r[4])==0 goto 
17
17  Ne 3 211 (BINARY)   51  if r[1]!=r[3] goto 
21
18  Column 0 0 800  r[8]=tbl.col
19  Column 0 1 900  r[9]=tbl.y
20  ResultRow  8 2 000  output=r[8..9]
21Next   0 3 001
22Halt   0 0 000
23Transaction0 0 2 0  01  usesStmtJournal=0
24Goto   0 1 000

There are two nested loops; the outer one scans Tbl, the inner one
scans OtherTbl.  However, the database has determined that the result of
the inner loop does not depend on the current row in the outer loop, so
the inner loop is executed only once (the Once opcode in line 4 jumps to
line 17 when it is encountered the second time).

> I’m even having problems with explain query plan (shown below) for that one.
>
> Scan table Tbl
> Execute scalar subquery 1
> Scan table OtherTbl
>
> Why would it scan Tbl first?

It doesn't; it _begins_ scanning Tbl first.  If Tbl does not happen to
have any rows, the inner loop would never by executed.

With nesting like this, it would be hard to define which (sub)query is
executed first.  (And then there are queries that use coroutines ...)


Regards,
Clemens


> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.

This e-mail contains public information intended for any subscriber of
this mailing list and for anybody else who bothers to read it; it will
be copied, disclosed and distributed to the public.  If you think you
are not the intended recipient, please commit suicide immediately.
These terms apply also to any e-mails quoted in, referenced from, or
answering this e-mail, and supersede any confidentiality notices in
those e-mails.  Additionally, confidentiality notices in those e-mails
will incur legal processing fees of $42 per line; you have agreed to
this by reading this confidentiality notice.
___
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

Re: [sqlite] Does prepare do arithmetic?

[Clemens Ladisch]

x wrote:
> The explain for
>
> Select * from Tbl where Col = (select min(OtherCol) from OtherTbl);
>
> I’m having problems with.

EXPLAIN Select * from Tbl where Col = (select min(OtherCol) from OtherTbl);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 23000  Start at 23
1 OpenRead   0 2 0 2  00  root=2 iDb=0; tbl
2 Rewind 0 22000
3   Column 0 0 100  r[1]=tbl.col
4   Once   0 17000
5   Null   0 3 300  r[3..3]=NULL; Init 
subquery result
6   Integer1 4 000  r[4]=1; LIMIT 
counter
7   Null   0 5 600  r[5..6]=NULL
8   OpenRead   1 3 0 1  00  root=3 iDb=0; 
OtherTbl
9   Rewind 1 14000
10Column 1 0 700  
r[7]=OtherTbl.OtherCol
11CollSeq0 0 0 (BINARY)   00
12AggStep0   0 7 5 min(1) 01  accum=r[5] 
step(r[7])
13  Next   1 10001
14  AggFinal   5 1 0 min(1) 00  accum=r[5] N=1
15  Copy   5 3 000  r[3]=r[5]
16  DecrJumpZero   4 17000  if (--r[4])==0 goto 
17
17  Ne 3 211 (BINARY)   51  if r[1]!=r[3] goto 
21
18  Column 0 0 800  r[8]=tbl.col
19  Column 0 1 900  r[9]=tbl.y
20  ResultRow  8 2 000  output=r[8..9]
21Next   0 3 001
22Halt   0 0 000
23Transaction0 0 2 0  01  usesStmtJournal=0
24Goto   0 1 000

There are two nested loops; the outer one scans Tbl, the inner one
scans OtherTbl.  However, the database has determined that the result of
the inner loop does not depend on the current row in the outer loop, so
the inner loop is executed only once (the Once opcode in line 4 jumps to
line 17 when it is encountered the second time).

> I’m even having problems with explain query plan (shown below) for that one.
>
> Scan table Tbl
> Execute scalar subquery 1
> Scan table OtherTbl
>
> Why would it scan Tbl first?

It doesn't; it _begins_ scanning Tbl first.  If Tbl does not happen to
have any rows, the inner loop would never by executed.

With nesting like this, it would be hard to define which (sub)query is
executed first.  (And then there are queries that use coroutines ...)


Regards,
Clemens


> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.

This e-mail contains public information intended for any subscriber of
this mailing list and for anybody else who bothers to read it; it will
be copied, disclosed and distributed to the public.  If you think you
are not the intended recipient, please commit suicide immediately.
These terms apply also to any e-mails quoted in, referenced from, or
answering this e-mail, and supersede any confidentiality notices in
those e-mails.  Additionally, confidentiality notices in those e-mails
will incur legal processing fees of $42 per line; you have agreed to
this by reading this confidentiality notice.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does prepare do arithmetic?

[x]

Thanks for the reply Gunter. The reason I added “from Tbl” was so there would 
be more than 1 step involved. The explains require a fair knowledge of the 
opcodes for more complex queries so I’m still not totally sure as to the answer 
to my question.

Assuming I’m reading the explains properly, the explain for

select 2+2 from Tbl;

suggests it is calculated at every step whereas the explain for

Select * from Tbl where Col=2+2;

seems to suggest the 2+2 is calculated once and then Tbl is scanned for rows 
where Col=4

The explain for

Select * from Tbl where Col = (select min(OtherCol) from OtherTbl);

I’m having problems with. I’m even having problems with explain query plan 
(shown below) for that one.

Scan table Tbl
Execute scalar subquery 1
Scan table OtherTbl

Why would it scan Tbl first?





From: Tom
Sent: 29 May 2017 11:07
To: tam118118
Subject: FW: [sqlite] Does prepare do arithmetic?



From: Hick Gunter
Sent: 29 May 2017 08:34
To: 'SQLite mailing list'
Subject: Re: [sqlite] Does prepare do arithmetic?

Try for yourself.

.mode explain
explain select 2 +2;
explain select 4;

BTW: The "from Tbl" clause will only affect the number of result rows (1 for 
every row in Tbl).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von xTom Byars
Gesendet: Montag, 29. Mai 2017 09:23
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] Does prepare do arithmetic?

Please satisfy a curiosity for me

If I prepare

Select 2 + 2 from Tbl;

Will the prepared code be the same as

Select 4 from Tbl;

Or is the 2+2 calculated at every step?

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
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] SELECT WHERE with RTREE and second condition slow

[Thomas Flemming]

Morning,

> Does ANALYZE gather statistical data about rtree virtual tables? I seem to
ANALYZE doesn't help.
I'm busy preparing and uploading a sample-db, then it might be easier to 
figure that out.

Tom

Am 28.05.2017 um 11:01 schrieb Wolfgang Enzinger:

Am Sat, 27 May 2017 19:20:00 -0400 schrieb Richard Hipp:


On 5/27/17, Thomas Flemming  wrote:

Hi,

I have a table Pois with points of interest (geogr. coordinate, label,
styleid) where I do regional querys using a rtree-index:

SELECT Pois.* FROM Pois_bb, Pois WHERE  y0 < -14.8600 AND  y1 > -15.12862
AND   x0 < 30.46203 AND  x1 > 30.00074766
AND   18 BETWEEN z0 AND z1
AND   Pois_bb.Id = Pois.Id
Thats very fast, 50ms.

The problem is, when I add a second condition to get certain poi-types only
in the area:

AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762

The query becomes really slow, 800ms.
There is of course also an index on styleid.

I also realized, just this query:

SELECT * FROM Pois WHERE styleid IN
1351,1362,1371,1374,1376,1542,1595,1597,1643,1762

is also slow for the first call. The second call is fast.

(Using SQLite Expert Professional 3.5 for testing).

Any ideas, how to speed this up?


Have you tried running ANALYZE on your database?


Does ANALYZE gather statistical data about rtree virtual tables? I seem to
remember that this is not the case.

I don't have an appropriate database at hand right now since I'm busy
currently with a different project, but I seem to remember that I observed
something similar: queries with an rtree table involved seem to *always*
prefer the spatial index over any other index, even if the clipping
embraces the complete extent of graphical data. This is unefficient
especially when the "related table" (that is, another table that is linked
to the rtree virtual table by an object ID) holds much more selective (and
indexed) criteria.

This is just out of the top of my head; anyway, if you don't hear back from
Thomas, please let me know, and I'll gladly provide more details (not
before Wednesday though).

And thanks again for then LEFT JOIN VIEW optimization - sorry that it
caused so much trouble ... I was completely unaware of the many pitfalls
this topic involves.

Wolfgang

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



--
/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CLI option to NOT autocheckpoint WAL databases

[Rowan Worth]

On 29 May 2017 at 14:46, Clemens Ladisch  wrote:

> Howard Kapustein wrote:
> > I'm effectively looking for a -readonly option, or how to achieve that
> > net effect
>
> sqlite3 "file:test.db?mode=ro"
>
> But if you want to be really sure, set the file permissions to disallow
> writes.  (You have to except the -shm file, and this is not officially
> supported, and it appears there is a delay due to a lock timeout
> somewhere.)
>

Note that there are some circumstances where sqlite _must_ be able write to
the database even if you are only interested in reading data. Eg. in
rollback journal mode, if a program crashes or you lose power partway
through COMMIT the database is likely left in an inconsistent state. Sqlite
resolves this next time the database is accessed by using information in
the rollback journal to arrive back at a consistent state, which of course
involves writing to the database to undo the partial COMMIT.

It seems like a similar situation might arise in WAL mode if there's a
crash/power failure during a checkpoint? But I'm not very familiar with WAL
so forgive me if this is an irrelevant tangent.

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

Re: [sqlite] Does prepare do arithmetic?

[Hick Gunter]

Try for yourself.

.mode explain
explain select 2 +2;
explain select 4;

BTW: The "from Tbl" clause will only affect the number of result rows (1 for 
every row in Tbl).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von xTom Byars
Gesendet: Montag, 29. Mai 2017 09:23
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] Does prepare do arithmetic?

Please satisfy a curiosity for me

If I prepare

Select 2 + 2 from Tbl;

Will the prepared code be the same as

Select 4 from Tbl;

Or is the 2+2 calculated at every step?

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

[sqlite] Does prepare do arithmetic?

[xTom Byars]

Please satisfy a curiosity for me

If I prepare

Select 2 + 2 from Tbl;

Will the prepared code be the same as

Select 4 from Tbl;

Or is the 2+2 calculated at every step?

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

Re: [sqlite] No check for invalid constraints

[Hick Gunter]

There is no "underscore notation" for multiword constraints. "NOT_NULL" is not 
"NOT NULL" and "PRIMARY KEY" is not "PRIMARY_KEY". As long as whatever you 
provide can be split up into tokens in a way that matches the grammar there 
will be no syntax error. It will just not be matching what you think you 
specified, but didn't.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Aflah Bhari
Gesendet: Samstag, 27. Mai 2017 15:48
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] No check for invalid constraints

Hi there,

I'm using SQLITE version 3.16.0 on macOS Sierra 10.12.4. I'm experiencing a 
unique bug where if I put in an primary key constraint with underscores then 
the primary key does not auto increment when I insert values.

*For example:*

CREATE TABLE sensor_status(status_id INTEGER *PRIMARY_KEY*, status text 
NOT_NULL UNIQUE); insert into sensor_status(status) VALUES("ONLINE"); select * 
from sensor_status;

status_id   status

--  --

ONLINE


*Versus:*

CREATE TABLE sensor_status(status_id INTEGER *PRIMARY KEY*, status text 
NOT_NULL UNIQUE); insert into sensor_status(status) VALUES("ONLINE"); select * 
from sensor_status;

status_id   status

--  --

1   ONLINE

I'm not sure what the implications of this are on other constraints such as 
NOT_NULL which also does not seem to work with the underscore notation.

EDIT: I've just realised that there is no check in place for whether the 
constraint is typed correctly.

I'm not sure now if this is necessarily a bug or a feature that hasn't been 
implemented (intentionally or unintentionally). I'm new to sqlite so I 
apologise if I'm wasting anyone's time by reporting this :(

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] CLI option to NOT autocheckpoint WAL databases

[Clemens Ladisch]

Howard Kapustein wrote:
> Is it possible to use the CLI to read a WAL database and exit without
> modifying the database?

The checkpoint-on-close feature is not affected by
PRAGMA wal_autocheckpoint.  The NO_CKPT_ON_CLOSE DB config flag is the
only mechanism to prevent it from inside the connection.

However, the comment in sqlite3WalClose() says:

/* If an EXCLUSIVE lock can be obtained on the database file (using the
** ordinary, rollback-mode locking methods, this guarantees that the
** connection associated with this log file is the only connection to
** the database. In this case checkpoint the database and unlink both
** the wal and wal-index files.

Well, using a second instance of the CLI to take a lock just kicks the
can down the road.

And I guess you don't simply want to kill the CLI.

> I'm effectively looking for a -readonly option, or how to achieve that
> net effect

sqlite3 "file:test.db?mode=ro"

But if you want to be really sure, set the file permissions to disallow
writes.  (You have to except the -shm file, and this is not officially
supported, and it appears there is a delay due to a lock timeout
somewhere.)


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