sqlite-users is probably more appropriate than the development list so this should be carried on there. Please use that list to continue this discussion as more people are likely to see it, participate, and benefit from the discussions.
Have you actually built some tables in SQLite and had it dump the VDBE code that it will execute to perform the query? This will probably help enormously in understanding how SQLite works. As an aside, SQLite does not join three tables by joining tables one and two into a temporary table, then joining in table three. It plots the most efficient nested loop (navigational) solution and executes the resulting row generator which yields each result row as it is found. See: http://www.sqlite.org/optoverview.html sqlite3_step() equates to: def stepper(): open table1 open table2 open table3 rewind table1 for each row in table1 matching the query constraints on table1 rewind table2 for each row in table2 matching the constraints in the query and provided by table1 rewind table3 for each row in table3 matching the constraints in the query and provided by table1 and table2 yield a result row close table3 close table2 close table1 where stepper is a generator. the first call executes the code up until the yield whereupon the result is returned to the caller. Subsequent calls carry on from the yield thus returning each result row one at a time. when all the loops run out and there is nothing more to yield the generator terminates and returns an no more rows indication to the caller. sqlite3_prepare prepares the stepper program by compiling the sql query that you have asked to be executed. SQLite version 3.8.7 2014-09-12 04:28:33 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table employee(name, stuff); sqlite> create table location(name, stuff); sqlite> create table company(name, stuff); sqlite> .explain sqlite> explain select * from employee, company, location where employee.name = company.name and company.name = location.name; SELECT item[0] = {0:0} AS name item[1] = {0:1} AS stuff item[2] = {1:0} AS name item[3] = {1:1} AS stuff item[4] = {2:0} AS name item[5] = {2:1} AS stuff FROM {0,*} = employee {1,*} = company {2,*} = location WHERE AND(EQ({0:0},{1:0}),EQ({1:0},{2:0})) END addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 51 0 00 Start at 51 1 OpenRead 0 2 0 2 00 root=2 iDb=0; employee 2 OpenRead 1 4 0 2 00 root=4 iDb=0; company 3 OpenRead 2 3 0 2 00 root=3 iDb=0; location 4 Noop 0 0 0 00 Begin WHERE-loop0: employee 5 Rewind 0 48 0 00 6 Once 0 15 0 00 7 OpenAutoindex 3 3 0 k(3,nil,nil,nil) 00 nColumn=3; for company 8 Rewind 1 15 0 00 9 Column 1 0 2 00 r[2]=company.name 10 Column 1 1 3 00 r[3]=company.stuff 11 Rowid 1 4 0 00 r[4]=rowid 12 MakeRecord 2 3 1 00 r[1]=mkrec(r[2..4]) 13 IdxInsert 3 1 0 10 key=r[1] 14 Next 1 9 0 03 15 Noop 0 0 0 00 Begin WHERE-loop1: company 16 Column 0 0 5 00 r[5]=employee.name 17 IsNull 5 46 0 00 if r[5]==NULL goto 46 18 SeekGE 3 46 5 1 00 key=r[5] 19 IdxGT 3 46 5 1 00 key=r[5] 20 Once 1 29 0 00 21 OpenAutoindex 4 3 0 k(3,nil,nil,nil) 00 nColumn=3; for location 22 Rewind 2 29 0 00 23 Column 2 0 2 00 r[2]=location.name 24 Column 2 1 3 00 r[3]=location.stuff 25 Rowid 2 4 0 00 r[4]=rowid 26 MakeRecord 2 3 1 00 r[1]=mkrec(r[2..4]) 27 IdxInsert 4 1 0 10 key=r[1] 28 Next 2 23 0 03 29 Noop 0 0 0 00 Begin WHERE-loop2: location 30 Column 3 0 6 00 r[6]=company.name 31 IsNull 6 44 0 00 if r[6]==NULL goto 44 32 SeekGE 4 44 6 1 00 key=r[6] 33 IdxGT 4 44 6 1 00 key=r[6] 34 Noop 0 0 0 00 Begin WHERE-core 35 Copy 5 7 0 00 r[7]=r[5] 36 Column 0 1 8 00 r[8]=employee.stuff 37 Copy 6 9 0 00 r[9]=r[6] 38 Column 3 1 10 00 r[10]=company.stuff 39 Column 4 0 11 00 r[11]=location.name 40 Column 4 1 12 00 r[12]=location.stuff 41 ResultRow 7 6 0 00 output=r[7..12] 42 Noop 0 0 0 00 End WHERE-core 43 Next 4 33 0 00 44 Noop 0 0 0 00 End WHERE-loop2: location 45 Next 3 19 0 00 46 Noop 0 0 0 00 End WHERE-loop1: company 47 Next 0 6 0 01 48 Noop 0 0 0 00 End WHERE-loop0: employee 49 Close 0 0 0 00 50 Halt 0 0 0 00 51 Transaction 0 0 3 0 01 52 TableLock 0 2 0 employee 00 iDb=0 root=2 write=0 53 TableLock 0 4 0 company 00 iDb=0 root=4 write=0 54 TableLock 0 3 0 location 00 iDb=0 root=3 write=0 55 Goto 0 1 0 00 sqlite> In this case "MakeRecord" makes a record for insertion in the indexes that the optimizer has decided should be created temporarily to service this query because the designer neglected to do so. Automatic Indexing is an optimization that has been chosen by the optimizer so that full table scans in each nested loop can be avoided. You can turn off automatic indexing optimization with a pragma which will tell the query optimizer not to generate indexes, thus full table scans will have to be used. This results in the following code, which is pretty much the same except without index creation and using full table scans in the nested loops -- it does not use MakeRecord because there is no need to construct records (for indexes in this example): sqlite> pragma automatic_index=0; sqlite> explain select * from employee, company, location where employee.name = company.name and company.name = location.name; SELECT item[0] = {0:0} AS name item[1] = {0:1} AS stuff item[2] = {1:0} AS name item[3] = {1:1} AS stuff item[4] = {2:0} AS name item[5] = {2:1} AS stuff FROM {0,*} = employee {1,*} = company {2,*} = location WHERE AND(EQ({0:0},{1:0}),EQ({1:0},{2:0})) END addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 36 0 00 Start at 36 1 OpenRead 0 2 0 2 00 root=2 iDb=0; employee 2 OpenRead 1 4 0 2 00 root=4 iDb=0; company 3 OpenRead 2 3 0 2 00 root=3 iDb=0; location 4 Noop 0 0 0 00 Begin WHERE-loop0: employee 5 Rewind 0 31 0 00 6 Noop 0 0 0 00 Begin WHERE-loop1: company 7 Rewind 1 29 0 00 8 Column 0 0 1 00 r[1]=employee.name 9 Column 1 0 2 00 r[2]=company.name 10 Ne 2 28 1 (BINARY) 6a if r[2]!=r[1] goto 28 11 Noop 0 0 0 00 begin transitive constraint 12 Ne 1 28 2 (BINARY) 6a if r[1]!=r[2] goto 28 13 Noop 0 0 0 00 Begin WHERE-loop2: location 14 Rewind 2 27 0 00 15 Column 2 0 3 00 r[3]=location.name 16 Ne 3 26 2 (BINARY) 6a if r[3]!=r[2] goto 26 17 Noop 0 0 0 00 Begin WHERE-core 18 Copy 1 4 0 00 r[4]=r[1] 19 Column 0 1 5 00 r[5]=employee.stuff 20 Copy 2 6 0 00 r[6]=r[2] 21 Column 1 1 7 00 r[7]=company.stuff 22 Copy 3 8 0 00 r[8]=r[3] 23 Column 2 1 9 00 r[9]=location.stuff 24 ResultRow 4 6 0 00 output=r[4..9] 25 Noop 0 0 0 00 End WHERE-core 26 Next 2 15 0 01 27 Noop 0 0 0 00 End WHERE-loop2: location 28 Next 1 8 0 01 29 Noop 0 0 0 00 End WHERE-loop1: company 30 Next 0 6 0 01 31 Noop 0 0 0 00 End WHERE-loop0: employee 32 Close 0 0 0 00 33 Close 1 0 0 00 34 Close 2 0 0 00 35 Halt 0 0 0 00 36 Transaction 0 0 3 0 01 37 TableLock 0 2 0 employee 00 iDb=0 root=2 write=0 38 TableLock 0 4 0 company 00 iDb=0 root=4 write=0 39 TableLock 0 3 0 location 00 iDb=0 root=3 write=0 40 Goto 0 1 0 00 sqlite> If it helps, all operations in SQLite are navigational. VDBE programs can navigate around the tables and move specific items from a current record into a register, do things with registers, and create a record (MakeRecord) from register(s) for insertion in an index or other table. There can only be one "current" record from any table-cursor or index at any point in time. You can think of MakeRecord as creating a list of pointers to the data items that will be used when then record is passed to some storage method (ie, written to a table, index, temp table, temp index, etc). Within the VDBE program there are no sets of records -- only persisted tables/indexes, so to create temporary index or for group by, etc, the result data is collected via MakeRecord then inserted into another table/index. This is then subsequently navigated to generate the ResultRow's. ie -- exactly how you would do it if all you had were ISAM files to work with and your programming language did not have the ability to create collections and data structures in memory but could only access the fields within the records and registers to contain the data temporarily. >-----Original Message----- >From: sqlite-dev-boun...@sqlite.org [mailto:sqlite-dev- >boun...@sqlite.org] On Behalf Of Prakash Premkumar >Sent: Friday, 12 September, 2014 08:21 >To: sqlite-...@sqlite.org >Subject: [sqlite-dev] SQLite Vdbe Opcode MakeRecord > >Hi, >Can you please explain what the opcode Make Record does ? I was not able >to understand it from the docs. > >Let's assume that there are three tables that are to be joined > > > example : select * from employee,company,location where >location.name = company.name and location.name = employee.name; > > >how do I get the result of the first join between location and company ? >Where is that result stored and how is the result joined with the result >of the next join ? > >Can you please explain me with reference to the source code ? > >Thanks >Prakash _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
