Skip site navigation (1) Skip section navigation (2)

Bug in 6.4.2. Aggregate/View/Where-condition

From: Chris Cogdon <chris(at)felidae(dot)apana(dot)org(dot)au>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug in 6.4.2. Aggregate/View/Where-condition
Date: 2000-02-28 12:35:43
Message-ID: Pine.LNX.4.10.10002282331560.12235-100000@uncia.felidae.apana.org.au (view raw or flat)
Thread:
Lists: pgsql-bugs

Hope you wonderful folks can help me with this problem. Even stating 'it
works fine under version such-and-such' would be a great help (save me
downloading and compliling n different versions :)


============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name		: Chris Cogdon
Your email address	: chris(at)felidae(dot)apana(dot)org(dot)au


System Configuration
---------------------
  Architecture (example: Intel Pentium)  	: Intel Pentium

  Operating System (example: Linux 2.0.26 ELF) 	: Linux 2.2.5

  PostgreSQL version (example: PostgreSQL-6.4.2)  :   PostgreSQL-6.4.2

  Compiler used (example:  gcc 2.8.0)  : (unknown... shipped with
redhat-6.1) So, I /believe/ its egcs-1.1.2-12


Please enter a FULL description of your problem:
------------------------------------------------

Generates error when attempting a query. See example below.

To give you some context for the type of query I'm trying to achive, the 
'vals' class contains records of resource allocation (the actual resource 
is omitted). starttime and endtime represent the start and end times 
for that resourse, in unixtime. 'ref' is a index for some activity.
Resource usages with the same 'ref' belong to the same activity.

The 'span' class holds the earliest starttime, and latest endtime, for
any activity. Assuming an activity's start and end time is solely
determined by its resource utilisation, the 'span' class will give
us the duration of any activity.

The query which fails, below, is asking for any activity which resides in,
partially or fully, in the time span 0 to 5.

I used the view to get around the complexity of using the aggregate functions
alongside WHICH clauses and table joins. Unfortunately, postgresql doesnt
seem to like this seemingly simple example :)

The data below is mostly from a pg_dump. THe rule and span classes were
created using:

create view span as select ref, min(starttime), max(endtime) from vals
group by ref;


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible: 
----------------------------------------------------------------------

CREATE TABLE "vals" (
	"ref" int4,
	"starttime" int4,
	"endtime" int4);
CREATE TABLE "span" (
	"ref" int4,
	"min" int4,
	"max" int4);
INSERT INTO "vals" values (1,1,3);
INSERT INTO "vals" values (1,2,4);
CREATE  INDEX "vals_ref" on "vals" using btree ( "ref" "int4_ops" );
CREATE  INDEX "vals_starttime" on "vals" using btree ( "starttime" "int4_ops" );
CREATE  INDEX "vals_endtime" on "vals" using btree ( "endtime" "int4_ops" );
CREATE RULE "_RETspan" AS ON SELECT TO "span" DO INSTEAD SELECT "ref", "min"("starttime") AS "min", "max"("endtime") AS "max" FROM "vals" GROUP BY "ref";

test1=> select * from span where 0<min;          
ref|min|max
---+---+---
   |   |   
(1 row)

test1=> select * from span where 0<min and 5>max;
ERROR:  _finalize_primnode: can't handle node 108




   ("`-/")_.-'"``-._        Ch'marr, a.k.a.
    . . `; -._    )-;-,_`)  Chris Cogdon <chris(at)felidae(dot)apana(dot)org(dot)au>
   (v_,)'  _  )`-.\  ``-'
  _.- _..-_/ / ((.'       FC1.3: FFH3cmA+>++C++D++H++M++P++R++T+++WZ++Sm++
((,.-'   ((,/   fL               RLCT acl+++d++e+f+++h++i++++jp-sm++


pgsql-bugs by date

Next:From: Chris CogdonDate: 2000-02-28 12:44:27
Subject: Bug in 6.4.2. Aggregate/View/Where-condition
Previous:From: Marko KreenDate: 2000-02-28 11:41:24
Subject: 7.0beta1: bugs appearing on cygwin

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group