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 <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of
Keith Medcalf
Sent: Wednesday, September 12, 2018 10:04 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
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_SIZE 4096 // 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_SIZE 0 // 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 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 p1 p2 p3 p4 p5 comment
>---- ------------- ---- ---- ---- ------------- -- -----------
>--
>0 Init 0 41 0 00 Start at 41
>1 Null 0 7 8 00
>r[7..8]=NULL
>2 OpenWrite 0 2 0 3 00 root=2
>iDb=0; Foo
>3 Noop 0 0 0 00 Begin
>WHERE-loop0: Foo
>4 Integer 1 12 0 00 r[12]=1
>5 SeekRowid 0 9 12 00
>intkey=r[12]
>6 Noop 0 0 0 00 Begin
>WHERE-core
>7 Rowid 0 8 0 00 r[8]=rowid
>8 Noop 0 0 0 00 End WHERE-
>core
>9 Noop 0 0 0 00 End WHERE-
>loop0: Foo
>10 OpenWrite 1 5 0 k(2,,) 00 root=5
>iDb=0; FooLenZ
>11 OpenWrite 3 3 0 k(2,,) 00 root=3
>iDb=0; FooX
>12 IsNull 8 40 0 00 if
>r[8]==NULL goto 40
>13 Integer 1 9 0 00 r[9]=1
>14 Column 0 1 10 00 r[10]=Foo.y
>15 Column 0 2 11 00 r[11]=Foo.z
>16 Noop 0 0 0 00 BEGIN:
>GenCnstCks(0,1,8,8,0)
>17 Noop 0 0 0 00 uniqueness
>check for FooLenZ
>18 Copy 11 13 0 00 r[13]=r[11]
>19 PureFunc0 0 13 2 length(1) 01 FooLenZ
>column 0
>20 IntCopy 8 3 0 00 r[3]=r[8];
>rowid
>21 MakeRecord 2 2 1 00
>r[1]=mkrec(r[2..3]); for FooLenZ
>22 Noop 0 0 0 00 uniqueness
>check for FooX
>23 SCopy 9 5 0 00 r[5]=r[9];
>x
>24 IntCopy 8 6 0 00 r[6]=r[8];
>rowid
>25 MakeRecord 5 2 4 00
>r[4]=mkrec(r[5..6]); for FooX
>26 Noop 0 0 0 00 END:
>GenCnstCks(0)
>27 Noop 0 0 0 00
>GenRowIdxDel for FooLenZ
>28 Column 0 2 13 40 r[13]=Foo.z
>29 PureFunc0 0 13 14 length(1) 01
>30 Rowid 0 15 0 00 r[15]=rowid
>31 IdxDelete 1 14 2 00
>key=r[14..15]
>32 Noop 0 0 0 00
>GenRowIdxDel for FooX
>33 Column 0 0 14 00 r[14]=Foo.x
>34 IdxDelete 3 14 2 00
>key=r[14..15]
>35 Delete 0 68 8 Foo 00
>36 IdxInsert 1 1 2 2 00 key=r[1]
>37 IdxInsert 3 4 5 2 00 key=r[4]
>38 MakeRecord 9 3 13 00
>r[13]=mkrec(r[9..11])
>39 Insert 0 13 8 Foo 05 intkey=r[8]
>data=r[13]
>40 Halt 0 0 0 00
>41 Transaction 0 1 4 0 01
>usesStmtJournal=0
>42 Goto 0 1 0 00
>
>
>
>---
>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 p1 p2 p3 p4 p5 comment
>>---- ------------- ---- ---- ---- ------------- -- ----------
>-
>>--
>>0 Init 0 33 0 00 Start at
>33
>>1 Null 0 7 8 00
>>r[7..8]=NULL
>>2 OpenWrite 0 2 0 3 00 root=2
>>iDb=0; Foo
>>3 Explain 3 0 0 SEARCH TABLE foo USING
>INTEGER
>>PRIMARY KEY (rowid=?) 00
>>4 Integer 1 12 0 00 r[12]=1
>>5 SeekRowid 0 7 12 00
>>intkey=r[12]; pk
>>6 Rowid 0 8 0 00 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 32 0 00 if
>>r[8]==NULL goto 32
>>10 Integer 1 9 0 00 r[9]=1
>>11 Column 0 1 10 00
>r[10]=Foo.y
>>12 Column 0 2 11 00
>r[11]=Foo.z
>>13 Noop 0 0 0 00 uniqueness
>>check for FooLenZ
>>14 Copy 11 13 0 00
>r[13]=r[11]
>>15 PureFunc0 0 13 2 length(1) 01 FooLenZ
>>column 0
>>16 IntCopy 8 3 0 00 r[3]=r[8];
>>rowid
>>17 MakeRecord 2 2 1 00
>>r[1]=mkrec(r[2..3]); for FooLenZ
>>18 Noop 0 0 0 00 uniqueness
>>check for FooX
>>19 SCopy 9 5 0 00 r[5]=r[9];
>>x
>>20 IntCopy 8 6 0 00 r[6]=r[8];
>>rowid
>>21 MakeRecord 5 2 4 00
>>r[4]=mkrec(r[5..6]); for FooX
>>22 Copy 11 13 0 00
>r[13]=r[11]
>>23 PureFunc0 0 13 14 length(1) 01
>>24 Rowid 0 15 0 00
>r[15]=rowid
>>25 IdxDelete 1 14 2 00
>>key=r[14..15]
>>26 Column 0 0 14 00
>r[14]=Foo.x
>>27 IdxDelete 3 14 2 00
>>key=r[14..15]
>>28 IdxInsert 1 1 2 2 00 key=r[1]
>>29 IdxInsert 3 4 5 2 00 key=r[4]
>>30 MakeRecord 9 3 13 00
>>r[13]=mkrec(r[9..11])
>>31 Insert 0 13 8 Foo 05
>intkey=r[8]
>>data=r[13]
>>32 Halt 0 0 0 00
>>33 Transaction 0 1 4 0 01
>>usesStmtJournal=0
>>34 Goto 0 1 0 00
>>
>>
>>-----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 p4 p5 comment
>>----- ---- ----------- -- -- -- --------- -- -------
>> 1 0 Init 0 30 0 00 (null)
>> 2 1 Null 0 7 8 00 (null)
>> 3 2 OpenWrite 0 2 0 3 00 (null) // opening
>'Foo'
>>(expected)
>> 4 3 Integer 1 12 0 00 (null)
>> 5 4 SeekRowid 0 6 12 00 (null)
>> 6 5 Rowid 0 8 0 00 (null)
>> 7 6 OpenWrite 1 5 0 k(2,,) 00 (null) // opening
>>'FooLenZ' (NOT expected)
>> 8 7 OpenWrite 3 3 0 k(2,,) 00 (null) // opening
>>'FooX' (expected)
>> 9 8 IsNull 8 29 0 00 (null)
>> 10 9 Integer 1 9 0 00 (null)
>> 11 10 Column 0 1 10 00 (null)
>> 12 11 Column 0 2 11 00 (null)
>> 13 12 Copy 11 13 0 00 (null)
>> 14 13 Function0 0 13 2 length(1) 01 (null)
>> 15 14 IntCopy 8 3 0 00 (null)
>> 16 15 MakeRecord 2 2 1 00 (null)
>> 17 16 SCopy 9 5 0 00 (null)
>> 18 17 IntCopy 8 6 0 00 (null)
>> 19 18 MakeRecord 5 2 4 00 (null)
>> 20 19 Copy 11 13 0 00 (null)
>> 21 20 Function0 0 13 14 length(1) 01 (null)
>> 22 21 Rowid 0 15 0 00 (null)
>> 23 22 IdxDelete 1 14 2 00 (null)
>> 24 23 Column 0 0 14 00 (null)
>> 25 24 IdxDelete 3 14 2 00 (null)
>> 26 25 IdxInsert 1 1 2 2 00 (null) // updating
>>'FooLenZ' (NOT expected)
>> 27 26 IdxInsert 3 4 5 2 00 (null) // updating
>>'FooX' (expected)
>> 28 27 MakeRecord 9 3 13 00 (null)
>> 29 28 Insert 0 13 8 Foo 05 (null)
>> 30 29 Halt 0 0 0 00 (null)
>> 31 30 Transaction 0 1 42 0 01 (null)
>> 32 31 Goto 0 1 0 00 (null)
>>
>>sqlite_master:
>>RecNo type name tbl_name rootpage sql
>>----- ----- ------- -------- -------- ------------------------------
>-
>>-------
>> 1 table Foo Foo 2 CREATE TABLE Foo(x, y, z)
>> 2 index FooX Foo 3 CREATE INDEX FooX on Foo(x)
>> 3 index FooZ Foo 4 CREATE INDEX FooZ on Foo(z)
>> 4 index FooLenZ Foo 5 CREATE INDEX FooLenZ on
>>Foo(length(z))
>>
>>
>>
>>_______________________________________________
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>_______________________________________________
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
