Hello all! I stumbled across this strange bug during Android development on 2.2.1 late last night. Please run the following snippet in SQLite 3.7.2 and 3.6.22 to compare the differences. The comments shows what alterations you can make to make the query return the expected result.
:-David Burström .header on .mode column .echo on -- -- The query below is supposed to return one row, starttime = 3 and endtime = 1. In SQLite 3.622 it works. In SQLite 3.7.2 it returns 0 rows. -- -- if endtime is in a different position in the table, the query works CREATE TABLE interval (endtime INTEGER, entryid INTEGER, starttime INTEGER); CREATE TABLE entry (id INTEGER PRIMARY KEY AUTOINCREMENT); INSERT INTO entry (id) VALUES ( 42); INSERT INTO interval (endtime, entryid, starttime) VALUES (2, 42, 1); -- if the endtime below is greater than or equal to the endtime above, the query works INSERT INTO interval (endtime, entryid, starttime) VALUES (1, 42, 3); -- if endtime is removed from the projection, the query works -- if the LEFT JOIN is changed to INNER JOIN, the query works SELECT starttime, endtime from entry LEFT JOIN interval ON interval.entryid = entry.id GROUP BY entry.id HAVING starttime = MAX(starttime); _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
