Re: Making CASE error handling less surprising

Hi,

On 2020-07-24 12:31:05 -0400, Robert Haas wrote:
> 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.

Wouldn't the rule that I proposed earlier, namely that sub-expressions
that involve only "proper" constants continue to get evaluated even
within CASE, largely address that?

> 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.

Well, if we formalize the desired behaviour it's probably a lot easier
to work towards implementing it in additional cases (like
subselects). It doesn't seem to hard to keep track of whether a specific
subquery can be evaluate constants in a certain way, if that's what we
need.

Greetings,

Andres Freund