date:20180912

[sqlite] Draft Geopoly docs

2018-09-12 Thread Jake Thaw

The following typos exist in the draft Geopoly docs:

3.9
"new polygon that is a affine transformation"
-> "new polygon that is an affine transformation"

4.1
"each dimension of each coordinate is of 32-byte floating point number"
-> "each dimension of each coordinate is a 32-bit floating point number"

Not mentioned in the docs are:
  - SQLITE_ENABLE_GEOPOLY compile option
  - Aggregate function geopoly_group_bbox

Regards

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

2018-09-12 Thread Keith Medcalf


For more information than you could ever want about how Windows does time and 
timers, see http://www.windowstimestamp.com/description 


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of David Raymond
>Sent: Wednesday, 12 September, 2018 12:12
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>Whoa, all sorts of undocumented stuff. Where did you find all these
>and what they do?
>
>Not seen on https://www.sqlite.org/compile.html (But are seen in 3.24
>amalgamation):
>SQLITE_DEFAULT_PROXYDIR_PERMISSIONS
>SQLITE_DEFAULT_RECURSIVE_TRIGGERS
>SQLITE_ENABLE_COSTMULT
>SQLITE_ENABLE_CURSOR_HINTS
>SQLITE_COUNTOFVIEW_OPTIMIZATION
>SQLITE_EXPLAIN_ESTIMATED_ROWS
>SQLITE_ENABLE_LOAD_EXTENSION
>SQLITE_ENABLE_MODULE_COMMENTS
>SQLITE_STAT4_SAMPLES
>SQLITE_OS_WIN
>SQLITE_OS_WINNT
>SQLITE_USE_MALLOC_H
>SQLITE_USE_MSIZE
>
>Search also not finding it in the 3.24 amalgamation either:
>SQLITE_DEFAULT_TEMP_CACHE_SIZE
>SQLITE_ENABLE_STAT_VTAB
>SQLITE_ENABLE_UNIONVTAB
>SQLITE_NOW_STABILITY_STMT
>SQLITE_USE_PRECISE_TIME
>SQLITE_DATETIME_NEW
>SQLITE_WIN32_FILE_RANDOM
>
>
>For my own I'm on Windows 7, apparently using MinGW-W64 with GCC
>7.3.0
>Compiling from the 3.24.0 amalgamation, with only a couple tweaks to
>shell.c to change the default settings. (.timer on, .eqp on, extra
>newline to start the prompt, etc.)
>
>Apparently enable_explain_comments isn't included in the bit that
>keeps track of things to report in pragma compile_options.
>Also, apparently .version isn't listed in the .help output of the
>CLI, but works. Wonder what else is missing.
>
>
>Microsoft Windows [Version 6.1.7601]
>Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
>
>D:\>sqlite3
>SQLite version 3.24.0 2018-06-04 19:24:41
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>
>sqlite> .version
>SQLite 3.24.0 2018-06-04 19:24:41
>c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366d5221ac8fb199a87ca
>gcc-7.3.0
>
>sqlite> pragma compile_options;
>compile_options
>ALLOW_COVERING_INDEX_SCAN
>COMPILER=gcc-7.3.0
>DEFAULT_AUTOMATIC_INDEX
>DEFAULT_CACHE_SIZE=-65536
>DEFAULT_FILE_FORMAT=4
>DEFAULT_FOREIGN_KEYS
>DEFAULT_JOURNAL_SIZE_LIMIT=0
>DEFAULT_LOCKING_MODE=0
>DEFAULT_MEMSTATUS
>DEFAULT_MMAP_SIZE=0
>DEFAULT_PAGE_SIZE=4096
>DEFAULT_SYNCHRONOUS=0
>DEFAULT_WAL_AUTOCHECKPOINT=1
>DEFAULT_WAL_SYNCHRONOUS=0
>DEFAULT_WORKER_THREADS=4
>ENABLE_COLUMN_METADATA
>ENABLE_DBSTAT_VTAB
>ENABLE_MEMORY_MANAGEMENT
>ENABLE_RTREE
>ENABLE_STMT_SCANSTATUS
>ENABLE_UNKNOWN_SQL_FUNCTION
>LIKE_DOESNT_MATCH_BLOBS
>MAX_ATTACHED=125
>MAX_EXPR_DEPTH=0
>MAX_MMAP_SIZE=0
>MAX_WORKER_THREADS=4
>OMIT_SHARED_CACHE
>STMTJRNL_SPILL=4194304
>THREADSAFE=0
>USE_ALLOCA
>Run Time: real 0.000 user 0.00 sys 0.00
>
>sqlite> CREATE TABLE Foo(x, y, z);
>QUERY PLAN
>`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)
>Run Time: real 0.000 user 0.00 sys 0.00
>
>sqlite> CREATE INDEX FooX on Foo(x);
>Run Time: real 0.000 user 0.00 sys 0.00
>
>sqlite> CREATE INDEX FooZ on Foo(z);
>Run Time: real 0.000 user 0.00 sys 0.00
>
>sqlite> CREATE INDEX FooLenZ on Foo(length(z));
>Run Time: real 0.000 user 0.00 sys 0.00
>
>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

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

2018-09-12 Thread Keith Medcalf


>SQLITE_DEFAULT_PROXYDIR_PERMISSIONS  Applicable to Unix only.  Sets the 
>permissions that will be used when certain kinds of directories are created.

>SQLITE_DEFAULT_RECURSIVE_TRIGGERS  Sets the default for the recursive_triggers 
>pragma (the default is 0 or off).

>SQLITE_ENABLE_COSTMULT  Enables the optimizer to use Cost Multiplier factors 
>in certain operations.

>SQLITE_ENABLE_CURSOR_HINTS  Sends additional information about the cursoring 
>function to the BTREE layer.

>SQLITE_COUNTOFVIEW_OPTIMIZATION  Enables a specific optimization for counting 
>records in a view (see the source documentation)

>SQLITE_EXPLAIN_ESTIMATED_ROWS  Adds information from Query Planner about 
>Estimated Rows to the Explain output

>SQLITE_ENABLE_LOAD_EXTENSION  Set the default load extension API to on.

>SQLITE_ENABLE_MODULE_COMMENTS  Adds additional comments to the explain showing 
>which module generated the code

>SQLITE_STAT4_SAMPLES  Changes the number of buckets in the stat4 histogram 
>created by ANALYZE;

These two force type of Windows being used rather than guessing ...
>SQLITE_OS_WIN
>SQLITE_OS_WINNT

>SQLITE_USE_MALLOC_H
>SQLITE_USE_MSIZE

These two are necessary because when using the MinGW compiler on Windows the 
code does not automatically detect that MALLOC USABLE SIZE is available and 
uses the MSVC semantics of the _msize call to get the size of the allocation 
from the system arena manager, and cannot easily be convinced to do so, except 
by adding these two internal defines, and getting there using proper compiler 
detection is problematic.  The default assumption that system malloc does not 
track usable size means that mem1.c allocates an extra 8 bytes in each 
allocation so that it can store the size information at the beginning of the 
block, then moves the pointer along 8 bytes so that this size is not 
overwritten and the consumer of the allocation is none the wiser.  This however 
causes problems because system malloc returns allocations aligned on paragraph 
(16-byte) boundaries.  The extra 8 bytes allocation and the pointer moving up 
by 8 bytes means that the returned allocation (to user code) is always 
misaligned from the paragraph boundary leading to alignment exceptions 
(hardware exceptions) for items which require paragraph alignment because the 
builtin assumptions about the alignment of memory returned by malloc no longer 
match reality (so if you store certain items in sqlite3_malloc'd memory which 
require paragraph alignment the compiler does not and cannot align them 
correctly -- you can fix this either by (a) increasing the space used for the 
block header recording the allocation size to a full paragraph multiple or (b) 
tell the code that MALLOC USABLE SIZE is available and turning the problem over 
to the runtime library (or (c) use the win32_alloc allocator which does track 
usable size by default).  (or you could generate slower code that assumes 
misaglinment).
>SQLITE_USE_MALLOC_H
>SQLITE_USE_MSIZE


>SQLITE_DEFAULT_TEMP_CACHE_SIZE Sets the default maximum size for the temp db 
>in memory.  Apparently no longer used.

These enable some additional extensions.
>SQLITE_ENABLE_STAT_VTAB
>SQLITE_ENABLE_UNIONVTAB

Once upon a time the sqlite 'now' was step-stable by default and the internal 
"now" (iCurrentTime in the Vdbe) was reset on each entry into the Vdbe (ie, 
each sqlite3_step).  This is a change I made that only resets iCurrentTime on 
"first entry" to the Vdbe (pc=0) thus making 'now' statement stable rather than 
step stable.  Changes along the line (when the SLOCHNG was introduced, I forget 
what version that was, to enable determinism for the query planner but not for 
indexing (when functions were allowed in index expressions)) meaning that for 
all intents and purposes this is now "suspenders" since the default is that 
'now' is statement stable after that version.
>SQLITE_NOW_STABILITY_STMT

>SQLITE_USE_PRECISE_TIME 
>SQLITE_DATETIME_NEW
>SQLITE_WIN32_FILE_RANDOM
These enable some custom changes I made.  

>SQLITE_USE_PRECISE_TIME simply replaces the call to the 
>GetSystemTimeAsFileTime API in the win32 vfs so that it uses the 
>GetSystemTimePreciseAsFileTime API instead (which means the code will only run 
>on Windows with that API being supported (Windows 8/Windows Server 2012 kernel 
>or later -- eventually I may make it choose dynamically at runtime to restore 
>the compatibility with all versions of Windows and the define will not be 
>required).  Although both APIs return the system time in huns (hundreds of 
>nanoseconds), the non-Precise version of the call reads from the system clock 
>which is only updated on each timer tick (which is somewhere between 16.5 ms 
>and 0.5 ms), depending on what you have requested the multimedia update 
>frequency be set to (note that the tick frequency is also the minimum sleep 
>interval since the scheduler is only dispatched on each tick).  The Precise 
>version of the API returns the time in huns without

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

2018-09-12 Thread David Raymond

Whoa, all sorts of undocumented stuff. Where did you find all these and what 
they do?

Not seen on https://www.sqlite.org/compile.html (But are seen in 3.24 
amalgamation):
SQLITE_DEFAULT_PROXYDIR_PERMISSIONS
SQLITE_DEFAULT_RECURSIVE_TRIGGERS
SQLITE_ENABLE_COSTMULT
SQLITE_ENABLE_CURSOR_HINTS
SQLITE_COUNTOFVIEW_OPTIMIZATION
SQLITE_EXPLAIN_ESTIMATED_ROWS
SQLITE_ENABLE_LOAD_EXTENSION
SQLITE_ENABLE_MODULE_COMMENTS
SQLITE_STAT4_SAMPLES
SQLITE_OS_WIN
SQLITE_OS_WINNT
SQLITE_USE_MALLOC_H
SQLITE_USE_MSIZE

Search also not finding it in the 3.24 amalgamation either:
SQLITE_DEFAULT_TEMP_CACHE_SIZE
SQLITE_ENABLE_STAT_VTAB
SQLITE_ENABLE_UNIONVTAB
SQLITE_NOW_STABILITY_STMT
SQLITE_USE_PRECISE_TIME
SQLITE_DATETIME_NEW
SQLITE_WIN32_FILE_RANDOM


For my own I'm on Windows 7, apparently using MinGW-W64 with GCC 7.3.0
Compiling from the 3.24.0 amalgamation, with only a couple tweaks to shell.c to 
change the default settings. (.timer on, .eqp on, extra newline to start the 
prompt, etc.)

Apparently enable_explain_comments isn't included in the bit that keeps track 
of things to report in pragma compile_options.
Also, apparently .version isn't listed in the .help output of the CLI, but 
works. Wonder what else is missing.


Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

D:\>sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> .version
SQLite 3.24.0 2018-06-04 19:24:41 
c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366d5221ac8fb199a87ca
gcc-7.3.0

sqlite> pragma compile_options;
compile_options
ALLOW_COVERING_INDEX_SCAN
COMPILER=gcc-7.3.0
DEFAULT_AUTOMATIC_INDEX
DEFAULT_CACHE_SIZE=-65536
DEFAULT_FILE_FORMAT=4
DEFAULT_FOREIGN_KEYS
DEFAULT_JOURNAL_SIZE_LIMIT=0
DEFAULT_LOCKING_MODE=0
DEFAULT_MEMSTATUS
DEFAULT_MMAP_SIZE=0
DEFAULT_PAGE_SIZE=4096
DEFAULT_SYNCHRONOUS=0
DEFAULT_WAL_AUTOCHECKPOINT=1
DEFAULT_WAL_SYNCHRONOUS=0
DEFAULT_WORKER_THREADS=4
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_MEMORY_MANAGEMENT
ENABLE_RTREE
ENABLE_STMT_SCANSTATUS
ENABLE_UNKNOWN_SQL_FUNCTION
LIKE_DOESNT_MATCH_BLOBS
MAX_ATTACHED=125
MAX_EXPR_DEPTH=0
MAX_MMAP_SIZE=0
MAX_WORKER_THREADS=4
OMIT_SHARED_CACHE
STMTJRNL_SPILL=4194304
THREADSAFE=0
USE_ALLOCA
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> CREATE TABLE Foo(x, y, z);
QUERY PLAN
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> CREATE INDEX FooX on Foo(x);
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> CREATE INDEX FooZ on Foo(z);
Run Time: real 0.000 user 0.00 sys 0.00

sqlite> CREATE INDEX FooLenZ on Foo(length(z));
Run Time: real 0.000 user 0.00 sys 0.00

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

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

2018-09-12 Thread Deon Brewis

Sorry guys, I was using 3.17 for the output of the email (we're not actually 
using that in our product - I accidentally used an old sqlite3.exe to paste it 
in here). 

However, it still repros on 3.24 - it just looks different. See my latest mail 
to Richard with the updated opcodes and the repro with Partial Indexes as well.

- Deon

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


The version is the current tip of trunk, compiler is MinGW GCC 8.1.0 (on 
Windows 10) ... with the following options defined:

#define _WIN32_WINNT 0x0600
#define WINVER _WIN32_WINNT
#define SQLITE_DEFAULT_CACHE_SIZE  262144   // 1 GB
#define SQLITE_DEFAULT_TEMP_CACHE_SIZE 262144   // 1 GB default: 
500 pages
#define SQLITE_DEFAULT_FOREIGN_KEYS 1   // default: 0
#define SQLITE_DEFAULT_PAGE_SIZE4096// default: 4096 
max: 65536
#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT   256 // default: 1000 
pages
#define SQLITE_DEFAULT_PROXYDIR_PERMISSIONS 0755// default: 0755
#define SQLITE_DEFAULT_RECURSIVE_TRIGGERS   1   // default: 0
#define SQLITE_DEFAULT_SHARED_CACHE 0   // default: 0
#define SQLITE_DEFAULT_MMAP_SIZE0   // default: 0
#define SQLITE_ALLOW_COVERING_INDEX_SCAN 1
#define SQLITE_INTROSPECTION_PRAGMAS 1  // Add 
Instropsection Pragmas
#define SQLITE_ENABLE_8_3_NAMES 1
#define SQLITE_ENABLE_API_ARMOR 1   // Enable API Armour
#define SQLITE_ENABLE_COLUMN_METADATA 1
#define SQLITE_ENABLE_COSTMULT 1
#define SQLITE_ENABLE_CURSOR_HINTS 1
#define SQLITE_COUNTOFVIEW_OPTIMIZATION 1 #define SQLITE_ENABLE_DBPAGE_VTAB 1 
#define SQLITE_ENABLE_DBSTAT_VTAB 1 #define SQLITE_ENABLE_DESERIALIZE 1 #define 
SQLITE_ENABLE_EXPLAIN_COMMENTS 1 #define SQLITE_EXPLAIN_ESTIMATED_ROWS 1 
#define SQLITE_ENABLE_FTS3 1 #define SQLITE_ENABLE_FTS3_PARENTHESIS 1 #define 
SQLITE_ENABLE_FTS4 1 #define SQLITE_ENABLE_FTS5 1
#define SQLITE_ENABLE_JSON1 1   // Enable JSON1 -- 
when standard extension
#define SQLITE_ENABLE_LOAD_EXTENSION 1
#define SQLITE_ENABLE_LOCKING_STYLE 1
#define SQLITE_ENABLE_MEMORY_MANAGEMENT 1   // Enable Memory 
Management (sqlite3_release_memory)
#define SQLITE_ENABLE_MODULE_COMMENTS 1
#define SQLITE_ENABLE_PREUPDATE_HOOK 1
#define SQLITE_ENABLE_RTREE 1
#define SQLITE_ENABLE_SORTER_REFERENCES 1   // Enable Sorter 
References
#define SQLITE_ENABLE_STAT_VTAB 1   // Enable 
dbstat_register called from shell
#define SQLITE_ENABLE_STAT1 1
#define SQLITE_ENABLE_STAT2 1
#define SQLITE_ENABLE_STAT3 1
#define SQLITE_ENABLE_STAT4 1
#define SQLITE_ENABLE_STMTVTAB 1// Enable Stmt VTAB
#define SQLITE_ENABLE_UNIONVTAB 1   // Enable unionvtab
#define SQLITE_STAT4_SAMPLES 64 // default: 24 
samples
#define SQLITE_SOUNDEX 1
#define SQLITE_THREADSAFE 1 // 0 = Single 
Threaded, 1 = Serialized, 2 = Multithreaded
#define SQLITE_TEMP_STORE 2 // 0 = Files 
Always, 1 = Files, 2 = Memory, 3 Memory Always
#define SQLITE_USE_URI 1// Enable URI 
Filenames
#define SQLITE_ALLOW_URI_AUTHORITY 1// Allow Authority 
(Host) in URI
#define SQLITE_MAX_ATTACHED 15  // default: 10  
max: 62
#define SQLITE_OS_WIN 1
#define SQLITE_OS_WINNT 1
#define SQLITE_NOW_STABILITY_STMT 1 // Make 'now' 
stable within a statement, not only for a step
#define WHERE_PATH_SIMPLE 50// Paths to 
remember for  2-way joins
#define WHERE_PATH_COMPLEX 100  // Paths to 
remember for >2-way joins
#define SQLITE_USE_PRECISE_TIME 1   // Use 
GetSystemTimePreciseAsFileTime
#define SQLITE_DATETIME_NEW 1   // Use New Datetime 
Functions
#define SQLITE_WIN32_FILE_RANDOM 1  // Force Windows 
RANDOM access cache behaviour
#define SQLITE_DEFAULT_WAL_SYNCHRONOUS  1   // Reduce 
Synchronous to NORMAL in WAL mode
#define SQLITE_DIRECT_OVERFLOW_READ 1   // Do not cache 
overflow pages in SQLite pagecache
#define SQLITE_LIKE_DOESNT_MATCH_BLOBS  1   // Disable LIKE 
matching for BLOBS
#define HAVE_FDATASYNC 1
#define HAVE_GMTIME_R 1
#define HAVE_LOCALTIME_S 1
#define HAVE_USLEEP 1
#define HAVE_UTIME 1
#define UNICODE_STRING_MAX_BYTES ((WORD)65534) #define UNICODE_STRING_MAX_CHARS 
(32766) #define HAVE_ISNAN 1 #define SQLITE_USE_MALLOC_H 1 #define 
SQLITE_USE_MSIZE 1 #define LONGDOUBLE_TYPE __float128

---
The fact

Re: [sqlite] foreign_keys = 0N with Entity Framework 6.2.0

2018-09-12 Thread Simon Slavin

On 12 Sep 2018, at 2:04pm, Urs Wagner  wrote:

> The following code is returning 0. Why?

Which version of SQLite ?

You coerce the result of the call into an integer.  Can you make the call and 
display (or use a debugger to see) exactly what it's returning ?

If you do "PRAGMA compile_options" are either of these returned ?

SQLITE_OMIT_FOREIGN_KEY
SQLITE_OMIT_TRIGGER

Simon.
___
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

2018-09-12 Thread 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 list

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

2018-09-12 Thread Keith Medcalf


The version is the current tip of trunk, compiler is MinGW GCC 8.1.0 (on 
Windows 10) ... with the following options defined:

#define _WIN32_WINNT 0x0600
#define WINVER _WIN32_WINNT
#define SQLITE_DEFAULT_CACHE_SIZE  262144   // 1 GB
#define SQLITE_DEFAULT_TEMP_CACHE_SIZE 262144   // 1 GB default: 
500 pages
#define SQLITE_DEFAULT_FOREIGN_KEYS 1   // default: 0
#define SQLITE_DEFAULT_PAGE_SIZE4096// default: 4096 
max: 65536
#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT   256 // default: 1000 
pages
#define SQLITE_DEFAULT_PROXYDIR_PERMISSIONS 0755// default: 0755
#define SQLITE_DEFAULT_RECURSIVE_TRIGGERS   1   // default: 0
#define SQLITE_DEFAULT_SHARED_CACHE 0   // default: 0
#define SQLITE_DEFAULT_MMAP_SIZE0   // default: 0
#define SQLITE_ALLOW_COVERING_INDEX_SCAN 1
#define SQLITE_INTROSPECTION_PRAGMAS 1  // Add 
Instropsection Pragmas
#define SQLITE_ENABLE_8_3_NAMES 1
#define SQLITE_ENABLE_API_ARMOR 1   // Enable API Armour
#define SQLITE_ENABLE_COLUMN_METADATA 1
#define SQLITE_ENABLE_COSTMULT 1
#define SQLITE_ENABLE_CURSOR_HINTS 1
#define SQLITE_COUNTOFVIEW_OPTIMIZATION 1
#define SQLITE_ENABLE_DBPAGE_VTAB 1
#define SQLITE_ENABLE_DBSTAT_VTAB 1
#define SQLITE_ENABLE_DESERIALIZE 1
#define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
#define SQLITE_EXPLAIN_ESTIMATED_ROWS 1
#define SQLITE_ENABLE_FTS3 1
#define SQLITE_ENABLE_FTS3_PARENTHESIS 1
#define SQLITE_ENABLE_FTS4 1
#define SQLITE_ENABLE_FTS5 1
#define SQLITE_ENABLE_JSON1 1   // Enable JSON1 -- 
when standard extension
#define SQLITE_ENABLE_LOAD_EXTENSION 1
#define SQLITE_ENABLE_LOCKING_STYLE 1
#define SQLITE_ENABLE_MEMORY_MANAGEMENT 1   // Enable Memory 
Management (sqlite3_release_memory)
#define SQLITE_ENABLE_MODULE_COMMENTS 1
#define SQLITE_ENABLE_PREUPDATE_HOOK 1
#define SQLITE_ENABLE_RTREE 1
#define SQLITE_ENABLE_SORTER_REFERENCES 1   // Enable Sorter 
References
#define SQLITE_ENABLE_STAT_VTAB 1   // Enable 
dbstat_register called from shell
#define SQLITE_ENABLE_STAT1 1
#define SQLITE_ENABLE_STAT2 1
#define SQLITE_ENABLE_STAT3 1
#define SQLITE_ENABLE_STAT4 1
#define SQLITE_ENABLE_STMTVTAB 1// Enable Stmt VTAB
#define SQLITE_ENABLE_UNIONVTAB 1   // Enable unionvtab
#define SQLITE_STAT4_SAMPLES 64 // default: 24 
samples
#define SQLITE_SOUNDEX 1
#define SQLITE_THREADSAFE 1 // 0 = Single 
Threaded, 1 = Serialized, 2 = Multithreaded
#define SQLITE_TEMP_STORE 2 // 0 = Files 
Always, 1 = Files, 2 = Memory, 3 Memory Always
#define SQLITE_USE_URI 1// Enable URI 
Filenames
#define SQLITE_ALLOW_URI_AUTHORITY 1// Allow Authority 
(Host) in URI
#define SQLITE_MAX_ATTACHED 15  // default: 10  
max: 62
#define SQLITE_OS_WIN 1
#define SQLITE_OS_WINNT 1
#define SQLITE_NOW_STABILITY_STMT 1 // Make 'now' 
stable within a statement, not only for a step
#define WHERE_PATH_SIMPLE 50// Paths to 
remember for  2-way joins
#define WHERE_PATH_COMPLEX 100  // Paths to 
remember for >2-way joins
#define SQLITE_USE_PRECISE_TIME 1   // Use 
GetSystemTimePreciseAsFileTime
#define SQLITE_DATETIME_NEW 1   // Use New Datetime 
Functions
#define SQLITE_WIN32_FILE_RANDOM 1  // Force Windows 
RANDOM access cache behaviour
#define SQLITE_DEFAULT_WAL_SYNCHRONOUS  1   // Reduce 
Synchronous to NORMAL in WAL mode
#define SQLITE_DIRECT_OVERFLOW_READ 1   // Do not cache 
overflow pages in SQLite pagecache
#define SQLITE_LIKE_DOESNT_MATCH_BLOBS  1   // Disable LIKE 
matching for BLOBS
#define HAVE_FDATASYNC 1
#define HAVE_GMTIME_R 1
#define HAVE_LOCALTIME_S 1
#define HAVE_USLEEP 1
#define HAVE_UTIME 1
#define UNICODE_STRING_MAX_BYTES ((WORD)65534)
#define UNICODE_STRING_MAX_CHARS (32766)
#define HAVE_ISNAN 1
#define SQLITE_USE_MALLOC_H 1
#define SQLITE_USE_MSIZE 1
#define LONGDOUBLE_TYPE __float128

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of David Raymond
>Sent: Wednesday, 12 September, 2018 10:21
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>A little weird and definitely differet, what version are you using
>and how are you compiling it? I checked with the precompiled Windows
>binary to make sure it wasn't something

[sqlite] sqlite3_get_autocommit() changes value when sqlite3_step() returns SQLITE_BUSY

2018-09-12 Thread Nic Ramage

Hi,

I think I have found a regression/bug in Sqlite with respect to
the sqlite3_get_autocommit() function.

Sometimes, when multiple connections are used in multiple threads,
sqlite3_get_autocommit()  reports that the connection has a transaction
open (i.e. not in auto commit mode), even though sqlite3_step() returns
SQLITE_BUSY.  I have verified that sqlite3_get_autocommit()  reports that
there is no transaction open before sqlite3_step() is called.  The problem
seems to be specific to the WAL journal mode, as I haven't been able to
reproduce it for

Initially, the problem only showed up under Linux, but I have now been able
to reproduce the problem on Windows too.  For me it shows up quicker under
linux, but that may just be because of the difference in environments.  I
have attached a single-file C++11 application that consistently reproduces
the problem for me.

I was also able to bisect the introduction of the problem to sqlite
v3.17.0.  v3.16.2 does not have the problem.  The latest 3.24.0 release
does.  I am not an expert on the sqlite source code, but during my
debugging, I was able to determine that the auto commit flag was being
cleared in "case OP_AutoCommit", which returns without error.  Obviously,
something else must be causing the  SQLITE_BUSY afterwards, but is not
resetting the auto commit flag.

In my code, I have worked around the problem by issuing a "rollback"
whenever I detect the problem described.

I hope this is enough information to be able to solve the bug.  If,
however, this behaviour turns out not to be a bug, but something that is
expected to happen occasionally, then the "rollback" provides a reasonable
way to mitigate the issue.

Regards
Nic

#include 
#include 

#include 
#include 

#include "sqlite3.h"


static const char DATABASE_NAME[] = "test.db";


using namespace std::chrono;


static volatile bool finished = false;
static int Update (sqlite3 *db)
{
printf ("Entering Update() for db = %p\n", db);

int rc = SQLITE_OK;
unsigned loops = 0;
const char *msg = "UNKNOWN";
while (finished == false && rc == SQLITE_OK)
{
++loops;

// We should not be in a transaction.
assert (sqlite3_get_autocommit (db) != 0);


// The failure always occurs on "begin immediate", so
// prepare and step it manually.
sqlite3_stmt *st = nullptr;
rc = sqlite3_prepare_v2 (db, "begin immediate", 15, , 
nullptr);
if (rc != SQLITE_OK)
{
msg = "sqlite3_prepare_v2()";
break;
}


// Repeat while SQLITE_BUSY
do
{
rc = sqlite3_step (st);
if (rc == SQLITE_DONE)
{
break;
}

msg = "sqlite3_step()";
if (rc == SQLITE_BUSY)
{
// Since sqlite3_step() failed, we do not expect
// to be in a transaction.
if (sqlite3_get_autocommit (db) == 0)
{
// Oops, this is unexpected!
printf ("Failed!\n");

msg = "sqlite3_get_autocommit()";
break;
}

// Back off for a while.
std::this_thread::sleep_for (milliseconds 
(rand() % 15));
}
}
while (rc == SQLITE_BUSY);


sqlite3_finalize (st);
if (rc != SQLITE_DONE)
{
break;
}


// UPDATE should not fail, as we have our lock,
// so just use sqlite3_exec() for simplicity.
rc = sqlite3_exec (db, "UPDATE t SET i = i + 1", nullptr, 
nullptr, nullptr);
if (rc != SQLITE_OK)
{
msg = "'UPDATE'";
break;
}


// COMMIT can cause a SQLITE_BUSY
do
{
msg = "'COMMIT'";
rc = sqlite3_exec (db, "commit", nullptr, nullptr, 
nullptr);
if (rc == SQLITE_BUSY)
{
// Back off for a while.
std::this_thread::sleep_for (milliseconds 
(rand() % 15));
}
}
while (rc == SQLITE_BUSY);
}


if (rc != SQLITE_OK && rc != SQLITE_INTERRUPT)
{
printf ("%s failed on db %p,

[sqlite] foreign_keys = 0N with Entity Framework 6.2.0

2018-09-12 Thread Urs Wagner

The following code is returning 0. Why?


var objectContext = ((IObjectContextAdapter) this).ObjectContext;

objectContext.ExecuteStoreCommand("PRAGMA foreign_keys = ON;");

var foreignKeys = objectContext.ExecuteStoreQuery("PRAGMA 
foreign_keys;").First();

I explicitely set the pragma foreign_keys to ON.



Thanks



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

2018-09-12 Thread David Raymond

A little weird and definitely differet, what version are you using and how are 
you compiling it? I checked with the precompiled Windows binary to make sure it 
wasn't something weird from my compilation, and it looks pretty close to my 
original, but different. So now I'm wondering where the noop's, explain's, and 
comments are coming from and what affects them.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf
Sent: Wednesday, September 12, 2018 11:40 AM
To: SQLite mailing list
Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on update


Interesting ... I get different output with explain comments enabled:

addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 41000  Start at 41
1 Null   0 7 800  r[7..8]=NULL
2 OpenWrite  0 2 0 3  00  root=2 iDb=0; Foo
3 Noop   0 0 000  Begin WHERE-loop0: Foo
4 Integer1 12000  r[12]=1
5 SeekRowid  0 9 12   00  intkey=r[12]
6 Noop   0 0 000  Begin WHERE-core
7 Rowid  0 8 000  r[8]=rowid
8 Noop   0 0 000  End WHERE-core
9 Noop   0 0 000  End WHERE-loop0: Foo
10OpenWrite  1 5 0 k(2,,) 00  root=5 iDb=0; FooLenZ
11OpenWrite  3 3 0 k(2,,) 00  root=3 iDb=0; FooX
12IsNull 8 40000  if r[8]==NULL goto 40
13Integer1 9 000  r[9]=1
14Column 0 1 10   00  r[10]=Foo.y
15Column 0 2 11   00  r[11]=Foo.z
16Noop   0 0 000  BEGIN: 
GenCnstCks(0,1,8,8,0)
17Noop   0 0 000  uniqueness check for 
FooLenZ
18Copy   1113000  r[13]=r[11]
19PureFunc0  0 132 length(1)  01  FooLenZ column 0
20IntCopy8 3 000  r[3]=r[8]; rowid
21MakeRecord 2 2 100  r[1]=mkrec(r[2..3]); 
for FooLenZ
22Noop   0 0 000  uniqueness check for 
FooX
23SCopy  9 5 000  r[5]=r[9]; x
24IntCopy8 6 000  r[6]=r[8]; rowid
25MakeRecord 5 2 400  r[4]=mkrec(r[5..6]); 
for FooX
26Noop   0 0 000  END: GenCnstCks(0)
27Noop   0 0 000  GenRowIdxDel for 
FooLenZ
28Column 0 2 13   40  r[13]=Foo.z
29PureFunc0  0 1314length(1)  01
30Rowid  0 15000  r[15]=rowid
31IdxDelete  1 14200  key=r[14..15]
32Noop   0 0 000  GenRowIdxDel for FooX
33Column 0 0 14   00  r[14]=Foo.x
34IdxDelete  3 14200  key=r[14..15]
35Delete 0 688 Foo00
36IdxInsert  1 1 2 2  00  key=r[1]
37IdxInsert  3 4 5 2  00  key=r[4]
38MakeRecord 9 3 13   00  r[13]=mkrec(r[9..11])
39Insert 0 138 Foo05  intkey=r[8] data=r[13]
40Halt   0 0 000
41Transaction0 1 4 0  01  usesStmtJournal=0
42Goto   0 1 000



---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


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

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

2018-09-12 Thread Keith Medcalf


Interesting ... I get different output with explain comments enabled:

addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 41000  Start at 41
1 Null   0 7 800  r[7..8]=NULL
2 OpenWrite  0 2 0 3  00  root=2 iDb=0; Foo
3 Noop   0 0 000  Begin WHERE-loop0: Foo
4 Integer1 12000  r[12]=1
5 SeekRowid  0 9 12   00  intkey=r[12]
6 Noop   0 0 000  Begin WHERE-core
7 Rowid  0 8 000  r[8]=rowid
8 Noop   0 0 000  End WHERE-core
9 Noop   0 0 000  End WHERE-loop0: Foo
10OpenWrite  1 5 0 k(2,,) 00  root=5 iDb=0; FooLenZ
11OpenWrite  3 3 0 k(2,,) 00  root=3 iDb=0; FooX
12IsNull 8 40000  if r[8]==NULL goto 40
13Integer1 9 000  r[9]=1
14Column 0 1 10   00  r[10]=Foo.y
15Column 0 2 11   00  r[11]=Foo.z
16Noop   0 0 000  BEGIN: 
GenCnstCks(0,1,8,8,0)
17Noop   0 0 000  uniqueness check for 
FooLenZ
18Copy   1113000  r[13]=r[11]
19PureFunc0  0 132 length(1)  01  FooLenZ column 0
20IntCopy8 3 000  r[3]=r[8]; rowid
21MakeRecord 2 2 100  r[1]=mkrec(r[2..3]); 
for FooLenZ
22Noop   0 0 000  uniqueness check for 
FooX
23SCopy  9 5 000  r[5]=r[9]; x
24IntCopy8 6 000  r[6]=r[8]; rowid
25MakeRecord 5 2 400  r[4]=mkrec(r[5..6]); 
for FooX
26Noop   0 0 000  END: GenCnstCks(0)
27Noop   0 0 000  GenRowIdxDel for 
FooLenZ
28Column 0 2 13   40  r[13]=Foo.z
29PureFunc0  0 1314length(1)  01
30Rowid  0 15000  r[15]=rowid
31IdxDelete  1 14200  key=r[14..15]
32Noop   0 0 000  GenRowIdxDel for FooX
33Column 0 0 14   00  r[14]=Foo.x
34IdxDelete  3 14200  key=r[14..15]
35Delete 0 688 Foo00
36IdxInsert  1 1 2 2  00  key=r[1]
37IdxInsert  3 4 5 2  00  key=r[4]
38MakeRecord 9 3 13   00  r[13]=mkrec(r[9..11])
39Insert 0 138 Foo05  intkey=r[8] data=r[13]
40Halt   0 0 000
41Transaction0 1 4 0  01  usesStmtJournal=0
42Goto   0 1 000



---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


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

Re: [sqlite] Draft Documentation small fixes

2018-09-12 Thread Richard Hipp

Tnx for the report.  Fixed now.

On 9/12/18, R Smith  wrote:
> In the paragraph explaining: SQLITE_FCNTL_DATA_VERSION found here:
> https://www.sqlite.org/draft/c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntldataversion
>
> (Suggested edits indicated like *this*)
>
> ...// The sqlite3_total_changes()
>  interface can be
> used to find if any database on the connection has changed, but that
> interface respon*ds* to changes on TEMP as well as MAIN and does not
> provide a mechanism to detect changes to MAIN only. Also, the
> sqlite3_total_changes()
>  interface
> respon*ds* to internal changes only and omits changes made by other
> database connections. The PRAGMA data_version
>  command
> provide*s* a mechanism to detect changes to a single attached database
> that occur due to other database connections, but omits changes
> implemented by the database connection for which it is called. This file
> control is the only mechanism to detect changes that happen either
> internally or externally*,* on a single database.
>
> ___
> 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

Re: [sqlite] Draft: Window Functions

2018-09-12 Thread Richard Hipp

Tnx for the report.  Fixed now.

On 9/12/18, no...@null.net  wrote:
> In https://www.sqlite.org/draft/windowfunctions.html:
>
> "Window functions may only appears in the result set and..."
>
> s/appears/appear/
>
>
> "If default is also provided, then it is returned instead of NULL
> if row identified by offset does not exist."
>
> s/if row/if the row/# 2 places - both lead() and lag() functions
>
> --
> Mark Lawrence
> ___
> 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

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

2018-09-12 Thread Richard Hipp

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

2018-09-12 Thread David Raymond

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

[sqlite] Draft Documentation small fixes

2018-09-12 Thread R Smith


In the paragraph explaining: SQLITE_FCNTL_DATA_VERSION found here:
https://www.sqlite.org/draft/c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntldataversion

(Suggested edits indicated like *this*)

...// The sqlite3_total_changes() 
 interface can be 
used to find if any database on the connection has changed, but that 
interface respon*ds* to changes on TEMP as well as MAIN and does not 
provide a mechanism to detect changes to MAIN only. Also, the 
sqlite3_total_changes() 
 interface 
respon*ds* to internal changes only and omits changes made by other 
database connections. The PRAGMA data_version 
 command 
provide*s* a mechanism to detect changes to a single attached database 
that occur due to other database connections, but omits changes 
implemented by the database connection for which it is called. This file 
control is the only mechanism to detect changes that happen either 
internally or externally*,* on a single database.


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

[sqlite] Draft: Window Functions

2018-09-12 Thread nomad

In https://www.sqlite.org/draft/windowfunctions.html:

"Window functions may only appears in the result set and..."

s/appears/appear/


"If default is also provided, then it is returned instead of NULL
if row identified by offset does not exist."

s/if row/if the row/# 2 places - both lead() and lag() functions

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

[sqlite] Draft Geopoly docs

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

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

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

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

Re: [sqlite] foreign_keys = 0N with Entity Framework 6.2.0

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

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

[sqlite] sqlite3_get_autocommit() changes value when sqlite3_step() returns SQLITE_BUSY

[sqlite] foreign_keys = 0N with Entity Framework 6.2.0

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

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

Re: [sqlite] Draft Documentation small fixes

Re: [sqlite] Draft: Window Functions

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

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

[sqlite] Draft Documentation small fixes

[sqlite] Draft: Window Functions

18 matches

Site Navigation

Mail list logo

Footer information