In sqlite 3.7.14 in debug mode it raises a constraint error that states that
the first argument is an invalid mem struct at location 7 in the trigger
subprogram
(gdb) print *pOp
$1 = {opcode = 75 'K', p4type = -4 '\374', opflags = 21 '\025', p5 = 107 'k',
p1 = 4, p2 = 11, p3 = 5, p4 = {i = 352254408, p = 0x14fef9c8, z = 0x14fef9c8
"@\372\376\024", pI64 = 0x14fef9c8, pReal = 0x14fef9c8, pFunc = 0x14fef9c8,
pVdbeFunc = 0x14fef9c8, pColl = 0x14fef9c8, pMem = 0x14fef9c8, pVtab =
0x14fef9c8, pKeyInfo = 0x14fef9c8, ai = 0x14fef9c8, pProgram = 0x14fef9c8,
xAdvance = 0x14fef9c8}, zComment = 0x0}
(gdb) print aMem[4]
$2 = {db = 0x14fef698, z = 0x0, r = 0, u = {i = 0, nZero = 0, pDef = 0x0,
pRowSet = 0x0, pFrame = 0x0}, n = 0, flags = 4224, type = 0 '\000', enc = 0
'\000', pScopyFrom = 0x0, pFiller = 0x0, xDel = 0, zMalloc = 0x0}
asql> explain delete from ical;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00 NULL
1 Goto 0 29 0 00 NULL
2 Integer 0 1 0 00 NULL
3 Null 0 2 0 00 NULL
4 OpenRead 0 2 1 0 00 ical
5 Rewind 0 10 0 00 NULL
6 Rowid 0 3 0 00 NULL
7 RowSetAdd 2 3 0 00 NULL
8 AddImm 1 1 0 00 NULL
9 Next 0 6 0 01 NULL
10 Close 0 0 0 00 NULL
11 OpenWrite 0 2 1 3 00 ical
12 RowSetRead 2 26 3 00 NULL
13 NotExists 0 25 3 00 NULL
14 Copy 3 4 0 00 NULL
15 NotExists 0 25 3 00 NULL
16 OpenRead 1 3 1 2 00 icalentry
17 Rewind 1 22 0 00 NULL
18 Column 1 1 8 00 icalentry.calendar
19 Ne 4 21 8 collseq(BINARY) 6b NULL
20 FkCounter 0 1 0 00 NULL
21 Next 1 18 0 01 NULL
22 Close 1 0 0 00 NULL
23 Delete 0 1 0 ical 00 NULL
24 Program 4 0 10 program 00 Call: fkey.abort
25 Goto 0 12 0 00 NULL
26 Close 0 0 0 00 NULL
27 ResultRow 1 1 0 00 NULL
28 Halt 0 0 0 00 NULL
29 Transaction 1 1 0 00 NULL
30 VerifyCookie 1 2 0 00 NULL
31 TableLock 1 2 1 ical 00 NULL
32 TableLock 1 3 1 icalentry 00 NULL
33 Goto 0 2 0 00 NULL
0 Trace 0 0 0 -- TRIGGER 00 Start: .abort (AFTER
DELETE ON ical)
1 Integer 0 1 0 00 NULL
2 Null 0 2 0 00 NULL
3 Param 2 4 0 00 old.oid -> $4
4 OpenRead 0 3 1 2 00 icalentry
5 Rewind 0 12 0 00 NULL
6 Column 0 1 5 00 icalentry.calendar
7 Ne 4 11 5 collseq(BINARY) 6b NULL
8 Rowid 0 3 0 00 NULL
9 RowSetAdd 2 3 0 00 NULL
10 AddImm 1 1 0 00 NULL
11 Next 0 6 0 01 NULL
12 Close 0 0 0 00 NULL
13 OpenWrite 0 3 1 3 00 icalentry
14 RowSetRead 2 30 3 00 NULL
15 NotExists 0 29 3 00 NULL
16 Copy 3 7 0 00 NULL
17 Column 0 1 9 00 icalentry.calendar
18 NotExists 0 29 3 00 NULL
19 FkIfZero 0 27 0 00 NULL
20 IsNull 9 27 0 00 NULL
21 SCopy 9 5 0 00 NULL
22 MustBeInt 5 26 0 00 NULL
23 OpenRead 1 2 1 3 00 ical
24 NotExists 1 26 5 00 NULL
25 Goto 0 27 0 00 NULL
26 FkCounter 0 -1 0 00 NULL
27 Close 1 0 0 00 NULL
28 Delete 0 1 0 icalentry 00 NULL
29 Goto 0 14 0 00 NULL
30 Close 0 0 0 00 NULL
31 ResetCount 0 0 0 00 NULL
32 Halt 0 0 0 00 End: .abort
-----Urspr?ngliche Nachricht-----
Von: R.Smith [mailto:rsmith at rsweb.co.za]
Gesendet: Donnerstag, 09. April 2015 20:52
An: sqlite-users at mailinglists.sqlite.org
Betreff: Re: [sqlite] possible Bug
On 2015-04-09 07:57 PM, Simon Slavin wrote:
> On 9 Apr 2015, at 6:04pm, Gustav Melno <knarks at online.de> wrote:
>
>> Thanks for the help. Adding a trailing underscore helped also. I'm still
>> wondering why insertion worked at all because defining oid as column name
>> with the type VARCHAR should result in an error on execution.
> The three names for the integer primary key column (oid, rowid, _rowid_) work
> only if the table doesn't have a defined column of that name. This allows
> compatibility with people who didn't know that they were had special meanings
> for SQLite. Clever, isn't it ?
Except it clearly did not work like that in the OP's case. He had defined
"oid" in his table, but then when he inserted into column oid, the value either
went to the rowid column or somehow messed up the rowid enough to cause a
constraint failure. I'm starting to think this might be a bug.
I've checked it like this (Copy paste this to a file and feed to an sqlite
parser):
-- 2015-04-09 20:34:18.791 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TABLE idTest( -- Table with every row id reference
id INTEGER PRIMARY KEY,
col1 TEXT,
oid INTEGER,
rowid INTEGER,
_rowid_ INTEGER
);
CREATE TABLE fkTest( -- table to test the Foreign Key
id INTEGER PRIMARY KEY,
Data TEXT,
FOREIGN KEY (id) REFERENCES idTest(id)
ON UPDATE CASCADE
ON DELETE CASCADE -- changes and deletes must cascade );
INSERT INTO idTest (col1, oid, rowid, _rowid_) VALUES
('TestA', 10, 100, 1000),
('TestA', 20, 200, 2000),
('TestA', 30, 300, 3000)
;
INSERT INTO idTest (col1, oid) VALUES ('TestB', 'aaa');
INSERT INTO fkTest (id, Data) VALUES (last_insert_rowid(), 'FK 1');
INSERT INTO idTest (col1, oid) VALUES ('TestC', 20);
INSERT INTO fkTest (id, Data) VALUES (last_insert_rowid(), 'FK 2');
-- So far all happens as expected and works perfectly...
SELECT * FROM idTest;
-- id | col1 | oid | rowid | _rowid_
-- ------------ | ------- | ----- | ----- | -------
-- 1 | TestA | 10 | 100 | 1000
-- 2 | TestA | 20 | 200 | 2000
-- 3 | TestA | 30 | 300 | 3000
-- 4 | TestB | aaa | |
-- 5 | TestC | 20 | |
SELECT F.*, I.*
FROM fkTest AS F
LEFT JOIN idTest AS I ON F.id=I.id;
-- id | Data | id | col1 | oid | rowid |
_rowid_
-- ------------ | ------ | ------------ | ------- | ----- | ----- |
-------
-- 4 | FK 1 | 4 | TestB | aaa | |
-- 5 | FK 2 | 5 | TestC | 20 | |
-- Here the problem happens. this statement should in SQL terms succeed
and update the fkTest
-- table - here I am NOT using oid as the OP did, I am using the correct
reference but it still fails.
-- And - it works if I remove the rowid references from the first table
create.
UPDATE idTest SET id = 40 WHERE id = 4;
-- 2015-04-09 20:34:18.807 | [ERROR] FOREIGN KEY constraint failed
-- Script Stats: Total Script Execution Time: 0d 00h 00m and
00.029s
-- Total Script Query Time: 0d 00h 00m and
00.005s
-- Total Database Rows Changed: 8
-- Total Virtual-Machine Steps: 396
-- Last executed Item Index: 12
-- Last Script Error: Script Failed in Item 11:
FOREIGN KEY constraint failed
--
------------------------------------------------------------------------------------------------
I won't add more test script outputs, but it fails for a DELETE request
too. I think including rowid / oid / _rowid_ in a table works normally
since the correct values ended up in the correct columns, but somehow
including those aliases confuses the FK check mechanism.
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___________________________________________
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
