Reputation: 23881
I have a database file with log data. The database contains a table LOG
and the table contains a column MSG
. There are 30 rows in the table, where the MSG
column contains the string "down" at the end of the line:
$ sqlite3 log.db "select msg from log" | grep down$ | wc -l
30
But when I try to find them with LIKE
, I get no match:
$ sqlite3 log.db "select msg from log where msg like '%down'" | grep down$ | wc -l
0
What could be the reason for this?
Update: MCVE
CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL);
INSERT INTO LOG VALUES (X'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578743d7368757474696e6720646f776e');
SELECT MSG FROM LOG; -- returns the row
SELECT MSG FROM LOG WHERE MSG LIKE '%down'; -- returns nothing
SELECT MSG FROM LOG WHERE MSG LIKE '%down%'; -- returns nothing
SELECT MSG FROM LOG WHERE CAST(MSG AS VARCHAR) LIKE '%down'; -- returns the row
I have no idea why a cast from VARCHAR
to VARCHAR
makes a difference.
Update: Another MCVE
CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL);
INSERT INTO LOG VALUES (X'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578743d7368757474696e6720646f776e');
INSERT INTO LOG VALUES ('facility=daemon;component=named;text=shutting down');
SELECT ROWID,MSG FROM LOG; -- returns both rows
SELECT ROWID,MSG FROM LOG WHERE MSG LIKE '%down'; -- returns just the second
SELECT ROWID,MSG FROM LOG WHERE MSG LIKE '%down%'; -- returns just the second
SELECT ROWID,MSG FROM LOG WHERE CAST(MSG AS VARCHAR) LIKE '%down'; -- returns both rows
SELECT HEX(MSG) FROM LOG;
Upvotes: 0
Views: 697
Reputation: 52674
Given your sample data and the results you're seeing on different versions of sqlite, here's what I'm sure is happening.
First, you're inserting blobs into your table, not strings. These blobs are stored unchanged, instead of being converted to strings the way numeric values are for a column with TEXT
affinity like you're using. See the documentation for details about column affinity and implicit datatype conversions.
Second, the sqlite3 instance that's not matching those blobs was built with the SQLITE_LIKE_DOESNT_MATCH_BLOBS
configuration option turned on, and the one that is matching them was built with it turned off (The default setting).
This compile-time option causes the LIKE operator to always return False if either operand is a BLOB. The default behavior of LIKE is that BLOB operands are cast to TEXT before the comparison is done.
If you check PRAGMA compile_options
output you should be able to verify that it's being used.
Upvotes: 1