Re: [sqlite] PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?

[Clemens Ladisch]

Howard Kapustein wrote:
>   /* Try to truncate the WAL file to zero bytes if the checkpoint
>   ** completed and fsynced (rc==SQLITE_OK) and we are in persistent
>   ** WAL mode (bPersist) and if the PRAGMA journal_size_limit is a
>   ** non-negative value (pWal->mxWalSize>=0).  Note that we truncate
>   ** to zero bytes as truncating to the journal_size_limit might
>   ** leave a corrupt WAL file on disk. */
>   walLimitSize(pWal, 0);
>
> That last comment "Note that we truncate..." is interesting. When
> would this corrupt WAL rear its head?

A 'normal' checkpoint would adjust the WAL file header to record that
the data has been checkpointed and is no longer valid.


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

Re: [sqlite] Intel 17

[Clemens Ladisch]

Dominique Devienne wrote:
> sqlite3.c(17654): error #265: floating-point operation result is out of range

Could this error be reduced to a warning?

> // around line 17644
> - result = 1e308*1e308*s; /* Infinity */
> + result = 1e308*(1e308*s); /* Infinity */

C99 7.12 () says:
| 4 The macro INFINITY expands to a constant expression of type float
|   representing positive or unsigned infinity, if available; else to
|   a positive constant of type float that overflows at translation
|   time.*
|   * In this case, using INFINITY will violate the constraint in 6.4.4
| and thus require a diagnostic.

So I guess an "#ifdef INFINITY" should be added.

This macro was introduced in C99 (as was strtod("INF")), so we still
need a fallback.  And if we don't want to hack the IEEE float bit
pattern, the only choices are overflow or "1.0/0.0".


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

Re: [sqlite] FW: PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?

[Dan Kennedy]

On 09/12/2017 09:24 AM, Joe Mistachkin wrote:

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Howard Kapustein
Sent: Monday, September 11, 2017 2:53 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] PRAGMA journal_size_limit prevents -wal growth or only
size outside a transaction?

Looking through the source a bit I see sqlite3_busy_timeout is a no-fail
operation (aside from misuse) so sqlite3_busy_timeout(0);
SQLITE_CHECKPOINT_TRUNCATEsqlite3_busy_timeout(n); does what I need. Thanks
all.

- Howard

P.S. sqlite3_close intentionally doesn't respect journal_size_limit. From
sqlite3WalClose

 }else if( pWal->mxWalSize>=0 ){
   /* Try to truncate the WAL file to zero bytes if the checkpoint
   ** completed and fsynced (rc==SQLITE_OK) and we are in persistent
   ** WAL mode (bPersist) and if the PRAGMA journal_size_limit is a
   ** non-negative value (pWal->mxWalSize>=0).  Note that we truncate
   ** to zero bytes as truncating to the journal_size_limit might
   ** leave a corrupt WAL file on disk. */
   walLimitSize(pWal, 0);
 }

That last comment "Note that we truncate..." is interesting. When would this
corrupt WAL rear its head?


Following recovery.

A wal file contains a series of transactions, where each transaction 
consists of the modified versions of the database pages. Say a wal file 
contains transactions 1, 2 and 3. Say you do a checkpoint, which copies 
pages from all three transactions into the database file and then exit 
leaving the wal file on disk. The next process that starts up reads the 
entire wal file. If it then does a checkpoint, that's no problem - as 
it's only overwriting the same database pages with the same data as the 
first checkpointer did.


It would also be safe if our first checkpointer truncated the wal file 
to zero bytes in size. The next client would start up and read no data 
from the wal file, which is fine as all the data was already copied into 
the db anyway.


But, instead of just exiting abruptly, say our first client truncated 
the wal file so that it contains just transactions 1 and 2. Then another 
client starts up, reads transactions 1 and 2 from the wal file and 
eventually checkpoints them. In this case, if transactions 1 and 2 
partially overlap (write some of the same pages) with transaction 3, the 
db will become corrupted. Just as if only some of the pages in 
transaction 3 had been written out in the first place.


This is why the "journal_file_size" limit is applied to the wal file by 
the first writer, not the checkpointer. Only after the writer has 
written a new wal file header can the wal file be safely truncated to 
the journal_size_limit. Otherwise, an unlucky crash might leave us in 
the situation described above, where a only a prefix of the wal file is 
read following recovery.


Dan.



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

Re: [sqlite] Common subexpression optimization of deterministic functions

[raypoker79]

Stop,,I'm not subscribed 


Sent via the Samsung Galaxy S7, an AT 4G LTE smartphone
 Original message From: Warren Young  Date: 
9/12/17  6:19 PM  (GMT-05:00) To: SQLite mailing list 
 Subject: Re: [sqlite] Common 
subexpression optimization of deterministic
  functions 
On Sep 12, 2017, at 2:36 PM, Jens Alfke  wrote:
> 
> On Sep 12, 2017, at 12:58 PM, Warren Young  wrote:
>> 
>> Could it be *extended* to mean what you want?  Of course, but that means 
>> you’re asking for a feature, not reporting a bug.
> 
> I never claimed to be reporting a bug!

