CODEWITHSUNDEEP
sqlitesql-like
ceving
ceving

Reputation: 23881

What could be the reason why sqlite's pattern match does not work?

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

Answers (1)

Shawn
Shawn

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

Related Questions