Changeset: 72bf919e19e0 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/72bf919e19e0 Modified Files: sql/server/rel_optimizer.c sql/test/merge-partitions/Tests/mergepart31.test sql/test/mergetables/Tests/addtable.test Branch: Jul2021 Log Message:
Make rel_merge_table_rewrite optimizer push selections under nested merge
tables. Also did some cleanup. This has to be in Jul2021 because this was an
important feature lacking
diffs (truncated from 1128 to 300 lines):
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -8859,9 +8859,9 @@ exp_range_overlap(atom *min, atom *max,
}
static sql_rel *
-rel_rename_part(mvc *sql, sql_rel *p, sql_rel *rel, char *tname, sql_table *mt)
-{
- sql_table *t = rel_base_table(p);
+rel_rename_part(mvc *sql, sql_rel *p, sql_rel *mt_rel, const char *mtalias)
+{
+ sql_table *mt = rel_base_table(mt_rel), *t = rel_base_table(p);
node *n;
assert(!p->exps);
@@ -8869,47 +8869,20 @@ rel_rename_part(mvc *sql, sql_rel *p, sq
const char *pname = t->base.name;
if (isRemote(t))
pname = mapiuri_table(t->query, sql->sa, pname);
- for (n = rel->exps->h; n; n = n->next) {
+ for (n = mt_rel->exps->h; n; n = n->next) {
sql_exp *e = n->data;
if (is_intern(e) || exp_name(e)[0] == '%') /* break on tid/idxs
*/
break;
sql_column *c = ol_find_name(mt->columns, exp_name(e))->data;
sql_column *rc = ol_fetch(t->columns, c->colnr);
/* with name find column in merge table, with colnr find column
in member */
- e = exp_alias(sql->sa, tname, c->base.name, pname,
rc->base.name, &rc->type, CARD_MULTI, rc->null, 0);
- append(p->exps, e);
+ list_append(p->exps, exp_alias(sql->sa, mtalias, c->base.name,
pname, rc->base.name, &rc->type, CARD_MULTI, rc->null, 0));
}
if (n) {
sql_exp *e = n->data;
- if (strcmp(exp_name(e), TID) == 0) {
- e = exp_alias(sql->sa, tname, TID, pname, TID,
sql_bind_localtype("oid"), CARD_MULTI, 0, 1);
- append(p->exps, e);
- }
- }
-#if 0
- node *n, *m;
- for( m = ol_first_node(mt->columns); m; m = m->next) {
- sql_column *c = m->data;
- append(p->exps, exp_alias(sql->sa, tname, c->base.name, pname,
c->base.name, &c->type, CARD_MULTI, c->null, 0));
- }
- if (n) /* skip TID */
- n = n->next;
- if (mt->idxs) {
- /* also possible index name mismatches */
- for( m = ol_first_node(mt->idxs); n && m; m = m->next) {
- sql_exp *ne = n->data;
- sql_idx *i = m->data;
- char *iname = NULL;
-
- if ((hash_index(i->type) && list_length(i->columns) <=
1) || !idx_has_column(i->type))
- continue;
-
- iname = sa_strconcat( sql->sa, "%", i->base.name);
- exp_setname(sql->sa, ne, tname, iname);
- n = n->next;
- }
- }
-#endif
+ if (strcmp(exp_name(e), TID) == 0)
+ list_append(p->exps, exp_alias(sql->sa, mtalias, TID,
pname, TID, sql_bind_localtype("oid"), CARD_MULTI, 0, 1));
+ }
rel_base_set_mergetable(p, mt);
return p;
}
@@ -8923,417 +8896,420 @@ typedef struct {
list *values;
} range_limit;
-/* rewrite merge tables into union of base tables and call optimizer again */
+typedef struct {
+ list *cols;
+ list *ranges;
+ sql_rel *sel;
+} merge_table_prune_info;
+
+static sql_rel *
+merge_table_prune_and_unionize(visitor *v, sql_rel *mt_rel,
merge_table_prune_info *info)
+{
+ sql_rel *nrel = NULL;
+ sql_table *mt = (sql_table*) mt_rel->l;
+ const char *mtalias = exp_relname(mt_rel->exps->h->data);
+ list *tables = sa_list(v->sql->sa);
+
+ if (mvc_highwater(v->sql))
+ return sql_error(v->sql, 10, SQLSTATE(42000) "Query too
complex: running out of stack space");
+
+ for (node *nt = mt->members->h; nt; nt = nt->next) {
+ sql_part *pd = nt->data;
+ sql_table *pt = find_sql_table_id(v->sql->session->tr, mt->s,
pd->member);
+ sqlstore *store = v->sql->session->tr->store;
+ int skip = 0, allowed = 1;
+
+ /* At the moment we throw an error in the optimizer, but later
this rewriter should move out from the optimizers */
+ if ((isMergeTable(pt) || isReplicaTable(pt)) &&
list_empty(pt->members))
+ return sql_error(v->sql, 02, SQLSTATE(42000) "The %s
'%s.%s' should have at least one table associated",
+
TABLE_TYPE_DESCRIPTION(pt->type, pt->properties), pt->s->base.name,
pt->base.name);
+ /* Do not include empty partitions */
+ if (isTable(pt) && pt->access == TABLE_READONLY &&
!store->storage_api.count_col(v->sql->session->tr,
ol_first_node(pt->columns)->data, 0))
+ continue;
+
+ if (!table_privs(v->sql, pt, PRIV_SELECT)) /* Test for
privileges */
+ allowed = 0;
+
+ for (node *n = mt_rel->exps->h; n && !skip; n = n->next) { /*
for each column of the child table */
+ sql_exp *e = n->data;
+ int i = 0;
+ bool first_attempt = true;
+ atom *cmin = NULL, *cmax = NULL, *rmin = NULL, *rmax =
NULL;
+ list *inlist = NULL;
+ const char *cname = e->r;
+ sql_column *mt_col = NULL, *col = NULL;
+
+ if (cname[0] == '%') /* Ignore TID and indexes here */
+ continue;
+
+ mt_col = ol_find_name(mt->columns, exp_name(e))->data;
+ col = ol_fetch(pt->columns, mt_col->colnr);
+ assert(e && e->type == e_column && col);
+ if (!allowed && !column_privs(v->sql, col, PRIV_SELECT))
+ return sql_error(v->sql, 02, SQLSTATE(42000)
"The user %s SELECT permissions on table '%s.%s' don't match %s '%s.%s'",
get_string_global_var(v->sql, "current_user"),
+
pt->s->base.name, pt->base.name, TABLE_TYPE_DESCRIPTION(mt->type,
mt->properties), mt->s->base.name, mt->base.name);
+ if (isTable(pt) && info && !list_empty(info->cols) &&
ATOMlinear(exp_subtype(e)->type->localtype)) {
+ for (node *nn = info->cols->h ; nn && !skip; nn
= nn->next) { /* test if it passes all predicates around it */
+ if (nn->data == e) {
+ range_limit *next =
list_fetch(info->ranges, i);
+ atom *lval = next->lval, *hval
= next->hval;
+ list *values = next->values;
+
+ /* I don't handle cmp_in or
cmp_notin cases with anti or null semantics yet */
+ if (next->flag == cmp_in &&
(next->anti || next->semantics))
+ continue;
+
+ assert(col && (lval || values));
+ if (!skip && pt->access ==
TABLE_READONLY) {
+ /* check if the part
falls within the bounds of the select expression else skip this (keep at least
on part-table) */
+ if (!cmin && !cmax &&
first_attempt) {
+ char *min =
NULL, *max = NULL;
+ (void)
sql_trans_ranges(v->sql->session->tr, col, &min, &max);
+ if (min && max)
{
+ cmin =
atom_general(v->sql->sa, &col->type, min);
+ cmax =
atom_general(v->sql->sa, &col->type, max);
+ }
+ first_attempt =
false; /* no more attempts to read from storage */
+ }
+
+ if (cmin && cmax) {
+ if (lval) {
+ if
(!next->semantics && ((lval && lval->isnull) || (hval && hval->isnull))) {
+
skip = 1; /* NULL values don't match, skip them */
+ } else
if (!next->semantics) {
+
if (next->flag == cmp_equal) {
+
skip |= next->anti ? exp_range_overlap(cmin, cmax, lval, hval, false,
false) != 0 :
+
exp_range_overlap(cmin,
cmax, lval, hval, false, false) == 0;
+
} else if (hval != lval) { /* range case */
+
comp_type lower = range2lcompare(next->flag), higher =
range2rcompare(next->flag);
+
skip |= next->anti ? exp_range_overlap(cmin, cmax, lval, hval, higher
== cmp_lt, lower == cmp_gt) != 0 :
+
exp_range_overlap(cmin,
cmax, lval, hval, higher == cmp_lt, lower == cmp_gt) == 0;
+
} else {
+
switch (next->flag) {
+
case cmp_gt:
+
skip |= next->anti ? VALcmp(&(lval->data),
&(cmax->data)) < 0 : VALcmp(&(lval->data), &(cmax->data)) >= 0;
+
break;
+
case cmp_gte:
+
skip |= next->anti ? VALcmp(&(lval->data),
&(cmax->data)) <= 0 : VALcmp(&(lval->data), &(cmax->data)) > 0;
+
break;
+
case cmp_lt:
+
skip |= next->anti ? VALcmp(&(lval->data),
&(cmax->data)) < 0 : VALcmp(&(cmin->data), &(lval->data)) >= 0;
+
break;
+
case cmp_lte:
+
skip |= next->anti ? VALcmp(&(lval->data),
&(cmax->data)) <= 0 : VALcmp(&(cmin->data), &(lval->data)) > 0;
+
break;
+
default:
+
break;
+
}
+
}
+ }
+ } else if
(next->flag == cmp_in) {
+ int
nskip = 1;
+ for
(node *m = values->h; m && nskip; m = m->next) {
+
atom *a = m->data;
+
+
if (a->isnull)
+
continue;
+
nskip &= exp_range_overlap(cmin, cmax, a, a, false, false) == 0;
+ }
+ skip |=
nskip;
+ }
+ }
+ }
+ if (!skip &&
isPartitionedByColumnTable(mt) && strcmp(mt->part.pcol->base.name,
col->base.name) == 0) {
+ if (!next->semantics &&
((lval && lval->isnull) || (hval && hval->isnull))) {
+ skip = 1; /*
NULL values don't match, skip them */
+ } else if
(next->semantics) {
+ /* TODO NOT
NULL prunning for partitions that just hold NULL values is still missing */
+ skip |=
next->flag == cmp_equal && !next->anti && lval && lval->isnull ? pd->with_nills
== 0 : 0; /* *= NULL case */
+ } else {
+ if
(isRangePartitionTable(mt)) {
+ if
(!rmin || !rmax) { /* initialize lazily */
+
rmin = atom_general_ptr(v->sql->sa, &col->type, pd->part.range.minvalue);
+
rmax = atom_general_ptr(v->sql->sa, &col->type, pd->part.range.maxvalue);
+ }
+
+ /*
Prune range partitioned tables */
+ if
(rmin->isnull && rmax->isnull) {
+
if (pd->with_nills == 1) /* the partition just holds null values, skip it */
+
skip = 1;
+
/* otherwise it holds all values in the range, cannot be pruned */
+ } else
if (rmin->isnull) { /* MINVALUE to limit */
+
if (lval) {
+
if (hval != lval) { /* range case */
+
/* There's need to call range2lcompare, because the partition's
upper limit is always exclusive */
+
skip |= next->anti ? VALcmp(&(lval->data), &(rmax->data)) < 0 :
VALcmp(&(lval->data), &(rmax->data)) >= 0;
+
} else {
+
switch (next->flag) { /* upper limit always exclusive */
+
case cmp_equal:
+
case cmp_gt:
+
case cmp_gte:
+
skip |= next->anti ? VALcmp(&(lval->data),
&(rmax->data)) < 0 : VALcmp(&(lval->data), &(rmax->data)) >= 0;
+
break;
+
default:
+
break;
+
}
+
}
+
} else if (next->flag == cmp_in) {
+
int nskip = 1;
+
for (node *m = values->h; m && nskip; m = m->next) {
+
atom *a = m->data;
+
+
if (a->isnull)
+
continue;
+
nskip &= VALcmp(&(a->data), &(rmax->data)) >= 0;
+
}
+
skip |= nskip;
+
}
+ } else
if (rmax->isnull) { /* limit to MAXVALUE */
+
if (lval) {
+
if (hval != lval) { /* range case */
+
comp_type higher = range2rcompare(next->flag);
+
if (higher == cmp_lt) {
+
skip |= next->anti ? VALcmp(&(rmin->data),
&(hval->data)) < 0 : VALcmp(&(rmin->data), &(hval->data)) >= 0;
+
} else if (higher == cmp_lte) {
+
skip |= next->anti ? VALcmp(&(rmin->data),
&(hval->data)) <= 0 : VALcmp(&(rmin->data), &(hval->data)) > 0;
+
} else {
+
assert(0);
+
}
+
} else {
+
switch (next->flag) {
+
case cmp_lt:
+
skip |= next->anti ? VALcmp(&(rmin->data),
&(hval->data)) < 0 : VALcmp(&(rmin->data), &(hval->data)) >= 0;
+
break;
+
case cmp_equal:
+
case cmp_lte:
+
skip |= next->anti ? VALcmp(&(rmin->data),
&(hval->data)) <= 0 : VALcmp(&(rmin->data), &(hval->data)) > 0;
+
break;
+
default:
+
break;
+
}
+
}
+
} else if (next->flag == cmp_in) {
+
int nskip = 1;
+
for (node *m = values->h; m && nskip; m = m->next) {
+
atom *a = m->data;
+
+
if (a->isnull)
+
continue;
+
nskip &= VALcmp(&(rmin->data), &(a->data)) > 0;
+
}
+
skip |= nskip;
+
}
+ } else
{ /* limit1 to limit2 (general case), limit2 is exclusive */
+
bool max_differ_min = ATOMcmp(col->type.type->localtype, &rmin->data.val,
&rmax->data.val) != 0;
+
+
if (lval) {
+
if (next->flag == cmp_equal) {
+
skip |= next->anti ? exp_range_overlap(rmin, rmax, lval, hval,
false, max_differ_min) != 0 :
+
exp_range_overlap(rmin, rmax, lval, hval, false, max_differ_min) == 0;
+
} else if (hval != lval) { /* For the between case */
+
comp_type higher = range2rcompare(next->flag);
+
skip |= next->anti ? exp_range_overlap(rmin, rmax, lval, hval,
higher == cmp_lt, max_differ_min) != 0 :
+
exp_range_overlap(rmin, rmax, lval, hval, higher == cmp_lt, max_differ_min) ==
0;
+
} else {
+
switch (next->flag) {
+
case cmp_gt:
+
skip |= next->anti ? VALcmp(&(lval->data),
&(rmax->data)) < 0 : VALcmp(&(lval->data), &(rmax->data)) >= 0;
+
break;
+
case cmp_gte:
+
if (max_differ_min)
+
skip |= next->anti ?
VALcmp(&(lval->data), &(rmax->data)) < 0 : VALcmp(&(lval->data), &(rmax->data))
>= 0;
+
else
+
skip |= next->anti ?
VALcmp(&(lval->data), &(rmax->data)) <= 0 : VALcmp(&(lval->data),
&(rmax->data)) > 0;
+
break;
+
case cmp_lt:
+
skip |= next->anti ? VALcmp(&(rmin->data),
&(lval->data)) < 0 : VALcmp(&(rmin->data), &(lval->data)) >= 0;
+
break;
+
case cmp_lte:
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
