Changeset: 79a9fe10c2c2 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/79a9fe10c2c2
Added Files:
sql/test/merge-statements/Tests/mergestmt05.test
Modified Files:
monetdb5/optimizer/opt_emptybind.c
sql/backends/monet5/rel_bin.c
sql/backends/monet5/rel_physical.c
sql/include/sql_relation.h
sql/server/rel_distribute.c
sql/server/rel_dump.c
sql/server/rel_exp.c
sql/server/rel_optimize_others.c
sql/server/rel_optimize_proj.c
sql/server/rel_optimize_sel.c
sql/server/rel_optimizer_private.h
sql/server/rel_propagate.c
sql/server/rel_rel.c
sql/server/rel_rewriter.c
sql/server/rel_updates.c
sql/server/sql_partition.c
sql/test/dict/Tests/dict01.test
sql/test/merge-statements/Tests/All
Branch: default
Log Message:
re-implemented merge statements using normal insert/delete and updates
added support for additional search conditions after the WHEN [NOT] MATCHED.
diffs (truncated from 709 to 300 lines):
diff --git a/monetdb5/optimizer/opt_emptybind.c
b/monetdb5/optimizer/opt_emptybind.c
--- a/monetdb5/optimizer/opt_emptybind.c
+++ b/monetdb5/optimizer/opt_emptybind.c
@@ -141,13 +141,8 @@ OPTemptybindImplementation(Client cntxt,
if (q && getModuleId(q) == sqlRef &&
isUpdateInstruction(q)) {
int c = getFunctionId(q) == claimRef;
/* claim has 2 results */
int cl = getFunctionId(q) ==
clear_tableRef; /* clear table has no mvc dependency */
- if (strcmp(getVarConstant(mb, getArg(q,
-
2 - cl + c)).val.sval,
- sch) == 0
- && strcmp(getVarConstant(mb,
-
getArg(q,
-
3 - cl + c)).val.sval,
- tbl) == 0) {
+ if (strcmp(getVarConstant(mb, getArg(q,
2 - cl + c)).val.sval, sch) == 0
+ && strcmp(getVarConstant(mb,
getArg(q, 3 - cl + c)).val.sval, tbl) == 0) {
empty[getArg(p, 0)] = 0;
if (p->retc == 2) {
empty[getArg(p, 1)] = 0;
@@ -156,8 +151,7 @@ OPTemptybindImplementation(Client cntxt,
}
}
if (q && getModuleId(q) == sqlcatalogRef) {
- if (strcmp(getVarConstant(mb, getArg(q,
2)).val.sval, sch)
- == 0) {
+ if (strcmp(getVarConstant(mb, getArg(q,
2)).val.sval, sch) == 0) {
empty[getArg(p, 0)] = 0;
if (p->retc == 2) {
empty[getArg(p, 1)] = 0;
diff --git a/sql/backends/monet5/rel_bin.c b/sql/backends/monet5/rel_bin.c
--- a/sql/backends/monet5/rel_bin.c
+++ b/sql/backends/monet5/rel_bin.c
@@ -2465,7 +2465,6 @@ rel2bin_args(backend *be, sql_rel *rel,
case op_inter:
case op_except:
- case op_merge:
args = rel2bin_args(be, rel->l, args);
args = rel2bin_args(be, rel->r, args);
break;
@@ -7273,133 +7272,6 @@ rel2bin_output(backend *be, sql_rel *rel
return res;
}
-static list *
-merge_stmt_join_projections(backend *be, stmt *left, stmt *right, stmt *jl,
stmt *jr, stmt *diff)
-{
- mvc *sql = be->mvc;
- list *l = sa_list(sql->sa);
-
- if (left)
- for (node *n = left->op4.lval->h; n; n = n->next) {
- stmt *c = n->data;
- assert(c->type == st_alias);
- const char *rnme = table_name(sql->sa, c);
- const char *nme = column_name(sql->sa, c);
- stmt *s = stmt_project(be, jl ? jl : diff, column(be,
c));
-
- s = stmt_alias(be, s, c->label, rnme, nme);
- list_append(l, s);
- }
- if (right)
- for (node *n = right->op4.lval->h; n; n = n->next) {
- stmt *c = n->data;
- assert(c->type == st_alias);
- const char *rnme = table_name(sql->sa, c);
- const char *nme = column_name(sql->sa, c);
- stmt *s = stmt_project(be, jr ? jr : diff, column(be,
c));
-
- s = stmt_alias(be, s, c->label, rnme, nme);
- list_append(l, s);
- }
- return l;
-}
-
-static void
-validate_merge_delete_update(backend *be, bool delete, stmt *bt_stmt, sql_rel
*bt, stmt *jl, stmt *ld)
-{
- mvc *sql = be->mvc;
- str msg;
- sql_table *t = bt->l;
- char *alias = (char *) rel_name(bt);
- stmt *cnt1 = stmt_aggr(be, jl, NULL, NULL, sql_bind_func(sql, "sys",
"count", sql_bind_localtype("void"), NULL, F_AGGR, true, true), 1, 0, 1);
- stmt *cnt2 = stmt_aggr(be, ld, NULL, NULL, sql_bind_func(sql, "sys",
"count", sql_bind_localtype("void"), NULL, F_AGGR, true, true), 1, 0, 1);
- sql_subfunc *add = sql_bind_func(sql, "sys", "sql_add",
tail_type(cnt1), tail_type(cnt2), F_FUNC, true, true);
- stmt *s1 = stmt_binop(be, cnt1, cnt2, NULL, add);
- stmt *cnt3 = stmt_aggr(be, bin_find_smallest_column(be, bt_stmt), NULL,
NULL, sql_bind_func(sql, "sys", "count", sql_bind_localtype("void"), NULL,
F_AGGR, true, true), 1, 0, 1);
- sql_subfunc *bf = sql_bind_func(sql, "sys", ">", tail_type(s1),
tail_type(cnt3), F_FUNC, true, true);
- stmt *s2 = stmt_binop(be, s1, cnt3, NULL, bf);
-
- if (alias && strcmp(alias, t->base.name) == 0) /* detect if alias is
present */
- alias = NULL;
- msg = sa_message(sql->sa, SQLSTATE(40002) "MERGE %s: Multiple rows in
the input relation match the same row in the target %s '%s%s%s'",
- delete ? "DELETE" : "UPDATE",
- alias ? "relation" : "table",
- alias ? alias : t->s ? t->s->base.name
: "", alias ? "" : ".", alias ? "" : t->base.name);
- (void)stmt_exception(be, s2, msg, 00001);
-}
-
-static stmt *
-rel2bin_merge_apply_update(backend *be, sql_rel *join, sql_rel *upd, list
*refs, stmt *bt_stmt, stmt *target_stmt, stmt *jl, stmt *jr, stmt *ld, stmt
**rd)
-{
- if (is_insert(upd->op)) {
- if (!*rd) {
- *rd = stmt_tdiff(be, stmt_mirror(be,
bin_find_smallest_column(be, target_stmt)), jr, NULL);
- }
- stmt *s = stmt_list(be, merge_stmt_join_projections(be, NULL,
target_stmt, NULL, NULL, *rd));
- refs_update_stmt(refs, join, s); /* project the differences on
the target side for inserts */
-
- return rel2bin_insert(be, upd, refs);
- } else {
- stmt *s = stmt_list(be, merge_stmt_join_projections(be,
bt_stmt, is_update(upd->op) ? target_stmt : NULL, jl, is_update(upd->op) ? jr :
NULL, NULL));
- refs_update_stmt(refs, join, s); /* project the matched values
on both sides for updates and deletes */
-
- assert(is_update(upd->op) || is_delete(upd->op));
- /* the left joined values + left difference must be smaller
than the table count */
- validate_merge_delete_update(be, is_update(upd->op), bt_stmt,
join->l, jl, ld);
-
- return is_update(upd->op) ? rel2bin_update(be, upd, refs) :
rel2bin_delete(be, upd, refs);
- }
-}
-
-static stmt *
-rel2bin_merge(backend *be, sql_rel *rel, list *refs)
-{
- mvc *sql = be->mvc;
- sql_rel *join;
-
- if (is_project(((sql_rel*)rel->l)->op)) {
- join = ((sql_rel*)rel->l)->l;
- } else {
- join = rel->l;
- }
-
- sql_rel *r = rel->r;
- stmt *join_st, *bt_stmt, *target_stmt, *jl, *jr, *ld, *rd = NULL, *ns;
- list *slist = sa_list(sql->sa);
-
- assert(rel_is_ref(join) && is_left(join->op));
- join_st = subrel_bin(be, join, refs);
- if (!join_st)
- return NULL;
-
- /* grab generated left join outputs and generate updates accordingly to
matched and not matched values */
- assert(join_st->type == st_list && list_length(join_st->extra) == 5);
- bt_stmt = join_st->extra->h->data;
- target_stmt = join_st->extra->h->next->data;
- jl = join_st->extra->h->next->next->data;
- jr = join_st->extra->h->next->next->next->data;
- ld = join_st->extra->h->next->next->next->next->data;
-
- if (is_ddl(r->op)) {
- assert(r->flag == ddl_list);
- if (r->l) {
- if ((ns = rel2bin_merge_apply_update(be, join, r->l,
refs, bt_stmt, target_stmt, jl, jr, ld, &rd)) == NULL)
- return NULL;
- list_append(slist, ns);
- }
- if (r->r) {
- if ((ns = rel2bin_merge_apply_update(be, join, r->r,
refs, bt_stmt, target_stmt, jl, jr, ld, &rd)) == NULL)
- return NULL;
- list_append(slist, ns);
- }
- } else {
- if (!(ns = rel2bin_merge_apply_update(be, join, r, refs,
bt_stmt, target_stmt, jl, jr, ld, &rd)))
- return NULL;
- list_append(slist, ns);
- }
- return stmt_list(be, slist);
-}
-
static stmt *
rel2bin_list(backend *be, sql_rel *rel, list *refs)
{
@@ -7871,11 +7743,6 @@ subrel_bin(backend *be, sql_rel *rel, li
if (sql->type == Q_TABLE)
sql->type = Q_UPDATE;
break;
- case op_merge:
- s = rel2bin_merge(be, rel, refs);
- if (sql->type == Q_TABLE)
- sql->type = Q_UPDATE;
- break;
case op_ddl:
s = rel2bin_ddl(be, rel, refs);
break;
diff --git a/sql/backends/monet5/rel_physical.c
b/sql/backends/monet5/rel_physical.c
--- a/sql/backends/monet5/rel_physical.c
+++ b/sql/backends/monet5/rel_physical.c
@@ -89,7 +89,6 @@ find_basetables(mvc *sql, sql_rel *rel,
case op_insert:
case op_update:
case op_delete:
- case op_merge:
if (rel->l)
find_basetables(sql, rel->l, tables);
if (rel->r)
@@ -174,8 +173,6 @@ has_groupby(sql_rel *rel)
case op_inter:
case op_except:
-
- case op_merge:
return has_groupby(rel->l) || has_groupby(rel->r);
case op_munion:
for (node *n = ((list*)rel->l)->h; n; n = n->next)
@@ -231,8 +228,6 @@ rel_partition(mvc *sql, sql_rel *rel)
case op_inter:
case op_except:
-
- case op_merge:
if (rel->l)
rel_partition(sql, rel->l);
if (rel->r)
diff --git a/sql/include/sql_relation.h b/sql/include/sql_relation.h
--- a/sql/include/sql_relation.h
+++ b/sql/include/sql_relation.h
@@ -174,7 +174,6 @@ typedef enum operator_type {
op_update, /* update(l=table, r update expressions) */
op_delete, /* delete(l=table, r delete expression) */
op_truncate, /* truncate(l=table) */
- op_merge /* IMPORTANT: keep op_merge last */
} operator_type;
#define is_atom(et) (et == e_atom)
@@ -211,13 +210,12 @@ typedef enum operator_type {
#define is_project(op) (op == op_project || op == op_groupby
|| is_set(op) || is_munion(op))
#define is_groupby(op) (op == op_groupby)
#define is_topn(op) (op == op_topn)
-#define is_modify(op) (op == op_insert || op == op_update || op ==
op_delete || op == op_truncate || op == op_merge)
+#define is_modify(op) (op == op_insert || op == op_update || op ==
op_delete || op == op_truncate)
#define is_sample(op) (op == op_sample)
#define is_insert(op) (op == op_insert)
#define is_update(op) (op == op_update)
#define is_delete(op) (op == op_delete)
#define is_truncate(op) (op == op_truncate)
-#define is_merge(op) (op == op_merge)
/* ZERO on empty sets, needed for sum (of counts)). */
#define zero_if_empty(e) ((e)->zero_if_empty)
diff --git a/sql/server/rel_distribute.c b/sql/server/rel_distribute.c
--- a/sql/server/rel_distribute.c
+++ b/sql/server/rel_distribute.c
@@ -74,7 +74,6 @@ has_remote_or_replica( sql_rel *rel )
case op_inter:
case op_except:
- case op_merge:
case op_insert:
case op_update:
@@ -410,7 +409,6 @@ rel_rewrite_remote_(visitor *v, sql_rel
case op_insert:
case op_update:
case op_delete:
- case op_merge:
if (rel->flag&MERGE_LEFT) /* search for any remote tables but
don't propagate over to this relation */
return rel;
diff --git a/sql/server/rel_dump.c b/sql/server/rel_dump.c
--- a/sql/server/rel_dump.c
+++ b/sql/server/rel_dump.c
@@ -702,17 +702,13 @@ rel_print_rel(mvc *sql, stream *fout, s
case op_insert:
case op_update:
case op_delete:
- case op_truncate:
- case op_merge: {
-
+ case op_truncate: {
if (rel->op == op_insert)
mnstr_printf(fout, "insert(");
else if (rel->op == op_update)
mnstr_printf(fout, "update(");
else if (rel->op == op_delete)
mnstr_printf(fout, "delete(");
- else if (rel->op == op_merge)
- mnstr_printf(fout, "merge(");
else if (rel->op == op_truncate) {
assert(list_length(rel->exps) == 2);
sql_exp *first = (sql_exp*) rel->exps->h->data, *second
= (sql_exp*) rel->exps->h->next->data;
@@ -740,7 +736,7 @@ rel_print_rel(mvc *sql, stream *fout, s
}
print_indent(sql, fout, depth, decorate);
mnstr_printf(fout, ")");
- if (rel->op != op_truncate && rel->op != op_merge && rel->exps)
+ if (rel->op != op_truncate && rel->exps)
exps_print(sql, fout, rel->exps, depth, refs, 1, 0,
decorate, 0);
} break;
default:
@@ -839,7 +835,6 @@ rel_print_refs(mvc *sql, stream* fout, s
case op_update:
case op_delete:
case op_truncate:
- case op_merge:
if (rel->l)
rel_print_refs(sql, fout, rel->l, depth, refs,
decorate);
if (rel->l && rel_is_ref(rel->l) && !find_ref(refs, rel->l)) {
diff --git a/sql/server/rel_exp.c b/sql/server/rel_exp.c
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
