SQLite COALESCE() does not short-circuit sometimes

[AnyhowStep]

This will short-circuit.

SELECT (
  'hello' IS COALESCE(
    (
      SELECT 'hello' AS "$aliased--value" 
      UNION 
      SELECT 'hello' AS "$aliased--value" 
      LIMIT 1 OFFSET 0
    ), 
    -- ABS(-9223372036854775808)
    (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL))
  )
) AS "$aliased--value" LIMIT 2 OFFSET 0

This will not,

SELECT (
  'hello' IS COALESCE(
    (
      SELECT 'hello' AS "$aliased--value" 
      UNION 
      SELECT 'hello' AS "$aliased--value" 
      LIMIT 1 OFFSET 0
    ), 
    ABS(-9223372036854775808)
  )
) AS "$aliased--value" LIMIT 2 OFFSET 0

[AnyhowStep]

Relevant links,

• http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2020-March/088723.html
• https://www.sqlite.org/src/tktview?name=3c9eadd2a6
• https://www.sqlite.org/src/timeline?r=do-not-factor-functions

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