## Summary **No review yet** ## Minimized query ```sql .lint PRAGMA ignore_check_constraints = YES; PRAGMA count_changes = 1; PRAGMA pragma_list; .eqp full BEGIN; CREATE TABLE t1(w int, x int, y int, z int); CREATE UNIQUE INDEX i1w ON t1(w); CREATE INDEX i1xy ON t1(x,y); CREATE INDEX i1zyx ON t1(z,y,x); COMMIT; SELECT * FROM t1 WHERE w BETWEEN 5 AND 6 ORDER BY +w; SELECT * FROM t1 WHERE +w BETWEEN 5 AND 65-y ORDER BY +w; INSERT INTO t1 DEFAULT VALUES; INSERT INTO t1 VALUES (1, 'x', 'x', 1) ON CONFLICT(w) DO UPDATE SET w = excluded.w, x = excluded.x, y = excluded.y; SELECT COUNT(*) FROM t1; SELECT SUM(z) FROM t1; INSERT OR REPLACE INTO t1 VALUES (NULL, '', '', ''); SELECT GROUP_CONCAT(x, '|') FROM t1; ``` ## Actual output ```sql analysis_limit application_id auto_vacuum automatic_index busy_timeout cache_size cache_spill case_sensitive_like cell_size_check checkpoint_fullfsync collation_list compile_options count_changes data_version database_list default_cache_size defer_foreign_keys empty_result_callbacks encoding foreign_key_check foreign_key_list foreign_keys freelist_count full_column_names fullfsync function_list hard_heap_limit ignore_check_constraints incremental_vacuum index_info index_list index_xinfo integrity_check journal_mode journal_size_limit legacy_alter_table locking_mode max_page_count mmap_size module_list optimize page_count page_size pragma_list query_only quick_check read_uncommitted recursive_triggers reverse_unordered_selects schema_version secure_delete short_column_names shrink_memory soft_heap_limit synchronous table_info table_list table_xinfo temp_store temp_store_directory threads trusted_schema user_version wal_autocheckpoint wal_checkpoint writable_schema addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 1 0 0 Start at 1 1 AutoCommit 0 0 0 0 2 Halt 0 0 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 28 0 0 Start at 28 1 ReadCookie 0 3 2 0 2 If 3 5 0 0 3 SetCookie 0 2 4 0 4 SetCookie 0 5 1 0 5 CreateBtree 0 2 1 0 r[2]=root iDb=0 flags=1 6 OpenWrite 0 1 0 5 0 root=1 iDb=0 7 NewRowid 0 1 0 0 r[1]=rowid 8 Blob 6 3 0  0 r[3]= (len=6) 9 Insert 0 3 1 8 intkey=r[1] data=r[3] 10 Close 0 0 0 0 11 Close 0 0 0 0 12 Null 0 4 5 0 r[4..5]=NULL 13 Noop 2 0 4 0 14 OpenWrite 1 1 0 5 0 root=1 iDb=0; sqlite_master 15 SeekRowid 1 17 1 0 intkey=r[1] 16 Rowid 1 5 0 0 r[5]=[P2]=PX rowid of P1 17 IsNull 5 25 0 0 if r[5]==NULL goto 25 18 String8 0 6 0 table 0 r[6]='table' 19 String8 0 7 0 t1 0 r[7]='t1' 20 String8 0 8 0 t1 0 r[8]='t1' 21 SCopy 2 9 0 0 r[9]=r[2] 22 String8 0 10 0 CREATE TABLE t1(w int, x int, y int, z int) 0 r[10]='CREATE TABLE t1(w int, x int, y int, z int)' 23 MakeRecord 6 5 4 BBBDB 0 r[4]=mkrec(r[6..10]) 24 Insert 1 4 5 0 intkey=r[5] data=r[4] 25 SetCookie 0 1 1 0 26 ParseSchema 0 0 0 tbl_name='t1' AND type!='trigger' 0 27 Halt 0 0 0 0 28 Transaction 0 1 0 0 1 usesStmtJournal=1 29 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 36 0 0 Start at 36 1 Noop 0 35 0 0 2 CreateBtree 0 1 2 0 r[1]=root iDb=0 flags=2 3 OpenWrite 0 1 0 5 0 root=1 iDb=0; sqlite_master 4 String8 0 3 0 index 0 r[3]='index' 5 String8 0 4 0 i1w 0 r[4]='i1w' 6 String8 0 5 0 t1 0 r[5]='t1' 7 SCopy 1 6 0 0 r[6]=r[1] 8 String8 0 7 0 CREATE UNIQUE INDEX i1w ON t1(w) 0 r[7]='CREATE UNIQUE INDEX i1w ON t1(w)' 9 NewRowid 0 2 0 0 r[2]=rowid 10 MakeRecord 3 5 8 BBBDB 0 r[8]=mkrec(r[3..7]) 11 Insert 0 8 2 24 intkey=r[2] data=r[8] 12 SorterOpen 3 0 1 k(2,,) 0 13 OpenRead 1 2 0 4 0 root=2 iDb=0; t1 14 Rewind 1 20 0 0 15 Column 1 0 10 0 r[10]=t1.w 16 Rowid 1 11 0 0 r[11]=t1.rowid 17 MakeRecord 10 2 9 0 r[9]=mkrec(r[10..11]) 18 SorterInsert 3 9 0 0 key=r[9] 19 Next 1 15 0 0 20 OpenWrite 2 1 0 k(2,,) 17 root=1 iDb=0 21 SorterSort 3 29 0 0 22 Goto 0 25 0 0 23 SorterCompare 3 22 9 1 0 if key(3)!=trim(r[9],1) goto 22 24 Halt 2067 2 0 t1.w 2 25 SorterData 3 9 2 0 r[9]=data 26 SeekEnd 2 0 0 0 27 IdxInsert 2 9 0 16 key=r[9] 28 SorterNext 3 23 0 0 29 Close 1 0 0 0 30 Close 2 0 0 0 31 Close 3 0 0 0 32 SetCookie 0 1 2 0 33 ParseSchema 0 0 0 name='i1w' AND type='index' 0 34 Expire 0 1 0 0 35 Halt 0 0 0 0 36 Transaction 0 1 1 0 1 usesStmtJournal=1 37 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 34 0 0 Start at 34 1 Noop 0 33 0 0 2 CreateBtree 0 1 2 0 r[1]=root iDb=0 flags=2 3 OpenWrite 0 1 0 5 0 root=1 iDb=0; sqlite_master 4 String8 0 3 0 index 0 r[3]='index' 5 String8 0 4 0 i1xy 0 r[4]='i1xy' 6 String8 0 5 0 t1 0 r[5]='t1' 7 SCopy 1 6 0 0 r[6]=r[1] 8 String8 0 7 0 CREATE INDEX i1xy ON t1(x,y) 0 r[7]='CREATE INDEX i1xy ON t1(x,y)' 9 NewRowid 0 2 0 0 r[2]=rowid 10 MakeRecord 3 5 8 BBBDB 0 r[8]=mkrec(r[3..7]) 11 Insert 0 8 2 24 intkey=r[2] data=r[8] 12 SorterOpen 3 0 2 k(3,,,) 0 13 OpenRead 1 2 0 4 0 root=2 iDb=0; t1 14 Rewind 1 21 0 0 15 Column 1 1 10 0 r[10]=t1.x 16 Column 1 2 11 0 r[11]=t1.y 17 Rowid 1 12 0 0 r[12]=t1.rowid 18 MakeRecord 10 3 9 0 r[9]=mkrec(r[10..12]) 19 SorterInsert 3 9 0 0 key=r[9] 20 Next 1 15 0 0 21 OpenWrite 2 1 0 k(3,,,) 17 root=1 iDb=0 22 SorterSort 3 27 0 0 23 SorterData 3 9 2 0 r[9]=data 24 SeekEnd 2 0 0 0 25 IdxInsert 2 9 0 16 key=r[9] 26 SorterNext 3 23 0 0 27 Close 1 0 0 0 28 Close 2 0 0 0 29 Close 3 0 0 0 30 SetCookie 0 1 3 0 31 ParseSchema 0 0 0 name='i1xy' AND type='index' 0 32 Expire 0 1 0 0 33 Halt 0 0 0 0 34 Transaction 0 1 2 0 1 usesStmtJournal=1 35 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 35 0 0 Start at 35 1 Noop 0 34 0 0 2 CreateBtree 0 1 2 0 r[1]=root iDb=0 flags=2 3 OpenWrite 0 1 0 5 0 root=1 iDb=0; sqlite_master 4 String8 0 3 0 index 0 r[3]='index' 5 String8 0 4 0 i1zyx 0 r[4]='i1zyx' 6 String8 0 5 0 t1 0 r[5]='t1' 7 SCopy 1 6 0 0 r[6]=r[1] 8 String8 0 7 0 CREATE INDEX i1zyx ON t1(z,y,x) 0 r[7]='CREATE INDEX i1zyx ON t1(z,y,x)' 9 NewRowid 0 2 0 0 r[2]=rowid 10 MakeRecord 3 5 8 BBBDB 0 r[8]=mkrec(r[3..7]) 11 Insert 0 8 2 24 intkey=r[2] data=r[8] 12 SorterOpen 3 0 3 k(4,,,,) 0 13 OpenRead 1 2 0 4 0 root=2 iDb=0; t1 14 Rewind 1 22 0 0 15 Column 1 3 10 0 r[10]=t1.z 16 Column 1 2 11 0 r[11]=t1.y 17 Column 1 1 12 0 r[12]=t1.x 18 Rowid 1 13 0 0 r[13]=t1.rowid 19 MakeRecord 10 4 9 0 r[9]=mkrec(r[10..13]) 20 SorterInsert 3 9 0 0 key=r[9] 21 Next 1 15 0 0 22 OpenWrite 2 1 0 k(4,,,,) 17 root=1 iDb=0 23 SorterSort 3 28 0 0 24 SorterData 3 9 2 0 r[9]=data 25 SeekEnd 2 0 0 0 26 IdxInsert 2 9 0 16 key=r[9] 27 SorterNext 3 24 0 0 28 Close 1 0 0 0 29 Close 2 0 0 0 30 Close 3 0 0 0 31 SetCookie 0 1 4 0 32 ParseSchema 0 0 0 name='i1zyx' AND type='index' 0 33 Expire 0 1 0 0 34 Halt 0 0 0 0 35 Transaction 0 1 3 0 1 usesStmtJournal=1 36 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 1 0 0 Start at 1 1 AutoCommit 1 0 0 0 2 Halt 0 0 0 0 QUERY PLAN |--SEARCH t1 USING INDEX i1w (w>? AND wr[2] goto 16 9 Column 0 0 7 0 r[7]=t1.w 10 Column 0 1 8 0 r[8]=t1.x 11 Column 0 2 9 0 r[9]=t1.y 12 Column 0 3 10 0 r[10]=t1.z 13 Column 0 0 6 0 r[6]=t1.w 14 MakeRecord 6 5 11 0 r[11]=mkrec(r[6..10]) 15 SorterInsert 1 11 6 5 0 key=r[11] 16 Next 0 4 0 1 17 OpenPseudo 2 12 6 0 6 columns in r[12] 18 SorterSort 1 26 0 0 19 SorterData 1 12 2 0 r[12]=data 20 Column 2 4 10 0 r[10]=z 21 Column 2 3 9 0 r[9]=y 22 Column 2 2 8 0 r[8]=x 23 Column 2 1 7 0 r[7]=w 24 ResultRow 7 4 0 0 output=r[7..10] 25 SorterNext 1 19 0 0 26 Halt 0 0 0 0 27 Transaction 0 0 4 0 1 usesStmtJournal=0 28 Integer 5 3 0 0 r[3]=5 29 Integer 65 4 0 0 r[4]=65 30 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 34 0 0 Start at 34 1 Integer 0 6 0 0 r[6]=0 2 OpenWrite 0 2 0 4 0 root=2 iDb=0; t1 3 OpenWrite 1 5 0 k(4,,,,) 0 root=5 iDb=0; i1zyx 4 OpenWrite 2 4 0 k(3,,,) 0 root=4 iDb=0; i1xy 5 OpenWrite 3 3 0 k(2,,) 0 root=3 iDb=0; i1w 6 NewRowid 0 1 0 0 r[1]=rowid 7 Affinity 2 4 0 DDDD 0 affinity(r[2..5]) 8 Noop 0 0 0 0 prep index i1zyx 9 SCopy 5 8 0 0 r[8]=r[5]; z 10 SCopy 4 9 0 0 r[9]=r[4]; y 11 SCopy 3 10 0 0 r[10]=r[3]; x 12 IntCopy 1 11 0 0 r[11]=r[1]; rowid 13 MakeRecord 8 4 7 0 r[7]=mkrec(r[8..11]); for i1zyx 14 Noop 0 0 0 0 prep index i1xy 15 SCopy 3 13 0 0 r[13]=r[3]; x 16 SCopy 4 14 0 0 r[14]=r[4]; y 17 IntCopy 1 15 0 0 r[15]=r[1]; rowid 18 MakeRecord 13 3 12 0 r[12]=mkrec(r[13..15]); for i1xy 19 Noop 0 0 0 0 prep index i1w 20 SCopy 2 17 0 0 r[17]=r[2]; w 21 IntCopy 1 18 0 0 r[18]=r[1]; rowid 22 MakeRecord 17 2 16 0 r[16]=mkrec(r[17..18]); for i1w 23 NoConflict 3 25 17 1 0 key=r[17] 24 Halt 2067 2 0 t1.w 2 25 MakeRecord 2 4 19 0 r[19]=mkrec(r[2..5]) 26 IdxInsert 1 7 8 4 16 key=r[7] 27 IdxInsert 2 12 13 3 16 key=r[12] 28 IdxInsert 3 16 17 2 16 key=r[16] 29 Insert 0 19 1 t1 57 intkey=r[1] data=r[19] 30 AddImm 6 1 0 0 r[6]=r[6]+1 31 FkCheck 0 0 0 0 32 ResultRow 6 1 0 0 output=r[6] 33 Halt 0 0 0 0 34 Transaction 0 1 4 0 1 usesStmtJournal=0 35 Null 0 2 0 0 r[2]=NULL 36 Null 0 3 0 0 r[3]=NULL 37 Null 0 4 0 0 r[4]=NULL 38 Null 0 5 0 0 r[5]=NULL 39 Goto 0 1 0 0 1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 85 0 0 Start at 85 1 Integer 0 6 0 0 r[6]=0 2 OpenWrite 0 2 0 4 0 root=2 iDb=0; t1 3 OpenWrite 1 5 0 k(4,,,,) 0 root=5 iDb=0; i1zyx 4 OpenWrite 2 4 0 k(3,,,) 0 root=4 iDb=0; i1xy 5 OpenWrite 3 3 0 k(2,,) 0 root=3 iDb=0; i1w 6 Integer 1 2 0 0 r[2]=1 7 String8 0 3 0 x 0 r[3]='x' 8 String8 0 4 0 x 0 r[4]='x' 9 Integer 1 5 0 0 r[5]=1 10 NewRowid 0 1 0 0 r[1]=rowid 11 Affinity 2 4 0 DDDD 0 affinity(r[2..5]) 12 Noop 0 0 0 0 prep index i1w 13 SCopy 2 17 0 0 r[17]=r[2]; w 14 IntCopy 1 18 0 0 r[18]=r[1]; rowid 15 MakeRecord 17 2 16 0 r[16]=mkrec(r[17..18]); for i1w 16 NoConflict 3 65 17 1 0 key=r[17] 17 Noop 0 0 0 0 Begin DO UPDATE of UPSERT 18 IdxRowid 3 21 0 0 r[21]=rowid 19 SeekRowid 0 0 21 0 intkey=r[21] 20 Null 0 34 35 0 r[34..35]=NULL 21 Noop 4 0 34 0 22 Rowid 0 35 0 0 r[35]=[P2]=PX rowid of P1 23 SCopy 2 36 0 0 r[36]=r[2] 24 SCopy 3 37 0 0 r[37]=r[3] 25 SCopy 4 38 0 0 r[38]=r[4] 26 Column 0 3 39 0 r[39]=t1.z 27 Affinity 36 4 0 DDDD 0 affinity(r[36..39]) 28 Noop 0 0 0 0 prep index i1zyx 29 SCopy 39 23 0 0 r[23]=r[39]; z 30 SCopy 38 24 0 0 r[24]=r[38]; y 31 SCopy 37 25 0 0 r[25]=r[37]; x 32 IntCopy 35 26 0 0 r[26]=r[35]; rowid 33 MakeRecord 23 4 22 0 r[22]=mkrec(r[23..26]); for i1zyx 34 Noop 0 0 0 0 prep index i1xy 35 SCopy 37 28 0 0 r[28]=r[37]; x 36 SCopy 38 29 0 0 r[29]=r[38]; y 37 IntCopy 35 30 0 0 r[30]=r[35]; rowid 38 MakeRecord 28 3 27 0 r[27]=mkrec(r[28..30]); for i1xy 39 Noop 0 0 0 0 prep index i1w 40 SCopy 36 32 0 0 r[32]=r[36]; w 41 IntCopy 35 33 0 0 r[33]=r[35]; rowid 42 MakeRecord 32 2 31 0 r[31]=mkrec(r[32..33]); for i1w 43 NoConflict 3 47 32 1 0 key=r[32] 44 IdxRowid 3 21 0 0 r[21]=rowid 45 Eq 21 47 35 144 if r[35]==r[21] goto 47 46 Halt 2067 2 0 t1.w 2 47 MakeRecord 36 4 34 0 r[34]=mkrec(r[36..39]) 48 Column 0 3 40 0 r[40]=t1.z 49 Column 0 2 41 0 r[41]=t1.y 50 Column 0 1 42 0 r[42]=t1.x 51 Rowid 0 43 0 0 r[43]=t1.rowid 52 IdxDelete 1 40 4 1 key=r[40..43] 53 Column 0 1 40 0 r[40]=t1.x 54 Rowid 0 42 0 0 r[42]=t1.rowid 55 IdxDelete 2 40 3 1 key=r[40..42] 56 Column 0 0 40 0 r[40]=t1.w 57 Rowid 0 41 0 0 r[41]=t1.rowid 58 IdxDelete 3 40 2 1 key=r[40..41] 59 IdxInsert 1 22 23 4 0 key=r[22] 60 IdxInsert 2 27 28 3 0 key=r[27] 61 IdxInsert 3 31 32 2 0 key=r[31] 62 Insert 0 34 35 t1 5 intkey=r[35] data=r[34] 63 Noop 0 0 0 0 End DO UPDATE of UPSERT 64 Goto 0 82 0 0 65 Noop 0 0 0 0 prep index i1zyx 66 SCopy 5 8 0 0 r[8]=r[5]; z 67 SCopy 4 9 0 0 r[9]=r[4]; y 68 SCopy 3 10 0 0 r[10]=r[3]; x 69 IntCopy 1 11 0 0 r[11]=r[1]; rowid 70 MakeRecord 8 4 7 0 r[7]=mkrec(r[8..11]); for i1zyx 71 Noop 0 0 0 0 prep index i1xy 72 SCopy 3 13 0 0 r[13]=r[3]; x 73 SCopy 4 14 0 0 r[14]=r[4]; y 74 IntCopy 1 15 0 0 r[15]=r[1]; rowid 75 MakeRecord 13 3 12 0 r[12]=mkrec(r[13..15]); for i1xy 76 MakeRecord 2 4 19 0 r[19]=mkrec(r[2..5]) 77 IdxInsert 1 7 8 4 16 key=r[7] 78 IdxInsert 2 12 13 3 16 key=r[12] 79 IdxInsert 3 16 17 2 16 key=r[16] 80 Insert 0 19 1 t1 57 intkey=r[1] data=r[19] 81 AddImm 6 1 0 0 r[6]=r[6]+1 82 FkCheck 0 0 0 0 83 ResultRow 6 1 0 0 output=r[6] 84 Halt 0 0 0 0 85 Transaction 0 1 4 0 1 usesStmtJournal=0 86 Goto 0 1 0 0 1 QUERY PLAN `--SCAN t1 USING COVERING INDEX i1w addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 7 0 0 Start at 7 1 OpenRead 1 3 0 k(2,,) 0 root=3 iDb=0 2 Count 1 1 0 0 r[1]=count() 3 Close 1 0 0 0 4 Copy 1 2 0 0 r[2]=r[1] 5 ResultRow 2 1 0 0 output=r[2] 6 Halt 0 0 0 0 7 Transaction 0 0 4 0 1 usesStmtJournal=0 8 Goto 0 1 0 0 2 QUERY PLAN `--SCAN t1 USING COVERING INDEX i1zyx addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 11 0 0 Start at 11 1 Null 0 1 2 0 r[1..2]=NULL 2 OpenRead 1 5 0 k(4,,,,) 0 root=5 iDb=0; i1zyx 3 Rewind 1 7 3 0 0 4 Column 1 0 3 0 r[3]=t1.z 5 AggStep 0 3 1 sum(1) 1 accum=r[1] step(r[3]) 6 Next 1 4 0 1 7 AggFinal 1 1 0 sum(1) 0 accum=r[1] N=1 8 Copy 1 4 0 0 r[4]=r[1] 9 ResultRow 4 1 0 0 output=r[4] 10 Halt 0 0 0 0 11 Transaction 0 0 4 0 1 usesStmtJournal=0 12 Goto 0 1 0 0 1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 49 0 0 Start at 49 1 Integer 0 6 0 0 r[6]=0 2 OpenWrite 0 2 0 4 0 root=2 iDb=0; t1 3 OpenWrite 1 5 0 k(4,,,,) 0 root=5 iDb=0; i1zyx 4 OpenWrite 2 4 0 k(3,,,) 0 root=4 iDb=0; i1xy 5 OpenWrite 3 3 0 k(2,,) 0 root=3 iDb=0; i1w 6 Null 0 2 0 0 r[2]=NULL 7 String8 0 3 0 0 r[3]='' 8 String8 0 4 0 0 r[4]='' 9 String8 0 5 0 0 r[5]='' 10 NewRowid 0 1 0 0 r[1]=rowid 11 Affinity 2 4 0 DDDD 0 affinity(r[2..5]) 12 Noop 0 0 0 0 prep index i1zyx 13 SCopy 5 8 0 0 r[8]=r[5]; z 14 SCopy 4 9 0 0 r[9]=r[4]; y 15 SCopy 3 10 0 0 r[10]=r[3]; x 16 IntCopy 1 11 0 0 r[11]=r[1]; rowid 17 MakeRecord 8 4 7 0 r[7]=mkrec(r[8..11]); for i1zyx 18 Noop 0 0 0 0 prep index i1xy 19 SCopy 3 13 0 0 r[13]=r[3]; x 20 SCopy 4 14 0 0 r[14]=r[4]; y 21 IntCopy 1 15 0 0 r[15]=r[1]; rowid 22 MakeRecord 13 3 12 0 r[12]=mkrec(r[13..15]); for i1xy 23 Noop 0 0 0 0 prep index i1w 24 SCopy 2 17 0 0 r[17]=r[2]; w 25 IntCopy 1 18 0 0 r[18]=r[1]; rowid 26 MakeRecord 17 2 16 0 r[16]=mkrec(r[17..18]); for i1w 27 NoConflict 3 40 17 1 0 key=r[17] 28 IdxRowid 3 20 0 0 r[20]=rowid 29 NotExists 0 40 20 1 0 intkey=r[20] 30 Column 0 3 21 0 r[21]=t1.z 31 Column 0 2 22 0 r[22]=t1.y 32 Column 0 1 23 0 r[23]=t1.x 33 Rowid 0 24 0 0 r[24]=t1.rowid 34 IdxDelete 1 21 4 1 key=r[21..24] 35 Column 0 1 21 0 r[21]=t1.x 36 Rowid 0 23 0 0 r[23]=t1.rowid 37 IdxDelete 2 21 3 1 key=r[21..23] 38 Delete 0 0 0 t1 0 39 Delete 3 0 0 0 40 MakeRecord 2 4 19 0 r[19]=mkrec(r[2..5]) 41 IdxInsert 1 7 8 4 16 key=r[7] 42 IdxInsert 2 12 13 3 16 key=r[12] 43 IdxInsert 3 16 17 2 16 key=r[16] 44 Insert 0 19 1 t1 57 intkey=r[1] data=r[19] 45 AddImm 6 1 0 0 r[6]=r[6]+1 46 FkCheck 0 0 0 0 47 ResultRow 6 1 0 0 output=r[6] 48 Halt 0 0 0 0 49 Transaction 0 1 4 0 1 usesStmtJournal=0 50 Goto 0 1 0 0 1 QUERY PLAN `--SCAN t1 USING COVERING INDEX i1xy addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 12 0 0 Start at 12 1 Null 0 1 2 0 r[1..2]=NULL 2 OpenRead 1 4 0 k(3,,,) 0 root=4 iDb=0; i1xy 3 Rewind 1 8 3 0 0 4 Column 1 0 3 0 r[3]=t1.x 5 String8 0 4 0 | 0 r[4]='|' 6 AggStep 0 3 1 group_concat(2) 2 accum=r[1] step(r[3..4]) 7 Next 1 4 0 1 8 AggFinal 1 2 0 group_concat(2) 0 accum=r[1] N=2 9 Copy 1 5 0 0 r[5]=r[1] 10 ResultRow 5 1 0 0 output=r[5] 11 Halt 0 0 0 0 12 Transaction 0 0 4 0 1 usesStmtJournal=0 13 Goto 0 1 0 0 |x ``` ## Expectation ```sql analysis_limit application_id auto_vacuum automatic_index busy_timeout cache_size cache_spill case_sensitive_like cell_size_check checkpoint_fullfsync collation_list compile_options count_changes data_version database_list default_cache_size defer_foreign_keys empty_result_callbacks encoding foreign_key_check foreign_key_list foreign_keys freelist_count full_column_names fullfsync function_list hard_heap_limit ignore_check_constraints incremental_vacuum index_info index_list index_xinfo integrity_check journal_mode journal_size_limit legacy_alter_table locking_mode max_page_count mmap_size module_list optimize page_count page_size pragma_list query_only quick_check read_uncommitted recursive_triggers reverse_unordered_selects schema_version secure_delete short_column_names shrink_memory soft_heap_limit synchronous table_info table_list table_xinfo temp_store temp_store_directory threads trusted_schema user_version wal_autocheckpoint wal_checkpoint writable_schema addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 3 0 0 Start at 3 1 AutoCommit 0 0 0 0 2 Halt 0 0 0 0 3 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 28 0 0 Start at 28 1 ReadCookie 0 3 2 0 2 If 3 5 0 0 3 SetCookie 0 2 4 0 4 SetCookie 0 5 1 0 5 CreateBtree 0 2 1 0 r[2]=root iDb=0 flags=1 6 OpenWrite 0 1 0 5 0 root=1 iDb=0 7 NewRowid 0 1 0 0 r[1]=rowid 8 Blob 6 3 0  0 r[3]= (len=6) 9 Insert 0 3 1 8 intkey=r[1] data=r[3] 10 Close 0 0 0 0 11 Close 0 0 0 0 12 Null 0 4 5 0 r[4..5]=NULL 13 Noop 2 0 4 0 14 OpenWrite 1 1 0 5 0 root=1 iDb=0; sqlite_master 15 SeekRowid 1 17 1 0 intkey=r[1] 16 Rowid 1 5 0 0 r[5]= rowid of 1 17 IsNull 5 25 0 0 if r[5]==NULL goto 25 18 String8 0 6 0 table 0 r[6]='table' 19 String8 0 7 0 t1 0 r[7]='t1' 20 String8 0 8 0 t1 0 r[8]='t1' 21 Copy 2 9 0 0 r[9]=r[2] 22 String8 0 10 0 CREATE TABLE t1(w int, x int, y int, z int) 0 r[10]='CREATE TABLE t1(w int, x int, y int, z int)' 23 MakeRecord 6 5 4 BBBDB 0 r[4]=mkrec(r[6..10]) 24 Insert 1 4 5 0 intkey=r[5] data=r[4] 25 SetCookie 0 1 1 0 26 ParseSchema 0 0 0 tbl_name='t1' AND type!='trigger' 0 27 Halt 0 0 0 0 28 Transaction 0 1 0 0 1 usesStmtJournal=1 29 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 36 0 0 Start at 36 1 Noop 0 35 0 0 2 CreateBtree 0 1 2 0 r[1]=root iDb=0 flags=2 3 OpenWrite 0 1 0 5 0 root=1 iDb=0; sqlite_master 4 String8 0 3 0 index 0 r[3]='index' 5 String8 0 4 0 i1w 0 r[4]='i1w' 6 String8 0 5 0 t1 0 r[5]='t1' 7 SCopy 1 6 0 0 r[6]=r[1] 8 String8 0 7 0 CREATE UNIQUE INDEX i1w ON t1(w) 0 r[7]='CREATE UNIQUE INDEX i1w ON t1(w)' 9 NewRowid 0 2 0 0 r[2]=rowid 10 MakeRecord 3 5 8 BBBDB 0 r[8]=mkrec(r[3..7]) 11 Insert 0 8 2 24 intkey=r[2] data=r[8] 12 SorterOpen 3 0 1 k(2,,) 0 13 OpenRead 1 2 0 4 0 root=2 iDb=0; t1 14 Rewind 1 20 0 0 15 Column 1 0 10 0 r[10]= cursor 1 column 0 16 Rowid 1 11 0 0 r[11]=t1.rowid 17 MakeRecord 10 2 9 0 r[9]=mkrec(r[10..11]) 18 SorterInsert 3 9 0 0 key=r[9] 19 Next 1 15 0 0 20 OpenWrite 2 1 0 k(2,,) 17 root=1 iDb=0 21 SorterSort 3 29 0 0 22 Goto 0 25 0 0 23 SorterCompare 3 22 9 1 0 if key(3)!=trim(r[9],1) goto 22 24 Halt 2067 2 0 t1.w 2 25 SorterData 3 9 2 0 r[9]=data 26 SeekEnd 2 0 0 0 27 IdxInsert 2 9 0 16 key=r[9] 28 SorterNext 3 23 0 0 29 Close 1 0 0 0 30 Close 2 0 0 0 31 Close 3 0 0 0 32 SetCookie 0 1 2 0 33 ParseSchema 0 0 0 name='i1w' AND type='index' 0 34 Expire 0 1 0 0 35 Halt 0 0 0 0 36 Transaction 0 1 1 0 1 usesStmtJournal=1 37 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 34 0 0 Start at 34 1 Noop 0 33 0 0 2 CreateBtree 0 1 2 0 r[1]=root iDb=0 flags=2 3 OpenWrite 0 1 0 5 0 root=1 iDb=0; sqlite_master 4 String8 0 3 0 index 0 r[3]='index' 5 String8 0 4 0 i1xy 0 r[4]='i1xy' 6 String8 0 5 0 t1 0 r[5]='t1' 7 SCopy 1 6 0 0 r[6]=r[1] 8 String8 0 7 0 CREATE INDEX i1xy ON t1(x,y) 0 r[7]='CREATE INDEX i1xy ON t1(x,y)' 9 NewRowid 0 2 0 0 r[2]=rowid 10 MakeRecord 3 5 8 BBBDB 0 r[8]=mkrec(r[3..7]) 11 Insert 0 8 2 24 intkey=r[2] data=r[8] 12 SorterOpen 3 0 2 k(3,,,) 0 13 OpenRead 1 2 0 4 0 root=2 iDb=0; t1 14 Rewind 1 21 0 0 15 Column 1 1 10 0 r[10]= cursor 1 column 1 16 Column 1 2 11 0 r[11]= cursor 1 column 2 17 Rowid 1 12 0 0 r[12]=t1.rowid 18 MakeRecord 10 3 9 0 r[9]=mkrec(r[10..12]) 19 SorterInsert 3 9 0 0 key=r[9] 20 Next 1 15 0 0 21 OpenWrite 2 1 0 k(3,,,) 17 root=1 iDb=0 22 SorterSort 3 27 0 0 23 SorterData 3 9 2 0 r[9]=data 24 SeekEnd 2 0 0 0 25 IdxInsert 2 9 0 16 key=r[9] 26 SorterNext 3 23 0 0 27 Close 1 0 0 0 28 Close 2 0 0 0 29 Close 3 0 0 0 30 SetCookie 0 1 3 0 31 ParseSchema 0 0 0 name='i1xy' AND type='index' 0 32 Expire 0 1 0 0 33 Halt 0 0 0 0 34 Transaction 0 1 2 0 1 usesStmtJournal=1 35 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 35 0 0 Start at 35 1 Noop 0 34 0 0 2 CreateBtree 0 1 2 0 r[1]=root iDb=0 flags=2 3 OpenWrite 0 1 0 5 0 root=1 iDb=0; sqlite_master 4 String8 0 3 0 index 0 r[3]='index' 5 String8 0 4 0 i1zyx 0 r[4]='i1zyx' 6 String8 0 5 0 t1 0 r[5]='t1' 7 SCopy 1 6 0 0 r[6]=r[1] 8 String8 0 7 0 CREATE INDEX i1zyx ON t1(z,y,x) 0 r[7]='CREATE INDEX i1zyx ON t1(z,y,x)' 9 NewRowid 0 2 0 0 r[2]=rowid 10 MakeRecord 3 5 8 BBBDB 0 r[8]=mkrec(r[3..7]) 11 Insert 0 8 2 24 intkey=r[2] data=r[8] 12 SorterOpen 3 0 3 k(4,,,,) 0 13 OpenRead 1 2 0 4 0 root=2 iDb=0; t1 14 Rewind 1 22 0 0 15 Column 1 3 10 0 r[10]= cursor 1 column 3 16 Column 1 2 11 0 r[11]= cursor 1 column 2 17 Column 1 1 12 0 r[12]= cursor 1 column 1 18 Rowid 1 13 0 0 r[13]=t1.rowid 19 MakeRecord 10 4 9 0 r[9]=mkrec(r[10..13]) 20 SorterInsert 3 9 0 0 key=r[9] 21 Next 1 15 0 0 22 OpenWrite 2 1 0 k(4,,,,) 17 root=1 iDb=0 23 SorterSort 3 28 0 0 24 SorterData 3 9 2 0 r[9]=data 25 SeekEnd 2 0 0 0 26 IdxInsert 2 9 0 16 key=r[9] 27 SorterNext 3 24 0 0 28 Close 1 0 0 0 29 Close 2 0 0 0 30 Close 3 0 0 0 31 SetCookie 0 1 4 0 32 ParseSchema 0 0 0 name='i1zyx' AND type='index' 0 33 Expire 0 1 0 0 34 Halt 0 0 0 0 35 Transaction 0 1 3 0 1 usesStmtJournal=1 36 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 3 0 0 Start at 3 1 AutoCommit 1 0 0 0 2 Halt 0 0 0 0 3 Goto 0 1 0 0 QUERY PLAN |--SEARCH t1 USING INDEX i1w (w>? AND w? AND wr[2] goto 17 10 Column 0 0 7 0 r[7]= cursor 0 column 0 11 Column 0 1 8 0 r[8]= cursor 0 column 1 12 Column 0 2 9 0 r[9]= cursor 0 column 2 13 Column 0 3 10 0 r[10]= cursor 0 column 3 14 Column 0 0 6 0 r[6]= cursor 0 column 0 15 MakeRecord 6 5 11 0 r[11]=mkrec(r[6..10]) 16 SorterInsert 1 11 6 5 0 key=r[11] 17 Next 0 5 0 1 18 Explain 18 0 0 USE TEMP B-TREE FOR ORDER BY 0 19 OpenPseudo 2 12 6 0 6 columns in r[12] 20 SorterSort 1 28 0 0 21 SorterData 1 12 2 0 r[12]=data 22 Column 2 4 10 0 r[10]=z 23 Column 2 3 9 0 r[9]=y 24 Column 2 2 8 0 r[8]=x 25 Column 2 1 7 0 r[7]=w 26 ResultRow 7 4 0 0 output=r[7..10] 27 SorterNext 1 21 0 0 28 Halt 0 0 0 0 29 Transaction 0 0 4 0 1 usesStmtJournal=0 30 Integer 5 3 0 0 r[3]=5 31 Integer 65 4 0 0 r[4]=65 32 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 34 0 0 Start at 34 1 Integer 0 6 0 0 r[6]=0 2 OpenWrite 0 2 0 4 0 root=2 iDb=0; t1 3 OpenWrite 1 5 0 k(4,,,,) 0 root=5 iDb=0; i1zyx 4 OpenWrite 2 4 0 k(3,,,) 0 root=4 iDb=0; i1xy 5 OpenWrite 3 3 0 k(2,,) 0 root=3 iDb=0; i1w 6 NewRowid 0 1 0 0 r[1]=rowid 7 Affinity 2 4 0 DDDD 0 affinity(r[2..5]) 8 Noop 0 0 0 0 prep index i1zyx 9 SCopy 5 8 0 0 r[8]=r[5]; z 10 SCopy 4 9 0 0 r[9]=r[4]; y 11 SCopy 3 10 0 0 r[10]=r[3]; x 12 IntCopy 1 11 0 0 r[11]=r[1]; rowid 13 MakeRecord 8 4 7 0 r[7]=mkrec(r[8..11]); for i1zyx 14 Noop 0 0 0 0 prep index i1xy 15 SCopy 3 13 0 0 r[13]=r[3]; x 16 SCopy 4 14 0 0 r[14]=r[4]; y 17 IntCopy 1 15 0 0 r[15]=r[1]; rowid 18 MakeRecord 13 3 12 0 r[12]=mkrec(r[13..15]); for i1xy 19 Noop 0 0 0 0 prep index i1w 20 SCopy 2 17 0 0 r[17]=r[2]; w 21 IntCopy 1 18 0 0 r[18]=r[1]; rowid 22 MakeRecord 17 2 16 0 r[16]=mkrec(r[17..18]); for i1w 23 NoConflict 3 25 17 1 0 key=r[17] 24 Halt 2067 2 0 t1.w 2 25 MakeRecord 2 4 19 0 r[19]=mkrec(r[2..5]) 26 IdxInsert 1 7 8 4 16 key=r[7] 27 IdxInsert 2 12 13 3 16 key=r[12] 28 IdxInsert 3 16 17 2 16 key=r[16] 29 Insert 0 19 1 t1 57 intkey=r[1] data=r[19] 30 AddImm 6 1 0 0 r[6]=r[6]+1 31 FkCheck 0 0 0 0 32 ResultRow 6 1 0 0 output=r[6] 33 Halt 0 0 0 0 34 Transaction 0 1 4 0 1 usesStmtJournal=0 35 Null 0 2 0 0 r[2]=NULL 36 Null 0 3 0 0 r[3]=NULL 37 Null 0 4 0 0 r[4]=NULL 38 Null 0 5 0 0 r[5]=NULL 39 Goto 0 1 0 0 1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 85 0 0 Start at 85 1 Integer 0 6 0 0 r[6]=0 2 OpenWrite 0 2 0 4 0 root=2 iDb=0; t1 3 OpenWrite 1 5 0 k(4,,,,) 0 root=5 iDb=0; i1zyx 4 OpenWrite 2 4 0 k(3,,,) 0 root=4 iDb=0; i1xy 5 OpenWrite 3 3 0 k(2,,) 0 root=3 iDb=0; i1w 6 Integer 1 2 0 0 r[2]=1 7 String8 0 3 0 x 0 r[3]='x' 8 String8 0 4 0 x 0 r[4]='x' 9 Integer 1 5 0 0 r[5]=1 10 NewRowid 0 1 0 0 r[1]=rowid 11 Affinity 2 4 0 DDDD 0 affinity(r[2..5]) 12 Noop 0 0 0 0 prep index i1w 13 SCopy 2 17 0 0 r[17]=r[2]; w 14 IntCopy 1 18 0 0 r[18]=r[1]; rowid 15 MakeRecord 17 2 16 0 r[16]=mkrec(r[17..18]); for i1w 16 NoConflict 3 65 17 1 0 key=r[17] 17 Noop 0 0 0 0 Begin DO UPDATE of UPSERT 18 IdxRowid 3 21 0 0 r[21]=rowid 19 SeekRowid 0 0 21 0 intkey=r[21] 20 Null 0 34 35 0 r[34..35]=NULL 21 Noop 4 0 34 0 22 Rowid 0 35 0 0 r[35]= rowid of 0 23 Copy 2 36 0 0 r[36]=r[2] 24 Copy 3 37 0 0 r[37]=r[3] 25 Copy 4 38 0 0 r[38]=r[4] 26 Column 0 3 39 0 r[39]= cursor 0 column 3 27 Affinity 36 4 0 DDDD 0 affinity(r[36..39]) 28 Noop 0 0 0 0 prep index i1zyx 29 SCopy 39 23 0 0 r[23]=r[39]; z 30 SCopy 38 24 0 0 r[24]=r[38]; y 31 SCopy 37 25 0 0 r[25]=r[37]; x 32 IntCopy 35 26 0 0 r[26]=r[35]; rowid 33 MakeRecord 23 4 22 0 r[22]=mkrec(r[23..26]); for i1zyx 34 Noop 0 0 0 0 prep index i1xy 35 SCopy 37 28 0 0 r[28]=r[37]; x 36 SCopy 38 29 0 0 r[29]=r[38]; y 37 IntCopy 35 30 0 0 r[30]=r[35]; rowid 38 MakeRecord 28 3 27 0 r[27]=mkrec(r[28..30]); for i1xy 39 Noop 0 0 0 0 prep index i1w 40 SCopy 36 32 0 0 r[32]=r[36]; w 41 IntCopy 35 33 0 0 r[33]=r[35]; rowid 42 MakeRecord 32 2 31 0 r[31]=mkrec(r[32..33]); for i1w 43 NoConflict 3 47 32 1 0 key=r[32] 44 IdxRowid 3 21 0 0 r[21]=rowid 45 Eq 21 47 35 144 if r[35]==r[21] goto 47 46 Halt 2067 2 0 t1.w 2 47 MakeRecord 36 4 34 0 r[34]=mkrec(r[36..39]) 48 Column 0 3 40 0 r[40]= cursor 0 column 3 49 Column 0 2 41 0 r[41]= cursor 0 column 2 50 Column 0 1 42 0 r[42]= cursor 0 column 1 51 Rowid 0 43 0 0 r[43]=t1.rowid 52 IdxDelete 1 40 4 1 key=r[40..43] 53 Column 0 1 40 0 r[40]= cursor 0 column 1 54 Rowid 0 42 0 0 r[42]=t1.rowid 55 IdxDelete 2 40 3 1 key=r[40..42] 56 Column 0 0 40 0 r[40]= cursor 0 column 0 57 Rowid 0 41 0 0 r[41]=t1.rowid 58 IdxDelete 3 40 2 1 key=r[40..41] 59 IdxInsert 1 22 23 4 0 key=r[22] 60 IdxInsert 2 27 28 3 0 key=r[27] 61 IdxInsert 3 31 32 2 0 key=r[31] 62 Insert 0 34 35 t1 5 intkey=r[35] data=r[34] 63 Noop 0 0 0 0 End DO UPDATE of UPSERT 64 Goto 0 82 0 0 65 Noop 0 0 0 0 prep index i1zyx 66 SCopy 5 8 0 0 r[8]=r[5]; z 67 SCopy 4 9 0 0 r[9]=r[4]; y 68 SCopy 3 10 0 0 r[10]=r[3]; x 69 IntCopy 1 11 0 0 r[11]=r[1]; rowid 70 MakeRecord 8 4 7 0 r[7]=mkrec(r[8..11]); for i1zyx 71 Noop 0 0 0 0 prep index i1xy 72 SCopy 3 13 0 0 r[13]=r[3]; x 73 SCopy 4 14 0 0 r[14]=r[4]; y 74 IntCopy 1 15 0 0 r[15]=r[1]; rowid 75 MakeRecord 13 3 12 0 r[12]=mkrec(r[13..15]); for i1xy 76 MakeRecord 2 4 19 0 r[19]=mkrec(r[2..5]) 77 IdxInsert 1 7 8 4 16 key=r[7] 78 IdxInsert 2 12 13 3 16 key=r[12] 79 IdxInsert 3 16 17 2 16 key=r[16] 80 Insert 0 19 1 t1 57 intkey=r[1] data=r[19] 81 AddImm 6 1 0 0 r[6]=r[6]+1 82 FkCheck 0 0 0 0 83 ResultRow 6 1 0 0 output=r[6] 84 Halt 0 0 0 0 85 Transaction 0 1 4 0 1 usesStmtJournal=0 86 Goto 0 1 0 0 1 QUERY PLAN `--SCAN t1 USING COVERING INDEX i1w addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 8 0 0 Start at 8 1 OpenRead 1 3 0 k(2,,) 0 root=3 iDb=0 2 Count 1 1 0 0 r[1]=count() 3 Close 1 0 0 0 4 Explain 4 0 0 SCAN t1 USING COVERING INDEX i1w 0 5 Copy 1 2 0 0 r[2]=r[1] 6 ResultRow 2 1 0 0 output=r[2] 7 Halt 0 0 0 0 8 Transaction 0 0 4 0 1 usesStmtJournal=0 9 Goto 0 1 0 0 2 QUERY PLAN `--SCAN t1 USING COVERING INDEX i1zyx addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 12 0 0 Start at 12 1 Null 0 1 2 0 r[1..2]=NULL 2 OpenRead 1 5 0 k(4,,,,) 0 root=5 iDb=0; i1zyx 3 Explain 3 0 214 SCAN t1 USING COVERING INDEX i1zyx 0 4 Rewind 1 8 3 0 0 5 Column 1 0 3 0 r[3]= cursor 1 column 0 6 AggStep 0 3 2 sum(1) 1 accum=r[2] step(r[3]) 7 Next 1 5 0 1 8 AggFinal 2 1 0 sum(1) 0 accum=r[2] N=1 9 Copy 2 4 0 0 r[4]=r[2] 10 ResultRow 4 1 0 0 output=r[4] 11 Halt 0 0 0 0 12 Transaction 0 0 4 0 1 usesStmtJournal=0 13 Goto 0 1 0 0 1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 49 0 0 Start at 49 1 Integer 0 6 0 0 r[6]=0 2 OpenWrite 0 2 0 4 0 root=2 iDb=0; t1 3 OpenWrite 1 5 0 k(4,,,,) 0 root=5 iDb=0; i1zyx 4 OpenWrite 2 4 0 k(3,,,) 0 root=4 iDb=0; i1xy 5 OpenWrite 3 3 0 k(2,,) 0 root=3 iDb=0; i1w 6 Null 0 2 0 0 r[2]=NULL 7 String8 0 3 0 0 r[3]='' 8 String8 0 4 0 0 r[4]='' 9 String8 0 5 0 0 r[5]='' 10 NewRowid 0 1 0 0 r[1]=rowid 11 Affinity 2 4 0 DDDD 0 affinity(r[2..5]) 12 Noop 0 0 0 0 prep index i1zyx 13 SCopy 5 8 0 0 r[8]=r[5]; z 14 SCopy 4 9 0 0 r[9]=r[4]; y 15 SCopy 3 10 0 0 r[10]=r[3]; x 16 IntCopy 1 11 0 0 r[11]=r[1]; rowid 17 MakeRecord 8 4 7 0 r[7]=mkrec(r[8..11]); for i1zyx 18 Noop 0 0 0 0 prep index i1xy 19 SCopy 3 13 0 0 r[13]=r[3]; x 20 SCopy 4 14 0 0 r[14]=r[4]; y 21 IntCopy 1 15 0 0 r[15]=r[1]; rowid 22 MakeRecord 13 3 12 0 r[12]=mkrec(r[13..15]); for i1xy 23 Noop 0 0 0 0 prep index i1w 24 SCopy 2 17 0 0 r[17]=r[2]; w 25 IntCopy 1 18 0 0 r[18]=r[1]; rowid 26 MakeRecord 17 2 16 0 r[16]=mkrec(r[17..18]); for i1w 27 NoConflict 3 40 17 1 0 key=r[17] 28 IdxRowid 3 20 0 0 r[20]=rowid 29 NotExists 0 40 20 1 0 intkey=r[20] 30 Column 0 3 21 0 r[21]= cursor 0 column 3 31 Column 0 2 22 0 r[22]= cursor 0 column 2 32 Column 0 1 23 0 r[23]= cursor 0 column 1 33 Rowid 0 24 0 0 r[24]=t1.rowid 34 IdxDelete 1 21 4 1 key=r[21..24] 35 Column 0 1 21 0 r[21]= cursor 0 column 1 36 Rowid 0 23 0 0 r[23]=t1.rowid 37 IdxDelete 2 21 3 1 key=r[21..23] 38 Delete 0 0 0 t1 0 39 Delete 3 0 0 0 40 MakeRecord 2 4 19 0 r[19]=mkrec(r[2..5]) 41 IdxInsert 1 7 8 4 16 key=r[7] 42 IdxInsert 2 12 13 3 16 key=r[12] 43 IdxInsert 3 16 17 2 16 key=r[16] 44 Insert 0 19 1 t1 57 intkey=r[1] data=r[19] 45 AddImm 6 1 0 0 r[6]=r[6]+1 46 FkCheck 0 0 0 0 47 ResultRow 6 1 0 0 output=r[6] 48 Halt 0 0 0 0 49 Transaction 0 1 4 0 1 usesStmtJournal=0 50 Goto 0 1 0 0 1 QUERY PLAN `--SCAN t1 USING COVERING INDEX i1xy addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 13 0 0 Start at 13 1 Null 0 1 2 0 r[1..2]=NULL 2 OpenRead 1 4 0 k(3,,,) 0 root=4 iDb=0; i1xy 3 Explain 3 0 213 SCAN t1 USING COVERING INDEX i1xy 0 4 Rewind 1 9 3 0 0 5 Column 1 0 3 0 r[3]= cursor 1 column 0 6 String8 0 4 0 | 0 r[4]='|' 7 AggStep 0 3 2 group_concat(2) 2 accum=r[2] step(r[3..4]) 8 Next 1 5 0 1 9 AggFinal 2 2 0 group_concat(2) 0 accum=r[2] N=2 10 Copy 2 5 0 0 r[5]=r[2] 11 ResultRow 5 1 0 0 output=r[5] 12 Halt 0 0 0 0 13 Transaction 0 0 4 0 1 usesStmtJournal=0 14 Goto 0 1 0 0 |x ``` ## Flag ``` ```