Hi.
According to Ticket UUID: 61fe97454c00bc4a5f5d826af02161d7df0a40c0 Title "Duplicate result using an IN operator in the WHERE clause" created on 2017-04-24 14:57:43 "Status" is "Closed and "Resolution" is "Fixed". However, using the latest version: sqlite-amalgamation-3180000.zip (and sqlite-amalgamation-3170000.zip) I got duplicates and missing results: Summary: -------------- I got: 2,3,6,7,9,12,13,14,15,16,17,18,21,25,26,26,27,28,29,29,30,31,32,32,33,34,34,35,36,36,37,38,39,39,40,40,41,41,42,42,42,44,45,45,106,118,119,120,120,125,126,127,133,134,136 i.e. duplicates (e.g. 26) and missing ID (e.g. 43). I expected: 2,3,6,7,9,12,13,14,15,16,17,18,21,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,106,118,119,120,125,126,127,133,134,136 i.e. no duplicates (despite normally harmless duplicates in the IN clause) and no missing IDs, all in order. The problem seems to occur where "INTEGER PRIMARY KEY" is used. In a duplicated table without an INTEGER PRIMARY KEY the results are correct. Note, the problem did not occur year(s) ago using sqlite3.c at that time. SQL exposing Error: -------------------------- SELECT ID FROM X WHERE ID IN (106,136,14,16,15,9,133,25,26,27,126,28,31,32,33,34,37,127,42,44,21,45,21,44,42,43,42,125,41,40,39,127,38,127,37,34,33,36,35,32,31,30,29,28,126,120,27,119,118,26,25,133,134,16,6,7,17,16,18,2,3,13,12,15,9,133,25,26,119,120,27,126,28,29,31,32,33,34,35,36,37,127,38,39,40,41,125,42,43,42,44,45,127,39,40); SQL not exposing Error: -------------------------------- SELECT ID FROM Z WHERE ID IN (106,136,14,16,15,9,133,25,26,27,126,28,31,32,33,34,37,127,42,44,21,45,21,44,42,43,42,125,41,40,39,127,38,127,37,34,33,36,35,32,31,30,29,28,126,120,27,119,118,26,25,133,134,16,6,7,17,16,18,2,3,13,12,15,9,133,25,26,119,120,27,126,28,29,31,32,33,34,35,36,37,127,38,39,40,41,125,42,43,42,44,45,127,39,40); Corresponding CREATE SQL: -------------------------------------- BEGIN; CREATE TABLE X (ID INTEGER PRIMARY KEY, Y); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); INSERT INTO X (Y) VALUES (69); CREATE TABLE Z AS SELECT * FROM X; COMMIT; Kind regards Gouranga :-) _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