I read your reference to an older version as implying that you thought this was 
a regression in functionality.  That is, that you thought this change first 
appeared in 3.19.
___
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 list spam from raypoker79

[Richard Hipp]

Thanks for reporting this, Darren.  I didn't see it because those
emails went straight into my spam folder.

I don't know how they got through, because raypoke...@yahoo.com is not
a subscriber, and new subscription requests are moderated.

If anybody else is seeing these emails, and/or has advice on how I can
stop them, please send me private email.

On 9/12/17, Darren Duncan  wrote:
> Richard, in case you didn't notice yet, there's an insane-or-troll-sounding
> list
> subscriber raypoker79 who has sent 3 messages in the last 90 minutes, as
> replies
> to normal threads, asking to be unsubscribed and talking about credit cards
> etc;


-- 
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 list spam from raypoker79

[Simon Slavin]

On 13 Sep 2017, at 12:55am, Richard Hipp  wrote:

> If anybody else is seeing these emails, and/or has advice on how I can
> stop them, please send me private email.

No messages from that address reached me.  My spam/junk/trash system has full 
logging and that email address does not appear.

There are traces of its use on the web, though they don’t seem to involve 
trolling, just not the type of person to subscribe to this mailing list.

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

Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs

[Yue]

Hello,
Thanks for your advice!
It works pretty well when I dropped the rowid and set the other field as
primary key.
I also enable wal mode at the cost of a little bit read performance.
Overall, it works really well now.

Really appreciate your help.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite list spam from raypoker79

[Darko Volaric]

Is it possible that the spammer got a hold of the subscriber list?


> On Sep 13, 2017, at 1:55 AM, Richard Hipp  wrote:
> 
> Thanks for reporting this, Darren.  I didn't see it because those
> emails went straight into my spam folder.
> 
> I don't know how they got through, because raypoke...@yahoo.com is not
> a subscriber, and new subscription requests are moderated.
> 
> If anybody else is seeing these emails, and/or has advice on how I can
> stop them, please send me private email.
> 
> On 9/12/17, Darren Duncan  wrote:
>> Richard, in case you didn't notice yet, there's an insane-or-troll-sounding
>> list
>> subscriber raypoker79 who has sent 3 messages in the last 90 minutes, as
>> replies
>> to normal threads, asking to be unsubscribed and talking about credit cards
>> etc;
> 
> 
> -- 
> 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] Seeking advice on improving batch sql update performance on zipvfs

[Yue]

Thanks for the advice!

I could never thought about this. Really learnt a lot.

It's a customized Android phone type device.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite list spam from raypoker79

[Darren Duncan]

On 2017-09-12 6:56 PM, Darko Volaric wrote:

Is it possible that the spammer got a hold of the subscriber list?


My quick analysis of mail headers showed that the messages passed through the 
mailing list, or at least 3-4 mail relays in a row closest to my end were the 
same as for a legitimate message.  Also, the spam messages used and replied to 
messages sent only minutes before.  There could be a question of whether the 
mailing list validates that the sender of a message actually is the sender, or 
whether they let them in just because they say they are someone on the list. -- 
Darren Duncan


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

Re: [sqlite] Common subexpression optimization of deterministic functions

[Warren Young]

On Sep 12, 2017, at 2:36 PM, Jens Alfke  wrote:
> 
> On Sep 12, 2017, at 12:58 PM, Warren Young  wrote:
>> 
>> Could it be *extended* to mean what you want?  Of course, but that means 
>> you’re asking for a feature, not reporting a bug.
> 
> I never claimed to be reporting a bug!

I read your reference to an older version as implying that you thought this was 
a regression in functionality.  That is, that you thought this change first 
appeared in 3.19.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite list spam from raypoker79

[Jim Borden]

For what it is worth, those messages reached my inbox twice on two different 
threads at least.

Jim Borden
Software Engineer

jim.bor...@couchbase.com

On 2017/09/13 9:02, "sqlite-users on behalf of Simon Slavin" 
 wrote:



On 13 Sep 2017, at 12:55am, Richard Hipp  wrote:

> If anybody else is seeing these emails, and/or has advice on how I can
> stop them, please send me private email.

No messages from that address reached me.  My spam/junk/trash system has 
full logging and that email address does not appear.

There are traces of its use on the web, though they don’t seem to involve 
trolling, just not the type of person to subscribe to this mailing list.

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] Intel 17

[Clemens Ladisch]

Richard Hipp wrote:
> Please try the latest trunk version of SQLite and let me know if it
> works for you.

I don't have the Intel compiler, but the sign is missing:

  SELECT CAST('-1e359' AS NUMBER), CAST('-1e360' AS NUMBER);
  -Inf|Inf


I take everything back and state the opposite: I guess an
"#ifndef INFINITY" should be added to set it if it does not exist.

