## Summary **No review yet** ## Minimized query ```sql ATTACH DATABASE ':memory:' AS aux30; BEGIN; .eqp full PRAGMA ignore_check_constraints = OFF; PRAGMA encoding = 'UTF-8'; PRAGMA /* NTILE({c}) */ reverse_unordered_selects = OFF; .read commands.sql CREATE TABLE [t1](x); INSERT INTO t1 VALUES(-(-(-(-(0 + 9223372036854775809))) + 0)); SELECT sum((SELECT 1 FROM (SELECT 2 WHERE x IS NULL) WHERE 0)) FROM t1; SELECT COUNT(*) FROM t1; CREATE TEMP VIEW IF NOT EXISTS v_t1_1780 AS SELECT x FROM t1; CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_9644 ON t1((x + 1)); SELECT COUNT(*) FROM t1; UPDATE t1 SET x = NULL WHERE rowid = 1; SELECT * FROM (SELECT * FROM t1) AS t1 CROSS JOIN (SELECT * FROM t1) AS t2 ON t1.x = t2.x; CREATE UNIQUE INDEX IF NOT EXISTS idx_t1_3677 ON t1(lower(x)); /**/ DELETE FROM t1 WHERE x IS NULL RETURNING *; INSERT INTO t1 VALUES (NULL); COMMIT TRANSACTION; SELECT * FROM t1 WHERE x = (SELECT x FROM t1 t2 WHERE t2.x = t1.x LIMIT 1); ANALYZE t1; SELECT * FROM t1; SELECT * FROM t1; DETACH DATABASE aux30; ``` ## Actual output ```sql addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 1 0 0 Start at 1 1 Expire 1 1 0 0 2 Expire 0 0 0 0 3 Halt 0 0 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 1 0 0 Start at 1 1 Expire 1 1 0 0 2 Halt 0 0 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 1 0 0 Start at 1 1 Expire 1 1 0 0 2 Expire 0 0 0 0 3 Halt 0 0 0 0 QUERY PLAN `--SCAN CONSTANT ROW addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 1 0 0 Start at 1 1 Integer 1 1 0 0 r[1]=1 2 ResultRow 1 1 0 0 output=r[1] 3 Halt 0 0 0 0 1 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](x) 0 r[10]='CREATE TABLE [t1](x)' 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 7 0 0 Start at 7 1 OpenWrite 0 2 0 1 0 root=2 iDb=0; t1 2 Subtract 5 4 2 0 r[2]=r[4]-r[5] 3 NewRowid 0 1 0 0 r[1]=rowid 4 MakeRecord 2 1 3 0 r[3]=mkrec(r[2]) 5 Insert 0 3 1 t1 57 intkey=r[1] data=r[3] 6 Halt 0 0 0 0 7 Transaction 0 1 1 0 1 usesStmtJournal=0 8 Integer 0 4 0 0 r[4]=0 9 Integer 0 7 0 0 r[7]=0 10 Integer 0 9 0 0 r[9]=0 11 Integer 0 11 0 0 r[11]=0 12 Integer 0 13 0 0 r[13]=0 13 Real 0 14 0 9.223372036854776e+18 0 r[14]=9.223372036854776e+18 14 Add 14 13 12 0 r[12]=r[14]+r[13] 15 Subtract 12 11 10 0 r[10]=r[11]-r[12] 16 Subtract 10 9 8 0 r[8]=r[9]-r[10] 17 Subtract 8 7 6 0 r[6]=r[7]-r[8] 18 Integer 0 8 0 0 r[8]=0 19 Add 8 6 5 0 r[5]=r[8]+r[6] 20 Goto 0 1 0 0 QUERY PLAN |--SCAN t1 `--CORRELATED SCALAR SUBQUERY 2 |--CO-ROUTINE (subquery-1) | `--SCAN CONSTANT ROW `--SCAN (subquery-1) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 26 0 0 Start at 26 1 Null 0 1 2 0 r[1..2]=NULL 2 OpenRead 0 2 0 1 0 root=2 iDb=0; t1 3 Rewind 0 22 0 0 4 BeginSubrtn 0 4 0 0 r[4]=NULL 5 Null 0 5 5 0 r[5..5]=NULL; Init subquery result 6 InitCoroutine 6 11 7 0 (subquery-1) 7 Goto 0 10 0 0 8 Integer 2 7 0 0 r[7]=2 9 Yield 6 0 0 0 10 EndCoroutine 6 0 0 0 11 Integer 1 8 0 0 r[8]=1; LIMIT counter 12 Goto 0 18 0 0 13 InitCoroutine 6 0 7 0 14 Yield 6 18 0 0 next row of (subquery-1) 15 Integer 1 5 0 0 r[5]=1 16 DecrJumpZero 8 18 0 0 if (--r[8])==0 goto 18 17 Goto 0 14 0 0 18 Return 4 5 1 0 19 Copy 5 3 0 0 r[3]=r[5] 20 AggStep 0 3 1 sum(1) 1 accum=r[1] step(r[3]) 21 Next 0 4 0 1 22 AggFinal 1 1 0 sum(1) 0 accum=r[1] N=1 23 Copy 1 9 0 0 r[9]=r[1] 24 ResultRow 9 1 0 0 output=r[9] 25 Halt 0 0 0 0 26 Transaction 0 0 1 0 1 usesStmtJournal=0 27 Goto 0 1 0 0 QUERY PLAN `--SCAN t1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 7 0 0 Start at 7 1 OpenRead 1 2 0 1 0 root=2 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 1 0 1 usesStmtJournal=0 8 Goto 0 1 0 0 1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 28 0 0 Start at 28 1 ReadCookie 1 3 2 0 2 If 3 5 0 0 3 SetCookie 1 2 4 0 4 SetCookie 1 5 1 0 5 Integer 0 2 0 0 r[2]=0 6 OpenWrite 0 1 1 5 0 root=1 iDb=1 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 1 5 0 root=1 iDb=1; sqlite_temp_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 view 0 r[6]='view' 19 String8 0 7 0 v_t1_1780 0 r[7]='v_t1_1780' 20 String8 0 8 0 v_t1_1780 0 r[8]='v_t1_1780' 21 SCopy 2 9 0 0 r[9]=r[2] 22 String8 0 10 0 CREATE VIEW v_t1_1780 AS SELECT x FROM t1 0 r[10]='CREATE VIEW v_t1_1780 AS SELECT x FROM t1' 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 1 1 1 0 26 ParseSchema 1 0 0 tbl_name='v_t1_1780' AND type!='trigger' 0 27 Halt 0 0 0 0 28 Transaction 1 1 0 0 1 usesStmtJournal=1 29 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 37 0 0 Start at 37 1 Noop 0 36 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 idx_t1_9644 0 r[4]='idx_t1_9644' 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 idx_t1_9644 ON t1((x + 1)) 0 r[7]='CREATE UNIQUE INDEX idx_t1_9644 ON t1((x + 1))' 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 1 0 root=2 iDb=0; t1 14 Rewind 1 21 0 0 15 Column 1 0 12 0 r[12]=t1.x 16 Add 13 12 10 0 r[10]=r[13]+r[12] 17 Rowid 1 11 0 0 r[11]=t1.rowid 18 MakeRecord 10 2 9 0 r[9]=mkrec(r[10..11]) 19 SorterInsert 3 9 0 0 key=r[9] 20 Next 1 15 0 0 21 OpenWrite 2 1 0 k(2,,) 17 root=1 iDb=0 22 SorterSort 3 30 0 0 23 Goto 0 26 0 0 24 SorterCompare 3 23 9 1 0 if key(3)!=trim(r[9],1) goto 23 25 Halt 2067 2 0 index 'idx_t1_9644' 2 26 SorterData 3 9 2 0 r[9]=data 27 SeekEnd 2 0 0 0 28 IdxInsert 2 9 0 16 key=r[9] 29 SorterNext 3 24 0 0 30 Close 1 0 0 0 31 Close 2 0 0 0 32 Close 3 0 0 0 33 SetCookie 0 1 2 0 34 ParseSchema 0 0 0 name='idx_t1_9644' AND type='index' 0 35 Expire 0 1 0 0 36 Halt 0 0 0 0 37 Transaction 0 1 1 0 1 usesStmtJournal=1 38 Integer 1 13 0 0 r[13]=1 39 Goto 0 1 0 0 QUERY PLAN `--SCAN t1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 7 0 0 Start at 7 1 OpenRead 1 2 0 1 0 root=2 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 2 0 1 usesStmtJournal=0 8 Goto 0 1 0 0 1 QUERY PLAN `--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 26 0 0 Start at 26 1 Null 0 4 5 0 r[4..5]=NULL 2 Noop 2 0 4 0 3 OpenWrite 0 2 0 1 0 root=2 iDb=0; t1 4 Integer 1 7 0 0 r[7]=1 5 SeekRowid 0 7 7 0 intkey=r[7] 6 Rowid 0 5 0 0 r[5]=[P2]=PX rowid of P1 7 OpenWrite 1 3 0 k(2,,) 0 root=3 iDb=0; idx_t1_9644 8 IsNull 5 25 0 0 if r[5]==NULL goto 25 9 Null 0 6 0 0 r[6]=NULL 10 Noop 0 0 0 0 prep index idx_t1_9644 11 Add 9 6 2 0 r[2]=r[9]+r[6]; idx_t1_9644 column 0 12 IntCopy 5 3 0 0 r[3]=r[5]; rowid 13 MakeRecord 2 2 1 0 r[1]=mkrec(r[2..3]); for idx_t1_9644 14 NoConflict 1 18 2 1 0 key=r[2] 15 IdxRowid 1 8 0 0 r[8]=rowid 16 Eq 8 18 5 144 if r[5]==r[8] goto 18 17 Halt 2067 2 0 index 'idx_t1_9644' 2 18 MakeRecord 6 1 4 0 r[4]=mkrec(r[6]) 19 Column 0 0 8 0 r[8]=t1.x 20 Add 9 8 10 0 r[10]=r[9]+r[8] 21 Rowid 0 11 0 0 r[11]=t1.rowid 22 IdxDelete 1 10 2 1 key=r[10..11] 23 IdxInsert 1 1 2 2 0 key=r[1] 24 Insert 0 4 5 t1 5 intkey=r[5] data=r[4] 25 Halt 0 0 0 0 26 Transaction 0 1 2 0 1 usesStmtJournal=0 27 Integer 1 9 0 0 r[9]=1 28 Goto 0 1 0 0 QUERY PLAN |--SCAN t1 `--SEARCH t1 USING AUTOMATIC COVERING INDEX (x=?) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 25 0 0 Start at 25 1 OpenRead 1 2 0 1 0 root=2 iDb=0; t1 2 OpenRead 3 2 0 1 0 root=2 iDb=0; t1 3 Rewind 1 24 0 0 4 Once 0 14 0 0 5 OpenAutoindex 4 2 0 k(2,B,) 0 nColumn=2; for t1 6 Blob 10000 1 0 0 r[1]= (len=10000) 7 Rewind 3 14 0 0 8 Column 3 0 3 0 r[3]=t1.x 9 Rowid 3 4 0 0 r[4]=t1.rowid 10 MakeRecord 3 2 2 0 r[2]=mkrec(r[3..4]) 11 FilterAdd 1 0 3 1 0 filter(1) += key(3) 12 IdxInsert 4 2 0 16 key=r[2] 13 Next 3 8 0 3 14 Column 1 0 5 0 r[5]=t1.x 15 IsNull 5 23 0 0 if r[5]==NULL goto 23 16 Filter 1 23 5 1 0 if key(5) not in filter(1) goto 23 17 SeekGE 4 23 5 1 0 key=r[5] 18 IdxGT 4 23 5 1 0 key=r[5] 19 Column 1 0 6 0 r[6]=t1.x 20 Column 4 0 7 0 r[7]=t1.x 21 ResultRow 6 2 0 0 output=r[6..7] 22 Next 4 18 0 0 23 Next 1 4 0 1 24 Halt 0 0 0 0 25 Transaction 0 0 2 0 1 usesStmtJournal=0 26 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 37 0 0 Start at 37 1 Noop 0 36 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 idx_t1_3677 0 r[4]='idx_t1_3677' 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 idx_t1_3677 ON t1(lower(x)) 0 r[7]='CREATE UNIQUE INDEX idx_t1_3677 ON t1(lower(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 1 k(2,,) 0 13 OpenRead 1 2 0 1 0 root=2 iDb=0; t1 14 Rewind 1 21 0 0 15 Column 1 0 12 0 r[12]=t1.x 16 PureFunc 0 12 10 lower(1) 32 r[10]=func(r[12]) 17 Rowid 1 11 0 0 r[11]=t1.rowid 18 MakeRecord 10 2 9 0 r[9]=mkrec(r[10..11]) 19 SorterInsert 3 9 0 0 key=r[9] 20 Next 1 15 0 0 21 OpenWrite 2 1 0 k(2,,) 17 root=1 iDb=0 22 SorterSort 3 30 0 0 23 Goto 0 26 0 0 24 SorterCompare 3 23 9 1 0 if key(3)!=trim(r[9],1) goto 23 25 Halt 2067 2 0 index 'idx_t1_3677' 2 26 SorterData 3 9 2 0 r[9]=data 27 SeekEnd 2 0 0 0 28 IdxInsert 2 9 0 16 key=r[9] 29 SorterNext 3 24 0 0 30 Close 1 0 0 0 31 Close 2 0 0 0 32 Close 3 0 0 0 33 SetCookie 0 1 3 0 34 ParseSchema 0 0 0 name='idx_t1_3677' AND type='index' 0 35 Expire 0 1 0 0 36 Halt 0 0 0 0 37 Transaction 0 1 2 0 1 usesStmtJournal=1 38 Goto 0 1 0 0 QUERY PLAN `--SCAN t1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 35 0 0 Start at 35 1 Null 0 1 0 0 r[1]=NULL 2 OpenRead 0 2 0 1 0 root=2 iDb=0; t1 3 Rewind 0 9 0 0 4 Column 0 0 2 0 r[2]=t1.x 5 NotNull 2 8 0 0 if r[2]!=NULL goto 8 6 Rowid 0 3 0 0 r[3]=t1.rowid 7 RowSetAdd 1 3 0 0 rowset(1)=r[3] 8 Next 0 4 0 1 9 OpenWrite 0 2 0 1 0 root=2 iDb=0; t1 10 OpenWrite 1 4 0 k(2,,) 8 root=4 iDb=0; idx_t1_3677 11 OpenWrite 2 3 0 k(2,,) 8 root=3 iDb=0; idx_t1_9644 12 RowSetRead 1 29 3 0 r[3]=rowset(1) 13 NotExists 0 28 3 1 0 intkey=r[3] 14 Copy 3 4 0 0 r[4]=r[3] 15 Column 0 0 5 0 r[5]=t1.x 16 Column 0 0 2 0 r[2]=t1.x 17 PureFunc 0 2 6 lower(1) 32 r[6]=func(r[2]) 18 Rowid 0 7 0 0 r[7]=t1.rowid 19 IdxDelete 1 6 2 1 key=r[6..7] 20 Column 0 0 2 0 r[2]=t1.x 21 Add 8 2 6 0 r[6]=r[8]+r[2] 22 IdxDelete 2 6 2 1 key=r[6..7] 23 Delete 0 1 0 t1 0 24 SCopy 5 9 0 0 r[9]=r[5] 25 MakeRecord 9 1 10 0 r[10]=mkrec(r[9]) 26 NewRowid 4 11 0 0 r[11]=rowid 27 Insert 4 10 11 0 intkey=r[11] data=r[10] 28 Goto 0 12 0 0 29 FkCheck 0 0 0 0 30 Rewind 4 34 0 0 31 Column 4 0 9 0 r[9]=[P3]=PX cursor P1 column P2 32 ResultRow 9 1 0 0 output=r[9] 33 Next 4 31 0 0 34 Halt 0 0 0 0 35 Transaction 0 1 3 0 1 usesStmtJournal=0 36 Integer 1 8 0 0 r[8]=1 37 OpenEphemeral 4 1 0 0 nColumn=1 38 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 24 0 0 Start at 24 1 OpenWrite 0 2 0 1 0 root=2 iDb=0; t1 2 OpenWrite 1 4 0 k(2,,) 0 root=4 iDb=0; idx_t1_3677 3 OpenWrite 2 3 0 k(2,,) 0 root=3 iDb=0; idx_t1_9644 4 Null 0 2 0 0 r[2]=NULL 5 NewRowid 0 1 0 0 r[1]=rowid 6 Noop 0 0 0 0 prep index idx_t1_3677 7 Copy 2 10 0 0 r[10]=r[2] 8 PureFunc 0 10 4 lower(1) 32 r[4]=func(r[10]); idx_t1_3677 column 0 9 IntCopy 1 5 0 0 r[5]=r[1]; rowid 10 MakeRecord 4 2 3 0 r[3]=mkrec(r[4..5]); for idx_t1_3677 11 NoConflict 1 13 4 1 0 key=r[4] 12 Halt 2067 2 0 index 'idx_t1_3677' 2 13 Noop 0 0 0 0 prep index idx_t1_9644 14 Add 11 2 7 0 r[7]=r[11]+r[2]; idx_t1_9644 column 0 15 IntCopy 1 8 0 0 r[8]=r[1]; rowid 16 MakeRecord 7 2 6 0 r[6]=mkrec(r[7..8]); for idx_t1_9644 17 NoConflict 2 19 7 1 0 key=r[7] 18 Halt 2067 2 0 index 'idx_t1_9644' 2 19 MakeRecord 2 1 9 0 r[9]=mkrec(r[2]) 20 IdxInsert 1 3 4 2 16 key=r[3] 21 IdxInsert 2 6 7 2 16 key=r[6] 22 Insert 0 9 1 t1 57 intkey=r[1] data=r[9] 23 Halt 0 0 0 0 24 Transaction 0 1 3 0 1 usesStmtJournal=0 25 Integer 1 11 0 0 r[11]=1 26 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 |--SCAN t1 `--CORRELATED SCALAR SUBQUERY 1 `--SCAN t2 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 25 0 0 Start at 25 1 OpenRead 0 2 0 1 0 root=2 iDb=0; t1 2 Rewind 0 24 0 0 3 Column 0 0 1 0 r[1]=t1.x 4 BeginSubrtn 0 3 0 0 r[3]=NULL 5 Null 0 4 4 0 r[4..4]=NULL; Init subquery result 6 Integer 1 5 0 0 r[5]=1 7 Ne 7 9 6 67 if r[6]!=r[7] goto 9 8 ZeroOrNull 6 5 7 0 r[5] = 0 OR NULL 9 MustBeInt 5 0 0 0 LIMIT counter 10 IfNot 5 19 0 0 11 OpenRead 1 2 0 1 0 root=2 iDb=0; t1 12 Rewind 1 19 0 0 13 Column 1 0 8 0 r[8]=t1.x 14 Column 0 0 9 0 r[9]=t1.x 15 Ne 9 18 8 BINARY-8 81 if r[8]!=r[9] goto 18 16 Column 1 0 4 0 r[4]=t1.x 17 DecrJumpZero 5 19 0 0 if (--r[5])==0 goto 19 18 Next 1 13 0 1 19 Return 3 5 1 0 20 Ne 4 23 1 BINARY-8 81 if r[1]!=r[4] goto 23 21 Column 0 0 10 0 r[10]=t1.x 22 ResultRow 10 1 0 0 output=r[10] 23 Next 0 3 0 1 24 Halt 0 0 0 0 25 Transaction 0 0 3 0 1 usesStmtJournal=0 26 Integer 1 6 0 0 r[6]=1 27 Integer 0 7 0 0 r[7]=0 28 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 79 0 0 Start at 79 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 4 0 4 0 14 OpenWrite 3 1 0 5 0 root=1 iDb=0; sqlite_master 15 SeekRowid 3 17 1 0 intkey=r[1] 16 Rowid 3 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 sqlite_stat1 0 r[7]='sqlite_stat1' 20 String8 0 8 0 sqlite_stat1 0 r[8]='sqlite_stat1' 21 SCopy 2 9 0 0 r[9]=r[2] 22 String8 0 10 0 CREATE TABLE sqlite_stat1(tbl,idx,stat) 0 r[10]='CREATE TABLE sqlite_stat1(tbl,idx,stat)' 23 MakeRecord 6 5 4 BBBDB 0 r[4]=mkrec(r[6..10]) 24 Insert 3 4 5 0 intkey=r[5] data=r[4] 25 SetCookie 0 1 4 0 26 ParseSchema 0 0 0 tbl_name='sqlite_stat1' AND type!='trigger' 0 27 OpenWrite 0 2 0 3 16 root=2 iDb=0; sqlite_stat1 28 OpenRead 5 2 0 1 0 root=2 iDb=0; t1 29 String8 0 18 0 t1 0 r[18]='t1' 30 String8 0 19 0 idx_t1_3677 0 r[19]='idx_t1_3677'; Analysis for t1.idx_t1_3677 31 OpenRead 6 4 0 k(2,,) 0 root=4 iDb=0; idx_t1_3677 32 Integer 2 14 0 0 r[14]=2 33 Integer 1 15 0 0 r[15]=1 34 Rewind 6 53 0 0 35 Count 6 16 1 0 r[16]=count() 36 Integer 0 17 0 0 r[17]=0 37 Function 0 14 13 stat_init(4) 0 r[13]=func(r[14..17]) 38 Integer 0 14 0 0 r[14]=0 39 Goto 0 46 0 0 40 NotNull 21 47 0 0 if r[21]!=NULL goto 47 41 Integer 0 14 0 0 r[14]=0 42 Column 6 0 16 0 r[16]=idx_t1_3677.expr(0) 43 Ne 16 46 21 BINARY-8 128 if r[21]!=r[16] goto 46 44 Integer 1 14 0 0 r[14]=1 45 Goto 0 47 0 0 46 Column 6 0 21 0 r[21]=idx_t1_3677.expr(0) 47 Function 1 13 16 stat_push(2) 0 r[16]=func(r[13..14]) 48 Next 6 40 0 0 49 Function 0 13 20 stat_get(1) 0 r[20]=func(r[13]) 50 MakeRecord 18 3 16 BBB 0 r[16]=mkrec(r[18..20]) 51 NewRowid 0 12 0 0 r[12]=rowid 52 Insert 0 16 12 8 intkey=r[12] data=r[16] 53 String8 0 19 0 idx_t1_9644 0 r[19]='idx_t1_9644'; Analysis for t1.idx_t1_9644 54 OpenRead 6 3 0 k(2,,) 0 root=3 iDb=0; idx_t1_9644 55 Integer 2 14 0 0 r[14]=2 56 Integer 1 15 0 0 r[15]=1 57 Rewind 6 76 0 0 58 Count 6 16 1 0 r[16]=count() 59 Integer 0 17 0 0 r[17]=0 60 Function 0 14 13 stat_init(4) 0 r[13]=func(r[14..17]) 61 Integer 0 14 0 0 r[14]=0 62 Goto 0 69 0 0 63 NotNull 21 70 0 0 if r[21]!=NULL goto 70 64 Integer 0 14 0 0 r[14]=0 65 Column 6 0 16 0 r[16]=idx_t1_9644.expr(0) 66 Ne 16 69 21 BINARY-8 128 if r[21]!=r[16] goto 69 67 Integer 1 14 0 0 r[14]=1 68 Goto 0 70 0 0 69 Column 6 0 21 0 r[21]=idx_t1_9644.expr(0) 70 Function 1 13 16 stat_push(2) 0 r[16]=func(r[13..14]) 71 Next 6 63 0 0 72 Function 0 13 20 stat_get(1) 0 r[20]=func(r[13]) 73 MakeRecord 18 3 16 BBB 0 r[16]=mkrec(r[18..20]) 74 NewRowid 0 12 0 0 r[12]=rowid 75 Insert 0 16 12 8 intkey=r[12] data=r[16] 76 LoadAnalysis 0 0 0 0 77 Expire 0 0 0 0 78 Halt 0 0 0 0 79 Transaction 0 1 3 0 1 usesStmtJournal=1 80 Goto 0 1 0 0 QUERY PLAN `--SCAN t1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 7 0 0 Start at 7 1 OpenRead 0 2 0 1 0 root=2 iDb=0; t1 2 Rewind 0 6 0 0 3 Column 0 0 1 0 r[1]=t1.x 4 ResultRow 1 1 0 0 output=r[1] 5 Next 0 3 0 1 6 Halt 0 0 0 0 7 Transaction 0 0 4 0 1 usesStmtJournal=0 8 Goto 0 1 0 0 QUERY PLAN `--SCAN t1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 7 0 0 Start at 7 1 OpenRead 0 2 0 1 0 root=2 iDb=0; t1 2 Rewind 0 6 0 0 3 Column 0 0 1 0 r[1]=t1.x 4 ResultRow 1 1 0 0 output=r[1] 5 Next 0 3 0 1 6 Halt 0 0 0 0 7 Transaction 0 0 4 0 1 usesStmtJournal=0 8 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 1 0 0 Start at 1 1 Null 0 1 0 0 r[1]=NULL 2 Null 0 2 0 0 r[2]=NULL 3 String8 0 3 0 aux30 0 r[3]='aux30' 4 Function 0 3 4 sqlite_detach(1) 0 r[4]=func(r[3]) 5 Expire 0 0 0 0 6 Halt 0 0 0 0 ``` ## Expectation ```sql addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 4 0 0 Start at 4 1 Expire 1 1 0 0 2 Expire 0 0 0 0 3 Halt 0 0 0 0 4 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 3 0 0 Start at 3 1 Expire 1 1 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 4 0 0 Start at 4 1 Expire 1 1 0 0 2 Expire 0 0 0 0 3 Halt 0 0 0 0 4 Goto 0 1 0 0 QUERY PLAN `--SCAN CONSTANT ROW addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 5 0 0 Start at 5 1 Explain 1 0 0 SCAN CONSTANT ROW 0 2 Integer 1 1 0 0 r[1]=1 3 ResultRow 1 1 0 0 output=r[1] 4 Halt 0 0 0 0 5 Goto 0 1 0 0 1 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](x) 0 r[10]='CREATE TABLE [t1](x)' 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 7 0 0 Start at 7 1 OpenWrite 0 2 0 1 0 root=2 iDb=0; t1 2 Subtract 5 4 2 0 r[2]=r[4]-r[5] 3 NewRowid 0 1 0 0 r[1]=rowid 4 MakeRecord 2 1 3 0 r[3]=mkrec(r[2]) 5 Insert 0 3 1 t1 57 intkey=r[1] data=r[3] 6 Halt 0 0 0 0 7 Transaction 0 1 1 0 1 usesStmtJournal=0 8 Integer 0 4 0 0 r[4]=0 9 Integer 0 7 0 0 r[7]=0 10 Integer 0 9 0 0 r[9]=0 11 Integer 0 11 0 0 r[11]=0 12 Integer 0 13 0 0 r[13]=0 13 Real 0 14 0 9.223372036854776e+18 0 r[14]=9.223372036854776e+18 14 Add 14 13 12 0 r[12]=r[14]+r[13] 15 Subtract 12 11 10 0 r[10]=r[11]-r[12] 16 Subtract 10 9 8 0 r[8]=r[9]-r[10] 17 Subtract 8 7 6 0 r[6]=r[7]-r[8] 18 Integer 0 8 0 0 r[8]=0 19 Add 8 6 5 0 r[5]=r[8]+r[6] 20 Goto 0 1 0 0 QUERY PLAN |--SCAN t1 `--CORRELATED SCALAR SUBQUERY 2 |--CO-ROUTINE (subquery-1) | `--SCAN CONSTANT ROW `--SCAN (subquery-1) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 31 0 0 Start at 31 1 Null 0 1 2 0 r[1..2]=NULL 2 OpenRead 0 2 0 1 0 root=2 iDb=0; t1 3 Explain 3 0 216 SCAN t1 0 4 Rewind 0 27 0 0 5 BeginSubrtn 0 4 0 0 r[4]=NULL 6 Explain 6 0 0 CORRELATED SCALAR SUBQUERY 2 0 7 Null 0 5 5 0 r[5..5]=NULL; Init subquery result 8 InitCoroutine 6 15 9 0 (subquery-1) 9 Explain 9 6 0 CO-ROUTINE (subquery-1) 0 10 Explain 10 9 0 SCAN CONSTANT ROW 0 11 Goto 0 14 0 0 12 Integer 2 7 0 0 r[7]=2 13 Yield 6 0 0 0 14 EndCoroutine 6 0 0 0 end (subquery-1) 15 Integer 1 8 0 0 r[8]=1; LIMIT counter 16 Goto 0 23 0 0 17 Explain 17 6 64 SCAN (subquery-1) 0 18 InitCoroutine 6 0 9 0 19 Yield 6 23 0 0 next row of (subquery-1) 20 Integer 1 5 0 0 r[5]=1 21 DecrJumpZero 8 23 0 0 if (--r[8])==0 goto 23 22 Goto 0 19 0 0 23 Return 4 6 1 0 24 Copy 5 3 0 0 r[3]=r[5] 25 AggStep 0 3 2 sum(1) 1 accum=r[2] step(r[3]) 26 Next 0 5 0 1 27 AggFinal 2 1 0 sum(1) 0 accum=r[2] N=1 28 Copy 2 9 0 0 r[9]=r[2] 29 ResultRow 9 1 0 0 output=r[9] 30 Halt 0 0 0 0 31 Transaction 0 0 1 0 1 usesStmtJournal=0 32 Goto 0 1 0 0 QUERY PLAN `--SCAN t1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 8 0 0 Start at 8 1 OpenRead 1 2 0 1 0 root=2 iDb=0 2 Count 1 1 0 0 r[1]=count() 3 Close 1 0 0 0 4 Explain 4 0 0 SCAN t1 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 1 0 1 usesStmtJournal=0 9 Goto 0 1 0 0 1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 28 0 0 Start at 28 1 ReadCookie 1 3 2 0 2 If 3 5 0 0 3 SetCookie 1 2 4 0 4 SetCookie 1 5 1 0 5 Integer 0 2 0 0 r[2]=0 6 OpenWrite 0 1 1 5 0 root=1 iDb=1 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 1 5 0 root=1 iDb=1; sqlite_temp_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 view 0 r[6]='view' 19 String8 0 7 0 v_t1_1780 0 r[7]='v_t1_1780' 20 String8 0 8 0 v_t1_1780 0 r[8]='v_t1_1780' 21 Copy 2 9 0 0 r[9]=r[2] 22 String8 0 10 0 CREATE VIEW v_t1_1780 AS SELECT x FROM t1 0 r[10]='CREATE VIEW v_t1_1780 AS SELECT x FROM t1' 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 1 1 1 0 26 ParseSchema 1 0 0 tbl_name='v_t1_1780' AND type!='trigger' 0 27 Halt 0 0 0 0 28 Transaction 1 1 0 0 1 usesStmtJournal=1 29 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 37 0 0 Start at 37 1 Noop 0 36 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 idx_t1_9644 0 r[4]='idx_t1_9644' 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 idx_t1_9644 ON t1((x + 1)) 0 r[7]='CREATE UNIQUE INDEX idx_t1_9644 ON t1((x + 1))' 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 1 0 root=2 iDb=0; t1 14 Rewind 1 21 0 0 15 Column 1 0 12 0 r[12]= cursor 1 column 0 16 Add 13 12 10 0 r[10]=r[13]+r[12] 17 Rowid 1 11 0 0 r[11]=t1.rowid 18 MakeRecord 10 2 9 0 r[9]=mkrec(r[10..11]) 19 SorterInsert 3 9 0 0 key=r[9] 20 Next 1 15 0 0 21 OpenWrite 2 1 0 k(2,,) 17 root=1 iDb=0 22 SorterSort 3 30 0 0 23 Goto 0 26 0 0 24 SorterCompare 3 23 9 1 0 if key(3)!=trim(r[9],1) goto 23 25 Halt 2067 2 0 index 'idx_t1_9644' 2 26 SorterData 3 9 2 0 r[9]=data 27 SeekEnd 2 0 0 0 28 IdxInsert 2 9 0 16 key=r[9] 29 SorterNext 3 24 0 0 30 Close 1 0 0 0 31 Close 2 0 0 0 32 Close 3 0 0 0 33 SetCookie 0 1 2 0 34 ParseSchema 0 0 0 name='idx_t1_9644' AND type='index' 0 35 Expire 0 1 0 0 36 Halt 0 0 0 0 37 Transaction 0 1 1 0 1 usesStmtJournal=1 38 Integer 1 13 0 0 r[13]=1 39 Goto 0 1 0 0 QUERY PLAN `--SCAN t1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 8 0 0 Start at 8 1 OpenRead 1 2 0 1 0 root=2 iDb=0 2 Count 1 1 0 0 r[1]=count() 3 Close 1 0 0 0 4 Explain 4 0 0 SCAN t1 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 2 0 1 usesStmtJournal=0 9 Goto 0 1 0 0 1 QUERY PLAN `--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 27 0 0 Start at 27 1 Null 0 4 5 0 r[4..5]=NULL 2 Noop 2 0 4 0 3 OpenWrite 0 2 0 1 0 root=2 iDb=0; t1 4 Explain 4 0 33 SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) 0 5 Integer 1 7 0 0 r[7]=1 6 SeekRowid 0 8 7 0 intkey=r[7] 7 Rowid 0 5 0 0 r[5]= rowid of 0 8 OpenWrite 1 3 0 k(2,,) 0 root=3 iDb=0; idx_t1_9644 9 IsNull 5 26 0 0 if r[5]==NULL goto 26 10 Null 0 6 0 0 r[6]=NULL 11 Noop 0 0 0 0 prep index idx_t1_9644 12 Add 9 6 2 0 r[2]=r[9]+r[6]; idx_t1_9644 column 0 13 IntCopy 5 3 0 0 r[3]=r[5]; rowid 14 MakeRecord 2 2 1 0 r[1]=mkrec(r[2..3]); for idx_t1_9644 15 NoConflict 1 19 2 1 0 key=r[2] 16 IdxRowid 1 8 0 0 r[8]=rowid 17 Eq 8 19 5 144 if r[5]==r[8] goto 19 18 Halt 2067 2 0 index 'idx_t1_9644' 2 19 MakeRecord 6 1 4 0 r[4]=mkrec(r[6]) 20 Column 0 0 8 0 r[8]= cursor 0 column 0 21 Add 9 8 10 0 r[10]=r[9]+r[8] 22 Rowid 0 11 0 0 r[11]=t1.rowid 23 IdxDelete 1 10 2 1 key=r[10..11] 24 IdxInsert 1 1 2 2 0 key=r[1] 25 Insert 0 4 5 t1 5 intkey=r[5] data=r[4] 26 Halt 0 0 0 0 27 Transaction 0 1 2 0 1 usesStmtJournal=0 28 Integer 1 9 0 0 r[9]=1 29 Goto 0 1 0 0 QUERY PLAN |--SCAN t1 |--BLOOM FILTER ON t1 (x=?) `--SEARCH t1 USING AUTOMATIC COVERING INDEX (x=?) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 28 0 0 Start at 28 1 OpenRead 1 2 0 1 0 root=2 iDb=0; t1 2 OpenRead 3 2 0 1 0 root=2 iDb=0; t1 3 Explain 3 0 216 SCAN t1 0 4 Rewind 1 27 0 0 5 Once 0 16 0 0 6 OpenAutoindex 4 2 0 k(2,B,) 0 nColumn=2; for t1 7 Explain 7 0 0 BLOOM FILTER ON t1 (x=?) 0 8 Blob 10000 1 0 0 r[1]= (len=10000) 9 Rewind 3 27 0 0 10 Column 3 0 3 0 r[3]= cursor 3 column 0 11 Rowid 3 4 0 0 r[4]=t1.rowid 12 MakeRecord 3 2 2 0 r[2]=mkrec(r[3..4]) 13 FilterAdd 1 0 3 1 0 filter(1) += key(3) 14 IdxInsert 4 2 0 16 key=r[2] 15 Next 3 10 0 3 16 Explain 16 0 53 SEARCH t1 USING AUTOMATIC COVERING INDEX (x=?) 0 17 Column 1 0 5 0 r[5]= cursor 1 column 0 18 IsNull 5 26 0 0 if r[5]==NULL goto 26 19 Filter 1 26 5 1 0 if key(5) not in filter(1) goto 26 20 SeekGE 4 26 5 1 0 key=r[5] 21 IdxGT 4 26 5 1 0 key=r[5] 22 Column 1 0 6 0 r[6]= cursor 1 column 0 23 Column 4 0 7 0 r[7]= cursor 4 column 0 24 ResultRow 6 2 0 0 output=r[6..7] 25 Next 4 21 0 0 26 Next 1 5 0 1 27 Halt 0 0 0 0 28 Transaction 0 0 2 0 1 usesStmtJournal=0 29 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 37 0 0 Start at 37 1 Noop 0 36 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 idx_t1_3677 0 r[4]='idx_t1_3677' 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 idx_t1_3677 ON t1(lower(x)) 0 r[7]='CREATE UNIQUE INDEX idx_t1_3677 ON t1(lower(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 1 k(2,,) 0 13 OpenRead 1 2 0 1 0 root=2 iDb=0; t1 14 Rewind 1 21 0 0 15 Column 1 0 12 0 r[12]= cursor 1 column 0 16 PureFunc 0 12 10 lower(1) 32 r[10]=func(r[12]) 17 Rowid 1 11 0 0 r[11]=t1.rowid 18 MakeRecord 10 2 9 0 r[9]=mkrec(r[10..11]) 19 SorterInsert 3 9 0 0 key=r[9] 20 Next 1 15 0 0 21 OpenWrite 2 1 0 k(2,,) 17 root=1 iDb=0 22 SorterSort 3 30 0 0 23 Goto 0 26 0 0 24 SorterCompare 3 23 9 1 0 if key(3)!=trim(r[9],1) goto 23 25 Halt 2067 2 0 index 'idx_t1_3677' 2 26 SorterData 3 9 2 0 r[9]=data 27 SeekEnd 2 0 0 0 28 IdxInsert 2 9 0 16 key=r[9] 29 SorterNext 3 24 0 0 30 Close 1 0 0 0 31 Close 2 0 0 0 32 Close 3 0 0 0 33 SetCookie 0 1 3 0 34 ParseSchema 0 0 0 name='idx_t1_3677' AND type='index' 0 35 Expire 0 1 0 0 36 Halt 0 0 0 0 37 Transaction 0 1 2 0 1 usesStmtJournal=1 38 Goto 0 1 0 0 QUERY PLAN `--SCAN t1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 36 0 0 Start at 36 1 Null 0 1 0 0 r[1]=NULL 2 OpenRead 0 2 0 1 0 root=2 iDb=0; t1 3 Explain 3 0 216 SCAN t1 0 4 Rewind 0 10 0 0 5 Column 0 0 2 128 r[2]= cursor 0 column 0 6 NotNull 2 9 0 0 if r[2]!=NULL goto 9 7 Rowid 0 3 0 0 r[3]=t1.rowid 8 RowSetAdd 1 3 0 0 rowset(1)=r[3] 9 Next 0 5 0 1 10 OpenWrite 0 2 0 1 0 root=2 iDb=0; t1 11 OpenWrite 1 4 0 k(2,,) 8 root=4 iDb=0; idx_t1_3677 12 OpenWrite 2 3 0 k(2,,) 8 root=3 iDb=0; idx_t1_9644 13 RowSetRead 1 30 3 0 r[3]=rowset(1) 14 NotExists 0 29 3 1 0 intkey=r[3] 15 Copy 3 4 0 0 r[4]=r[3] 16 Column 0 0 5 0 r[5]= cursor 0 column 0 17 Column 0 0 2 0 r[2]= cursor 0 column 0 18 PureFunc 0 2 6 lower(1) 32 r[6]=func(r[2]) 19 Rowid 0 7 0 0 r[7]=t1.rowid 20 IdxDelete 1 6 2 1 key=r[6..7] 21 Column 0 0 2 0 r[2]= cursor 0 column 0 22 Add 8 2 6 0 r[6]=r[8]+r[2] 23 IdxDelete 2 6 2 1 key=r[6..7] 24 Delete 0 1 0 t1 0 25 Copy 5 9 0 0 r[9]=r[5] 26 MakeRecord 9 1 10 0 r[10]=mkrec(r[9]) 27 NewRowid 4 11 0 0 r[11]=rowid 28 Insert 4 10 11 0 intkey=r[11] data=r[10] 29 Goto 0 13 0 0 30 FkCheck 0 0 0 0 31 Rewind 4 35 0 0 32 Column 4 0 9 0 r[9]= cursor 4 column 0 33 ResultRow 9 1 0 0 output=r[9] 34 Next 4 32 0 0 35 Halt 0 0 0 0 36 Transaction 0 1 3 0 1 usesStmtJournal=1 37 Integer 1 8 0 0 r[8]=1 38 OpenEphemeral 4 1 0 0 nColumn=1 39 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 24 0 0 Start at 24 1 OpenWrite 0 2 0 1 0 root=2 iDb=0; t1 2 OpenWrite 1 4 0 k(2,,) 0 root=4 iDb=0; idx_t1_3677 3 OpenWrite 2 3 0 k(2,,) 0 root=3 iDb=0; idx_t1_9644 4 Null 0 2 0 0 r[2]=NULL 5 NewRowid 0 1 0 0 r[1]=rowid 6 Noop 0 0 0 0 prep index idx_t1_3677 7 SCopy 2 10 0 0 r[10]=r[2] 8 PureFunc 0 10 4 lower(1) 32 r[4]=func(r[10]); idx_t1_3677 column 0 9 IntCopy 1 5 0 0 r[5]=r[1]; rowid 10 MakeRecord 4 2 3 0 r[3]=mkrec(r[4..5]); for idx_t1_3677 11 NoConflict 1 13 4 1 0 key=r[4] 12 Halt 2067 2 0 index 'idx_t1_3677' 2 13 Noop 0 0 0 0 prep index idx_t1_9644 14 Add 11 2 7 0 r[7]=r[11]+r[2]; idx_t1_9644 column 0 15 IntCopy 1 8 0 0 r[8]=r[1]; rowid 16 MakeRecord 7 2 6 0 r[6]=mkrec(r[7..8]); for idx_t1_9644 17 NoConflict 2 19 7 1 0 key=r[7] 18 Halt 2067 2 0 index 'idx_t1_9644' 2 19 MakeRecord 2 1 9 0 r[9]=mkrec(r[2]) 20 IdxInsert 1 3 4 2 16 key=r[3] 21 IdxInsert 2 6 7 2 16 key=r[6] 22 Insert 0 9 1 t1 57 intkey=r[1] data=r[9] 23 Halt 0 0 0 0 24 Transaction 0 1 3 0 1 usesStmtJournal=0 25 Integer 1 11 0 0 r[11]=1 26 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 |--SCAN t1 `--CORRELATED SCALAR SUBQUERY 1 `--SCAN t2 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 25 0 0 Start at 25 1 OpenRead 0 2 0 1 0 root=2 iDb=0; t1 2 Explain 2 0 216 SCAN t1 0 3 Rewind 0 24 0 0 4 Column 0 0 1 0 r[1]= cursor 0 column 0 5 IsNull 1 23 0 0 if r[1]==NULL goto 23; skip right operand 6 BeginSubrtn 0 3 0 0 r[3]=NULL 7 Explain 7 0 0 CORRELATED SCALAR SUBQUERY 1 0 8 Null 0 4 4 0 r[4..4]=NULL; Init subquery result 9 Integer 1 5 0 0 r[5]=1; LIMIT counter 10 OpenRead 1 2 0 1 0 root=2 iDb=0; t1 11 Explain 11 7 216 SCAN t2 0 12 Rewind 1 19 0 0 13 Column 1 0 6 0 r[6]= cursor 1 column 0 14 Column 0 0 7 0 r[7]= cursor 0 column 0 15 Ne 7 18 6 BINARY-8 81 if r[6]!=r[7] goto 18 16 Column 1 0 4 0 r[4]= cursor 1 column 0 17 DecrJumpZero 5 19 0 0 if (--r[5])==0 goto 19 18 Next 1 13 0 1 19 Return 3 7 1 0 20 Ne 4 23 1 BINARY-8 81 if r[1]!=r[4] goto 23 21 Column 0 0 8 0 r[8]= cursor 0 column 0 22 ResultRow 8 1 0 0 output=r[8] 23 Next 0 4 0 1 24 Halt 0 0 0 0 25 Transaction 0 0 3 0 1 usesStmtJournal=0 26 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 79 0 0 Start at 79 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 4 0 4 0 14 OpenWrite 3 1 0 5 0 root=1 iDb=0; sqlite_master 15 SeekRowid 3 17 1 0 intkey=r[1] 16 Rowid 3 5 0 0 r[5]= rowid of 3 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 sqlite_stat1 0 r[7]='sqlite_stat1' 20 String8 0 8 0 sqlite_stat1 0 r[8]='sqlite_stat1' 21 Copy 2 9 0 0 r[9]=r[2] 22 String8 0 10 0 CREATE TABLE sqlite_stat1(tbl,idx,stat) 0 r[10]='CREATE TABLE sqlite_stat1(tbl,idx,stat)' 23 MakeRecord 6 5 4 BBBDB 0 r[4]=mkrec(r[6..10]) 24 Insert 3 4 5 0 intkey=r[5] data=r[4] 25 SetCookie 0 1 4 0 26 ParseSchema 0 0 0 tbl_name='sqlite_stat1' AND type!='trigger' 0 27 OpenWrite 0 2 0 3 16 root=2 iDb=0; sqlite_stat1 28 OpenRead 5 2 0 1 0 root=2 iDb=0; t1 29 String8 0 18 0 t1 0 r[18]='t1' 30 String8 0 19 0 idx_t1_3677 0 r[19]='idx_t1_3677'; Analysis for t1.idx_t1_3677 31 OpenRead 6 4 0 k(2,,) 0 root=4 iDb=0; idx_t1_3677 32 Integer 0 17 0 0 r[17]=0 33 Integer 2 14 0 0 r[14]=2 34 Integer 1 15 0 0 r[15]=1 35 Count 6 16 0 0 r[16]=count() 36 Function 0 14 13 stat_init(4) 0 r[13]=func(r[14..17]) 37 Rewind 6 53 0 0 38 Integer 0 14 0 0 r[14]=0 39 Goto 0 46 0 0 40 NotNull 21 47 0 0 if r[21]!=NULL goto 47 41 Integer 0 14 0 0 r[14]=0 42 Column 6 0 16 0 r[16]=idx_t1_3677.expr(0) 43 Ne 16 46 21 BINARY-8 128 if r[21]!=r[16] goto 46 44 Integer 1 14 0 0 r[14]=1 45 Goto 0 47 0 0 46 Column 6 0 21 0 r[21]=idx_t1_3677.expr(0) 47 Function 1 13 16 stat_push(2) 0 r[16]=func(r[13..14]) 48 Next 6 40 0 0 49 Function 0 13 20 stat_get(1) 0 r[20]=func(r[13]) 50 MakeRecord 18 3 16 BBB 0 r[16]=mkrec(r[18..20]) 51 NewRowid 0 12 0 0 r[12]=rowid 52 Insert 0 16 12 8 intkey=r[12] data=r[16] 53 String8 0 19 0 idx_t1_9644 0 r[19]='idx_t1_9644'; Analysis for t1.idx_t1_9644 54 OpenRead 6 3 0 k(2,,) 0 root=3 iDb=0; idx_t1_9644 55 Integer 0 17 0 0 r[17]=0 56 Integer 2 14 0 0 r[14]=2 57 Integer 1 15 0 0 r[15]=1 58 Count 6 16 0 0 r[16]=count() 59 Function 0 14 13 stat_init(4) 0 r[13]=func(r[14..17]) 60 Rewind 6 76 0 0 61 Integer 0 14 0 0 r[14]=0 62 Goto 0 69 0 0 63 NotNull 21 70 0 0 if r[21]!=NULL goto 70 64 Integer 0 14 0 0 r[14]=0 65 Column 6 0 16 0 r[16]=idx_t1_9644.expr(0) 66 Ne 16 69 21 BINARY-8 128 if r[21]!=r[16] goto 69 67 Integer 1 14 0 0 r[14]=1 68 Goto 0 70 0 0 69 Column 6 0 21 0 r[21]=idx_t1_9644.expr(0) 70 Function 1 13 16 stat_push(2) 0 r[16]=func(r[13..14]) 71 Next 6 63 0 0 72 Function 0 13 20 stat_get(1) 0 r[20]=func(r[13]) 73 MakeRecord 18 3 16 BBB 0 r[16]=mkrec(r[18..20]) 74 NewRowid 0 12 0 0 r[12]=rowid 75 Insert 0 16 12 8 intkey=r[12] data=r[16] 76 LoadAnalysis 0 0 0 0 77 Expire 0 0 0 0 78 Halt 0 0 0 0 79 Transaction 0 1 3 0 1 usesStmtJournal=1 80 Goto 0 1 0 0 QUERY PLAN `--SCAN t1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 8 0 0 Start at 8 1 OpenRead 0 2 0 1 0 root=2 iDb=0; t1 2 Explain 2 0 16 SCAN t1 0 3 Rewind 0 7 0 0 4 Column 0 0 1 0 r[1]= cursor 0 column 0 5 ResultRow 1 1 0 0 output=r[1] 6 Next 0 4 0 1 7 Halt 0 0 0 0 8 Transaction 0 0 4 0 1 usesStmtJournal=0 9 Goto 0 1 0 0 QUERY PLAN `--SCAN t1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 8 0 0 Start at 8 1 OpenRead 0 2 0 1 0 root=2 iDb=0; t1 2 Explain 2 0 16 SCAN t1 0 3 Rewind 0 7 0 0 4 Column 0 0 1 0 r[1]= cursor 0 column 0 5 ResultRow 1 1 0 0 output=r[1] 6 Next 0 4 0 1 7 Halt 0 0 0 0 8 Transaction 0 0 4 0 1 usesStmtJournal=0 9 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 7 0 0 Start at 7 1 Null 0 1 0 0 r[1]=NULL 2 Null 0 2 0 0 r[2]=NULL 3 String8 0 3 0 aux30 0 r[3]='aux30' 4 Function 0 3 4 sqlite_detach(1) 0 r[4]=func(r[3]) 5 Expire 0 0 0 0 6 Halt 0 0 0 0 7 Goto 0 1 0 0 ``` ## Flag ``` ```