SQLite Comparisons (1 != '1')
Back to sqlite.
I’m extremely okay with SQLite doing things differently from what other DB engines do. Type annotations being optional is unusual, but fine.
But then this bit me hard:
SQLite Distinguishes Between Integer And Text Literals SQLite says that the following query returns false:
SELECT 1='1';It does this because an integer is not a string. Every other major SQL database engine says this is true, for reasons that the creator of SQLite does not understand.
I couldn’t understand why I was only updating half of my items:
sqlite> update times set type = 3 where user = 1;
sqlite> select * from times;
user type start end
---- ---- ---------- ----------
1 3 1732732843
1 3 1732732847
1 1 1732635074
1 1 1732529576
Huh???
Then it finally dawned on me, the first two entries had been created by hand
with values (1, 1, unixepoch('now'). (Where user was clearly an integer.)
But my application was inserting '1' as a string (apparently).
Sure enough, this updated all of the string "1" users:
sqlite> update times set type = 777 where user = '1';
sqlite> select * from times;
user type start end
---- ---- ---------- ----------
1 3 1732732843
1 3 1732732847
1 777 1732635074
1 777 1732529576
Adding type annotations to 'fix' the dirty data
SQLite calls the type annotations "affinities" to make it clear that these are not strictly enforced - merely suggestions. (SQLite now has STRICT tables which do enforce types, but I’m not too concerned about that. I just want my dang comparisons to work as expected!)
My needs are: I may end up sending "1" as either a string or integer from my front-end interface because I don’t want to bulk up my code with type conversions just to make a type system happy (here I agree with SQLite). However, I have zero desire to ever store my integer IDs as strings. I want the conversion to happen. And I always, always, always want any form of "1" to match integer "1" in my DB.
There’s probably a way to update my existing tables, but since my DB is super tiny, I’m just going to drop them and re-create them:
sqlite> drop table users; sqlite> drop table times;
I’m adding INTEGER type "affinities" to all fields in my times table because
even the date-times are integers (seconds since the Unix Epoch):
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name,
picture,
login
);
CREATE TABLE times (
user INTEGER,
type INTEGER,
start INTEGER, -- unix epoch datetime
end INTEGER -- unix epoch datetime
);
Here’s some test data: I insert rows with user IDs "1" as both integer and string values:
sqlite> insert into times values (1,1,unixepoch('now'),null);
sqlite> insert into times values ('1',1,unixepoch('now'),null);
Can I select them using an integer value, like the field?
sqlite> select * from times where user = 1;
user type start end
---- ---- ---------- ---
1 1 1732924663
1 1 1732924671
Yes.
And how about using a string value?
sqlite> select * from times where user = '1';
user type start end
---- ---- ---------- ---
1 1 1732924663
1 1 1732924671
Also yes! Okay, I think that settles it.
Conclusion
I find SQLite’s permissiveness in storage at odds with its strictness in comparison.
I’m a little surprised this is the first time I’ve run into this problem, and am suspicious that I’ve simply not noticed bugs in some of my previous hobby applications? Or perhaps my application type systems have prevented the mismatched comparisons from happening in the first place?
Either way, I think I now lean heavily towards using type affinities for any numeric field that will be used for comparison, particulary ID fields and especially those which will be used for joins.