On 07/03/2013 1:45 PM, Simon Slavin wrote:
On 7 Mar 2013, at 6:27pm, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote:
The problem is sqlite3 doesn't cast to REAL first. It just parses the string
until it hits '.' (which isn't a valid part of an integer) and then returns
whatever it had accumulated so far. That breaks in creative ways for values
like:
cast('10e-1' as integer) -- 10
cast('abc' as integer) -- 0
Ah, thank you. Good explanation.
I consider this behaviour wrong, too. Casting a string as INTEGER should take
into account what it means as a REAL. For instance, as a
programmer/statistician, what do you think the desired behaviour would be for
CAST('0.999999999' AS INTEGER)
I know what I want. Perhaps this can be fixed in SQLite4.
I tend to agree that some intelligent rounding is in order (those darn
epsilons in floating point!).
Meanwhile, though, I'd be delighted if column affinity, cast(), implicit
conversions performed by arithmetic operations, check(), and triggers
all behaved the same way, with the current behavior of column affinity
probably the least surprising/troublesome. Right now those five
operations have four different behaviors to keep track of.
And that's *before* you factor in what happens when you use several of
those operations on the same column, e.g.:
sqlite> select x,typeof(x),0+x from t1;
3|integer|3
3.0|real|3.0
3.0|real|3.0
3|text|3
3.0|text|3.0
30e-1|text|3.0
sqlite> select x,0+x,typeof(x) from t1;
3|3|integer
3.0|3.0|real
3.0|3.0|real
3|3|integer
3.0|3.0|real
30e-1|3.0|real
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
