This is probably a bug in SQL parser, that doesn't reduced to one parenthesis, causing the side effect in VDBE engine, that doesn't returned all ID´s to IN
evaluator in first select. This should be easily reproduced, but the fix you must wait for Richard or other that have knowledge on VDBE instruction debugging. I've tested the same thing on SQL Server 2008, correct results appeared: --------------------------- create database x go use x go create table test (id int not null, primary key(id)) go insert into test values (1) insert into test values (2) insert into test values (3) insert into test values (4) insert into test values (5) insert into test values (6) insert into test values (7) insert into test values (8) insert into test values (9) insert into test values (10) go select id from test where id in (select id from test where id > 5) go id ----------- 6 7 8 9 10 (5 row(s) affected) select id from test where id in (select id from test where (id > 5)) go id ----------- 6 7 8 9 10 (5 row(s) affected) select id from test where id in ((select id from test where (id > 5))) go id ----------- 6 7 8 9 10 (5 row(s) affected) ---------------- []'s -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Valerio Aimale Sent: sábado, 19 de dezembro de 2009 00:23 To: sqlite-users@sqlite.org Subject: [sqlite] is this a bug? Hello all, I've run into an interesting situation; when duplicating parenthesis around a 'in ()' subquery, only the first row is returned. This is not my real-life query, but a test that replicates the problem. Thanks, Valerio $ sqlite3 --version 3.6.16 prepare some dummy data: create table test ( id INT ); insert into test VALUES(1); insert into test VALUES(2); insert into test VALUES(3); insert into test VALUES(4); insert into test VALUES(5); insert into test VALUES(6); insert into test VALUES(7); insert into test VALUES(8); insert into test VALUES(9); insert into test VALUES(10); sqlite> select id from test where (id > 5); 6 7 8 9 10 [Good] sqlite> select id from test where id in (select id from test where (id > 5)); 6 7 8 9 10 [Still Good] Now let's duplicate parenthesis around the subquery: sqlite> select id from test where id in ((select id from test where (id > 5))); 6 Why only one value returned when parenthesis are duplicated? Same with triple parenthesis enclosing: sqlite> select id from test where id in (((select id from test where (id > 5)))); 6 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