And while we're at it: the "#include " at the top of util.c is
guarded with HAVE_ISNAN, which is implied by C99, but now misleading.


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

Re: [sqlite] Intel 17

[Richard Hipp]

On 9/12/17, Clemens Ladisch  wrote:
> Richard Hipp wrote:
>> Please try the latest trunk version of SQLite and let me know if it
>> works for you.
>
> I don't have the Intel compiler, but the sign is missing:
>
>   SELECT CAST('-1e359' AS NUMBER), CAST('-1e360' AS NUMBER);
>   -Inf|Inf
>

Thanks.  Should be fixed now.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.20, are you sure?

[Richard Hipp]

On 9/12/17, p...@arbolone.ca  wrote:
> I have a file named SQLite3.dll, but I don't know which sqlite version it
> is. Any help on how to finding that out?TIA

Right-click on the file.  Select "Properties"

-- 
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] Intel 17

[Dominique Devienne]

On Tue, Sep 12, 2017 at 8:47 AM, Clemens Ladisch  wrote:

> Dominique Devienne wrote:
> > sqlite3.c(17654): error #265: floating-point operation result is out of
> range
>
> Could this error be reduced to a warning?
>

Probably. We typically compile with a strict warning-as-error setting.


> > // around line 17644
> > - result = 1e308*1e308*s; /* Infinity */
> > + result = 1e308*(1e308*s); /* Infinity */
>
> C99 7.12 () says:
> | 4 The macro INFINITY expands to a constant expression of type float
> |   representing positive or unsigned infinity, if available; else to
> |   a positive constant of type float that overflows at translation
> |   time.*
> |   * In this case, using INFINITY will violate the constraint in 6.4.4
> | and thus require a diagnostic.
>
> So I guess an "#ifdef INFINITY" should be added.
>
> This macro was introduced in C99 (as was strtod("INF")), so we still
> need a fallback.  And if we don't want to hack the IEEE float bit
> pattern, the only choices are overflow or "1.0/0.0".
>

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

[sqlite] SQLite 3.20, are you sure?

[papa]

I have a file named SQLite3.dll, but I don't know which sqlite version it is. 
Any help on how to finding that out?TIA
ArbolOne.caUsing Fire Fox and Thunderbird.ArbolOne is composed of students and 
volunteers dedicated to providing free services to charitable 
organizations.ArbolOne on Java Development in progress ⏳
-- Original message--From: Jens AlfkeDate: Mon, Sep 11, 2017 7:05 PMTo: 
SQLite mailing list;Subject:[sqlite] Table-valued function crashing in SQLite 
3.20
We have a virtual table serving as a table-valued function, which has worked 
fine up through SQLite 3.19, but now in 3.20.1 is crashing whenever it’s used. 
I know there were changes in 3.20 relating to native functions taking pointers, 
but this doesn’t seem to be related to that. Were there any other 
possibly-breaking changes to the way virtual tables work? I didn’t see any in 
the release notes.As virtual tables go it’s pretty simple. It was shamelessly 
copied from the json_each implementation in json1.c, then adapted from there, 
and it’s still quite similar (it’s just that the data collection it’s iterating 
over is not in JSON format.)What I’m seeing is that our fl_each's  xConnect and 
xBestIndex are called at compile/prepare time, then xOpen is called during the 
first call to sqlite3_step, and immediately after that comes a crash in 
sqlite3VdbeCursorMoveto() because the passed-in VdbeCursor is NULL. (Actually 
pp is a valid pointer, but *pp is NULL.) This is right at the start of handling 
the next opcode, Column.Top of the backtrace is:* thread #1, queue = 
'com.apple.main-thread', stop reason = EXC_BAD_ACCESS (code=1, address=0x0)
frame #0: sqlite3VdbeCursorMoveto(pp=0x7ffeefbfb028, 
piCol=0x7ffeefbfb034) at sqlite3.c:75132  * frame #1: 
sqlite3VdbeExec(p=0x000101171680) at sqlite3.c:81369frame #2: 
sqlite3Step(p=0x000101171680) at sqlite3.c:77352frame #3: 
sqlite3_step(pStmt=0x000101171680) at sqlite3.c:77416The SQL query looks 
like:SELECT sequence, key, version, flags FROM kv_default WHERE (EXISTS (SELECT 
1 FROM fl_each(body, 'likes') AS _like WHERE _like.value = 'climbing’))EXPLAIN 
returns:0Init024000Start at 241Integer02000r[2]=0; Init EXISTS 
result2Integer13000r[3]=1; LIMIT 
counter3VOpen100vtab:10116FC4Column04600r[6]=kv_default.body<==CRASH AT 
THIS 
OPCODE5String8070likes00r[7]='likes'6Integer24000r[4]=27Integer25000r[5]=28VFilter114400iplan=r[4]
 zplan=''9VColumn11800r[8]=vcolumn(1); fl_each.value10Ne9138(BINARY)51if 
r[8]!=r[9] goto 1311Integer12000r[2]=112DecrJumpZero314000if (--r[3])==0 goto 
1413VNext1900014IfNot22310015OpenRead040500root=4 iDb=0; 
kv_default16Rewind02300017Column011100r[11]=kv_default.sequence18Column001200r[12]=kv_default.key19Column031300r[13]=kv_default.version20Column0214000r[14]=kv_default.flags21ResultRow114000output=r[11..14]22Next01700123Halt024Transaction004001usesStmtJournal=025String8090climbing00r[9]='climbing'26Goto01000(This
 is on Mac OS; I’m on the current beta of 10.13, though that shouldn’t make a 
difference.)—Jens___sqlite-users 
mailing 
listsqlite-users@mailinglists.sqlite.orghttp://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] Common subexpression optimization of deterministic functions

[Jens Alfke]

> On Sep 12, 2017, at 11:09 AM, Warren Young  wrote:
> 
> From my reading of the docs, I don’t see that that is the purpose of 
> SQLITE_DETERMINISTIC:
> 
>https://www.sqlite.org/deterministic.html 
> 

Actually it is. "A deterministic function always gives the same answer when it 
has the same inputs." That is the definition of a mathematical (also called 
"pure") function. Such a function call can of course be factored out as a 
common subexpression.

> The purpose is simply so that the SQLite internals know whether it is safe to 
> use the user-defined function in certain query types.

The reason SQLITE_DETERMINISTIC was added is to allow such functions to be used 
in indexes and then matched with the same function in a query. That allows 
indexing things like JSON properties (via the deterministic json_value 
function.)

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

Re: [sqlite] Table-valued function crashing in SQLite 3.20

[Richard Hipp]

On 9/12/17, Jens Alfke  wrote:
>
>
>> On Sep 11, 2017, at 4:20 PM, Richard Hipp  wrote:
>>
>> Does it work on trunk?
>
> I downloaded "sqlite-snapshot-201708251543.tar.gz”, used the included
> sqlite3.{c,h}, and got exactly the same crash.
>

The proposed fix for your problem is from 20170904 -
https://www.sqlite.org/src/info/c7f9f47b239fdd99

I have uploaded a new snapshot for you.

-- 
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] Common subexpression optimization of deterministic functions

