Re: Making CASE error handling less surprising

On Thu, Jul 23, 2020 at 12:57 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Every so often we get a complaint like [1] about how a CASE should have
> prevented a run-time error and didn't, because constant-folding tried
> to evaluate a subexpression that would not have been entered at run-time.

Yes, I've heard such complaints from other sources as well.

> It struck me that it would not be hard to improve this situation a great
> deal. If, within a CASE subexpression that isn't certain to be executed
> at runtime, we refuse to pre-evaluate *any* function (essentially, treat
> them all as volatile), then we should largely get the semantics that
> users expect. There's some potential for query slowdown if a CASE
> contains a constant subexpression that we formerly reduced at plan time
> and now do not, but that doesn't seem to me to be a very big deal.

Like Pavel, and I think implicitly Dagfinn and Andres, I'm not sure I
believe this. Pavel's example is a good one. The leakproof exception
helps, but it doesn't cover everything. Users I've encountered throw
things like date_trunc() and lpad() into SQL code and expect them to
behave (from a performance point of view) like constants, but they
also expect 1/0 not to get evaluated too early when e.g. CASE is used.
It's difficult to meet both sets of expectations at the same time and
we're probably never going to have a perfect solution, but I think
you're minimizing the concern too much here.

> This is not a complete fix, because if you write a sub-SELECT the
> contents of the sub-SELECT are not processed by the outer query's
> eval_const_expressions pass; instead, we look at it within the
> sub-SELECT itself, and in that context there's no apparent reason
> to avoid const-folding. So
> CASE WHEN x < 0 THEN (SELECT 1/0) END
> fails even if x is never less than zero. I don't see any great way
> to avoid that, and I'm not particularly concerned about it anyhow;
> usually the point of a sub-SELECT like this is to be decoupled from
> outer query evaluation, so that the behavior should not be that
> surprising.

I don't think I believe this either. I don't think an average user is
going to expect <expression> to behave differently from (SELECT
<expression>). This one actually bothers me more than the previous
one. How would we even document it? Sometimes things get inlined,
sometimes they don't. Sometimes subqueries get pulled up, sometimes
not. The current behavior isn't great, but at least it handles these
cases consistently. Getting the easy cases "right" while making the
behavior in more complex cases harder to understand is not necessarily
a win.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company