Automatic primary key

[siv-kon]

limbo> CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);
limbo>
limbo>
limbo> INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
Parse error: MustBeInt: the value in the register is not an integer
limbo>

The same in Sqlite3 works.

[penberg]

Thanks for reporting this @siv-kon!

SQLite generates the following bytecode:

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     9     0                    0   Start at 9
1     OpenWrite      0     2     0     3              0   root=2 iDb=0; users
2     SoftNull       2     0     0                    0   r[2]=NULL
3     String8        0     3     0     John Doe       0   r[3]='John Doe'
4     String8        0     4     0     john@example.com 0   r[4]='john@example.com'
5     NewRowid       0     1     0                    0   r[1]=rowid
6     MakeRecord     2     3     5     DBB            0   r[5]=mkrec(r[2..4])
7     Insert         0     5     1     users          57  intkey=r[1] data=r[5]
8     Halt           0     0     0                    0
9     Transaction    0     1     1     0              1   usesStmtJournal=0
10    Goto           0     1     0                    0

Limbo goes on a wild adventure with coroutines:

limbo> EXPLAIN INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
addr  opcode             p1    p2    p3    p4             p5  comment
----  -----------------  ----  ----  ----  -------------  --  -------
0     Init               0     21    0                    0   Start at 21
1     InitCoroutine      5     6     2                    0
2     String8            0     2     0     John Doe       0   r[2]='John Doe'
3     String8            0     3     0     john@example.com  0   r[3]='john@example.com'
4     Yield              5     0     0                    0
5     EndCoroutine       5     0     0                    0
6     OpenWriteAsync     0     2     0                    0
7     OpenWriteAwait     0     0     0                    0
8     Yield              5     20    0                    0
9     Copy               2     1     0                    0   r[1]=r[2]
10    SoftNull           2     0     0                    0
11    NotNull            1     13    0                    0   r[1]!=NULL -> goto 13
12    NewRowId           0     1     0                    0
13    MustBeInt          1     0     0                    0
14    NotExists          0     16    1                    0
15    Halt               1555  0     0                    0
16    MakeRecord         2     3     6                    0   r[6]=mkrec(r[2..4])
17    InsertAsync        0     6     1                    0
18    InsertAwait        0     0     0                    0
19    Goto               0     8     0                    0
20    Halt               0     0     0                    0
21    Transaction        0     1     0                    0
22    Goto               0     1     0                    0

@jussisaurio any ideas what's going wrong here?

[jussisaurio]

@penberg sqlite does this type of thing when there's multiple tuples being inserted

SQLite version 3.46.0 2024-05-23 13:25:27
Enter ".help" for usage hints.
sqlite> CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);
sqlite> explain insert into users (name, email) values ('a','a'), ('b','b');
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     19    0                    0   Start at 19
1     InitCoroutine  1     9     2                    0   
2     String8        0     4     0     a              0   r[4]='a'
3     String8        0     5     0     a              0   r[5]='a'
4     Yield          1     0     0                    0   
5     String8        0     4     0     b              0   r[4]='b'
6     String8        0     5     0     b              0   r[5]='b'
7     Yield          1     0     0                    0   
8     EndCoroutine   1     0     0                    0   
9     OpenWrite      0     2     0     3              0   root=2 iDb=0; users
10      Yield          1     18    0                    0   
11      SoftNull       9     0     0                    0   r[9]=NULL
12      SCopy          4     10    0                    0   r[10]=r[4]
13      SCopy          5     11    0                    0   r[11]=r[5]
14      NewRowid       0     8     0                    0   r[8]=rowid
15      MakeRecord     9     3     12    DBB            0   r[12]=mkrec(r[9..11])
16      Insert         0     12    8     users          57  intkey=r[8] data=r[12]
17    Goto           0     10    0                    0   
18    Halt           0     0     0                    0   
19    Transaction    0     1     1     0              1   usesStmtJournal=0
20    Goto           0     1     0                    0 

and it does an even closer-looking thing when the table is declared without INTEGER PRIMARY KEY:

sqlite> CREATE TABLE users2 (
    id INTEGER,
    name TEXT,
    email TEXT
);
sqlite> explain insert into users (id, name, email) values (1, 'a','a'), (1, 'b','b');
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     25    0                    0   Start at 25
1     InitCoroutine  1     11    2                    0   
2     Integer        1     4     0                    0   r[4]=1
3     String8        0     5     0     a              0   r[5]='a'
4     String8        0     6     0     a              0   r[6]='a'
5     Yield          1     0     0                    0   
6     Integer        1     4     0                    0   r[4]=1
7     String8        0     5     0     b              0   r[5]='b'
8     String8        0     6     0     b              0   r[6]='b'
9     Yield          1     0     0                    0   
10    EndCoroutine   1     0     0                    0   
11    OpenWrite      0     2     0     3              0   root=2 iDb=0; users
12      Yield          1     24    0                    0   
13      Copy           4     3     0                    0   r[3]=r[4]
14      SoftNull       4     0     0                    0   r[4]=NULL
15      NotNull        3     17    0                    0   if r[3]!=NULL goto 17
16      NewRowid       0     3     0                    0   r[3]=rowid
17      MustBeInt      3     0     0                    0   
18      Noop           0     0     0                    0   uniqueness check for ROWID
19      NotExists      0     21    3                    0   intkey=r[3]
20      Halt           1555  2     0     users.id       2   
21      MakeRecord     4     3     14    DBB            0   r[14]=mkrec(r[4..6])
22      Insert         0     14    3     users          49  intkey=r[3] data=r[14]
23    Goto           0     12    0                    0   
24    Halt           0     0     0                    0   
25    Transaction    0     1     2     0              1   usesStmtJournal=1
26    Goto           0     1     0                    0 

I think our INSERTs are currently hardcoded to assume 1. multiple tuples being inserted and 2. table w/o INTEGER PRIMARY KEY rowid alias

related: #407
cc @pereman2