bug(?) : order by function limit x

From: pilsl(at)goldfisch(dot)at
To: pgsql-general(at)postgresql(dot)org
Subject: bug(?) : order by function limit x
Date: 2002-09-23 20:43:59
Message-ID: 20020923224359.D24588@goldfisch.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)goldfisch(dot)at

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Josh Berkus 2002-09-23 20:49:27 Re: [GENERAL] CURRENT_TIMESTAMP
Previous Message Bruce Momjian 2002-09-23 20:41:44 Re: [GENERAL] CURRENT_TIMESTAMP