Update of /cvsroot/monetdb/sql/src/server
In directory 23jxhf1.ch3.sourceforge.com:/tmp/cvs-serv11174/src/server
Modified Files:
rel_exp.mx rel_optimizer.mx
Log Message:
introduced a avg-> sum/cnt optimize step.
Currently disabled for decimal input columns (the current *100/100 etc
stuff breaks it). This should also be moved to the optimizer, later.
U rel_exp.mx
Index: rel_exp.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_exp.mx,v
retrieving revision 1.21
retrieving revision 1.22
diff -u -d -r1.21 -r1.22
--- rel_exp.mx 7 Jan 2009 14:19:29 -0000 1.21
+++ rel_exp.mx 13 Jan 2009 14:47:54 -0000 1.22
@@ -52,6 +52,7 @@
extern sql_exp * exp_atom( atom *a);
extern sql_exp * exp_atom_bool(int b);
extern sql_exp * exp_atom_int(int i);
+extern sql_exp * exp_atom_wrd(wrd w);
extern sql_exp * exp_atom_str(str s, sql_subtype *st);
extern sql_exp * exp_atom_clob(str s);
extern sql_exp * exp_atom_ref(int i, sql_subtype *tpe);
@@ -263,6 +264,15 @@
}
sql_exp *
+exp_atom_wrd(wrd w)
+{
+ sql_subtype it;
+
+ sql_find_subtype(&it, "wrd", 19, 0);
+ return exp_atom(atom_int(&it, (int)w ));
+}
+
+sql_exp *
exp_atom_str(str s, sql_subtype *st)
{
return exp_atom(atom_string(st, _strdup(s), 1 ));
U rel_optimizer.mx
Index: rel_optimizer.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_optimizer.mx,v
retrieving revision 1.36
retrieving revision 1.37
diff -u -d -r1.36 -r1.37
--- rel_optimizer.mx 7 Jan 2009 14:19:29 -0000 1.36
+++ rel_optimizer.mx 13 Jan 2009 14:47:54 -0000 1.37
@@ -107,10 +107,6 @@
case op_groupby:
if (!rel->exps)
break;
- /* ??
- } else if (rname) {
- alias = exps_bind_column2(rel->exps, rname, name);
- */
alias = exps_bind_column(rel->exps, name);
break;
case op_insert:
@@ -1270,6 +1266,172 @@
return rel;
}
+static sql_exp *
+rel_find_aggr_exp( sql_rel *rel, list *exps, sql_exp *e, char *name)
+{
+ list *ea = e->l;
+ sql_exp *a = NULL, *eae;
+ node *n;
+
+ if (list_length(ea) != 1)
+ return NULL;
+ eae = ea->h->data;
+ if (eae->type != e_column)
+ return NULL;
+ for( n = exps->h; n; n = n->next) {
+ a = n->data;
+
+ if (a->type == e_aggr) {
+ sql_subaggr *af = a->f;
+ list *aa = a->l;
+
+ /* TODO handle distinct and no-nil etc ! */
+ if (strcmp(af->aggr->base.name, name) == 0 &&
+ /* TODO handle count (has no args!!) */
+ aa && list_length(aa) == 1) {
+ sql_exp *aae = aa->h->data;
+
+ if (eae->type == e_column &&
+ ((!aae->l && !eae->l) ||
+ (aae->l && eae->l &&
+ strcmp(aae->l, eae->l) == 0)) &&
+ (aae->r && eae->r &&
+ strcmp(aae->r, eae->r) == 0))
+ return exp_column(rel_name(rel),
exp_name(a), exp_subtype(a), a->card, has_nil(a), is_intern(a));
+ }
+ }
+ }
+ return NULL;
+}
+
+static sql_subfunc *
+find_func( mvc *sql, char *name, list *exps )
+{
+ list * l = list_create(NULL);
+ node *n;
+
+ for(n = exps->h; n; n = n->next)
+ append(l, exp_subtype(n->data));
+ return sql_bind_func_(sql->session->schema, name, l);
+}
+
+/* rewrite avg into sum/count */
+static sql_rel *
+rel_avg2sum_count(int *changes, mvc *sql, sql_rel *rel)
+{
+ *changes = 0;
+ if (is_groupby(rel->op)) {
+ list *pexps, *nexps = new_exp_list(), *avgs = new_exp_list();
+ node *m, *n;
+
+ /* Find all avg's */
+ for (m = rel->exps->h; m; m = m->next) {
+ sql_exp *e = exp_dup(m->data);
+
+ if (e->type == e_aggr) {
+ sql_subaggr *a = e->f;
+
+ if (strcmp(a->aggr->base.name, "avg") == 0) {
+ list *exps = e->l;
+ sql_exp *ae = exps->h->data;
+
+ /* FOR NOW ESCAPE ON AVG ON DECIMALS,
+ we need a rewrite or DEC's in
+ the optimizer to solve this problem*/
+ if (exp_subtype(ae)->type->eclass ==
EC_DEC)
+ return rel;
+ append(avgs, e);
+ continue;
+ }
+ }
+ append(nexps, e);
+ }
+ if (!list_length(avgs))
+ return rel;
+
+ /* For each avg, find count and sum */
+ for (m = avgs->h; m; m = m->next) {
+ list *args;
+ sql_exp *avg = m->data, *navg, *cond, *cnt_d;
+ sql_exp *cnt = rel_find_aggr_exp(rel, nexps, avg,
"count");
+ sql_exp *sum = rel_find_aggr_exp(rel, nexps, avg,
"sum");
+ sql_subfunc *div, *ifthen, *cmp;
+ sql_subtype *dbl_t;
+
+ if (!cnt) {
+ list *l = avg->l;
+ sql_subaggr *cf =
sql_bind_aggr(sql->session->schema, "count", exp_subtype(l->h->data));
+ sql_exp *e = exp_aggr(avg->l, cf,
need_distinct(avg), has_no_nil(avg), avg->card, has_nil(avg));
+
+ exp_label(e, ++sql->label);
+ append(nexps, e);
+ cnt = exp_column(NULL, exp_name(e),
exp_subtype(e), e->card, has_nil(e), is_intern(e));
+ }
+ if (!sum) {
+ list *l = avg->l;
+ sql_subaggr *sf =
sql_bind_aggr(sql->session->schema, "sum", exp_subtype(l->h->data));
+ sql_exp *e = exp_aggr(avg->l, sf,
need_distinct(avg), has_no_nil(avg), avg->card, has_nil(avg));
+
+ exp_label(e, ++sql->label);
+ append(nexps, e);
+ sum = exp_column(NULL, exp_name(e),
exp_subtype(e), e->card, has_nil(e), is_intern(e));
+ }
+ /* create new sum/cnt exp */
+
+ /* For now we always convert to dbl */
+ dbl_t = sql_bind_localtype("dbl");
+ cnt_d = exp_convert(cnt, exp_subtype(cnt), dbl_t);
+ sum = exp_convert(sum, exp_subtype(sum), dbl_t);
+
+ args = new_exp_list();
+ append(args, cnt);
+ append(args, exp_atom_wrd(0));
+ cmp = find_func(sql, "=", args);
+ assert(cmp);
+ cond = exp_op(args, cmp);
+
+ args = new_exp_list();
+ append(args, cond);
+ append(args, exp_atom(atom_general(dbl_t, NULL, 0)));
+ append(args, cnt_d);
+ ifthen = find_func(sql, "ifthenelse", args);
+ assert(ifthen);
+ cnt_d = exp_op(args, ifthen);
+
+ args = new_exp_list();
+ append(args, sum);
+ append(args, cnt_d);
+ div = find_func(sql, "sql_div", args);
+ assert(div);
+ navg = exp_op(args, div);
+
+ exp_setname(navg, avg->rname, avg->name );
+ m->data = navg;
+ }
+ pexps = new_exp_list();
+ for (m = rel->exps->h, n = avgs->h; m; m = m->next) {
+ sql_exp *e = exp_dup(m->data);
+
+ if (e->type == e_aggr) {
+ sql_subaggr *a = e->f;
+
+ if (strcmp(a->aggr->base.name, "avg") == 0) {
+ sql_exp *avg = exp_dup(n->data);
+
+ append(pexps, avg);
+ n = n->next;
+ continue;
+ }
+ }
+ append(pexps, exp_column(e->rname, exp_name(e),
exp_subtype(e), e->card, has_nil(e), is_intern(e)));
+ }
+ rel->exps = nexps;
+ rel = rel_project(rel, pexps);
+ *changes = 1;
+ }
+ return rel;
+}
+
/* Pushing projects up the tree. Done very early in the optimizer.
* Makes later steps easier.
*/
@@ -1794,6 +1956,9 @@
if (gp.cnt[op_join] && gp.cnt[op_groupby])
rel = rewrite(sql, rel, &rel_push_join_down);
+ if (gp.cnt[op_groupby])
+ rel = rewrite(sql, rel, &rel_avg2sum_count);
+
if (gp.cnt[op_join] || gp.cnt[op_left])
rel = rewrite(sql, rel, &rel_join_order);
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
Monetdb-sql-checkins mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
