## Summary **No review yet** ## Minimized query ```sql PRAGMA wal_checkpoint('NOOP'); BEGIN EXCLUSIVE; .eqp full .vfsinfo creATE VIRTUAL TABLE t0 USING fts3("x", "y", "z"); DROP TABLE t0; PRAGMA automatic_index = OFF; CREATE TABLE T ( a TEXT, b INTEGER, c REAL ); INSERT INTO T VALUES ('a',1,2.0), ('a',2,-3.0), ('b',1,4.5); SELECT a,b,c, SUM(c) OVER ( PARTITION BY a ORDER BY b ) AS d FROM T; SELECT COUNT(*) OVER (PARTITION BY c ORDER BY c) FROM T; SELECT * FROM (SELECT * FROM (SELECT * FROM T) AS inner1) AS inner2; END; SELECT * FROM T WHERE EXISTS (SELECT 1 FROM T WHERE c > 0); ``` ## Actual output ```sql 0|-1|-1 vfs.zName = "unix" vfs.iVersion = 3 vfs.szOsFile = 120 vfs.mxPathname = 512 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 31 0 0 Start at 31 1 VBegin 0 0 0 0 2 ReadCookie 0 3 2 0 3 If 3 6 0 0 4 SetCookie 0 2 4 0 5 SetCookie 0 5 1 0 6 Integer 0 2 0 0 r[2]=0 7 OpenWrite 0 1 0 5 0 root=1 iDb=0 8 NewRowid 0 1 0 0 r[1]=rowid 9 Blob 6 3 0  0 r[3]= (len=6) 10 Insert 0 3 1 8 intkey=r[1] data=r[3] 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 t0 0 r[7]='t0' 20 String8 0 8 0 t0 0 r[8]='t0' 21 Integer 0 9 0 0 r[9]=0 22 String8 0 10 0 CREATE VIRTUAL TABLE t0 USING fts3("x", "y", "z") 0 r[10]='CREATE VIRTUAL TABLE t0 USING fts3("x", "y", "z")' 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 Expire 0 0 0 0 27 ParseSchema 0 0 0 name='t0' AND sql='CREATE VIRTUAL TABLE t0 USING fts3("x", "y", "z")' 0 28 String8 0 12 0 t0 0 r[12]='t0' 29 VCreate 0 12 0 0 30 Halt 0 0 0 0 31 Transaction 0 1 0 0 1 usesStmtJournal=1 32 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 16 0 0 Start at 16 1 VBegin 0 0 0 0 2 Null 0 1 0 0 r[1]=NULL 3 OpenWrite 0 1 0 5 0 root=1 iDb=0; sqlite_master 4 Rewind 0 12 0 0 5 Column 0 2 2 0 r[2]=sqlite_master.tbl_name 6 Ne 3 11 2 BINARY-8 82 if r[2]!=r[3] goto 11 7 Column 0 0 2 0 r[2]=sqlite_master.type 8 Eq 4 11 2 BINARY-8 82 if r[2]==r[4] goto 11 9 Rowid 0 5 0 0 r[5]=sqlite_master.rowid 10 Delete 0 0 0 2 11 Next 0 5 0 1 12 VDestroy 0 0 0 t0 0 13 DropTable 0 0 0 t0 0 14 SetCookie 0 1 5 0 15 Halt 0 0 0 0 16 Transaction 0 1 4 0 1 usesStmtJournal=1 17 String8 0 3 0 t0 0 r[3]='t0' 18 String8 0 4 0 trigger 0 r[4]='trigger' 19 Goto 0 1 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 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 T 0 r[7]='T' 20 String8 0 8 0 T 0 r[8]='T' 21 SCopy 2 9 0 0 r[9]=r[2] 22 String8 0 10 0 CREATE TABLE T ( a TEXT, b INTEGER, c REAL ) 0 r[10]='CREATE TABLE T ( a TEXT, b INTEGER, c REAL )' 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 6 0 26 ParseSchema 0 0 0 tbl_name='T' AND type!='trigger' 0 27 Halt 0 0 0 0 28 Transaction 0 1 5 0 1 usesStmtJournal=1 29 Goto 0 1 0 0 QUERY PLAN `--SCAN 3 CONSTANT ROWS addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 22 0 0 Start at 22 1 InitCoroutine 5 15 2 0 2 String8 0 2 0 a 0 r[2]='a' 3 Integer 1 3 0 0 r[3]=1 4 Real 0 4 0 2 0 r[4]=2 5 Yield 5 0 0 0 6 String8 0 2 0 a 0 r[2]='a' 7 Integer 2 3 0 0 r[3]=2 8 Real 0 4 0 -3 0 r[4]=-3 9 Yield 5 0 0 0 10 String8 0 2 0 b 0 r[2]='b' 11 Integer 1 3 0 0 r[3]=1 12 Real 0 4 0 4.5 0 r[4]=4.5 13 Yield 5 0 0 0 14 EndCoroutine 5 0 0 0 15 OpenWrite 0 2 0 3 0 root=2 iDb=0; T 16 Yield 5 21 0 0 17 NewRowid 0 1 0 0 r[1]=rowid 18 MakeRecord 2 3 6 BDE 0 r[6]=mkrec(r[2..4]) 19 Insert 0 6 1 T 57 intkey=r[1] data=r[6] 20 Goto 0 16 0 0 21 Halt 0 0 0 0 22 Transaction 0 1 6 0 1 usesStmtJournal=0 23 Goto 0 1 0 0 QUERY PLAN |--CO-ROUTINE (subquery-2) | |--SCAN T | `--USE TEMP B-TREE FOR ORDER BY `--SCAN (subquery-2) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 112 0 0 Start at 112 1 Null 0 1 0 0 r[1]=NULL 2 InitCoroutine 3 29 3 0 (subquery-2) 3 SorterOpen 6 9 0 k(2,B,B) 0 4 OpenRead 0 2 0 3 0 root=2 iDb=0; T 5 Rewind 0 17 0 0 6 Column 0 0 6 0 r[6]=T.a 7 Column 0 1 7 0 r[7]=T.b 8 Column 0 2 8 0 r[8]=T.c 9 RealAffinity 8 0 0 0 10 Column 0 2 9 0 r[9]=T.c 11 RealAffinity 9 0 0 0 12 Column 0 0 4 0 r[4]=T.a 13 Column 0 1 5 0 r[5]=T.b 14 MakeRecord 4 6 12 0 r[12]=mkrec(r[4..9]) 15 SorterInsert 6 12 4 6 0 key=r[12] 16 Next 0 6 0 1 17 OpenPseudo 7 13 9 0 9 columns in r[13] 18 SorterSort 6 28 0 0 19 SorterData 6 13 7 0 r[13]=data 20 Column 7 5 11 0 r[11]=[P3]=PX cursor P1 column P2 21 Column 7 1 10 0 r[10]=[P3]=PX cursor P1 column P2 22 Column 7 0 9 0 r[9]=[P3]=PX cursor P1 column P2 23 Column 7 4 8 0 r[8]=[P3]=PX cursor P1 column P2 24 Column 7 3 7 0 r[7]=[P3]=PX cursor P1 column P2 25 Column 7 2 6 0 r[6]=[P3]=PX cursor P1 column P2 26 Yield 3 0 0 0 27 SorterNext 6 19 0 0 28 EndCoroutine 3 0 0 0 29 OpenEphemeral 1 6 0 0 nColumn=6 30 OpenDup 2 1 0 0 31 OpenDup 3 1 0 0 32 OpenDup 4 1 0 0 33 Null 0 14 14 0 r[14..14]=NULL 34 Integer 1 15 0 0 r[15]=1 35 InitCoroutine 3 0 3 0 36 Yield 3 80 0 0 next row of 37 Copy 6 17 0 0 r[17]=r[6] 38 Copy 7 18 0 0 r[18]=r[7] 39 Copy 8 19 0 0 r[19]=r[8] 40 Copy 9 20 0 0 r[20]=r[9] 41 Copy 10 21 0 0 r[21]=r[10] 42 Copy 11 22 0 0 r[22]=r[11] 43 MakeRecord 17 6 23 0 r[23]=mkrec(r[17..22]) 44 Compare 20 14 1 k(1,B) 0 r[20] <-> r[14] 45 Jump 46 48 46 0 46 Gosub 29 81 0 0 call flush_partition 47 Copy 20 14 0 0 r[14]=r[20] 48 NewRowid 2 24 0 0 r[24]=rowid 49 Insert 2 23 24 0 intkey=r[24] data=r[23] 50 Ne 15 59 24 0 if r[24]!=r[15] goto 59 51 Null 0 1 0 0 r[1]=NULL 52 Rewind 1 1 0 0 53 Rewind 4 1 0 0 54 Copy 21 25 0 0 r[25]=r[21] 55 Copy 25 26 0 0 r[26]=r[25] 56 Copy 25 27 0 0 r[27]=r[25] 57 Copy 25 28 0 0 r[28]=r[25] 58 Goto 0 79 0 0 59 Compare 25 21 1 k(1,B) 0 r[25] <-> r[21] 60 Jump 61 79 61 0 61 Copy 21 25 0 0 r[25]=r[21] 62 Column 4 5 30 0 r[30]=[P3]=PX cursor P1 column P2 63 AggStep 0 30 1 sum(1) 1 accum=r[1] step(r[30]) 64 Next 4 66 0 0 65 Goto 0 70 0 0 66 Column 4 4 31 0 r[31]=[P3]=PX cursor P1 column P2 67 Compare 28 31 1 k(1,B) 0 r[28] <-> r[31] 68 Jump 69 62 69 0 69 Copy 31 28 0 0 r[28]=r[31] 70 AggValue 1 1 2 sum(1) 0 r[2]=value N=1 71 Gosub 16 103 0 0 72 Delete 1 0 0 2 73 Next 1 75 0 0 74 Goto 0 79 0 0 75 Column 1 4 31 0 r[31]=[P3]=PX cursor P1 column P2 76 Compare 27 31 1 k(1,B) 0 r[27] <-> r[31] 77 Jump 78 71 78 0 78 Copy 31 27 0 0 r[27]=r[31] 79 Goto 0 36 0 0 80 Integer 101 29 0 0 r[29]=101 81 Rewind 2 100 0 0 82 Column 4 5 30 0 r[30]=[P3]=PX cursor P1 column P2 83 AggStep 0 30 1 sum(1) 1 accum=r[1] step(r[30]) 84 Next 4 86 0 0 85 Goto 0 90 0 0 86 Column 4 4 31 0 r[31]=[P3]=PX cursor P1 column P2 87 Compare 28 31 1 k(1,B) 0 r[28] <-> r[31] 88 Jump 89 82 89 0 89 Copy 31 28 0 0 r[28]=r[31] 90 AggValue 1 1 2 sum(1) 0 r[2]=value N=1 91 Gosub 16 103 0 0 92 Delete 1 0 0 2 93 Next 1 95 0 0 94 Goto 0 100 0 0 95 Column 1 4 31 0 r[31]=[P3]=PX cursor P1 column P2 96 Compare 27 31 1 k(1,B) 0 r[27] <-> r[31] 97 Jump 98 91 98 0 98 Copy 31 27 0 0 r[27]=r[31] 99 Goto 0 90 0 0 100 ResetSorter 1 0 0 0 101 Return 29 0 0 0 102 Goto 0 111 0 0 103 Noop 0 0 0 0 inner-loop subroutine 104 Column 1 0 32 0 r[32]=.a 105 Column 1 1 33 0 r[33]=.b 106 Column 1 2 34 0 r[34]=.c 107 RealAffinity 34 0 0 0 108 Copy 2 35 0 0 r[35]=r[2] 109 ResultRow 32 4 0 0 output=r[32..35] 110 Return 16 0 0 0 end inner-loop subroutine 111 Halt 0 0 0 0 112 Transaction 0 0 6 0 1 usesStmtJournal=0 113 Goto 0 1 0 0 a|1|2.0|2.0 a|2|-3.0|-1.0 b|1|4.5|4.5 QUERY PLAN |--CO-ROUTINE (subquery-2) | |--SCAN T | `--USE TEMP B-TREE FOR ORDER BY `--SCAN (subquery-2) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 96 0 0 Start at 96 1 Null 0 1 0 0 r[1]=NULL 2 InitCoroutine 3 23 3 0 (subquery-2) 3 SorterOpen 6 5 0 k(2,B,B) 0 4 OpenRead 0 2 0 3 0 root=2 iDb=0; T 5 Rewind 0 15 0 0 6 Column 0 2 6 0 r[6]=T.c 7 RealAffinity 6 0 0 0 8 Column 0 2 4 0 r[4]=T.c 9 RealAffinity 4 0 0 0 10 Column 0 2 5 0 r[5]=T.c 11 RealAffinity 5 0 0 0 12 MakeRecord 4 3 8 0 r[8]=mkrec(r[4..6]) 13 SorterInsert 6 8 4 3 0 key=r[8] 14 Next 0 6 0 1 15 OpenPseudo 7 9 5 0 5 columns in r[9] 16 SorterSort 6 22 0 0 17 SorterData 6 9 7 0 r[9]=data 18 Column 7 1 7 0 r[7]=[P3]=PX cursor P1 column P2 19 Column 7 2 6 0 r[6]=[P3]=PX cursor P1 column P2 20 Yield 3 0 0 0 21 SorterNext 6 17 0 0 22 EndCoroutine 3 0 0 0 23 OpenEphemeral 1 2 0 0 nColumn=2 24 OpenDup 2 1 0 0 25 OpenDup 3 1 0 0 26 OpenDup 4 1 0 0 27 Null 0 10 10 0 r[10..10]=NULL 28 Integer 1 11 0 0 r[11]=1 29 InitCoroutine 3 0 3 0 30 Yield 3 69 0 0 next row of 31 Copy 6 13 0 0 r[13]=r[6] 32 Copy 7 14 0 0 r[14]=r[7] 33 MakeRecord 13 2 15 0 r[15]=mkrec(r[13..14]) 34 Compare 13 10 1 k(1,B) 0 r[13] <-> r[10] 35 Jump 36 38 36 0 36 Gosub 21 70 0 0 call flush_partition 37 Copy 13 10 0 0 r[10]=r[13] 38 NewRowid 2 16 0 0 r[16]=rowid 39 Insert 2 15 16 0 intkey=r[16] data=r[15] 40 Ne 11 49 16 0 if r[16]!=r[11] goto 49 41 Null 0 1 0 0 r[1]=NULL 42 Rewind 1 1 0 0 43 Rewind 4 1 0 0 44 Copy 14 17 0 0 r[17]=r[14] 45 Copy 17 18 0 0 r[18]=r[17] 46 Copy 17 19 0 0 r[19]=r[17] 47 Copy 17 20 0 0 r[20]=r[17] 48 Goto 0 68 0 0 49 Compare 17 14 1 k(1,B) 0 r[17] <-> r[14] 50 Jump 51 68 51 0 51 Copy 14 17 0 0 r[17]=r[14] 52 AggStep 0 22 1 count(0) 0 accum=r[1] step(r[22]) 53 Next 4 55 0 0 54 Goto 0 59 0 0 55 Column 4 1 22 0 r[22]=[P3]=PX cursor P1 column P2 56 Compare 20 22 1 k(1,B) 0 r[20] <-> r[22] 57 Jump 58 52 58 0 58 Copy 22 20 0 0 r[20]=r[22] 59 AggValue 1 0 2 count(0) 0 r[2]=value N=0 60 Gosub 12 91 0 0 61 Delete 1 0 0 2 62 Next 1 64 0 0 63 Goto 0 68 0 0 64 Column 1 1 22 0 r[22]=[P3]=PX cursor P1 column P2 65 Compare 19 22 1 k(1,B) 0 r[19] <-> r[22] 66 Jump 67 60 67 0 67 Copy 22 19 0 0 r[19]=r[22] 68 Goto 0 30 0 0 69 Integer 89 21 0 0 r[21]=89 70 Rewind 2 88 0 0 71 AggStep 0 22 1 count(0) 0 accum=r[1] step(r[22]) 72 Next 4 74 0 0 73 Goto 0 78 0 0 74 Column 4 1 22 0 r[22]=[P3]=PX cursor P1 column P2 75 Compare 20 22 1 k(1,B) 0 r[20] <-> r[22] 76 Jump 77 71 77 0 77 Copy 22 20 0 0 r[20]=r[22] 78 AggValue 1 0 2 count(0) 0 r[2]=value N=0 79 Gosub 12 91 0 0 80 Delete 1 0 0 2 81 Next 1 83 0 0 82 Goto 0 88 0 0 83 Column 1 1 22 0 r[22]=[P3]=PX cursor P1 column P2 84 Compare 19 22 1 k(1,B) 0 r[19] <-> r[22] 85 Jump 86 79 86 0 86 Copy 22 19 0 0 r[19]=r[22] 87 Goto 0 78 0 0 88 ResetSorter 1 0 0 0 89 Return 21 0 0 0 90 Goto 0 95 0 0 91 Noop 0 0 0 0 inner-loop subroutine 92 Copy 2 23 0 0 r[23]=r[2] 93 ResultRow 23 1 0 0 output=r[23] 94 Return 12 0 0 0 end inner-loop subroutine 95 Halt 0 0 0 0 96 Transaction 0 0 6 0 1 usesStmtJournal=0 97 Goto 0 1 0 0 1 1 1 QUERY PLAN `--SCAN T addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 10 0 0 Start at 10 1 OpenRead 2 2 0 3 0 root=2 iDb=0; T 2 Rewind 2 9 0 0 3 Column 2 0 1 0 r[1]=T.a 4 Column 2 1 2 0 r[2]=T.b 5 Column 2 2 3 0 r[3]=T.c 6 RealAffinity 3 0 0 0 7 ResultRow 1 3 0 0 output=r[1..3] 8 Next 2 3 0 1 9 Halt 0 0 0 0 10 Transaction 0 0 6 0 1 usesStmtJournal=0 11 Goto 0 1 0 0 a|1|2.0 a|2|-3.0 b|1|4.5 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 T `--SCALAR SUBQUERY 1 `--SCAN T addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 24 0 0 Start at 24 1 OpenRead 0 2 0 3 0 root=2 iDb=0; T 2 Rewind 0 23 0 0 3 BeginSubrtn 0 2 0 0 r[2]=NULL 4 Once 0 15 0 0 5 Integer 0 3 0 0 r[3]=0; Init EXISTS result 6 Integer 1 4 0 0 r[4]=1; LIMIT counter 7 OpenRead 1 2 0 3 0 root=2 iDb=0; T 8 Rewind 1 15 0 0 9 Column 1 2 5 0 r[5]=T.c 10 RealAffinity 5 0 0 0 11 Le 6 14 5 BINARY-8 85 if r[5]<=r[6] goto 14 12 Integer 1 3 0 0 r[3]=1 13 DecrJumpZero 4 15 0 0 if (--r[4])==0 goto 15 14 Next 1 9 0 1 15 Return 2 4 1 0 16 IfNot 3 22 1 0 17 Column 0 0 8 0 r[8]=T.a 18 Column 0 1 9 0 r[9]=T.b 19 Column 0 2 10 0 r[10]=T.c 20 RealAffinity 10 0 0 0 21 ResultRow 8 3 0 0 output=r[8..10] 22 Next 0 3 0 1 23 Halt 0 0 0 0 24 Transaction 0 0 6 0 1 usesStmtJournal=0 25 Integer 0 6 0 0 r[6]=0 26 Goto 0 1 0 0 a|1|2.0 a|2|-3.0 b|1|4.5 ``` ## Expectation ```sql 0|-1|-1 vfs.zName = "unix" vfs.iVersion = 3 vfs.szOsFile = 120 vfs.mxPathname = 512 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 31 0 0 Start at 31 1 VBegin 0 0 0 0 2 ReadCookie 0 3 2 0 3 If 3 6 0 0 4 SetCookie 0 2 4 0 5 SetCookie 0 5 1 0 6 Integer 0 2 0 0 r[2]=0 7 OpenWrite 0 1 0 5 0 root=1 iDb=0 8 NewRowid 0 1 0 0 r[1]=rowid 9 Blob 6 3 0  0 r[3]= (len=6) 10 Insert 0 3 1 8 intkey=r[1] data=r[3] 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 t0 0 r[7]='t0' 20 String8 0 8 0 t0 0 r[8]='t0' 21 Integer 0 9 0 0 r[9]=0 22 String8 0 10 0 CREATE VIRTUAL TABLE t0 USING fts3("x", "y", "z") 0 r[10]='CREATE VIRTUAL TABLE t0 USING fts3("x", "y", "z")' 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 Expire 0 0 0 0 27 ParseSchema 0 0 0 name='t0' AND sql='CREATE VIRTUAL TABLE t0 USING fts3("x", "y", "z")' 0 28 String8 0 12 0 t0 0 r[12]='t0' 29 VCreate 0 12 0 0 30 Halt 0 0 0 0 31 Transaction 0 1 0 0 1 usesStmtJournal=1 32 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 16 0 0 Start at 16 1 VBegin 0 0 0 0 2 Null 0 1 0 0 r[1]=NULL 3 OpenWrite 0 1 0 5 0 root=1 iDb=0; sqlite_master 4 Rewind 0 12 0 0 5 Column 0 2 2 0 r[2]= cursor 0 column 2 6 Ne 3 11 2 BINARY-8 82 if r[2]!=r[3] goto 11 7 Column 0 0 2 0 r[2]= cursor 0 column 0 8 Eq 4 11 2 BINARY-8 82 if r[2]==r[4] goto 11 9 Rowid 0 5 0 0 r[5]=sqlite_master.rowid 10 Delete 0 0 0 2 11 Next 0 5 0 1 12 VDestroy 0 0 0 t0 0 13 DropTable 0 0 0 t0 0 14 SetCookie 0 1 5 0 15 Halt 0 0 0 0 16 Transaction 0 1 4 0 1 usesStmtJournal=1 17 String8 0 3 0 t0 0 r[3]='t0' 18 String8 0 4 0 trigger 0 r[4]='trigger' 19 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 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 T 0 r[7]='T' 20 String8 0 8 0 T 0 r[8]='T' 21 Copy 2 9 0 0 r[9]=r[2] 22 String8 0 10 0 CREATE TABLE T ( a TEXT, b INTEGER, c REAL ) 0 r[10]='CREATE TABLE T ( a TEXT, b INTEGER, c REAL )' 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 6 0 26 ParseSchema 0 0 0 tbl_name='T' AND type!='trigger' 0 27 Halt 0 0 0 0 28 Transaction 0 1 5 0 1 usesStmtJournal=1 29 Goto 0 1 0 0 QUERY PLAN `--SCAN 3-ROW VALUES CLAUSE addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 23 0 0 Start at 23 1 InitCoroutine 1 15 2 0 2 String8 0 4 0 a 0 r[4]='a' 3 Integer 1 5 0 0 r[5]=1 4 Real 0 6 0 2 0 r[6]=2 5 Yield 1 0 0 0 6 String8 0 4 0 a 0 r[4]='a' 7 Integer 2 5 0 0 r[5]=2 8 Real 0 6 0 -3 0 r[6]=-3 9 Yield 1 0 0 0 10 String8 0 4 0 b 0 r[4]='b' 11 Integer 1 5 0 0 r[5]=1 12 Real 0 6 0 4.5 0 r[6]=4.5 13 Yield 1 0 0 0 14 EndCoroutine 1 0 0 0 15 Explain 15 0 0 SCAN 3-ROW VALUES CLAUSE 0 16 OpenWrite 0 2 0 3 0 root=2 iDb=0; T 17 Yield 1 22 0 0 18 NewRowid 0 3 0 0 r[3]=rowid 19 MakeRecord 4 3 14 BDE 0 r[14]=mkrec(r[4..6]) 20 Insert 0 14 3 T 57 intkey=r[3] data=r[14] 21 Goto 0 17 0 0 22 Halt 0 0 0 0 23 Transaction 0 1 6 0 1 usesStmtJournal=0 24 Goto 0 1 0 0 QUERY PLAN |--CO-ROUTINE (subquery-2) | |--SCAN T | `--USE TEMP B-TREE FOR ORDER BY `--SCAN (subquery-2) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 116 0 0 Start at 116 1 Null 0 1 0 0 r[1]=NULL 2 InitCoroutine 3 32 3 0 (subquery-2) 3 Explain 3 0 0 CO-ROUTINE (subquery-2) 0 4 SorterOpen 6 9 0 k(2,B,B) 0 5 OpenRead 0 2 0 3 0 root=2 iDb=0; T 6 Explain 6 3 216 SCAN T 0 7 Rewind 0 19 0 0 8 Column 0 0 6 0 r[6]= cursor 0 column 0 9 Column 0 1 7 0 r[7]= cursor 0 column 1 10 Column 0 2 8 0 r[8]= cursor 0 column 2 11 RealAffinity 8 0 0 0 12 Column 0 2 9 0 r[9]= cursor 0 column 2 13 RealAffinity 9 0 0 0 14 Column 0 0 4 0 r[4]= cursor 0 column 0 15 Column 0 1 5 0 r[5]= cursor 0 column 1 16 MakeRecord 4 6 12 0 r[12]=mkrec(r[4..9]) 17 SorterInsert 6 12 4 6 0 key=r[12] 18 Next 0 8 0 1 19 Explain 19 3 0 USE TEMP B-TREE FOR ORDER BY 0 20 OpenPseudo 7 13 9 0 9 columns in r[13] 21 SorterSort 6 31 0 0 22 SorterData 6 13 7 0 r[13]=data 23 Column 7 5 11 0 r[11]= cursor 7 column 5; 24 Column 7 1 10 0 r[10]= cursor 7 column 1; 25 Column 7 0 9 0 r[9]= cursor 7 column 0; 26 Column 7 4 8 0 r[8]= cursor 7 column 4; 27 Column 7 3 7 0 r[7]= cursor 7 column 3; 28 Column 7 2 6 0 r[6]= cursor 7 column 2; 29 Yield 3 0 0 0 30 SorterNext 6 22 0 0 31 EndCoroutine 3 0 0 0 end (subquery-2) 32 OpenEphemeral 1 6 0 0 nColumn=6 33 OpenDup 2 1 0 0 34 OpenDup 3 1 0 0 35 OpenDup 4 1 0 0 36 Null 0 14 14 0 r[14..14]=NULL 37 Integer 1 15 0 0 r[15]=1 38 Explain 38 0 216 SCAN (subquery-2) 0 39 InitCoroutine 3 0 3 0 40 Yield 3 84 0 0 next row of 41 Copy 6 17 0 2 r[17]=r[6] 42 Copy 7 18 0 2 r[18]=r[7] 43 Copy 8 19 0 2 r[19]=r[8] 44 Copy 9 20 0 2 r[20]=r[9] 45 Copy 10 21 0 2 r[21]=r[10] 46 Copy 11 22 0 2 r[22]=r[11] 47 MakeRecord 17 6 23 0 r[23]=mkrec(r[17..22]) 48 Compare 20 14 1 k(1,B) 0 r[20] <-> r[14] 49 Jump 50 52 50 0 50 Gosub 29 85 0 0 call flush_partition 51 Copy 20 14 0 0 r[14]=r[20] 52 NewRowid 2 24 0 0 r[24]=rowid 53 Insert 2 23 24 0 intkey=r[24] data=r[23] 54 Ne 15 63 24 0 if r[24]!=r[15] goto 63 55 Null 0 1 0 0 r[1]=NULL 56 Rewind 1 0 0 0 57 Rewind 4 0 0 0 58 Copy 21 25 0 0 r[25]=r[21] 59 Copy 25 26 0 0 r[26]=r[25] 60 Copy 25 27 0 0 r[27]=r[25] 61 Copy 25 28 0 0 r[28]=r[25] 62 Goto 0 83 0 0 63 Compare 25 21 1 k(1,B) 0 r[25] <-> r[21] 64 Jump 65 83 65 0 65 Copy 21 25 0 0 r[25]=r[21] 66 Column 4 5 30 0 r[30]= cursor 4 column 5 67 AggStep 0 30 1 sum(1) 1 accum=r[1] step(r[30]) 68 Next 4 70 0 0 69 Goto 0 74 0 0 70 Column 4 4 31 0 r[31]= cursor 4 column 4 71 Compare 28 31 1 k(1,B) 0 r[28] <-> r[31] 72 Jump 73 66 73 0 73 Copy 31 28 0 0 r[28]=r[31] 74 AggValue 1 1 2 sum(1) 0 r[2]=value N=1 75 Gosub 16 107 0 0 76 Delete 1 0 0 2 77 Next 1 79 0 0 78 Goto 0 83 0 0 79 Column 1 4 31 0 r[31]= cursor 1 column 4 80 Compare 27 31 1 k(1,B) 0 r[27] <-> r[31] 81 Jump 82 75 82 0 82 Copy 31 27 0 0 r[27]=r[31] 83 Goto 0 40 0 0 84 Integer 105 29 0 0 r[29]=105 85 Rewind 2 104 0 0 86 Column 4 5 30 0 r[30]= cursor 4 column 5 87 AggStep 0 30 1 sum(1) 1 accum=r[1] step(r[30]) 88 Next 4 90 0 0 89 Goto 0 94 0 0 90 Column 4 4 31 0 r[31]= cursor 4 column 4 91 Compare 28 31 1 k(1,B) 0 r[28] <-> r[31] 92 Jump 93 86 93 0 93 Copy 31 28 0 0 r[28]=r[31] 94 AggValue 1 1 2 sum(1) 0 r[2]=value N=1 95 Gosub 16 107 0 0 96 Delete 1 0 0 2 97 Next 1 99 0 0 98 Goto 0 104 0 0 99 Column 1 4 31 0 r[31]= cursor 1 column 4 100 Compare 27 31 1 k(1,B) 0 r[27] <-> r[31] 101 Jump 102 95 102 0 102 Copy 31 27 0 0 r[27]=r[31] 103 Goto 0 94 0 0 104 ResetSorter 1 0 0 0 105 Return 29 0 0 0 106 Goto 0 115 0 0 107 Noop 0 0 0 0 inner-loop subroutine 108 Column 1 0 32 0 r[32]= cursor 1 column 0 109 Column 1 1 33 0 r[33]= cursor 1 column 1 110 Column 1 2 34 0 r[34]= cursor 1 column 2 111 RealAffinity 34 0 0 0 112 Copy 2 35 0 0 r[35]=r[2] 113 ResultRow 32 4 0 0 output=r[32..35] 114 Return 16 0 0 0 end inner-loop subroutine 115 Halt 0 0 0 0 116 Transaction 0 0 6 0 1 usesStmtJournal=0 117 Goto 0 1 0 0 a|1|2.0|2.0 a|2|-3.0|-1.0 b|1|4.5|4.5 QUERY PLAN |--CO-ROUTINE (subquery-2) | |--SCAN T | `--USE TEMP B-TREE FOR ORDER BY `--SCAN (subquery-2) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 100 0 0 Start at 100 1 Null 0 1 0 0 r[1]=NULL 2 InitCoroutine 3 26 3 0 (subquery-2) 3 Explain 3 0 0 CO-ROUTINE (subquery-2) 0 4 SorterOpen 6 5 0 k(2,B,B) 0 5 OpenRead 0 2 0 3 0 root=2 iDb=0; T 6 Explain 6 3 216 SCAN T 0 7 Rewind 0 17 0 0 8 Column 0 2 6 0 r[6]= cursor 0 column 2 9 RealAffinity 6 0 0 0 10 Column 0 2 4 0 r[4]= cursor 0 column 2 11 RealAffinity 4 0 0 0 12 Column 0 2 5 0 r[5]= cursor 0 column 2 13 RealAffinity 5 0 0 0 14 MakeRecord 4 3 8 0 r[8]=mkrec(r[4..6]) 15 SorterInsert 6 8 4 3 0 key=r[8] 16 Next 0 8 0 1 17 Explain 17 3 0 USE TEMP B-TREE FOR ORDER BY 0 18 OpenPseudo 7 9 5 0 5 columns in r[9] 19 SorterSort 6 25 0 0 20 SorterData 6 9 7 0 r[9]=data 21 Column 7 1 7 0 r[7]= cursor 7 column 1; 22 Column 7 2 6 0 r[6]= cursor 7 column 2; 23 Yield 3 0 0 0 24 SorterNext 6 20 0 0 25 EndCoroutine 3 0 0 0 end (subquery-2) 26 OpenEphemeral 1 2 0 0 nColumn=2 27 OpenDup 2 1 0 0 28 OpenDup 3 1 0 0 29 OpenDup 4 1 0 0 30 Null 0 10 10 0 r[10..10]=NULL 31 Integer 1 11 0 0 r[11]=1 32 Explain 32 0 216 SCAN (subquery-2) 0 33 InitCoroutine 3 0 3 0 34 Yield 3 73 0 0 next row of 35 Copy 6 13 0 2 r[13]=r[6] 36 Copy 7 14 0 2 r[14]=r[7] 37 MakeRecord 13 2 15 0 r[15]=mkrec(r[13..14]) 38 Compare 13 10 1 k(1,B) 0 r[13] <-> r[10] 39 Jump 40 42 40 0 40 Gosub 21 74 0 0 call flush_partition 41 Copy 13 10 0 0 r[10]=r[13] 42 NewRowid 2 16 0 0 r[16]=rowid 43 Insert 2 15 16 0 intkey=r[16] data=r[15] 44 Ne 11 53 16 0 if r[16]!=r[11] goto 53 45 Null 0 1 0 0 r[1]=NULL 46 Rewind 1 0 0 0 47 Rewind 4 0 0 0 48 Copy 14 17 0 0 r[17]=r[14] 49 Copy 17 18 0 0 r[18]=r[17] 50 Copy 17 19 0 0 r[19]=r[17] 51 Copy 17 20 0 0 r[20]=r[17] 52 Goto 0 72 0 0 53 Compare 17 14 1 k(1,B) 0 r[17] <-> r[14] 54 Jump 55 72 55 0 55 Copy 14 17 0 0 r[17]=r[14] 56 AggStep 0 22 1 count(0) 0 accum=r[1] step(r[22]) 57 Next 4 59 0 0 58 Goto 0 63 0 0 59 Column 4 1 22 0 r[22]= cursor 4 column 1 60 Compare 20 22 1 k(1,B) 0 r[20] <-> r[22] 61 Jump 62 56 62 0 62 Copy 22 20 0 0 r[20]=r[22] 63 AggValue 1 0 2 count(0) 0 r[2]=value N=0 64 Gosub 12 95 0 0 65 Delete 1 0 0 2 66 Next 1 68 0 0 67 Goto 0 72 0 0 68 Column 1 1 22 0 r[22]= cursor 1 column 1 69 Compare 19 22 1 k(1,B) 0 r[19] <-> r[22] 70 Jump 71 64 71 0 71 Copy 22 19 0 0 r[19]=r[22] 72 Goto 0 34 0 0 73 Integer 93 21 0 0 r[21]=93 74 Rewind 2 92 0 0 75 AggStep 0 22 1 count(0) 0 accum=r[1] step(r[22]) 76 Next 4 78 0 0 77 Goto 0 82 0 0 78 Column 4 1 22 0 r[22]= cursor 4 column 1 79 Compare 20 22 1 k(1,B) 0 r[20] <-> r[22] 80 Jump 81 75 81 0 81 Copy 22 20 0 0 r[20]=r[22] 82 AggValue 1 0 2 count(0) 0 r[2]=value N=0 83 Gosub 12 95 0 0 84 Delete 1 0 0 2 85 Next 1 87 0 0 86 Goto 0 92 0 0 87 Column 1 1 22 0 r[22]= cursor 1 column 1 88 Compare 19 22 1 k(1,B) 0 r[19] <-> r[22] 89 Jump 90 83 90 0 90 Copy 22 19 0 0 r[19]=r[22] 91 Goto 0 82 0 0 92 ResetSorter 1 0 0 0 93 Return 21 0 0 0 94 Goto 0 99 0 0 95 Noop 0 0 0 0 inner-loop subroutine 96 Copy 2 23 0 0 r[23]=r[2] 97 ResultRow 23 1 0 0 output=r[23] 98 Return 12 0 0 0 end inner-loop subroutine 99 Halt 0 0 0 0 100 Transaction 0 0 6 0 1 usesStmtJournal=0 101 Goto 0 1 0 0 1 1 1 QUERY PLAN `--SCAN T addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 11 0 0 Start at 11 1 OpenRead 2 2 0 3 0 root=2 iDb=0; T 2 Explain 2 0 216 SCAN T 0 3 Rewind 2 10 0 0 4 Column 2 0 1 0 r[1]= cursor 2 column 0 5 Column 2 1 2 0 r[2]= cursor 2 column 1 6 Column 2 2 3 0 r[3]= cursor 2 column 2 7 RealAffinity 3 0 0 0 8 ResultRow 1 3 0 0 output=r[1..3] 9 Next 2 4 0 1 10 Halt 0 0 0 0 11 Transaction 0 0 6 0 1 usesStmtJournal=0 12 Goto 0 1 0 0 a|1|2.0 a|2|-3.0 b|1|4.5 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 T `--SCAN T EXISTS addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 20 0 0 Start at 20 1 IfNot 1 19 1 0 2 OpenRead 0 2 0 3 0 root=2 iDb=0; T 3 OpenRead 1 2 0 3 0 root=2 iDb=0; T 4 Explain 4 0 216 SCAN T 0 5 Rewind 0 19 0 0 6 Explain 6 0 216 SCAN T EXISTS 0 7 Rewind 1 19 0 0 8 Column 1 2 2 0 r[2]= cursor 1 column 2 9 RealAffinity 2 0 0 0 10 Le 3 17 2 BINARY-8 85 if r[2]<=r[3] goto 17 11 Column 0 0 4 0 r[4]= cursor 0 column 0 12 Column 0 1 5 0 r[5]= cursor 0 column 1 13 Column 0 2 6 0 r[6]= cursor 0 column 2 14 RealAffinity 6 0 0 0 15 ResultRow 4 3 0 0 output=r[4..6] 16 Goto 0 18 0 0 EXISTS break 17 Next 1 8 0 1 18 Next 0 6 0 1 19 Halt 0 0 0 0 20 Transaction 0 0 6 0 1 usesStmtJournal=0 21 Integer 1 1 0 0 r[1]=1 22 Integer 0 3 0 0 r[3]=0 23 Goto 0 1 0 0 a|1|2.0 a|2|-3.0 b|1|4.5 ``` ## Flag ``` ```