Re: Making CASE error handling less surprising

pá 24. 7. 2020 v 18:49 odesílatel Andres Freund <andres(at)anarazel(dot)de> napsal:

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

It doesn't solve a possible performance problem with one shot (EXECUTE stmt
plpgsql) queries, or with parameterized queries

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