Max Vlasov <max.vla...@gmail.com> wrote: > On Fri, Feb 10, 2012 at 5:45 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > >> Value has TEXT affinity, 5 has none. So 5 is converted to '5', and then >> lexicographic comparisons are performed. It so happens that all strings in >> the Value column lexicographically precede '5'. >> >> If you wanted Value to be treated as an integer and compared numerically, >> why would you declare it as VARCHAR? > > Hmm, I never saw mentioning literal had NONE affinity (select typeof(5) > returns integer).
>From the document you cited, http://www.sqlite.org/datatype3.html : 3.2 Affinity Of Comparison Operand - An expression that is a simple reference to a column value has the same affinity as the column - An expression of the form "CAST(expr AS type)" has an affinity that is the same as a column with a declared type of "type". - *Otherwise, an expression has NONE affinity.* Emphasis mine. > As for the source of the problem, I have a table where cell can contain > values (12) or ranges (12-15) so the type should be non-integer Can't you have two integer columns? A single value could then be represented by having the same value in both columns. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
