On 2015-03-19 05:32 PM, Paul wrote: >> On 19 Mar 2015, at 3:19pm, Paul wrote: >> >>> This may cause very nasty hard-to-find bugs, since SQLite allows to store >>> any content inside BLOB field: >>> >>> sqlite> create table foo(a int, b int, primary key(a, b)); >>> sqlite> insert into foo(a, b) VALUES(1, ''), (1, x''); >>> sqlite> select *, length(b) from foo; >> True, but the content which is stored is not of BLOB type unless you stored >> a BLOB. Do >> >> select *, typeof(b), length(b) from foo; >> >> and you'll find that you get out what you put in. >> > Yeah, that's true. But still, this can cause nasty bugs. > It feels unnatural, because there is implicit conversion from string to int:
I hear what you are saying Paul, but there is no comparison where different types (especially involving blobs) can ever be confused in a way that one iteration will survive a Uniqueness test and another won't. At least, I thought there wouldn't be, but from your post I decided to check anyway, trying all different kinds of ways to "trick" SQLite into duplicating a key by using casts and the like. One of my test scripts attached hereunder, but as of now I am unsuccessful. Someone else might be able to find a hole, but I couldn't break it in any way - thus no reason yet to think any kind of bug exists. The question remains as to whether one may regard an empty blob to be equal to zero or not, or indeed an empty string. I don't think so, but other opinions may exist. -- ================================================================================================ CREATE TABLE foo(a int, b int, PRIMARY KEY(a, b)); INSERT INTO foo(a, b) VALUES(1, ''), (1, x''); INSERT INTO foo(a, b) VALUES(2, 255), (2, x'FF'); INSERT INTO foo(a, b) VALUES(3, CAST(x'7F' AS INT)), (3, x'7F'); SELECT T1.*, length(T1.b) AS L1, T2.*, length(T2.b) AS L2 FROM foo AS T1 JOIN foo AS T2 ON T1.b=T2.b WHERE 1; -- a b L1 a b L2 -- - - -- - - -- -- 1 0 1 0 -- 1 0 1 0 -- 2 255 3 2 255 3 -- 2 0xFF 1 2 0xFF 1 -- 3 0 1 3 0 1 -- 3 0x7F 1 3 0x7F 1 -- Item Stats: Item No: 5 Query Size (Chars): 118 -- Result Columns: 6 Result Rows: 6 -- VM Work Steps: 154 Rows Modified: 0 -- Full Query Time: -- --- --- --- --.---- -- Query Result: Success. -- ------------------------------------------------------------------------------------------------ SELECT T1.*, length(T1.b) AS L1, T2.*, length(T2.b) AS L2 FROM foo AS T1 JOIN foo AS T2 ON CAST(T1.b AS INT)=CAST(T2.b AS INT) WHERE 1; -- a b L1 a b L2 -- - - -- - - -- -- 1 0 1 0 -- 1 0 1 0 -- 1 0 2 0xFF 1 -- 1 0 3 0 1 -- 1 0 3 0x7F 1 -- 1 0 1 0 -- 1 0 1 0 -- 1 0 2 0xFF 1 -- 1 0 3 0 1 -- 1 0 3 0x7F 1 -- 2 255 3 2 255 3 -- 2 0xFF 1 1 0 -- 2 0xFF 1 1 0 -- 2 0xFF 1 2 0xFF 1 -- 2 0xFF 1 3 0 1 -- 2 0xFF 1 3 0x7F 1 -- 3 0 1 1 0 -- 3 0 1 1 0 -- 3 0 1 2 0xFF 1 -- 3 0 1 3 0 1 -- 3 0 1 3 0x7F 1 -- 3 0x7F 1 1 0 -- 3 0x7F 1 1 0 -- 3 0x7F 1 2 0xFF 1 -- 3 0x7F 1 3 0 1 -- 3 0x7F 1 3 0x7F 1 -- Item Stats: Item No: 6 Query Size (Chars): 144 -- Result Columns: 6 Result Rows: 26 -- VM Work Steps: 472 Rows Modified: 0 -- Full Query Time: -- --- --- --- --.---- -- Query Result: Success. -- ------------------------------------------------------------------------------------------------ UPDATE foo SET b=CAST(b AS INT) WHERE 1; -- 2015-03-19 18:25:18.614 | [ERROR] UNIQUE constraint failed: foo.a, foo.b -- Script Stats: Total Script Execution Time: 0d 00h 00m and 00.031s -- Total Script Query Time: 0d 00h 00m and 00.004s -- Total Database Rows Changed: 6 -- Total Virtual-Machine Steps: 851 -- Last executed Item Index: 7 -- Last Script Error: Script Failed in Item 6: UNIQUE constraint failed: foo.a, foo.b -- ------------------------------------------------------------------------------------------------ -- 2015-03-19 18:25:18.615 | [Info] Script failed - Rolling back... -- 2015-03-19 18:25:18.618 | [Success] Transaction Rolled back. -- 2015-03-19 18:25:18.618 | [ERROR] Failed to complete: Script Failed in Item 6: UNIQUE constraint failed: foo.a, foo.b -- ------- DB-Engine Logs (Contains logged information from all DB connections during run) ------ -- [2015-03-19 18:25:18.571] APPLICATION : Script D:\Documents\BlobUniquenessTestScript.sql started with Initialization at 18:25:18.571 on 19 March. -- [2015-03-19 18:25:18.612] ERROR (284) : automatic index on foo(b) -- [2015-03-19 18:25:18.614] ERROR (1555) : abort at 23 in [UPDATE foo SET b=CAST(b AS INT) WHERE 1;]: UNIQUE constraint failed: foo.a, foo.b -- ================================================================================================
