title: What could be the reason why sqlite's pattern match does not work?
body: <p>I have a database file with log data. The database contains a table <code>LOG</code> and the table contains a column <code>MSG</code>. There are 30 rows in the table, where the <code>MSG</code> column contains the string "down" at the end of the line:</p>
  
  <pre><code>$ sqlite3 log.db "select msg from log" | grep down$ | wc -l
  30
  </code></pre>
  
  <p>But when I try to find them with <code>LIKE</code>, I get no match:</p>
  
  <pre><code>$ sqlite3 log.db "select msg from log where msg like '%down'" | grep down$ | wc -l
  0
  </code></pre>
  
  <p>What could be the reason for this?</p>
  
  <p><strong>Update: MCVE</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p>I have no idea why a cast from <code>VARCHAR</code> to <code>VARCHAR</code> makes a difference.</p>
  
  <p><strong>Update: Another MCVE</strong></p>
  
  <pre><code>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;
  </code></pre>