On 3/12/20, Hick Gunter <h...@scigames.at> wrote: > 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
Exactly. SQLite tries to factor out constant expressions into the prologue so that they are only evaluated once, rather than once for each row. This is a performance optimization. A traditional compiler like gcc or clang would do an enormous amount of code movement, similar to this, as well as other simplifications, to make the code run faster, especially with options like -O2. But the compiler for SQLite does not have that luxury. Depending on the query, the time spent compiling the query into byte code can be a significant fraction of the total running time. Hence, the compiler needs to be very fast. This is an ongoing design struggle with SQLite: how many CPU cycles do we burn trying to optimize the bytecode with the hopes of making up those lost CPU cycles with a shorter run-time? Optimization is also constrained by the desire to keep the SQLite code small. Hence, the optimizations applied by the SQLite byte-code compiler are relatively simple, so that they can be implemented with few CPU cycles and with few bytes of machine code. Returning to the original discussion: The underlying problem is that the constant expressions that get moved into the prologue, if they involve function calls, might throw an exception. That is what is happening with abs(-9223372036854775808). And that exception prevents the main body of the code from running, even if the offending expression was never actually going to be used. The solution is to not factor out expressions that use functions, but instead use the OP_Once opcode (https://www.sqlite.org/opcode.html#Once) to prevent those expressions from being evaluated more than once. This seems to make Coalesce (and CASE...END) short-circuit again. And it also fixes ticket https://www.sqlite.org/src/info/3c9eadd2a6ba0aa5 That change is implemented by check-in https://www.sqlite.org/src/info/c5f96a085db9688a > > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
