Hi, On 2020-07-24 12:31:05 -0400, Robert Haas wrote: > On Thu, Jul 23, 2020 at 12:57 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > Every so often we get a complaint like [1] about how a CASE should have > > prevented a run-time error and didn't, because constant-folding tried > > to evaluate a subexpression that would not have been entered at run-time. > > Yes, I've heard such complaints from other sources as well. > > > It struck me that it would not be hard to improve this situation a great > > deal. If, within a CASE subexpression that isn't certain to be executed > > at runtime, we refuse to pre-evaluate *any* function (essentially, treat > > them all as volatile), then we should largely get the semantics that > > users expect. There's some potential for query slowdown if a CASE > > contains a constant subexpression that we formerly reduced at plan time > > and now do not, but that doesn't seem to me to be a very big deal. > > Like Pavel, and I think implicitly Dagfinn and Andres, I'm not sure I > believe this. Pavel's example is a good one. The leakproof exception > helps, but it doesn't cover everything. Users I've encountered throw > things like date_trunc() and lpad() into SQL code and expect them to > behave (from a performance point of view) like constants, but they > also expect 1/0 not to get evaluated too early when e.g. CASE is used. > It's difficult to meet both sets of expectations at the same time and > we're probably never going to have a perfect solution, but I think > you're minimizing the concern too much here.
Wouldn't the rule that I proposed earlier, namely that sub-expressions that involve only "proper" constants continue to get evaluated even within CASE, largely address that? > I don't think I believe this either. I don't think an average user is > going to expect <expression> to behave differently from (SELECT > <expression>). This one actually bothers me more than the previous > one. How would we even document it? Sometimes things get inlined, > sometimes they don't. Sometimes subqueries get pulled up, sometimes > not. The current behavior isn't great, but at least it handles these > cases consistently. Getting the easy cases "right" while making the > behavior in more complex cases harder to understand is not necessarily > a win. Well, if we formalize the desired behaviour it's probably a lot easier to work towards implementing it in additional cases (like subselects). It doesn't seem to hard to keep track of whether a specific subquery can be evaluate constants in a certain way, if that's what we need. Greetings, Andres Freund
