Run... pragma compile_options; ...and see if LIKE_DOESNT_MATCH_BLOBS is in the list that comes up.
If it is NOT in the list then both rows will show up. This is the case for the precompiled Windows cli for example. If it IS in the list, then only the second one that was inserted as text will show up, and the blob will not be selected. https://www.sqlite.org/compile.html#like_doesnt_match_blobs SQLITE_LIKE_DOESNT_MATCH_BLOBS 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. This compile-time option makes SQLite run more efficiently when processing queries that use the LIKE operator, at the expense of breaking backwards compatibility. However, the backwards compatibility break may be only a technicality. There was a long-standing bug in the LIKE processing logic (see https://www.sqlite.org/src/info/05f43be8fdda9f) that caused it to misbehavior for BLOB operands and nobody observed that bug in nearly 10 years of active use. So for more users, it is probably safe to enable this compile-time option and thereby save a little CPU time on LIKE queries. This compile-time option affects the SQL LIKE operator only and has no impact on the sqlite3_strlike() C-language interface. Since I just pasted it there's typo in there. "caused it to misbehavior" should be "caused it to misbehave" -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of Jose Isaias Cabrera Sent: Friday, December 13, 2019 2:58 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Difference between hex notation and string notation Sascha Ziemann, on Friday, December 13, 2019 04:16 AM, wrote... > > I have a problem to find rows in a database when I write in hex notation: > > 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 CAST(MSG AS VARCHAR) LIKE '%down'; -- > returns both rows > > This looks like a bug to me. Update to 3.30.0 or higher. It works fine for me... 14:55:34.46>sqlite3 SQLite version 3.30.0 2019-10-04 15:03:17 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL); sqlite> INSERT INTO LOG VALUES ...> (X'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578743d7368757474696e6720646f776e'); sqlite> INSERT INTO LOG VALUES ('facility=daemon;component=named;text=shutting ...> down'); sqlite> SELECT ROWID,MSG FROM LOG; 1|facility=daemon;component=named;text=shutting down 2|facility=daemon;component=named;text=shutting down sqlite> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
