Skip to content
New issue

Have a question about this project? # for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “#”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? # to your account

unique constraint error with INSERT (1), (2) #407

Open
pereman2 opened this issue Nov 18, 2024 · 2 comments
Open

unique constraint error with INSERT (1), (2) #407

pereman2 opened this issue Nov 18, 2024 · 2 comments
Labels
bug Something isn't working compat help wanted Extra attention is needed

Comments

@pereman2
Copy link
Collaborator

$> limbo hello.db
Limbo v0.0.5
Enter ".help" for usage hints.
limbo> CREATE TABLE t(x);
limbo> INSERT INTO t VALUES (1), (2);
Runtime error: UNIQUE constraint failed: t.x (19)
limbo> SELECT * FROM t;
1
@jussisaurio
Copy link
Collaborator

jussisaurio commented Nov 21, 2024

the reason is the NotNull opcode on line 10 which prevents NewRowid from being called again on the second iteration - register 1 is not null at that point because it contains the first created rowid, and after that it fails the NotExists check.

edit: actually im not sure if thats the reason .... anyway heres the bytecode difference:

edit2: ok i think the problem here is: the insert would be correctly called with two distinct values of r[2], but the NotNull and NotExists checks are made against the same value of r[1] because NewRowid ends up getting called only once.

limbo> explain insert into t values (1), (2);
addr  opcode             p1    p2    p3    p4             p5  comment
----  -----------------  ----  ----  ----  -------------  --  -------
0     Init               0     20    0                    0   Start at 20
1     InitCoroutine      3     7     2                    0   
2     Integer            1     2     0                    0   r[2]=1
3     Yield              3     0     0                    0   
4     Integer            2     2     0                    0   r[2]=2
5     Yield              3     0     0                    0   
6     EndCoroutine       3     0     0                    0   
7     OpenWriteAsync     0     2     0                    0   
8     OpenWriteAwait     0     0     0                    0   
9     Yield              3     19    0                    0   
10    NotNull            1     12    0                    0   r[1]!=NULL -> goto 12
11    NewRowId           0     1     0                    0   
12    MustBeInt          1     0     0                    0   
13    NotExists          0     15    1                    0   
14    Halt               1555  0     0                    0   
15    MakeRecord         2     1     4                    0   r[4]=mkrec(r[2..2])
16    InsertAsync        0     4     1                    0   
17    InsertAwait        0     0     0                    0   
18    Goto               0     9     0                    0   
19    Halt               0     0     0                    0   
20    Transaction        0     1     0                    0   
21    Goto               0     1     0                    0 
sqlite> explain insert into t values (1), (2);
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     14    0                    0   Start at 14
1     InitCoroutine  1     7     2                    0   
2     Integer        1     4     0                    0   r[4]=1
3     Yield          1     0     0                    0   
4     Integer        2     4     0                    0   r[4]=2
5     Yield          1     0     0                    0   
6     EndCoroutine   1     0     0                    0   
7     OpenWrite      0     2     0     1              0   root=2 iDb=0; t
8       Yield          1     13    0                    0   
9       NewRowid       0     3     0                    0   r[3]=rowid
10      MakeRecord     4     1     8                    0   r[8]=mkrec(r[4])
11      Insert         0     8     3     t              57  intkey=r[3] data=r[8]
12    Goto           0     8     0                    0   
13    Halt           0     0     0                    0   
14    Transaction    0     1     1     0              1   usesStmtJournal=0
15    Goto           0     1     0                    0 

quite different bytecode

@jussisaurio
Copy link
Collaborator

jussisaurio commented Nov 21, 2024

sqlite> create table t(x INTEGER PRIMARY KEY);
sqlite> explain insert into t values (1), (2);
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     21    0                    0   Start at 21
1     InitCoroutine  1     7     2                    0   
2     Integer        1     4     0                    0   r[4]=1
3     Yield          1     0     0                    0   
4     Integer        2     4     0                    0   r[4]=2
5     Yield          1     0     0                    0   
6     EndCoroutine   1     0     0                    0   
7     OpenWrite      0     2     0     1              0   root=2 iDb=0; t
8       Yield          1     20    0                    0   
9       Copy           4     3     0                    0   r[3]=r[4]
10      SoftNull       4     0     0                    0   r[4]=NULL
11      NotNull        3     13    0                    0   if r[3]!=NULL goto 13
12      NewRowid       0     3     0                    0   r[3]=rowid
13      MustBeInt      3     0     0                    0   
14      Noop           0     0     0                    0   uniqueness check for ROWID
15      NotExists      0     17    3                    0   intkey=r[3]
16      Halt           1555  2     0     t.x            2   
17      MakeRecord     4     1     8     D              0   r[8]=mkrec(r[4])
18      Insert         0     8     3     t              49  intkey=r[3] data=r[8]
19    Goto           0     8     0                    0   
20    Halt           0     0     0                    0   
21    Transaction    0     1     1     0              1   usesStmtJournal=1
22    Goto           0     1     0                    0

With INTEGER PRIMARY KEY the bytecode looks more similar

i guess the difference here is that now the rowids come externally from the query so NewRowid is never called (because x is the INTEGER PRIMARY KEY, whereas in the first table with no INTEGER PRIMARY KEY they are generated with NewRowid)

So to make these correct we need to be aware of whether the user is inserting the rowid explicitly or not (i.e. INTEGER PRIMARY KEY or not)

Since we currently only support plain rowid tables (I think?) we should just copy sqlite's implementation from the first comment

# for free to join this conversation on GitHub. Already have an account? # to comment
Labels
bug Something isn't working compat help wanted Extra attention is needed
Projects
None yet
Development

No branches or pull requests

3 participants