[Jens Alfke]

> On Sep 12, 2017, at 12:02 PM, Darren Duncan  wrote:
> 
> Practically speaking any optimization to reduce actual calls to the 
> deterministic function would have to be at compile time to rewrite the query 
> to explicitly keep the result of the function and use it several times,

Exactly.

> which is someone users can also do by writing the query differently.

Great — any advice on how to do it? I'm totally willing to do this :) but I'm 
not sure how. As I said, a WITH clause looks promising, but I don't know if 
that is purely syntactic sugar, like a macro. (And changing my query generator 
to factor common calls into WITH clauses would be a nontrivial amount of work, 
so I would like to get some assurance that it might help, before I try it.)

The CSE optimization has long been standard in traditional compilers, even 
though the programmer could get the same result by changing their code. (The 
same is true of many other optimizations.) The benefit is that it lets the 
developer write simpler, clearer code with less effort. 

I realize SQLite doesn't have the kind of industrial-strength query optimizers 
that other SQL databases have, but (from an outside perspective) this seems 
like a fairly straightforward optimization. SQLite is already doing some 
similar tricks to recognize matching sub-expressions when it applies an 
expression-based index to a query, for example.

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

Re: [sqlite] Common subexpression optimization of deterministic functions

[Jens Alfke]

> On Sep 12, 2017, at 10:52 AM, Richard Hipp  wrote:
> 
>   ... WHERE fl_value(body,'contact.address.state') IN ('CA','WA');

This was just a simple example query. In general it's possible to have 
arbitrary queries that use arbitrary properties. For example it might be 
comparing a property to a specific value as well as checking the string's 
length.

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

[sqlite] SQLITE_DETERMINISTIC sticky in sqlite3_create_function?

[Nelson, Erik - 2]

If I'm redefining a user-defined function as SQLITE_DETERMINISTIC before each 
query, will the function still get called for subsequent queries?  For example, 
if I had

void user_func(sqlite3_context *context, int argc, sqlite3_value **argv){ 
return random() }

sqlite3_create_function(db, "user", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 
NULL, _func, NULL, NULL))

> select user()
.54

sqlite3_create_function(db, "user", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 
NULL, _func, NULL, NULL))

> select user()
.54  <-- should this result be the same as the previous call to user()?  Or 
would it get evaluated once after each sqlite3_create_function() call?

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.20, are you sure?

[Papa]

Outch, that was not a very smart question, sorry folks for the maluse 
(this is my own word, mal-use) of the bandwidth.


