Hi,

    the  first step on views using aggregate was successful.  But
    I don't know enough about GROUP BY, HAVING and all the  other
    stuff.  So I need someone to assist me in getting the rewrite
    system to handle this kind of views properly.

    The patch below is only for hackers ON THIS  TODO  TOPIC,  it
    makes things temporary worse!!!

    What works with it:

        create table t1 (k int4);
        CREATE
        insert into t1 values (1);
        INSERT 18441 1
        insert into t1 values (2);
        INSERT 18442 1
        insert into t1 values (3);
        INSERT 18443 1

        create table t2 (a int4, k int4);
        CREATE
        insert into t2 values (1, 1);
        INSERT 18454 1
        insert into t2 values (2, 1);
        INSERT 18455 1
        insert into t2 values (3, 1);
        INSERT 18456 1
        insert into t2 values (4, 2);
        INSERT 18457 1
        insert into t2 values (5, 2);
        INSERT 18458 1

        create view v1 as
            select k, count(t2.a) from t1 where t1.k = t2.k
            group by k;
        CREATE

        select * from v1;
        k|count
        -+-----
        1|    3
        2|    2
        (2 rows)

    I  don't  know if it's right that no row with k=3 shows up. I
    had expected a row 'k=3 count=0'. But it's exactly  what  the
    select  statement  without  the  view  returns. So it's not a
    problem of the rewrite system any more.

    But doing

        select k from v1;

    still  crashes  the  backend  (though  somewhere   completely
    different).

    Before  going into details and fixing more things I must have
    some different view definitions and table settings (including
    nested  views  with and without aggregates) and what they are
    expected to output!

    And please some complicated select statements building  joins
    from tables and the views with GROUP BY etc. too if possible.

    As I don't know enough about what GROUP BY really should do I
    cannot work out all these test cases myself.


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


diff -c -r rewrite.old/rewriteHandler.c rewrite/rewriteHandler.c
*** rewrite.old/rewriteHandler.c	Mon Feb 23 18:09:44 1998
--- rewrite/rewriteHandler.c	Mon Feb 23 18:43:30 1998
***************
*** 354,359 ****
--- 354,366 ----
  	ChangeVarNodes(rule_qual, PRS2_CURRENT_VARNO + rt_length, rt_index, 0);
  	if (relation_level)
  	{
+ 		OffsetVarNodes((Node *)rule_action->groupClause, rt_length);
+ 		ChangeVarNodes((Node *)rule_action->groupClause,
+ 				   PRS2_CURRENT_VARNO + rt_length, rt_index, 0);
+ 		parsetree->groupClause = nconc(parsetree->groupClause,
+ 				   copyObject(rule_action->groupClause));
+ 		parsetree->hasAggs = rule_action->hasAggs;
+ 		parsetree->hasSubLinks = rule_action->hasSubLinks;
  		HandleViewRule(parsetree, rtable, rule_action->targetList, rt_index,
  					   modified);
  	}
Only in rewrite: rewriteHandler.c.swp
diff -c -r rewrite.old/rewriteManip.c rewrite/rewriteManip.c
*** rewrite.old/rewriteManip.c	Mon Feb 23 18:09:44 1998
--- rewrite/rewriteManip.c	Mon Feb 23 18:36:21 1998
***************
*** 79,84 ****
--- 79,91 ----
  				}
  			}
  			break;
+ 		case T_GroupClause:
+ 			{
+ 				GroupClause *grp = (GroupClause *) node;
+ 
+ 				OffsetVarNodes((Node *) grp->entry, offset);
+ 			}
+ 			break;
  		default:
  			/* ignore the others */
  			break;
***************
*** 140,145 ****
--- 147,159 ----
  
  				ChangeVarNodes((Node *)query->qual, old_varno, new_varno,
  										sublevels_up + 1);
+ 			}
+ 			break;
+ 		case T_GroupClause:
+ 			{
+ 				GroupClause *grp = (GroupClause *) node;
+ 
+ 				ChangeVarNodes((Node *) grp->entry, old_varno, new_varno, sublevels_up);
  			}
  			break;
  		default:

