Inner join, table aliases, qualified column names

[jussisaurio]

• Inner join with unqualified names (select foo_col, bar_col from foo join bar on foo_col = bar_col)
• Inner join with qualified names (select foo.col, bar.col from foo join bar on foo.col = bar.col)
• Inner join with table aliases (select f.col, b.col from foo f join bar b on f.col = b.col)
• Inner self join (select f1.col, f2.col from foo f1 join foo f2 on f1.col = f2.col + 1)
• No support in joins for: logical ops AND etc, multiple joins JOIN ... JOIN, LEFT JOIN etc

---

• Cursor references are now a tuple of (table_identifier: String, table: Table); allows duplicates -- raises an error if resolve_ident_table is ambiguous
• Combine handling of where and join
• Support Insn::Add
• Remove unnecessary GOTO instruction from translate_expr binary expression cases (e.g. select col1 > col2 from table)

---

Code generation e.g.

Limbo

> explain select first_name, name from users join products on first_name != name limit 1;
addr  opcode             p1    p2    p3    p4             p5  comment
----  -----------------  ----  ----  ----  -------------  --  -------
0     Init               0     22    0                    0   Start at 22
1     Integer            1     1     0                    0   r[1]=1
2     OpenReadAsync      0     2     0                    0   root=2
3     OpenReadAwait      0     0     0                    0   
4     OpenReadAsync      1     3     0                    0   root=3
5     OpenReadAwait      0     0     0                    0   
6     RewindAsync        0     0     0                    0   
7     RewindAwait        0     21    0                    0   
8       RewindAsync      1     0     0                    0   
9       RewindAwait      1     19    0                    0   
10        Column         0     1     2                    0   r[2]=users.first_name
11        Column         1     1     3                    0   r[3]=products.name
12        Eq             2     3     17                   0   r[2] == r[3] -> 17
13        Column         0     1     4                    0   r[4]=users.first_name
14        Column         1     1     5                    0   r[5]=products.name
15        ResultRow      4     2     0                    0   output=r[4..5]
16        DecrJumpZero   1     21    0                    0   if (--r[1]==0) goto 21
17      NextAsync        1     0     0                    0   
18      NextAwait        1     9     0                    0   
19    NextAsync          0     0     0                    0   
20    NextAwait          0     7     0                    0   
21    Halt               0     0     0                    0   
22    Transaction        0     0     0                    0   
23    Goto               0     1     0                    0

Sqlite

sqlite> explain select first_name, name from users join products on first_name != name limit 1;
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     16    0                    0   Start at 16
1     Integer        1     1     0                    0   r[1]=1; LIMIT counter
2     OpenRead       0     2     0     2              0   root=2 iDb=0; users
3     OpenRead       1     3     0     2              0   root=3 iDb=0; products
4     Rewind         0     15    0                    0   
5       Rewind         1     15    0                    0   
6         Column         0     1     2                    0   r[2]= cursor 0 column 1
7         Column         1     1     3                    0   r[3]= cursor 1 column 1
8         Eq             3     13    2     BINARY-8       81  if r[2]==r[3] goto 13
9         Column         0     1     4                    0   r[4]= cursor 0 column 1
10        Column         1     1     5                    0   r[5]= cursor 1 column 1
11        ResultRow      4     2     0                    0   output=r[4..5]
12        DecrJumpZero   1     15    0                    0   if (--r[1])==0 goto 15
13      Next           1     6     0                    1   
14    Next           0     5     0                    1   
15    Halt           0     0     0                    0   
16    Transaction    0     0     2     0              1   usesStmtJournal=0
17    Goto           0     1     0                    0 

[penberg]

Looks reasonable to me @jussisaurio, but let's give @pereman2 the opportunity to review this too since it's a major improvement just to make sure it's aligned on the overall direction.

[jussisaurio]

Looks reasonable to me @jussisaurio, but let's give @pereman2 the opportunity to review this too since it's a major improvement just to make sure it's aligned on the overall direction.

👍 Sure, it's good to ensure that this can be extended/refactored fairly easily and doesn't paint us into a corner or anything