Hi, SQL in general doe not make any guarantees about result ordering if you do not sort them. Use ORDER BY if you need to sort the results somehow. Otherwise expect nothing.
-- Gruesse, Jakub -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Oliver Kock Sent: Donnerstag, 10. September 2015 14:40 To: sqlite-users at mailinglists.sqlite.org Subject: [sqlite] Unexpected ordering when index exists 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 _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
