Suppose I write: SELECT quick_expr AND slow_function() or SELECT quick_expr OR slow_function()

Presently, both operands of those boolean operators are evaluated, then the operator is applied to the operand values. This may be much slower than if evaluation of one operand could be deferred until its value is needed and never evaluated if not needed. Such behavior is known as "short-circuiting".¹

Is there some way, short of getting the same effect with much additional query complexity, to express short-circuiting in SQL?

---

1. ^{^} This would be exactly as C's && and || work.

  SELECT CASE WHEN quick_expr AND slow_function() THEN true ELSE false END;

SELECT CASE WHEN quick_expr AND slow_function() THEN true ELSE false END;

I assume the same would be working but returning TRUE if quick_expr() yields TRUE for:

SELECT CASE WHEN quick_expr OR slow_function() THEN true ELSE false END
;

My question is if this next statement would avoid calculating the slow_function() given quick_expr() > 20 returns FALSE, and also avoid evaluating slow_function() twice if not?

SELECT CASE WHEN (quick_expr > 20) AND (slow_function() > 9) THEN slow_function() ELSE quick_expr END
;

If this pattern avoids calculating slow_function() twice, or even once given quick_expr > 20 returns FALSE, then it's a real tool to avoid heavy calculations in-place in large queries. If it works, would it work always, or are there exceptions or caveats?

PS - Before someone points out the obvious, I understand I can also avoid the above heavy calculation using where-clause filtering (and right now it is my go-to method), for example:

SELECT slow_function()
  FROM tbl
 WHERE quick_expr > 20
;

Which works, except when it is joined to another table or wanting to show more rows than what that WHERE-filter yields.

My question is if this next statement would avoid calculating the slow_function() ...
SELECT CASE WHEN (quick_expr > 20) AND (slow_function() > 9)
THEN slow_function() ELSE quick_expr END

I'm afraid that the question brings in the confounding factor of the optimizer's effect and whether slow_function() has been registered as being deterministic.

Running this query in the CLI, explain select case when datetime('now')>'2022-12-24 23:59:59' AND random()>0 then random() else datetime('now') end; shows the answer, (I think), which is that the result clauses are evaluated anew.

BTW, There is a shorter way to use CASE for short-circuited AND and/or OR.

CASE WHEN quick_expr THEN slow_function() ELSE false END -- ss AND

CASE WHEN NOT quick_expr THEN slow_function() ELSE true END -- ss OR