Re: [sqlite] COALESCE() does not short-circuit sometimes
> 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 evaluate during the parse/prepare phase of the processing, not the execution. There are similar problems in more traditional languages (especially scripting languages) that attempt to optimize out or pre-compute constant expressions. If that’s the case, then the issue is not so much that the COALESCE() is failing to short-circuit, but rather than the SQL statement failing to “compiling” an invalid statement. If you’re doing this in code as separate prepare/step/finalize, it would be interesting to see where it fails. My guess is prepare, not step. -j ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COALESCE() does not short-circuit sometimes
> 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 coalesce is not "short-circuiting" is incorrect. > If x is not null, then x is returned and the constant expression is not > returned. > If the constant expression were a correlated expression, then it would not be > evaluated in the candidate context. > If x were null and the expression was not constant (and thus required > evaluation) > then it would be evaluated within the current context and its result returned. > > The documentation DOES NOT say that the arguments to coalesce will not be > evaluated, merely that the first one that is not-null will be returned. > > https://sqlite.org/lang_corefunc.html#coalesce You're right that the documentation doesn't say so. I did assume COALESCE() would always short-circuit because the SQL standard says COALESCE() is equivalent to CASE expressions. And I was pretty sure CASE expressions should always short-circuit. However, turns out, CASE expressions don't always short-circuit, either. 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. So, I guess I learned something new today. > > Something like THROW_IF_NULL(x) > > You could certainly write a function that did that. It would certainly be > better than a "hack" that does not even work. I'd rather keep the hack that does work than use a user-defined function, if I can help it. I feel like user-defined functions should be a last resort. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COALESCE() does not short-circuit sometimes
> 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 coalesce is not "short-circuiting" is incorrect. > If x is not null, then x is returned and the constant expression is not > returned. > If the constant expression were a correlated expression, then it would not be > evaluated in the candidate context. > If x were null and the expression was not constant (and thus required > evaluation) > then it would be evaluated within the current context and its result returned. > > The documentation DOES NOT say that the arguments to coalesce will not be > evaluated, merely that the first one that is not-null will be returned. > > https://sqlite.org/lang_corefunc.html#coalesce You're right that the documentation doesn't say so. I did assume COALESCE() would always short-circuit because the SQL standard says COALESCE() is equivalent to CASE expressions. And I was pretty sure CASE expressions should always short-circuit. However, turns out, CASE expressions don't always short-circuit, either. 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. So, I guess I learned something new today. > > Something like THROW_IF_NULL(x) > > You could certainly write a function that did that. It would certainly be > better than a "hack" that does not even work. I'd rather keep the hack that does work than use a user-defined function, if I can help it. I feel like user-defined functions should be a last resort. Thanks for your input, anyway. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COALESCE() does not short-circuit sometimes
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 well might. I believe there is a term for a programmer that uses sentinels and then forgets to handle them. There is even a diagnostic code for it. Some people offend themselves when they see the code, however, because it lets their secret out of the bag. >So, the COALESCE(x, ABS(-9223372036854775808)) construct is a hack to >sanity-check queries. It is not a "hack" because it does not work. It is what is called a "failed attempt" at a hack. However, your assessment that coalesce is not "short-circuiting" is incorrect. If x is not null, then x is returned and the constant expression is not returned. If the constant expression were a correlated expression, then it would not be evaluated in the candidate context. If x were null and the expression was not constant (and thus required evaluation) then it would be evaluated within the current context and its result returned. The documentation DOES NOT say that the arguments to coalesce will not be evaluated, merely that the first one that is not-null will be returned. https://sqlite.org/lang_corefunc.html#coalesce >Something like THROW_IF_NULL(x) You could certainly write a function that did that. It would certainly be better than a "hack" that does not even work. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COALESCE() does not short-circuit sometimes
> 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, 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. So, the COALESCE(x, ABS(-9223372036854775808)) construct is a hack to sanity-check queries. Something like THROW_IF_NULL(x) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COALESCE() does not short-circuit sometimes
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 correctly: 8:32:20.56>sqlite3 SQLite version 3.24.0 2018-06-04 19:24:41 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT ...> COALESCE( ...> NULL, ...> (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL)) ...> ); Error: integer overflow > -- Query 2 > SELECT > COALESCE( > (SELECT 'hello'), > (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT > NULL)) > ); Also, on 3.24.0. this worked correctly: sqlite> SELECT ...> COALESCE( ...> (SELECT 'hello'), ...> (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL)) ...> ); hello > -- Query 3 > SELECT > COALESCE( > (SELECT 'hello'), > ABS(-9223372036854775808) > ); This one, above, was bad at 3.24.0 also. sqlite> SELECT ...> COALESCE( ...> (SELECT 'hello'), ...> ABS(-9223372036854775808) ...> ); Error: integer overflow > Query 1 should throw `integer overflow`. > Query 2 should return 'hello'. > Query 3 should return 'hello'. > > However, Query 3 throws `integer overflow`. It should short-circuit and > not evaluate ABS() 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é ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COALESCE() does not short-circuit sometimes
On Wed, Mar 11, 2020 at 12:03 PM Justin Ng wrote: > -- Query 3 > SELECT > COALESCE( > (SELECT 'hello'), > ABS(-9223372036854775808) > ); > [...]. It should short-circuit and not evaluate ABS() Interestingly, found this as well: https://github.com/AnyhowStep/tsql/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
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 NULL UNION ALL SELECT NULL)) ); -- Query 3 SELECT COALESCE( (SELECT 'hello'), ABS(-9223372036854775808) ); Query 1 should throw `integer overflow`. Query 2 should return 'hello'. Query 3 should return 'hello'. However, Query 3 throws `integer overflow`. It should short-circuit and not evaluate ABS() ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
