Re: Possible bug in CASE evaluation

2013/6/21 Andres Freund <andres@2ndquadrant.com>:
> 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.

yes, I dislike it too - then we can have inconsistent behave of
constant between CASE and other statements.

We should to do without any performance lost, if we do some changes in
this area.

Regards

Pavel

>
> 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
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers