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
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 |