## Summary **No review yet** ## Minimized query ```sql .eqp full SAVEPOINT sp7156; PRAGMA vdbe_trace = NO; PRAGMA table_xinfo(users); .headers off DROP TABLE IF EXISTS t1; CREATE TABLE t1(a int, b int); INSERT INTO t1 VALUES((CAST(x'10c1be15aa566e' AS CHAR(10)) * 1),2),(1,18),(2,19); SELECT x, y FROM (SELECT 98 AS x, 99 AS y UNION SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a) AS w WHERE NOT y>=20 ORDER BY +x; SELECT x, y FROM (SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a UNION SELECT 98 AS x, 99 AS y) AS w WHERE y>=20 ORDER BY +x; SELECT * FROM t1 AS a LEFT OUTER JOIN t1 AS b ON a.rowid = b.rowid; SELECT COUNT(*) FROM t1; SELECT SUM(b) FROM t1; SELECT * FROM t1 AS a JOIN t1 AS b ON a.rowid = b.rowid; CREATE TABLE T ( A VARCHAR(20), B VARCHAR(10), PRIMARY KEY (A, B) ); INSERT INTO T VALUES ('a', 'p'); INSERT INTO T VALUES ('a', 'q'); INSERT INTO T VALUES ('b', 'q'); INSERT INTO T VALUES ('c', 'r'); INSERT INTO T VALUES ('b', 'r'); INSERT INTO T VALUES ('b', 't'); SELECT B, COUNT(DISTINCT A) AS D FROM T GROUP BY B ORDER BY D DESC, B ASC; ALTER TABLE t1 ADD COLUMN extra_7039 CHAR(10); ROLLBACK TRANSACTION TO SAVEPOINT sp7156; RELEASE SAVEPOINT sp7156; ``` ## Actual output ```sql addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 1 0 0 Start at 1 1 Savepoint 0 0 0 sp7156 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 Halt 0 0 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 Transaction 0 0 0 0 1 usesStmtJournal=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 JournalMode 0 1 -1 0 2 Halt 0 0 0 0 3 Transaction 0 0 0 0 1 usesStmtJournal=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]=[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(a int, b int) 0 r[10]='CREATE TABLE t1(a int, b 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 QUERY PLAN `--SCAN 3 CONSTANT ROWS addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 19 0 0 Start at 19 1 InitCoroutine 4 12 2 0 2 Multiply 6 5 2 0 r[2]=r[6]*r[5] 3 Integer 2 3 0 0 r[3]=2 4 Yield 4 0 0 0 5 Integer 1 2 0 0 r[2]=1 6 Integer 18 3 0 0 r[3]=18 7 Yield 4 0 0 0 8 Integer 2 2 0 0 r[2]=2 9 Integer 19 3 0 0 r[3]=19 10 Yield 4 0 0 0 11 EndCoroutine 4 0 0 0 12 OpenWrite 0 2 0 2 0 root=2 iDb=0; t1 13 Yield 4 18 0 0 14 NewRowid 0 1 0 0 r[1]=rowid 15 MakeRecord 2 2 7 DD 0 r[7]=mkrec(r[2..3]) 16 Insert 0 7 1 t1 57 intkey=r[1] data=r[7] 17 Goto 0 13 0 0 18 Halt 0 0 0 0 19 Transaction 0 1 1 0 1 usesStmtJournal=0 20 Blob 7 5 0 Á¾ªVn 0 r[5]=Á¾ªVn (len=7) 21 Cast 5 66 0 0 affinity(r[5]) 22 Integer 1 6 0 0 r[6]=1 23 Goto 0 1 0 0 QUERY PLAN |--CO-ROUTINE w | `--COMPOUND QUERY | |--LEFT-MOST SUBQUERY | | `--SCAN CONSTANT ROW | `--UNION USING TEMP B-TREE | |--SCAN t1 | `--USE TEMP B-TREE FOR GROUP BY |--SCAN w `--USE TEMP B-TREE FOR ORDER BY addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 77 0 0 Start at 77 1 InitCoroutine 1 58 2 0 w 2 OpenEphemeral 2 2 0 k(2,B,B) 0 nColumn=2 3 Ge 3 8 2 BINARY-8 80 if r[2]>=r[3] goto 8 4 Integer 98 4 0 0 r[4]=98 5 Integer 99 5 0 0 r[5]=99 6 MakeRecord 4 2 6 0 r[6]=mkrec(r[4..5]) 7 IdxInsert 2 6 4 2 0 key=r[6] 8 SorterOpen 3 2 0 k(1,B) 0 9 Integer 0 11 0 0 r[11]=0; clear abort flag 10 Null 0 14 14 0 r[14..14]=NULL 11 Gosub 13 48 0 0 12 OpenRead 1 2 0 2 0 root=2 iDb=0; t1 13 Rewind 1 19 0 0 14 Column 1 0 16 0 r[16]=t1.a 15 Column 1 1 17 0 r[17]=t1.b 16 MakeRecord 16 2 6 0 r[6]=mkrec(r[16..17]) 17 SorterInsert 3 6 0 0 key=r[6] 18 Next 1 14 0 1 19 OpenPseudo 4 6 2 0 2 columns in r[6] 20 SorterSort 3 51 0 0 GROUP BY sort 21 SorterData 3 6 4 0 r[6]=data 22 Column 4 0 15 0 r[15]=[P3]=PX cursor P1 column P2 23 Compare 14 15 1 k(1,B) 0 r[14] <-> r[15] 24 Jump 25 29 25 0 25 Move 15 14 1 0 r[14]=r[15] 26 Gosub 12 39 0 0 output one row 27 IfPos 11 51 0 0 if r[11]>0 then r[11]-=0, goto 51; check abort flag 28 Gosub 13 48 0 0 reset accumulator 29 Column 4 1 18 0 r[18]=t1.b 30 AggStep 0 18 8 sum(1) 1 accum=r[8] step(r[18]) 31 If 10 33 0 0 32 Column 4 0 7 0 r[7]=t1.a 33 Integer 1 10 0 0 r[10]=1; indicate data in accumulator 34 SorterNext 3 21 0 0 35 Gosub 12 39 0 0 output final row 36 Goto 0 51 0 0 37 Integer 1 11 0 0 r[11]=1; set abort flag 38 Return 12 0 0 0 39 IfPos 10 41 0 0 if r[10]>0 then r[10]-=0, goto 41; Groupby result generator entry point 40 Return 12 0 0 0 41 AggFinal 8 1 0 sum(1) 0 accum=r[8] N=1 42 Ge 3 40 8 BINARY-8 80 if r[8]>=r[3] goto 40 43 SCopy 7 4 0 0 r[4]=r[7] 44 SCopy 8 5 0 0 r[5]=r[8] 45 MakeRecord 4 2 18 0 r[18]=mkrec(r[4..5]) 46 IdxInsert 2 18 4 2 0 key=r[18] 47 Return 12 0 0 0 end groupby result generator 48 Null 0 7 9 0 r[7..9]=NULL 49 Integer 0 10 0 0 r[10]=0; indicate accumulator empty 50 Return 13 0 0 0 51 Rewind 2 56 0 0 52 Column 2 0 19 0 r[19]=x 53 Column 2 1 20 0 r[20]=y 54 Yield 1 0 0 0 55 Next 2 52 0 0 56 Close 2 0 0 0 57 EndCoroutine 1 0 0 0 58 SorterOpen 5 4 0 k(1,B) 0 59 InitCoroutine 1 0 2 0 60 Yield 1 69 0 0 next row of w 61 Copy 20 21 0 0 r[21]=r[20]; w.y 62 Ge 3 68 21 BINARY-8 80 if r[21]>=r[3] goto 68 63 Copy 19 23 0 0 r[23]=r[19]; w.x 64 Copy 20 24 0 0 r[24]=r[20]; w.y 65 Copy 19 22 0 0 r[22]=r[19]; w.x 66 MakeRecord 22 3 25 0 r[25]=mkrec(r[22..24]) 67 SorterInsert 5 25 22 3 0 key=r[25] 68 Goto 0 60 0 0 69 OpenPseudo 6 26 4 0 4 columns in r[26] 70 SorterSort 5 76 0 0 71 SorterData 5 26 6 0 r[26]=data 72 Column 6 2 24 0 r[24]=y 73 Column 6 1 23 0 r[23]=x 74 ResultRow 23 2 0 0 output=r[23..24] 75 SorterNext 5 71 0 0 76 Halt 0 0 0 0 77 Transaction 0 0 1 0 1 usesStmtJournal=0 78 Integer 99 2 0 0 r[2]=99 79 Integer 20 3 0 0 r[3]=20 80 Goto 0 1 0 0 0|2 1|18 2|19 QUERY PLAN |--CO-ROUTINE w | `--COMPOUND QUERY | |--LEFT-MOST SUBQUERY | | |--SCAN t1 | | `--USE TEMP B-TREE FOR GROUP BY | `--UNION USING TEMP B-TREE | `--SCAN CONSTANT ROW |--SCAN w `--USE TEMP B-TREE FOR ORDER BY addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 77 0 0 Start at 77 1 InitCoroutine 1 58 2 0 w 2 OpenEphemeral 2 2 0 k(2,B,B) 0 nColumn=2 3 SorterOpen 3 2 0 k(1,B) 0 4 Integer 0 6 0 0 r[6]=0; clear abort flag 5 Null 0 9 9 0 r[9..9]=NULL 6 Gosub 8 43 0 0 7 OpenRead 1 2 0 2 0 root=2 iDb=0; t1 8 Rewind 1 14 0 0 9 Column 1 0 11 0 r[11]=t1.a 10 Column 1 1 12 0 r[12]=t1.b 11 MakeRecord 11 2 13 0 r[13]=mkrec(r[11..12]) 12 SorterInsert 3 13 0 0 key=r[13] 13 Next 1 9 0 1 14 OpenPseudo 4 13 2 0 2 columns in r[13] 15 SorterSort 3 46 0 0 GROUP BY sort 16 SorterData 3 13 4 0 r[13]=data 17 Column 4 0 10 0 r[10]=[P3]=PX cursor P1 column P2 18 Compare 9 10 1 k(1,B) 0 r[9] <-> r[10] 19 Jump 20 24 20 0 20 Move 10 9 1 0 r[9]=r[10] 21 Gosub 7 34 0 0 output one row 22 IfPos 6 46 0 0 if r[6]>0 then r[6]-=0, goto 46; check abort flag 23 Gosub 8 43 0 0 reset accumulator 24 Column 4 1 14 0 r[14]=t1.b 25 AggStep 0 14 3 sum(1) 1 accum=r[3] step(r[14]) 26 If 5 28 0 0 27 Column 4 0 2 0 r[2]=t1.a 28 Integer 1 5 0 0 r[5]=1; indicate data in accumulator 29 SorterNext 3 16 0 0 30 Gosub 7 34 0 0 output final row 31 Goto 0 46 0 0 32 Integer 1 6 0 0 r[6]=1; set abort flag 33 Return 7 0 0 0 34 IfPos 5 36 0 0 if r[5]>0 then r[5]-=0, goto 36; Groupby result generator entry point 35 Return 7 0 0 0 36 AggFinal 3 1 0 sum(1) 0 accum=r[3] N=1 37 Lt 15 35 3 BINARY-8 80 if r[3]0 then r[1]-=0, goto 16 14 NullRow 1 0 0 0 15 Goto 0 7 0 0 16 Next 0 4 0 1 17 Halt 0 0 0 0 18 Transaction 0 0 1 0 1 usesStmtJournal=0 19 Goto 0 1 0 0 0|2|0|2 1|18|1|18 2|19|2|19 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 3 QUERY PLAN `--SCAN t1 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 0 2 0 2 0 root=2 iDb=0; t1 3 Rewind 0 7 0 0 4 Column 0 1 3 0 r[3]=t1.b 5 AggStep 0 3 1 sum(1) 1 accum=r[1] step(r[3]) 6 Next 0 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 1 0 1 usesStmtJournal=0 12 Goto 0 1 0 0 39 QUERY PLAN |--SCAN a `--SEARCH b USING INTEGER PRIMARY KEY (rowid=?) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 13 0 0 Start at 13 1 OpenRead 0 2 0 2 0 root=2 iDb=0; t1 2 OpenRead 1 2 0 2 0 root=2 iDb=0; t1 3 Rewind 0 12 0 0 4 Rowid 0 1 0 0 r[1]=t1.rowid 5 SeekRowid 1 11 1 0 intkey=r[1] 6 Column 0 0 2 0 r[2]=t1.a 7 Column 0 1 3 0 r[3]=t1.b 8 Column 1 0 4 0 r[4]=t1.a 9 Column 1 1 5 0 r[5]=t1.b 10 ResultRow 2 4 0 0 output=r[2..5] 11 Next 0 4 0 1 12 Halt 0 0 0 0 13 Transaction 0 0 1 0 1 usesStmtJournal=0 14 Goto 0 1 0 0 0|2|0|2 1|18|1|18 2|19|2|19 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 39 0 0 Start at 39 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 Noop 0 22 0 0 12 CreateBtree 0 4 2 0 r[4]=root iDb=0 flags=2 13 OpenWrite 1 1 0 5 0 root=1 iDb=0; sqlite_master 14 String8 0 6 0 index 0 r[6]='index' 15 String8 0 7 0 sqlite_autoindex_T_1 0 r[7]='sqlite_autoindex_T_1' 16 String8 0 8 0 T 0 r[8]='T' 17 SCopy 4 9 0 0 r[9]=r[4] 18 Null 0 10 0 0 r[10]=NULL 19 NewRowid 1 5 0 0 r[5]=rowid 20 MakeRecord 6 5 11 BBBDB 0 r[11]=mkrec(r[6..10]) 21 Insert 1 11 5 24 intkey=r[5] data=r[11] 22 Close 0 0 0 0 23 Null 0 12 13 0 r[12..13]=NULL 24 Noop 3 0 12 0 25 OpenWrite 2 1 0 5 0 root=1 iDb=0; sqlite_master 26 SeekRowid 2 28 1 0 intkey=r[1] 27 Rowid 2 13 0 0 r[13]=[P2]=PX rowid of P1 28 IsNull 13 36 0 0 if r[13]==NULL goto 36 29 String8 0 14 0 table 0 r[14]='table' 30 String8 0 15 0 T 0 r[15]='T' 31 String8 0 16 0 T 0 r[16]='T' 32 SCopy 2 17 0 0 r[17]=r[2] 33 String8 0 18 0 CREATE TABLE T ( A VARCHAR(20), B VARCHAR(10), PRIMARY KEY (A, B) ) 0 r[18]='CREATE TABLE T ( A VARCHAR(20), B VARCHAR(10), PRIMARY KEY (A, B) )' 34 MakeRecord 14 5 12 BBBDB 0 r[12]=mkrec(r[14..18]) 35 Insert 2 12 13 0 intkey=r[13] data=r[12] 36 SetCookie 0 1 2 0 37 ParseSchema 0 0 0 tbl_name='T' AND type!='trigger' 0 38 Halt 0 0 0 0 39 Transaction 0 1 1 0 1 usesStmtJournal=1 40 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 18 0 0 Start at 18 1 OpenWrite 0 3 0 2 0 root=3 iDb=0; T 2 OpenWrite 1 4 0 k(3,,,) 0 root=4 iDb=0; sqlite_autoindex_T_1 3 String8 0 2 0 a 0 r[2]='a' 4 String8 0 3 0 p 0 r[3]='p' 5 NewRowid 0 1 0 0 r[1]=rowid 6 Affinity 2 2 0 BB 0 affinity(r[2..3]) 7 Noop 0 0 0 0 prep index sqlite_autoindex_T_1 8 SCopy 2 5 0 0 r[5]=r[2]; A 9 SCopy 3 6 0 0 r[6]=r[3]; B 10 IntCopy 1 7 0 0 r[7]=r[1]; rowid 11 MakeRecord 5 3 4 0 r[4]=mkrec(r[5..7]); for sqlite_autoindex_T_1 12 NoConflict 1 14 5 2 0 key=r[5..6] 13 Halt 1555 2 0 T.A, T.B 2 14 MakeRecord 2 2 8 0 r[8]=mkrec(r[2..3]) 15 IdxInsert 1 4 5 3 16 key=r[4] 16 Insert 0 8 1 T 57 intkey=r[1] data=r[8] 17 Halt 0 0 0 0 18 Transaction 0 1 2 0 1 usesStmtJournal=0 19 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 18 0 0 Start at 18 1 OpenWrite 0 3 0 2 0 root=3 iDb=0; T 2 OpenWrite 1 4 0 k(3,,,) 0 root=4 iDb=0; sqlite_autoindex_T_1 3 String8 0 2 0 a 0 r[2]='a' 4 String8 0 3 0 q 0 r[3]='q' 5 NewRowid 0 1 0 0 r[1]=rowid 6 Affinity 2 2 0 BB 0 affinity(r[2..3]) 7 Noop 0 0 0 0 prep index sqlite_autoindex_T_1 8 SCopy 2 5 0 0 r[5]=r[2]; A 9 SCopy 3 6 0 0 r[6]=r[3]; B 10 IntCopy 1 7 0 0 r[7]=r[1]; rowid 11 MakeRecord 5 3 4 0 r[4]=mkrec(r[5..7]); for sqlite_autoindex_T_1 12 NoConflict 1 14 5 2 0 key=r[5..6] 13 Halt 1555 2 0 T.A, T.B 2 14 MakeRecord 2 2 8 0 r[8]=mkrec(r[2..3]) 15 IdxInsert 1 4 5 3 16 key=r[4] 16 Insert 0 8 1 T 57 intkey=r[1] data=r[8] 17 Halt 0 0 0 0 18 Transaction 0 1 2 0 1 usesStmtJournal=0 19 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 18 0 0 Start at 18 1 OpenWrite 0 3 0 2 0 root=3 iDb=0; T 2 OpenWrite 1 4 0 k(3,,,) 0 root=4 iDb=0; sqlite_autoindex_T_1 3 String8 0 2 0 b 0 r[2]='b' 4 String8 0 3 0 q 0 r[3]='q' 5 NewRowid 0 1 0 0 r[1]=rowid 6 Affinity 2 2 0 BB 0 affinity(r[2..3]) 7 Noop 0 0 0 0 prep index sqlite_autoindex_T_1 8 SCopy 2 5 0 0 r[5]=r[2]; A 9 SCopy 3 6 0 0 r[6]=r[3]; B 10 IntCopy 1 7 0 0 r[7]=r[1]; rowid 11 MakeRecord 5 3 4 0 r[4]=mkrec(r[5..7]); for sqlite_autoindex_T_1 12 NoConflict 1 14 5 2 0 key=r[5..6] 13 Halt 1555 2 0 T.A, T.B 2 14 MakeRecord 2 2 8 0 r[8]=mkrec(r[2..3]) 15 IdxInsert 1 4 5 3 16 key=r[4] 16 Insert 0 8 1 T 57 intkey=r[1] data=r[8] 17 Halt 0 0 0 0 18 Transaction 0 1 2 0 1 usesStmtJournal=0 19 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 18 0 0 Start at 18 1 OpenWrite 0 3 0 2 0 root=3 iDb=0; T 2 OpenWrite 1 4 0 k(3,,,) 0 root=4 iDb=0; sqlite_autoindex_T_1 3 String8 0 2 0 c 0 r[2]='c' 4 String8 0 3 0 r 0 r[3]='r' 5 NewRowid 0 1 0 0 r[1]=rowid 6 Affinity 2 2 0 BB 0 affinity(r[2..3]) 7 Noop 0 0 0 0 prep index sqlite_autoindex_T_1 8 SCopy 2 5 0 0 r[5]=r[2]; A 9 SCopy 3 6 0 0 r[6]=r[3]; B 10 IntCopy 1 7 0 0 r[7]=r[1]; rowid 11 MakeRecord 5 3 4 0 r[4]=mkrec(r[5..7]); for sqlite_autoindex_T_1 12 NoConflict 1 14 5 2 0 key=r[5..6] 13 Halt 1555 2 0 T.A, T.B 2 14 MakeRecord 2 2 8 0 r[8]=mkrec(r[2..3]) 15 IdxInsert 1 4 5 3 16 key=r[4] 16 Insert 0 8 1 T 57 intkey=r[1] data=r[8] 17 Halt 0 0 0 0 18 Transaction 0 1 2 0 1 usesStmtJournal=0 19 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 18 0 0 Start at 18 1 OpenWrite 0 3 0 2 0 root=3 iDb=0; T 2 OpenWrite 1 4 0 k(3,,,) 0 root=4 iDb=0; sqlite_autoindex_T_1 3 String8 0 2 0 b 0 r[2]='b' 4 String8 0 3 0 r 0 r[3]='r' 5 NewRowid 0 1 0 0 r[1]=rowid 6 Affinity 2 2 0 BB 0 affinity(r[2..3]) 7 Noop 0 0 0 0 prep index sqlite_autoindex_T_1 8 SCopy 2 5 0 0 r[5]=r[2]; A 9 SCopy 3 6 0 0 r[6]=r[3]; B 10 IntCopy 1 7 0 0 r[7]=r[1]; rowid 11 MakeRecord 5 3 4 0 r[4]=mkrec(r[5..7]); for sqlite_autoindex_T_1 12 NoConflict 1 14 5 2 0 key=r[5..6] 13 Halt 1555 2 0 T.A, T.B 2 14 MakeRecord 2 2 8 0 r[8]=mkrec(r[2..3]) 15 IdxInsert 1 4 5 3 16 key=r[4] 16 Insert 0 8 1 T 57 intkey=r[1] data=r[8] 17 Halt 0 0 0 0 18 Transaction 0 1 2 0 1 usesStmtJournal=0 19 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 18 0 0 Start at 18 1 OpenWrite 0 3 0 2 0 root=3 iDb=0; T 2 OpenWrite 1 4 0 k(3,,,) 0 root=4 iDb=0; sqlite_autoindex_T_1 3 String8 0 2 0 b 0 r[2]='b' 4 String8 0 3 0 t 0 r[3]='t' 5 NewRowid 0 1 0 0 r[1]=rowid 6 Affinity 2 2 0 BB 0 affinity(r[2..3]) 7 Noop 0 0 0 0 prep index sqlite_autoindex_T_1 8 SCopy 2 5 0 0 r[5]=r[2]; A 9 SCopy 3 6 0 0 r[6]=r[3]; B 10 IntCopy 1 7 0 0 r[7]=r[1]; rowid 11 MakeRecord 5 3 4 0 r[4]=mkrec(r[5..7]); for sqlite_autoindex_T_1 12 NoConflict 1 14 5 2 0 key=r[5..6] 13 Halt 1555 2 0 T.A, T.B 2 14 MakeRecord 2 2 8 0 r[8]=mkrec(r[2..3]) 15 IdxInsert 1 4 5 3 16 key=r[4] 16 Insert 0 8 1 T 57 intkey=r[1] data=r[8] 17 Halt 0 0 0 0 18 Transaction 0 1 2 0 1 usesStmtJournal=0 19 Goto 0 1 0 0 QUERY PLAN |--SCAN T |--USE TEMP B-TREE FOR GROUP BY |--USE TEMP B-TREE FOR count(DISTINCT) `--USE TEMP B-TREE FOR ORDER BY addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 56 0 0 Start at 56 1 SorterOpen 1 5 0 k(2,-B,B) 0 2 SorterOpen 3 2 0 k(1,B) 0 3 Integer 0 5 0 0 r[5]=0; clear abort flag 4 Null 0 8 8 0 r[8..8]=NULL 5 Gosub 7 44 0 0 6 OpenRead 0 3 0 2 0 root=3 iDb=0; T 7 Rewind 0 13 0 0 8 Column 0 1 10 0 r[10]=T.B 9 Column 0 0 11 0 r[11]=T.A 10 MakeRecord 10 2 12 0 r[12]=mkrec(r[10..11]) 11 SorterInsert 3 12 0 0 key=r[12] 12 Next 0 8 0 1 13 OpenPseudo 4 12 2 0 2 columns in r[12] 14 SorterSort 3 48 0 0 GROUP BY sort 15 SorterData 3 12 4 0 r[12]=data 16 Column 4 0 9 0 r[9]=[P3]=PX cursor P1 column P2 17 Compare 8 9 1 k(1,B) 0 r[8] <-> r[9] 18 Jump 19 23 19 0 19 Move 9 8 1 0 r[8]=r[9] 20 Gosub 6 36 0 0 output one row 21 IfPos 5 48 0 0 if r[5]>0 then r[5]-=0, goto 48; check abort flag 22 Gosub 7 44 0 0 reset accumulator 23 Column 4 1 13 0 r[13]=T.A 24 Found 2 28 13 1 0 key=r[13] 25 MakeRecord 13 1 14 0 r[14]=mkrec(r[13]) 26 IdxInsert 2 14 13 1 16 key=r[14] 27 AggStep 0 13 2 count(1) 1 accum=r[2] step(r[13]) 28 If 4 30 0 0 29 Column 4 0 1 0 r[1]=T.B 30 Integer 1 4 0 0 r[4]=1; indicate data in accumulator 31 SorterNext 3 15 0 0 32 Gosub 6 36 0 0 output final row 33 Goto 0 48 0 0 34 Integer 1 5 0 0 r[5]=1; set abort flag 35 Return 6 0 0 0 36 IfPos 4 38 0 0 if r[4]>0 then r[4]-=0, goto 38; Groupby result generator entry point 37 Return 6 0 0 0 38 AggFinal 2 1 0 count(1) 0 accum=r[2] N=1 39 Copy 2 15 0 0 r[15]=r[2] 40 Copy 1 16 0 0 r[16]=r[1] 41 MakeRecord 15 2 19 0 r[19]=mkrec(r[15..16]) 42 SorterInsert 1 19 15 2 0 key=r[19] 43 Return 6 0 0 0 end groupby result generator 44 Null 0 1 3 0 r[1..3]=NULL 45 OpenEphemeral 2 0 0 k(1,B) 0 nColumn=0 46 Integer 0 4 0 0 r[4]=0; indicate accumulator empty 47 Return 7 0 0 0 48 OpenPseudo 5 20 5 0 5 columns in r[20] 49 SorterSort 1 55 0 0 50 SorterData 1 20 5 0 r[20]=data 51 Column 5 0 18 0 r[18]=D 52 Column 5 1 17 0 r[17]=B 53 ResultRow 17 2 0 0 output=r[17..18] 54 SorterNext 1 50 0 0 55 Halt 0 0 0 0 56 Transaction 0 0 2 0 1 usesStmtJournal=0 57 Goto 0 1 0 0 q|2 r|2 p|1 t|1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 41 0 0 Start at 41 1 Null 0 1 2 0 r[1..2]=NULL 2 OpenEphemeral 1 0 1 0 nColumn=0 3 OpenRead 0 1 0 2 0 root=1 iDb=0; sqlite_master 4 Rewind 0 12 0 0 5 Column 0 0 8 0 r[8]=sqlite_master.type 6 Ne 9 11 8 BINARY-8 82 if r[8]!=r[9] goto 11 7 Column 0 1 8 0 r[8]=sqlite_master.name 8 Ne 10 11 8 BINARY-8 82 if r[8]!=r[10] goto 11 9 Rowid 0 2 0 0 r[2]=[P2]=PX rowid of P1 10 Insert 1 1 2 0 intkey=r[2] data=r[1] 11 Next 0 5 0 1 12 OpenWrite 0 1 0 5 0 root=1 iDb=0; sqlite_master 13 Rewind 1 33 0 0 14 Rowid 1 2 0 0 r[2]=[P2]=PX rowid of P1 15 NotExists 0 32 2 0 intkey=r[2] 16 Column 0 0 3 0 r[3]=sqlite_master.type 17 Column 0 1 4 0 r[4]=sqlite_master.name 18 Column 0 2 5 0 r[5]=sqlite_master.tbl_name 19 Column 0 3 6 0 r[6]=sqlite_master.rootpage 20 Column 0 4 14 0 r[14]=sqlite_master.sql 21 Function 1 13 12 printf(-1) 0 r[12]=func(r[13..14]) 22 Concat 15 12 8 0 r[8]=r[12]+r[15] 23 Column 0 4 16 0 r[16]=sqlite_master.sql 24 Column 0 4 22 0 r[22]=sqlite_master.sql 25 Function 1 21 20 printf(-1) 0 r[20]=func(r[21..22]) 26 Function 0 20 19 length(1) 0 r[19]=func(r[20]) 27 Add 19 18 17 0 r[17]=r[19]+r[18] 28 Function 0 16 12 substr(2) 0 r[12]=func(r[16..17]) 29 Concat 12 8 7 0 r[7]=r[8]+r[12] 30 MakeRecord 3 5 11 BBBDB 0 r[11]=mkrec(r[3..7]) 31 Insert 0 11 2 0 intkey=r[2] data=r[11] 32 Next 1 14 0 0 33 ReadCookie 0 12 2 0 34 AddImm 12 -2 0 0 r[12]=r[12]+-2 35 IfPos 12 37 0 0 if r[12]>0 then r[12]-=0, goto 37 36 SetCookie 0 2 3 0 37 SetCookie 0 1 3 0 38 ParseSchema 0 0 0 3 39 ParseSchema 1 0 0 3 40 Halt 0 0 0 0 41 Transaction 0 1 2 0 1 usesStmtJournal=1 42 String8 0 9 0 table 0 r[9]='table' 43 String8 0 10 0 t1 0 r[10]='t1' 44 String8 0 13 0 %.28s, 0 r[13]='%.28s, ' 45 String8 0 15 0 extra_7039 CHAR(10) 0 r[15]='extra_7039 CHAR(10)' 46 Integer 1 18 0 0 r[18]=1 47 String8 0 21 0 %.28s 0 r[21]='%.28s' 48 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 1 0 0 Start at 1 1 Savepoint 2 0 0 sp7156 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 Savepoint 1 0 0 sp7156 0 2 Halt 0 0 0 0 ``` ## Expectation ```sql addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 3 0 0 Start at 3 1 Savepoint 0 0 0 sp7156 0 2 Halt 0 0 0 0 3 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 3 0 0 Start at 3 1 Expire 1 1 0 0 2 Halt 0 0 0 0 3 Transaction 0 0 0 0 1 usesStmtJournal=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 JournalMode 0 1 -1 0 2 Halt 0 0 0 0 3 Transaction 0 0 0 0 1 usesStmtJournal=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 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(a int, b int) 0 r[10]='CREATE TABLE t1(a int, b 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 QUERY PLAN `--SCAN 3-ROW VALUES CLAUSE addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 20 0 0 Start at 20 1 InitCoroutine 1 12 2 0 2 Multiply 9 8 4 0 r[4]=r[9]*r[8] 3 Integer 2 5 0 0 r[5]=2 4 Yield 1 0 0 0 5 Integer 1 4 0 0 r[4]=1 6 Integer 18 5 0 0 r[5]=18 7 Yield 1 0 0 0 8 Integer 2 4 0 0 r[4]=2 9 Integer 19 5 0 0 r[5]=19 10 Yield 1 0 0 0 11 EndCoroutine 1 0 0 0 12 Explain 12 0 0 SCAN 3-ROW VALUES CLAUSE 0 13 OpenWrite 0 2 0 2 0 root=2 iDb=0; t1 14 Yield 1 19 0 0 15 NewRowid 0 3 0 0 r[3]=rowid 16 MakeRecord 4 2 13 DD 0 r[13]=mkrec(r[4..5]) 17 Insert 0 13 3 t1 57 intkey=r[3] data=r[13] 18 Goto 0 14 0 0 19 Halt 0 0 0 0 20 Transaction 0 1 1 0 1 usesStmtJournal=0 21 Blob 7 8 0 Á¾ªVn 0 r[8]=Á¾ªVn (len=7) 22 Cast 8 66 0 0 affinity(r[8]) 23 Integer 1 9 0 0 r[9]=1 24 Goto 0 1 0 0 QUERY PLAN |--CO-ROUTINE w | `--COMPOUND QUERY | |--LEFT-MOST SUBQUERY | | `--SCAN CONSTANT ROW | `--UNION USING TEMP B-TREE | |--SCAN t1 | `--USE TEMP B-TREE FOR GROUP BY |--SCAN w `--USE TEMP B-TREE FOR ORDER BY addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 86 0 0 Start at 86 1 InitCoroutine 1 65 2 0 w 2 Explain 2 0 0 CO-ROUTINE w 0 3 Explain 3 2 0 COMPOUND QUERY 0 4 Explain 4 3 0 LEFT-MOST SUBQUERY 0 5 OpenEphemeral 2 2 0 k(2,B,B) 0 nColumn=2 6 Explain 6 4 0 SCAN CONSTANT ROW 0 7 Ge 3 12 2 BINARY-8 80 if r[2]>=r[3] goto 12 8 Integer 98 4 0 0 r[4]=98 9 Integer 99 5 0 0 r[5]=99 10 MakeRecord 4 2 6 0 r[6]=mkrec(r[4..5]) 11 IdxInsert 2 6 4 2 0 key=r[6] 12 Explain 12 3 0 UNION USING TEMP B-TREE 0 13 SorterOpen 3 2 0 k(1,B) 0 14 Integer 0 8 0 0 r[8]=0; clear abort flag 15 Null 0 11 11 0 r[11..11]=NULL 16 Gosub 10 55 0 0 17 OpenRead 1 2 0 2 0 root=2 iDb=0; t1 18 Explain 18 12 216 SCAN t1 0 19 Rewind 1 26 0 0 20 Explain 20 12 0 USE TEMP B-TREE FOR GROUP BY 0 21 Column 1 0 16 0 r[16]= cursor 1 column 0 22 Column 1 1 17 0 r[17]= cursor 1 column 1 23 MakeRecord 16 2 6 0 r[6]=mkrec(r[16..17]) 24 SorterInsert 3 6 0 0 key=r[6] 25 Next 1 20 0 1 26 OpenPseudo 4 6 2 0 2 columns in r[6] 27 SorterSort 3 58 0 0 GROUP BY sort 28 SorterData 3 6 4 0 r[6]=data 29 Column 4 0 12 0 r[12]= cursor 4 column 0 30 Compare 11 12 1 k(1,B) 0 r[11] <-> r[12] 31 Jump 32 36 32 0 32 Gosub 9 46 0 0 output one row of 2 33 Move 12 11 1 0 r[11]=r[12] 34 IfPos 8 58 0 0 if r[8]>0 then r[8]-=0, goto 58; check abort flag 35 Gosub 10 55 0 0 reset accumulator 2 36 Column 4 1 18 0 r[18]=t1.b 37 AggStep 0 18 15 sum(1) 1 accum=r[15] step(r[18]) 38 If 7 40 0 0 39 Column 4 0 13 0 r[13]=t1.a 40 Integer 1 7 0 0 r[7]=1; indicate data in accumulator 2 41 SorterNext 3 28 0 0 42 Gosub 9 46 0 0 output final row of 2 43 Goto 0 58 0 0 44 Integer 1 8 0 0 r[8]=1; set abort flag 45 Return 9 0 0 0 46 IfPos 7 48 0 0 if r[7]>0 then r[7]-=0, goto 48; Groupby result generator entry point 2 47 Return 9 0 0 0 48 AggFinal 15 1 0 sum(1) 0 accum=r[15] N=1 49 Ge 3 47 15 BINARY-8 80 if r[15]>=r[3] goto 47 50 SCopy 13 4 0 0 r[4]=r[13] 51 SCopy 15 5 0 0 r[5]=r[15] 52 MakeRecord 4 2 18 0 r[18]=mkrec(r[4..5]) 53 IdxInsert 2 18 4 2 0 key=r[18] 54 Return 9 0 0 0 end groupby result generator 2 55 Null 0 13 15 0 r[13..15]=NULL 56 Integer 0 7 0 0 r[7]=0; indicate accumulator 2 empty 57 Return 10 0 0 0 58 Rewind 2 63 0 0 59 Column 2 0 19 0 r[19]=x 60 Column 2 1 20 0 r[20]=y 61 Yield 1 0 0 0 62 Next 2 59 0 0 63 Close 2 0 0 0 64 EndCoroutine 1 0 0 0 end w 65 SorterOpen 5 4 0 k(1,B) 0 66 Explain 66 0 82 SCAN w 0 67 InitCoroutine 1 0 2 0 68 Yield 1 77 0 0 next row of w 69 Copy 20 21 0 2 r[21]=r[20] 70 Ge 3 76 21 BINARY-8 80 if r[21]>=r[3] goto 76 71 Copy 19 23 0 2 r[23]=r[19] 72 Copy 20 24 0 2 r[24]=r[20] 73 Copy 19 22 0 2 r[22]=r[19] 74 MakeRecord 22 3 25 0 r[25]=mkrec(r[22..24]) 75 SorterInsert 5 25 22 3 0 key=r[25] 76 Goto 0 68 0 0 77 Explain 77 0 0 USE TEMP B-TREE FOR ORDER BY 0 78 OpenPseudo 6 26 4 0 4 columns in r[26] 79 SorterSort 5 85 0 0 80 SorterData 5 26 6 0 r[26]=data 81 Column 6 2 24 0 r[24]=y 82 Column 6 1 23 0 r[23]=x 83 ResultRow 23 2 0 0 output=r[23..24] 84 SorterNext 5 80 0 0 85 Halt 0 0 0 0 86 Transaction 0 0 1 0 1 usesStmtJournal=0 87 Integer 99 2 0 0 r[2]=99 88 Integer 20 3 0 0 r[3]=20 89 Null 0 11 11 0 r[11..11]=NULL 90 Goto 0 1 0 0 0|2 1|18 2|19 QUERY PLAN |--CO-ROUTINE w | `--COMPOUND QUERY | |--LEFT-MOST SUBQUERY | | |--SCAN t1 | | `--USE TEMP B-TREE FOR GROUP BY | `--UNION USING TEMP B-TREE | `--SCAN CONSTANT ROW |--SCAN w `--USE TEMP B-TREE FOR ORDER BY addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 86 0 0 Start at 86 1 InitCoroutine 1 65 2 0 w 2 Explain 2 0 0 CO-ROUTINE w 0 3 Explain 3 2 0 COMPOUND QUERY 0 4 Explain 4 3 0 LEFT-MOST SUBQUERY 0 5 OpenEphemeral 2 2 0 k(2,B,B) 0 nColumn=2 6 SorterOpen 3 2 0 k(1,B) 0 7 Integer 0 3 0 0 r[3]=0; clear abort flag 8 Null 0 6 6 0 r[6..6]=NULL 9 Gosub 5 48 0 0 10 OpenRead 1 2 0 2 0 root=2 iDb=0; t1 11 Explain 11 4 216 SCAN t1 0 12 Rewind 1 19 0 0 13 Explain 13 4 0 USE TEMP B-TREE FOR GROUP BY 0 14 Column 1 0 11 0 r[11]= cursor 1 column 0 15 Column 1 1 12 0 r[12]= cursor 1 column 1 16 MakeRecord 11 2 13 0 r[13]=mkrec(r[11..12]) 17 SorterInsert 3 13 0 0 key=r[13] 18 Next 1 13 0 1 19 OpenPseudo 4 13 2 0 2 columns in r[13] 20 SorterSort 3 51 0 0 GROUP BY sort 21 SorterData 3 13 4 0 r[13]=data 22 Column 4 0 7 0 r[7]= cursor 4 column 0 23 Compare 6 7 1 k(1,B) 0 r[6] <-> r[7] 24 Jump 25 29 25 0 25 Gosub 4 39 0 0 output one row of 1 26 Move 7 6 1 0 r[6]=r[7] 27 IfPos 3 51 0 0 if r[3]>0 then r[3]-=0, goto 51; check abort flag 28 Gosub 5 48 0 0 reset accumulator 1 29 Column 4 1 14 0 r[14]=t1.b 30 AggStep 0 14 10 sum(1) 1 accum=r[10] step(r[14]) 31 If 2 33 0 0 32 Column 4 0 8 0 r[8]=t1.a 33 Integer 1 2 0 0 r[2]=1; indicate data in accumulator 1 34 SorterNext 3 21 0 0 35 Gosub 4 39 0 0 output final row of 1 36 Goto 0 51 0 0 37 Integer 1 3 0 0 r[3]=1; set abort flag 38 Return 4 0 0 0 39 IfPos 2 41 0 0 if r[2]>0 then r[2]-=0, goto 41; Groupby result generator entry point 1 40 Return 4 0 0 0 41 AggFinal 10 1 0 sum(1) 0 accum=r[10] N=1 42 Lt 15 40 10 BINARY-8 80 if r[10]0 then r[1]-=0, goto 18 16 NullRow 1 0 0 0 17 Goto 0 9 0 0 18 Next 0 5 0 1 19 Halt 0 0 0 0 20 Transaction 0 0 1 0 1 usesStmtJournal=0 21 Goto 0 1 0 0 0|2|0|2 1|18|1|18 2|19|2|19 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 3 QUERY PLAN `--SCAN t1 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 0 2 0 2 0 root=2 iDb=0; t1 3 Explain 3 0 216 SCAN t1 0 4 Rewind 0 8 0 0 5 Column 0 1 3 0 r[3]= cursor 0 column 1 6 AggStep 0 3 2 sum(1) 1 accum=r[2] step(r[3]) 7 Next 0 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 1 0 1 usesStmtJournal=0 13 Goto 0 1 0 0 39 QUERY PLAN |--SCAN a `--SEARCH b USING INTEGER PRIMARY KEY (rowid=?) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 15 0 0 Start at 15 1 OpenRead 0 2 0 2 0 root=2 iDb=0; t1 2 OpenRead 1 2 0 2 0 root=2 iDb=0; t1 3 Explain 3 0 216 SCAN a 0 4 Rewind 0 14 0 0 5 Explain 5 0 45 SEARCH b USING INTEGER PRIMARY KEY (rowid=?) 0 6 Rowid 0 1 0 0 r[1]=t1.rowid 7 SeekRowid 1 13 1 0 intkey=r[1] 8 Column 0 0 2 0 r[2]= cursor 0 column 0 9 Column 0 1 3 0 r[3]= cursor 0 column 1 10 Column 1 0 4 0 r[4]= cursor 1 column 0 11 Column 1 1 5 0 r[5]= cursor 1 column 1 12 ResultRow 2 4 0 0 output=r[2..5] 13 Next 0 5 0 1 14 Halt 0 0 0 0 15 Transaction 0 0 1 0 1 usesStmtJournal=0 16 Goto 0 1 0 0 0|2|0|2 1|18|1|18 2|19|2|19 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 39 0 0 Start at 39 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 Noop 0 22 0 0 12 CreateBtree 0 4 2 0 r[4]=root iDb=0 flags=2 13 OpenWrite 1 1 0 5 0 root=1 iDb=0; sqlite_master 14 String8 0 6 0 index 0 r[6]='index' 15 String8 0 7 0 sqlite_autoindex_T_1 0 r[7]='sqlite_autoindex_T_1' 16 String8 0 8 0 T 0 r[8]='T' 17 SCopy 4 9 0 0 r[9]=r[4] 18 Null 0 10 0 0 r[10]=NULL 19 NewRowid 1 5 0 0 r[5]=rowid 20 MakeRecord 6 5 11 BBBDB 0 r[11]=mkrec(r[6..10]) 21 Insert 1 11 5 24 intkey=r[5] data=r[11] 22 Close 0 0 0 0 23 Null 0 12 13 0 r[12..13]=NULL 24 Noop 3 0 12 0 25 OpenWrite 2 1 0 5 0 root=1 iDb=0; sqlite_master 26 SeekRowid 2 28 1 0 intkey=r[1] 27 Rowid 2 13 0 0 r[13]= rowid of 2 28 IsNull 13 36 0 0 if r[13]==NULL goto 36 29 String8 0 14 0 table 0 r[14]='table' 30 String8 0 15 0 T 0 r[15]='T' 31 String8 0 16 0 T 0 r[16]='T' 32 Copy 2 17 0 0 r[17]=r[2] 33 String8 0 18 0 CREATE TABLE T ( A VARCHAR(20), B VARCHAR(10), PRIMARY KEY (A, B) ) 0 r[18]='CREATE TABLE T ( A VARCHAR(20), B VARCHAR(10), PRIMARY KEY (A, B) )' 34 MakeRecord 14 5 12 BBBDB 0 r[12]=mkrec(r[14..18]) 35 Insert 2 12 13 0 intkey=r[13] data=r[12] 36 SetCookie 0 1 2 0 37 ParseSchema 0 0 0 tbl_name='T' AND type!='trigger' 0 38 Halt 0 0 0 0 39 Transaction 0 1 1 0 1 usesStmtJournal=1 40 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 18 0 0 Start at 18 1 OpenWrite 0 3 0 2 0 root=3 iDb=0; T 2 OpenWrite 1 4 0 k(3,,,) 0 root=4 iDb=0; sqlite_autoindex_T_1 3 String8 0 2 0 a 0 r[2]='a' 4 String8 0 3 0 p 0 r[3]='p' 5 NewRowid 0 1 0 0 r[1]=rowid 6 Affinity 2 2 0 BB 0 affinity(r[2..3]) 7 Noop 0 0 0 0 prep index sqlite_autoindex_T_1 8 SCopy 2 5 0 0 r[5]=r[2]; A 9 SCopy 3 6 0 0 r[6]=r[3]; B 10 IntCopy 1 7 0 0 r[7]=r[1]; rowid 11 MakeRecord 5 3 4 0 r[4]=mkrec(r[5..7]); for sqlite_autoindex_T_1 12 NoConflict 1 14 5 2 0 key=r[5..6] 13 Halt 1555 2 0 T.A, T.B 2 14 MakeRecord 2 2 8 0 r[8]=mkrec(r[2..3]) 15 IdxInsert 1 4 5 3 16 key=r[4] 16 Insert 0 8 1 T 57 intkey=r[1] data=r[8] 17 Halt 0 0 0 0 18 Transaction 0 1 2 0 1 usesStmtJournal=0 19 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 18 0 0 Start at 18 1 OpenWrite 0 3 0 2 0 root=3 iDb=0; T 2 OpenWrite 1 4 0 k(3,,,) 0 root=4 iDb=0; sqlite_autoindex_T_1 3 String8 0 2 0 a 0 r[2]='a' 4 String8 0 3 0 q 0 r[3]='q' 5 NewRowid 0 1 0 0 r[1]=rowid 6 Affinity 2 2 0 BB 0 affinity(r[2..3]) 7 Noop 0 0 0 0 prep index sqlite_autoindex_T_1 8 SCopy 2 5 0 0 r[5]=r[2]; A 9 SCopy 3 6 0 0 r[6]=r[3]; B 10 IntCopy 1 7 0 0 r[7]=r[1]; rowid 11 MakeRecord 5 3 4 0 r[4]=mkrec(r[5..7]); for sqlite_autoindex_T_1 12 NoConflict 1 14 5 2 0 key=r[5..6] 13 Halt 1555 2 0 T.A, T.B 2 14 MakeRecord 2 2 8 0 r[8]=mkrec(r[2..3]) 15 IdxInsert 1 4 5 3 16 key=r[4] 16 Insert 0 8 1 T 57 intkey=r[1] data=r[8] 17 Halt 0 0 0 0 18 Transaction 0 1 2 0 1 usesStmtJournal=0 19 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 18 0 0 Start at 18 1 OpenWrite 0 3 0 2 0 root=3 iDb=0; T 2 OpenWrite 1 4 0 k(3,,,) 0 root=4 iDb=0; sqlite_autoindex_T_1 3 String8 0 2 0 b 0 r[2]='b' 4 String8 0 3 0 q 0 r[3]='q' 5 NewRowid 0 1 0 0 r[1]=rowid 6 Affinity 2 2 0 BB 0 affinity(r[2..3]) 7 Noop 0 0 0 0 prep index sqlite_autoindex_T_1 8 SCopy 2 5 0 0 r[5]=r[2]; A 9 SCopy 3 6 0 0 r[6]=r[3]; B 10 IntCopy 1 7 0 0 r[7]=r[1]; rowid 11 MakeRecord 5 3 4 0 r[4]=mkrec(r[5..7]); for sqlite_autoindex_T_1 12 NoConflict 1 14 5 2 0 key=r[5..6] 13 Halt 1555 2 0 T.A, T.B 2 14 MakeRecord 2 2 8 0 r[8]=mkrec(r[2..3]) 15 IdxInsert 1 4 5 3 16 key=r[4] 16 Insert 0 8 1 T 57 intkey=r[1] data=r[8] 17 Halt 0 0 0 0 18 Transaction 0 1 2 0 1 usesStmtJournal=0 19 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 18 0 0 Start at 18 1 OpenWrite 0 3 0 2 0 root=3 iDb=0; T 2 OpenWrite 1 4 0 k(3,,,) 0 root=4 iDb=0; sqlite_autoindex_T_1 3 String8 0 2 0 c 0 r[2]='c' 4 String8 0 3 0 r 0 r[3]='r' 5 NewRowid 0 1 0 0 r[1]=rowid 6 Affinity 2 2 0 BB 0 affinity(r[2..3]) 7 Noop 0 0 0 0 prep index sqlite_autoindex_T_1 8 SCopy 2 5 0 0 r[5]=r[2]; A 9 SCopy 3 6 0 0 r[6]=r[3]; B 10 IntCopy 1 7 0 0 r[7]=r[1]; rowid 11 MakeRecord 5 3 4 0 r[4]=mkrec(r[5..7]); for sqlite_autoindex_T_1 12 NoConflict 1 14 5 2 0 key=r[5..6] 13 Halt 1555 2 0 T.A, T.B 2 14 MakeRecord 2 2 8 0 r[8]=mkrec(r[2..3]) 15 IdxInsert 1 4 5 3 16 key=r[4] 16 Insert 0 8 1 T 57 intkey=r[1] data=r[8] 17 Halt 0 0 0 0 18 Transaction 0 1 2 0 1 usesStmtJournal=0 19 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 18 0 0 Start at 18 1 OpenWrite 0 3 0 2 0 root=3 iDb=0; T 2 OpenWrite 1 4 0 k(3,,,) 0 root=4 iDb=0; sqlite_autoindex_T_1 3 String8 0 2 0 b 0 r[2]='b' 4 String8 0 3 0 r 0 r[3]='r' 5 NewRowid 0 1 0 0 r[1]=rowid 6 Affinity 2 2 0 BB 0 affinity(r[2..3]) 7 Noop 0 0 0 0 prep index sqlite_autoindex_T_1 8 SCopy 2 5 0 0 r[5]=r[2]; A 9 SCopy 3 6 0 0 r[6]=r[3]; B 10 IntCopy 1 7 0 0 r[7]=r[1]; rowid 11 MakeRecord 5 3 4 0 r[4]=mkrec(r[5..7]); for sqlite_autoindex_T_1 12 NoConflict 1 14 5 2 0 key=r[5..6] 13 Halt 1555 2 0 T.A, T.B 2 14 MakeRecord 2 2 8 0 r[8]=mkrec(r[2..3]) 15 IdxInsert 1 4 5 3 16 key=r[4] 16 Insert 0 8 1 T 57 intkey=r[1] data=r[8] 17 Halt 0 0 0 0 18 Transaction 0 1 2 0 1 usesStmtJournal=0 19 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 18 0 0 Start at 18 1 OpenWrite 0 3 0 2 0 root=3 iDb=0; T 2 OpenWrite 1 4 0 k(3,,,) 0 root=4 iDb=0; sqlite_autoindex_T_1 3 String8 0 2 0 b 0 r[2]='b' 4 String8 0 3 0 t 0 r[3]='t' 5 NewRowid 0 1 0 0 r[1]=rowid 6 Affinity 2 2 0 BB 0 affinity(r[2..3]) 7 Noop 0 0 0 0 prep index sqlite_autoindex_T_1 8 SCopy 2 5 0 0 r[5]=r[2]; A 9 SCopy 3 6 0 0 r[6]=r[3]; B 10 IntCopy 1 7 0 0 r[7]=r[1]; rowid 11 MakeRecord 5 3 4 0 r[4]=mkrec(r[5..7]); for sqlite_autoindex_T_1 12 NoConflict 1 14 5 2 0 key=r[5..6] 13 Halt 1555 2 0 T.A, T.B 2 14 MakeRecord 2 2 8 0 r[8]=mkrec(r[2..3]) 15 IdxInsert 1 4 5 3 16 key=r[4] 16 Insert 0 8 1 T 57 intkey=r[1] data=r[8] 17 Halt 0 0 0 0 18 Transaction 0 1 2 0 1 usesStmtJournal=0 19 Goto 0 1 0 0 QUERY PLAN |--SCAN T |--USE TEMP B-TREE FOR GROUP BY |--USE TEMP B-TREE FOR count(DISTINCT) `--USE TEMP B-TREE FOR ORDER BY addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 60 0 0 Start at 60 1 SorterOpen 1 5 0 k(2,-B,B) 0 2 SorterOpen 3 2 0 k(1,B) 0 3 Integer 0 2 0 0 r[2]=0; clear abort flag 4 Null 0 5 5 0 r[5..5]=NULL 5 Gosub 4 46 0 0 6 OpenRead 0 3 0 2 0 root=3 iDb=0; T 7 Explain 7 0 216 SCAN T 0 8 Rewind 0 15 0 0 9 Explain 9 0 0 USE TEMP B-TREE FOR GROUP BY 0 10 Column 0 1 10 0 r[10]= cursor 0 column 1 11 Column 0 0 11 0 r[11]= cursor 0 column 0 12 MakeRecord 10 2 12 0 r[12]=mkrec(r[10..11]) 13 SorterInsert 3 12 0 0 key=r[12] 14 Next 0 9 0 1 15 OpenPseudo 4 12 2 0 2 columns in r[12] 16 SorterSort 3 51 0 0 GROUP BY sort 17 SorterData 3 12 4 0 r[12]=data 18 Column 4 0 6 0 r[6]= cursor 4 column 0 19 Compare 5 6 1 k(1,B) 0 r[5] <-> r[6] 20 Jump 21 25 21 0 21 Gosub 3 38 0 0 output one row of 1 22 Move 6 5 1 0 r[5]=r[6] 23 IfPos 2 51 0 0 if r[2]>0 then r[2]-=0, goto 51; check abort flag 24 Gosub 4 46 0 0 reset accumulator 1 25 Column 4 1 13 0 r[13]=T.A 26 Found 2 30 13 1 0 key=r[13] 27 MakeRecord 13 1 14 0 r[14]=mkrec(r[13]) 28 IdxInsert 2 14 13 1 16 key=r[14] 29 AggStep 0 13 9 count(1) 1 accum=r[9] step(r[13]) 30 If 1 32 0 0 31 Column 4 0 7 0 r[7]=T.B 32 Integer 1 1 0 0 r[1]=1; indicate data in accumulator 1 33 SorterNext 3 17 0 0 34 Gosub 3 38 0 0 output final row of 1 35 Goto 0 51 0 0 36 Integer 1 2 0 0 r[2]=1; set abort flag 37 Return 3 0 0 0 38 IfPos 1 40 0 0 if r[1]>0 then r[1]-=0, goto 40; Groupby result generator entry point 1 39 Return 3 0 0 0 40 AggFinal 9 1 0 count(1) 0 accum=r[9] N=1 41 Copy 9 15 0 0 r[15]=r[9] 42 Copy 7 16 0 0 r[16]=r[7] 43 MakeRecord 15 2 19 0 r[19]=mkrec(r[15..16]) 44 SorterInsert 1 19 15 2 0 key=r[19] 45 Return 3 0 0 0 end groupby result generator 1 46 Null 0 7 9 0 r[7..9]=NULL 47 OpenEphemeral 2 0 0 k(1,B) 0 nColumn=0 48 Explain 48 0 0 USE TEMP B-TREE FOR count(DISTINCT) 0 49 Integer 0 1 0 0 r[1]=0; indicate accumulator 1 empty 50 Return 4 0 0 0 51 Explain 51 0 0 USE TEMP B-TREE FOR ORDER BY 0 52 OpenPseudo 5 20 5 0 5 columns in r[20] 53 SorterSort 1 59 0 0 54 SorterData 1 20 5 0 r[20]=data 55 Column 5 0 18 0 r[18]=D 56 Column 5 1 17 0 r[17]=B 57 ResultRow 17 2 0 0 output=r[17..18] 58 SorterNext 1 54 0 0 59 Halt 0 0 0 0 60 Transaction 0 0 2 0 1 usesStmtJournal=0 61 Null 0 5 5 0 r[5..5]=NULL 62 Goto 0 1 0 0 q|2 r|2 p|1 t|1 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 41 0 0 Start at 41 1 Null 0 1 2 0 r[1..2]=NULL 2 OpenEphemeral 1 0 1 0 nColumn=0 3 OpenRead 0 1 0 2 0 root=1 iDb=0; sqlite_master 4 Rewind 0 12 0 0 5 Column 0 0 8 0 r[8]= cursor 0 column 0 6 Ne 9 11 8 BINARY-8 82 if r[8]!=r[9] goto 11 7 Column 0 1 8 0 r[8]= cursor 0 column 1 8 Ne 10 11 8 BINARY-8 82 if r[8]!=r[10] goto 11 9 Rowid 0 2 0 0 r[2]= rowid of 0 10 Insert 1 1 2 0 intkey=r[2] data=r[1] 11 Next 0 5 0 1 12 OpenWrite 0 1 0 5 0 root=1 iDb=0; sqlite_master 13 Rewind 1 33 0 0 14 Rowid 1 2 0 0 r[2]= rowid of 1 15 NotExists 0 32 2 0 intkey=r[2] 16 Column 0 0 3 0 r[3]= cursor 0 column 0 17 Column 0 1 4 0 r[4]= cursor 0 column 1 18 Column 0 2 5 0 r[5]= cursor 0 column 2 19 Column 0 3 6 0 r[6]= cursor 0 column 3 20 Column 0 4 14 0 r[14]= cursor 0 column 4 21 Function 1 13 12 printf(-1) 0 r[12]=func(r[13..14]) 22 Concat 15 12 8 0 r[8]=r[12]+r[15] 23 Column 0 4 16 0 r[16]= cursor 0 column 4 24 Column 0 4 22 0 r[22]= cursor 0 column 4 25 Function 1 21 20 printf(-1) 0 r[20]=func(r[21..22]) 26 Function 0 20 19 length(1) 0 r[19]=func(r[20]) 27 Add 19 18 17 0 r[17]=r[19]+r[18] 28 Function 0 16 12 substr(2) 0 r[12]=func(r[16..17]) 29 Concat 12 8 7 0 r[7]=r[8]+r[12] 30 MakeRecord 3 5 11 BBBDB 0 r[11]=mkrec(r[3..7]) 31 Insert 0 11 2 0 intkey=r[2] data=r[11] 32 Next 1 14 0 0 33 ReadCookie 0 12 2 0 34 AddImm 12 -2 0 0 r[12]=r[12]+-2 35 IfPos 12 37 0 0 if r[12]>0 then r[12]-=0, goto 37 36 SetCookie 0 2 3 0 37 SetCookie 0 1 3 0 38 ParseSchema 0 0 0 3 39 ParseSchema 1 0 0 3 40 Halt 0 0 0 0 41 Transaction 0 1 2 0 1 usesStmtJournal=1 42 String8 0 9 0 table 0 r[9]='table' 43 String8 0 10 0 t1 0 r[10]='t1' 44 String8 0 13 0 %.28s, 0 r[13]='%.28s, ' 45 String8 0 15 0 extra_7039 CHAR(10) 0 r[15]='extra_7039 CHAR(10)' 46 Integer 1 18 0 0 r[18]=1 47 String8 0 21 0 %.28s 0 r[21]='%.28s' 48 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 3 0 0 Start at 3 1 Savepoint 2 0 0 sp7156 0 2 Halt 0 0 0 0 3 Goto 0 1 0 0 addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 3 0 0 Start at 3 1 Savepoint 1 0 0 sp7156 0 2 Halt 0 0 0 0 3 Goto 0 1 0 0 ``` ## Flag ``` ```