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

Tom Lane wrote:

>
> jwieck@debis.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@debis.com (Jan Wieck) #