Re: review: More frame options in window functions

On Sat, January 16, 2010 09:29, Hitoshi Harada wrote:
> 2010/1/16 Erik Rijkers <er(at)xs4all(dot)nl>:
>>
>>> Thanks for the review. I've found another crash today and attached is
>>> fixed version. The case is:
>>>
>>> SELECT four, sum(ten) over (PARTITION BY four ORDER BY four RANGE 1
>>> PRECEDING) FROM tenk1 WHERE unique1 < 10;
>>>
>>
>> Hi,
>>
>> The patch (more_frame_options.20100115.patch.gz) applies cleanly, but the regression test gives:
>
> It doesn't happen here. Could you send more information like configure
> options and EXPLAIN output of that query?
>

Sorry, I should have done that the first time. Here is more info:

Linux 2.6.18-164.el5 x86_64 / CentOS release 5.4 (Final)

Source is CVS as of today (which without the patch compiles and runs the regression test OK).
With the patch, compiled + installed without 'make check', pg_config gives:

CONFIGURE = '--prefix=/var/data1/pg_stuff/pg_installations/pgsql.rows_frame_types'
'--with-pgport=6547'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement
-Wendif-labels -fno-strict-aliasing -fwrapv
CFLAGS_SL = -fpic
LDFLAGS = -Wl,-rpath,'/var/data1/pg_stuff/pg_installations/pgsql.rows_frame_types/lib'
LDFLAGS_SL =
LIBS = -lpgport -lz -lreadline -ltermcap -lcrypt -ldl -lm
VERSION = PostgreSQL 8.5devel-rows_frame_types

psql -f $pgsql_regress_sql/create_table.sql;
cat $pgsql_regress_data/tenk.data | psql -c "copy tenk1 from stdin csv delimiter E'\t'";

explain
SELECT four, ten, sum(ten) over (partition by four order by four range 1 preceding)
FROM tenk1 WHERE unique1 < 10;

QUERY PLAN
--------------------------------------------------------------------
WindowAgg (cost=483.17..483.37 rows=10 width=8)
-> Sort (cost=483.17..483.19 rows=10 width=8)
Sort Key: four
-> Seq Scan on tenk1 (cost=0.00..483.00 rows=10 width=8)
Filter: (unique1 < 10)
(5 rows)

explain analyze
SELECT four, ten, sum(ten) over (partition by four order by four range 1 preceding)
FROM tenk1 WHERE unique1 < 10;

ERROR: cannot extract system attribute from minimal tuple

hth,

Erik Rijkers