Re: [sqlite] SQLITE touches unchanged expression indexes on update
This seems to work perfectly for expression indexes. Thanks, it makes a HUGE difference for us! Would you perhaps be able to make a similar fix for partial indexes? i.e. this scenario: CREATE TABLE Foo(x, y, z); CREATE INDEX FooX on Foo(x); CREATE INDEX FooZ on Foo(z); CREATE INDEX FooPartialZ on Foo(z) WHERE z > 42; explain UPDATE foo SET x=1 WHERE rowid=1; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 36000 Start at 36 1 Null 0 7 800 r[7..8]=NULL 2 OpenWrite 0 2 0 3 00 root=2 iDb=0; Foo 3 Integer1 12000 r[12]=1 4 SeekRowid 0 6 12 00 intkey=r[12] 5 Rowid 0 8 000 r[8]=rowid 6 OpenWrite 1 5 0 k(2,,) 00 root=5 iDb=0; FooPartialZ <<<=== 7 OpenWrite 3 3 0 k(2,,) 00 root=3 iDb=0; FooX 8 IsNull 8 35000 if r[8]==NULL goto 35 9 Integer1 9 000 r[9]=1 10Column 0 1 10 00 r[10]=Foo.y 11Column 0 2 11 00 r[11]=Foo.z 12Noop 0 0 000 uniqueness check for FooPartialZ <<<=== 13Null 0 1 000 r[1]=NULL 14Le 141811(BINARY) 51 if r[11]<=r[14] goto 18 15SCopy 112 000 r[2]=r[11]; z 16IntCopy8 3 000 r[3]=r[8]; rowid 17MakeRecord 2 2 100 r[1]=mkrec(r[2..3]); for FooPartialZ <<<=== 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 22Column 0 2 13 00 r[13]=Foo.z 23Le 142713(BINARY) 51 if r[13]<=r[14] goto 27 24Column 0 2 15 00 r[15]=Foo.z 25Rowid 0 16000 r[16]=rowid 26IdxDelete 1 15200 key=r[15..16] 27Column 0 0 15 00 r[15]=Foo.x 28Rowid 0 16000 r[16]=rowid 29IdxDelete 3 15200 key=r[15..16] 30IsNull 1 32000 if r[1]==NULL goto 32 31IdxInsert 1 1 2 2 00 key=r[1] <<<=== 32IdxInsert 3 4 5 2 00 key=r[4] 33MakeRecord 9 3 13 00 r[13]=mkrec(r[9..11]) 34Insert 0 138 Foo05 intkey=r[8] data=r[13] 35Halt 0 0 000 36Transaction0 1 4 0 01 usesStmtJournal=0 37Integer4214000 r[14]=42 38Goto 0 1 000 -Original Message- From: drhsql...@gmail.com On Behalf Of Richard Hipp Sent: Saturday, September 15, 2018 2:46 PM To: SQLite mailing list Cc: de...@outlook.com 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. This enhancement did not make the cutoff for 3.25.0. But as 3.25.0 is now out, I have started the next release cycle and you can find this enhancement on the latest trunk version of SQLite. You'll need to grab a tarball (or clone the Fossil repository) and compile it yourself. If you can, please do this and try out the code and let me know whether or not it works, that will be appreciated. -- 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
On 9/11/18, Deon Brewis wrote: > It seems like there is an opportunity for improvement on updates if an index > contains expressions. This enhancement did not make the cutoff for 3.25.0. But as 3.25.0 is now out, I have started the next release cycle and you can find this enhancement on the latest trunk version of SQLite. You'll need to grab a tarball (or clone the Fossil repository) and compile it yourself. If you can, please do this and try out the code and let me know whether or not it works, that will be appreciated. -- 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
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]=Fo
Re: [sqlite] SQLITE touches unchanged expression indexes on update
s 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 reference to the clock >tick interval (by using the TSC, so if the CPU is old and has a non-stable TSC >then the result may not actually be very Precise after all, however, this is a >problem for Microsoft to fix if they choose to do so (it probably will be as >part of the change of the underlying OS time semantics from a POSIX base to a >UTC base). This means that the iJD (julian day in milliseconds, which is how >sqlite3 tracks time) will always have millisecond precision rather than being >limited to the update granularity of the system timer tick. >SQLITE_DATETIME_NEW turns on some more changes I made to the "datetime" module >by changing the semantics to always return the applicable UTC offset as part >of the resulting string, even when using "localtime" (and thus bypassing most >of the processing for 'utc' since the timestamp with an offset is always a UTC >timestamp). The replacement functions (datetime/date/time) all behave the >same and this turns on that behaviour (which requires a new variable in the >DateTime structure). The replacement datetime/date/time functions (which also >work without this being defined, though the timestamps are not localized) also >permit the use of an IANA standard timezone name in place of 'localtime' (thus >requiring the current IANA timezone database be loaded into the sqlite3 >database in use in order to work) thus fixing the timezone handling on Windows >so that it is accurate (or at least as accurate as the IANA database) for all >timezones for all times (Linux and other OSes that use the timezone database >in their localtime functions do not need this since they know how to compute >localtime properly -- only Windows is horridly broken in this case). >SQLITE_WIN32_FILE_RANDOM is another local change which forces all file opens >to pass the RANDOM flag to the win32 createfile API so as to ensure that the >Windows readahead is always disabled even when the OS is not WINCE. --- 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_WO
Re: [sqlite] SQLITE touches unchanged expression indexes on update
[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 Run Time: real 0.062 user 0.00 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_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_MA
Re: [sqlite] SQLITE touches unchanged expression indexes on update
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 f
Re: [sqlite] SQLITE touches unchanged expression indexes on update
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
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 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 >bin
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 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 Explain
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 >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[
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 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)
