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 <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] Re: [sqlite] COALESCE() does not short-circuit sometimes On Wed, Mar 11, 2020 at 12:03 PM Justin Ng <justin.ng.1...@outlook.com> 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
