Re: bug(?) : order by function limit x

On Tue, 24 Sep 2002 pilsl(at)goldfisch(dot)at wrote:

> On Mon, Sep 23, 2002 at 05:23:17PM -0400, Tom Lane wrote:
>
> >
> > I think there must be something you haven't told us. Can you produce
> > a self-contained example script that gets a wrong result?
> >
>
> The bug is not reproduceable on any other machine I tried now. As soon
> as I move function/table the bug vanishes. I also imported the very
> same table/function into a different database on the same machine and
> the troubles did not occure.
>
> What I did now - and what makes the phenomena disappear - is to delete
> the function and the corresponding index and recreated function and
> index and maybe this is was I havnt told you :
>
> There was an index on this table and function:
>
> create INDEX tanzen_rankval_idx on tanzen (rankval(releasedate,ranking));
>
> Can this have to do with the problem ?

Probably. The index was almost certainly completely broken. Functional
index functions shouldn't depend on things outside the columns passed in
(in this case current_timestamp). 7.2 and above wouldn't have let you
make the index without marking your function which probably would have
been the tipoff.