[prev in list] [next in list] [prev in thread] [next in thread]
List: monetdb-sql-checkins
Subject: [Monetdb-sql-checkins] sql/src/server rel_bin.mx, , 1.46,
From: Niels Nes <nielsnes () users ! sourceforge ! net>
Date: 2008-06-23 21:27:11
Message-ID: E1KAtZR-0006BQ-FQ () mail ! sourceforge ! net
[Download RAW message or body]
Update of /cvsroot/monetdb/sql/src/server
In directory sc8-pr-cvs16.sourceforge.net:/tmp/cvs-serv1049/server
Modified Files:
rel_bin.mx rel_dump.mx rel_exp.mx rel_optimizer.mx
rel_select.mx rel_semantic.mx sql_mvc.mx
Log Message:
handle WITH in algebra too
improved the rel_optimizer,
use join indices alsofor left outer joins (only for the inner part of the outer \
join) now pushes joins through group bys
pushes selects, through projects and set ops (ie rewrite expressions)
also we cleanup empty selects and useless projects
we keep if an expression may have nils now (reduces calles to isnil)
in rel_bin we now generate relselect's which make sure rel2bin does
a correct shrink_select ranges
U rel_select.mx
Index: rel_select.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_select.mx,v
retrieving revision 1.78
retrieving revision 1.79
diff -u -d -r1.78 -r1.79
--- rel_select.mx 20 Jun 2008 20:50:31 -0000 1.78
+++ rel_select.mx 23 Jun 2008 21:27:05 -0000 1.79
@@ -30,6 +30,7 @@
extern void rel_select_add_exp(sql_rel *l, sql_exp *e);
extern sql_rel *rel_select(sql_rel *l, sql_exp *e);
+extern sql_rel *rel_select_copy(sql_rel *l, list *exps);
extern sql_exp *rel_bind_column( mvc *sql, sql_rel *rel, char *cname );
extern sql_exp *rel_bind_column2( mvc *sql, sql_rel *rel, char *tname, char *cname \
); @@ -48,6 +49,7 @@
extern sql_rel *rel_dup(sql_rel *r);
+extern sql_rel *rel_copy(sql_rel *r);
extern void rel_destroy(sql_rel *rel);
#define new_rel_list() list_create((fdestroy)NULL)
@@ -187,11 +189,11 @@
nme = number2name(name, 16, ++sql->label);
exp_setname(old, NULL, nme);
- return exp_column(tname, nme, exp_subtype(old), orel->card);
+ return exp_column(tname, nme, exp_subtype(old), orel->card, has_nil(old));
} else if (cname && !old->name) {
exp_setname(old, tname, cname);
}
- return exp_column(tname, cname, exp_subtype(old), orel->card);
+ return exp_column(tname, cname, exp_subtype(old), orel->card, has_nil(old));
}
static sql_exp *
@@ -204,11 +206,11 @@
if (rel->exps) /* list of aliases */
e = exps_bind_column(rel->exps, cname);
if (e)
- return exp_column(NULL, cname, exp_subtype(e), rel->card);
+ return exp_column(NULL, cname, exp_subtype(e), rel->card, has_nil(e));
for (cn = t->columns.set->h; cn; cn = cn->next) {
sql_column *c = cn->data;
if (strcmp(c->base.name, cname) == 0)
- return exp_column(rel->name, cname, &c->type, CARD_MULTI );
+ return exp_column(rel->name, cname, &c->type, CARD_MULTI, c->null );
}
return NULL;
}
@@ -420,12 +422,12 @@
node *en;
for (en = rel->exps->h; en; en = en->next) {
sql_exp *e = en->data;
- append(exps, exp_column(NULL, e->name, exp_subtype(e), rel->card));
+ append(exps, exp_column(NULL, e->name, exp_subtype(e), rel->card, has_nil(e)));
}
} else {
for (cn = t->columns.set->h; cn; cn = cn->next) {
sql_column *c = cn->data;
- append(exps, exp_column(tname, c->base.name, &c->type, CARD_MULTI));
+ append(exps, exp_column(tname, c->base.name, &c->type, CARD_MULTI, c->null));
}
}
return exps;
@@ -436,7 +438,7 @@
return NULL;
}
-static sql_rel *
+sql_rel *
rel_copy( sql_rel *i )
{
sql_rel *rel = rel_create();
@@ -588,7 +590,7 @@
static sql_rel *
rel_distinct(sql_rel *l)
{
- if (l->card > CARD_ATOM)
+ if (l->card > CARD_AGGR)
set_distinct(l);
return l;
}
@@ -667,7 +669,7 @@
}
if (!tname && e->type == e_column)
tname = e->l;
- return exp_column(tname, m->name, exp_subtype(m), rel->card);
+ return exp_column(tname, m->name, exp_subtype(m), rel->card, has_nil(m));
}
static void
@@ -706,7 +708,7 @@
rel, rel->exps->t->data, 1);
assert(is_project(rel->op));
e = rel->exps->t->data;
- return exp_column(rel->name, e->name, exp_subtype(e), e->card);
+ return exp_column(rel->name, e->name, exp_subtype(e), e->card, has_nil(e));
}
void
@@ -737,6 +739,24 @@
return rel;
}
+sql_rel *
+rel_select_copy(sql_rel *l, list *exps)
+{
+ sql_rel *rel = rel_create();
+
+ rel->name = (l && l->name)?_strdup(l->name):NULL;
+ rel->l = l;
+ rel->r = NULL;
+ rel->op = op_select;
+ rel->exps = list_dup(exps, (fdup)&exp_dup);
+ rel->card = CARD_ATOM; /* no relation */
+ if (l) {
+ rel->card = l->card;
+ rel->nrcols = l->nrcols;
+ }
+ return rel;
+}
+
static sql_rel*
rel_project2groupby(mvc *sql, sql_rel **g)
{
@@ -1103,6 +1123,8 @@
node *cn;
sql_table *t = rel->l;
+ if (rel->exps && exps_bind_column(rel->exps, cname))
+ return rel;
for (cn = t->columns.set->h; cn; cn = cn->next) {
sql_column *c = cn->data;
if (strcmp(c->base.name, cname) == 0)
@@ -1137,7 +1159,7 @@
rel->exps = new_exp_list();
append(rel->exps, e);
exp_setname(e, NULL, nme);
- return exp_column(NULL, nme, exp_subtype(e), rel->card);
+ return exp_column(NULL, nme, exp_subtype(e), rel->card, has_nil(e));
}
sql_exp *
@@ -1196,7 +1218,7 @@
for (cn = t->columns.set->h; cn; cn = cn->next) {
sql_column *c = cn->data;
if (strcmp(c->base.name, cname) == 0)
- return exp_column(rel->name, cname, &c->type, CARD_MULTI);
+ return exp_column(rel->name, cname, &c->type, CARD_MULTI, c->null);
}
}
return NULL;
@@ -1229,7 +1251,7 @@
exps = new_exp_list();
for (m = st->comp_type->columns.set->h; m; m = m->next) {
sql_column *c = m->data;
- append(exps, exp_column(NULL, c->base.name, &c->type, CARD_MULTI));
+ append(exps, exp_column(NULL, c->base.name, &c->type, CARD_MULTI, c->null));
}
return rel_table_func(tname, e, exps);
}
@@ -1242,6 +1264,7 @@
(void)rel;
if (tableref->token == SQL_NAME) {
+ sql_rel *temp_table = NULL;
char *sname = qname_schema(tableref->data.lval->h->data.lval);
sql_schema *s = NULL;
tname = qname_table(tableref->data.lval->h->data.lval);
@@ -1256,15 +1279,20 @@
s = tmp_schema(sql);
t = mvc_bind_table(sql, s, tname);
}
- if (!t) {
+ if (!t && !sname)
+ temp_table = stack_find_rel_view(sql, tname);
+ if (!t && !temp_table) {
return sql_error(sql, 02, "SELECT: no such table '%s'", tname);
- } else if (!table_privs(sql, t, PRIV_SELECT)) {
- return sql_error(sql, 02, "User is not allowed to select from table %s", tname);
+ } else if (!temp_table && !table_privs(sql, t, PRIV_SELECT)) {
+ return sql_error(sql, 02, "SELECT: access denied for %s to table '%s.%s'", \
stack_get_string(sql, "current_user"), s->base.name, tname); }
if (tableref->data.lval->h->next->data.sym) { /* AS */
tname = tableref->data.lval->h->next->data.sym->data.lval->h->data.sval;
}
- if (isView(t) && sql->emode != m_instantiate) {
+ if (temp_table && !t) {
+ rel_set_name(temp_table, tname);
+ return temp_table;
+ } else if (isView(t) && sql->emode != m_instantiate) {
/* instantiate base view */
node *n,*m;
sql_rel *rel = rel_parse(sql, t->query, m_instantiate);
@@ -1789,7 +1817,7 @@
if (r->card > CARD_ATOM) {
sql_subaggr *zero_or_one = sql_bind_aggr(sql->session->schema, "zero_or_one", \
exp_subtype(rs));
- rs = exp_aggr1(rs, zero_or_one, 0, 0, CARD_ATOM);
+ rs = exp_aggr1(rs, zero_or_one, 0, 0, CARD_ATOM, 0);
}
rel = rel_crossproduct(rel, r, op_join);
}
@@ -1898,7 +1926,7 @@
if (r->card > CARD_ATOM) {
sql_subaggr *zero_or_one = sql_bind_aggr(sql->session->schema, "zero_or_one", \
exp_subtype(rs));
- rs = exp_aggr1(rs, zero_or_one, 0, 0, CARD_ATOM);
+ rs = exp_aggr1(rs, zero_or_one, 0, 0, CARD_ATOM, 0);
}
*rel = rel_crossproduct(*rel, r, op_join);
}
@@ -2218,9 +2246,8 @@
return NULL;
ek.card = card_set;
- /* first remove the NULLs, TODO optimize for not null exprs */
- /* TODO use exp_compare with 2 arguments */
- if (l->card != CARD_ATOM) {
+ /* first remove the NULLs */
+ if (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))
@@ -2825,7 +2852,7 @@
a = sql_bind_aggr(sql->session->schema, aggrstr, NULL);
/* add aggr expression to the groupby, and return a
column expression */
- e = exp_aggr(NULL, a, distinct, 0, groupby->card);
+ e = exp_aggr(NULL, a, distinct, 0, groupby->card, 0);
if (*rel == groupby && f == sql_sel) /* selection */
return e;
return rel_groupby_add_aggr(sql, groupby, e);
@@ -2847,7 +2874,7 @@
/* type may have changed, ie. need to fix_scale */
sql_subtype *t = exp_subtype(e);
- e = exp_aggr1(e, a, distinct, no_nil, groupby->card);
+ e = exp_aggr1(e, a, distinct, no_nil, groupby->card, has_nil(e));
exp = e;
if (*rel != old)
exp = rel_groupby_add_aggr(sql, *rel, exp);
@@ -3359,7 +3386,7 @@
if (e->card > CARD_ATOM) {
sql_subaggr *zero_or_one = sql_bind_aggr(sql->session->schema, "zero_or_one", \
exp_subtype(e));
- e = exp_aggr1(e, zero_or_one, 0, 0, CARD_ATOM);
+ e = exp_aggr1(e, zero_or_one, 0, 0, CARD_ATOM, 0);
}
if (*rel) {
/* current projection list */
@@ -3684,7 +3711,7 @@
assert(e->type == e_cmp);
assert(re->type == e_column);
- re = exp_column(inner->name, re->r, exp_subtype(re), inner->card);
+ re = exp_column(inner->name, re->r, exp_subtype(re), inner->card, has_nil(re));
e = exp_compare( exp_dup(e->l), re, e->flag);
append(jexps, e);
}
@@ -3751,7 +3778,7 @@
if (outer) {
sql_subaggr *zero_or_one = sql_bind_aggr(sql->session->schema, "zero_or_one", \
exp_subtype(ce));
- ce = exp_aggr1(ce, zero_or_one, 0, 0, rel->card);
+ ce = exp_aggr1(ce, zero_or_one, 0, 0, rel->card, 0);
} else if (ce->name) {
return sql_error(sql, 02, "SELECT: cannot use non GROUP BY column '%s' in \
query results without an aggregate function", ce->name); @@ -4245,6 +4272,30 @@
}
}
+static sql_rel *
+view_rename_columns( char *name, sql_rel *sq, dlist *column_spec)
+{
+ dnode *n = column_spec->h;
+ node *m = sq->exps->h;
+ list *l = create_stmt_list();
+
+ for (; n && m; n = n->next, m = m->next) {
+ char *cname = n->data.sval;
+ sql_exp *e = m->data;
+
+ sql_exp *n = exp_column(e->rname, e->name, exp_subtype(e), sq->card, has_nil(e));
+ exp_setname(n, NULL, cname);
+ list_append(l, n);
+ }
+ if (n || m) {
+ list_destroy(l);
+ return NULL;
+ }
+ (void)name;
+ return rel_project(sq, l);
+}
+
+
sql_rel *
rel_view(mvc *sql, dlist *qname, dlist *column_spec, symbol *query, int check, int \
persistent) {
@@ -4294,10 +4345,10 @@
t = mvc_create_view(sql, s, name, q, 0);
as_subquery( sql, t, sq, column_spec );
}
-/* todo
+
if (!persistent && column_spec)
sq = view_rename_columns( name, sq, column_spec);
-*/
+
if (!instantiate && sq && persistent) {
stmt *sqs = rel_bin(sql, sq);
list *view_id_l = stmt_list_dependencies(sqs, VIEW_DEPENDENCY);
U rel_optimizer.mx
Index: rel_optimizer.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_optimizer.mx,v
retrieving revision 1.17
retrieving revision 1.18
diff -u -d -r1.17 -r1.18
--- rel_optimizer.mx 20 Jun 2008 20:50:30 -0000 1.17
+++ rel_optimizer.mx 23 Jun 2008 21:27:05 -0000 1.18
@@ -295,6 +295,8 @@
return 0;
if (find_prop(e->p, PROP_JOINIDX))
*cnt += 100;
+ if (find_prop(e->p, PROP_HASHIDX))
+ *cnt += 100;
switch(e->type) {
case e_cmp:
switch (e->flag) {
@@ -488,19 +490,15 @@
}
}
-static sql_rel *
-order_joins(list *rels, list *exps)
+static list *
+find_fk(list *rels, list *exps)
{
- sql_rel *top = NULL, *l, *r;
- sql_exp *cje;
node *djn;
- list *sdje, *n_rels = list_create(NULL);
- int fnd = 0;
+ list *sdje, *aje, *dje;
/* first find the distinct join expressions */
- list *aje = list_select(exps, (void*)1, (fcmp) &exp_is_join, (fdup)exp_dup);
- list *dje = list_distinct2(aje, rels, (fcmp2) &joinexp_cmp, (fdup)exp_dup);
-
+ aje = list_select(exps, (void*)1, (fcmp) &exp_is_join, (fdup)exp_dup);
+ dje = list_distinct2(aje, rels, (fcmp2) &joinexp_cmp, (fdup)exp_dup);
for(djn=dje->h; djn; djn = djn->next) {
/* equal join expressions */
sql_idx *idx = NULL;
@@ -548,7 +546,6 @@
idx = find_fk_index(r, rcols, l, lcols);
swapped = 1;
}
- /* TODO then maybe 2 hash indices */
#ifdef DEBUG
printf("%s %d %s %d (%d)\n",
@@ -588,6 +585,20 @@
sdje = list_sort(dje, (fkeyvalue)&exp_keyvalue, (fdup)&exp_dup);
list_destroy(dje);
+ return sdje;
+}
+
+static sql_rel *
+order_joins(list *rels, list *exps)
+{
+ sql_rel *top = NULL, *l, *r;
+ sql_exp *cje;
+ node *djn;
+ list *sdje, *n_rels = list_create(NULL);
+ int fnd = 0;
+
+ sdje = find_fk(rels, exps);
+
/* get the first expression */
cje = sdje->h->data;
list_remove_data(sdje, cje);
@@ -705,15 +716,19 @@
return rel;
rel->exps = NULL; /* should be all crosstables by now */
rels = list_create(NULL);
- get_relations(rel, rels);
-
+ if (is_outerjoin(rel->op)) {
+ /* try to use an join index also for outer joins */
+ list_append(rels, rel->l);
+ list_append(rels, rel->r);
+ rel->exps = find_fk(rels, exps);
+ } else {
+ get_relations(rel, rels);
#ifdef DEBUG
-printf("SPLIT RELATIONS\n");
- for(n = rels->h; n; n = n->next)
- rel_print(sql, n->data, 0);
+ for(n = rels->h; n; n = n->next)
+ rel_print(sql, n->data, 0);
#endif
-
- rel = order_joins(rels, exps);
+ rel = order_joins(rels, exps);
+ }
list_destroy(rels);
list_destroy(exps);
return rel;
@@ -747,12 +762,13 @@
static sql_rel *
rel_join_order(mvc *sql, sql_rel *rel)
{
- if (rel->op == op_join && rel->exps) {
+ if (is_join(rel->op) && rel->exps) {
#ifdef DEBUG
printf("BEFORE\n");
rel_print(sql, rel, 0);
#endif
- rel->exps = push_up_join_exps(rel);
+ if (rel->op == op_join)
+ rel->exps = push_up_join_exps(rel);
rel = reorder_join(sql, rel);
#ifdef DEBUG
printf("AFTER\n");
@@ -833,7 +849,7 @@
if (e->type == e_func)
return exp_op(nl, sql_dup_func(e->f));
else
- return exp_aggr(nl, sql_dup_aggr(e->f), e->flag&1, (e->flag>>1), e->card);
+ return exp_aggr(nl, sql_dup_aggr(e->f), e->flag&1, (e->flag>>1), e->card, \
has_nil(e)); }
case e_atom:
return exp_dup(e);
@@ -841,9 +857,17 @@
return NULL;
}
+/*
+ * Push select down, pushes the selects through (simple) projections. Also
+ * it cleans up the projections which become useless.
+ */
sql_rel *
rel_push_select_down(mvc *sql, sql_rel *rel)
{
+ list *exps = NULL;
+ sql_rel *r = NULL;
+ node *n;
+
(void)sql;
if ((is_join(rel->op) || is_project(rel->op) || rel->op == op_topn) && rel->l) {
sql_rel *l = rel->l;
@@ -852,6 +876,7 @@
rel->l = l->l;
l->l = NULL;
rel_destroy(l);
+ return rel_push_select_down(sql, rel);
}
}
if ((is_join(rel->op) || is_set(rel->op)) && rel->r) {
@@ -861,142 +886,189 @@
rel->r = r->l;
r->l = NULL;
rel_destroy(r);
+ return rel_push_select_down(sql, rel);
}
}
- if (rel->op == op_select && rel->l) {
- list *exps = rel->exps;
- sql_rel *r = rel->l;
- node *n;
-
- assert(exps);
-
- switch(r->op) {
- case op_basetable:
- case op_table:
- return rel;
-
- case op_join:
- case op_left:
- case op_right:
- case op_full:
-
- case op_semi:
- case op_anti:
-
-#ifdef DEBUG
- rel_print(sql, rel, 0);
-#endif
-
- /* for each exp check if we can push it left or right */
-/*
- for (n=exps->h; n; n = n->next) {
- sql_exp *e = n->data;
-
- if (e->type == e_cmp) {
- sql_exp *le = e->l;
- sql_exp *re = e->r;
- sql_rel *nr;
-
- e = exp_dup(e);
+ if (rel->op == op_select && r && (r->op == op_select || r->op == op_except)) {
+ (void)list_merge(r->exps, rel->exps, (fdup)&exp_dup);
+ rel->l = NULL;
+ rel_destroy(rel);
+ return rel_push_select_down(sql, r);
+ }
+ exps = rel->exps;
+ r = rel->l;
+ if (rel->op == op_select && r && is_set(r->op)) {
+ rel->exps = new_exp_list();
+ for (n = exps->h; n; n = n->next) {
+ sql_exp *e = n->data, *ne = NULL;
+ if (e->type == e_cmp) {
+ int err = 0;
+ ne = exp_push_down(sql, e, r, r->l);
+ if (ne && ne != e) {
+ sql_exp *re = ne->r;
if (re->card >= CARD_AGGR) {
- nr = rel_push_join(r, le, re, e);
+ r->l = rel_push_join(r->l, ne->l, re, ne);
} else {
- nr =rel_push_select(r, le, re);
+ r->l = rel_push_select(r->l, ne->l, ne);
}
- //assert(nr==r);
- (void)nr;
- }
- }
- rel_print(sql, rel, 0);
-*/
- return rel;
-
- case op_union:
- case op_inter:
+ } else
+ err = 1;
+ ne = exp_push_down(sql, e, r, r->r);
+ if (ne && ne != e) {
+ sql_exp *re = ne->r;
+ if (re->card >= CARD_AGGR) {
+ r->r = rel_push_join(r->r, ne->l, re, ne);
+ } else {
+ r->r = rel_push_select(r->r, ne->l, ne);
+ }
+ } else
+ err = 1;
- /* we could push through both */
-/*
-#ifdef DEBUG
- rel_print(sql, rel, 0);
-#endif
+ if (err)
+ list_append(rel->exps, exp_dup(e));
+ } else {
+ list_append(rel->exps, exp_dup(e));
+ }
+ }
+ list_destroy(exps);
+ return rel_push_select_down(sql, r);
+ }
+ if (rel->op == op_project && r && r->op == op_project) {
+ int all = 1;
- r = rel_dup(rel->l);
- rl = rel_select(rel_dup(r->l), NULL);
- rr = rel_select(rel_dup(r->r), NULL);
- for (n=exps->h; n; n = n->next) {
- sql_exp *le, *re, *e = n->data;
-*/
+ /* here we need to fix aliases */
+ rel->exps = new_exp_list();
+ /* for each exp check if we can rename it */
+ for (n = exps->h; n && all; n = n->next) {
+ sql_exp *e = n->data, *ne = NULL;
+ sql_rel *prj = r;
- /* rewrite expression */
-/*
- 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));
+ if (e->type != e_column) {
+ all = 0;
+ } else {
+ ne = exp_push_down(sql, e, prj, prj->l);
+ /* can we move it down */
+ if (ne && ne != e) {
+ /* we need to keep the alias */
+ exp_setname(ne, e->rname, e->name);
+ list_append(rel->exps, ne);
+ } else {
+ all = 0;
+ exp_destroy(ne);
+ }
}
- rel_destroy(r->l);
- rel_destroy(r->r);
- r->l = rel_push_select_down(sql, rl);
- r->r = rel_push_select_down(sql, rr);
- if (rel->name)
- rel_set_name(r, rel->name);
- rel_destroy(rel);
-#ifdef DEBUG
- rel_print(sql, r, 0);
-#endif
- return r;
-*/
- break;
+ }
+ if (all) {
+ /* we can now remove the intermediate project */
+ rel->l = r->l;
+ r->l = NULL;
+ rel_destroy(r);
+ list_destroy(exps);
+ return rel_push_select_down(sql, rel);
+ } else {
+ /* leave as is */
+ list_destroy(rel->exps);
+ rel->exps = exps;
+ }
+ return rel;
+ } else if (rel->op == op_select && r && r->op == op_project) {
+ /* here we need to fix aliases */
+ rel->exps = new_exp_list();
+ /* for each exp check if we can rename it */
+ for (n = exps->h; n; n = n->next) {
+ sql_exp *e = n->data, *ne = NULL, *le, *re;
+ sql_rel *prj = r, *nr, *nl;
- case op_except:
- case op_select:
- (void)list_merge(r->exps, rel->exps, (fdup)&exp_dup);
- rel->l = NULL;
- rel_destroy(rel);
- return r;
-
- case op_project:
+ /* sometimes we also have functions in the expression list (TODO change them to \
e_cmp (predicates like (1=0))) */ + if (e->type == e_cmp) {
+ 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) {
+ nl = rel_push_join(nr, le, re, ne);
+ } else {
+ nl = rel_push_select(nr, le, ne);
+ }
+ if (nl != nr)
+ prj->l = nl;
+ } else {
+ list_append(rel->exps, exp_dup(e));
+ }
+ }
+ list_destroy(exps);
+ return rel;
+ }
+ return rel;
+}
- /* here we need to fix aliases */
+/*
+ * Push joins down, pushes the joins through group by expressions.
+ * When the join is on the group by columns, we can push the joins left
+ * under the group by.
+ */
+sql_rel *
+rel_push_join_down(mvc *sql, sql_rel *rel)
+{
+ list *exps = NULL;
+ sql_rel *gb = NULL, *l = NULL;
- rel->exps = new_exp_list();
- /* for each exp check if we can rename it */
- for (n = exps->h; n; n = n->next) {
- sql_exp *e = n->data, *ne = NULL, *le, *re;
- sql_rel *prj = r, *nr, *nl;
+ (void)sql;
+ if (is_join(rel->op) && rel->l) {
+ gb = rel->r;
+ exps = rel->exps;
+ if (gb->op == op_groupby && gb->r) {
+ list *jes = new_exp_list();
+ node *n, *m;
+ list *gbes = gb->r;
+ /* find out if all group by expressions are
+ used in the join */
+ for(n = gbes->h; n; n = n->next) {
+ sql_exp *gbe = n->data;
+ int fnd = 0;
+ char *rname = (gbe->rname)?gbe->rname:gb->name;
+ for (m = exps->h; m && !fnd; m = m->next) {
+ sql_exp *je = m->data;
- /* sometimes we also have functions in the expression list (TODO change them to \
e_cmp (predicates like (1=0))) */
- if (e->type == e_cmp) {
- 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) {
- nl = rel_push_join(nr, le, re, ne);
- } else {
- nl = rel_push_select(nr, le, ne);
+ if (je->card >= CARD_ATOM && je->type == e_cmp) {
+ /* expect right expression to match */
+ sql_exp *r = je->r;
+ if (r->type != e_column)
+ continue;
+ if (r && r->l && rname && strcmp(r->l, rname) == 0 && \
strcmp(r->r,gbe->name)==0) { + fnd = 1;
+ } else if (r && !r->l && !rname && strcmp(r->r,gbe->name)==0) {
+ fnd = 1;
+ }
+ if (fnd) {
+ sql_exp *re = exp_push_down(sql, r, gb, gb->l);
+ assert(re);
+ je = exp_compare(exp_dup(je->l), re, je->flag);
+ list_append(jes, je);
+ }
}
- if (nl != nr)
- prj->l = nl;
- } else {
- list_append(rel->exps, exp_dup(e));
+ }
+ if (!fnd) {
+ list_destroy(jes);
+ return rel;
}
}
- list_destroy(exps);
- return rel;
-
- case op_groupby:
- case op_topn:
+ /* 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;
return rel;
- }
+ }
}
return rel;
}
@@ -1250,8 +1322,6 @@
rel_print(sql, rel, 0);
#endif
- /* TODO add rename optimizer, ie remove projects */
-
/* TODO add optimizer which removes unions
(for example common rels, with only one different expression) */
/* TODO common sub relation/expression optimizer */
@@ -1259,15 +1329,18 @@
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] && gp.cnt[op_groupby])
+ rel = rewrite(sql, rel, &rel_push_join_down);
- if (gp.cnt[op_join])
+ if (gp.cnt[op_join] || gp.cnt[op_left])
rel = rewrite(sql, rel, &rel_join_order);
if (gp.cnt[op_select])
rel = rewrite(sql, rel, &rel_select_order);
+ if (gp.cnt[op_select])
+ rel = rewrite(sql, rel, &rel_select_use_index);
+
#ifdef DEBUG
rel_print(sql, rel, 0);
#endif
U rel_dump.mx
Index: rel_dump.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_dump.mx,v
retrieving revision 1.26
retrieving revision 1.27
diff -u -d -r1.26 -r1.27
--- rel_dump.mx 9 Apr 2008 09:56:23 -0000 1.26
+++ rel_dump.mx 23 Jun 2008 21:27:04 -0000 1.27
@@ -116,9 +116,9 @@
case e_aggr: {
sql_subaggr *a = e->f;
printf("%s", a->aggr->base.name );
- if (is_distinct(e))
+ if (need_distinct(e))
printf(" unique ");
- if (is_no_nil(e))
+ if (has_no_nil(e))
printf(" no nil ");
exps_print( sql, e->l, depth, alias, 1);
} break;
@@ -145,6 +145,8 @@
}
if (e->type != e_atom && is_ascending(e))
printf(" ASC");
+ if (e->type != e_atom && e->type != e_cmp && !has_nil(e))
+ printf(" NOT NULL");
if (e->name && alias) {
printf(" as ");
if (e->rname)
@@ -268,7 +270,7 @@
else if (!rel->exps && rel->op == op_join)
r = "crossproduct";
print_indent(sql, depth);
- if (is_distinct(rel))
+ if (need_distinct(rel))
printf("distinct ");
printf("%s (", r);
rel_print(sql, rel->l, depth+1);
@@ -293,7 +295,7 @@
r = "top N";
if (rel->l) {
print_indent(sql, depth);
- if (is_distinct(rel))
+ if (need_distinct(rel))
printf("distinct ");
printf("%s (", r);
rel_print(sql, rel->l, depth+1);
U rel_semantic.mx
Index: rel_semantic.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_semantic.mx,v
retrieving revision 1.26
retrieving revision 1.27
diff -u -d -r1.26 -r1.27
--- rel_semantic.mx 1 Apr 2008 17:04:56 -0000 1.26
+++ rel_semantic.mx 23 Jun 2008 21:27:07 -0000 1.27
@@ -184,8 +184,36 @@
case SQL_DECLARE:
case SQL_CALL:
case SQL_SET:
+ return NULL;
case SQL_WITH:
+ {
+ dnode *d = s->data.lval->h;
+ symbol *select = d->next->data.sym;
+ sql_rel *rel = NULL;
+
+ stack_push_frame(sql, "WITH");
+ /* first handle all with's (ie inlined views) */
+ for (d = d->data.lval->h; d; d = d->next) {
+ symbol *sym = d->data.sym;
+ dnode *dn = sym->data.lval->h;
+ char *name = qname_table(dn->data.lval);
+
+ if (frame_find_var(sql, name)) {
+ return sql_error(sql, 01, "Variable '%s' allready declared", name);
+ }
+ rel = rel_semantic(sql, sym);
+ if (!rel) {
+ stack_pop_frame(sql);
+ return NULL;
+ }
+ stack_push_rel_view(sql, name, rel);
+ }
+ rel = rel_semantic(sql, select);
+ stack_pop_frame(sql);
+ return rel;
+ }
+
case SQL_MULSTMT:
/*return sql_error(sql, 02, "Not implemented");*/
return NULL;
U sql_mvc.mx
Index: sql_mvc.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_mvc.mx,v
retrieving revision 1.203
retrieving revision 1.204
diff -u -d -r1.203 -r1.204
--- sql_mvc.mx 20 Jun 2008 20:50:31 -0000 1.203
+++ sql_mvc.mx 23 Jun 2008 21:27:07 -0000 1.204
@@ -31,6 +31,7 @@
#include <sql_types.h>
#include <sql_backend.h>
#include <sql_catalog.h>
+#include <sql_relation.h>
#include <sql_storage.h>
#include <sql_keyword.h>
#include <sql_atom.h>
@@ -57,8 +58,9 @@
#define mod_trace 4
#define mod_record 8
+struct stmt;
typedef struct sql_var {
- struct stmt *s;
+ void *s; /* stmt or sql_rel */
char *name;
ValRecord value;
sql_subtype type;
@@ -206,11 +208,13 @@
/* variable management */
extern void stack_push_var(mvc *sql, char *name, struct stmt *var, sql_subtype \
*type); extern void stack_push_view(mvc *sql, char *name, struct stmt *var);
+extern void stack_push_rel_view(mvc *sql, char *name, sql_rel *view);
extern void stack_push_frame(mvc *sql, char *name);
extern void stack_pop_frame(mvc *sql);
extern void stack_pop_until(mvc *sql, int top);
extern sql_subtype *stack_find_type(mvc *sql, char *name);
extern struct stmt *stack_find_view(mvc *sql, char *name);
+extern sql_rel *stack_find_rel_view(mvc *sql, char *name);
extern struct stmt *stack_find_var(mvc *sql, char *name);
/* find var in current frame */
extern int frame_find_var(mvc *sql, char *name);
@@ -239,6 +243,7 @@
#include "sql_env.h"
#include "sql_semantic.h"
#include "sql_privileges.h"
+#include "rel_select.h"
static int mvc_debug = 0;
@@ -1377,6 +1382,21 @@
sql->topvars++;
}
+void
+stack_push_rel_view(mvc *sql, char *name, sql_rel *var)
+{
+ if (sql->topvars == sql->sizevars) {
+ sql->sizevars <<= 1;
+ sql->vars = RENEW_ARRAY(sql_var,sql->vars,sql->sizevars);
+ }
+ sql->vars[sql->topvars].s = rel_dup(var);
+ sql->vars[sql->topvars].name = _strdup(name);
+ sql->vars[sql->topvars].value.vtype = 0;
+ sql->vars[sql->topvars].view = 1;
+ sql->vars[sql->topvars].type.comp_type = NULL;
+ sql->topvars++;
+}
+
void
stack_set_var(mvc *sql, char *name, ValRecord *v)
@@ -1481,6 +1501,19 @@
return NULL;
}
+sql_rel *
+stack_find_rel_view(mvc *sql, char *name)
+{
+ int i;
+
+ for (i = sql->topvars-1; i >= 0; i--) {
+ if (sql->vars[i].s && sql->vars[i].view &&
+ strcmp(sql->vars[i].name, name)==0)
+ return rel_copy(sql->vars[i].s);
+ }
+ return NULL;
+}
+
stmt *
stack_find_var(mvc *sql, char *name)
{
U rel_exp.mx
Index: rel_exp.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_exp.mx,v
retrieving revision 1.11
retrieving revision 1.12
diff -u -d -r1.11 -r1.12
--- rel_exp.mx 14 May 2008 20:18:23 -0000 1.11
+++ rel_exp.mx 23 Jun 2008 21:27:04 -0000 1.12
@@ -48,9 +48,9 @@
exp_op(append(append(append(new_exp_list(),l),r),r2), f)
#define exp_op4(l,r,r2,r3,f) \
exp_op(append(append(append(append(new_exp_list(),l),r),r2),r3), f)
-extern sql_exp *exp_aggr( list *l, sql_subaggr *a, int distinct, int no_nils, int \
card );
-#define exp_aggr1(e, a, d, n, c) \
- exp_aggr(append(new_exp_list(), e), a, d, n, c)
+extern sql_exp *exp_aggr( list *l, sql_subaggr *a, int distinct, int no_nils, int \
card, int has_nil ); +#define exp_aggr1(e, a, d, n, c, hn) \
+ exp_aggr(append(new_exp_list(), e), a, d, n, c, hn)
extern sql_exp * exp_atom( atom *a);
extern sql_exp * exp_atom_bool(int b);
extern sql_exp * exp_atom_int(int i);
@@ -58,7 +58,7 @@
extern sql_exp * exp_atom_clob(str s);
extern sql_exp * exp_atom_ref(int i, sql_subtype *tpe);
extern sql_exp * exp_param(char *name, sql_subtype *tpe, int frame);
-extern sql_exp * exp_column( char *rname, char *name, sql_subtype *t, int card);
+extern sql_exp * exp_column( char *rname, char *name, sql_subtype *t, int card, int \
has_nils); extern void exp_setname( sql_exp *e, char *rname, char *name );
extern void exp_swap( sql_exp *e );
@@ -217,7 +217,7 @@
}
sql_exp *
-exp_aggr( list *l, sql_subaggr *a, int distinct, int no_nils, int card )
+exp_aggr( list *l, sql_subaggr *a, int distinct, int no_nils, int card, int has_nils \
) {
sql_exp *e = exp_create(e_aggr);
e->card = card;
@@ -227,6 +227,8 @@
set_distinct(e);
if (no_nils)
set_no_nil(e);
+ if (!has_nils)
+ set_has_no_nil(e);
return e;
}
@@ -296,7 +298,7 @@
}
sql_exp *
-exp_column( char *rname, char *cname, sql_subtype *t, int card)
+exp_column( char *rname, char *cname, sql_subtype *t, int card, int has_nils)
{
sql_exp *e = exp_create(e_column);
@@ -306,6 +308,8 @@
e->l = (rname)?_strdup(rname):NULL;
e->r = _strdup(cname);
e->f = t;
+ if (!has_nils)
+ set_has_no_nil(e);
return e;
}
U rel_bin.mx
Index: rel_bin.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_bin.mx,v
retrieving revision 1.46
retrieving revision 1.47
diff -u -d -r1.46 -r1.47
--- rel_bin.mx 20 Jun 2008 20:50:30 -0000 1.46
+++ rel_bin.mx 23 Jun 2008 21:27:03 -0000 1.47
@@ -267,12 +267,12 @@
*
* so here we need to ignore NULLs
*/
- if (is_no_nil(e) && attr) {
+ if (has_no_nil(e) && attr) {
sql_subtype *t = exp_subtype(attr->h->data);
stmt *n = stmt_atom(atom_general(t, NULL, 0));
as = stmt_select2(as, n, stmt_dup(n), 0);
}
- if (is_distinct(e)){
+ if (need_distinct(e)){
if (g)
as = stmt_unique(as, grp_dup(grp));
else
@@ -848,7 +848,7 @@
stmt_destroy(left);
stmt_destroy(right);
sub = stmt_list(l);
- if (is_distinct(rel))
+ if (need_distinct(rel))
sub = rel2bin_distinct(sub);
return sub;
}
@@ -900,8 +900,8 @@
/* now find the matching groups */
s = stmt_releqjoin_init();
for (n = left->op1.lval->h, m = right->op1.lval->h; n && m; n = n->next, m = \
m->next) {
- stmt *l = stmt_dup(n->data);
- stmt *r = stmt_dup(m->data);
+ stmt *l = column(n->data);
+ stmt *r = column(m->data);
l = stmt_join(stmt_dup(lgrp->ext), l, cmp_equal);
r = stmt_join(stmt_dup(rgrp->ext), r, cmp_equal);
@@ -946,7 +946,7 @@
/* project columns of left hand expression */
for (n = left->op1.lval->h; n; n = n->next) {
- stmt *c1 = stmt_dup(n->data);
+ stmt *c1 = column(n->data);
char *rnme = rel->name;
char *nme = column_name(c1);
@@ -966,7 +966,7 @@
stmt_destroy(left);
stmt_destroy(right);
sub = stmt_list(stmts);
- if (is_distinct(rel))
+ if (need_distinct(rel))
sub = rel2bin_distinct(sub);
return sub;
}
@@ -1018,8 +1018,8 @@
/* now find the matching groups */
s = stmt_releqjoin_init();
for (n = left->op1.lval->h, m = right->op1.lval->h; n && m; n = n->next, m = \
m->next) {
- stmt *l = stmt_dup(n->data);
- stmt *r = stmt_dup(m->data);
+ stmt *l = column(n->data);
+ stmt *r = column(m->data);
l = stmt_join(stmt_dup(lgrp->ext), l, cmp_equal);
r = stmt_join(stmt_dup(rgrp->ext), r, cmp_equal);
@@ -1051,7 +1051,7 @@
/* project columns of left hand expression */
for (n = left->op1.lval->h; n; n = n->next) {
- stmt *c1 = stmt_dup(n->data);
+ stmt *c1 = column(n->data);
char *rnme = rel->name;
char *nme = column_name(c1);
@@ -1071,7 +1071,7 @@
stmt_destroy(left);
stmt_destroy(right);
sub = stmt_list(stmts);
- if (is_distinct(rel))
+ if (need_distinct(rel))
sub = rel2bin_distinct(sub);
return sub;
}
@@ -1113,7 +1113,7 @@
list_append(l, s);
}
psub = stmt_list(l);
- if (is_distinct(rel))
+ if (need_distinct(rel))
psub = rel2bin_distinct(psub);
if (rel->r) {
list *oexps = rel->r;
@@ -1159,6 +1159,7 @@
sql_exp *l = e->l;
stmt *idx = bin_find_column(sub, l->l, i->base.name);
+ /* TODO should be in key order! */
for( en = rel->exps->h; en; en = en->next ) {
sql_exp *e = en->data;
stmt *s;
@@ -1217,8 +1218,10 @@
sel = rel2bin_hash_lookup(sql, rel, sub, i, en);
}
}
+ sel = stmt_relselect_init();
for( en = rel->exps->h; en; en = en->next ) {
- stmt *s = exp_bin(sql, en->data, sub, NULL, NULL, sel);
+ //stmt *s = exp_bin(sql, en->data, sub, NULL, NULL, sel);
+ stmt *s = exp_bin(sql, en->data, sub, NULL, NULL, NULL);
if (!s) {
assert(0);
@@ -1232,14 +1235,23 @@
predicate = rel2bin_predicate();
predicate = stmt_uselect(predicate, s, cmp_equal);
} else {
+ /*
if (sel)
stmt_destroy(sel);
sel = s;
+ */
+ stmt_relselect_fill(sel, s);
}
}
- if (predicate && sel)
- sel = stmt_join(sel, stmt_dup(predicate), cmp_all);
+ if (predicate && sel) {
+ if (list_length(sel->op1.lval) == 0) {
+ stmt_destroy(sel);
+ sel = NULL;
+ } else {
+ sel = stmt_join(sel, stmt_dup(predicate), cmp_all);
+ }
+ }
/* construct relation */
l = create_stmt_list();
if (sub && sel) {
-------------------------------------------------------------------------
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
Monetdb-sql-checkins@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic