Update of /cvsroot/monetdb/sql/src/server
In directory sc8-pr-cvs16.sourceforge.net:/tmp/cvs-serv12075/src/server
Modified Files:
rel_optimizer.mx rel_select.mx
Log Message:
sql_relational.h: added some handy operator tests (is_semi and is_union etc)
rel_optimizer.mx:
we push joins stemming from sql 'in' as lowest join expressions, as
they are similar to selects, which we also prefer over other operators.
we rewrite semi/anti join ( l, group by (r) [groupby exps] [exps] )
into semi/anti join (l, project(r) [exps] )
if the group by was done on the join columns
rel_select.mx:
we only create a new select operator if needed, ie if we allready have
one we simply add the expression.
bug fix: in SQL_OR handling we first project all columns then to the
union. Else the columns of the left and right relations of the union
maybe become differently sorted by the optimizer later.
only SQL_NOT_IN requires removal of nulls on the left hand of the IN.
U rel_select.mx
Index: rel_select.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_select.mx,v
retrieving revision 1.82
retrieving revision 1.83
diff -u -d -r1.82 -r1.83
--- rel_select.mx 23 Jul 2008 19:43:49 -0000 1.82
+++ rel_select.mx 4 Aug 2008 13:18:49 -0000 1.83
@@ -723,7 +723,10 @@
{
sql_rel *rel = rel_create();
- assert(rel->op != op_select);
+ if (l && l->op == op_select) { /* refine old select */
+ rel_select_add_exp(l, e);
+ return l;
+ }
rel->name = (l && l->name)?_strdup(l->name):NULL;
rel->l = l;
rel->r = NULL;
@@ -885,10 +888,6 @@
if (!l) {
/* expression has no clear parent relation, so filter current
with it */
- if (rel && rel->op == op_select) { /* refine old select */
- rel_select_add_exp(rel, e);
- return rel;
- }
return rel_select(rel, e);
}
@@ -1838,6 +1837,8 @@
sql_rel *rel;
(void)f;
+ l = rel_project(l, rel_projections(sql, l, NULL, 1));
+ r = rel_project(r, rel_projections(sql, r, NULL, 1));
rel = rel_setop(l, r, op_union);
rel->exps = rel_projections(sql, rel, NULL, 1);
return rel;
@@ -1852,7 +1853,6 @@
if (!sc)
return NULL;
- //assert(f >= sql_sel);
switch (sc->token) {
case SQL_OR:
case SQL_AND:
@@ -2250,7 +2250,7 @@
ek.card = card_set;
/* first remove the NULLs */
- if (l->card != CARD_ATOM && has_nil(l)) {
+ if (sc->token == SQL_NOT_IN && l->card != CARD_ATOM &&
has_nil(l)) {
e = rel_unop_(sql, exp_dup(l), NULL, "isnull");
e = exp_compare( e, exp_atom_bool(0), cmp_equal);
if (!is_select(rel->op))
U rel_optimizer.mx
Index: rel_optimizer.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_optimizer.mx,v
retrieving revision 1.20
retrieving revision 1.21
diff -u -d -r1.20 -r1.21
--- rel_optimizer.mx 23 Jul 2008 19:43:48 -0000 1.20
+++ rel_optimizer.mx 4 Aug 2008 13:18:49 -0000 1.21
@@ -275,8 +275,7 @@
static void
get_relations(sql_rel *rel, list *rels)
{
- if (rel->op == op_join) {
- assert(rel->exps == NULL);
+ if (rel->op == op_join && rel->exps == NULL) {
get_relations(rel->l, rels);
get_relations(rel->r, rels);
rel->l = NULL;
@@ -591,7 +590,7 @@
static sql_rel *
order_joins(list *rels, list *exps)
{
- sql_rel *top = NULL, *l, *r;
+ sql_rel *top = NULL, *l = NULL, *r = NULL;
sql_exp *cje;
node *djn;
list *sdje, *n_rels = list_create(NULL);
@@ -599,14 +598,16 @@
sdje = find_fk(rels, exps);
- /* get the first expression */
- cje = sdje->h->data;
- list_remove_data(sdje, cje);
- list_remove_data(exps, cje);
+ if (sdje->h) {
+ /* get the first expression */
+ cje = sdje->h->data;
+ list_remove_data(sdje, cje);
+ list_remove_data(exps, cje);
- /* find the involved relations */
- l = find_rel(rels, cje->l);
- r = find_rel(rels, cje->r);
+ /* find the involved relations */
+ l = find_rel(rels, cje->l);
+ r = find_rel(rels, cje->r);
+ }
if (l && r) {
list_remove_data(rels, l);
list_remove_data(rels, r);
@@ -637,7 +638,7 @@
while(list_length(exps) && fnd) {
fnd = 0;
/* find the first expression which could be added */
- for(djn = sdje->h; djn && !fnd; djn = djn->next) {
+ for(djn = sdje->h; djn && !fnd; djn = (!fnd)?djn->next:NULL) {
node *ln, *rn, *en;
cje = djn->data;
@@ -702,6 +703,52 @@
return top;
}
+static void
+push_in_join_down(list *rels, list *exps)
+{
+ node *n;
+ int restart = 1;
+
+ /* one of the rels should be a op_union with nrcols == 0 */
+ while(restart) {
+ for(n = rels->h; n; n = n->next) {
+ sql_rel *r = n->data;
+
+ restart = 0;
+ if (is_union(r->op) && r->nrcols == 0) {
+ /* next step find expression on this relation */
+ node *m;
+ sql_rel *l = NULL;
+ sql_exp *je = NULL;
+
+ for(m = exps->h; !je && m; m = m->next) {
+ sql_exp *e = m->data;
+
+ if (e->type == e_cmp && e->flag == cmp_equal) {
+ /* in values are on
+ the right of the join */
+ if (rel_has_exp(r, e->r) >= 0)
+ je = e;
+ }
+ }
+ /* with this expression find other relation */
+ if (je && (l = find_rel(rels, je->l)) != NULL) {
+ sql_rel *nr = rel_crossproduct(l, r, op_join);
+
+ rel_join_add_exp(nr, exp_dup(je));
+ list_append(rels, nr);
+ list_remove_data(rels, l);
+ list_remove_data(rels, r);
+ list_remove_data(exps, je);
+ restart = 1;
+ break;
+ }
+
+ }
+ }
+ }
+}
+
static sql_rel *
reorder_join(mvc *sql, sql_rel *rel)
{
@@ -727,6 +774,8 @@
for(n = rels->h; n; n = n->next)
rel_print(sql, n->data, 0);
#endif
+ push_in_join_down(rels, exps);
+
rel = order_joins(rels, exps);
}
list_destroy(rels);
@@ -748,7 +797,8 @@
list_destroy(r);
}
if (rel->exps) {
- r = l;
+ if (l && !r)
+ r = l;
l = list_merge(rel->exps, r, (fdup)&exp_dup);
list_destroy(r);
}
@@ -1023,7 +1073,7 @@
sql_rel *gb = NULL, *l = NULL;
(void)sql;
- if (is_join(rel->op) && rel->l) {
+ if ((is_join(rel->op) || is_semi(rel->op)) && rel->l) {
gb = rel->r;
exps = rel->exps;
if (gb->op == op_groupby && gb->r) {
@@ -1062,13 +1112,20 @@
return rel;
}
}
- /* push join's left side (as semijoin) down group by */
- l = rel_copy(rel->l);
- /* now we need to translate the names using the join
- expressions */
- gb->l = rel_crossproduct(gb->l, l, op_semi);
- l = gb->l;
- l->exps = jes;
+ if (is_join(rel->op)) {
+ /* push join's left side (as semijoin) down
group by */
+ l = rel_copy(rel->l);
+ /* now we need to translate the names using the
join
+ expressions */
+ gb->l = rel_crossproduct(gb->l, l, op_semi);
+ l = gb->l;
+ l->exps = jes;
+ } else { /* semi join */
+ /* rewrite group by into project */
+ gb->op = op_project;
+ list_destroy(gb->r);
+ gb->r = NULL;
+ }
return rel;
}
}
@@ -1328,11 +1385,16 @@
/* TODO add optimizer which removes unions
(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);
+ /* TODO push select up. Sounds bad, but isn't. In case of an join-idx
we want the selection on
+ the 'unique/primary (right hand side)' done before the (fake)-join
and the selections on the foreign
+ part done after. */
+
if (gp.cnt[op_join] && gp.cnt[op_groupby])
rel = rewrite(sql, rel, &rel_push_join_down);
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Monetdb-sql-checkins mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
