Max Vlasov <max.vla...@gmail.com> wrote: > On Fri, Feb 10, 2012 at 4:53 PM, Richard Hipp <d...@sqlite.org> wrote: >> In the statement: >> >> SELECT '25' < 25; >> >> There are no columns, only literals. And hence no affinity is applied. > > So if a string looks like a numeral it should be treated as numeral by > sqlite?
Not unless something else in the expression forces numeric affinity. > The following db and query returns results bigger than 5, is this ok? > > CREATE TABLE [testtable] ( > [Id] INTEGER PRIMARY KEY AUTOINCREMENT, > [value] VARCHAR(200) > ); > INSERT INTO "testtable" VALUES(4,'-3'); > INSERT INTO "testtable" VALUES(5,'-2'); > INSERT INTO "testtable" VALUES(11,'33'); > INSERT INTO "testtable" VALUES(12,'44'); > > SELECT value from testtable WHERE Value <= 5 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? -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
