Hi sqlite mailing list, I'm experiencing unexpected ordering with a WHERE clause and an index on the filtered column. At first I'll state the reproduction steps:
DROP TABLE IF EXISTS t1; CREATE TABLE t1(x, y); CREATE INDEX ix_t1_y ON t1(y); INSERT INTO t1(x,y) VALUES(1,'AB'); INSERT INTO t1(x,y) VALUES(2,'CD'); INSERT INTO t1(x,y) VALUES(3,'EF'); INSERT INTO t1(x,y) VALUES(4,'AB'); INSERT INTO t1(x,y) VALUES(5,'CD'); INSERT INTO t1(x,y) VALUES(6,'EF'); SELECT * FROM t1 WHERE (y = 'AB') OR (y = 'CD'); The result consists of 4 rows, in the following order: "1" "AB" "4" "AB" "2" "CD" "5" "CD" Especially you can see that the result is sorted by column 'y'. This is reproducible and I can't understand that. When you remove the index ix_t1_y, the results are more comprehensible: "1" "AB" "2" "CD" "4" "AB" "5" "CD" So the sorting is connected to the existence of the index, I guess. Is this expected behavior or a bug? Environment: For simplification of my example I used DB Browser for SQLite (http://sqlitebrowser.org/, Version 3.7.0), which internally uses SQLite 3.8.10.2. Kind regards, Oliver
