56 KiB
56 KiB
Summary
**No review yet**Minimized query
PRAGMA wal_checkpoint('FULL');
.eqp full
CREATE TABLE T (
a TEXT,
b TEXT,
c REAL
);
INSERT INTO T VALUES ('a','b',CAST(1.5 AS BIGINT)), ('a','c',-2.25), ('b','b',4.75);
SELECT a,
SUM(CASE WHEN b='b' THEN c ELSE 0 END) AS d,
SUM(CASE WHEN b='c' THEN c ELSE 0 END) AS e
FROM T
GROUP BY a;
UPDATE T SET a = '' WHERE a BETWEEN 0 AND 100 RETURNING *;
REINDEX T;
INSERT OR REPLACE INTO T VALUES ('', 'x', 'x');
SELECT MAX(c) OVER (PARTITION BY c ORDER BY c RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS) FROM T;
DROP TABLE IF EXISTS T;
ANALYZE;
Actual output
0|-1|-1
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 28 0 0 Start at 28
1 ReadCookie 0 3 2 0
2 If 3 5 0 0
3 SetCookie 0 2 4 0
4 SetCookie 0 5 1 0
5 CreateBtree 0 2 1 0 r[2]=root iDb=0 flags=1
6 OpenWrite 0 1 0 5 0 root=1 iDb=0
7 NewRowid 0 1 0 0 r[1]=rowid
8 Blob 6 3 0 0 r[3]= (len=6)
9 Insert 0 3 1 8 intkey=r[1] data=r[3]
10 Close 0 0 0 0
11 Close 0 0 0 0
12 Null 0 4 5 0 r[4..5]=NULL
13 Noop 2 0 4 0
14 OpenWrite 1 1 0 5 0 root=1 iDb=0; sqlite_master
15 SeekRowid 1 17 1 0 intkey=r[1]
16 Rowid 1 5 0 0 r[5]=[P2]=PX rowid of P1
17 IsNull 5 25 0 0 if r[5]==NULL goto 25
18 String8 0 6 0 table 0 r[6]='table'
19 String8 0 7 0 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 TEXT,
c REAL
) 0 r[10]='CREATE TABLE T (
a TEXT,
b TEXT,
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 1 0
26 ParseSchema 0 0 0 tbl_name='T' 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 23 0 0 Start at 23
1 InitCoroutine 5 16 2 0
2 String8 0 2 0 a 0 r[2]='a'
3 String8 0 3 0 b 0 r[3]='b'
4 Real 0 4 0 1.5 0 r[4]=1.5
5 Cast 4 68 0 0 affinity(r[4])
6 Yield 5 0 0 0
7 String8 0 2 0 a 0 r[2]='a'
8 String8 0 3 0 c 0 r[3]='c'
9 Real 0 4 0 -2.25 0 r[4]=-2.25
10 Yield 5 0 0 0
11 String8 0 2 0 b 0 r[2]='b'
12 String8 0 3 0 b 0 r[3]='b'
13 Real 0 4 0 4.75 0 r[4]=4.75
14 Yield 5 0 0 0
15 EndCoroutine 5 0 0 0
16 OpenWrite 0 2 0 3 0 root=2 iDb=0; T
17 Yield 5 22 0 0
18 NewRowid 0 1 0 0 r[1]=rowid
19 MakeRecord 2 3 6 BBE 0 r[6]=mkrec(r[2..4])
20 Insert 0 6 1 T 57 intkey=r[1] data=r[6]
21 Goto 0 17 0 0
22 Halt 0 0 0 0
23 Transaction 0 1 1 0 1 usesStmtJournal=0
24 Goto 0 1 0 0
QUERY PLAN
|--SCAN T
`--USE TEMP B-TREE FOR GROUP BY
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 57 0 0 Start at 57
1 SorterOpen 1 3 0 k(1,B) 0
2 Integer 0 7 0 0 r[7]=0; clear abort flag
3 Null 0 10 10 0 r[10..10]=NULL
4 Gosub 9 53 0 0
5 OpenRead 0 2 0 3 0 root=2 iDb=0; T
6 Rewind 0 14 0 0
7 Column 0 0 12 0 r[12]=T.a
8 Column 0 1 13 0 r[13]=T.b
9 Column 0 2 14 0 r[14]=T.c
10 RealAffinity 14 0 0 0
11 MakeRecord 12 3 15 0 r[15]=mkrec(r[12..14])
12 SorterInsert 1 15 0 0 key=r[15]
13 Next 0 7 0 1
14 OpenPseudo 2 15 3 0 3 columns in r[15]
15 SorterSort 1 56 0 0 GROUP BY sort
16 SorterData 1 15 2 0 r[15]=data
17 Column 2 0 11 0 r[11]=[P3]=PX cursor P1 column P2
18 Compare 10 11 1 k(1,B) 0 r[10] <-> r[11]
19 Jump 20 24 20 0
20 Move 11 10 1 0 r[10]=r[11]
21 Gosub 8 46 0 0 output one row
22 IfPos 7 56 0 0 if r[7]>0 then r[7]-=0, goto 56; check abort flag
23 Gosub 9 53 0 0 reset accumulator
24 Column 2 1 17 0 r[17]=T.b
25 Ne 18 29 17 BINARY-8 82 if r[17]!=r[18] goto 29
26 Column 2 2 16 0 r[16]=T.c
27 RealAffinity 16 0 0 0
28 Goto 0 30 0 0
29 Integer 0 16 0 0 r[16]=0
30 AggStep 0 16 2 sum(1) 1 accum=r[2] step(r[16])
31 Column 2 1 17 0 r[17]=T.b
32 Ne 19 36 17 BINARY-8 82 if r[17]!=r[19] goto 36
33 Column 2 2 16 0 r[16]=T.c
34 RealAffinity 16 0 0 0
35 Goto 0 37 0 0
36 Integer 0 16 0 0 r[16]=0
37 AggStep 0 16 3 sum(1) 1 accum=r[3] step(r[16])
38 If 6 40 0 0
39 Column 2 0 1 0 r[1]=T.a
40 Integer 1 6 0 0 r[6]=1; indicate data in accumulator
41 SorterNext 1 16 0 0
42 Gosub 8 46 0 0 output final row
43 Goto 0 56 0 0
44 Integer 1 7 0 0 r[7]=1; set abort flag
45 Return 8 0 0 0
46 IfPos 6 48 0 0 if r[6]>0 then r[6]-=0, goto 48; Groupby result generator entry point
47 Return 8 0 0 0
48 AggFinal 2 1 0 sum(1) 0 accum=r[2] N=1
49 AggFinal 3 1 0 sum(1) 0 accum=r[3] N=1
50 Copy 1 20 2 0 r[20..22]=r[1..3]
51 ResultRow 20 3 0 0 output=r[20..22]
52 Return 8 0 0 0 end groupby result generator
53 Null 0 1 5 0 r[1..5]=NULL
54 Integer 0 6 0 0 r[6]=0; indicate accumulator empty
55 Return 9 0 0 0
56 Halt 0 0 0 0
57 Transaction 0 0 1 0 1 usesStmtJournal=0
58 String8 0 18 0 b 0 r[18]='b'
59 String8 0 19 0 c 0 r[19]='c'
60 Goto 0 1 0 0
a|1.0|-2.25
b|4.75|0
QUERY PLAN
`--SCAN T
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 2 0 1 0 root=2 iDb=0; T
4 Rewind 0 11 0 0
5 Column 0 0 10 0 r[10]=T.a
6 Lt 12 10 10 BINARY-8 82 if r[10]<r[12] goto 10
7 Gt 13 10 10 BINARY-8 82 if r[10]>r[13] goto 10
8 Rowid 0 2 0 0 r[2]=[P2]=PX rowid of P1
9 Insert 1 1 2 0 intkey=r[2] data=r[1]
10 Next 0 5 0 1
11 OpenWrite 0 2 0 3 0 root=2 iDb=0; T
12 Rewind 1 33 0 0
13 Rowid 1 2 0 0 r[2]=[P2]=PX rowid of P1
14 NotExists 0 32 2 0 intkey=r[2]
15 Column 0 0 3 0 r[3]=T.a
16 Column 0 1 4 0 r[4]=T.b
17 Column 0 2 5 0 r[5]=T.c
18 RealAffinity 5 0 0 0
19 Copy 2 6 0 0 r[6]=r[2]
20 String8 0 7 0 0 r[7]=''
21 Column 0 1 8 0 r[8]=T.b
22 Column 0 2 9 0 r[9]=T.c
23 RealAffinity 9 0 0 0
24 MakeRecord 7 3 14 BBE 0 r[14]=mkrec(r[7..9])
25 Insert 0 14 6 T 5 intkey=r[6] data=r[14]
26 SCopy 7 15 0 0 r[15]=r[7]
27 SCopy 8 16 0 0 r[16]=r[8]
28 SCopy 9 17 0 0 r[17]=r[9]
29 MakeRecord 15 3 18 0 r[18]=mkrec(r[15..17])
30 NewRowid 3 19 0 0 r[19]=rowid
31 Insert 3 18 19 0 intkey=r[19] data=r[18]
32 Next 1 13 0 0
33 FkCheck 0 0 0 0
34 Rewind 3 40 0 0
35 Column 3 0 15 0 r[15]=[P3]=PX cursor P1 column P2
36 Column 3 1 16 0 r[16]=[P3]=PX cursor P1 column P2
37 Column 3 2 17 0 r[17]=[P3]=PX cursor P1 column P2
38 ResultRow 15 3 0 0 output=r[15..17]
39 Next 3 35 0 0
40 Halt 0 0 0 0
41 Transaction 0 1 1 0 1 usesStmtJournal=0
42 Integer 0 12 0 0 r[12]=0
43 Integer 100 13 0 0 r[13]=100
44 OpenEphemeral 3 3 0 0 nColumn=3
45 Goto 0 1 0 0
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 1 0 0 Start at 1
1 Halt 0 0 0 0
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 9 0 0 Start at 9
1 OpenWrite 0 2 0 3 0 root=2 iDb=0; T
2 String8 0 2 0 0 r[2]=''
3 String8 0 3 0 x 0 r[3]='x'
4 String8 0 4 0 x 0 r[4]='x'
5 NewRowid 0 1 0 0 r[1]=rowid
6 MakeRecord 2 3 5 BBE 0 r[5]=mkrec(r[2..4])
7 Insert 0 5 1 T 57 intkey=r[1] data=r[5]
8 Halt 0 0 0 0
9 Transaction 0 1 1 0 1 usesStmtJournal=0
10 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 26 3 0 (subquery-2)
3 SorterOpen 6 6 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 2 6 0 r[6]=T.c
7 RealAffinity 6 0 0 0
8 Column 0 2 7 0 r[7]=T.c
9 RealAffinity 7 0 0 0
10 Column 0 2 4 0 r[4]=T.c
11 RealAffinity 4 0 0 0
12 Column 0 2 5 0 r[5]=T.c
13 RealAffinity 5 0 0 0
14 MakeRecord 4 4 9 0 r[9]=mkrec(r[4..7])
15 SorterInsert 6 9 4 4 0 key=r[9]
16 Next 0 6 0 1
17 OpenPseudo 7 10 6 0 6 columns in r[10]
18 SorterSort 6 25 0 0
19 SorterData 6 10 7 0 r[10]=data
20 Column 7 1 8 0 r[8]=[P3]=PX cursor P1 column P2
21 Column 7 3 7 0 r[7]=[P3]=PX cursor P1 column P2
22 Column 7 2 6 0 r[6]=[P3]=PX cursor P1 column P2
23 Yield 3 0 0 0
24 SorterNext 6 19 0 0
25 EndCoroutine 3 0 0 0
26 OpenEphemeral 1 3 0 0 nColumn=3
27 OpenDup 2 1 0 0
28 OpenDup 3 1 0 0
29 OpenDup 4 1 0 0
30 Null 0 11 11 0 r[11..11]=NULL
31 Integer 1 12 0 0 r[12]=1
32 Integer 1 13 0 0 r[13]=1
33 Integer 0 14 0 0 r[14]=0
34 OpenDup 8 1 0 0
35 InitCoroutine 3 0 3 0
36 Yield 3 68 0 0 next row of
37 Copy 6 16 0 0 r[16]=r[6]
38 Copy 7 17 0 0 r[17]=r[7]
39 Copy 8 18 0 0 r[18]=r[8]
40 MakeRecord 16 3 19 0 r[19]=mkrec(r[16..18])
41 Compare 16 11 1 k(1,B) 0 r[16] <-> r[11]
42 Jump 43 45 43 0
43 Gosub 25 69 0 0 call flush_partition
44 Copy 16 11 0 0 r[11]=r[16]
45 NewRowid 2 20 0 0 r[20]=rowid
46 Insert 2 19 20 0 intkey=r[20] data=r[19]
47 Ne 12 57 20 0 if r[20]!=r[12] goto 57
48 Null 0 1 0 0 r[1]=NULL
49 Rewind 3 1 0 0
50 Rewind 1 1 0 0
51 Rewind 4 1 0 0
52 Copy 17 21 0 0 r[21]=r[17]
53 Copy 21 22 0 0 r[22]=r[21]
54 Copy 21 23 0 0 r[23]=r[21]
55 Copy 21 24 0 0 r[24]=r[21]
56 Goto 0 67 0 0
57 Compare 21 17 1 k(1,B) 0 r[21] <-> r[17]
58 Jump 59 67 59 0
59 Copy 17 21 0 0 r[21]=r[17]
60 AddImm 14 1 0 0 r[14]=r[14]+1
61 Next 4 63 0 0
62 Goto 0 67 0 0
63 Column 4 1 27 0 r[27]=[P3]=PX cursor P1 column P2
64 Compare 24 27 1 k(1,B) 0 r[24] <-> r[27]
65 Jump 66 60 66 0
66 Copy 27 24 0 0 r[24]=r[27]
67 Goto 0 36 0 0
68 Integer 109 25 0 0 r[25]=109
69 Rewind 2 106 0 0
70 AddImm 14 1 0 0 r[14]=r[14]+1
71 Next 4 73 0 0
72 Goto 0 77 0 0
73 Column 4 1 27 0 r[27]=[P3]=PX cursor P1 column P2
74 Compare 24 27 1 k(1,B) 0 r[24] <-> r[27]
75 Jump 76 70 76 0
76 Copy 27 24 0 0 r[24]=r[27]
77 Rowid 1 27 0 0 r[27]=[P2]=PX rowid of P1
78 Column 1 1 29 0 r[29]=[P3]=PX cursor P1 column P2
79 Null 0 1 0 0 r[1]=NULL
80 SeekGE 8 87 13 0 key=r[13]
81 Rowid 8 28 0 0 r[28]=[P2]=PX rowid of P1
82 Gt 14 87 28 0 if r[28]>r[14] goto 87
83 Column 8 2 26 0 r[26]=[P3]=PX cursor P1 column P2
84 CollSeq 0 0 0 BINARY-8 0
85 AggStep 0 26 1 max(1) 1 accum=r[1] step(r[26])
86 Next 8 81 0 0
87 AggFinal 1 1 0 max(1) 0 accum=r[1] N=1
88 Copy 1 2 0 0 r[2]=r[1]
89 Null 0 1 0 0 r[1]=NULL
90 Gosub 15 111 0 0
91 Next 1 93 0 0
92 Goto 0 106 0 0
93 Column 1 1 29 0 r[29]=[P3]=PX cursor P1 column P2
94 Compare 23 29 1 k(1,B) 0 r[23] <-> r[29]
95 Jump 96 77 96 0
96 Copy 29 23 0 0 r[23]=r[29]
97 AddImm 13 1 0 0 r[13]=r[13]+1
98 Delete 3 0 0 2
99 Next 3 101 0 0
100 Goto 0 105 0 0
101 Column 3 1 29 0 r[29]=[P3]=PX cursor P1 column P2
102 Compare 22 29 1 k(1,B) 0 r[22] <-> r[29]
103 Jump 104 97 104 0
104 Copy 29 22 0 0 r[22]=r[29]
105 Goto 0 77 0 0
106 ResetSorter 1 0 0 0
107 Integer 1 13 0 0 r[13]=1
108 Integer 0 14 0 0 r[14]=0
109 Return 25 0 0 0
110 Goto 0 115 0 0
111 Noop 0 0 0 0 inner-loop subroutine
112 Copy 2 31 0 0 r[31]=r[2]
113 ResultRow 31 1 0 0 output=r[31]
114 Return 15 0 0 0 end inner-loop subroutine
115 Halt 0 0 0 0
116 Transaction 0 0 1 0 1 usesStmtJournal=0
117 Goto 0 1 0 0
-2.25
1.0
4.75
x
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 37 0 0 Start at 37
1 Null 0 1 0 0 r[1]=NULL
2 OpenWrite 0 1 0 5 0 root=1 iDb=0; sqlite_master
3 Rewind 0 11 0 0
4 Column 0 2 2 0 r[2]=sqlite_master.tbl_name
5 Ne 3 10 2 BINARY-8 82 if r[2]!=r[3] goto 10
6 Column 0 0 2 0 r[2]=sqlite_master.type
7 Eq 4 10 2 BINARY-8 82 if r[2]==r[4] goto 10
8 Rowid 0 5 0 0 r[5]=sqlite_master.rowid
9 Delete 0 0 0 2
10 Next 0 4 0 1
11 Destroy 2 2 0 0
12 Null 0 6 7 0 r[6..7]=NULL
13 OpenEphemeral 2 0 6 0 nColumn=0
14 IfNot 2 22 1 0
15 OpenRead 1 1 0 4 0 root=1 iDb=0; sqlite_master
16 Rewind 1 22 0 0
17 Column 1 3 13 0 r[13]=sqlite_master.rootpage
18 Ne 2 21 13 BINARY-8 84 if r[13]!=r[2] goto 21
19 Rowid 1 7 0 0 r[7]=[P2]=PX rowid of P1
20 Insert 2 6 7 0 intkey=r[7] data=r[6]
21 Next 1 17 0 1
22 OpenWrite 1 1 0 5 0 root=1 iDb=0; sqlite_master
23 Rewind 2 34 0 0
24 Rowid 2 7 0 0 r[7]=[P2]=PX rowid of P1
25 NotExists 1 33 7 0 intkey=r[7]
26 Column 1 0 8 0 r[8]=sqlite_master.type
27 Column 1 1 9 0 r[9]=sqlite_master.name
28 Column 1 2 10 0 r[10]=sqlite_master.tbl_name
29 Integer 2 11 0 0 r[11]=2
30 Column 1 4 12 0 r[12]=sqlite_master.sql
31 MakeRecord 8 5 15 BBBDB 0 r[15]=mkrec(r[8..12])
32 Insert 1 15 7 0 intkey=r[7] data=r[15]
33 Next 2 24 0 0
34 DropTable 0 0 0 T 0
35 SetCookie 0 1 2 0
36 Halt 0 0 0 0
37 Transaction 0 1 1 0 1 usesStmtJournal=1
38 String8 0 3 0 T 0 r[3]='T'
39 String8 0 4 0 trigger 0 r[4]='trigger'
40 Goto 0 1 0 0
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 31 0 0 Start at 31
1 ReadCookie 0 3 2 0
2 If 3 5 0 0
3 SetCookie 0 2 4 0
4 SetCookie 0 5 1 0
5 CreateBtree 0 2 1 0 r[2]=root iDb=0 flags=1
6 OpenWrite 0 1 0 5 0 root=1 iDb=0
7 NewRowid 0 1 0 0 r[1]=rowid
8 Blob 6 3 0 0 r[3]= (len=6)
9 Insert 0 3 1 8 intkey=r[1] data=r[3]
10 Close 0 0 0 0
11 Close 0 0 0 0
12 Null 0 4 5 0 r[4..5]=NULL
13 Noop 4 0 4 0
14 OpenWrite 3 1 0 5 0 root=1 iDb=0; sqlite_master
15 SeekRowid 3 17 1 0 intkey=r[1]
16 Rowid 3 5 0 0 r[5]=[P2]=PX rowid of P1
17 IsNull 5 25 0 0 if r[5]==NULL goto 25
18 String8 0 6 0 table 0 r[6]='table'
19 String8 0 7 0 sqlite_stat1 0 r[7]='sqlite_stat1'
20 String8 0 8 0 sqlite_stat1 0 r[8]='sqlite_stat1'
21 SCopy 2 9 0 0 r[9]=r[2]
22 String8 0 10 0 CREATE TABLE sqlite_stat1(tbl,idx,stat) 0 r[10]='CREATE TABLE sqlite_stat1(tbl,idx,stat)'
23 MakeRecord 6 5 4 BBBDB 0 r[4]=mkrec(r[6..10])
24 Insert 3 4 5 0 intkey=r[5] data=r[4]
25 SetCookie 0 1 3 0
26 ParseSchema 0 0 0 tbl_name='sqlite_stat1' AND type!='trigger' 0
27 OpenWrite 0 2 0 3 16 root=2 iDb=0; sqlite_stat1
28 LoadAnalysis 0 0 0 0
29 Expire 0 0 0 0
30 Halt 0 0 0 0
31 Transaction 0 1 2 0 1 usesStmtJournal=1
32 Goto 0 1 0 0
Expectation
0|-1|-1
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 28 0 0 Start at 28
1 ReadCookie 0 3 2 0
2 If 3 5 0 0
3 SetCookie 0 2 4 0
4 SetCookie 0 5 1 0
5 CreateBtree 0 2 1 0 r[2]=root iDb=0 flags=1
6 OpenWrite 0 1 0 5 0 root=1 iDb=0
7 NewRowid 0 1 0 0 r[1]=rowid
8 Blob 6 3 0 0 r[3]= (len=6)
9 Insert 0 3 1 8 intkey=r[1] data=r[3]
10 Close 0 0 0 0
11 Close 0 0 0 0
12 Null 0 4 5 0 r[4..5]=NULL
13 Noop 2 0 4 0
14 OpenWrite 1 1 0 5 0 root=1 iDb=0; sqlite_master
15 SeekRowid 1 17 1 0 intkey=r[1]
16 Rowid 1 5 0 0 r[5]= rowid of 1
17 IsNull 5 25 0 0 if r[5]==NULL goto 25
18 String8 0 6 0 table 0 r[6]='table'
19 String8 0 7 0 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 TEXT,
c REAL
) 0 r[10]='CREATE TABLE T (
a TEXT,
b TEXT,
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 1 0
26 ParseSchema 0 0 0 tbl_name='T' 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
`--COMPOUND QUERY
|--LEFT-MOST SUBQUERY
| `--SCAN CONSTANT ROW
`--UNION ALL
`--SCAN 2-ROW VALUES CLAUSE
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 37 0 0 Start at 37
1 InitCoroutine 1 11 2 0
2 String8 0 4 0 a 0 r[4]='a'
3 String8 0 5 0 c 0 r[5]='c'
4 Real 0 6 0 -2.25 0 r[6]=-2.25
5 Yield 1 0 0 0
6 String8 0 4 0 b 0 r[4]='b'
7 String8 0 5 0 b 0 r[5]='b'
8 Real 0 6 0 4.75 0 r[6]=4.75
9 Yield 1 0 0 0
10 EndCoroutine 1 0 0 0
11 InitCoroutine 14 30 12 0
12 Explain 12 0 0 COMPOUND QUERY 0
13 Explain 13 12 0 LEFT-MOST SUBQUERY 0
14 Explain 14 13 0 SCAN CONSTANT ROW 0
15 String8 0 11 0 a 0 r[11]='a'
16 String8 0 12 0 b 0 r[12]='b'
17 Real 0 13 0 1.5 0 r[13]=1.5
18 Cast 13 68 0 0 affinity(r[13])
19 Yield 14 0 0 0
20 Explain 20 12 0 UNION ALL 0
21 Explain 21 20 216 SCAN 2-ROW VALUES CLAUSE 0
22 InitCoroutine 1 0 2 0
23 Yield 1 29 0 0 next row of 2-ROW VALUES CLAUSE
24 Copy 4 11 0 2 r[11]=r[4]
25 Copy 5 12 0 2 r[12]=r[5]
26 Copy 6 13 0 2 r[13]=r[6]
27 Yield 14 0 0 0
28 Goto 0 23 0 0
29 EndCoroutine 14 0 0 0
30 OpenWrite 1 2 0 3 0 root=2 iDb=0; T
31 Yield 14 36 0 0
32 NewRowid 1 10 0 0 r[10]=rowid
33 MakeRecord 11 3 15 BBE 0 r[15]=mkrec(r[11..13])
34 Insert 1 15 10 T 57 intkey=r[10] data=r[15]
35 Goto 0 31 0 0
36 Halt 0 0 0 0
37 Transaction 0 1 1 0 1 usesStmtJournal=0
38 Goto 0 1 0 0
QUERY PLAN
|--SCAN T
`--USE TEMP B-TREE FOR GROUP BY
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 60 0 0 Start at 60
1 SorterOpen 1 3 0 k(1,B) 0
2 Integer 0 2 0 0 r[2]=0; clear abort flag
3 Null 0 5 5 0 r[5..5]=NULL
4 Gosub 4 56 0 0
5 OpenRead 0 2 0 3 0 root=2 iDb=0; T
6 Explain 6 0 216 SCAN T 0
7 Rewind 0 16 0 0
8 Explain 8 0 0 USE TEMP B-TREE FOR GROUP BY 0
9 Column 0 0 12 0 r[12]= cursor 0 column 0
10 Column 0 1 13 0 r[13]= cursor 0 column 1
11 Column 0 2 14 0 r[14]= cursor 0 column 2
12 RealAffinity 14 0 0 0
13 MakeRecord 12 3 15 0 r[15]=mkrec(r[12..14])
14 SorterInsert 1 15 0 0 key=r[15]
15 Next 0 8 0 1
16 OpenPseudo 2 15 3 0 3 columns in r[15]
17 SorterSort 1 59 0 0 GROUP BY sort
18 SorterData 1 15 2 0 r[15]=data
19 Column 2 0 6 0 r[6]= cursor 2 column 0
20 Compare 5 6 1 k(1,B) 0 r[5] <-> r[6]
21 Jump 22 26 22 0
22 Gosub 3 48 0 0 output one row of 1
23 Move 6 5 1 0 r[5]=r[6]
24 IfPos 2 59 0 0 if r[2]>0 then r[2]-=0, goto 59; check abort flag
25 Gosub 4 56 0 0 reset accumulator 1
26 Column 2 1 17 0 r[17]=T.b
27 Ne 18 31 17 BINARY-8 82 if r[17]!=r[18] goto 31
28 Column 2 2 16 0 r[16]=T.c
29 RealAffinity 16 0 0 0
30 Goto 0 32 0 0
31 Integer 0 16 0 0 r[16]=0
32 AggStep 0 16 10 sum(1) 1 accum=r[10] step(r[16])
33 Column 2 1 17 0 r[17]=T.b
34 Ne 19 38 17 BINARY-8 82 if r[17]!=r[19] goto 38
35 Column 2 2 16 0 r[16]=T.c
36 RealAffinity 16 0 0 0
37 Goto 0 39 0 0
38 Integer 0 16 0 0 r[16]=0
39 AggStep 0 16 11 sum(1) 1 accum=r[11] step(r[16])
40 If 1 42 0 0
41 Column 2 0 7 0 r[7]=T.a
42 Integer 1 1 0 0 r[1]=1; indicate data in accumulator 1
43 SorterNext 1 18 0 0
44 Gosub 3 48 0 0 output final row of 1
45 Goto 0 59 0 0
46 Integer 1 2 0 0 r[2]=1; set abort flag
47 Return 3 0 0 0
48 IfPos 1 50 0 0 if r[1]>0 then r[1]-=0, goto 50; Groupby result generator entry point 1
49 Return 3 0 0 0
50 AggFinal 10 1 0 sum(1) 0 accum=r[10] N=1
51 AggFinal 11 1 0 sum(1) 0 accum=r[11] N=1
52 Copy 7 20 0 0 r[20]=r[7]
53 Copy 10 21 1 0 r[21..22]=r[10..11]
54 ResultRow 20 3 0 0 output=r[20..22]
55 Return 3 0 0 0 end groupby result generator 1
56 Null 0 7 11 0 r[7..11]=NULL
57 Integer 0 1 0 0 r[1]=0; indicate accumulator 1 empty
58 Return 4 0 0 0
59 Halt 0 0 0 0
60 Transaction 0 0 1 0 1 usesStmtJournal=0
61 Null 0 5 5 0 r[5..5]=NULL
62 String8 0 18 0 b 0 r[18]='b'
63 String8 0 19 0 c 0 r[19]='c'
64 Goto 0 1 0 0
a|1.0|-2.25
b|4.75|0
QUERY PLAN
`--SCAN T
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 43 0 0 Start at 43
1 Null 0 1 2 0 r[1..2]=NULL
2 OpenEphemeral 1 0 1 0 nColumn=0
3 OpenRead 0 2 0 1 0 root=2 iDb=0; T
4 Explain 4 0 216 SCAN T 0
5 Rewind 0 12 0 0
6 Column 0 0 10 0 r[10]= cursor 0 column 0
7 Lt 12 11 10 BINARY-8 82 if r[10]<r[12] goto 11
8 Gt 13 11 10 BINARY-8 82 if r[10]>r[13] 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 6 0 1
12 OpenWrite 0 2 0 3 0 root=2 iDb=0; T
13 Rewind 1 35 0 0
14 Rowid 1 2 0 0 r[2]= rowid of 1
15 NotExists 0 34 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 RealAffinity 5 0 0 0
20 Copy 2 6 0 0 r[6]=r[2]
21 String8 0 7 0 0 r[7]=''
22 Column 0 1 8 0 r[8]= cursor 0 column 1
23 Column 0 2 9 0 r[9]= cursor 0 column 2
24 RealAffinity 9 0 0 0
25 MakeRecord 7 3 14 BBE 0 r[14]=mkrec(r[7..9])
26 Insert 0 14 6 T 5 intkey=r[6] data=r[14]
27 Copy 7 15 0 0 r[15]=r[7]
28 Copy 8 16 0 0 r[16]=r[8]
29 Copy 9 17 0 0 r[17]=r[9]
30 RealAffinity 17 0 0 0
31 MakeRecord 15 3 18 0 r[18]=mkrec(r[15..17])
32 NewRowid 3 19 0 0 r[19]=rowid
33 Insert 3 18 19 0 intkey=r[19] data=r[18]
34 Next 1 14 0 0
35 FkCheck 0 0 0 0
36 Rewind 3 42 0 0
37 Column 3 0 15 0 r[15]= cursor 3 column 0
38 Column 3 1 16 0 r[16]= cursor 3 column 1
39 Column 3 2 17 0 r[17]= cursor 3 column 2
40 ResultRow 15 3 0 0 output=r[15..17]
41 Next 3 37 0 0
42 Halt 0 0 0 0
43 Transaction 0 1 1 0 1 usesStmtJournal=0
44 Integer 0 12 0 0 r[12]=0
45 Integer 100 13 0 0 r[13]=100
46 OpenEphemeral 3 3 0 0 nColumn=3
47 Goto 0 1 0 0
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 2 0 0 Start at 2
1 Halt 0 0 0 0
2 Goto 0 1 0 0
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 9 0 0 Start at 9
1 OpenWrite 0 2 0 3 0 root=2 iDb=0; T
2 String8 0 2 0 0 r[2]=''
3 String8 0 3 0 x 0 r[3]='x'
4 String8 0 4 0 x 0 r[4]='x'
5 NewRowid 0 1 0 0 r[1]=rowid
6 MakeRecord 2 3 5 BBE 0 r[5]=mkrec(r[2..4])
7 Insert 0 5 1 T 57 intkey=r[1] data=r[5]
8 Halt 0 0 0 0
9 Transaction 0 1 1 0 1 usesStmtJournal=0
10 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 120 0 0 Start at 120
1 Null 0 1 0 0 r[1]=NULL
2 InitCoroutine 3 29 3 0 (subquery-2)
3 Explain 3 0 0 CO-ROUTINE (subquery-2) 0
4 SorterOpen 6 6 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 2 6 0 r[6]= cursor 0 column 2
9 RealAffinity 6 0 0 0
10 Column 0 2 7 0 r[7]= cursor 0 column 2
11 RealAffinity 7 0 0 0
12 Column 0 2 4 0 r[4]= cursor 0 column 2
13 RealAffinity 4 0 0 0
14 Column 0 2 5 0 r[5]= cursor 0 column 2
15 RealAffinity 5 0 0 0
16 MakeRecord 4 4 9 0 r[9]=mkrec(r[4..7])
17 SorterInsert 6 9 4 4 0 key=r[9]
18 Next 0 8 0 1
19 Explain 19 3 0 USE TEMP B-TREE FOR ORDER BY 0
20 OpenPseudo 7 10 6 0 6 columns in r[10]
21 SorterSort 6 28 0 0
22 SorterData 6 10 7 0 r[10]=data
23 Column 7 1 8 0 r[8]= cursor 7 column 1;
24 Column 7 3 7 0 r[7]= cursor 7 column 3;
25 Column 7 2 6 0 r[6]= cursor 7 column 2;
26 Yield 3 0 0 0
27 SorterNext 6 22 0 0
28 EndCoroutine 3 0 0 0 end (subquery-2)
29 OpenEphemeral 1 3 0 0 nColumn=3
30 OpenDup 2 1 0 0
31 OpenDup 3 1 0 0
32 OpenDup 4 1 0 0
33 Null 0 11 11 0 r[11..11]=NULL
34 Integer 1 12 0 0 r[12]=1
35 Integer 1 13 0 0 r[13]=1
36 Integer 0 14 0 0 r[14]=0
37 OpenDup 8 1 0 0
38 Explain 38 0 216 SCAN (subquery-2) 0
39 InitCoroutine 3 0 3 0
40 Yield 3 72 0 0 next row of
41 Copy 6 16 0 2 r[16]=r[6]
42 Copy 7 17 0 2 r[17]=r[7]
43 Copy 8 18 0 2 r[18]=r[8]
44 MakeRecord 16 3 19 0 r[19]=mkrec(r[16..18])
45 Compare 16 11 1 k(1,B) 0 r[16] <-> r[11]
46 Jump 47 49 47 0
47 Gosub 25 73 0 0 call flush_partition
48 Copy 16 11 0 0 r[11]=r[16]
49 NewRowid 2 20 0 0 r[20]=rowid
50 Insert 2 19 20 0 intkey=r[20] data=r[19]
51 Ne 12 61 20 0 if r[20]!=r[12] goto 61
52 Null 0 1 0 0 r[1]=NULL
53 Rewind 3 0 0 0
54 Rewind 1 0 0 0
55 Rewind 4 0 0 0
56 Copy 17 21 0 0 r[21]=r[17]
57 Copy 21 22 0 0 r[22]=r[21]
58 Copy 21 23 0 0 r[23]=r[21]
59 Copy 21 24 0 0 r[24]=r[21]
60 Goto 0 71 0 0
61 Compare 21 17 1 k(1,B) 0 r[21] <-> r[17]
62 Jump 63 71 63 0
63 Copy 17 21 0 0 r[21]=r[17]
64 AddImm 14 1 0 0 r[14]=r[14]+1
65 Next 4 67 0 0
66 Goto 0 71 0 0
67 Column 4 1 27 0 r[27]= cursor 4 column 1
68 Compare 24 27 1 k(1,B) 0 r[24] <-> r[27]
69 Jump 70 64 70 0
70 Copy 27 24 0 0 r[24]=r[27]
71 Goto 0 40 0 0
72 Integer 113 25 0 0 r[25]=113
73 Rewind 2 110 0 0
74 AddImm 14 1 0 0 r[14]=r[14]+1
75 Next 4 77 0 0
76 Goto 0 81 0 0
77 Column 4 1 27 0 r[27]= cursor 4 column 1
78 Compare 24 27 1 k(1,B) 0 r[24] <-> r[27]
79 Jump 80 74 80 0
80 Copy 27 24 0 0 r[24]=r[27]
81 Rowid 1 27 0 0 r[27]= rowid of 1
82 Column 1 1 29 0 r[29]= cursor 1 column 1
83 Null 0 1 0 0 r[1]=NULL
84 SeekGE 8 91 13 0 key=r[13]
85 Rowid 8 28 0 0 r[28]= rowid of 8
86 Gt 14 91 28 0 if r[28]>r[14] goto 91
87 Column 8 2 26 0 r[26]= cursor 8 column 2
88 CollSeq 0 0 0 BINARY-8 0
89 AggStep 0 26 1 max(1) 1 accum=r[1] step(r[26])
90 Next 8 85 0 0
91 AggFinal 1 1 0 max(1) 0 accum=r[1] N=1
92 Copy 1 2 0 0 r[2]=r[1]
93 Null 0 1 0 0 r[1]=NULL
94 Gosub 15 115 0 0
95 Next 1 97 0 0
96 Goto 0 110 0 0
97 Column 1 1 29 0 r[29]= cursor 1 column 1
98 Compare 23 29 1 k(1,B) 0 r[23] <-> r[29]
99 Jump 100 81 100 0
100 Copy 29 23 0 0 r[23]=r[29]
101 AddImm 13 1 0 0 r[13]=r[13]+1
102 Delete 3 0 0 2
103 Next 3 105 0 0
104 Goto 0 109 0 0
105 Column 3 1 29 0 r[29]= cursor 3 column 1
106 Compare 22 29 1 k(1,B) 0 r[22] <-> r[29]
107 Jump 108 101 108 0
108 Copy 29 22 0 0 r[22]=r[29]
109 Goto 0 81 0 0
110 ResetSorter 1 0 0 0
111 Integer 1 13 0 0 r[13]=1
112 Integer 0 14 0 0 r[14]=0
113 Return 25 0 0 0
114 Goto 0 119 0 0
115 Noop 0 0 0 0 inner-loop subroutine
116 Copy 2 31 0 0 r[31]=r[2]
117 ResultRow 31 1 0 0 output=r[31]
118 Return 15 0 0 0 end inner-loop subroutine
119 Halt 0 0 0 0
120 Transaction 0 0 1 0 1 usesStmtJournal=0
121 Goto 0 1 0 0
-2.25
1.0
4.75
x
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 37 0 0 Start at 37
1 Null 0 1 0 0 r[1]=NULL
2 OpenWrite 0 1 0 5 0 root=1 iDb=0; sqlite_master
3 Rewind 0 11 0 0
4 Column 0 2 2 0 r[2]= cursor 0 column 2
5 Ne 3 10 2 BINARY-8 82 if r[2]!=r[3] goto 10
6 Column 0 0 2 0 r[2]= cursor 0 column 0
7 Eq 4 10 2 BINARY-8 82 if r[2]==r[4] goto 10
8 Rowid 0 5 0 0 r[5]=sqlite_master.rowid
9 Delete 0 0 0 2
10 Next 0 4 0 1
11 Destroy 2 2 0 0
12 Null 0 6 7 0 r[6..7]=NULL
13 OpenEphemeral 2 0 6 0 nColumn=0
14 IfNot 2 22 1 0
15 OpenRead 1 1 0 4 0 root=1 iDb=0; sqlite_master
16 Rewind 1 22 0 0
17 Column 1 3 13 0 r[13]= cursor 1 column 3
18 Ne 2 21 13 BINARY-8 84 if r[13]!=r[2] goto 21
19 Rowid 1 7 0 0 r[7]= rowid of 1
20 Insert 2 6 7 0 intkey=r[7] data=r[6]
21 Next 1 17 0 1
22 OpenWrite 1 1 0 5 0 root=1 iDb=0; sqlite_master
23 Rewind 2 34 0 0
24 Rowid 2 7 0 0 r[7]= rowid of 2
25 NotExists 1 33 7 0 intkey=r[7]
26 Column 1 0 8 0 r[8]= cursor 1 column 0
27 Column 1 1 9 0 r[9]= cursor 1 column 1
28 Column 1 2 10 0 r[10]= cursor 1 column 2
29 Integer 2 11 0 0 r[11]=2
30 Column 1 4 12 0 r[12]= cursor 1 column 4
31 MakeRecord 8 5 15 BBBDB 0 r[15]=mkrec(r[8..12])
32 Insert 1 15 7 0 intkey=r[7] data=r[15]
33 Next 2 24 0 0
34 DropTable 0 0 0 T 0
35 SetCookie 0 1 2 0
36 Halt 0 0 0 0
37 Transaction 0 1 1 0 1 usesStmtJournal=1
38 String8 0 3 0 T 0 r[3]='T'
39 String8 0 4 0 trigger 0 r[4]='trigger'
40 Goto 0 1 0 0
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 31 0 0 Start at 31
1 ReadCookie 0 3 2 0
2 If 3 5 0 0
3 SetCookie 0 2 4 0
4 SetCookie 0 5 1 0
5 CreateBtree 0 2 1 0 r[2]=root iDb=0 flags=1
6 OpenWrite 0 1 0 5 0 root=1 iDb=0
7 NewRowid 0 1 0 0 r[1]=rowid
8 Blob 6 3 0 0 r[3]= (len=6)
9 Insert 0 3 1 8 intkey=r[1] data=r[3]
10 Close 0 0 0 0
11 Close 0 0 0 0
12 Null 0 4 5 0 r[4..5]=NULL
13 Noop 4 0 4 0
14 OpenWrite 3 1 0 5 0 root=1 iDb=0; sqlite_master
15 SeekRowid 3 17 1 0 intkey=r[1]
16 Rowid 3 5 0 0 r[5]= rowid of 3
17 IsNull 5 25 0 0 if r[5]==NULL goto 25
18 String8 0 6 0 table 0 r[6]='table'
19 String8 0 7 0 sqlite_stat1 0 r[7]='sqlite_stat1'
20 String8 0 8 0 sqlite_stat1 0 r[8]='sqlite_stat1'
21 Copy 2 9 0 0 r[9]=r[2]
22 String8 0 10 0 CREATE TABLE sqlite_stat1(tbl,idx,stat) 0 r[10]='CREATE TABLE sqlite_stat1(tbl,idx,stat)'
23 MakeRecord 6 5 4 BBBDB 0 r[4]=mkrec(r[6..10])
24 Insert 3 4 5 0 intkey=r[5] data=r[4]
25 SetCookie 0 1 3 0
26 ParseSchema 0 0 0 tbl_name='sqlite_stat1' AND type!='trigger' 0
27 OpenWrite 0 2 0 3 16 root=2 iDb=0; sqlite_stat1
28 LoadAnalysis 0 0 0 0
29 Expire 0 0 0 0
30 Halt 0 0 0 0
31 Transaction 0 1 2 0 1 usesStmtJournal=1
32 Goto 0 1 0 0
Flag