Richard, what does the explain look like with your code change, please. Doug
> -----Original Message----- > From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> > On Behalf Of Richard Hipp > Sent: Thursday, March 12, 2020 3:09 AM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short- > circuit sometimes > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
