Thanks a lot Clemens! I will use your suggestion and add the 'UNION'.
Then I only need to make sure that the comparison "[some number] > NULL" in my WHERE clause always return an empty set. Thanks again! Gunnar Gunnar Harms T +31 (0)20 53 53 487 F +31 (0)20 42 08 852 I www.hiqinvest.nl HiQ Invest Rembrandt Tower ? 9th floor Amstelplein 1 1096 HA Amsterdam On 02/18/2015 10:23 AM, Clemens Ladisch 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 > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