Mon, 23 Feb 1998 19:12:23 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Views on aggregates - need assistence
> 
> Hi,
> 
>     the  first step on views using aggregate was successful.  But
>     I don't know enough about GROUP BY, HAVING and all the  other
>     stuff.  So I need someone to assist me in getting the rewrite
>     system to handle this kind of views properly.
> 
>     The patch below is only for hackers ON THIS  TODO  TOPIC,  it
>     makes things temporary worse!!!
> 
>     What works with it:
> 
>         create table t1 (k int4);
>         CREATE
>         insert into t1 values (1);
>         INSERT 18441 1
>         insert into t1 values (2);
>         INSERT 18442 1
>         insert into t1 values (3);
>         INSERT 18443 1
> 
>         create table t2 (a int4, k int4);
>         CREATE
>         insert into t2 values (1, 1);
>         INSERT 18454 1
>         insert into t2 values (2, 1);
>         INSERT 18455 1
>         insert into t2 values (3, 1);
>         INSERT 18456 1
>         insert into t2 values (4, 2);
>         INSERT 18457 1
>         insert into t2 values (5, 2);
>         INSERT 18458 1
> 
>         create view v1 as
>             select k, count(t2.a) from t1 where t1.k = t2.k
>             group by k;
>         CREATE
> 
>         select * from v1;
>         k|count
>         -+-----
>         1|    3
>         2|    2
>         (2 rows)
> 
>     I  don't  know if it's right that no row with k=3 shows up. I
>     had expected a row 'k=3 count=0'. But it's exactly  what  the
>     select  statement  without  the  view  returns. So it's not a
>     problem of the rewrite system any more.

I think the join properly eliminates the k=3 row.  The aggregate happens
after the join.

For the aggregate gory details, see backend/optimizer/plan/planner.c. 
You will see how GROUP and Agg nodes are inserted above the tree to then
be handled by the executor.  Hopefully if the rewrite system works, the
change will be transparent to the optimizer, but you have to set the
query Aggreg fields properly when doing this.  You can also look at
parser/parse_agg.c to see how a normal aggregate coming in from the
parser is configured.

One other cool way of doing testing is to run the backend with -d3 debug
level, and then look at the post-rewrite trees for an aggregate query
and an aggregate from a view, and see if they are the same.  The output
shows almost all the fields in the query.

> 
>     But doing
> 
>         select k from v1;
> 
>     still  crashes  the  backend  (though  somewhere   completely
>     different).
> 
>     Before  going into details and fixing more things I must have
>     some different view definitions and table settings (including
>     nested  views  with and without aggregates) and what they are
>     expected to output!
> 
>     And please some complicated select statements building  joins
>     from tables and the views with GROUP BY etc. too if possible.
> 
>     As I don't know enough about what GROUP BY really should do I
>     cannot work out all these test cases myself.
> 
> 
> 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) #
> 
> 
> diff -c -r rewrite.old/rewriteHandler.c rewrite/rewriteHandler.c
> *** rewrite.old/rewriteHandler.c	Mon Feb 23 18:09:44 1998
> --- rewrite/rewriteHandler.c	Mon Feb 23 18:43:30 1998
> ***************
> *** 354,359 ****
> --- 354,366 ----
>   	ChangeVarNodes(rule_qual, PRS2_CURRENT_VARNO + rt_length, rt_index, 0);
>   	if (relation_level)
>   	{
> + 		OffsetVarNodes((Node *)rule_action->groupClause, rt_length);
> + 		ChangeVarNodes((Node *)rule_action->groupClause,
> + 				   PRS2_CURRENT_VARNO + rt_length, rt_index, 0);
> + 		parsetree->groupClause = nconc(parsetree->groupClause,
> + 				   copyObject(rule_action->groupClause));
> + 		parsetree->hasAggs = rule_action->hasAggs;
> + 		parsetree->hasSubLinks = rule_action->hasSubLinks;
>   		HandleViewRule(parsetree, rtable, rule_action->targetList, rt_index,
>   					   modified);
>   	}
> Only in rewrite: rewriteHandler.c.swp
> diff -c -r rewrite.old/rewriteManip.c rewrite/rewriteManip.c
> *** rewrite.old/rewriteManip.c	Mon Feb 23 18:09:44 1998
> --- rewrite/rewriteManip.c	Mon Feb 23 18:36:21 1998
> ***************
> *** 79,84 ****
> --- 79,91 ----
>   				}
>   			}
>   			break;
> + 		case T_GroupClause:
> + 			{
> + 				GroupClause *grp = (GroupClause *) node;
> + 
> + 				OffsetVarNodes((Node *) grp->entry, offset);
> + 			}
> + 			break;
>   		default:
>   			/* ignore the others */
>   			break;
> ***************
> *** 140,145 ****
> --- 147,159 ----
>   
>   				ChangeVarNodes((Node *)query->qual, old_varno, new_varno,
>   										sublevels_up + 1);
> + 			}
> + 			break;
> + 		case T_GroupClause:
> + 			{
> + 				GroupClause *grp = (GroupClause *) node;
> + 
> + 				ChangeVarNodes((Node *) grp->entry, old_varno, new_varno, sublevels_up);
>   			}
>   			break;
>   		default:
> 
> 


