Обсуждение: bug(?) : order by function limit x

I just face a very strange phenomena with postgres. I want to order my
output using a userdefined function and I want to limit the output.

In my case when using limit<=7 the result gots
totally mixed up and postgres simply does not order in the correct way:

example:

#select OID,rankval(releasedate,ranking),releasedate,ranking from tanzen where type!='TK' and released='1' order by
rankval(releasedate,ranking)desc offset 0 limit 3; 
  oid   |       rankval       |      releasedate       | ranking
--------+---------------------+------------------------+---------
 608153 | 9 days 14:08:10     | 2002-09-23 12:45:13+02 |      10
 325620 | -152 days -04:24:49 | 2002-04-21 18:12:14+02 |       3
 592403 | -8 days -02:21:43   | 2002-09-14 20:15:20+02 |       1
(3 rows)

The output is definitely not ordered by the value of rankval !!
The same is with limit=2,4,5,6,7

now the same query on the same database/table with limit=8:
# select OID,rankval(releasedate,ranking),releasedate,ranking from tanzen where type!='TK' and released='1' order by
rankval(releasedate,ranking)desc offset 0 limit 8; 
  oid   |      rankval       |      releasedate       | ranking
--------+--------------------+------------------------+---------
 608153 | 9 days 14:07:44    | 2002-09-23 12:45:13+02 |      10
 592403 | -8 days -02:22:09  | 2002-09-14 20:15:20+02 |       1
 570285 | -11 days -10:33:46 | 2002-09-02 12:03:43+02 |      10
 521871 | -39 days -05:45:38 | 2002-08-06 16:51:51+02 |       9
 458942 | -69 days -04:29:49 | 2002-07-06 18:07:40+02 |      10
 448472 | -84 days -08:19:21 | 2002-07-01 14:18:08+02 |       0
 442558 | -88 days -04:13:21 | 2002-06-27 18:24:08+02 |       0
 425840 | -96 days -12:09:16 | 2002-06-18 10:28:13+02 |       1

Now it works !!

The function "rankval" is defined as:

CREATE FUNCTION "rankval" (timestamp with time zone,integer) RETURNS interval AS
'select timestamp_mi($1+interval($2*86400),current_timestamp\);'
LANGUAGE 'sql';

It should return the interval between 'releasedate'+'ranking' and
'current_timestamp' where 'ranking' is given in days.

and the table is defined as:

# \d tanzen
                                Table "tanzen"
    Attribute    |           Type           |             Modifier
-----------------+--------------------------+----------------------------------
 releasedate     | timestamp with time zone |
 ranking         | integer                  | default 3
<skip the rest>

what is going on here ?

I use version 7.1.3 on linux.

thnx,
peter


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

pilsl@goldfisch.at writes:
> I just face a very strange phenomena with postgres. I want to order my
> output using a userdefined function and I want to limit the output.

Bizarre.  I tried to reproduce this using the following test:


create table tanzen(releasedate      timestamp with time zone,
                    ranking          integer default 3);

copy tanzen from stdin;
2002-09-23 12:45:13+02    10
2002-09-14 20:15:20+02    1
2002-09-02 12:03:43+02    10
2002-08-06 16:51:51+02    9
2002-07-06 18:07:40+02    10
2002-07-01 14:18:08+02    0
2002-06-27 18:24:08+02    0
2002-06-18 10:28:13+02    1
2002-04-21 18:12:14+02    3
\.

CREATE FUNCTION "rankval" (timestamp with time zone,integer) RETURNS interval AS
'select ($1+"interval"($2*86400)) - current_timestamp;'
LANGUAGE 'sql';

select OID,rankval(releasedate,ranking),releasedate,ranking from tanzen
order by rankval(releasedate,ranking) desc offset 0 limit 3;
select OID,rankval(releasedate,ranking),releasedate,ranking from tanzen
order by rankval(releasedate,ranking) desc offset 0 limit 8;


(I altered the function body a tad to make it acceptable to newer releases)
but this works fine for me on all versions back to and including 7.1.3.

I think there must be something you haven't told us.  Can you produce
a self-contained example script that gets a wrong result?

            regards, tom lane

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

On Tue, 24 Sep 2002 pilsl@goldfisch.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.