Re: [HACKERS] views and group by (formerly: create view as selec

jwieck(at)debis(dot)com (Jan Wieck) writes:
> I tried to make it and it works partially. The problems arise
> if you have a view with a group by clause but do not select
> the attributes the group by clause uses:

> CREATE TABLE t1 (a int4, b int4);
> CREATE VIEW v1 AS SELECT b, count(b) FROM t1 GROUP BY b;

> SELECT count FROM v1;
> SELECT count(*) FROM v1;

> Both selects crash the backend!

Hmm, this sounds very similar to a problem I was looking at on Sunday:

select sum(quantity), ID+1 from aggtest1 group by ID+1;
ERROR: replace_agg_clause: variable not in target list

The error message is new as of Sunday; with code older than that this
will crash the backend. And, in fact, what I get from Jan's example
above is:

SELECT count FROM v1;
ERROR: replace_agg_clause: variable not in target list

In both situations, it's necessary to add variables to the target list
that aren't in the list produced by the parser. We have code that does
that sort of thing, but it's evidently not getting applied...

regards, tom lane