-- 
Bruce Momjian
maillist@candle.pha.pa.us

Mon, 23 Feb 1998 13:49:23 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Views on aggregates - need assistence

Bruce wrote:
> >         create view v1 as
> >             select k, count(t2.a) from t1 where t1.k = t2.k
> >             group by k;
> >         CREATE
> >
> >         select * from v1;
> >         k|count
> >         -+-----
> >         1|    3
> >         2|    2
> >         (2 rows)
> >
> >     I  don't  know if it's right that no row with k=3 shows up. I
> >     had expected a row 'k=3 count=0'. But it's exactly  what  the
> >     select  statement  without  the  view  returns. So it's not a
> >     problem of the rewrite system any more.
>
> I think the join properly eliminates the k=3 row.  The aggregate happens
> after the join.

   Then it's OK.

>
> For the aggregate gory details, see backend/optimizer/plan/planner.c.
> You will see how GROUP and Agg nodes are inserted above the tree to then
> be handled by the executor.  Hopefully if the rewrite system works, the
> change will be transparent to the optimizer, but you have to set the
> query Aggreg fields properly when doing this.  You can also look at
> parser/parse_agg.c to see how a normal aggregate coming in from the
> parser is configured.
>
> One other cool way of doing testing is to run the backend with -d3 debug
> level, and then look at the post-rewrite trees for an aggregate query
> and an aggregate from a view, and see if they are the same.  The output
> shows almost all the fields in the query.

    That's  exactly how I saw that the rewritten parsetree missed
    the groupClause from the views select and  the  hasAgg  flag,
    and  what  told me that OffsetVarNodes() and ChangeVarNodes()
    didn't handle the T_GroupClause :-)

    Anyway - the rewrite system doesn't handle the  view  queries
    sortClause,  havingQual  and unionClause either. And I really
    think doing all this in one step is better  than  groupClause
    now and the others later. But doing all is way too much to be
    done properly for 6.3.  So I ask for moving all these  issues
    into the 6.4 TODO.


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, 23 Feb 1998 20:41:17 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Views on aggregates - need assistence
>     That's  exactly how I saw that the rewritten parsetree missed
>     the groupClause from the views select and  the  hasAgg  flag,
>     and  what  told me that OffsetVarNodes() and ChangeVarNodes()
>     didn't handle the T_GroupClause :-)

Yep, the fact the switch defaults to nothing can be a problem.  Some
day, I was going to look at all the switch defaults to make sure
skipping it is really the proper thing to do, rather than elog out.


>     Anyway - the rewrite system doesn't handle the  view  queries
>     sortClause,  havingQual  and unionClause either. And I really
>     think doing all this in one step is better  than  groupClause
>     now and the others later. But doing all is way too much to be
>     done properly for 6.3.  So I ask for moving all these  issues
>     into the 6.4 TODO.

Actually, it does handle unions of views, but not views of unions. 
Informix doesn't support it either, and I don't know what the other
dbms's do, but I think I am going to find out soon from someone.  :-)

I will move it to the TODO list.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)


Mon, 23 Feb 1998 14:46:10 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Views on aggregates - need assistence
On Mon, 23 Feb 1998, Bruce Momjian wrote:

> Actually, it does handle unions of views, but not views of unions. 
> Informix doesn't support it either, and I don't know what the other
> dbms's do, but I think I am going to find out soon from someone.  :-)

	What exactly would you like me to try here? *raised eyebrow*



