On Fri, Dec 18, 2009 at 07:23:24PM -0700, Valerio Aimale scratched on the wall: > Hello all, > > I've run into an interesting situation; when duplicating parenthesis > around a 'in ()' subquery, only the first row is returned.
> Why only one value returned when parenthesis are duplicated? Wrapping a sub-SELECT in parenthesis turns it into an expression, rather than a result-set. This is done by returning the first value. See the diagram here: http://www.sqlite.org/lang_expr.html In your specific case, the "IN" operator is defined as: <expression> IN ( <select> | <expression-list> ) In other words, it allows either a SELECT -or- one or more expressions. If it is a sub-SELECT that has one column, the IN operator is smart enough to consider the returned column to be an expression set. However, when you wrap the sub-SELECT in parenthesis, it becomes a scalar expression of only one value (the first row), so you're IN test-set has only one value, and returns only one row in the super-SELECT. If you're using a sub-SELECT, the IN operator must see it directly. > Same with triple parenthesis enclosing: An expression in parenthesis is still just an expression. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
