Re: Possible bug in CASE evaluation

On Tue, Jun 25, 2013 at 03:01:52PM +0200, Andres Freund wrote:
> On 2013-06-24 21:35:53 -0400, Noah Misch wrote:
> > Simple enough, yes. The other point still stands.
>
> You mean performance? Primarily I still think we should first worry
> about correctness first and then about performance. And CASE is the
> documented (and really only, without writing procedual code) solution to
> use for the cases where evaluation order actually *is* important.

I largely share that sentiment, but it's tempered here by the incorrect
behavior's long tenure, the difficulty of encountering a problem without
constructing a test case for the purpose of doing so, the availability of
workarounds, and the open-ended negative performance implications of your
proposed correction.

> But anyway, the question is to find realistic cases to measure the
> performance of. Obviously you can just make arbitrarily expensive
> expressions that can be computed full during constant folding. Which I
> don't find very interesting, do you?

> Now, for the other extreme, the following completely random query I just
> typed out:
> SELECT f FROM (SELECT (CASE g.i WHEN -1 THEN 0 WHEN 1 THEN 3.0/1 WHEN g.i THEN 2.0/3 END) f FROM generate_series(1, 1000000) g(i)) s WHERE f = 0;
> master: 491.931 patched: 943.629
>
> suffers way much worse because the division is so expensive...

That's a clear indicator for this strategy being a dead end. It's not far
from that to a realistic use case; e.g. log(10,2)/g.i or g.i*(2.0/3).

I'm still interested in your answer to my first question here:
http://www.postgresql.org/message-id/20130621150554.GC740984@tornado.leadboat.com

nm

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