bug: compare number with empty string get different resut

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

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: bug: compare number with empty string get different resut

Abroży Nieprzełoży
It's documented behaviour.
https://www.sqlite.org/datatype3.html#comparisons


2018-08-19 8:58 GMT+02:00, 麦轲数据管家 <[hidden email]>:

> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: bug: compare number with empty string get different resut

R Smith-2
In reply to this post by 麦轲数据管家
On 2018/08/19 8:58 AM, 麦轲数据管家 wrote:

> 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

And what an amazingly important difference that is!

In the first instance, you tell the schema you wish to store values in
column c which you would like to be treated as INTEGERs. Not only do you
ASK it to be treated as integer, but you then go ahead and INSERT them
as integers (without quotes).

Then later, you try to compare these integer values against strings, and
an Integer and strings are not compatible and will fail in any strongly
typed language, and in a duck-typing system like SQLite, there are
usually rules defined for when an incompatible type comparison happens.
In the case where one operand is is INT and one is TEXT (like in your
first example), then SQLite uses Numeric affinity for the TEXT side
USUALLY, but since in this case the empty string ('') doesn't compute to
anything useful in Numeric terms, that conversion is moot.  [Should { ''
= '0' } return true?]

In the second example you ask it to store the values as strings
(characters) and then continue to compare them with other strings,
something that is definitely allowed, easily understood by ANY type
system and as expected, yield correct results.

A simple cast would fix your problem:

create table t1(c int);
insert into t1 values (1),(2),(3);
select * from t1 where cast(c AS text) > '' ;   --gets correct results

Or, using a function that implicitly casts, like this:

create table t1(c int);
insert into t1 values (1),(2),(3);
select * from t1 where trim(c) > '' ;   --gets correct results


I hope that answers that, but let me offer some reasoning as to why it is not only needed, but also useful/efficient to choose the right types/affinities:

Equality is a straight forward concept in set theory, but it's weird thing in reality. For example, if I have two of the exact same model Toyotas, we could say they are equal. But what if one is green and one red? - Some would say they are exactly the same cars if you consider their "carness", and other would point out that they are not exactly the same because the colour is different. Similarly, we can ask if two values are the same, but they have different types, are they really equal? Arguments can be made for both answers and the mentioned rules typically aim to choose the best in any situation. To further illustrate the problem faced by SQLite (and duck typing systems in general):  Would you say the value 1.23E+5 should be higher or lower than the text '1.23E10', '1.23E' or '12345'? (At which point do we choose one compare method over the other?)

In human terms it feels "easy" to check the entire text for "numberness", but I don't want my comparator to dwell on numbernicity for thousands of CPU cycles on every comparison before it decides. This is why we have types and affinities to express how we want it to interpret values. (Apologies for inventing lots of words there). If SQLite knows before-hand what the operand affinities are, it spends no time figuring out which rule to apply, or more importantly, converting values to fit the decided-upon rule.

SQLite makes a good effort to compare apples with apples, and does so rather efficiently, but the less it has to "figure out" on the fly, the better.

Here are some simple examples to show the (sometimes less intuitive) workings when comparing things in SQLite:


select 5 = '5' ;
   -- -------
   --    0


select CAST('5' AS INT) = '5' ;
   -- ------------
   --       1


select CAST(5 AS TEXT) = '5' ;
   -- ------------
   --       1


select 534545 < ' 1' ;
   -- ------------
   --       1



Cheers,
Ryan


_______________________________________________
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: bug: compare number with empty string get different resut

Norman Dunbar
Duck Typing? Never heard that before.

Cheers,
Norm.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity and any "auto corrections" that are just wrong!
_______________________________________________
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: bug: compare number with empty string get different resut

Warren Young
On Aug 19, 2018, at 10:03 AM, Norman Dunbar <[hidden email]> wrote:
>
> Duck Typing? Never heard that before.

It’s a pretty common term of art in the software development world:

   https://en.wikipedia.org/wiki/Duck_typing
_______________________________________________
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: bug: compare number with empty string get different resut

Simon Slavin-3
On 19 Aug 2018, at 7:56pm, Warren Young <[hidden email]> wrote:

> On Aug 19, 2018, at 10:03 AM, Norman Dunbar <[hidden email]> wrote:
>
>> Duck Typing? Never heard that before.
>
> It’s a pretty common term of art in the software development world:
>
>   https://en.wikipedia.org/wiki/Duck_typing

That article was written 20 years too late.  Duck Typing was around before everything had methods and properties.  An early form of Duck Typing looked like this (pseudocode, not a real language):

* Attempt to figure out what kind of value is in A
* without risking the program crashing
* A is source, B is result, T is temporary and can be lost
*
LET T = A + 11
* If A is a string, plus appends, and T is now a string ending in "11"
* If A is a number, T is a number, but cannot be 2 characters longer
*
IF LEN(T) = LEN(A) + 2 THEN LET B = "string" ELSE LET B = "number"

Because of the limitations of programming languages at the time, you were often limited to passing just one parameter or one variable.  But using a string function on a number, or vice versa, would crash the program with an error message.  Hence code like that.

Simon.
_______________________________________________
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: bug: compare number with empty string get different resut

Norman Dunbar
Ok, thanks everyone, it's perfectly clear now.

Cheers,
Norm.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
_______________________________________________
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: bug: compare number with empty string get different resut

Keith Medcalf
In reply to this post by 麦轲数据管家

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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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