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.
