Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
On 3/12/20, Doug wrote: > > Something triggered me when I looked at the generated code: you use the > contents of register 2 for the constant value each time through the loop. > What if the select looks like this, with more than one function call in the > coalesce? Do you handle it properly? > > SELECT coalesce(a, abs(0), abs(1), abs(2)) FROM t1; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 23000 Start at 23 1 OpenRead 0 2 0 1 00 root=2 iDb=0; t1 2 Explain2 0 0 SCAN TABLE t1 00 3 Rewind 0 22000 4 Column 0 0 100 r[1]=t1.a 5 NotNull1 20000 if r[1]!=NULL goto 20 6 Once 0 9 000 7 Integer0 3 000 r[3]=0 8 Function 1 3 2 abs(1) 00 r[2]=func(r[3]) 9 SCopy 2 1 000 r[1]=r[2] 10 NotNull1 20000 if r[1]!=NULL goto 20 11 Once 0 14000 12 Integer1 5 000 r[5]=1 13 Function 1 5 4 abs(1) 00 r[4]=func(r[5]) 14 SCopy 4 1 000 r[1]=r[4] 15 NotNull1 20000 if r[1]!=NULL goto 20 16 Once 0 19000 17 Integer2 7 000 r[7]=2 18 Function 1 7 6 abs(1) 00 r[6]=func(r[7]) 19 SCopy 6 1 000 r[1]=r[6] 20 ResultRow 1 1 000 output=r[1] 21Next 0 4 001 22Halt 0 0 000 23Transaction0 0 1 0 01 usesStmtJournal=0 24Goto 0 1 000 -- 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
Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
Thanks, Richard. Something triggered me when I looked at the generated code: you use the contents of register 2 for the constant value each time through the loop. What if the select looks like this, with more than one function call in the coalesce? Do you handle it properly? SELECT coalesce(a, abs(0), abs(1), abs(2)) FROM t1; Just curious... Doug > -Original Message- > From: sqlite-users > On Behalf Of Richard Hipp > Sent: Thursday, March 12, 2020 7:41 AM > To: SQLite mailing list > Subject: Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short- > circuit sometimes > > On 3/12/20, Doug wrote: > > Richard, what does the explain look like with your code change, > please. > > Test case: > > CREATE TABLE t1(a); > explain SELECT coalesce(a, abs(-9223372036854775808)) FROM t1; > > Before the change: > > addr opcode p1p2p3p4 p5 comment > - - -- > - > 0 Init 0 10000 Start at > 10 > 1 OpenRead 0 2 0 1 00 root=2 > iDb=0; t1 > 2 Explain2 0 0 SCAN TABLE t1 00 > 3 Rewind 0 9 000 > 4 Column 0 0 100 > r[1]=t1.a > 5 NotNull1 7 000 if > r[1]!=NULL goto 7 > 6 SCopy 2 1 000 > r[1]=r[2] > 7 ResultRow 1 1 000 > output=r[1] > 8 Next 0 4 001 > 9 Halt 0 0 000 > 10Transaction0 0 1 0 01 > usesStmtJournal=0 > 11Int64 0 3 0 -9223372036854775808 00 > r[3]=-9223372036854775808 > 12Function 1 3 2 abs(1) 00 > r[2]=func(r[3]) > 13Goto 0 1 000 > > Notice that the abs() function is invoked in the "prologue" code. > The > prologue begins on instruction 10 and continues through the Goto > at > instruction 13. > > After the change: > > addr opcode p1p2p3p4 p5 comment > - - -- > - > 0 Init 0 13000 Start at > 13 > 1 OpenRead 0 2 0 1 00 root=2 > iDb=0; t1 > 2 Explain2 0 0 SCAN TABLE t1 00 > 3 Rewind 0 12000 > 4 Column 0 0 100 > r[1]=t1.a > 5 NotNull1 10000 if > r[1]!=NULL goto 10 > 6 Once 0 9 000 > 7 Int64 0 3 0 -9223372036854775808 00 > r[3]=-9223372036854775808 > 8 Function 1 3 2 abs(1) 00 > r[2]=func(r[3]) > 9 SCopy 2 1 000 > r[1]=r[2] > 10 ResultRow 1 1 000 > output=r[1] > 11Next 0 4 001 > 12Halt 0 0 000 > 13Transaction0 0 1 0 01 > usesStmtJournal=0 > 14Goto 0 1 000 > > Now the prologue is just instructions 13 and 14 and omits the > abs() > function. The abs() function is now computed on instructions 7 > and 8, > but those instructions only run one time due to the "Once" opcode > on > instruction 6. > > -- > 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
Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
On 3/12/20, Doug wrote: > Richard, what does the explain look like with your code change, please. Test case: CREATE TABLE t1(a); explain SELECT coalesce(a, abs(-9223372036854775808)) FROM t1; Before the change: addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 10000 Start at 10 1 OpenRead 0 2 0 1 00 root=2 iDb=0; t1 2 Explain2 0 0 SCAN TABLE t1 00 3 Rewind 0 9 000 4 Column 0 0 100 r[1]=t1.a 5 NotNull1 7 000 if r[1]!=NULL goto 7 6 SCopy 2 1 000 r[1]=r[2] 7 ResultRow 1 1 000 output=r[1] 8 Next 0 4 001 9 Halt 0 0 000 10Transaction0 0 1 0 01 usesStmtJournal=0 11Int64 0 3 0 -9223372036854775808 00 r[3]=-9223372036854775808 12Function 1 3 2 abs(1) 00 r[2]=func(r[3]) 13Goto 0 1 000 Notice that the abs() function is invoked in the "prologue" code. The prologue begins on instruction 10 and continues through the Goto at instruction 13. After the change: addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 13000 Start at 13 1 OpenRead 0 2 0 1 00 root=2 iDb=0; t1 2 Explain2 0 0 SCAN TABLE t1 00 3 Rewind 0 12000 4 Column 0 0 100 r[1]=t1.a 5 NotNull1 10000 if r[1]!=NULL goto 10 6 Once 0 9 000 7 Int64 0 3 0 -9223372036854775808 00 r[3]=-9223372036854775808 8 Function 1 3 2 abs(1) 00 r[2]=func(r[3]) 9 SCopy 2 1 000 r[1]=r[2] 10 ResultRow 1 1 000 output=r[1] 11Next 0 4 001 12Halt 0 0 000 13Transaction0 0 1 0 01 usesStmtJournal=0 14Goto 0 1 000 Now the prologue is just instructions 13 and 14 and omits the abs() function. The abs() function is now computed on instructions 7 and 8, but those instructions only run one time due to the "Once" opcode on instruction 6. -- 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
Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
Richard, what does the explain look like with your code change, please. Doug > -Original Message- > From: sqlite-users > On Behalf Of Richard Hipp > Sent: Thursday, March 12, 2020 3:09 AM > To: SQLite mailing list > Subject: Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short- > circuit sometimes > > On 3/12/20, Hick Gunter 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 p1p2p3p4 p5 > comment > > - - -- -- > --- > > 0 Init 0 11000 Start > at 11 > > 1 Once 0 6 000 > > 2 Null 0 2 200 > r[2..2]=NULL; Init > > subquery result > > 3 Integer1 3 000 > r[3]=1; LIMIT > > counter > > 4 String80 2 0 hello 00 > r[2]='hello' > > 5 DecrJumpZero 3 6 000 if (-- > r[3])==0 > > goto 6 > > 6 SCopy 2 1 000 > r[1]=r[2] > > 7 NotNull1 9 000 if > r[1]!=NULL goto > > 9 > > 8 SCopy 4 1 000 > r[1]=r[4] > > 9 ResultRow 1 1 000 > output=r[1] > > 10Halt 0 0 000 > > 11Int64 0 5 0 -9223372036854775808 00 > > r[5]=-9223372036854775808 > > 12Function0 1 5 4 abs(1) 01 > r[4]=func(r[5]) > > 13Goto 0 1 000 > > > > -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 > > 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 > > >> wrote: > >> > >> They generally do short-circuit but there are edge cases where > they don't. &
Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
On 3/12/20, Hick Gunter 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 p1p2p3p4 p5 comment > - - -- - > 0 Init 0 11000 Start at 11 > 1 Once 0 6 000 > 2 Null 0 2 200 r[2..2]=NULL; Init > subquery result > 3 Integer1 3 000 r[3]=1; LIMIT > counter > 4 String80 2 0 hello 00 r[2]='hello' > 5 DecrJumpZero 3 6 000 if (--r[3])==0 > goto 6 > 6 SCopy 2 1 000 r[1]=r[2] > 7 NotNull1 9 000 if r[1]!=NULL goto > 9 > 8 SCopy 4 1 000 r[1]=r[4] > 9 ResultRow 1 1 000 output=r[1] > 10Halt 0 0 000 > 11Int64 0 5 0 -9223372036854775808 00 > r[5]=-9223372036854775808 > 12Function0 1 5 4 abs(1) 01 r[4]=func(r[5]) > 13Goto 0 1 000 > > -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 > 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 >> 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 | > Klitschga
Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
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 p1p2p3p4 p5 comment - - -- - 0 Init 0 11000 Start at 11 1 Once 0 6 000 2 Null 0 2 200 r[2..2]=NULL; Init subquery result 3 Integer1 3 000 r[3]=1; LIMIT counter 4 String80 2 0 hello 00 r[2]='hello' 5 DecrJumpZero 3 6 000 if (--r[3])==0 goto 6 6 SCopy 2 1 000 r[1]=r[2] 7 NotNull1 9 000 if r[1]!=NULL goto 9 8 SCopy 4 1 000 r[1]=r[4] 9 ResultRow 1 1 000 output=r[1] 10Halt 0 0 000 11Int64 0 5 0 -9223372036854775808 00 r[5]=-9223372036854775808 12Function0 1 5 4 abs(1) 01 r[4]=func(r[5]) 13Goto 0 1 000 -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 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 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
Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
> I have a patch to fix the problem on a branch > (https://www.sqlite.org/src/timeline?r=do-not-factor-functions) which > you can experiment with. More changes and analysis are needed prior to > landing on trunk. I cannot guarantee that such a landing will in fact > occur, though it seems more likely than not at this point. I remember reporting a bug about COALESCE((... LIMIT 0), x) and you fixed it the very next day. I forgot to thank you for that! Thanks for the quick work, as always! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
I recently tried using Coalesce() to check existence of a column name. SELECT DISTINCT COALESCE((SELECT 'AColumnThatDoesNotExist' FROM TD), -999) FROM TD; But, the SQL error code dominates? "no such column: AColumnThatDoesNotExist" Had to resort to: SELECT name FROM pragma_table_info('TD') WHERE name = 'AColumnThatDoesNotExist'; And oddly, pragma_table_info() fails below if the Table TD is unadorned? SELECT name FROM pragma_table_info(TD) WHERE name = 'AColumnThatDoesNotExist'; On Wed, Mar 11, 2020 at 2:20 PM Richard Hipp wrote: > On 3/11/20, Hick Gunter wrote: > > While ORACLE does state that COALESCE will short circuit, > > A similar problem was reported on a ticket here: > https://www.sqlite.org/src/tktview?name=3c9eadd2a6 (The problem > reported on that ticket might not seem to be the same at first glance, > but deep down they are both the same issue.) > > I have a patch to fix the problem on a branch > (https://www.sqlite.org/src/timeline?r=do-not-factor-functions) which > you can experiment with. More changes and analysis are needed prior to > landing on trunk. I cannot guarantee that such a landing will in fact > occur, though it seems more likely than not at this point. > > -- > 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
Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
On 3/11/20, Hick Gunter wrote: > While ORACLE does state that COALESCE will short circuit, A similar problem was reported on a ticket here: https://www.sqlite.org/src/tktview?name=3c9eadd2a6 (The problem reported on that ticket might not seem to be the same at first glance, but deep down they are both the same issue.) I have a patch to fix the problem on a branch (https://www.sqlite.org/src/timeline?r=do-not-factor-functions) which you can experiment with. More changes and analysis are needed prior to landing on trunk. I cannot guarantee that such a landing will in fact occur, though it seems more likely than not at this point. -- 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
Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
While ORACLE does state that COALESCE will short circuit, SQLite does not. May I suggest implementing your own user defined function to do this instead. void THROW_IF_NULL( sqlite3_context *ctx, int argc, sqlite3_value**argv) { int ii; for( ii == 0; ii < argc; ii++) { if (sqlite3_value_type(argv[ii]) != SQLITE_NULL) { sqlite3_result_value(ctx,argv[ii])); return; } } sqlite3_result_error(ctx,"NULL or empty", SQLITE_TRANSIENT); } -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Justin Ng Gesendet: Mittwoch, 11. März 2020 16:24 An: sqlite-users@mailinglists.sqlite.org Cc: vi1p195mb06545f03a24d50dd2785ac2bde...@vi1p195mb0654.eurp195.prod.outlook.com Betreff: [EXTERNAL] 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 ___ 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
Re: [sqlite] [EXTERNAL] Re: COALESCE() does not short-circuit sometimes
It is possible to infer, from the EXPLAIN output, that the SQLite program generator attempts to isolate constant expressions and evaluates them first, before it enters the COALESCE loop. From my experience in reading SQL Programs, the general structure is GOTO INIT START: - load constant values - load paramters - open tables LOOP: - create next result row IF end-of-results THEN EXIT ELSE GOTO LOOP INIT: - check schema version - take required locks - evaluate constant expressions GOTO START Which is quite nifty, as it allows stuff unknown until the end of parsing to be appended to the program -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Mittwoch, 11. März 2020 13:19 An: SQLite mailing list Betreff: [EXTERNAL] 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 ___ 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