Mon, 23 Feb 1998 15:02:41 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Views on aggregates - need assistence
> 
> On Mon, 23 Feb 1998, Bruce Momjian wrote:
> 
> > Actually, it does handle unions of views, but not views of unions. 
> > Informix doesn't support it either, and I don't know what the other
> > dbms's do, but I think I am going to find out soon from someone.  :-)
> 
> 	What exactly would you like me to try here? *raised eyebrow*
> 
> 
> 


	create view as
		select oid from pg_user union select oid from pg_class

or something like that.  That will not work under PostgreSQL.  But you
can use views as part of a union:

	select oid from view1
	union
	select oid from view2


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Mon, 23 Feb 1998 15:05:14 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Views on aggregates - need assistence
On Mon, 23 Feb 1998, Bruce Momjian wrote:

> > 
> > On Mon, 23 Feb 1998, Bruce Momjian wrote:
> > 
> > > Actually, it does handle unions of views, but not views of unions. 
> > > Informix doesn't support it either, and I don't know what the other
> > > dbms's do, but I think I am going to find out soon from someone.  :-)
> > 
> > 	What exactly would you like me to try here? *raised eyebrow*
> > 
> > 
> > 
> 
> 
> 	create view as
> 		select oid from pg_user union select oid from pg_class
> 
> or something like that.  That will not work under PostgreSQL.  But you
> can use views as part of a union:
> 
> 	select oid from view1
> 	union
> 	select oid from view2

You mean like:

SQL> select * from one
  2  ;

         A
----------
         3
         4
         2

SQL> create table two ( B int );

Table created.

SQL> insert into two values ( 1 );

1 row created.

SQL> insert into two values ( 2 ) ;

1 row created.

SQL> insert into two values ( 3 ) ;

1 row created.

SQL> create view v1 as select A from one union select B from two;

View created.

SQL> select * from v1;

         A
----------
         1
         2
         3
         4




