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