:-[

On 2017-09-12 9:11 AM, Richard Hipp wrote:

On 9/12/17, p...@arbolone.ca  wrote:

I have a file named SQLite3.dll, but I don't know which sqlite version it
is. Any help on how to finding that out?TIA

Right-click on the file.  Select "Properties"



--
ArbolOne.ca
Using Fire Fox and Thunderbird.
ArbolOne is composed of students and volunteers dedicated to providing free 
services to charitable organizations.
ArbolOne on Java Development in progress [ í ]

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

Re: [sqlite] Common subexpression optimization of deterministic functions

[Richard Hipp]

On 9/12/17, Jens Alfke  wrote:
> SQLite 3.19 doesn’t seem to coalesce identical calls to a deterministic
> function. For example, in this query, where `fl_value` is a function I’ve
> registered as SQLITE_DETERMINISTIC:
>
> SELECT key FROM kv_default
> WHERE fl_value(body, 'contact.address.state') = 'CA'
>OR fl_value(body, 'contact.address.state') = 'WA'
>
> fl_value gets called twice per row in the table, with the same inputs both
> times of course. As fl_value is not a cheap function — it’s similar to
> json_value — it would be a noticeable speedup if it were evaluated only once
> per row.

   ... WHERE fl_value(body,'contact.address.state') IN ('CA','WA');

-- 
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] Common subexpression optimization of deterministic functions

[Warren Young]

On Sep 12, 2017, at 11:25 AM, Jens Alfke  wrote:
> 
> SQLite 3.19 doesn’t seem to coalesce identical calls to a deterministic 
> function. For example, in this query, where `fl_value` is a function I’ve 
> registered as SQLITE_DETERMINISTIC:

From my reading of the docs, I don’t see that that is the purpose of 
SQLITE_DETERMINISTIC:

https://www.sqlite.org/deterministic.html

The purpose is simply so that the SQLite internals know whether it is safe to 
use the user-defined function in certain query types.

Still, it *would* be a neat optimization to be able to mark a function as a 
“function” in the mathematical sense and have SQLite treat it as referentially 
transparent in contexts like this.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common subexpression optimization of deterministic functions

[Darren Duncan]

On 2017-09-12 11:41 AM, Jens Alfke wrote:

On Sep 12, 2017, at 11:09 AM, Warren Young  wrote:

From my reading of the docs, I don’t see that that is the purpose of 
SQLITE_DETERMINISTIC:

   https://www.sqlite.org/deterministic.html 



Actually it is. "A deterministic function always gives the same answer when it has the same 
inputs." That is the definition of a mathematical (also called "pure") function. 
Such a function call can of course be factored out as a common subexpression.


The purpose is simply so that the SQLite internals know whether it is safe to 
use the user-defined function in certain query types.


The reason SQLITE_DETERMINISTIC was added is to allow such functions to be used 
in indexes and then matched with the same function in a query. That allows 
indexing things like JSON properties (via the deterministic json_value 
function.)


Practically speaking any optimization to reduce actual calls to the 
deterministic function would have to be at compile time to rewrite the query to 
explicitly keep the result of the function and use it several times, which is 
someone users can also do by writing the query differently.


The fact is, any runtime-level smarts to prevent multiple calls to the function 
would have to involve creating and maintaining an index of inputs to outputs so 
that the DBMS knows whether the function was already called with particular 
inputs or not, so that would be an added complexity.


-- Darren Duncan

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

Re: [sqlite] Common subexpression optimization of deterministic functions

[Jens Alfke]

> On Sep 12, 2017, at 1:41 PM, Darko Volaric  wrote:
> 
> You can implement this by using user defined functions to implement row 
> "local variables" or "registers". They're single assignment storage that 
> keeps intermediate results, namely the common subexpressions.

Thanks! That's a very interesting technique.

> Note that you would need to order these so they are evaluated in dependency 
> order, i.e. ensure each name is set before it is got.

What's a good way to do that? Since SQL is a non-imperative language there 
isn't much notion of order of operations. I can imagine using a 
short-circuiting AND operator, but can I be guaranteed that this will always 
work?

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

Re: [sqlite] Common subexpression optimization of deterministic functions

[Jens Alfke]

> On Sep 12, 2017, at 12:55 PM, Richard Hipp  wrote:
> 
> But we deliberately omit common subexpression elimination (CSE).  This
> is because our research shows that out of the millions of queries that
> SQLite compiles every second, only a very tiny fraction would actually
> benefit from CSE

OMG, I can see the BuzzFeed headline: "SQLite database engine is secretly 
sending your queries back to their server for analytics!" ;-)

Seriously, I see your point. But JSON support may have created a new class of 
queries that do benefit, because every reference to a JSON property is through 
a function call. So while a traditional query can use the same column name 
multiple times without incurring overhead, the JSON equivalent repeats a 
json_extract call multiple times, which does have overhead.

Traditional:
SELECT * FROM stuff WHERE length(name) = 5 OR name = 'five';
JSON:
SELECT * FROM stuff WHERE length(json_extract(jsn,'$.name')) = 5 OR 
json_extract(jsn,'$.name') = 'five';

If CSE optimization is too expensive to do by default, maybe it could be 
enabled by a flag when preparing the statement?

