On Wed, 18 Feb 2015 10:23:16 +0100 Clemens Ladisch <clemens at ladisch.de> wrote:
> gunnar wrote: > > When I execute a query that has a subquery behind a comparison > > operator in its where-clause and the subquery returns an empty > > result, then the result of the complete query is exactly how I want > > it: also an empty result. Can I assume that this behaviour will > > stay the same in next versions of sqlite? > > > > SELECT * > > FROM ordercallback > > WHERE account=@ACCOUNT > > AND cb_seq_num>( > > SELECT cb_seq_num > > FROM ordercallback > > WHERE cb_uuid=@CBUUID); > > AFAIK the SQL standard requires such a subquery to return exactly one > result. > > SQLite allows any number of results; if there are more than one, it > uses the first one; if there is none, it uses NULL instead. This > behaviour is not expected to change because it would break too many > programs that already rely on it. > > If you want to make this more compatible with other databases, handle > these cases explicitly: > > ... WHERE cb_seq_num > ( > SELECT cb_seq_num > FROM ordercallback > WHERE cb_uuid=@CBUUID > UNION ALL > SELECT NULL -- at least one result > LIMIT 1) -- at most one result Shouldn't add an ORDER BY cb_seq_num to get the lower one? ... WHERE cb_uuid=@CBUUID ORDER BY cb_seq_num ASC UNION ALL ... > > > Regards, > Clemens --- --- Eduardo Morras <emorrasg at yahoo.es>
