Re: Possible bug in CASE evaluation

On 2013-06-21 09:51:05 -0400, Noah Misch wrote:
> On Fri, Jun 21, 2013 at 09:20:21AM +0000, Albe Laurenz wrote:
> > Andres Freund wrote:
> > > Yes, I think it's pretty clearly a bug - Tom doesn't seem think so
> > > though. If we can agree it is, the fix outlined over on -bugs seems to
> > > be easily enough implemented...
>
> If you refer to this:
>
> On Tue, Jun 18, 2013 at 03:31:32PM +0200, Andres Freund wrote:
> > So it seems we need to stop processing after finding a single WHEN
> > that's not const? Does anybody have a better idea?
>
> eval_const_expressions() is not just an optimization: it performs mandatory
> transformations such as the conversion of named-argument function calls to
> positional function calls.

Ah yes. Forgot about that... Scrap that. Although it surely isn't nice
that all that is done in a function calleed eval_const_expressions()...

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

> 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. I don't have any better ideas than to actually
split eval_const_expressions into one function that does the necessary
things like canonicalization of AND/OR and one that actually evaluates
expressions inside though.
So maybe that's the way to go :/

> > I think that it is surprising behaviour.
>
> That's about how I characterize it, too.
>
> 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);

That example will most likely only crashes in 9.2+ because it will
replan it with the acutal parameter values in place. But you could have
the same in earlier versions e.g. using PQExecParams(), but that's
harder to demonstrate.

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

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services