Unfortunately using a WITH clause doesn't seem to help; as I suspected, it's 
basically used as a macro that gets expanded. Here's one I tried:
WITH docs AS (SELECT rowid as id, json_extract(jsn,'$.name') AS name 
FROM stuff) 
SELECT id FROM docs WHERE length(name) <= 5 OR name = 'five';
The EXPLAIN command shows that this query still makes two identical calls to 
json_extract per row.

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

Re: [sqlite] Common subexpression optimization of deterministic functions

[Jens Alfke]

> On Sep 12, 2017, at 12:58 PM, Warren Young  wrote:
> 
> Could it be *extended* to mean what you want?  Of course, but that means 
> you’re asking for a feature, not reporting a bug.

I never claimed to be reporting a bug! The subject line refers to this as an 
"optimization", and in the initial message I said "it would be a noticeable 
speedup". 

I pointed out the SQLITE_DETERMINISTIC flag because it already gives the query 
optimizer the information it would need to detect that a function call is pure 
and can safely be factored out.

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

Re: [sqlite] Common subexpression optimization of deterministic functions

[Darko Volaric]

You can implement this by using user defined functions to implement row "local 
variables" or "registers". They're single assignment storage that keeps 
intermediate results, namely the common subexpressions.

You'd define two functions, something like Get(rowid, name) and Set(rowid, 
name, value). You call Set with the subexpressions as the last parameter. It 
doesn't return any value and just stores the value. The Get function returns a 
previously set value with the given name and is used in the expressions where 
that subexpression would otherwise appear. Note that you would need to order 
these so they are evaluated in dependency order, i.e. ensure each name is set 
before it is got. The rowid parameter is used to detect when the row changes 
and the local variables are all cleared in readiness for the next row.



> On Sep 12, 2017, at 9:22 PM, Jens Alfke  wrote:
> 
> 
> 
>> On Sep 12, 2017, at 12:02 PM, Darren Duncan  wrote:
>> 
>> Practically speaking any optimization to reduce actual calls to the 
>> deterministic function would have to be at compile time to rewrite the query 
>> to explicitly keep the result of the function and use it several times,
> 
> Exactly.
> 
>> which is someone users can also do by writing the query differently.
> 
> Great — any advice on how to do it? I'm totally willing to do this :) but I'm 
> not sure how. As I said, a WITH clause looks promising, but I don't know if 
> that is purely syntactic sugar, like a macro. (And changing my query 
> generator to factor common calls into WITH clauses would be a nontrivial 
> amount of work, so I would like to get some assurance that it might help, 
> before I try it.)
> 
> The CSE optimization has long been standard in traditional compilers, even 
> though the programmer could get the same result by changing their code. (The 
> same is true of many other optimizations.) The benefit is that it lets the 
> developer write simpler, clearer code with less effort. 
> 
> I realize SQLite doesn't have the kind of industrial-strength query 
> optimizers that other SQL databases have, but (from an outside perspective) 
> this seems like a fairly straightforward optimization. SQLite is already 
> doing some similar tricks to recognize matching sub-expressions when it 
> applies an expression-based index to a query, for example.
> 
> —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] Common subexpression optimization of deterministic functions

[Richard Hipp]

On 9/12/17, Jens Alfke  wrote:
>
> I realize SQLite doesn't have the kind of industrial-strength query
> optimizers that other SQL databases have, but (from an outside perspective)
> this seems like a fairly straightforward optimization. SQLite is already
> doing some similar tricks to recognize matching sub-expressions when it
> applies an expression-based index to a query, for example.
>

The query planner in SQLite will hold its own against most others.

But we deliberately omit common subexpression elimination (CSE).  This
is because our research shows that out of the millions of queries that
SQLite compiles every second, only a very tiny fraction would actually
benefit from CSE, but checking for CSE is expensive in both memory and
CPU cycles, and all queries would have to pay the extra checking
overhead whether they benefit or not.

A traditional compiler like GCC is free to use as much memory, time,
and code space to implement esoteric optimizations as it wants,
because compilation happens separately from the application and the
build product will be reused many times and so the cost of compilation
is amortized over many executions.  But a query planner in an RDBMS
(which is really just a compiler that translates the SQL programming
language into some low-level representation - byte code in the case of
SQLite) is more constrained because the compilation happens at
application run-time and the number of uses of the build product is
approximately 1.  And so when writing a query planner, one must be
careful in the use of memory, time, and code space devoted to
optimizations.  This is particularly so for SQLite which is an
embedded RDBMS.

Our belief is that CSE would be not worth the extra memory, CPU, and
code space required to implement it since CSE simply does not come up
that often in SQL statements.  If, in the future, we find that people
begin coding more complex SQL statements which will more often benefit
from CSE, then we might revisit this decision.

-- 
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] Common subexpression optimization of deterministic functions

[Warren Young]

