(Apologies if this gets posted twice.) The following ORDER BY query returns between 0 and 2 duplicates of each row, for 1 to 3 copies total, in 3.8.9 for Linux (CentOS). Otherwise, the returned rows are complete and correctly ordered. This database worked fine under 3.8.6. I can test other OSes or versions between 3.8.6 and 3.8.9 if requested.
Table ICSD has 50 columns. I can probably produce a copy if requested. The BIN_DATA column holds strings 150 characters long. EL_COUNT and COLL_CODE contain integers. If I select a column other than BIN_DATA, or if I limit the ORDER BY clause to a single term, then the query works. The query fails the same way when 3.8.9 is accessed several different ways: sqlite-amalgamation-3080900.zip, sqlite-autoconf-3080900.tar.gz, and also when the Sqlite library is accessed via the Ruby Sqlite gem. -- works, returns 156679 SELECT COUNT(*) FROM ICSD; -- fails, returning 322747 rows SELECT BIN_DATA FROM ICSD ORDER BY EL_COUNT, COLL_CODE; -- also fails. Including rowid to show that it really is -- the exact same row that gets returned twice. SELECT ROWID, BIN_DATA FROM ICSD ORDER BY EL_COUNT, COLL_CODE; -- works, returns 156679 rows SELECT ROWID, SGR_NUM FROM ICSD ORDER BY EL_COUNT, COLL_CODE; -- works, returns 156679 rows SELECT BIN_DATA FROM ICSD ORDER BY EL_COUNT;
