This implies that the "group by" clause is not supported in views.  I have
created views that use the group by clause and they appear to work.  I have
not verified the content of the records.  I would like to know more about
what Jan means when he says that "group by" is not supported in views?  Does
it mean that the content of the results could be unexpected or are they
conditions where they may work and other conditions where they don't work?
More info would be greatly appreciated.

Thanks, Michael

	-----Original Message-----
	From:	jwieck@debis.com [SMTP:jwieck@debis.com]
	Sent:	Monday, April 26, 1999 9:35 AM
	To:	rbrad@hpb50023.boi.hp.com
	Cc:	pgsql-hackers@postgreSQL.org
	Subject:	Re: [HACKERS] create view as select distinct (fwd)

	>
	> This is on the TODO list.
	>
	> I actually have a solution that seems to work fine, but I wanted
to run it past
	> the backend guru's after we have finished the 6.5 beta.
	>
	> Sorry I din't get it finished before the beta started.
	>
	> -Ryan

	    I wonder how it does!

	    Have the following:

	      CREATE TABLE t1 (a int4, b text);
	      CREATE TABLE t2 (c int4, d text);
	      CREATE VIEW v2 AS SELECT DISTINCT ON c * FROM t2;

	    Populate them with:

	      t1:
	      1    'one'
	      1    'ena'
	      2    'two'
	      2    'thio'
	      3    'three'
	      3    'tria'
	      4    'four'
	      4    'tessera'

	      t2:
	      1    'I'
	      1    'eins'
	      2    'II'
	      2    'zwei'
	      3    'III'
	      3    'drei'

	    Now you do

	      SELECT t1.a, t1.b, v2.d FROM t1, v2
	        WHERE t1.a = v2.c;

	    Does  that  work  and  produce the correct results? Note that
	    there are more than one correct results. The DISTINCT  SELECT
	    from t2 already has. But in any case, the above SELECT should
	    present 6 rows (all the rows of t1 from 1 to  33  in  english
	    and  greek) and column d must show either the roman or german
	    number.

	    To make it more complicated, add table  t3  and  populate  it
	    with more languages.  Then setup

	      CREATE VIEW v3 AS SELECT DISTINCT ON e * FROM t3;

	    and expand the above SELECT to a join over t1, v2, v3.

	    Finally,  think  about  a view that is a DISTINCT SELECT over
	    multiple tables.  Now you build another view as  SELECT  from
	    the  first  plus  some  other  table  and  make  the new view
	    DISTINCT again.

	    The same kind of problem causes that views  currently  cannot
	    have ORDER BY or GROUP BY clauses. All these clauses can only
	    appear once per query, so there is no room where the  rewrite
	    system  can place multiple different ones.  Implementing this
	    requires first dramatic changes to the querytree layout and I
	    think it needs subselecting RTE's too.


	Sorry - 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) #

	

Mon, 26 Apr 1999 18:05:44 -0500
Michael J Davis <michael.j.davis@tvguide.com>
RE: [HACKERS]  views and group by (formerly: create view as selec
	t distinct)
Michael J Davis wrote:

>
> This implies that the "group by" clause is not supported in views.  I have
> created views that use the group by clause and they appear to work.  I have
> not verified the content of the records.  I would like to know more about
> what Jan means when he says that "group by" is not supported in views?  Does
> it mean that the content of the results could be unexpected or are they
> conditions where they may work and other conditions where they don't work?
> More info would be greatly appreciated.

    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!

    If  you have a view that uses GROUP BY and do a simple SELECT
    * from it, then it will work and return the correct  results.


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) #



Tue, 27 Apr 1999 09:20:09 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS]  views and group by (formerly: create view as selec
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...

			regards, tom lane

Tue, 27 Apr 1999 10:50:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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) #



