91 KiB
91 KiB
Summary
**No review yet**Minimized query
.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
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]<r[15] goto 35
38 SCopy 2 16 0 0 r[16]=r[2]
39 SCopy 3 17 0 0 r[17]=r[3]
40 MakeRecord 16 2 14 0 r[14]=mkrec(r[16..17])
41 IdxInsert 2 14 16 2 0 key=r[14]
42 Return 7 0 0 0 end groupby result generator
43 Null 0 2 4 0 r[2..4]=NULL
44 Integer 0 5 0 0 r[5]=0; indicate accumulator empty
45 Return 8 0 0 0
46 Lt 15 51 18 BINARY-8 80 if r[18]<r[15] goto 51
47 Integer 98 16 0 0 r[16]=98
48 Integer 99 17 0 0 r[17]=99
49 MakeRecord 16 2 14 0 r[14]=mkrec(r[16..17])
50 IdxInsert 2 14 16 2 0 key=r[14]
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 Lt 15 68 21 BINARY-8 80 if r[21]<r[15] 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 20 15 0 0 r[15]=20
79 Integer 99 18 0 0 r[18]=99
80 Goto 0 1 0 0
98|99
QUERY PLAN
|--SCAN a
`--SEARCH b USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 18 0 0 Start at 18
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 17 0 0
4 Integer 0 1 0 0 r[1]=0; init LEFT JOIN no-match flag
5 Rowid 0 2 0 0 r[2]=t1.rowid
6 SeekRowid 1 13 2 0 intkey=r[2]
7 Integer 1 1 0 0 r[1]=1; record LEFT JOIN hit
8 Column 0 0 3 0 r[3]=t1.a
9 Column 0 1 4 0 r[4]=t1.b
10 Column 1 0 5 0 r[5]=t1.a
11 Column 1 1 6 0 r[6]=t1.b
12 ResultRow 3 4 0 0 output=r[3..6]
13 IfPos 1 16 0 0 if r[1]>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
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]<r[15] goto 40
43 SCopy 8 16 0 0 r[16]=r[8]
44 SCopy 10 17 0 0 r[17]=r[10]
45 MakeRecord 16 2 14 0 r[14]=mkrec(r[16..17])
46 IdxInsert 2 14 16 2 0 key=r[14]
47 Return 4 0 0 0 end groupby result generator 1
48 Null 0 8 10 0 r[8..10]=NULL
49 Integer 0 2 0 0 r[2]=0; indicate accumulator 1 empty
50 Return 5 0 0 0
51 Explain 51 3 0 UNION USING TEMP B-TREE 0
52 Explain 52 51 0 SCAN CONSTANT ROW 0
53 Lt 15 58 18 BINARY-8 80 if r[18]<r[15] goto 58
54 Integer 98 16 0 0 r[16]=98
55 Integer 99 17 0 0 r[17]=99
56 MakeRecord 16 2 14 0 r[14]=mkrec(r[16..17])
57 IdxInsert 2 14 16 2 0 key=r[14]
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 Lt 15 76 21 BINARY-8 80 if r[21]<r[15] 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 Null 0 6 6 0 r[6..6]=NULL
88 Integer 20 15 0 0 r[15]=20
89 Integer 99 18 0 0 r[18]=99
90 Goto 0 1 0 0
98|99
QUERY PLAN
|--SCAN a
`--SEARCH b USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 20 0 0 Start at 20
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 19 0 0
5 Explain 5 0 45 SEARCH b USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN 0
6 Integer 0 1 0 0 r[1]=0; init LEFT JOIN match flag
7 Rowid 0 2 0 0 r[2]=t1.rowid
8 SeekRowid 1 15 2 0 intkey=r[2]
9 Integer 1 1 0 0 r[1]=1; record LEFT JOIN hit
10 Column 0 0 3 0 r[3]= cursor 0 column 0
11 Column 0 1 4 0 r[4]= cursor 0 column 1
12 Column 1 0 5 0 r[5]= cursor 1 column 0
13 Column 1 1 6 0 r[6]= cursor 1 column 1
14 ResultRow 3 4 0 0 output=r[3..6]
15 IfPos 1 18 0 0 if r[1]>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