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 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...
If you expect the DB layer to work exactly like postgres, why are you not
simply using postgres? It costs the same. :)
Ok, enough with the tongue-in-cheek pokes. On a more serious note, may I ask what you had hoped to achieve, and why do you use the
double bracketing, or how do you mean for it to be different to single bracket sets, or (if I read the post right) why did you use
double bracketing expecting it to have the same effect as single bracketing? If their is some RDBMs advantage to one of the ways
that has eluded me till now, I would genuinely be interested to know.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
