Limbo does not create an index on the primary key if it's not an alias for the rowid

[jussisaurio]

https://www.sqlite.org/lang_createtable.html#rowids_and_the_integer_primary_key

rowid alias:

sqlite> explain create table foo (id integer primary key, value);
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     29    0                    0   Start at 29
1     ReadCookie     0     3     2                    0   
2     If             3     5     0                    0   
3     SetCookie      0     2     4                    0   
4     SetCookie      0     5     1                    0   
5     CreateBtree    0     2     1                    0   r[2]=root iDb=0 flags=1
6     OpenWrite      0     1     0     5              0   root=1 iDb=0
7     NewRowid       0     1     0                    0   r[1]=rowid
8     Blob           6     3     0                   0   r[3]= (len=6)
9     Insert         0     3     1                    8   intkey=r[1] data=r[3]
10    Close          0     0     0                    0   
11    Close          0     0     0                    0   
12    Null           0     4     5                    0   r[4..5]=NULL
13    Noop           2     0     4                    0   
14    OpenWrite      1     1     0     5              0   root=1 iDb=0; sqlite_master
15    SeekRowid      1     17    1                    0   intkey=r[1]
16    Rowid          1     5     0                    0   r[5]= rowid of 1
17    IsNull         5     26    0                    0   if r[5]==NULL goto 26
18    String8        0     6     0     table          0   r[6]='table'
19    String8        0     7     0     foo            0   r[7]='foo'
20    String8        0     8     0     foo            0   r[8]='foo'
21    Copy           2     9     0                    0   r[9]=r[2]
22    String8        0     10    0     CREATE TABLE foo (id integer primary key, value) 0   r[10]='CREATE TABLE foo (id integer primary key, value)'
23    MakeRecord     6     5     4     BBBDB          0   r[4]=mkrec(r[6..10])
24    Delete         1     68    5                    0   
25    Insert         1     4     5                    0   intkey=r[5] data=r[4]
26    SetCookie      0     1     1                    0   
27    ParseSchema    0     0     0     tbl_name='foo' AND type!='trigger' 0   
28    Halt           0     0     0                    0   
29    Transaction    0     1     0     0              1   usesStmtJournal=1
30    Goto           0     1     0                    0 

not a rowid alias:

sqlite> explain create table foo (id int primary key, value);
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     40    0                    0   Start at 40
1     ReadCookie     0     3     2                    0   
2     If             3     5     0                    0   
3     SetCookie      0     2     4                    0   
4     SetCookie      0     5     1                    0   
5     CreateBtree    0     2     1                    0   r[2]=root iDb=0 flags=1
6     OpenWrite      0     1     0     5              0   root=1 iDb=0
7     NewRowid       0     1     0                    0   r[1]=rowid
8     Blob           6     3     0                   0   r[3]= (len=6)
9     Insert         0     3     1                    8   intkey=r[1] data=r[3]
10    Close          0     0     0                    0   
11    Noop           0     22    0                    0   
12    CreateBtree    0     4     2                    0   r[4]=root iDb=0 flags=2
13    OpenWrite      1     1     0     5              0   root=1 iDb=0; sqlite_master
14    String8        0     6     0     index          0   r[6]='index'
15    String8        0     7     0     sqlite_autoindex_foo_1 0   r[7]='sqlite_autoindex_foo_1'
16    String8        0     8     0     foo            0   r[8]='foo'
17    SCopy          4     9     0                    0   r[9]=r[4]
18    Null           0     10    0                    0   r[10]=NULL
19    NewRowid       1     5     0                    0   r[5]=rowid
20    MakeRecord     6     5     11    BBBDB          0   r[11]=mkrec(r[6..10])
21    Insert         1     11    5                    24  intkey=r[5] data=r[11]
22    Close          0     0     0                    0   
23    Null           0     12    13                   0   r[12..13]=NULL
24    Noop           3     0     12                   0   
25    OpenWrite      2     1     0     5              0   root=1 iDb=0; sqlite_master
26    SeekRowid      2     28    1                    0   intkey=r[1]
27    Rowid          2     13    0                    0   r[13]= rowid of 2
28    IsNull         13    37    0                    0   if r[13]==NULL goto 37
29    String8        0     14    0     table          0   r[14]='table'
30    String8        0     15    0     foo            0   r[15]='foo'
31    String8        0     16    0     foo            0   r[16]='foo'
32    Copy           2     17    0                    0   r[17]=r[2]
33    String8        0     18    0     CREATE TABLE foo (id int primary key, value) 0   r[18]='CREATE TABLE foo (id int primary key, value)'
34    MakeRecord     14    5     12    BBBDB          0   r[12]=mkrec(r[14..18])
35    Delete         2     68    13                   0   
36    Insert         2     12    13                   0   intkey=r[13] data=r[12]
37    SetCookie      0     1     1                    0   
38    ParseSchema    0     0     0     tbl_name='foo' AND type!='trigger' 0   
39    Halt           0     0     0                    0   
40    Transaction    0     1     0     0              1   usesStmtJournal=1

[jussisaurio]

TBH we don't have write path index support in the first place yet though...