Mon, 23 Feb 1998 15:22:22 -0500 (EST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Views on aggregates - need assistence
> > can use views as part of a union:
> > 
> > 	select oid from view1
> > 	union
> > 	select oid from view2
> 
> You mean like:
> 
> SQL> select * from one
>   2  ;
> 
>          A
> ----------
>          3
>          4
>          2
> 
> SQL> create table two ( B int );
> 
> Table created.
> SQL> create view v1 as select A from one union select B from two;
> 
> View created.
> 
> SQL> select * from v1;
> 
>          A
> ----------
>          1
>          2
>          3
>          4
> 

Yep, we can't currently do that.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Mon, 23 Feb 1998 15:25:48 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Views on aggregates - need assistence
Jan Wieck wrote:
> 
> Hi,
> 
>     the  first step on views using aggregate was successful.  But
>     I don't know enough about GROUP BY, HAVING and all the  other
>     stuff.  So I need someone to assist me in getting the rewrite
>     system to handle this kind of views properly.

...

> 
>     Before  going into details and fixing more things I must have
>     some different view definitions and table settings (including
>     nested  views  with and without aggregates) and what they are
>     expected to output!
> 
>     And please some complicated select statements building  joins
>     from tables and the views with GROUP BY etc. too if possible.

create view v as select x, sum(y) as sy from A group by x;
select * from B, V where B.z = V.sy;

- how can we handle this (aggregates in WHERE) ?
It seems that current VIEW implementation using RULEs has
unresolvable problems :(

Vadim

Tue, 24 Feb 1998 09:29:25 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] Views on aggregates - need assistence

Thank you for addressing this issue!  It has been bugging me for a
while.  Usually I just select into a new table and select from that
(but yes, it is multiple queries).  Normally I want to do something
like:

select bar,count(a.oid) as c from a,b where a.ab = b.bar and c > 1;

This actually seems to be a different issue with more complicated
unresolvable (?) problems, because you want a pre-result (per
combination of instances matched) where and a result where (per result
tuple)..  Is this possible to do using subqueries?  I'll try to find out.	

This might be totally unrelated, actually.  I do not know enough about
view system to understand unresolvable conflicts.

--brett


On Tue, 24 February 1998, at 09:29:25, Vadim B. Mikheev wrote:

> create view v as select x, sum(y) as sy from A group by x;
> select * from B, V where B.z = V.sy;
> 
> - how can we handle this (aggregates in WHERE) ?
> It seems that current VIEW implementation using RULEs has
> unresolvable problems :(
> 
> Vadim

Mon, 23 Feb 1998 20:59:10 -0800
Brett McCormick <brett@work.chicken.org>
Re: [HACKERS] Views on aggregates - need assistence
Brett McCormick wrote:
> 
> Thank you for addressing this issue!  It has been bugging me for a
> while.  Usually I just select into a new table and select from that
> (but yes, it is multiple queries).  Normally I want to do something
> like:
> 
> select bar,count(a.oid) as c from a,b where a.ab = b.bar and c > 1;
                                                               ^^^^^
This is what HAVING is for (unimplemented, yet).

> 
> This actually seems to be a different issue with more complicated
> unresolvable (?) problems, because you want a pre-result (per
> combination of instances matched) where and a result where (per result
> tuple)..  Is this possible to do using subqueries?  I'll try to find out.

No, if you really want to see count in output. If you would be happy
with bar only then this could help:

select bar from b where 1 < (select count(*) from a where a.ab = b.bar);

(Having HAVING would be better, of 'course :)

> This might be totally unrelated, actually.  I do not know enough about
> view system to understand unresolvable conflicts.

You could CREATE VIEW V as select bar,count(a.oid) as c from a,b 
where a.ab = b.bar group by bar;
and then just select * from v where c > 1.

Vadim

Tue, 24 Feb 1998 12:19:53 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] Views on aggregates - need assistence
> >     Before  going into details and fixing more things I must have
> >     some different view definitions and table settings (including
> >     nested  views  with and without aggregates) and what they are
> >     expected to output!
> >
> >     And please some complicated select statements building  joins
> >     from tables and the views with GROUP BY etc. too if possible.
>
> create view v as select x, sum(y) as sy from A group by x;
> select * from B, V where B.z = V.sy;
>
> - how can we handle this (aggregates in WHERE) ?
> It seems that current VIEW implementation using RULEs has
> unresolvable problems :(

Well, there may be a subset of the possible cases which could work?


Tue, 24 Feb 1998 06:49:56 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Views on aggregates - need assistence
>     Anyway - the rewrite system doesn't handle the  view  queries
>     sortClause,  havingQual  and unionClause either. And I really
>     think doing all this in one step is better  than  groupClause
>     now and the others later. But doing all is way too much to be
>     done properly for 6.3.  So I ask for moving all these  issues
>     into the 6.4 TODO.

Moved to TODO.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Wed, 25 Feb 1998 10:36:36 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Views on aggregates - need assistence
Kello!

I have a recusive relation, like this:
=> create table re( name text, oid parent);
and needed the name from a tuple, and the name of all parents...
i tryd some, like:
=> create function fullname(oid) returns text
-> as 'select fullname(re.parent) || re.name from re where re.oid=$1;'
-> language 'sql'; 
that isn't work. of course ;(

 i can get a name, and the parents oid, and the oid of parents parent etc...
 with _MORE_ queries. 

for example: 
=> select oid,* from re;
oid | name | parent
----+------+--------
5000|one   | (NULL or 0) 
5001|two   | 5000
5002|3d    | 5000
5003|4d    | 5002
i now the oid:5003. i need the name: "one/two/4d"
must i send thees queries to the backend? (in pseudo code):
$curr=5003
$fulln=''
while $curr do
  => SELECT name, parent FROM re WHERE oid=$curr;
  $fulln='$fulln/$name'; $curr=$parent;
enddo
so i liked send _ONLY_ 5003, and reveive in a trice the full name.
this time i can't this ;(

sprintf         ("`-''-/").___..--''"`-._            Error In
("%|s",          `6_ 6  )   `-.  (     ).`-.__.`)    Loading Object
"Petike"         (_Y_.)'  ._   )  `._ `. ``-..-'     line:3
/* Neko */     _..`--'_..-_/  /--'_.' ,'             Before /*Neko*/
 );           (il),-''  (li),'  ((!.-'	 see: http://lsc.kva.hu


Mon, 16 Mar 1998 15:37:50 +0100 (NFT)
"Vazsonyi Peter[ke]" <neko@kornel.szif.hu>
recursive seek...?