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.000000 sys 0.000000 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.000000 sys 0.000000 sqlite> CREATE INDEX FooX on Foo(x); Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> CREATE INDEX FooZ on Foo(z); Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> CREATE INDEX FooLenZ on Foo(length(z)); Run Time: real 0.000 user 0.000000 sys 0.000000 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 Run Time: real 0.062 user 0.000000 sys 0.015600 sqlite> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Wednesday, September 12, 2018 1:04 PM 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_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