Update of /cvsroot/monetdb/sql/src/server
In directory sc8-pr-cvs16.sourceforge.net:/tmp/cvs-serv17548
Modified Files:
rel_bin.mx rel_optimizer.mx rel_select.mx sql_mvc.mx
sql_select.mx
Log Message:
cleanup scale fixing in functions, this is now stored inside a new field
in the sql_subfunc structure (ie no longer in the sql_func result type
scale field)
rel_optimizer now pushes selects also through renaming projections
rel_optimizer now orders the select expressions on reducing quality
rel_bin now generates (like bin version) stmt_releqjoin and stmt_reljoin
(which are then by sql_rel2bin optimized, ie using hash for multi attribute
joins)
U sql_mvc.mx
Index: sql_mvc.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_mvc.mx,v
retrieving revision 1.202
retrieving revision 1.203
diff -u -d -r1.202 -r1.203
--- sql_mvc.mx 5 Jun 2008 09:23:22 -0000 1.202
+++ sql_mvc.mx 20 Jun 2008 20:50:31 -0000 1.203
@@ -772,7 +772,7 @@
sql_table *
mvc_bind_table(mvc *m, sql_schema *s, char *tname)
{
- sql_table *t;
+ sql_table *t = NULL;
if (!s) { /* Declared tables during query compilation have no schema */
sql_subtype *tpe = stack_find_type(m, tname);
U rel_select.mx
Index: rel_select.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_select.mx,v
retrieving revision 1.77
retrieving revision 1.78
diff -u -d -r1.77 -r1.78
--- rel_select.mx 17 Jun 2008 10:35:08 -0000 1.77
+++ rel_select.mx 20 Jun 2008 20:50:31 -0000 1.78
@@ -2572,14 +2572,14 @@
}
}
if (f) {
- if (f->func->res.scale == SCALE_FIX) {
+ if (f->func->fix_scale == SCALE_FIX) {
l = exp_fix_scale(sql, t2, l, 0, 0);
r = exp_fix_scale(sql, t1, r, 0, 0);
- } else if (f->func->res.scale == SCALE_SUB) {
+ } else if (f->func->fix_scale == SCALE_SUB) {
l = exp_scale_algebra(sql, f, l, r);
- } else if (f->func->res.scale == SCALE_ADD) {
+ } else if (f->func->fix_scale == SCALE_ADD) {
l = exp_sum_scales(sql, f, l, r);
- } else if (f->func->res.scale == DIGITS_ADD) {
+ } else if (f->func->fix_scale == DIGITS_ADD) {
f->res.digits = t1->digits + t2->digits;
}
return exp_binop(l, r, f);
@@ -2621,14 +2621,14 @@
t2 = exp_subtype(r);
f = sql_bind_func(s, fname, t1, t2);
if (f) {
- if (f->func->res.scale == SCALE_FIX) {
+ if (f->func->fix_scale == SCALE_FIX) {
l = exp_fix_scale(sql, t2, l, 0, 0);
r = exp_fix_scale(sql, t1, r, 0, 0);
- } else if (f->func->res.scale == SCALE_SUB) {
+ } else if (f->func->fix_scale == SCALE_SUB) {
l = exp_scale_algebra(sql, f, l, r);
- } else if (f->func->res.scale == SCALE_ADD) {
+ } else if (f->func->fix_scale == SCALE_ADD) {
l = exp_sum_scales(sql, f, l, r);
- } else if (f->func->res.scale == DIGITS_ADD) {
+ } else if (f->func->fix_scale == DIGITS_ADD) {
f->res.digits = digits;
}
exp_destroy(ol);
U rel_optimizer.mx
Index: rel_optimizer.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_optimizer.mx,v
retrieving revision 1.16
retrieving revision 1.17
diff -u -d -r1.16 -r1.17
--- rel_optimizer.mx 14 May 2008 20:18:24 -0000 1.16
+++ rel_optimizer.mx 20 Jun 2008 20:50:30 -0000 1.17
@@ -762,70 +762,11 @@
return rel;
}
-#if 0
-static sql_exp *
-exp_remove_alias(sql_exp *e, list *exps)
-{
- sql_exp *r = NULL;
-
- switch(e->type) {
- case e_column:
- if (e->l)
- r = exps_bind_column2(exps, e->l, e->r);
- else
- r = exps_bind_column(exps, e->r);
- if (r) {
- r = exp_dup(r);
- exp_destroy(e);
- return r;
- }
- break;
- case e_cmp:
- e->l = exp_remove_alias(e->l, exps);
- e->r = exp_remove_alias(e->r, exps);
- if (e->f)
- e->f = exp_remove_alias(e->f, exps);
- return e;
- case e_convert:
- e->l = exp_remove_alias(e->l, exps);
- return e;
- case e_aggr:
- case e_func: {
- list *l = e->l;
- if (l) {
- node *n;
- list *nl = new_exp_list();
- for(n=l->h; n; n=n->next) {
- sql_exp *arg = n->data;
-
- arg = exp_dup(exp_remove_alias(arg, exps));
- append(nl, arg);
- }
- list_destroy(l);
- e->l = nl;
- }
- } return e;
- case e_atom:
- return e;
- }
- return NULL;
-}
-#endif
-
-/*
-static sql_exp *
-exps_remove_alias(sql_exp *e, list *exps)
-{
- return exp_dup(exp_remove_alias(e, exps));
-}
-*/
-
-#if 0
/* push the expression down, ie translate colum references from relation f
into expression of relation t */
static sql_exp *
exp_push_down(mvc *sql, sql_exp *e, sql_rel *f, sql_rel *t)
{
- sql_exp *ne = NULL;
+ sql_exp *ne = NULL, *l, *r, *r2;
switch(e->type) {
case e_column:
@@ -839,25 +780,35 @@
ne = exps_bind_column(f->exps, e->r);
assert(ne);
e = NULL;
- if (ne->name && strcmp(ne->r, ne->name) == 0){ /* no alias */
+ if (ne->name && ne->r && strcmp(ne->r, ne->name) == 0){
if (ne->l) {
e = rel_bind_column2(sql, t, ne->l, ne->r);
if (!e && t->name && strcmp(ne->l, t->name) ==
0)
e = rel_bind_column(sql, t, ne->r);
}
}
- if (!e)
+ if (!e && ne->r)
e = rel_bind_column(sql, t, ne->r);
- assert(e);
- //return exp_dup(e);
return e;
case e_cmp:
- if (e->f)
- return exp_compare2( exp_push_down(sql, e->l, f, t),
exp_push_down(sql, e->r, f, t), exp_push_down(sql, e->f, f, t), e->flag);
- else
- return exp_compare( exp_push_down(sql, e->l, f, t),
exp_push_down(sql, e->r, f, t), e->flag);
+ l = exp_push_down(sql, e->l, f, t);
+ r = exp_push_down(sql, e->r, f, t);
+ if (e->f) {
+ r2 = exp_push_down(sql, e->f, f, t);
+ if (l && r && r2)
+ return exp_compare2( l, r, r2, e->flag);
+ } else if (l && r) {
+ return exp_compare( l, r, e->flag);
+ }
+ exp_destroy(l);
+ exp_destroy(r);
+ return NULL;
case e_convert:
- return exp_convert( exp_push_down(sql, e->l, f, t),
exp_fromtype(e), exp_totype(e));
+ l = exp_push_down(sql, e->l, f, t);
+ if (l)
+ return exp_convert( l, exp_fromtype(e), exp_totype(e));
+ exp_destroy(l);
+ return NULL;
case e_aggr:
case e_func: {
list *l = e->l, *nl = NULL;
@@ -872,31 +823,52 @@
sql_exp *arg = n->data;
arg = exp_push_down(sql, arg, f, t);
+ if (!arg) {
+ list_destroy(nl);
+ return NULL;
+ }
append(nl, arg);
}
}
if (e->type == e_func)
return exp_op(nl, sql_dup_func(e->f));
- return exp_aggr(nl, sql_dup_aggr(e->f), e->flag&1,
(e->flag>>1), e->card);
+ else
+ return exp_aggr(nl, sql_dup_aggr(e->f), e->flag&1,
(e->flag>>1), e->card);
}
case e_atom:
return exp_dup(e);
}
return NULL;
}
-#endif
sql_rel *
rel_push_select_down(mvc *sql, sql_rel *rel)
{
- //sql_rel *rr, *rl;
-
-(void)sql;
+ (void)sql;
+ if ((is_join(rel->op) || is_project(rel->op) || rel->op == op_topn) &&
rel->l) {
+ sql_rel *l = rel->l;
+ if (l->op == op_select &&
+ (!l->exps || list_length(l->exps) == 0)) {
+ rel->l = l->l;
+ l->l = NULL;
+ rel_destroy(l);
+ }
+ }
+ if ((is_join(rel->op) || is_set(rel->op)) && rel->r) {
+ sql_rel *r = rel->r;
+ if (r->op == op_select &&
+ (!r->exps || list_length(r->exps) == 0)) {
+ rel->r = r->l;
+ r->l = NULL;
+ rel_destroy(r);
+ }
+ }
if (rel->op == op_select && rel->l) {
+ list *exps = rel->exps;
sql_rel *r = rel->l;
- // node *n;
+ node *n;
- assert(rel->exps);
+ assert(exps);
switch(r->op) {
case op_basetable:
@@ -917,7 +889,7 @@
/* for each exp check if we can push it left or right */
/*
- for (n=rel->exps->h; n; n = n->next) {
+ for (n=exps->h; n; n = n->next) {
sql_exp *e = n->data;
if (e->type == e_cmp) {
@@ -931,7 +903,7 @@
} else {
nr =rel_push_select(r, le, re);
}
- assert(nr==r);
+ //assert(nr==r);
(void)nr;
}
}
@@ -951,7 +923,7 @@
r = rel_dup(rel->l);
rl = rel_select(rel_dup(r->l), NULL);
rr = rel_select(rel_dup(r->r), NULL);
- for (n=rel->exps->h; n; n = n->next) {
+ for (n=exps->h; n; n = n->next) {
sql_exp *le, *re, *e = n->data;
*/
@@ -959,6 +931,7 @@
/*
le = exp_push_down(sql, e, r, r->l);
re = exp_push_down(sql, e, r, r->r);
+
rel_select_add_exp(rl, exp_dup(le));
rel_select_add_exp(rr, exp_dup(re));
}
@@ -987,34 +960,37 @@
/* here we need to fix aliases */
-// r = r->l;
+ rel->exps = new_exp_list();
/* for each exp check if we can rename it */
-/*
- for (n=rel->exps->h; n; n = n->next) {
- sql_exp *e = n->data;
-
+ for (n = exps->h; n; n = n->next) {
+ sql_exp *e = n->data, *ne = NULL, *le, *re;
+ sql_rel *prj = r, *nr, *nl;
+
+ /* sometimes we also have functions in the
expression list (TODO change them to e_cmp (predicates like (1=0))) */
if (e->type == e_cmp) {
- sql_exp *le, *re;
- sql_rel *nr, *p = rel->l;
-
- e = exps_remove_alias(e, p->exps);
- le = e->l;
- re = e->r;
+ ne = exp_push_down(sql, e, prj, prj->l);
+ /* can we move it down */
+ if (ne && ne != e) {
+ nr = prj->l;
+ } else {
+ nr = rel;
+ exp_destroy(ne);
+ ne = exp_dup(e);
+ }
+ le = ne->l;
+ re = ne->r;
if (re->card >= CARD_AGGR) {
- nr = rel_push_join(r, le, re,
e);
+ nl = rel_push_join(nr, le, re,
ne);
} else {
- nr = rel_push_select(r, le, re);
+ nl = rel_push_select(nr, le,
ne);
}
- assert(nr==r);
- (void)nr;
+ if (nl != nr)
+ prj->l = nl;
+ } else {
+ list_append(rel->exps, exp_dup(e));
}
}
- r = rel->l;
- rel->l = NULL;
- rel_destroy( rel );
- rel = r;
- rel_print(sql, rel, 0);
-*/
+ list_destroy(exps);
return rel;
case op_groupby:
@@ -1175,6 +1151,51 @@
return rel;
}
+/* TODO CSE */
+static list *
+exp_merge(list *exps)
+{
+ node *n, *m;
+ for (n=exps->h; n && n->next; n = n->next) {
+ sql_exp *e = n->data;
+ //sql_exp *le = e->l;
+ sql_exp *re = e->r;
+
+ assert(e->type == e_cmp);
+ /* only look for gt, gte, lte, lt */
+ if (re->card == CARD_ATOM && e->flag < cmp_equal) {
+ for (m=n->next; m; m = m->next) {
+ sql_exp *f = m->data;
+ //sql_exp *lf = f->l;
+ sql_exp *rf = f->r;
+
+ assert(f->type == e_cmp);
+ if (rf->card == CARD_ATOM && e->flag <
cmp_equal) {
+ //printf("possible candidate\n");
+ }
+ }
+ }
+ }
+ return exps;
+}
+
+static sql_rel *
+rel_select_order(mvc *sql, sql_rel *rel)
+{
+ (void)sql;
+ if (rel->op == op_select && rel->exps && list_length(rel->exps)>1) {
+ list *exps = NULL;
+
+#ifdef DEBUG
+ rel_print(sql, rel, 0);
+#endif
+ exps = list_sort(rel->exps, (fkeyvalue)&exp_keyvalue,
(fdup)&exp_dup);
+ list_destroy(rel->exps);
+ rel->exps = exp_merge(exps);
+ }
+ return rel;
+}
+
static sql_rel *
rewrite(mvc *sql, sql_rel *rel, rewrite_fptr rewriter)
{
@@ -1235,16 +1256,18 @@
(for example common rels, with only one different expression) */
/* TODO common sub relation/expression optimizer */
-/*
if (gp.cnt[op_select])
rel = rewrite(sql, rel, &rel_push_select_down);
-*/
+
if (gp.cnt[op_select])
rel = rewrite(sql, rel, &rel_select_use_index);
if (gp.cnt[op_join])
rel = rewrite(sql, rel, &rel_join_order);
+ if (gp.cnt[op_select])
+ rel = rewrite(sql, rel, &rel_select_order);
+
#ifdef DEBUG
rel_print(sql, rel, 0);
#endif
U rel_bin.mx
Index: rel_bin.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_bin.mx,v
retrieving revision 1.45
retrieving revision 1.46
diff -u -d -r1.45 -r1.46
--- rel_bin.mx 17 Jun 2008 10:35:06 -0000 1.45
+++ rel_bin.mx 20 Jun 2008 20:50:30 -0000 1.46
@@ -537,6 +537,22 @@
return sub;
}
+static int
+equi_join(stmt *j)
+{
+ if (j->flag == cmp_equal)
+ return 0;
+ return -1;
+}
+
+static int
+not_equi_join(stmt *j)
+{
+ if (j->flag != cmp_equal)
+ return 0;
+ return -1;
+}
+
static stmt *
rel2bin_join( mvc *sql, sql_rel *rel )
{
@@ -556,29 +572,28 @@
return NULL;
}
if (rel->exps) {
- /* TODO introduce hash optimized reljoin (see rel2bin.mx) */
- /* probably better to simply generate stmt_reljoin here */
+ int idx = 0;
+ list *jns = create_stmt_list();
- /* simply keep a list of joins */
- /* at end split the lists in 2, ie those with equi joins and
- those not. With those results create releqjoin and reljoin
- statements.
- */
+ /* generate a stmt_reljoin */
for( en = rel->exps->h; en; en = en->next ) {
+ int join_idx = sql->opt_stats[0];
stmt *s = exp_bin(sql, en->data, left, right, NULL,
NULL);
if (!s) {
assert(0);
stmt_destroy(left);
stmt_destroy(right);
- if (join)
- stmt_destroy(join);
+ list_destroy(jns);
return NULL;
}
+ if (join_idx != sql->opt_stats[0])
+ idx = 1;
if (!join) {
join = s;
} else if (s->type != st_join && s->type != st_join2) {
/* handle select expressions */
+ assert(0);
if (s->h == join->h) {
join = stmt_semijoin(join,s);
} else {
@@ -586,44 +601,25 @@
join = stmt_semijoin(join,s);
join = stmt_reverse(join);
}
+ continue;
+ }
+ list_append(jns, s);
+ }
+ if (list_length(jns) > 1) {
+ int o = 1, *O = &o;
+ /* move all equi joins into a releqjoin */
+ list *eqjns = list_select(jns, O, (fcmp)&equi_join,
(fdup)stmt_dup);
+ if (!idx && list_length(eqjns) > 1) {
+ list *neqjns = list_select(jns, O,
(fcmp)¬_equi_join, (fdup)stmt_dup);
+ list_destroy(jns);
+ join = stmt_reljoin(stmt_releqjoin1(eqjns),
neqjns);
} else {
- /* break column join */
- stmt *l = stmt_mark(stmt_reverse(join), 100);
- stmt *r = stmt_mark(stmt_dup(join), 100);
- stmt *ld = stmt_dup(s->op1.stval);
- stmt *le = stmt_join(l, ld, cmp_equal);
- if (s->type == st_join2) {
- stmt *rd = stmt_dup(s->op2.stval);
- stmt *re = stmt_join(r, rd, cmp_equal);
- stmt *r2 = stmt_dup(s->op3.stval);
- stmt *re2 = stmt_join(stmt_dup(r), r2,
cmp_equal);
- comp_type c1 = s->flag&2?cmp_gte:cmp_gt;
- comp_type c2 = s->flag&1?cmp_lte:cmp_lt;
- stmt *v1 = stmt_uselect(le, re, c1);
- stmt *v2 = stmt_uselect(stmt_dup(le),
re2, c2);
- l = stmt_semijoin(stmt_dup(l),
stmt_dup(v1));
- l = stmt_semijoin(l, stmt_dup(v2));
- r = stmt_semijoin(stmt_dup(r), v1);
- r = stmt_semijoin(r, v2);
- join = stmt_join(stmt_reverse(l), r,
cmp_equal);
- stmt_destroy(s);
- } else {
- stmt *rd =
stmt_reverse(stmt_dup(s->op2.stval));
- stmt *re = stmt_join(r, rd, cmp_equal);
- sql_subfunc *f =
sql_bind_func(sql->session->schema, compare_func((comp_type)s->flag),
tail_type(le), tail_type(le));
- stmt * cmp;
-
- assert(f);
-
- cmp = stmt_binop(le, re, f);
- cmp = stmt_uselect(cmp, stmt_bool(1),
cmp_equal);
-
- l = stmt_semijoin(stmt_dup(l),
stmt_dup(cmp));
- r = stmt_semijoin(stmt_dup(r), cmp);
- join = stmt_join(stmt_reverse(l), r,
cmp_equal);
- stmt_destroy(s);
- }
+ join = stmt_reljoin(NULL, jns);
+ list_destroy(eqjns);
}
+ } else {
+ join = stmt_dup(jns->h->data);
+ list_destroy(jns);
}
} else {
stmt *l = bin_first_column(left);
U sql_select.mx
Index: sql_select.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_select.mx,v
retrieving revision 1.236
retrieving revision 1.237
diff -u -d -r1.236 -r1.237
--- sql_select.mx 17 Jun 2008 10:35:11 -0000 1.236
+++ sql_select.mx 20 Jun 2008 20:50:31 -0000 1.237
@@ -894,14 +894,14 @@
}
}
if (f) {
- if (f->func->res.scale == SCALE_FIX) {
+ if (f->func->fix_scale == SCALE_FIX) {
ls = fix_scale(sql, t2, ls, 0, 0);
rs = fix_scale(sql, t1, rs, 0, 0);
- } else if (f->func->res.scale == SCALE_SUB) {
+ } else if (f->func->fix_scale == SCALE_SUB) {
ls = scale_algebra(sql, f, ls, rs);
- } else if (f->func->res.scale == SCALE_ADD) {
+ } else if (f->func->fix_scale == SCALE_ADD) {
ls = sum_scales(sql, f, ls, rs);
- } else if (f->func->res.scale == DIGITS_ADD) {
+ } else if (f->func->fix_scale == DIGITS_ADD) {
f->res.digits = t1->digits + t2->digits;
}
return stmt_binop(ls, rs, f);
@@ -943,14 +943,14 @@
t2 = tail_type(rs);
f = sql_bind_func(s, fname, t1, t2);
if (f) {
- if (f->func->res.scale == SCALE_FIX) {
+ if (f->func->fix_scale == SCALE_FIX) {
ls = fix_scale(sql, t2, ls, 0, 0);
rs = fix_scale(sql, t1, rs, 0, 0);
- } else if (f->func->res.scale == SCALE_SUB) {
+ } else if (f->func->fix_scale == SCALE_SUB) {
ls = scale_algebra(sql, f, ls, rs);
- } else if (f->func->res.scale == SCALE_ADD) {
+ } else if (f->func->fix_scale == SCALE_ADD) {
ls = sum_scales(sql, f, ls, rs);
- } else if (f->func->res.scale == DIGITS_ADD) {
+ } else if (f->func->fix_scale == DIGITS_ADD) {
f->res.digits = digits;
}
stmt_destroy(ols);
-------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://sourceforge.net/services/buy/index.php
_______________________________________________
Monetdb-sql-checkins mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
