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

Tom Lane wrote:

>
> 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...

Yes, and the attributes could be marked junk so they are
taken out of the final result again later. But I wouldn't
spend time on it because I think it's an incomplete solution.

Let's have a view doing a sum() over a field with a group by.
The values are measured in meters. And there is another table
with factors to convert between meters and inches, feet,
yards.

CREATE TABLE t1 (id serial, owner text, len float8);
CREATE TABLE t2 (quant text, factor float8);
CREATE VIEW v1 AS SELECT owner, sum(len) FROM t1 GROUP BY owner;

Now you want the sums converted to any quantity and do a:

SELECT a.owner, a.sum as meter, b.quant, a.sum * b.factor as size
FROM v1 a, t2 b;

Ooops - there's only one row per owner left. And more OOOPS -
it has sum()*count(* from t2) as meters! You must explicitly
tell "GROUP BY a.owner, b.quant" to get the correct result.
This is a case, where IMHO nothing else than a subselecting
RTE could help. The problem in this case is that the rewrite
system would have to add another attribute to the group by
clause which is already there. But I see absolutely no way
how it could decide which one. And there might be cases where
totally no grouping could produce the correct result.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #