Re: Possible bug in CASE evaluation

On Fri, Jun 21, 2013 at 04:12:32PM +0200, Andres Freund wrote:
> On 2013-06-21 09:51:05 -0400, Noah Misch wrote:
> > On Fri, Jun 21, 2013 at 09:20:21AM +0000, Albe Laurenz wrote:

> > Even if you could skip it, queries with expensive
> > constant expressions would notice the performance loss. The situations helped
> > by a change like this are too marginal to accept that cost.
>
> I have to say, that argument doesn't excite me mu8ch. It's not like we
> don't want to do the constant expression evaluation at all anymore. Just
> not inside CASE WHEN blocks which already are barring some optimizations
> anyway...

Sure, it's a narrow loss. Before introducing a new narrow loss to fix an
existing one, we should consider which loss hurts more. Offhand, I sympathize
with the folks who would lose performance more than with the folks who want to
write the sorts of expressions under consideration.

> > Would it work to run eval_const_expressions() lazily on THEN clauses? The
> > plan-time eval_const_expressions() would not descend to them. The first
> > ExecEvalCase() to use a given THEN clause would run eval_const_expressions()
> > before proceeding.
>
> Ugh. Doesn't sound nice.

Would you elaborate?

> > I question whether real applications care. It's important to have CASE usable
> > for avoiding data-dependent errors, but what's the use of including in your
> > query an expression that can do nothing but throw an error? Does anyone have
> > a real-world example? Perhaps some generated-query scenario.
>
> It doesn't need to be an actual constant. Something that evaluates to
> the value at plan time is enough:
> PREPARE foo AS SELECT CASE WHEN (SELECT $1::int)=0 THEN 0 ELSE 1/$1::int END;
> EXECUTE foo(0);

> Now, that example only crashes because one place uses (SELECT $1) and
> the other doesn't, but...

Not the "real-world" I was hoping for, but fair enough.

"Crash" in this context means "raise an error", right?

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com