Exactly what I gained from the EXPLAIN output. The SQL "compiler" is extracting the constant expression ABS(...) and evaluating it in the program prolog (where schema is checked and locks taken). See instructions 11 and 12
asql> explain select coalesce((SELECT 'hello'),ABS(-9223372036854775808)); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 11 0 00 Start at 11 1 Once 0 6 0 00 2 Null 0 2 2 00 r[2..2]=NULL; Init subquery result 3 Integer 1 3 0 00 r[3]=1; LIMIT counter 4 String8 0 2 0 hello 00 r[2]='hello' 5 DecrJumpZero 3 6 0 00 if (--r[3])==0 goto 6 6 SCopy 2 1 0 00 r[1]=r[2] 7 NotNull 1 9 0 00 if r[1]!=NULL goto 9 8 SCopy 4 1 0 00 r[1]=r[4] 9 ResultRow 1 1 0 00 output=r[1] 10 Halt 0 0 0 00 11 Int64 0 5 0 -9223372036854775808 00 r[5]=-9223372036854775808 12 Function0 1 5 4 abs(1) 01 r[4]=func(r[5]) 13 Goto 0 1 0 00 -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jay Kreibich Gesendet: Mittwoch, 11. März 2020 20:53 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Cc: 38866424f8f6fc429174ff1ab2355...@mail.dessus.com Betreff: [EXTERNAL] Re: [sqlite] COALESCE() does not short-circuit sometimes > On Mar 11, 2020, at 2:16 PM, Justin Ng <justin.ng.1...@outlook.com> 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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
