bug(?) : order by function limit x - Mailing list pgsql-general

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