Re: BUG #5816: index not used in function

> We may have different perceptions of something being a 'bug'. I always
> have several simple ways of determining it. One of them is when a
> work-around is in the proposal. Yours is one.

It seems to me that the important question in this case is whether or not the query produced the correct result.

You are complaining about a performance issue, not a correctness issue, right?

Kevin's work-around is meant to help you *gain better performance*, not to obtain correct results when you are getting incorrect results.

> There can be quite a number of ways of looking at the issue. First, it
> is truly an implementation matter (making it in the true sense a bug). I
> do not believe that the spec would in formal way say that 'well, there
> are caveats where you have to do this and that to work around'.

The "spec" (by which I assume you mean the SQL standard) says nothing about which execution plan will be selected the optimizer.

> <snip>
> If by 'kept from one execution to another' means that (the concept of) a
> plan is implemented static, this can be a low level design issue, which
> in general will still be regarded as implementation, thus a bug.

The execution plan is not quite static - it is computed the first time you run the function (within a session) and is discarded when your session ends (or when the compiled function/execution plan becomes obsolete because of a change to a dependency).

That is by design.

If you want a dynamic plan that is re-computed each time you execute the query, you can get that behavior by using dynamic SQL, as Kevin suggested.

-- Korry