David Finlayson wrote:
Thanks for the link <http://www.sqlite.org/datatype3.html>. In section
6 of this page there is mention of Affinity Modes, how do you activate
these?
See section 2.1 of that page.
As it is now you can get completely irrational behavior with
mathematical operators:
sqlite> create table temp (a INTEGER);
sqlite> insert into temp values("1");
sqlite> insert into temp values("1.9");
sqlite> insert into temp values("2hello");
sqlite> insert into temp values("hello2");
sqlite> select * from temp;
a
----------
1
1.9
2hello
hello2
sqlite> select sum(a) from temp;
sum(a)
----------
4.9
sqlite> select sum(a) from temp where a = "2hello";
sum(a)
----------
2
sqlite> select sum(a) from temp where a = "hello2";
sum(a)
----------
0
That is just irrational. str("2hello") should always return an error
or null or maybe even 0, but surely not 2. Is this some kind of
casting behavior inherited from C?
Sort of. The text values are converted from strings to integers using
atoi() which stops on the first illegal character.
This usually isn't a problem in practice. Its is also kind of
"irrational" to have both string and numeric data in the same column and
expect to get a valid sum.
If all the values are actually string representations of actual numeric
data, the conversions will succeed and the sum will be correct. This
behavior is a holdover from earlier versions of sqlite which were
completely typeless (all data was stored as text strings).
Also, significant white space between delimiters? ",0.9," is numeric
", 0.9," is text. That isn't very friendly.
I agree, but this is only a problem with the .import meta command and
not something inherent in sqlite. You can always write your own program
to read the data the way you want (ignoring whitespace) and call the
correct sqlite API functions to bind that data with the correct type
when it is inserted into sqlite.
