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