On Sep 12, 2017, at 12:41 PM, Jens Alfke  wrote:
> 
>> On Sep 12, 2017, at 11:09 AM, Warren Young  wrote:
>> 
>> From my reading of the docs, I don’t see that that is the purpose of 
>> SQLITE_DETERMINISTIC:
>> 
>>   https://www.sqlite.org/deterministic.html 
>> 
> 
> Actually it is. "A deterministic function always gives the same answer when 
> it has the same inputs." That is the definition of a mathematical (also 
> called "pure") function. Such a function call can of course be factored out 
> as a common subexpression.

All true, but irrelevant is the *purpose* of this constant is not what you want 
it to be.

As I read the docs, the only purpose of this constant is so that SQLite can 
give an error if you use a non-deterministic function anywhere that would cause 
DB corruption.  That is all.

Could it be *extended* to mean what you want?  Of course, but that means you’re 
asking for a feature, not reporting a bug.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table-valued function crashing in SQLite 3.20

[Jens Alfke]

> On Sep 12, 2017, at 10:50 AM, Richard Hipp  wrote:
> 
> The proposed fix for your problem is from 20170904 -
> https://www.sqlite.org/src/info/c7f9f47b239fdd99 
> 
> 
> I have uploaded a new snapshot for you.

Thanks, this does look like exactly the same bug.
But the latest snapshot at http://www.sqlite.org/download.html 
 is still the 201708251543 one. Did you 
upload the new one somewhere else?

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

Re: [sqlite] SQLITE_DETERMINISTIC sticky in sqlite3_create_function?

[Jens Alfke]

> On Sep 12, 2017, at 12:23 PM, Nelson, Erik - 2 
>  wrote:
> 
>> select user()
> .54  <-- should this result be the same as the previous call to user()?  Or 
> would it get evaluated once after each sqlite3_create_function() call?

It gets evaluated whenever the SELECT runs. The value isn't cached anywhere.

The only difference between a deterministic and a regular function currently 
(AFAIK) is that the former can be used in a CREATE INDEX statement. This allows 
a query that makes an equivalent call to use the index. TL;DR: it's how you can 
do efficient queries on derived values like JSON properties.

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

Re: [sqlite] Table-valued function crashing in SQLite 3.20

[Jens Alfke]

> On Sep 12, 2017, at 1:00 PM, Jens Alfke  wrote:
> 
> Thanks, this does look like exactly the same bug.
> But the latest snapshot at http://www.sqlite.org/download.html 
>   > is still the 201708251543 one. Did you 
> upload the new one somewhere else?

The new snapshot is visible to me now, and I can confirm that it fixes the 
crash. Thanks!

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

Re: [sqlite] Common subexpression optimization of deterministic functions

[raypoker79]

This is a scam please take me off this email the card has been reported stolen 


Sent via the Samsung Galaxy S7, an AT 4G LTE smartphone
 Original message From: Darko Volaric  Date: 
9/12/17  4:41 PM  (GMT-05:00) To: SQLite mailing list 
, Jens Alfke  
Subject: Re: [sqlite] Common subexpression optimization of deterministic
functions 
You can implement this by using user defined functions to implement row "local 
variables" or "registers". They're single assignment storage that keeps 
intermediate results, namely the common subexpressions.

You'd define two functions, something like Get(rowid, name) and Set(rowid, 
name, value). You call Set with the subexpressions as the last parameter. It 
doesn't return any value and just stores the value. The Get function returns a 
previously set value with the given name and is used in the expressions where 
that subexpression would otherwise appear. Note that you would need to order 
these so they are evaluated in dependency order, i.e. ensure each name is set 
before it is got. The rowid parameter is used to detect when the row changes 
and the local variables are all cleared in readiness for the next row.



> On Sep 12, 2017, at 9:22 PM, Jens Alfke  wrote:
> 
> 
> 
>> On Sep 12, 2017, at 12:02 PM, Darren Duncan  wrote:
>> 
>> Practically speaking any optimization to reduce actual calls to the 
>> deterministic function would have to be at compile time to rewrite the query 
>> to explicitly keep the result of the function and use it several times,
> 
> Exactly.
> 
>> which is someone users can also do by writing the query differently.
> 
> Great — any advice on how to do it? I'm totally willing to do this :) but I'm 
> not sure how. As I said, a WITH clause looks promising, but I don't know if 
> that is purely syntactic sugar, like a macro. (And changing my query 
> generator to factor common calls into WITH clauses would be a nontrivial 
> amount of work, so I would like to get some assurance that it might help, 
> before I try it.)
> 
> The CSE optimization has long been standard in traditional compilers, even 
> though the programmer could get the same result by changing their code. (The 
> same is true of many other optimizations.) The benefit is that it lets the 
> developer write simpler, clearer code with less effort. 
> 
> I realize SQLite doesn't have the kind of industrial-strength query 
> optimizers that other SQL databases have, but (from an outside perspective) 
> this seems like a fairly straightforward optimization. SQLite is already 
> doing some similar tricks to recognize matching sub-expressions when it 
> applies an expression-based index to a query, for example.
> 
> —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] Table-valued function crashing in SQLite 3.20

