//create table
sql="create table em(name text primary key,age text,pts text);"\
"create table l(name text primary key,fame text);";
//insert values
sql="insert into em values(44,20,1);"\
"insert into em values(11,20,2);"\
"insert into em values(5,21,3);"\
"insert into l values(11,11);"\
"insert into l values(12,20);"\
"insert into l values(1,20);";
//query
sql = "select * from em,l where l.fame=em.age";
Below is a sample VDBE program:
0 Init 0 27 0 00
1 OpenRead 0 2 0 3 00
2 OpenRead 1 15 0 2 00
3 Rewind 0 25 0 00
4 Once 0 13 0 00
5 OpenAutoindex 2 3 0 k(3,nil,nil,nil) 00
6 Rewind 1 13 0 00
7 Column 1 1 2 00
8 Column 1 0 3 00
9 Rowid 1 4 0 00
10 MakeRecord 2 3 1 00
11 IdxInsert 2 1 0 10
12 Next 1 7 0 03
13 Column 0 1 5 00
14 IsNull 5 24 0 00
15 SeekGE 2 24 5 1 00
16 IdxGT 2 24 5 1 00
17 Column 0 0 6 00
18 Copy 5 7 0 00
19 Column 0 2 8 00
20 Column 2 1 9 00
21 Column 2 0 10 00
22 ResultRow 6 5 0 00
23 Next 2 16 0 00
24 Next 0 4 0 01
25 Close 0 0 0 00
26 Halt 0 0 0 00
27 Transaction 0 0 48833 0 01
28 TableLock 0 2 0 em 00
29 TableLock 0 15 0 l 00
30 Goto 0 1 0 00
Question:
whenever the condition in the where clause is false, the program jumps to
the instruction pointed by p2 of SeekGe but if the condition proves to be
false for the row 1 of both the tables, then the program jumps to line
24(in this case) which corresponds to outer table and takes the second row
of outer table for next iteration, then when will the program fetch 1st row
of table-1 and remaining rows of table-2 ???
