BETWEEN operator generates different bytecode than SQLite

[glommer]

the COMPAT matrix claims BETWEEN doesn't work, but the query seems to.

this is because before getting to translate_condition_expr(), we seem to have parsed this to the equivalent comparisons. Printing Expr right at the beginning of the function for the query

SELECT id, value FROM demo WHERE id BETWEEN 2 AND 4;

yields

EXPR Binary(Binary(Literal(Numeric("2")), LessEquals, Column { database: None, table: 0, column: 0, is_rowid_alias: false }), And, Binary(Column { database: None, table: 0, column: 0, is_rowid_alias: false }, LessEquals, Literal(Numeric("4"))))

As a result, while SQLite ends up generating this bytecode:

sqlite> explain SELECT id, value FROM demo WHERE id BETWEEN 2 AND 4;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     11    0                    0   Start at 11
1     OpenRead       0     2     0     2              0   root=2 iDb=0; demo
2     Rewind         0     10    0                    0
3       Column         0     0     1                    0   r[1]= cursor 0 column 0
4       Lt             3     9     1     BINARY-8       84  if r[1]<r[3] goto 9
5       Gt             4     9     1     BINARY-8       84  if r[1]>r[4] goto 9
6       Column         0     0     5                    0   r[5]= cursor 0 column 0
7       Column         0     1     6                    0   r[6]= cursor 0 column 1
8       ResultRow      5     2     0                    0   output=r[5..6]
9     Next           0     3     0                    1
10    Halt           0     0     0                    0
11    Transaction    0     0     1     0              1   usesStmtJournal=0
12    Integer        2     3     0                    0   r[3]=2
13    Integer        4     4     0                    0   r[4]=4
14    Goto           0     1     0                    0

we generate:

limbo> explain SELECT id, value FROM demo WHERE id BETWEEN 2 AND 4;
addr  opcode             p1    p2    p3    p4             p5  comment
----  -----------------  ----  ----  ----  -------------  --  -------
0     Init               0     15    0                    0   Start at 15
1     OpenReadAsync      0     2     0                    0   table=demo, root=2
2     OpenReadAwait      0     0     0                    0
3     RewindAsync        0     0     0                    0
4     RewindAwait        0     14    0                    0   Rewind table demo
5       Column           0     0     4                    0   r[4]=demo.id
6       Gt               3     4     12                   0   if r[3]>r[4] goto 12
7       Column           0     0     5                    0   r[5]=demo.id
8       Gt               5     6     12                   0   if r[5]>r[6] goto 12
9       Column           0     0     1                    0   r[1]=demo.id
10      Column           0     1     2                    0   r[2]=demo.value
11      ResultRow        1     2     0                    0   output=r[1..2]
12    NextAsync          0     0     0                    0
13    NextAwait          0     5     0                    0
14    Halt               0     0     0                    0
15    Transaction        0     0     0                    0
16    Integer            2     3     0                    0   r[3]=2
17    Integer            4     6     0                    0   r[6]=4
18    Goto               0     1     0                    0

[diegoreis42]

I tracked down where this happened and it seems #490 has a good discussion about this. Maybe it should be revisited when we support indexes?

[jussisaurio]

Yes we made a decision in #490 to (temporarily) accept an extra bytecode instruction for the benefit of being able to rewrite BETWEEN entirely as inequality comparisons. As Diego said there's a discussion in that PR about the way forward. We do have limited support for indexes already, but we don't implement what SQLite does, which is:

Thus if the BETWEEN term is not used as an index constraint and instead must be used to test input rows, the expr1 expression is only evaluated once.