select in sqlite3 failed

Question

I've defined schema like this

sqlite> .schema
CREATE TABLE session(sid vchar(100) PRIMARY KEY ASC, msgid vchar(100), updated int(32), ip vchar(40), port int(32));

but there is no answer when i query the db use select * from session when sid='xxxxx'

for example

there is a row SINKSESSIONID0153FD46440001|000000000000001542|1409109272|10.0.0.1|35963

when i use select * from session when sid='SINKSESSIONID0153FD46440001'
it failed like this

sqlite> select * from session where sid = 'SINKSESSIONID0153FD46440001';
sqlite>

but when i use select * from session when sid like 'SINKSESSIONID0153FD46440001'
the right answer is returned

sqlite> select * from session where sid like 'SINKSESSIONID0153FD46440001';
SINKSESSIONID0153FD46440001|000000000000001542|1409109272|10.0.0.1|35963

i don't know why...

btw there is more than 50000 row in session

sqlite> select count(sid) from session;
57570

thanks a lot

Check this post, you can find some useful info: http://stackoverflow.com/questions/543580/equals-vs-like — Ruslan Veselov, Aug 27 '14 at 10:08
Does it work with `select * from session where +sid='SINKSESSIONID0153FD46440001';`? — CL., Aug 27 '14 at 10:23
It looks like UPPER/LOWER case problem with COLLATE BINARY option in DB. Makes sure you query `when sid='SINK....` not for example `when sid='sINK...` (with lower case letters) in this case `sid='sINK` returns no rows but `LIKE 'sINK...` returns one row as expected. — valex, Aug 27 '14 at 10:58
What is the output of `SELECT DISTINCT typeof(sid) FROM session`? — CL., Aug 28 '14 at 06:32

score 0 · Answer 1 · answered Aug 28 '14 at 14:01

Your problem is that some (all?) sid values in the database are blobs, not strings, so a comparison with a string will not match. (LIKE does not make sense with blobs, so it automatically converts to strings.)

If the sid values are supposed to be blobs, you would have to compare them with a blob value. In an SQL query, this could be done with a blob literal:

SELECT *
FROM session
WHERE sid = x'53494E4B53455353494F4E49443031353346443436343430303031';

If the sid values are supposed to be strings, you can fix the database with a command like this:

UPDATE session SET sid = CAST(sid AS TEXT);

select in sqlite3 failed

1 Answers1