Thread: Bug in 6.4.2. Aggregate/View/Where-condition
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@felidae.apana.org.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@felidae.apana.org.au>
(v_,)' _ )`-.\ ``-'
_.- _..-_/ / ((.' FC1.3: FFH3cmA+>++C++D++H++M++P++R++T+++WZ++Sm++
((,.-' ((,/ fL RLCT acl+++d++e+f+++h++i++++jp-sm++
On Mon, 28 Feb 2000, Chris Cogdon wrote:
>
>
> 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 :)
Update:
1. It does a similar thing under 6.5.2 (redhat-6.1 this time, the previous
was redhat-6.0), except the error message is different:
ERROR: ExecEvalExpr: unknown expression type 108
2. Notice how this example under 6.4.2 returns one, empty row?
> test1=> select * from span where 0<min;
> ref|min|max
> ---+---+---
> | |
> (1 row)
Under 6.5.2, it returns, correctly:
test1=> select * from span where 0<min;
ref|min|max
---+---+---
1| 1| 4
(1 row)
but, under 6.5.2 a query that shouldnt return /any/ rows, still returns
one empty row:
test1=> select * from span where 50<min;
ref|min|max
---+---+---
| |
(1 row)
bugsbugsbugsbugsbugsbugsbugs :)
("`-/")_.-'"``-._ Ch'marr, a.k.a.
. . `; -._ )-;-,_`) Chris Cogdon <chris@felidae.apana.org.au>
(v_,)' _ )`-.\ ``-'
_.- _..-_/ / ((.' FC1.3: FFH3cmA+>++C++D++H++M++P++R++T+++WZ++Sm++
((,.-' ((,/ fL RLCT acl+++d++e+f+++h++i++++jp-sm++
Chris Cogdon <chris@felidae.apana.org.au> writes:
> create view span as select ref, min(starttime), max(endtime) from vals
> group by ref;
> select * from span where 0<min;
Current sources (7.0beta1) don't give the "node 108" failure, but they
don't give right answers either. With a few more data rows than you
showed, viz
regression=# select * from vals;
ref | starttime | endtime
-----+-----------+---------
1 | 1 | 3
1 | 2 | 4
3 | 2 | 4
3 | 1 | 7
4 | 5 | 7
(5 rows)
regression=# select * from span;
ref | min | max
-----+-----+-----
1 | 1 | 4
3 | 1 | 7
4 | 5 | 7
(3 rows)
So far so good, but:
regression=# select * from span where min > 3;
ref | min | max
-----+-----+-----
(0 rows)
Ooops. (I think the problem here is that the WHERE clause really needs
to be a HAVING clause after the rule is expanded, since that "min" is
really an aggregate invocation --- but the rewriter isn't smart enough
to make that change.)
The bottom line is that grouped views don't work right in any but the
very simplest cases, and they can't work right given the current
implementation of rules. We need to redesign the internal querytree
data structure to support explicit subqueries. I hope to see that
happen for 7.1, but it's not done or even started as of today.
Sorry the news isn't better :-(
regards, tom lane
On Mon, 28 Feb 2000, Tom Lane wrote:
> regression=# select * from span where min > 3;
> ref | min | max
> -----+-----+-----
> (0 rows)
Note that under 6.4 and 6.5, the result of a aggregate (or a subquery,
even) has to be on the RHS Of an operator. Viz:
test1=> select * from span where min>0;
ERROR: rewrite: aggregate column of view must be at rigth side in
The fact that this error does not come up in 7 either means that they've
fixed a limitation, or there's a bug that's crept in. Can you try it with
select * from span where 3<min;
and see what you get.
> Ooops. (I think the problem here is that the WHERE clause really needs
> to be a HAVING clause after the rule is expanded, since that "min" is
> really an aggregate invocation --- but the rewriter isn't smart enough
> to make that change.)
Okay... here's a test under 6.5.2:
test1=> select * from span having 0<min;
ERROR: SELECT/HAVING requires aggregates to be valid
Oops.
Does this work under 7.beta?
>
> The bottom line is that grouped views don't work right in any but the
> very simplest cases, and they can't work right given the current
> implementation of rules. We need to redesign the internal querytree
> data structure to support explicit subqueries. I hope to see that
> happen for 7.1, but it's not done or even started as of today.
>
> Sorry the news isn't better :-(
Thanks tons for trying that out for me, tom. Hope that gives our kind,
wonderful developers food for thought :)
(PS... just in case having min and max as column names in the view was
giving 6.4 or 6.5 headaches, I've repeated /all/ the tests using different
names for the columns, with no apparent change in results)
("`-/")_.-'"``-._ Ch'marr, a.k.a.
. . `; -._ )-;-,_`) Chris Cogdon <chris@felidae.apana.org.au>
(v_,)' _ )`-.\ ``-'
_.- _..-_/ / ((.' FC1.3: FFH3cmA+>++C++D++H++M++P++R++T+++WZ++Sm++
((,.-' ((,/ fL RLCT acl+++d++e+f+++h++i++++jp-sm++
Chris Cogdon <chris@felidae.apana.org.au> writes:
> On Mon, 28 Feb 2000, Tom Lane wrote:
>> regression=# select * from span where min > 3;
> Note that under 6.4 and 6.5, the result of a aggregate (or a subquery,
> even) has to be on the RHS Of an operator. Viz:
> The fact that this error does not come up in 7 either means that they've
> fixed a limitation,
They (that is, I) fixed it. However, that's just a removal of
one small limitation in code that's fundamentally bogus to start
with :-(. The rewriter's entire approach to aggregates that it
inserts into WHERE is wrong.
> Can you try it with
> select * from span where 3<min;
> and see what you get.
The same thing.
> Okay... here's a test under 6.5.2:
> test1=> select * from span having 0<min;
> ERROR: SELECT/HAVING requires aggregates to be valid
> Does this work under 7.beta?
No:
regression=# select * from span having 0<min;
ERROR: Attribute span.ref must be GROUPed or used in an aggregate function
The appearance of this particular error might represent a fixable bug,
but that's small comfort knowing that the "0 < min" part cannot possibly
work as you want it to. It's tough to justify putting much effort into
patching small bugs in a chunk of code that I know needs to be thrown
away and rewritten in toto...
regards, tom lane