Tue, 27 Apr 1999 18:24:23 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] views and group by (formerly: create view as selec
jwieck@debis.com (Jan Wieck) wrote:
>       CREATE TABLE t1 (a int4, b int4);
>       CREATE VIEW v1 AS SELECT b, count(b) FROM t1 GROUP BY b;
>       SELECT count FROM v1;
>       [ ... ker-boom ... ]

and I said I thought it was the same planner bug I was chasing in
non-VIEW-using examples of GROUP BY.  It turns out it's not the same.
In the above example, the breakage happens in rewrite before the
planner ever sees it.  When make_groupPlan is called, it sees a
GroupClause like this:

(gdb) p *((GroupClause *) 0x400b8690)->entry
$107 = {type = T_TargetEntry, resdom = 0x400b86c0, fjoin = 0x0,
  expr = 0x400b8700}
(gdb) p *((GroupClause *) 0x400b8690)->entry->resdom
$108 = {type = T_Resdom, resno = 1, restype = 23, restypmod = -1,
  resname = 0x400b86e8 "b", reskey = 0, reskeyop = 0, resjunk = 0}
(gdb) p *(Var*)((GroupClause *) 0x400b8690)->entry->expr
$114 = {type = T_Var, varno = 4, varattno = 2, vartype = 23, vartypmod = -1,
  varlevelsup = 0, varnoold = 4, varoattno = 2}

and a target list like this:

(gdb) p *(TargetEntry*)0x400b8a70
$118 = {type = T_TargetEntry, resdom = 0x400b8a88, fjoin = 0x0,
  expr = 0x400b8ac8}
(gdb) p *((TargetEntry*)0x400b8a70)->resdom
$119 = {type = T_Resdom, resno = 1, restype = 23, restypmod = -1,
  resname = 0x400b8ab0 "count", reskey = 0, reskeyop = 0, resjunk = 0}
(gdb) p *(Aggref*)((TargetEntry*)0x400b8a70)->expr
$121 = {type = T_Aggref, aggname = 0x400b8af0 "count", basetype = 0,
  aggtype = 23, target = 0x400b8b08, aggno = 0, usenulls = 0 '\000'}
(gdb) p *(Var*)((Aggref*)((TargetEntry*)0x400b8a70)->expr)->target
$123 = {type = T_Var, varno = 4, varattno = 2, vartype = 23, vartypmod = -1,
  varlevelsup = 0, varnoold = 4, varoattno = 2}

which is all fine except that the two different expressions have been
given the same Resdom number (resno = 1 in both).  That confuses
make_groupPlan into thinking that they are the same expression, and
trouble ensues.

If I understand this stuff correctly, the rewriter should have been
careful to assign different Resdom numbers to distinct expressions
in the target and group-by lists of the rewritten query.  That's how
things look in an un-rewritten query, anyway.  So I think this is a
rewrite bug.

If you don't like that answer, it might be possible to change the
planner so that it doesn't put any faith in the Resdom numbers, but
uses equal() on the expr fields to decide whether target and group-by
entries are the same.  That would be a slower but probably much more
robust approach.  Jan, what do you think?

			regards, tom lane

Sat, 01 May 1999 19:51:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] views and group by (formerly: create view as selec 

I now get.  I am sure it instills confidence in our users:
	
	test=> CREATE TABLE t1 (a int4, b int4);
	CREATE
	test=> 
	test=> CREATE VIEW v1 AS SELECT b, count(b) FROM t1 GROUP BY b;
	CREATE
	test=> SELECT count FROM v1;
	ERROR:  union_planner: query is marked hasAggs, but I don't see any


> jwieck@debis.com (Jan Wieck) wrote:
> >       CREATE TABLE t1 (a int4, b int4);
> >       CREATE VIEW v1 AS SELECT b, count(b) FROM t1 GROUP BY b;
> >       SELECT count FROM v1;
> >       [ ... ker-boom ... ]
> 
> and I said I thought it was the same planner bug I was chasing in
> non-VIEW-using examples of GROUP BY.  It turns out it's not the same.
> In the above example, the breakage happens in rewrite before the
> planner ever sees it.  When make_groupPlan is called, it sees a
> GroupClause like this:
> 
> (gdb) p *((GroupClause *) 0x400b8690)->entry
> $107 = {type = T_TargetEntry, resdom = 0x400b86c0, fjoin = 0x0,
>   expr = 0x400b8700}
> (gdb) p *((GroupClause *) 0x400b8690)->entry->resdom
> $108 = {type = T_Resdom, resno = 1, restype = 23, restypmod = -1,
>   resname = 0x400b86e8 "b", reskey = 0, reskeyop = 0, resjunk = 0}
> (gdb) p *(Var*)((GroupClause *) 0x400b8690)->entry->expr
> $114 = {type = T_Var, varno = 4, varattno = 2, vartype = 23, vartypmod = -1,
>   varlevelsup = 0, varnoold = 4, varoattno = 2}
> 
> and a target list like this:
> 
> (gdb) p *(TargetEntry*)0x400b8a70
> $118 = {type = T_TargetEntry, resdom = 0x400b8a88, fjoin = 0x0,
>   expr = 0x400b8ac8}
> (gdb) p *((TargetEntry*)0x400b8a70)->resdom
> $119 = {type = T_Resdom, resno = 1, restype = 23, restypmod = -1,
>   resname = 0x400b8ab0 "count", reskey = 0, reskeyop = 0, resjunk = 0}
> (gdb) p *(Aggref*)((TargetEntry*)0x400b8a70)->expr
> $121 = {type = T_Aggref, aggname = 0x400b8af0 "count", basetype = 0,
>   aggtype = 23, target = 0x400b8b08, aggno = 0, usenulls = 0 '\000'}
> (gdb) p *(Var*)((Aggref*)((TargetEntry*)0x400b8a70)->expr)->target
> $123 = {type = T_Var, varno = 4, varattno = 2, vartype = 23, vartypmod = -1,
>   varlevelsup = 0, varnoold = 4, varoattno = 2}
> 
> which is all fine except that the two different expressions have been
> given the same Resdom number (resno = 1 in both).  That confuses
> make_groupPlan into thinking that they are the same expression, and
> trouble ensues.
> 
> If I understand this stuff correctly, the rewriter should have been
> careful to assign different Resdom numbers to distinct expressions
> in the target and group-by lists of the rewritten query.  That's how
> things look in an un-rewritten query, anyway.  So I think this is a
> rewrite bug.
> 
> If you don't like that answer, it might be possible to change the
> planner so that it doesn't put any faith in the Resdom numbers, but
> uses equal() on the expr fields to decide whether target and group-by
> entries are the same.  That would be a slower but probably much more
> robust approach.  Jan, what do you think?
> 
> 			regards, tom lane
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 10 May 1999 12:51:03 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] views and group by (formerly: create view as selec
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> I now get.  I am sure it instills confidence in our users:
> 	ERROR:  union_planner: query is marked hasAggs, but I don't see any

Yes :-(.  I've been waiting for Jan to respond to the issue --- I think
this is a rewriter problem, so I wanted to know whether he could do
anything with it.  (See my message "GROUP BY fixes committed" dated
02 May 1999 20:54:30 -0400.)

It'd be possible to work around this problem inside the planner, by not
believing what the rewriter says about either resnos or hasAggs, but
that seems like a kluge rather than a fix.

			regards, tom lane

Mon, 10 May 1999 13:31:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] views and group by (formerly: create view as selec 
>
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > I now get.  I am sure it instills confidence in our users:
> >  ERROR:  union_planner: query is marked hasAggs, but I don't see any
>
> Yes :-(.  I've been waiting for Jan to respond to the issue --- I think
> this is a rewriter problem, so I wanted to know whether he could do
> anything with it.  (See my message "GROUP BY fixes committed" dated
> 02 May 1999 20:54:30 -0400.)
>
> It'd be possible to work around this problem inside the planner, by not
> believing what the rewriter says about either resnos or hasAggs, but
> that seems like a kluge rather than a fix.

    Sorry - forgot about that one.

    I  think  the best place to check if the query has aggregates
    or not is at the beginning of the planner. The rewrite system
    is  recursive,  and  thus  the  check at the end of one cycle
    doesn't guarantee that it will still be true at  the  end  of
    all rewrites.

    OTOH  views  with  aggregates  are very buggy and introducing
    ton's of problems (as the other threads show). I'm  not  sure
    that  it was a good idea to make it partially working :-( for
    v6.4. There was only the advice that they shouldn't be  used.
    Now  it's  a released feature. More and more pressure for the
    subselecting RTE's which are IMHO the only way to solve  that
    all cleanly.

    The  other  issue  about the resno's is something that I must
    still search for in the  rewriter.  There  are  bad  problems
    where  it  seems to mangle up not only resno's, it also get's
    lost of  varlevelsup  somehow  and  producing  totally  wrong
    varno's  into  subselects.  Still  problems  coming  from the
    EXCEPT/INTERSECT patch I haven't found so far >:-{


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) #



Mon, 10 May 1999 20:01:24 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] views and group by (formerly: create view as selec