Gesendet: Mittwoch, 11. Juni 2014 um 10:29 Uhr Von: RSmith <rsm...@rsweb.co.za> An: sqlite-users@sqlite.org Betreff: Re: [sqlite] BUG with unnecessary brackets in queries with IN ( SELECT .. ) >On 2014/06/11 07:48, be...@web.de wrote: >>>> Simple example : >>>> >>>> Working (correct result) >>>> select id from tbl where id in (select id from tbl) >>>> >>>> NOT working (incorrect result) : >>>> select id from tbl where id in ( ( select id from tbl) ) >>>> >>> In the first query, SQLite looks for values of "id" that are in the >>> subquery. >>> >>> In the second query, the subquery is evaluated as an expression. That >>> means that only the first row of the result set is used. And the >>> right-hand side of the IN clause is a list of expressions instead of a >>> subquery. >>> >>> So the two queries mean different things. >>> >>> >>> -- >>> D. Richard Hipp >>> d...@sqlite.org >>> >> >> Just a bit surprising to find that postgres delivers the same answer to both >> queries, after having seen your presentation on PGCon. > >Postgres is forgiving you for a silly mistake because it has ample code-space >for additional features such as protecting users from >a common mistake. It is not SQL standard behaviour (not that I am a disciple >of the standard btw.) but it seems like a sensible >allowance, and the folks over at postgres are known for sensibility. (Have you >tried that in strict mode?) - it is however in no way >translating into a standard just because they did it - and may I add - >although the "role-model" notion was clear, nothing in the >PGCon keynote indicated that SQLite strives for copying their exact syntax. >
I wasn't aware of that behaviour being standard - even though I suspected so the way Dr. Hipp answered. >I could argue that the function UPDATE "myTable" SET IntField++ WHERE >AddMarker=1; should in fact increase that field's value by 1 >for each record, because, y'know, it works in C and Java and stuff... Not quite so. But I'd expect to get the same answer to apparently easy questions from different RDBMs (same syntax - same result?). > >If you expect the DB layer to work exactly like postgres, why are you not >simply using postgres? It costs the same. :) Personally I use both and I like them both - but I prefer SQLite for its overall 'simplicity'. But actually I'm not the original author of the mail - just inserted the wrong subject (stupid web mailer - stupid me!). _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
