pg 9.1.1 - ERROR: could not find pathkey item to sort

Started by bricklenover 14 years ago3 messagesbugs
Jump to latest
#1bricklen
bricklen@gmail.com

Is this a bug, or due to the way I am using the RANK() function in the
following query? The error is triggered when I use (sum(y) / sum(x))
in the ORDER BY of the RANK() function.

Simple test case:

create table t (a int, b int, x numeric, y integer );
insert into t
select g,round(mod(g,4)),
round((random()*1000)::NUMERIC,2),round((random()*1000)::NUMERIC) from
generate_series(1, 100) as g;

select a,
b,
(sum(y) / sum(x)) as res,
rank() over (partition by a,b order by (sum(y) / sum(x)) desc) as rank
--rank() over (partition by a,b order by sum(y) desc) as rank
-- no error
from t
group by a,b;

select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit

Any other details needed?

Cheers,

Bricklen

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: bricklen (#1)
Re: pg 9.1.1 - ERROR: could not find pathkey item to sort

bricklen <bricklen@gmail.com> writes:

Is this a bug,

Yes. Thanks for the test case, will look.

regards, tom lane

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Tom Lane (#2)
Re: pg 9.1.1 - ERROR: could not find pathkey item to sort

I wrote:

bricklen <bricklen@gmail.com> writes:

Is this a bug,

Yes. Thanks for the test case, will look.

Fixed, patch is at
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1679e9feddc94bd7372a6829db92868e55ef7177

regards, tom lane