BLOB and TEXT comparisons

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

BLOB and TEXT comparisons

Charles Leifer
I ran into a somewhat surprising result and wanted to just get a little
clarification.

I'll use the following statement as an example:

SELECT SUBSTR(?, 1, 3) == ?

And the parameters will be:

* "abcde"
* "abc"

If I bind both parameters using the same type, the comparison returns True:

* sqlite3_bind_text
* sqlite3_bind_blob

However, if I mix the types, e.g. sqlite3_bind_text("abcde") and
sqlite3_bind_blob("abc") then the comparison returns False.

Fom a byte-to-byte perspective, this comparison should always return True.

What's going on?

Thanks!
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BLOB and TEXT comparisons

Clemens Ladisch
Charles Leifer wrote:
> SELECT SUBSTR(?, 1, 3) == ?
>
> However, if I mix the types, e.g. sqlite3_bind_text("abcde") and
> sqlite3_bind_blob("abc") then the comparison returns False.
>
> Fom a byte-to-byte perspective, this comparison should always return True.
>
> What's going on?

Apparently, not only a byte-to-byte comparison.

Withou affinity, only integer and real values can compare equal:

sqlite> select 1=1.0, 1='1', '1'=x'31';
1|0|0

See <https://www.sqlite.org/datatype3.html#comparison_expressions>.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BLOB and TEXT comparisons

Darren Duncan
In reply to this post by Charles Leifer
What's going on is that a Blob and a Text are logically different values and so
can never possibly equal each other.

Also they aren't represented by the same bytes either, because every value
includes its type and the comparison is comparing the bytes indicating the type
as well, which differ.

-- Darren Duncan

On 2019-07-12 7:19 a.m., Charles Leifer wrote:

> I ran into a somewhat surprising result and wanted to just get a little
> clarification.
>
> I'll use the following statement as an example:
>
> SELECT SUBSTR(?, 1, 3) == ?
>
> And the parameters will be:
>
> * "abcde"
> * "abc"
>
> If I bind both parameters using the same type, the comparison returns True:
>
> * sqlite3_bind_text
> * sqlite3_bind_blob
>
> However, if I mix the types, e.g. sqlite3_bind_text("abcde") and
> sqlite3_bind_blob("abc") then the comparison returns False.
>
> Fom a byte-to-byte perspective, this comparison should always return True.
>
> What's going on?
>
> Thanks!

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] BLOB and TEXT comparisons

Hick Gunter
In reply to this post by Charles Leifer
https://sqlite.org/datatype3.html

4.1. Sort Order

The results of a comparison depend on the storage classes of the operands, according to the following rules:

•A value with storage class NULL is considered less than any other value (including another value with storage class NULL).
•An INTEGER or REAL value is less than any TEXT or BLOB value. When an INTEGER or REAL is compared to another INTEGER or REAL, a numerical comparison is performed.
•A TEXT value is less than a BLOB value. When two TEXT values are compared an appropriate collating sequence is used to determine the result.
•When two BLOB values are compared, the result is determined using memcmp().

"A TEXT value is less than a BLOB value"


-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Charles Leifer
Gesendet: Freitag, 12. Juli 2019 16:19
An: General Discussion of SQLite Database <[hidden email]>
Betreff: [EXTERNAL] [sqlite] BLOB and TEXT comparisons

I ran into a somewhat surprising result and wanted to just get a little clarification.

I'll use the following statement as an example:

SELECT SUBSTR(?, 1, 3) == ?

And the parameters will be:

* "abcde"
* "abc"

If I bind both parameters using the same type, the comparison returns True:

* sqlite3_bind_text
* sqlite3_bind_blob

However, if I mix the types, e.g. sqlite3_bind_text("abcde") and
sqlite3_bind_blob("abc") then the comparison returns False.

Fom a byte-to-byte perspective, this comparison should always return True.

What's going on?

Thanks!
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users