Changeset: 19f676b55173 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=19f676b55173
Modified Files:
sql/backends/monet5/rel_bin.c
sql/server/rel_optimizer.c
sql/server/rel_select.c
sql/test/BugTracker-2014/Tests/temporary.Bug-3430.stable.err
sql/test/Dependencies/Tests/Dependencies.stable.out
sql/test/Dependencies/Tests/Dependencies.stable.out.int128
Branch: Mar2018
Log Message:
fixes for bug 6602, ie make sure we handle null's in NOT in (left and right
side are handled in the rel2bin code now)
diffs (truncated from 401 to 300 lines):
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
@@ -28,6 +28,15 @@ static stmt * subrel_bin(backend *be, sq
static stmt *check_types(backend *be, sql_subtype *ct, stmt *s, check_type
tpe);
static stmt *
+stmt_selectnil( backend *be, stmt *col)
+{
+ sql_subtype *t = tail_type(col);
+ stmt *n = stmt_atom(be, atom_general(be->mvc->sa, t, NULL));
+ stmt *nn = stmt_uselect2(be, col, n, n, 3, NULL, 0);
+ return nn;
+}
+
+static stmt *
sql_unop_(backend *be, sql_schema *s, const char *fname, stmt *rs)
{
mvc *sql = be->mvc;
@@ -1946,6 +1955,53 @@ rel2bin_semijoin(backend *be, sql_rel *r
* first cheap join(s) (equality or idx)
* second selects/filters
*/
+ if (rel->exps && rel->op == op_anti && need_no_nil(rel)) {
+ list *l;
+ stmt *sel = NULL;
+
+ for( en = rel->exps->h; en; en = en->next ) {
+ sql_exp *e = en->data, *r, *l;
+
+ if (e->type != e_cmp || e->flag != cmp_equal)
+ break;
+ l = e->l;
+ r = e->r;
+
+ /* for each equality join add a rel_select(r is NULL) */
+ stmt *s = exp_bin(be, r, right, NULL, NULL, NULL, NULL,
NULL);
+ if (!s)
+ s = exp_bin(be, l, right, NULL, NULL, NULL,
NULL, NULL);
+ if (s && !exp_is_atom(r)) {
+ sql_subtype *lng = sql_bind_localtype("lng");
+ sql_subaggr *cnt = sql_bind_aggr(sql->sa,
sql->session->schema, "count", NULL);
+ sql_subtype *bt = sql_bind_localtype("bit");
+ sql_subfunc *ne = sql_bind_func_result(sql->sa,
sql->session->schema, "<>", lng, lng, bt);
+
+ stmt *l;
+ s = stmt_selectnil(be, s);
+ s = stmt_binop(be, stmt_aggr(be, s, NULL, NULL,
cnt, 1, 0, 1), stmt_atom_lng(be, 0), ne);
+
+ l = bin_first_column(be, left);
+ /* keep if no nulls are in the right side */
+ l = stmt_const(be, l, stmt_bool(be,0));
+ sel = stmt_uselect(be, l, s, cmp_equal, sel, 0);
+ }
+
+ }
+ l = sa_list(sql->sa);
+ if (left && sel) {
+ for( n = left->op4.lval->h; n; n = n->next ) {
+ stmt *col = n->data;
+
+ if (col->nrcols == 0) /* constant */
+ col = stmt_const(be, sel, col);
+ else
+ col = stmt_project(be, sel, col);
+ list_append(l, col);
+ }
+ left = stmt_list(be, l);
+ }
+ }
if (rel->exps) {
int idx = 0;
list *lje = sa_list(sql->sa);
@@ -2993,15 +3049,6 @@ stmt_selectnonil( backend *be, stmt *col
}
static stmt *
-stmt_selectnil( backend *be, stmt *col)
-{
- sql_subtype *t = tail_type(col);
- stmt *n = stmt_atom(be, atom_general(be->mvc->sa, t, NULL));
- stmt *nn = stmt_uselect2(be, col, n, n, 3, NULL, 0);
- return nn;
-}
-
-static stmt *
insert_check_ukey(backend *be, list *inserts, sql_key *k, stmt *idx_inserts)
{
mvc *sql = be->mvc;
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
@@ -4579,6 +4579,10 @@ rel_push_join_down_union(int *changes, m
}
nl = rel_crossproduct(sql->sa, ll, rel_dup(or),
rel->op);
nr = rel_crossproduct(sql->sa, lr, rel_dup(or),
rel->op);
+ if (need_no_nil(rel)) {
+ set_no_nil(nl);
+ set_no_nil(nr);
+ }
nl->exps = exps_copy(sql->sa, exps);
nr->exps = exps_copy(sql->sa, exps);
nl = rel_project(sql->sa, nl, rel_projections(sql, nl,
NULL, 1, 1));
@@ -4622,6 +4626,10 @@ rel_push_join_down_union(int *changes, m
}
nl = rel_crossproduct(sql->sa, ll, rl, rel->op);
nr = rel_crossproduct(sql->sa, lr, rr, rel->op);
+ if (need_no_nil(rel)) {
+ set_no_nil(nl);
+ set_no_nil(nr);
+ }
nl->exps = exps_copy(sql->sa, exps);
nr->exps = exps_copy(sql->sa, exps);
nl = rel_project(sql->sa, nl, rel_projections(sql, nl,
NULL, 1, 1));
@@ -4651,6 +4659,10 @@ rel_push_join_down_union(int *changes, m
}
nl = rel_crossproduct(sql->sa, rel_dup(ol), rl,
rel->op);
nr = rel_crossproduct(sql->sa, rel_dup(ol), rr,
rel->op);
+ if (need_no_nil(rel)) {
+ set_no_nil(nl);
+ set_no_nil(nr);
+ }
nl->exps = exps_copy(sql->sa, exps);
nr->exps = exps_copy(sql->sa, exps);
nl = rel_project(sql->sa, nl, rel_projections(sql, nl,
NULL, 1, 1));
@@ -4698,6 +4710,8 @@ rel_push_join_down_union(int *changes, m
rl->exps = exps_copy(sql->sa, or->exps);
}
nl = rel_crossproduct(sql->sa, rel_dup(ol), rl,
rel->op);
+ if (need_no_nil(rel))
+ set_no_nil(nl);
nl->exps = exps_copy(sql->sa, exps);
(*changes)++;
return rel_inplace_project(sql->sa, rel, nl,
rel_projections(sql, rel, NULL, 1, 1));
@@ -4716,6 +4730,8 @@ rel_push_join_down_union(int *changes, m
rr->exps = exps_copy(sql->sa, or->exps);
}
nl = rel_crossproduct(sql->sa, rel_dup(ol), rr,
rel->op);
+ if (need_no_nil(rel))
+ set_no_nil(nl);
nl->exps = exps_copy(sql->sa, exps);
(*changes)++;
return rel_inplace_project(sql->sa, rel, nl,
rel_projections(sql, rel, NULL, 1, 1));
@@ -7586,6 +7602,8 @@ rel_rewrite_antijoin(int *changes, mvc *
rel_rename_exps(sql, r->exps, rr->exps);
nl = rel_crossproduct(sql->sa, rel->l, rl, op_anti);
+ if (need_no_nil(rel))
+ set_no_nil(nl);
nl->exps = exps_copy(sql->sa, rel->exps);
rel->l = nl;
rel->r = rr;
@@ -8860,6 +8878,8 @@ rel_apply_rewrite(int *changes, mvc *sql
sql_rel *rr = rel_dup(r->r);
nl = rel_crossproduct(sql->sa, nl, rr, r->op);
+ if (need_no_nil(r))
+ set_no_nil(nl);
nl->exps = exps_copy(sql->sa, r->exps);
rel_destroy(rel);
rel = nl;
@@ -8868,6 +8888,8 @@ rel_apply_rewrite(int *changes, mvc *sql
sql_rel *rl = rel_dup(r->l);
nr = rel_crossproduct(sql->sa, rl, nr, r->op);
+ if (need_no_nil(r))
+ set_no_nil(nr);
nr->exps = exps_copy(sql->sa, r->exps);
rel_destroy(rel);
rel = nr;
@@ -8877,6 +8899,8 @@ rel_apply_rewrite(int *changes, mvc *sql
sql_rel *nr = rel_apply(sql, rel_dup(rel->l),
rel_dup(r->r), rel->exps, rel->flag);
l = rel_crossproduct(sql->sa, nl, nr, r->op);
+ if (need_no_nil(r))
+ set_no_nil(l);
l->exps = exps_copy(sql->sa, r->exps);
rel_destroy(rel);
(*changes)++;
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
@@ -2539,27 +2539,6 @@ rel_logical_exp(mvc *sql, sql_rel *rel,
ek.card = card_set;
select = rel_select(sql->sa, rel_dup(rel), NULL); /*
dup to make sure we get a new select op */
rel_destroy(rel);
-
- /* first remove the NULLs */
- if (!l_is_value && sc->token == SQL_NOT_IN &&
- l->card != CARD_ATOM && has_nil(l)) {
- sql_exp *ol;
-
- if (l->type != e_column) {
- pexps = rel_projections(sql, rel, NULL,
1, 1);
- rel = rel_project(sql->sa, rel,
rel_projections(sql, rel, NULL, 1, 1));
- select->l = rel;
- l = exp_label(sql->sa, l, ++sql->label);
- append(rel->exps, l);
- ol = l;
- l = exp_column(sql->sa,
exp_relname(ol), exp_name(ol), exp_subtype(ol), ol->card, has_nil(ol),
is_intern(ol));
- }
- e = rel_unop_(sql, l, NULL, "isnull",
card_value);
- e = exp_compare(sql->sa, e,
exp_atom_bool(sql->sa, 0), cmp_equal);
- rel_select_add_exp(sql->sa, select, e);
- if (pexps)
- l = exp_column(sql->sa,
exp_relname(ol), exp_name(ol), exp_subtype(ol), ol->card, has_nil(ol),
is_intern(ol));
- }
rel = left = select;
append(ll, l);
@@ -2808,10 +2787,11 @@ rel_logical_exp(mvc *sql, sql_rel *rel,
rel = rel_crossproduct(sql->sa, left, right,
op_join);
rel->exps = jexps;
}
- if (sc->token == SQL_IN || correlated || l_is_value) {
- rel->op = (sc->token == SQL_IN)?op_semi:op_anti;
- } else if (sc->token == SQL_NOT_IN) {
+ if (sc->token == SQL_IN || correlated || l_is_value)
+ rel->op = op_semi;
+ if (sc->token == SQL_NOT_IN) {
rel->op = op_anti;
+ set_no_nil(rel);
set_processed(rel);
}
if (pexps)
diff --git a/sql/test/BugTracker-2014/Tests/temporary.Bug-3430.stable.err
b/sql/test/BugTracker-2014/Tests/temporary.Bug-3430.stable.err
--- a/sql/test/BugTracker-2014/Tests/temporary.Bug-3430.stable.err
+++ b/sql/test/BugTracker-2014/Tests/temporary.Bug-3430.stable.err
@@ -35,7 +35,7 @@ ERROR = !CREATE TABLE: no such schema 't
CODE = 3F000
MAPI = (monetdb) /var/tmp/mtest-30274/.s.monetdb.37685
QUERY = create temporary table tempo.dummy(i int);
-ERROR = !CREATE TABLE: local tempory tables should be stored in the 'tmp'
schema
+ERROR = !CREATE TABLE: local temporary tables should be stored in the 'tmp'
schema
CODE = 3F000
MAPI = (monetdb) /var/tmp/mtest-30274/.s.monetdb.37685
QUERY = select * from tempo.dummy;
diff --git a/sql/test/Dependencies/Tests/Dependencies.stable.out
b/sql/test/Dependencies/Tests/Dependencies.stable.out
--- a/sql/test/Dependencies/Tests/Dependencies.stable.out
+++ b/sql/test/Dependencies/Tests/Dependencies.stable.out
@@ -632,9 +632,9 @@ Cleanup
[ "functions", "dependency_functions_on_views", "DEP_VIEW" ]
[ "users", "dependency_schemas_on_users", "DEP_VIEW" ]
[ "schemas", "dependency_schemas_on_users", "DEP_VIEW" ]
-[ "tables", "dependency_tables_on_functions", "DEP_VIEW" ]
+[ "functions", "dependency_tables_on_functions", "DEP_VIEW" ]
[ "dependencies", "dependency_tables_on_functions", "DEP_VIEW"
]
-[ "functions", "dependency_tables_on_functions", "DEP_VIEW" ]
+[ "tables", "dependency_tables_on_functions", "DEP_VIEW" ]
[ "functions", "dependency_views_on_functions", "DEP_VIEW" ]
[ "dependencies", "dependency_views_on_functions", "DEP_VIEW"
]
[ "tables", "dependency_views_on_functions", "DEP_VIEW" ]
@@ -657,16 +657,16 @@ Cleanup
[ "tables", "dependency_tables_on_indexes", "DEP_VIEW" ]
[ "keys", "dependency_tables_on_indexes", "DEP_VIEW" ]
[ "columns", "dependency_columns_on_indexes", "DEP_VIEW" ]
-[ "idxs", "dependency_columns_on_indexes", "DEP_VIEW" ]
+[ "tables", "dependency_columns_on_indexes", "DEP_VIEW" ]
[ "objects", "dependency_columns_on_indexes", "DEP_VIEW" ]
-[ "tables", "dependency_columns_on_indexes", "DEP_VIEW" ]
+[ "idxs", "dependency_columns_on_indexes", "DEP_VIEW" ]
[ "keys", "dependency_columns_on_indexes", "DEP_VIEW" ]
[ "keys", "dependency_tables_on_foreignkeys", "DEP_VIEW" ]
[ "tables", "dependency_tables_on_foreignkeys", "DEP_VIEW" ]
[ "keys", "dependency_keys_on_foreignkeys", "DEP_VIEW" ]
-[ "tables", "dependency_tables_on_procedures", "DEP_VIEW" ]
+[ "functions", "dependency_tables_on_procedures", "DEP_VIEW" ]
[ "dependencies", "dependency_tables_on_procedures", "DEP_VIEW"
]
-[ "functions", "dependency_tables_on_procedures", "DEP_VIEW" ]
+[ "tables", "dependency_tables_on_procedures", "DEP_VIEW" ]
[ "functions", "dependency_views_on_procedures", "DEP_VIEW" ]
[ "dependencies", "dependency_views_on_procedures", "DEP_VIEW"
]
[ "tables", "dependency_views_on_procedures", "DEP_VIEW" ]
@@ -838,11 +838,11 @@ Cleanup
[ "id", "dependency_schemas_on_users", "DEP_VIEW" ]
[ "name", "dependency_schemas_on_users", "DEP_VIEW" ]
[ "id", "dependency_tables_on_functions", "DEP_VIEW" ]
+[ "name", "dependency_tables_on_functions", "DEP_VIEW" ]
+[ "type", "dependency_tables_on_functions", "DEP_VIEW" ]
+[ "id", "dependency_tables_on_functions", "DEP_VIEW" ]
[ "depend_id", "dependency_tables_on_functions", "DEP_VIEW" ]
[ "depend_type", "dependency_tables_on_functions", "DEP_VIEW"
]
-[ "id", "dependency_tables_on_functions", "DEP_VIEW" ]
-[ "name", "dependency_tables_on_functions", "DEP_VIEW" ]
-[ "type", "dependency_tables_on_functions", "DEP_VIEW" ]
[ "id", "dependency_views_on_functions", "DEP_VIEW" ]
[ "name", "dependency_views_on_functions", "DEP_VIEW" ]
[ "type", "dependency_views_on_functions", "DEP_VIEW" ]
@@ -890,12 +890,12 @@ Cleanup
[ "table_id", "dependency_tables_on_indexes", "DEP_VIEW" ]
[ "name", "dependency_tables_on_indexes", "DEP_VIEW" ]
[ "id", "dependency_columns_on_indexes", "DEP_VIEW" ]
+[ "name", "dependency_columns_on_indexes", "DEP_VIEW" ]
+[ "nr", "dependency_columns_on_indexes", "DEP_VIEW" ]
+[ "id", "dependency_columns_on_indexes", "DEP_VIEW" ]
[ "table_id", "dependency_columns_on_indexes", "DEP_VIEW" ]
[ "type", "dependency_columns_on_indexes", "DEP_VIEW" ]
[ "name", "dependency_columns_on_indexes", "DEP_VIEW" ]
-[ "id", "dependency_columns_on_indexes", "DEP_VIEW" ]
-[ "name", "dependency_columns_on_indexes", "DEP_VIEW" ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
