> Perhaps this is the way it was supposed to work. But presence of index > does affect something, so I assumed it somehow messes affinity (what else?).
Comparison of numbers and strings without affinities is supposed to work this way, yes. In this case though I think you're right - comparison on second column in index forgets to apply affinity rules when there's equality condition on the first column in index. SQLite version 3.6.23.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE t (c1 INTEGER, c2 INTEGER); sqlite> INSERT INTO t (c1, c2) VALUES (5, 1); sqlite> INSERT INTO t (c1, c2) VALUES (5, 5); sqlite> INSERT INTO t (c1, c2) VALUES (5, -1); sqlite> CREATE INDEX t_c1_c2 ON t (c1, c2); sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2'; 5|1 5|5 sqlite> Pavel On Wed, Jun 9, 2010 at 11:18 AM, Tomash Brechko <tomash.brec...@gmail.com> wrote: > 2010/6/9 Pavel Ivanov <paiva...@gmail.com> > >> You can see that these 2 cases compare the same way. They both show >> that string is always greater than number and thus '11' > 2 and '2' > >> 11. And no affinity rules are applicable here because you use >> constants which don't have any affinity. > > > Perhaps this is the way it was supposed to work. But presence of index > does affect something, so I assumed it somehow messes affinity (what else?). > _______________________________________________ > 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
