Optimize literal in loop

[benclmnt]

Move where clause literal out of loop. Related #122

Previous:

> explain select * from products where name = 'hat';
addr  opcode             p1    p2    p3    p4             p5  comment
----  -----------------  ----  ----  ----  -------------  --  -------
0     Init               0     16    0                    0   Start at 16
1     OpenReadAsync      0     3     0                    0   root=3
2     OpenReadAwait      0     0     0                    0   
3     RewindAsync        0     0     0                    0   
4     RewindAwait        0     15    0                    0   
5       Column           0     1     1                    0   r[1]=products.name
6       String8          2     0     0     hat            0   r[2]= 'hat'
7       Ne               1     2     13                   0   r[1] != r[2] -> 13
8       RowId            0     3     0                    0   r[3]=products.rowid
9       Column           0     1     4                    0   r[4]=products.name
10      Column           0     2     5                    0   r[5]=products.price
11      RealAffinity     5     0     0                    0   
12      ResultRow        3     3     0                    0   output=r[3..5]
13    NextAsync          0     0     0                    0   
14    NextAwait          0     4     0                    0   
15    Halt               0     0     0                    0   
16    Transaction        0     0     0                    0   
17    Goto               0     1     0                    0   

Now: Move line 6 to line 16

> explain select * from products where name = 'hat';
addr  opcode             p1    p2    p3    p4             p5  comment
----  -----------------  ----  ----  ----  -------------  --  -------
0     Init               0     15    0                    0   Start at 15
1     OpenReadAsync      0     3     0                    0   root=3
2     OpenReadAwait      0     0     0                    0   
3     RewindAsync        0     0     0                    0   
4     RewindAwait        0     14    0                    0   
5       Column           0     1     1                    0   r[1]=products.name
6       Ne               1     2     12                   0   if r[1]!=r[2] goto 12
7       RowId            0     3     0                    0   r[3]=products.rowid
8       Column           0     1     4                    0   r[4]=products.name
9       Column           0     2     5                    0   r[5]=products.price
10      RealAffinity     5     0     0                    0   
11      ResultRow        3     3     0                    0   output=r[3..5]
12    NextAsync          0     0     0                    0   
13    NextAwait          0     4     0                    0   
14    Halt               0     0     0                    0   
15    Transaction        0     0     0                    0   
16    String8            0     2     0     hat            0   r[2]='hat'
17    Goto               0     1     0                    0   

sqlite3:

addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     13    0                    0   Start at 13
1     OpenRead       0     3     0     3              0   root=3 iDb=0; products
2     Explain        2     0     0     SCAN products  0   
3     Rewind         0     12    0                    0   
4       Column         0     1     1                    0   r[1]=products.name
5       Ne             2     11    1     BINARY-8       82  if r[1]!=r[2] goto 11
6       Rowid          0     3     0                    0   r[3]=products.rowid
7       Column         0     1     4                    0   r[4]=products.name
8       Column         0     2     5                    0   r[5]=products.price
9       RealAffinity   5     0     0                    0   
10      ResultRow      3     3     0                    0   output=r[3..5]
11    Next           0     4     0                    1   
12    Halt           0     0     0                    0   
13    Transaction    0     0     2     0              1   usesStmtJournal=0
14    String8        0     2     0     hat            0   r[2]='hat'
15    Goto           0     1     0                    0   

[pereman2]

sqlite> explain select * from products where id = 1;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     10    0                    0   Start at 10
1     OpenRead       0     3     0     3              0   root=3 iDb=0; products
2     Integer        1     1     0                    0   r[1]=1
3     SeekRowid      0     9     1                    0   intkey=r[1]
4     Rowid          0     2     0                    0   r[2]=products.rowid
5     Column         0     1     3                    0   r[3]= cursor 0 column 1
6     Column         0     2     4                    0   r[4]= cursor 0 column 2
7     RealAffinity   4     0     0                    0
8     ResultRow      2     3     0                    0   output=r[2..4]
9     Halt           0     0     0                    0
10    Transaction    0     0     2     0              1   usesStmtJournal=0
11    Goto           0     1     0                    0

I was checking sqlite3 and it doesn't generate a String8 opcode. What am I missing?

forget this comment, query was not correct.

[benclmnt]

I was checking sqlite3 and it doesn't generate a String8 opcode. What am I missing?

That sql query makes use of a covering index. Oops i might have forgot to change the query 😅 - edited now