Re: [sqlite] Difference between hex notation and string notation
Yeah, mine was vanilla compiled,
sqlite> pragma compile_options;
COMPILER=gcc-5.2.0
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS5
ENABLE_JSON1
ENABLE_RTREE
ENABLE_STMTVTAB
ENABLE_UNKNOWN_SQL_FUNCTION
THREADSAFE=0
sqlite>
From: sqlite-users on behalf of
David Raymond
Sent: Friday, December 13, 2019 03:14 PM
To: SQLite mailing list
Subject: Re: [sqlite] Difference between hex notation and string notation
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 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Difference between hex notation and string notation
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 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
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
Re: [sqlite] Difference between hex notation and string notation
On Friday, 13 December, 2019 02:16, Sascha Ziemann 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');
Yes, those are identical byte sequences, however, the first one is a "blob" and
the second one is "text":
SELECT ROWID, typeof(MSG), MSG FROM LOG;
1|blob|facility=daemon;component=named;text=shutting down
2|text|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
The version of the SQLite3 library that you are using must have been compiled
with the SQLITE_LIKE_DOESNT_MATCH_BLOBS preprocessor symbol defined. If you
want LIKE to match against blobs, do not define this preprocessor symbol when
compiling SQLite3.
When you cast msg to text it is now text and not a blob and therefore LIKE can
match it.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Difference between hex notation and string notation
I would suggest that if you ran:
SELECT ROWID,MSG,typeof(MSG) FROM LOG;
and read https://sqlite.org/datatype3.html
you would have a better idea of what is going on. You might still consider
it a but, but it is expected behavior.
Gerry
On Fri, Dec 13, 2019 at 8:51 AM Sascha Ziemann 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.
>
> Regards
> ___
> 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
Re: [sqlite] Difference between hex notation and string notation
On 13 Dec 2019, at 9:16am, Sascha Ziemann wrote: > returns just the second Works fine for me in SQLite version 3.28.0 2019-04-15 14:49:49 Have you tried it in the SQLite command-line tool ? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Difference between hex notation and string notation
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.
Regards
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
