Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Jay Kreibich
> On Mar 11, 2020, at 2:16 PM, Justin Ng wrote: > > They generally do short-circuit but there are edge cases where they don't. It > isn't entirely intuitive to me what the conditions are, though. > "ABS(-9223372036854775808)" is a constant expression, and as such, it makes sense that it is

Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Justin Ng
> It is not a "hack" because it does not work. It is what is called a "failed > attempt" at a hack. Yeah, the ABS() hack does not work. Which led me to use `(SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL))` which **does** work. > However, your assessment that

Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Justin Ng
> It is not a "hack" because it does not work. It is what is called a "failed > attempt" at a hack. Yeah, the ABS() hack does not work. Which led me to use `(SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL))` which **does** work. > However, your assessment that

Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Keith Medcalf
On Wednesday, 11 March, 2020 09:24, Justin Ng wrote: >Sometimes, when querying data, rather than letting NULLs propagate, >it might be better to throw an error if NULLs are not expected from an >expression. >The presence of NULLs might indicate an error in logic, data, or both. Yes, it very

Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Justin Ng
> Why do you think that that it should not evaluate ABS? It is there and you > asked for it. I believe it's a good idea to say, "hey, the amount you placed > here is out of boundary, think about what you are doing here." IMO, of > course. Thanks. > > josé Sometimes, when querying data,

Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Jose Isaias Cabrera
Justin Ng, on Wednesday, March 11, 2020 07:03 AM, wrote... > > This happens in SQLite 3.28 and 3.31. > > Consider the following queries, > > -- Query 1 > SELECT > COALESCE( > NULL, > (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT > NULL)) > ); On 3.24.0, this worked

Re: [sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Dominique Devienne
sql/issues/233 SQLite COALESCE() does not short-circuit sometimes #233 _______ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] COALESCE() does not short-circuit sometimes

2020-03-11 Thread Justin Ng
This happens in SQLite 3.28 and 3.31. Consider the following queries, -- Query 1 SELECT COALESCE( NULL, (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL)) ); -- Query 2 SELECT COALESCE( (SELECT 'hello'), (SELECT SUM(9223372036854775807) FROM (SELECT