My opinion is that the double parentheses should be ignored. I've tested this in other engines like H2, Apache Derby or MySQL and all of them simply ignores the double parentheses and returns the three rows.
On Wed, Mar 11, 2015 at 2:57 PM, Hick Gunter <hick at scigames.at> wrote: > The syntax diagram mandates 1 set of parentheses around the select for the > IN operator. > > Putting a SELECT statement inside parentheses makes it a SCALAR SUBQUERY > that returns (at most) 1 row with 1 column. Any extra data is ignored. > > Works as specified. > > -----Urspr?ngliche Nachricht----- > Von: Federico Fernandez Beltran [mailto:federico.f.b at 47deg.com] > Gesendet: Mittwoch, 11. M?rz 2015 14:36 > An: sqlite-users > Betreff: [sqlite] Unexpected behaviour when use "IN" clause and double > parentheses > > Hi, > > First of all thanks for your effort creating this database engine. > > I've encountered a problem adapting an ORM library to use with SQLite. The > good news is that is easily reproducible with the command line client. > > The problem is when I use double parentheses in a expression with "IN" > clause like this: > > SELECT * FROM tableA WHERE id IN ((SELECT foreignId FROM tableB)); > > The above query return always only one row even if there are more rows > that satisfy the clause. > > The steps to reproduce the problem are: > > sqlite> CREATE TABLE tableA (`id` INTEGER); CREATE TABLE tableB (`id` > sqlite> INTEGER, `foreignId` INTEGER); INSERT INTO tableA VALUES(1); > sqlite> INSERT INTO tableA VALUES(2); INSERT INTO tableA VALUES(3); > sqlite> INSERT INTO tableA VALUES(4); INSERT INTO tableA VALUES(5); > sqlite> INSERT INTO tableB VALUES(1, 1); INSERT INTO tableB VALUES(2, > sqlite> 2); INSERT INTO tableB VALUES(3, 3); SELECT * FROM tableA WHERE > sqlite> id IN (SELECT foreignId FROM tableB); > 1 > 2 > 3 > sqlite> SELECT * FROM tableA WHERE id IN ((SELECT foreignId FROM > sqlite> tableB)); > 1 > sqlite> > > A solution could be not putting this double parentheses but I'm using an > ORM library and I can't change it. On the other hand others database > engines doesn't have this behaviour and the tests pass successfully. > > Hope this helps to identify the problem. > > Best > > -- > Fede Fern?ndez > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___________________________________________ > Gunter Hick > Software Engineer > Scientific Games International GmbH > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: hick at scigames.at > > This communication (including any attachments) is intended for the use of > the intended recipient(s) only and may contain information that is > confidential, privileged or legally protected. Any unauthorized use or > dissemination of this communication is strictly prohibited. If you have > received this communication in error, please immediately notify the sender > by return e-mail message and delete all copies of the original > communication. Thank you for your cooperation. > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Fede Fern?ndez
