On Wed, Jun 9, 2010 at 11:29 AM, Pavel Ivanov <paiva...@gmail.com> wrote:
> > 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. > Close, but not quite the problem. This issue is in range constraints: c2>0 AND c2<'2'. Turns out that if the no affinity transformation is required on the first part of the constraint (c2>0) then none is applied to the second (c2<'2') even if it is required. Hence all of the following work: SELECT * FROM t1 WHERE c1=5 AND c2>'0' AND c2<'2'; SELECT * FROM t1 WHERE c1=5 AND c2>0 AND c2<2; SELECT * FROM t1 WHERE c1=5 AND c2>'0' AND c2<2; Only the one case reported fails: SELECT * FROM t1 WHERE c1=5 AND c2>0 AND c2<'2'; Dan will be checking in a fix shortly. > > 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 > -- --------------------- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
