The comparison is between a column with "numeric" (integer) affinity and a text
value with no affinity. The text value is an empty string.
Affinity conversion would attempt to convert the text value with no affinity
into a numeric value (0) IF AND ONLY IF the conversion is lossless and
reversible.
In the case of an empty string, the conversion is not reversible: cast('' as
numeric) -> 0 but cast(0 as text) -> '0' ('0' != '') so therefore no conversion
is made.
Since the '' is a value with no affinity, no conversion to text is done.
Because the column has an affinity there can be no attempt to convert it to a
text storage class to permit the comparison to be performed in the text domain.
You would have to do this yourself with a cast.
This means that an numeric is compared to text, and the numeric value is always
less.
If, rather than an empty string, you had used a value of the string which
permitted the conversion of the string to NUMERIC to be both lossless and
reversible (ie, actually had something in the string, like '0'), you would
obtain different results.
(note that a value has no affinity, although it does have a storage class, and
the conversion rules are based on affinities, not storage classes. Affinity is
applied to a value either because it originated from a column which has an
affinity or is assigned by the cast operator).
Note that although 5 == '5' is false, this is because there is no affinity
assigned to either of the values 5 and '5', so no conversions occur, and
numbers are always less than text strings.
If one did assign one of the values an affinity, for example by using a cast on
either (cast(5 as integer) or cast('5' as text)) or because one of them was
derived from a column-value that had an affinity (either text or numeric), then
the result would be true since '5' can be losslessly and reversibly converted
to an integer (and 5 can be losslessly and reversibly converted to text '5'),
and in either case the result of the converted comparison in either domain is
true.
https://www.sqlite.org/datatype3.html#comparisons
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of ??????
>Sent: Sunday, 19 August, 2018 00:58
>To: sqlite-users
>Subject: [sqlite] bug: compare number with empty string get different
>resut
>
>create table t1(c int);
>insert into t1 values (1),(2),(3);
>select * from t1 where c>'' ; --get nothing(empty result)
>select * from t1 where c<''; --get result set:1,2,3
>
>
>create table t2(c char);
>insert into t2 values (1),(2),(3);
>select * from t2 where c>''; --get correct result set: 1,2,3
>
>the only difference of t1 and t2 is the column data type: int vs char
>_______________________________________________
>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
