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

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 ?

I just imagine the following:

day1 = 2002-01-10  : insert new line1 with releasedate="2002-01-10"
                     => rankval=0 and stored in the index

day2 = 2002-01-20  : insert new line2 with releasedate="2002-01-19"
                     => rankval=-1 and stored in the index

day3 = 2002-02-25  : perform the query  :  ...select by rankval desc
           the value for rankval is taken from the index and therefore is
       line1 listed on top and line2 is listed second, while - if
           rankval was not stored in an index the order would be
           reversed cause the current value of rankval is now:
            for line1: rankval=-16 days
            for line2: rankval= -6 days

This would explain the wrong order in some cases but it still does not
explain why the order was correct again if the used limit was greater
than 7.  But maybe this has to do with some internal index-processing ?

Unfortunately I didnt think about the index before and so could have
done more query to deeper check the phenomena.

btw: anyone knows how to get the defintion for a user-defintion
function from postgres directly. I always create a full database-dump
to get the definition.

thnx,
peter



>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

--
mag. peter pilsl
IT-Consulting
tel: +43-699-1-3574035
fax: +43-699-4-3574035
pilsl@goldfisch.at