[raypoker79]

This is a scam and has been reported to attorney General and will be filing 
complaint with bbb,,I did not request this and habe cancelled all cards 


Sent via the Samsung Galaxy S7, an AT 4G LTE smartphone
 Original message From: Jens Alfke  Date: 
9/12/17  5:31 PM  (GMT-05:00) To: SQLite mailing list 
 Subject: Re: [sqlite] Table-valued 
function crashing in SQLite 3.20 


> On Sep 12, 2017, at 1:00 PM, Jens Alfke  wrote:
> 
> Thanks, this does look like exactly the same bug.
> But the latest snapshot at http://www.sqlite.org/download.html 
>   > is still the 201708251543 one. Did you 
> upload the new one somewhere else?

The new snapshot is visible to me now, and I can confirm that it fixes the 
crash. Thanks!

—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] Common subexpression optimization of deterministic functions

[Darko Volaric]

Yeah that is a tricky bit, especially since the query optimizer might evaluate 
join expressions in an arbitrary order. A possible approach to this is to work 
out how to always get a particular expression evaluated first (that may well 
just be the left-most expression in the WHERE clause) then create a trivial 
function that takes any number of parameters and always returns true and pass 
all the Set functions as parameters in left to right dependency order. This 
should work becuase I believe function parameters are evaluated left to right 
before the function is called.


> On Sep 12, 2017, at 11:27 PM, Jens Alfke  wrote:
> 
> 
>> On Sep 12, 2017, at 1:41 PM, Darko Volaric > > wrote:
>> 
>> You can implement this by using user defined functions to implement row 
>> "local variables" or "registers". They're single assignment storage that 
>> keeps intermediate results, namely the common subexpressions.
> 
> Thanks! That's a very interesting technique.
> 
>> Note that you would need to order these so they are evaluated in dependency 
>> order, i.e. ensure each name is set before it is got.
> 
> What's a good way to do that? Since SQL is a non-imperative language there 
> isn't much notion of order of operations. I can imagine using a 
> short-circuiting AND operator, but can I be guaranteed that this will always 
> work?
> 
> —Jens

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

Re: [sqlite] Intel 17

[Richard Hipp]

On 9/11/17, Dominique Devienne  wrote:
> FYI, here are the modifications we did to a 3.19.3 amalgamation to build
> with the Intel 17 compiler. Note that we have a few modifs of our own, so
> line numbers might be off. --DD

Please try the latest trunk version of SQLite and let me know if it
works for you.
-- 
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 3.20, are you sure?

[David Raymond]

Not a waste of time. It being in the properties tab is nice with the 
pre-compiled versions. I checked, and my self-compiled version does not have 
that nifty metadata. I guess I should look up on how to add that in the 
compiler. (And remember to update it)

So if that's missing is the other way basically just writing something that 
runs and outputs "select sqlite3_version();"? Or something along those lines?


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Papa
Sent: Tuesday, September 12, 2017 11:38 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SQLite 3.20, are you sure?

Outch, that was not a very smart question, sorry folks for the maluse 
(this is my own word, mal-use) of the bandwidth.

:-[

On 2017-09-12 9:11 AM, Richard Hipp wrote:
> On 9/12/17, p...@arbolone.ca  wrote:
>> I have a file named SQLite3.dll, but I don't know which sqlite version it
>> is. Any help on how to finding that out?TIA
> Right-click on the file.  Select "Properties"
>

-- 
ArbolOne.ca
Using Fire Fox and Thunderbird.
ArbolOne is composed of students and volunteers dedicated to providing free 
services to charitable organizations.
ArbolOne on Java Development in progress [ í ]

___
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] Table-valued function crashing in SQLite 3.20

[Jens Alfke]

> On Sep 11, 2017, at 4:20 PM, Richard Hipp  wrote:
> 
> Does it work on trunk?

I downloaded "sqlite-snapshot-201708251543.tar.gz”, used the included 
sqlite3.{c,h}, and got exactly the same crash.

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

[sqlite] Common subexpression optimization of deterministic functions

[Jens Alfke]

SQLite 3.19 doesn’t seem to coalesce identical calls to a deterministic 
function. For example, in this query, where `fl_value` is a function I’ve 
registered as SQLITE_DETERMINISTIC:

SELECT key FROM kv_default 
WHERE fl_value(body, 'contact.address.state') = 'CA'
   OR fl_value(body, 'contact.address.state') = 'WA'

fl_value gets called twice per row in the table, with the same inputs both 
times of course. As fl_value is not a cheap function — it’s similar to 
json_value — it would be a noticeable speedup if it were evaluated only once 
per row.

Is there a way I can restructure these (automatically generated) queries to do 
the refactoring explicitly? Sort of like assigning to a temporary variable in 
an imperative language? It looks like a WITH clause lets me do this 
syntactically, but I'm not sure if it'll make a difference at runtime.

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