[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

2015-02-24 Thread James K. Lowden
On Wed, 18 Feb 2015 08:55:37 +0100 gunnar wrote: instead of > SELECT * > FROM ordercallback > WHERE account=@ACCOUNT >AND cb_seq_num>( > SELECT cb_seq_num > FROM ordercallback > WHERE cb_uuid=@CBUUID); SELECT * FROM ordercallback ocb WHERE account=@ACCOUNT AND exists (

[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

2015-02-18 Thread gunnar
Ah indeed! thanks! On 02/18/2015 12:22 PM, Clemens Ladisch wrote: > gunnar wrote: >> The subquery will always return one result or no result. So I only >> have to UNION it ALL with the SELECT NULL part. Clemens Ladisch wrote: > ... WHERE cb_seq_num > ( > SELECT cb_seq_num

[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

2015-02-18 Thread Clemens Ladisch
gunnar wrote: > The subquery will always return one result or no result. So I only > have to UNION it ALL with the SELECT NULL part. >>> Clemens Ladisch wrote: ... WHERE cb_seq_num > ( SELECT cb_seq_num FROM ordercallback WHERE cb_uuid=@CBUUID

[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

2015-02-18 Thread gunnar
The subquery will always return one result or no result. So I only have to UNION it ALL with the SELECT NULL part. (I still have to refresh my memory how UNION ALL exactly works since I never used it and only know it from college long time ago :) I cannot imagine, but when it incurs a performanc

[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

2015-02-18 Thread Clemens Ladisch
Eduardo Morras wrote: > Clemens Ladisch wrote: >> ... 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

[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

2015-02-18 Thread Eduardo Morras
On Wed, 18 Feb 2015 10:23:16 +0100 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 e

[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

2015-02-18 Thread gunnar
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 I

[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

2015-02-18 Thread Clemens Ladisch
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

[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

2015-02-18 Thread gunnar
Hello, 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 version