Duplicate and Missing results using an IN operator in the WHERE clause

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Duplicate and Missing results using an IN operator in the WHERE clause

Gouranga Gupta
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Duplicate and Missing results using an IN operator in the WHERE clause

Richard Hipp-3
On 5/1/17, Gouranga Gupta <[hidden email]> wrote:
> 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:

The release date for SQLite 3.18.0 is 2017-03-28 and the "fixed" date
on the ticket is 2017-04-24.

Have you tried the "prerelease snapshot" from
https://www.sqlite.org/download.html with a date of 2017-05-02?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users