Re: Making CASE error handling less surprising

On Fri, Jul 24, 2020 at 4:35 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andres Freund <andres(at)anarazel(dot)de> writes:
> > I'm a bit worried about a case like:
>
> > CREATE FUNCTION yell(int, int)
> > RETURNS int
> > IMMUTABLE
> > LANGUAGE SQL AS $$
> > SELECT CASE WHEN $1 != 0 THEN 17 / $2 ELSE NULL END
> > $$;
>
> > EXPLAIN SELECT yell(g.i, 0) FROM generate_series(1, 10) g(i);
>
> > I don't think the parameters here would have been handled before
> > inlining, right?
>
> Ah, I see what you mean. Yeah, that throws an error today, and it
> still would with the patch I was envisioning (attached), because
> inlining does Param substitution in a different way. I'm not
> sure that we could realistically fix the inlining case with this
> sort of approach.
>
> I think this bears out the comment I made before that this approach
> still leaves us with a very complicated behavior. Maybe we should
> stick with the previous approach, possibly supplemented with a
> leakproofness exception.
>

I am actually not so sure this is a good idea. Here are two doubts I have.

1. The problem of when a given SQL expression is evaluated crops up in a
wide variety of different contexts and, worst case, causes far more damage
than queries which always error. Removing the lower hanging fruit while
leaving cases like:

select lock_foo(id), * from foo where somefield > 100; -- which rows does
lock_foo(id) run on? Does it matter?

is going to legitimize these complaints in a way which will be very hard to
do unless we also want to eventually be able to specify when volatile
functions may be run. The two cases don't look the same but they are
manifestations of the same problem which is that when you execute a SQL
query you have no control over when expressions are actually run.

2. The refusal to fold immutables within case statements here mean either
we do more tricks to get around the planner if we hit a pathological cases
in performance. I am not convinced this is a net win.

If we go this route, would it be too much to ask to allow a GUC variable to
preserve the old behavior?

> regards, tom lane
>
>

--
Best Regards,
Chris Travers
Head of Database

Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com
Saarbrücker Straße 37a, 10405 Berlin