On Mar 13, 2009, at 10:10 PM, Fitter Man wrote: > > @John: Is that documented anywhere? I ask because there are some > cases I'd > like to understand better without going through a lot of testing to > determine how it works. I'm inferring from your example the rule is > all > numerics come first, with integer and floats interleaved according > to their > numeric sequence, and the remaining values would be treated as > strings and > sorted accordingly. (Null values get stuck somewhere: I think MySQL > puts > them at the beginning, but again I'll have to fiddle with this to > figure it > out, hence my request for a document reference.) Thanks for the prompt > reply.
Everything you need is in the one you already read: http://www.sqlite.org/datatype3.html Section 3 describes how comparisons are made. Blobs are larger than strings which are larger than numbers which are larger than NULL. No exceptions. Try this: sqlite> SELECT 5 > '4'; It returns 0, because the string '4' is always greater than the number 5. However, SQLite does what it calls "applying affinity" to values in some circumstances. "Applying affinity" is fancy term for changing a text value that looks like a number to a number, or changing a number to a text field. Affinity may be applied in two circumstances: * When inserting a value into a table. The affinity applied (if any) depends on the declaration of the column type. Say you do: CREATE TABLE t1(a INTEGER, b TEXT); INSERT INTO t1 VALUES('10', 10); Despite what it looks like, column a now contains a number and column b contains a text field. If I tried to insert a text value like 'abc' into column a, the attempt to convert to a number would fail and the value stored would be a text field. * When you compare something to a column value, the affinity of the column may be applied to the "something" before the comparison takes place. Say you now do: SELECT a>'9' FROM t1 Because the literal text '9' is being compared to a column with numeric affinity, it is transformed to a number before the comparison takes place. 10 is greater than 9 so this query returns "1". Exactly when affinities are applied is described in section 3 of the datatype3.html document. Check out the examples in datatype3.html. They should make everything clear. Dan. > -- > View this message in context: > http://www.nabble.com/WHERE-clause-doesn%27t-seem-to-work-right-tp22497543p22498479.html > Sent from the SQLite mailing list archive at Nabble.com. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
