On 07/03/2013 1:15 PM, Simon Slavin wrote:
On 7 Mar 2013, at 4:07pm, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote:
That does leave the question of what to do with cast ('1.0' as integer), though.
Without the prefix-based matching that would now return NULL rather than 1, even
though cast(1.0 as integer) would still return 1. Then again, disallowing all
floats might be better than the current practice of returning 1 from a cast of
both '1e-10' and '1e10' (the real->integer casts do the right thing, as does
assignment to a column with integer affinity).
Can you (or someone) explain the problem with
cast ('1.0' as integer)
? Possibly by explaining what "prefix-based matching" is. I see no problem
with SQLite /internally/ first casting as a REAL and then casting that REAL as an
INTEGER, resulting in the integer 1. The real question is what you'd get if you tried
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
cast ('1.1' as integer)
Can you acceptably cast a non-integer REAL as an INTEGER ? Or should /that/
produce a NULL ? Since what you're actually doing is depending on some
mathematical function like round().
IMHO, casting string version of reals to actual integers should do
whatever happens when you cast an actual real to integer. Whether that
means NULL, truncate, or round is debatable, but there's pretty strong
precedent for truncation when casting real to int.
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
