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). 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, at least to be mysql-compatible. One of my joins should compare a subset that guaranteed to contain non-range value, so WHERE clause safely compared values as integers. After querying in MySql and sqlite I got different results that led to this narrowing. Affinity section mentioned the goal of more compatibility, but without numerical literal being treated exactly like numerical fields the compatibility looks a little bit limited. I can live with that , but I think the datatype doc page can also mention this difference. Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
