Changeset: d66505ca653e for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d66505ca653e
Modified Files:
sql/server/rel_optimizer.c
sql/server/rel_select.c
sql/server/rel_select.h
sql/test/BugTracker-2016/Tests/LEFT-JOIN_with_OR_conditions_triggers_assertion.Bug-3908.stable.out
sql/test/BugTracker-2016/Tests/incorrect_column_name_in_OR_condition_of_LEFT-JOIN_crashes_mserver.Bug-3909.stable.err
Branch: Jul2015
Log Message:
rewrite outer-joins with or expressions, fixes bug 3908.
diffs (221 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
@@ -6386,6 +6386,112 @@ rel_semijoin_use_fk(int *changes, mvc *s
return rel;
}
+/* leftouterjoin(a,b)[ a.C op b.D or a.E op2 b.F ]) ->
+ * union(
+ * join(a,b)[ a.C op b.D or a.E op2 b. F ],
+ * project(
+ * antijoin(a,b) [a.C op b.D or a.E op2 b.F ])
+ * [ a.*, NULL * foreach column of b]
+ * )
+ */
+static int
+exps_nr_of_or(list *exps)
+{
+ int ors = 0;
+ node *n;
+
+ if (!exps)
+ return ors;
+ for(n=exps->h; n; n = n->next) {
+ sql_exp *e = n->data;
+
+ if (e->type == e_cmp && e->flag == cmp_or)
+ ors++;
+ }
+ return ors;
+}
+
+static void
+add_nulls(mvc *sql, sql_rel *rel, sql_rel *r)
+{
+ list *exps;
+ node *n;
+
+ exps = rel_projections(sql, r, NULL, 1, 1);
+ for(n = exps->h; n; n = n->next) {
+ sql_exp *e = n->data, *ne;
+
+ ne = exp_atom(sql->sa, atom_general(sql->sa, exp_subtype(e),
NULL));
+ exp_setname(sql->sa, ne, exp_relname(e), exp_name(e));
+ append(rel->exps, ne);
+ }
+}
+
+static sql_rel *
+rel_split_outerjoin(int *changes, mvc *sql, sql_rel *rel)
+{
+ if ((rel->op == op_left || rel->op == op_right || rel->op == op_full)
&&
+ list_length(rel->exps) && exps_nr_of_or(rel->exps) ==
list_length(rel->exps)) {
+ sql_rel *l = rel_dup(rel->l), *nl, *nll, *nlr;
+ sql_rel *r = rel_dup(rel->r), *nr;
+ sql_exp *e;
+
+ nll = rel_crossproduct(sql->sa, l, r, op_join);
+ nlr = rel_crossproduct(sql->sa, l, r, op_join);
+
+ /* TODO find or exp, ie handle rest with extra joins */
+ /* expect only a single or expr for now */
+ assert(list_length(rel->exps) == 1);
+ e = rel->exps->h->data;
+ nll->exps = exps_copy(sql->sa, e->l);
+ nlr->exps = exps_copy(sql->sa, e->r);
+ nl = rel_or( sql, nll, nlr, NULL, e->l, e->r);
+
+ if (rel->op == op_full) {
+ l = rel_dup(l);
+ r = rel_dup(r);
+ }
+
+ if (rel->op == op_left || rel->op == op_full) {
+ /* split in 2 anti joins */
+ nr = rel_crossproduct(sql->sa, l, r, op_anti);
+ nr->exps = exps_copy(sql->sa, e->l);
+ nr = rel_crossproduct(sql->sa, nr, r, op_anti);
+ nr->exps = exps_copy(sql->sa, e->r);
+
+ /* project left */
+ nr = rel_project(sql->sa, nr,
+ rel_projections(sql, l, NULL, 1, 1));
+ /* add null's for right */
+ add_nulls( sql, nr, r);
+ nl = rel_setop(sql->sa, nl, nr, op_union);
+ }
+ if (rel->op == op_right || rel->op == op_full) {
+ /* split in 2 anti joins */
+ nr = rel_crossproduct(sql->sa, r, l, op_anti);
+ nr->exps = exps_copy(sql->sa, e->l);
+ nr = rel_crossproduct(sql->sa, nr, l, op_anti);
+ nr->exps = exps_copy(sql->sa, e->r);
+
+ nr = rel_project(sql->sa, nr, sa_list(sql->sa));
+ /* add null's for left */
+ add_nulls( sql, nr, l);
+ /* project right */
+ nr->exps = list_merge(nr->exps,
+ rel_projections(sql, r, NULL, 1, 1),
+ (fdup)NULL);
+ nl = rel_setop(sql->sa, nl, nr, op_union);
+ }
+
+ rel->l = NULL;
+ rel->r = NULL;
+ rel_destroy(rel);
+ *changes = 1;
+ rel = nl;
+ }
+ return rel;
+}
+
/* rewrite sqltype into backend types */
static sql_rel *
rel_rewrite_types(int *changes, mvc *sql, sql_rel *rel)
@@ -7089,7 +7195,9 @@ static sql_rel *
return rel;
}
rel = rel_project(sql->sa, rel, rel_projections(sql, rel, NULL, 1, 1));
- e = rel_unop_(sql, rel->exps->h->data, NULL, "identity", card_value);
+ //e = rel_unop_(sql, rel->exps->h->data, NULL, "identity", card_value);
+ e = rel->exps->h->data;
+ e = exp_unop(sql->sa, e, sql_bind_func(sql->sa, NULL, "identity",
exp_subtype(e), NULL, F_FUNC));
e->p = prop_create(sql->sa, PROP_HASHCOL, e->p);
*exp = exp_label(sql->sa, e, ++sql->label);
rel_project_add_exp(sql, rel, e);
@@ -7587,6 +7695,8 @@ static sql_rel *
if (level <= 0)
rel = rewrite_topdown(sql, rel, &rel_semijoin_use_fk,
&changes);
}
+ if (gp.cnt[op_left] || gp.cnt[op_right] || gp.cnt[op_full])
+ rel = rewrite_topdown(sql, rel, &rel_split_outerjoin, &changes);
if (gp.cnt[op_select]) {
/* only once */
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -2660,8 +2660,8 @@ rel_compare(mvc *sql, sql_rel *rel, symb
return rel_compare_exp(sql, rel, ls, rs, compare_op, rs2, k.reduce);
}
-static sql_rel *
-rel_or(mvc *sql, sql_rel *l, sql_rel *r, list *oexps, list *lexps, list
*rexps, int f)
+sql_rel *
+rel_or(mvc *sql, sql_rel *l, sql_rel *r, list *oexps, list *lexps, list *rexps)
{
sql_rel *rel, *ll = l->l, *rl = r->l;
@@ -2676,7 +2676,7 @@ rel_or(mvc *sql, sql_rel *l, sql_rel *r,
}
if (l->op == r->op &&
- ((ll == rl && l->r == r->r) ||
+ ((ll == rl && l->r == r->r && l->r == NULL /* or check if
columns are equal*/) ||
(exps_card(l->exps) == exps_card(r->exps) && exps_card(l->exps)
<= CARD_ATOM))) {
sql_exp *e = exp_or(sql->sa, l->exps, r->exps);
list *nl = new_exp_list(sql->sa);
@@ -2686,7 +2686,6 @@ rel_or(mvc *sql, sql_rel *l, sql_rel *r,
l->exps = nl;
return l;
}
- (void)f;
l = rel_project(sql->sa, l, rel_projections(sql, l, NULL, 1, 1));
r = rel_project(sql->sa, r, rel_projections(sql, r, NULL, 1, 1));
set_processed(l);
@@ -3118,7 +3117,7 @@ rel_logical_exp(mvc *sql, sql_rel *rel,
if (!lr || !rr)
return NULL;
- return rel_or(sql, lr, rr, exps, lexps, rexps, f);
+ return rel_or(sql, lr, rr, exps, lexps, rexps);
}
case SQL_AND:
{
diff --git a/sql/server/rel_select.h b/sql/server/rel_select.h
--- a/sql/server/rel_select.h
+++ b/sql/server/rel_select.h
@@ -49,6 +49,7 @@ extern void rel_join_add_exp(sql_allocat
extern sql_rel *rel_push_select(mvc *sql, sql_rel *rel, sql_exp *ls, sql_exp
*e);
extern sql_rel *rel_push_join(mvc *sql, sql_rel *rel, sql_exp *ls, sql_exp
*rs, sql_exp *rs2, sql_exp *e);
+extern sql_rel *rel_or(mvc *sql, sql_rel *l, sql_rel *r, list *oexps, list
*lexps, list *rexps);
/* TODO rename to exp_check_type + move to rel_exp.mx */
extern sql_exp *rel_check_type(mvc *sql, sql_subtype *t, sql_exp *exp, int
tpe);
extern int rel_convert_types(mvc *sql, sql_exp **L, sql_exp **R, int
scale_fixing, int tpe);
diff --git
a/sql/test/BugTracker-2016/Tests/LEFT-JOIN_with_OR_conditions_triggers_assertion.Bug-3908.stable.out
b/sql/test/BugTracker-2016/Tests/LEFT-JOIN_with_OR_conditions_triggers_assertion.Bug-3908.stable.out
---
a/sql/test/BugTracker-2016/Tests/LEFT-JOIN_with_OR_conditions_triggers_assertion.Bug-3908.stable.out
+++
b/sql/test/BugTracker-2016/Tests/LEFT-JOIN_with_OR_conditions_triggers_assertion.Bug-3908.stable.out
@@ -80,10 +80,10 @@ Ready.
% .b, .f # table_name
% value, id # name
% char, int # type
-% 10, 1 # length
+% 10, 3 # length
[ "aaa", 100 ]
+[ "bbb", 101 ]
[ "bbb", 100 ]
-[ "bbb", 101 ]
[ "ccc", 101 ]
#DROP TABLE bar;
#DROP TABLE foo;
diff --git
a/sql/test/BugTracker-2016/Tests/incorrect_column_name_in_OR_condition_of_LEFT-JOIN_crashes_mserver.Bug-3909.stable.err
b/sql/test/BugTracker-2016/Tests/incorrect_column_name_in_OR_condition_of_LEFT-JOIN_crashes_mserver.Bug-3909.stable.err
---
a/sql/test/BugTracker-2016/Tests/incorrect_column_name_in_OR_condition_of_LEFT-JOIN_crashes_mserver.Bug-3909.stable.err
+++
b/sql/test/BugTracker-2016/Tests/incorrect_column_name_in_OR_condition_of_LEFT-JOIN_crashes_mserver.Bug-3909.stable.err
@@ -30,13 +30,7 @@ stderr of test 'incorrect_column_name_in
# 12:49:55 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-29239" "--port=35896"
# 12:49:55 >
-MAPI = (monetdb) /var/tmp/mtest-15285/.s.monetdb.30430
-QUERY = CREATE TABLE foo (id INTEGER, bar1 INTEGER, bar2 INTEGER);
-ERROR = !CREATE TABLE: name 'foo' already in use
-MAPI = (monetdb) /var/tmp/mtest-15285/.s.monetdb.30430
-QUERY = CREATE TABLE bar (barid INTEGER, value CHAR(10));
-ERROR = !CREATE TABLE: name 'bar' already in use
-MAPI = (monetdb) /var/tmp/mtest-15285/.s.monetdb.30430
+MAPI = (monetdb) /var/tmp/mtest-17623/.s.monetdb.37506
QUERY = SELECT B.value, F.id FROM bar B LEFT JOIN foo F ON (F.bar1 = B.barid
OR F.bar2 = B.id);
ERROR = !SELECT: no such column 'b.id'
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
