Changeset: e4fbadd3a8e0 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=e4fbadd3a8e0
Modified Files:
sql/server/rel_optimizer.c
sql/server/rel_rel.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: Jun2020
Log Message:
merge projects
make sure expressions resulting after a outerjoin have the proper has_nil set
diffs (truncated from 544 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
@@ -6379,6 +6379,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;
@@ -7553,7 +7559,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;
@@ -7587,8 +7593,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/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
-| | | | | | | | | | | | | | | | | | | | | | | | ) [ "table4"."t4colb111" =
"lookup11"."t6pkcol" NOT NULL HASHCOL ],
-| | | | | | | | | | | | | | | | | | | | | | | | table(sys.table2) [
"table2"."t2colb112" as "lookup21"."t2colb112" ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | | ) [ "table4"."t4colb112" =
"lookup21"."t2colb112" ],
-| | | | | | | | | | | | | | | | | | | | | | | table(sys.table5) [
"table5"."t5pkcol" NOT NULL HASHCOL as "lookup31"."t5pkcol" ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | ) [ "table5"."t5colb113" =
"lookup31"."t5pkcol" NOT NULL HASHCOL ],
-| | | | | | | | | | | | | | | | | | | | | | table(sys.table7) [
"table7"."t7pkcol" NOT NULL HASHCOL as "lookup41"."t7pkcol" ] COUNT
-| | | | | | | | | | | | | | | | | | | | | ) [ "table4"."t4colb114" =
"lookup41"."t7pkcol" NOT NULL HASHCOL ],
-| | | | | | | | | | | | | | | | | | | | | table(sys.table8) [
"table8"."t8pkcol" NOT NULL HASHCOL as "lookup51"."t8pkcol" ] COUNT
-| | | | | | | | | | | | | | | | | | | | ) [ "table4"."t4colb115" =
"lookup51"."t8pkcol" NOT NULL HASHCOL ],
-| | | | | | | | | | | | | | | | | | | | table(sys.table6) [ "table6"."t6pkcol"
NOT NULL HASHCOL as "clookup1"."t6pkcol" ] COUNT
-| | | | | | | | | | | | | | | | | | | ) [ "table2"."t2colc111" =
"clookup1"."t6pkcol" NOT NULL HASHCOL ],
-| | | | | | | | | | | | | | | | | | | table(sys.table2) [ "table2"."t2pkcol"
NOT NULL HASHCOL as "clookup2"."t2pkcol" ] COUNT
-| | | | | | | | | | | | | | | | | | ) [ "table2"."t2colc112" =
"clookup2"."t2pkcol" NOT NULL HASHCOL ],
-| | | | | | | | | | | | | | | | | | table(sys.table5) [ "table5"."t5pkcol" NOT
NULL HASHCOL as "clookup3"."t5pkcol" ] COUNT
-| | | | | | | | | | | | | | | | | ) [ "table2"."t2colc113" =
"clookup3"."t5pkcol" NOT NULL HASHCOL ],
-| | | | | | | | | | | | | | | | | table(sys.table7) [ "table7"."t7pkcol" NOT
NULL HASHCOL as "clookup4"."t7pkcol" ] COUNT
-| | | | | | | | | | | | | | | | ) [ "table2"."t2colc114" =
"clookup4"."t7pkcol" NOT NULL HASHCOL ],
-| | | | | | | | | | | | | | | | table(sys.table8) [ "table8"."t8pkcol" NOT
NULL HASHCOL as "clookup5"."t8pkcol" ] COUNT
-| | | | | | | | | | | | | | | ) [ "table2"."t2colc115" = "clookup5"."t8pkcol"
NOT NULL HASHCOL ],
-| | | | | | | | | | | | | | | table(sys.table9) [ "table9"."t9pkcol" NOT NULL
HASHCOL , "table9"."t9cola1", "table9"."t9cola91", "table9"."t9cola111" ] COUNT
-| | | | | | | | | | | | | | ) [ "table1"."t1pkcol" HASHCOL =
"table9"."t9cola111" ],
-| | | | | | | | | | | | | | table(sys.table10) [ "table10"."t10pkcol" NOT NULL
HASHCOL , "table10"."t10cola1", "table10"."t10cola91" ] COUNT
-| | | | | | | | | | | | | ) [ "table9"."t9pkcol" HASHCOL =
"table10"."t10pkcol" NOT NULL HASHCOL ],
-| | | | | | | | | | | | | table(sys.table11) [ "table11"."t11pkcol" NOT NULL
HASHCOL , "table11"."t11cola91" ] COUNT
-| | | | | | | | | | | | ) [ "table9"."t9pkcol" HASHCOL = "table11"."t11pkcol"
NOT NULL HASHCOL ]
-| | | | | | | | | | | ) [ "table1"."t1pkcol" NOT NULL HASHCOL ,
"table1"."t1cola1", "table1"."t1cola11", "table1"."t1cola12",
"table1"."t1cola82", "table1"."t1cola91", "table1"."t1cola101",
"table1"."t1cola114", "table1"."t1colb1", "table1"."t1colb111",
"table1"."t1colb112", "table1"."t1colb113", "table1"."t1colb114",
"table1"."t1colc91", "table1"."t1cold1", "table1"."t1cold111",
"table1"."t1cold112", "table1"."t1cold113", "table4"."t4cola1",
"table4"."t4cola2", "table4"."t4colb111", "table4"."t4colb112",
"table4"."t4colb114", "table4"."t4colb115", "table2"."t2cola1",
"table2"."t2cola10", "table2"."t2cola81", "table2"."t2cola82",
"table2"."t2cola113", "table2"."t2colc111", "table2"."t2colc112",
"table2"."t2colc113", "table2"."t2colc114", "table2"."t2colc115",
"table5"."t5cola1", "table5"."t5cola2", "table5"."t5cola3", "table5"."t5cola5",
"table5"."t5cola81", "table5"."t5cola113", "table5"."t5colb113",
"lookup1"."t6pkcol" NOT NULL HASHCOL , "lookup2"."t2colb111",
"lookup3"."t5colb112
", "lookup4"."t7colb113", "lookup5"."t8colb114", "lookup11"."t6pkcol" NOT NULL
HASHCOL , "lookup21"."t2colb112", "lookup31"."t5pkcol" NOT NULL HASHCOL ,
"lookup41"."t7pkcol" NOT NULL HASHCOL , "lookup51"."t8pkcol" NOT NULL HASHCOL ,
"clookup1"."t6pkcol" NOT NULL HASHCOL , "clookup2"."t2pkcol" NOT NULL HASHCOL ,
"clookup3"."t5pkcol" NOT NULL HASHCOL , "clookup4"."t7pkcol" NOT NULL HASHCOL ,
"clookup5"."t8pkcol" NOT NULL HASHCOL , "table9"."t9pkcol" NOT NULL HASHCOL ,
"table9"."t9cola1", "table9"."t9cola91", "table9"."t9cola111",
"table10"."t10pkcol" NOT NULL HASHCOL , "table10"."t10cola1",
"table10"."t10cola91", "table11"."t11pkcol" NOT NULL HASHCOL ,
"table11"."t11cola91" ],
-| | | | | | | | | | | table(sys.table3) [ "table3"."t3pkcol" NOT NULL HASHCOL
as "a1"."t3pkcol", "table3"."t3cola1" as "a1"."t3cola1" ] COUNT
-| | | | | | | | | | ) [ "a1"."t3pkcol" NOT NULL HASHCOL =
"table1"."t1cold111" ]
-| | | | | | | | | ) [ "table1"."t1pkcol" NOT NULL HASHCOL ,
"table1"."t1cola1", "table1"."t1cola11", "table1"."t1cola12",
"table1"."t1cola82", "table1"."t1cola91", "table1"."t1cola101",
"table1"."t1cola114", "table1"."t1colb1", "table1"."t1colb111",
"table1"."t1colb112", "table1"."t1colb113", "table1"."t1colb114",
"table1"."t1colc91", "table1"."t1cold1", "table1"."t1cold111",
"table1"."t1cold112", "table1"."t1cold113", "table4"."t4cola1",
"table4"."t4cola2", "table4"."t4colb111", "table4"."t4colb112",
"table4"."t4colb114", "table4"."t4colb115", "table2"."t2cola1",
"table2"."t2cola10", "table2"."t2cola81", "table2"."t2cola82",
"table2"."t2colc111", "table2"."t2colc112", "table2"."t2colc113",
"table2"."t2colc114", "table2"."t2colc115", "table5"."t5cola1",
"table5"."t5cola2", "table5"."t5cola3", "table5"."t5cola5",
"table5"."t5cola81", "table5"."t5colb113", "lookup1"."t6pkcol" NOT NULL HASHCOL
, "lookup2"."t2colb111", "lookup3"."t5colb112", "lookup4"."t7colb113",
"lookup5"."t8colb114",
"lookup11"."t6pkcol" NOT NULL HASHCOL , "lookup21"."t2colb112",
"lookup31"."t5pkcol" NOT NULL HASHCOL , "lookup41"."t7pkcol" NOT NULL HASHCOL ,
"lookup51"."t8pkcol" NOT NULL HASHCOL , "clookup1"."t6pkcol" NOT NULL HASHCOL ,
"clookup2"."t2pkcol" NOT NULL HASHCOL , "clookup3"."t5pkcol" NOT NULL HASHCOL ,
"clookup4"."t7pkcol" NOT NULL HASHCOL , "clookup5"."t8pkcol" NOT NULL HASHCOL ,
"table9"."t9pkcol" NOT NULL HASHCOL , "table9"."t9cola1", "table9"."t9cola91",
"table9"."t9cola111", "table10"."t10pkcol" NOT NULL HASHCOL ,
"table10"."t10cola1", "table10"."t10cola91", "table11"."t11pkcol" NOT NULL
HASHCOL , "table11"."t11cola91", "a1"."t3pkcol" NOT NULL HASHCOL ,
"a1"."t3cola1" ],
-| | | | | | | | | table(sys.table3) [ "table3"."t3pkcol" NOT NULL HASHCOL as
"a2"."t3pkcol" ] COUNT
-| | | | | | | | ) [ "a2"."t3pkcol" NOT NULL HASHCOL = "table1"."t1cold112" ]
-| | | | | | | ) [ "table1"."t1pkcol" NOT NULL HASHCOL , "table1"."t1cola1",
"table1"."t1cola11", "table1"."t1cola12", "table1"."t1cola82",
"table1"."t1cola91", "table1"."t1cola101", "table1"."t1colb1",
"table1"."t1colb111", "table1"."t1colb112", "table1"."t1colb113",
"table1"."t1colb114", "table1"."t1colc91", "table1"."t1cold1",
"table1"."t1cold111", "table1"."t1cold112", "table1"."t1cold113",
"table4"."t4cola1", "table4"."t4cola2", "table4"."t4colb111",
"table4"."t4colb112", "table4"."t4colb114", "table4"."t4colb115",
"table2"."t2cola1", "table2"."t2cola10", "table2"."t2cola81",
"table2"."t2cola82", "table2"."t2colc111", "table2"."t2colc112",
"table2"."t2colc113", "table2"."t2colc114", "table2"."t2colc115",
"table5"."t5cola1", "table5"."t5cola2", "table5"."t5cola3", "table5"."t5cola5",
"table5"."t5cola81", "table5"."t5colb113", "lookup2"."t2colb111",
"lookup3"."t5colb112", "lookup4"."t7colb113", "lookup5"."t8colb114",
"lookup11"."t6pkcol" NOT NULL HASHCOL , "lookup21"."t2colb112",
"lookup31"."t5pkcol" NOT NULL HASHCOL , "lookup41"."t7pkcol" NOT NULL HASHCOL
, "lookup51"."t8pkcol" NOT NULL HASHCOL , "clookup1"."t6pkcol" NOT NULL HASHCOL
, "clookup2"."t2pkcol" NOT NULL HASHCOL , "clookup3"."t5pkcol" NOT NULL HASHCOL
, "clookup4"."t7pkcol" NOT NULL HASHCOL , "clookup5"."t8pkcol" NOT NULL HASHCOL
, "table9"."t9pkcol" NOT NULL HASHCOL , "table9"."t9cola1",
"table9"."t9cola91", "table9"."t9cola111", "table10"."t10pkcol" NOT NULL
HASHCOL , "table10"."t10cola1", "table10"."t10cola91", "table11"."t11pkcol" NOT
NULL HASHCOL , "table11"."t11cola91", "a1"."t3pkcol" NOT NULL HASHCOL ,
"a1"."t3cola1", "a2"."t3pkcol" NOT NULL HASHCOL ],
-| | | | | | | table(sys.table3) [ "table3"."t3pkcol" NOT NULL HASHCOL as
"a3"."t3pkcol" ] COUNT
-| | | | | | ) [ "a3"."t3pkcol" NOT NULL HASHCOL = "table1"."t1cold113" ]
-| | | | | ) [ "table1"."t1pkcol" NOT NULL HASHCOL , "table1"."t1cola1",
"table1"."t1cola11", "table1"."t1cola12", "table1"."t1cola82",
"table1"."t1cola91", "table1"."t1cola101", "table1"."t1colb1",
"table1"."t1colb112", "table1"."t1colb113", "table1"."t1colb114",
"table1"."t1colc91", "table1"."t1cold1", "table1"."t1cold111",
"table1"."t1cold112", "table1"."t1cold113", "table4"."t4cola1",
"table4"."t4cola2", "table4"."t4colb111", "table4"."t4colb112",
"table4"."t4colb114", "table4"."t4colb115", "table2"."t2cola1",
"table2"."t2cola10", "table2"."t2cola81", "table2"."t2cola82",
"table2"."t2colc111", "table2"."t2colc112", "table2"."t2colc113",
"table2"."t2colc114", "table2"."t2colc115", "table5"."t5cola1",
"table5"."t5cola2", "table5"."t5cola3", "table5"."t5cola5",
"table5"."t5cola81", "table5"."t5colb113", "lookup3"."t5colb112",
"lookup4"."t7colb113", "lookup5"."t8colb114", "lookup11"."t6pkcol" NOT NULL
HASHCOL , "lookup21"."t2colb112", "lookup31"."t5pkcol" NOT NULL HASHCOL ,
"lookup41
"."t7pkcol" NOT NULL HASHCOL , "lookup51"."t8pkcol" NOT NULL HASHCOL ,
"clookup1"."t6pkcol" NOT NULL HASHCOL , "clookup2"."t2pkcol" NOT NULL HASHCOL ,
"clookup3"."t5pkcol" NOT NULL HASHCOL , "clookup4"."t7pkcol" NOT NULL HASHCOL ,
"clookup5"."t8pkcol" NOT NULL HASHCOL , "table9"."t9pkcol" NOT NULL HASHCOL ,
"table9"."t9cola1", "table9"."t9cola91", "table9"."t9cola111",
"table10"."t10pkcol" NOT NULL HASHCOL , "table10"."t10cola1",
"table10"."t10cola91", "table11"."t11pkcol" NOT NULL HASHCOL ,
"table11"."t11cola91", "a1"."t3pkcol" NOT NULL HASHCOL , "a1"."t3cola1",
"a2"."t3pkcol" NOT NULL HASHCOL , "a3"."t3pkcol" NOT NULL HASHCOL ],
+| | | | | | | | | | | | | | | | | | | | | | | | | | | | | 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" 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
+| | | | | | | | | | | | | | | | | | | | ) [ "table4"."t4colb111" =
"lookup11"."t6pkcol" HASHCOL ],
+| | | | | | | | | | | | | | | | | | | | table(sys.table2) [
"table2"."t2colb112" as "lookup21"."t2colb112" ] COUNT
+| | | | | | | | | | | | | | | | | | | ) [ "table4"."t4colb112" =
"lookup21"."t2colb112" ],
+| | | | | | | | | | | | | | | | | | | table(sys.table5) [ "table5"."t5pkcol"
NOT NULL HASHCOL as "lookup31"."t5pkcol" ] COUNT
+| | | | | | | | | | | | | | | | | | ) [ "table5"."t5colb113" =
"lookup31"."t5pkcol" HASHCOL ],
+| | | | | | | | | | | | | | | | | | table(sys.table7) [ "table7"."t7pkcol" NOT
NULL HASHCOL as "lookup41"."t7pkcol" ] COUNT
+| | | | | | | | | | | | | | | | | ) [ "table4"."t4colb114" =
"lookup41"."t7pkcol" HASHCOL ],
+| | | | | | | | | | | | | | | | | table(sys.table8) [ "table8"."t8pkcol" NOT
NULL HASHCOL as "lookup51"."t8pkcol" ] COUNT
+| | | | | | | | | | | | | | | | ) [ "table4"."t4colb115" =
"lookup51"."t8pkcol" HASHCOL ],
+| | | | | | | | | | | | | | | | table(sys.table6) [ "table6"."t6pkcol" NOT
NULL HASHCOL as "clookup1"."t6pkcol" ] COUNT
+| | | | | | | | | | | | | | | ) [ "table2"."t2colc111" = "clookup1"."t6pkcol"
HASHCOL ],
+| | | | | | | | | | | | | | | table(sys.table2) [ "table2"."t2pkcol" NOT NULL
HASHCOL as "clookup2"."t2pkcol" ] COUNT
+| | | | | | | | | | | | | | ) [ "table2"."t2colc112" = "clookup2"."t2pkcol"
HASHCOL ],
+| | | | | | | | | | | | | | table(sys.table5) [ "table5"."t5pkcol" NOT NULL
HASHCOL as "clookup3"."t5pkcol" ] COUNT
+| | | | | | | | | | | | | ) [ "table2"."t2colc113" = "clookup3"."t5pkcol"
HASHCOL ],
+| | | | | | | | | | | | | table(sys.table7) [ "table7"."t7pkcol" NOT NULL
HASHCOL as "clookup4"."t7pkcol" ] COUNT
+| | | | | | | | | | | | ) [ "table2"."t2colc114" = "clookup4"."t7pkcol"
HASHCOL ],
+| | | | | | | | | | | | table(sys.table8) [ "table8"."t8pkcol" NOT NULL
HASHCOL as "clookup5"."t8pkcol" ] COUNT
+| | | | | | | | | | | ) [ "table2"."t2colc115" = "clookup5"."t8pkcol" HASHCOL
],
+| | | | | | | | | | | table(sys.table9) [ "table9"."t9pkcol" NOT NULL HASHCOL
, "table9"."t9cola1", "table9"."t9cola91", "table9"."t9cola111" ] COUNT
+| | | | | | | | | | ) [ "table1"."t1pkcol" HASHCOL = "table9"."t9cola111" ],
+| | | | | | | | | | table(sys.table10) [ "table10"."t10pkcol" NOT NULL HASHCOL
, "table10"."t10cola1", "table10"."t10cola91" ] COUNT
+| | | | | | | | | ) [ "table9"."t9pkcol" HASHCOL = "table10"."t10pkcol"
HASHCOL ],
+| | | | | | | | | table(sys.table11) [ "table11"."t11pkcol" NOT NULL HASHCOL ,
"table11"."t11cola91" ] COUNT
+| | | | | | | | ) [ "table9"."t9pkcol" HASHCOL = "table11"."t11pkcol" HASHCOL
],
+| | | | | | | | table(sys.table3) [ "table3"."t3pkcol" NOT NULL HASHCOL as
"a1"."t3pkcol", "table3"."t3cola1" as "a1"."t3cola1" ] COUNT
+| | | | | | | ) [ "a1"."t3pkcol" HASHCOL = "table1"."t1cold111" ],
+| | | | | | | table(sys.table3) [ "table3"."t3pkcol" NOT NULL HASHCOL as
"a2"."t3pkcol" ] COUNT
+| | | | | | ) [ "a2"."t3pkcol" HASHCOL = "table1"."t1cold112" ],
+| | | | | | table(sys.table3) [ "table3"."t3pkcol" NOT NULL HASHCOL as
"a3"."t3pkcol" ] COUNT
+| | | | | ) [ "a3"."t3pkcol" HASHCOL = "table1"."t1cold113" ],
| | | | | table(sys.table12) [ "table12"."t12cola1" ] COUNT
| | | | ) [ "table12"."t12cola1" = "table1"."t1cola1" ]
| | | ) [ (((((((((((((((clob[char["table1"."t1cold1"]] as "table1"."t1cold1")
FILTER ilike (clob "%a%", clob "")) or ((clob[char["table1"."t1cola1"]] as
"table1"."t1cola1") FILTER ilike (clob "%a%", clob ""))) or
((clob[char["table1"."t1colb1"]] as "table1"."t1colb1") FILTER ilike (clob
"%a%", clob ""))) or ((clob[char["table1"."t1cola11"]] as "table1"."t1cola11")
FILTER ilike (clob "%business%", clob ""))) or ("table1"."t1colc91" >=
timestamp(7)[char(19) "2016-03-21 05:00:00"])) or ("table1"."t1cola101" =
tinyint "1")) or ((clob[char["table1"."t1cola12"]] as "table1"."t1cola12")
FILTER ilike (clob "%Vijay%", clob ""))) or ((clob[char["table2"."t2cola1"]] as
"table2"."t2cola1") ! FILTER ilike (clob "%gmail%", clob ""),
(clob[char["table2"."t2cola1"]] as "table2"."t2cola1") ! FILTER ilike (clob
"%yahoo%", clob ""))) or ((clob[char["table2"."t2cola1"]] as
"table2"."t2cola1") FILTER ilike (clob "%efequitygroup.com%", clob ""))) or
("table4"."t4cola1" = clob "Customer")) or ("table4"."
t4cola2" ! * = clob "NULL")) or ("table2"."t2cola81" >= date "2009-08-31")) or
((("table5"."t5cola1" = clob "BAT") or ((clob[char["table5"."t5cola2"]] as
"table5"."t5cola2") FILTER ilike (clob "%AUSTRALIA%", clob ""))) or
((clob[char["table5"."t5cola2"]] as "table5"."t5cola2") FILTER ilike (clob
"%Monet%", clob ""), "table5"."t5cola3" = clob "Facebook", "table5"."t5cola5" =
clob "new", "table5"."t5cola81" > date "2015-07-30"))) or
((("table10"."t10cola1" != clob "Completed", "table9"."t9cola1" = clob "Tasks",
"table9"."t9cola91" >= timestamp(7)[char(19) "2012-01-01 04:32:27"],
"table10"."t10cola91" <= timestamp(7)[char(19) "2013-01-01 04:32:27"]) or
("table9"."t9cola1" = clob "Events", timestamp(7)[char(19) "2012-01-01
04:32:27"] <= "table11"."t11cola91" <= timestamp(7)[char(19) "2013-01-01
04:32:27"] BETWEEN )) or ("table9"."t9cola1" = clob "Calls",
timestamp(7)[char(19) "2012-01-01 04:32:27"] <= "table10"."t10cola91" <=
timestamp(7)[char(19) "2013-01-01 04:32:27"] BETWEEN )), "tab
le1"."t1cold111" in (bigint "15842000014793046" as "%64"."%64", bigint
"15842000017701488" as "%65"."%65", bigint "15842000000024019" as "%66"."%66",
bigint "15842000000074007" as "%67"."%67", bigint "15842000009358096" as
"%70"."%70", bigint "15842000010487625" as "%71"."%71", bigint
"15842000006731919" as "%72"."%72", bigint "15842000002590112" as "%73"."%73",
bigint "15842000000019001" as "%74"."%74", bigint "15842000014923682" as
"%75"."%75", bigint "15842000027547249" as "%76"."%76")) or
("table12"."t12cola1" in (clob[bigint "15842000280111951"] as "%100"."%100",
clob[bigint "15842000280163015"] as "%101"."%101")) ]
diff --git
a/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-explain-2join-query.stable.out
b/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-explain-2join-query.stable.out
---
a/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-explain-2join-query.stable.out
+++
b/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-explain-2join-query.stable.out
@@ -71,19 +71,19 @@ end user.s4_0;
% clob # type
% 189 # length
function user.s6_0():void;
- X_1:void := querylog.define("explain select id from fk left outer join pk1
on fk.fk1 = pk1.pk1 left outer join pk2 on fk.fk2 = pk2.pk2 order by id;":str,
"sequential_pipe":str, 32:int);
- X_44:bat[:str] := bat.pack("sys.fk":str);
- X_45:bat[:str] := bat.pack("id":str);
- X_46:bat[:str] := bat.pack("int":str);
- X_47:bat[:int] := bat.pack(32:int);
- X_48:bat[:int] := bat.pack(0:int);
+ X_1:void := querylog.define("explain select id from fk left outer join pk1
on fk.fk1 = pk1.pk1 left outer join pk2 on fk.fk2 = pk2.pk2 order by id;":str,
"sequential_pipe":str, 22:int);
+ X_30:bat[:str] := bat.pack("sys.fk":str);
+ X_31:bat[:str] := bat.pack("id":str);
+ X_32:bat[:str] := bat.pack("int":str);
+ X_33:bat[:int] := bat.pack(32:int);
+ X_34:bat[:int] := bat.pack(0:int);
X_4:int := sql.mvc();
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
