Closed pereman2 closed 1 month ago
In sqlite3 generating the loop to read multiple joined tables follows the pattern:
sqlite3WhereBegin(); sqlite3WhereEnd();
and this generates:
sqlite> explain select * from users, products; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 23 0 0 Start at 23 1 OpenRead 0 2 0 10 0 root=2 iDb=0; users 2 OpenRead 1 3 0 3 0 root=3 iDb=0; products 3 Rewind 0 22 0 0 4 Rewind 1 22 0 0 5 Rowid 0 1 0 0 r[1]=users.rowid 6 Column 0 1 2 0 r[2]= cursor 0 column 1 7 Column 0 2 3 0 r[3]= cursor 0 column 2 8 Column 0 3 4 0 r[4]= cursor 0 column 3 9 Column 0 4 5 0 r[5]= cursor 0 column 4 10 Column 0 5 6 0 r[6]= cursor 0 column 5 11 Column 0 6 7 0 r[7]= cursor 0 column 6 12 Column 0 7 8 0 r[8]= cursor 0 column 7 13 Column 0 8 9 0 r[9]= cursor 0 column 8 14 Column 0 9 10 0 r[10]= cursor 0 column 9 15 Rowid 1 11 0 0 r[11]=products.rowid 16 Column 1 1 12 0 r[12]= cursor 1 column 1 17 Column 1 2 13 0 r[13]= cursor 1 column 2 18 RealAffinity 13 0 0 0 19 ResultRow 1 13 0 0 output=r[1..13] 20 Next 1 5 0 1 21 Next 0 4 0 1 22 Halt 0 0 0 0 23 Transaction 0 0 2 0 1 usesStmtJournal=0 24 Goto 0 1 0 0
sqlite3WhereBegin() as the name represents, mainly does stuff with WHERE expressions + loop generation. This is why I decided to change the name to translate_tables_begin to try improve the naming.
sqlite3WhereBegin()
WHERE
translate_tables_begin
In our case:
translate_table_open_cursor(program, &mut context, select.from.as_ref().unwrap()); translate_table_open_loop(program, &mut context, loop_index);
translates into:
> explain select * from users, products; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------- 0 Init 0 28 0 0 Start at 28 1 OpenReadAsync 0 2 0 0 root=2 2 OpenReadAwait 0 0 0 0 3 OpenReadAsync 1 3 0 0 root=3 4 OpenReadAwait 0 0 0 0 5 RewindAsync 0 0 0 0 6 RewindAwait 0 27 0 0 7 RewindAsync 1 0 0 0 8 RewindAwait 1 25 0 0 9 RowId 0 0 0 0 10 Column 0 1 1 0 r[1]= cursor 0 column 1 11 Column 0 2 2 0 r[2]= cursor 0 column 2 12 Column 0 3 3 0 r[3]= cursor 0 column 3 13 Column 0 4 4 0 r[4]= cursor 0 column 4 14 Column 0 5 5 0 r[5]= cursor 0 column 5 15 Column 0 6 6 0 r[6]= cursor 0 column 6 16 Column 0 7 7 0 r[7]= cursor 0 column 7 17 Column 0 8 8 0 r[8]= cursor 0 column 8 18 Column 0 9 9 0 r[9]= cursor 0 column 9 19 RowId 1 10 0 0 20 Column 1 1 11 0 r[11]= cursor 1 column 1 21 Column 1 2 12 0 r[12]= cursor 1 column 2 22 ResultRow 0 13 0 0 output=r[0..13] 23 NextAsync 1 0 0 0 24 NextAwait 1 8 0 0 25 NextAsync 0 0 0 0 26 NextAwait 0 6 0 0 27 Halt 0 0 0 0 28 Transaction 0 0 0 0 29 Goto 0 1 0 0
This works on as many joined tables but... it is ready to extend for further join operations.
In sqlite3 generating the loop to read multiple joined tables follows the pattern:
and this generates:
sqlite3WhereBegin()as the name represents, mainly does stuff withWHEREexpressions + loop generation. This is why I decided to change the name totranslate_tables_beginto try improve the naming.In our case:
translates into:
This works on as many joined tables but... it is ready to extend for further join operations.