Thread
-
Bug in 6.4.2. Aggregate/View/Where-condition
Chris Cogdon <chris@felidae.apana.org.au> — 2000-02-28T12:51:46Z
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++ -
Re: [BUGS] Bug in 6.4.2. Aggregate/View/Where-condition
Chris Cogdon <chris@felidae.apana.org.au> — 2000-02-28T13:22:28Z
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++ -
Re: [BUGS] Bug in 6.4.2. Aggregate/View/Where-condition
Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-28T15:06:28Z
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
-
Re: [BUGS] Bug in 6.4.2. Aggregate/View/Where-condition
Chris Cogdon <chris@felidae.apana.org.au> — 2000-02-28T21:28:52Z
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++ -
Re: [BUGS] Bug in 6.4.2. Aggregate/View/Where-condition
Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-28T22:40:23Z
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