Changeset: 17f9716d9992 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=17f9716d9992
Modified Files:
sql/backends/monet5/rel_bin.c
sql/server/rel_optimizer.c
sql/server/rel_rel.c
sql/server/rel_unnest.c
sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-explain-2join-query.stable.out
sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-explain-2join-view.stable.out
sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-2join-query.stable.out
sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-2join-view.stable.out
Branch: default
Log Message:
Merged with Jun2020
diffs (truncated from 651 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
@@ -2635,6 +2635,7 @@ rel2bin_semijoin(backend *be, sql_rel *r
join = stmt_join(be, l, r, 0, cmp_all, 0, false);
}
} else {
+ right = subrel_project(be, right, refs, rel->r);
stmt *l = bin_first_column(be, left);
stmt *r = bin_first_column(be, right);
join = stmt_join(be, l, r, 0, cmp_all, 0, false);
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
@@ -1946,6 +1946,31 @@ rel_push_topn_and_sample_down(mvc *sql,
operator_type relation_type = is_topn(rel->op) ? op_topn :
op_sample;
sql_rel *(*func) (sql_allocator *, sql_rel *, list *) =
is_topn(rel->op) ? rel_topn : rel_sample;
+ /* nested topN relations without offset */
+ if (r && is_topn(rel->op) && is_topn(r->op) &&
list_length(rel->exps) == 1 && list_length(r->exps) == 1) {
+ sql_exp *topN1 = rel->exps->h->data, *topN2 =
r->exps->h->data;
+
+ if (topN1->l && topN2->l) {
+ atom *a1 = (atom *)topN1->l, *a2 = (atom
*)topN2->l;
+
+ if (a1->tpe.type->localtype ==
a2->tpe.type->localtype && !a1->isnull && !a2->isnull) {
+ if (atom_cmp(a1, a2) < 0) {
+ rel->l = r->l;
+ r->l = NULL;
+ rel_destroy(r);
+ (*changes)++;
+ return rel;
+ } else {
+ rel->l = NULL;
+ rel_destroy(rel);
+ rel = r;
+ (*changes)++;
+ return rel;
+ }
+ }
+ }
+ }
+
if (r && is_simple_project(r->op) && need_distinct(r))
return rel;
@@ -6218,6 +6243,12 @@ rel_remove_join(mvc *sql, sql_rel *rel,
static sql_rel *
rel_push_project_up(mvc *sql, sql_rel *rel, int *changes)
{
+ if (is_simple_project(rel->op) && rel->l && !rel_is_ref(rel)) {
+ sql_rel *l = rel->l;
+ if (is_simple_project(l->op))
+ return rel_merge_projects(sql, rel, changes);
+ }
+
/* project/project cleanup is done later */
if (is_join(rel->op) || is_select(rel->op)) {
node *n;
@@ -7392,7 +7423,7 @@ rel_simplify_like_select(mvc *sql, sql_r
static sql_exp *
rel_simplify_predicates(mvc *sql, sql_rel *rel, sql_exp *e, int depth, int
*changes)
{
- (void) depth;
+ (void)depth;
if (is_select(rel->op) || is_join(rel->op) || is_semi(rel->op)) {
if (is_atom(e->type) && ((!e->l && !e->r && !e->f) || e->r)) /*
prepared statement parameter or argument */
return e;
@@ -7426,8 +7457,7 @@ rel_simplify_predicates(mvc *sql, sql_re
list *args = l->l;
sql_exp *ie = args->h->data;
- /* TODO, we have to fix the NOT NULL
flag propagation on columns after an outer join, so we can remove the
is_outerjoin check */
- if (!is_outerjoin(rel->op) &&
(!has_nil(ie) || exp_is_not_null(sql, ie))) { /* is null on something that is
never null, is always false */
+ if (!has_nil(ie) ||
exp_is_not_null(sql, ie)) { /* is null on something that is never null, is
always false */
ie = exp_atom_bool(sql->sa, 0);
(*changes)++;
e->l = ie;
diff --git a/sql/server/rel_rel.c b/sql/server/rel_rel.c
--- a/sql/server/rel_rel.c
+++ b/sql/server/rel_rel.c
@@ -238,7 +238,7 @@ rel_issubquery(sql_rel*r)
}
static sql_rel *
-rel_bind_column_(mvc *sql, sql_rel **p, sql_rel *rel, const char *cname, int
no_tname)
+rel_bind_column_(mvc *sql, int *exp_has_nil, sql_rel *rel, const char *cname,
int no_tname)
{
int ambiguous = 0;
sql_rel *l = NULL, *r = NULL;
@@ -253,15 +253,12 @@ rel_bind_column_(mvc *sql, sql_rel **p,
case op_full: {
sql_rel *right = rel->r;
- *p = rel;
- r = rel_bind_column_(sql, p, rel->r, cname, no_tname);
-
+ r = rel_bind_column_(sql, exp_has_nil, rel->r, cname, no_tname);
if (!r || !rel_issubquery(right)) {
sql_exp *e = r?exps_bind_column(r->exps, cname,
&ambiguous, 0):NULL;
if (!r || !e || !is_freevar(e)) {
- *p = rel;
- l = rel_bind_column_(sql, p, rel->l, cname,
no_tname);
+ l = rel_bind_column_(sql, exp_has_nil, rel->l,
cname, no_tname);
if (l && r && !rel_issubquery(r) &&
!is_dependent(rel)) {
(void) sql_error(sql, ERR_AMBIGUOUS,
SQLSTATE(42000) "SELECT: identifier '%s' ambiguous", cname);
return NULL;
@@ -270,8 +267,13 @@ rel_bind_column_(mvc *sql, sql_rel **p,
}
if (sql->session->status == -ERR_AMBIGUOUS)
return NULL;
- if (l && !r)
+ if (l && !r) {
+ if (is_full(rel->op) || is_right(rel->op))
+ *exp_has_nil = 1;
return l;
+ }
+ if (r && (is_full(rel->op) || is_left(rel->op)))
+ *exp_has_nil = 1;
return r;
}
case op_union:
@@ -289,11 +291,10 @@ rel_bind_column_(mvc *sql, sql_rel **p,
(void) sql_error(sql, ERR_AMBIGUOUS, SQLSTATE(42000)
"SELECT: identifier '%s' ambiguous", cname);
return NULL;
}
- *p = rel;
if (is_processed(rel))
return NULL;
if (rel->l && !(is_base(rel->op)))
- return rel_bind_column_(sql, p, rel->l, cname,
no_tname);
+ return rel_bind_column_(sql, exp_has_nil, rel->l,
cname, no_tname);
break;
case op_semi:
case op_anti:
@@ -301,9 +302,8 @@ rel_bind_column_(mvc *sql, sql_rel **p,
case op_select:
case op_topn:
case op_sample:
- *p = rel;
if (rel->l)
- return rel_bind_column_(sql, p, rel->l, cname,
no_tname);
+ return rel_bind_column_(sql, exp_has_nil, rel->l,
cname, no_tname);
/* fall through */
default:
return NULL;
@@ -314,12 +314,12 @@ rel_bind_column_(mvc *sql, sql_rel **p,
sql_exp *
rel_bind_column( mvc *sql, sql_rel *rel, const char *cname, int f, int
no_tname)
{
- sql_rel *p = NULL;
+ int exp_has_nil = 0; /* mark if we passed any outer joins */
if (is_sql_sel(f) && rel && is_simple_project(rel->op) &&
!is_processed(rel))
rel = rel->l;
- if (!rel || (rel = rel_bind_column_(sql, &p, rel, cname, no_tname)) ==
NULL)
+ if (!rel || (rel = rel_bind_column_(sql, &exp_has_nil, rel, cname,
no_tname)) == NULL)
return NULL;
if ((is_project(rel->op) || is_base(rel->op)) && rel->exps) {
@@ -330,8 +330,9 @@ rel_bind_column( mvc *sql, sql_rel *rel,
sql_exp *e = exps_bind_column(rel->r, cname, NULL,
no_tname);
if (e)
e = exp_alias_or_copy(sql, exp_relname(e),
cname, rel, e);
- return e;
}
+ if (e && exp_has_nil)
+ set_has_nil(e);
return e;
}
return NULL;
@@ -377,8 +378,14 @@ rel_bind_column2( mvc *sql, sql_rel *rel
return rel_bind_column2(sql, rel->l, tname, cname, f);
} else if (is_join(rel->op)) {
sql_exp *e = rel_bind_column2(sql, rel->l, tname, cname, f);
- if (!e)
+
+ if (e && (is_right(rel->op) || is_full(rel->op)))
+ set_has_nil(e);
+ if (!e) {
e = rel_bind_column2(sql, rel->r, tname, cname, f);
+ if (e && (is_left(rel->op) || is_full(rel->op)))
+ set_has_nil(e);
+ }
return e;
} else if (is_set(rel->op) ||
is_sort(rel) ||
diff --git a/sql/server/rel_unnest.c b/sql/server/rel_unnest.c
--- a/sql/server/rel_unnest.c
+++ b/sql/server/rel_unnest.c
@@ -1819,6 +1819,51 @@ rewrite_or_exp(mvc *sql, sql_rel *rel, i
return rel;
}
+static sql_rel *
+rewrite_split_select_exps(mvc *sql, sql_rel *rel, int *changes)
+{
+ if (is_select(rel->op) && !list_empty(rel->exps)) {
+ int i = 0;
+ bool has_complex_exps = false, has_simple_exps = false,
*complex_exps = (bool*) GDKmalloc(list_length(rel->exps) * sizeof(bool));
+
+ if (!complex_exps)
+ return sql_error(sql, 02, SQLSTATE(HY013)
MAL_MALLOC_FAIL);
+
+ for (node *n = rel->exps->h ; n ; n = n->next) {
+ sql_exp *e = n->data;
+
+ if (exp_has_rel(e) || exp_has_freevar(sql, e)) {
+ complex_exps[i] = true;
+ has_complex_exps = true;
+ } else {
+ complex_exps[i] = false;
+ has_simple_exps = true;
+ }
+ i++;
+ }
+
+ if (has_complex_exps && has_simple_exps) {
+ sql_rel *nsel = rel_select_copy(sql->sa, rel->l, NULL);
+ rel->l = nsel;
+
+ i = 0;
+ for (node *n = rel->exps->h ; n ; ) {
+ node *nxt = n->next;
+
+ if (!complex_exps[i]) {
+ rel_select_add_exp(sql->sa, nsel,
n->data);
+ list_remove_node(rel->exps, n);
+ }
+ n = nxt;
+ i++;
+ }
+ (*changes)++;
+ }
+ GDKfree(complex_exps);
+ }
+ return rel;
+}
+
/* exp visitor */
static sql_exp *
rewrite_rank(mvc *sql, sql_rel *rel, sql_exp *e, int depth, int *changes)
@@ -3028,6 +3073,8 @@ rel_unnest(mvc *sql, sql_rel *rel)
rel = rel_visitor_bottomup(sql, rel, &rewrite_or_exp, &changes);
if (changes > 0)
rel = rel_visitor_bottomup(sql, rel, &rel_remove_empty_select,
&changes);
+ rel = rel_visitor_bottomup(sql, rel, &rewrite_split_select_exps,
&changes); /* has to run before rewrite_complex */
+
rel = rel_visitor_bottomup(sql, rel, &rewrite_aggregates, &changes);
rel = rel_exp_visitor_bottomup(sql, rel, &rewrite_rank, &changes);
rel = rel_visitor_bottomup(sql, rel, &rewrite_outer2inner_union,
&changes);
diff --git
a/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
b/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
---
a/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
+++
b/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
@@ -94,13 +94,13 @@ top N (
| | project (
| | | select (
| | | | left outer join (
-| | | | | project (
+| | | | | left outer join (
| | | | | | left outer join (
-| | | | | | | project (
+| | | | | | | left outer join (
| | | | | | | | left outer join (
-| | | | | | | | | project (
+| | | | | | | | | left outer join (
| | | | | | | | | | left outer join (
-| | | | | | | | | | | project (
+| | | | | | | | | | | left outer join (
| | | | | | | | | | | | left outer join (
| | | | | | | | | | | | | left outer join (
| | | | | | | | | | | | | | left outer join (
@@ -118,63 +118,55 @@ top N (
| | | | | | | | | | | | | | | | | | | | | | | | | | left outer join (
| | | | | | | | | | | | | | | | | | | | | | | | | | | left outer join (
| | | | | | | | | | | | | | | | | | | | | | | | | | | | left outer join (
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | | left outer join (
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | left outer join (
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | left outer join (
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | left outer
join (
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
table(sys.table1) [ "table1"."t1pkcol" NOT NULL HASHCOL , "table1"."t1cola1",
"table1"."t1cola11", "table1"."t1cola12", "table1"."t1cola82",
"table1"."t1cola91", "table1"."t1cola101", "table1"."t1cola111",
"table1"."t1cola112", "table1"."t1cola114", "table1"."t1colb1",
"table1"."t1colb111", "table1"."t1colb112", "table1"."t1colb113",
"table1"."t1colb114", "table1"."t1colc91", "table1"."t1cold1",
"table1"."t1cold111", "table1"."t1cold112", "table1"."t1cold113" ] COUNT ,
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
table(sys.table4) [ "table4"."t4cola1", "table4"."t4cola2",
"table4"."t4cola111", "table4"."t4colb111", "table4"."t4colb112",
"table4"."t4colb114", "table4"."t4colb115" ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | ) [
"table1"."t1cola111" = "table4"."t4cola111" ],
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
table(sys.table2) [ "table2"."t2cola1", "table2"."t2cola10",
"table2"."t2cola81", "table2"."t2cola82", "table2"."t2cola112",
"table2"."t2cola113", "table2"."t2colc111", "table2"."t2colc112",
"table2"."t2colc113", "table2"."t2colc114", "table2"."t2colc115" ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | ) [
"table1"."t1cola112" = "table2"."t2cola112" ],
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
table(sys.table5) [ "table5"."t5cola1", "table5"."t5cola2", "table5"."t5cola3",
"table5"."t5cola5", "table5"."t5cola81", "table5"."t5cola113",
"table5"."t5colb113" ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | ) [
"table5"."t5cola113" = "table2"."t2cola113" ],
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table6)
[ "table6"."t6pkcol" NOT NULL HASHCOL as "lookup1"."t6pkcol" ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | | ) [
"table1"."t1cola114" = "lookup1"."t6pkcol" NOT NULL HASHCOL ],
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table2) [
"table2"."t2colb111" as "lookup2"."t2colb111" ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | ) [
"table1"."t1colb111" = "lookup2"."t2colb111" ],
-| | | | | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table5) [
"table5"."t5colb112" as "lookup3"."t5colb112" ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | | | | | | ) [ "table1"."t1colb112"
= "lookup3"."t5colb112" ],
-| | | | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table7) [
"table7"."t7colb113" as "lookup4"."t7colb113" ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | | | | | ) [ "table1"."t1colb113" =
"lookup4"."t7colb113" ],
-| | | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table8) [
"table8"."t8colb114" as "lookup5"."t8colb114" ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | | | | ) [ "table1"."t1colb114" =
"lookup5"."t8colb114" ],
-| | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table6) [
"table6"."t6pkcol" NOT NULL HASHCOL as "lookup11"."t6pkcol" ] COUNT
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
