This is a bit weird...it appears it's just the = operator causing this... sqlite> select * from t where c1>=5 and c2>0 and c2<='2'; sqlite> select * from t where c1<=5 and c2>0 and c2<='2'; sqlite> select * from t where c1=5 and c2>0 and c2<='2'; 5|5 sqlite> drop index t_c1_c2; sqlite> select * from t where c1<=5 and c2>0 and c2<='2'; sqlite> select * from t where c1>=5 and c2>0 and c2<='2'; sqlite> select * from t where c1=5 and c2>0 and c2<='2'; sqlite> So the index is messing up the '=' operator. Michael D. Black Senior Scientist Northrop Grumman Mission Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Tomash Brechko Sent: Wed 6/9/2010 8:52 AM To: sqlite-users@sqlite.org Subject: [sqlite] [BUG] Adding an index changes query result Hello, With SQLite 3.6.23.1 I see the following: $ ./sqlite3 /tmp/a.sqlite 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, 5); sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2'; sqlite> CREATE INDEX t_c1_c2 ON t (c1, c2); sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2'; 5|5 Note that the same query may or may not return the result based on whether there's an index present. Apparently on second invocation the comparison is 5 <= '2', and numbers compare before strings. It's not clear to me which result should be considered correct though: sqlite> select 11 > 2; 1 sqlite> select '11' > '2'; 0 sqlite> select '11' > 2; 1 sqlite> select 11 > '2'; 0 It seems that last two cases should compare the same way, no matter what the actual affinity rules are. Regards, -- Tomash Brechko _______________________________________________ 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
