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

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

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)

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*

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)

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

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)

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

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:

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

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?

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

create view testv as
select * from dual
union all
select * from dual;

Works in Oracle, I think this is great, I have been missing this feature in
Informix.
It can for example solve the large table problem (create several tables and
a union view).
But it is definitely a goody that could wait a little on the TODO list.

Andreas

Works in Oracle, I think this is great, I have been missing this feature in
Informix.
It can for example solve the large table problem (create several tables and
a union view).
But it is definitely a goody that could wait a little on the TODO list.

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

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)

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