Changeset: ae6674917107 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=ae6674917107
Modified Files:
sql/server/rel_optimizer.c
sql/server/rel_rel.c
sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-1join-query.stable.out
sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-1join-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
sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-prologue.sql
sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-prologue.stable.out
sql/test/xquery/Tests/q01.stable.out
Branch: Jun2020
Log Message:
If a column of an index has NULL values, set the index as nullable. At the
foreign key join simplification, if the foreign key has NULL values and the
other side doesn't, then filter those out
diffs (truncated from 598 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
@@ -1801,12 +1801,12 @@ rel_simplify_project_fk_join(mvc *sql, s
{
sql_rel *rl = r->l;
sql_rel *rr = r->r;
- sql_exp *je;
+ sql_exp *je, *le, *nje, *re;
node *n;
int fk_left = 1;
/* check for foreign key join */
- if (!r->exps || list_length(r->exps) != 1)
+ if (list_length(r->exps) != 1)
return r;
je = r->exps->h->data;
if (je && !find_prop(je->p, PROP_JOINIDX))
@@ -1851,10 +1851,23 @@ rel_simplify_project_fk_join(mvc *sql, s
}
(*changes)++;
- /* rewrite, ie remove pkey side */
- if (fk_left)
- return r->l;
- return r->r;
+ /* rewrite, ie remove pkey side if possible */
+ le = (sql_exp*)je->l, re = (sql_exp*)je->l;
+
+ /* both have NULL and there are semantics, the join cannot be removed */
+ if (is_semantics(je) && has_nil(le) && has_nil(re))
+ return r;
+
+ /* if the foreign key column doesn't have NULL values, then return it */
+ if (!has_nil(le) || is_full(r->op) || (fk_left && is_left(r->op)) ||
(!fk_left && is_right(r->op)))
+ return fk_left ? r->l : r->r;
+
+ /* remove NULL values, ie generate a select not null */
+ nje = exp_compare(sql->sa, exp_ref(sql, le), exp_atom(sql->sa,
atom_general(sql->sa, exp_subtype(le), NULL)), cmp_equal);
+ set_anti(nje);
+ set_has_no_nil(nje);
+ set_semantics(nje);
+ return rel_select(sql->sa, fk_left ? r->l : r->r, nje);
}
static sql_rel *
@@ -1862,11 +1875,11 @@ rel_simplify_count_fk_join(mvc *sql, sql
{
sql_rel *rl = r->l;
sql_rel *rr = r->r;
- sql_exp *oce, *je;
+ sql_exp *je, *le, *nje, *re, *oce;
int fk_left = 1;
/* check for foreign key join */
- if (!r->exps || list_length(r->exps) != 1)
+ if (list_length(r->exps) != 1)
return r;
je = r->exps->h->data;
if (je && !find_prop(je->p, PROP_JOINIDX))
@@ -1899,10 +1912,23 @@ rel_simplify_count_fk_join(mvc *sql, sql
}
(*changes)++;
- /* rewrite, ie remove pkey side */
- if (fk_left)
- return r->l;
- return r->r;
+ /* rewrite, ie remove pkey side if possible */
+ le = (sql_exp*)je->l, re = (sql_exp*)je->l;
+
+ /* both have NULL and there are semantics, the join cannot be removed */
+ if (is_semantics(je) && has_nil(le) && has_nil(re))
+ return r;
+
+ /* if the foreign key column doesn't have NULL values, then return it */
+ if (!has_nil(le) || is_full(r->op) || (fk_left && is_left(r->op)) ||
(!fk_left && is_right(r->op)))
+ return fk_left ? r->l : r->r;
+
+ /* remove NULL values, ie generate a select not null */
+ nje = exp_compare(sql->sa, exp_ref(sql, le), exp_atom(sql->sa,
atom_general(sql->sa, exp_subtype(le), NULL)), cmp_equal);
+ set_anti(nje);
+ set_has_no_nil(nje);
+ set_semantics(nje);
+ return rel_select(sql->sa, fk_left ? r->l : r->r, nje);
}
/*
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
@@ -835,6 +835,7 @@ rel_basetable(mvc *sql, sql_table *t, co
sql_idx *i = cn->data;
sql_subtype *t = sql_bind_localtype("lng"); /* hash
"lng" */
char *iname = NULL;
+ int has_nils = 0;
/* do not include empty indices in the plan */
if (hash_index(i->type) && list_length(i->columns) <= 1)
@@ -844,7 +845,13 @@ rel_basetable(mvc *sql, sql_table *t, co
t = sql_bind_localtype("oid");
iname = sa_strconcat( sa, "%", i->base.name);
- e = exp_alias(sa, atname, iname, tname, iname, t,
CARD_MULTI, 0, 1);
+ for (node *n = i->columns->h ; n && !has_nils; n =
n->next) { /* check for NULL values */
+ sql_kc *kc = n->data;
+
+ if (kc->c->null)
+ has_nils = 1;
+ }
+ e = exp_alias(sa, atname, iname, tname, iname, t,
CARD_MULTI, has_nils, 1);
/* index names are prefixed, to make them independent */
if (hash_index(i->type)) {
p = e->p = prop_create(sa, PROP_HASHIDX, e->p);
diff --git
a/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-1join-query.stable.out
b/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-1join-query.stable.out
---
a/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-1join-query.stable.out
+++
b/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-1join-query.stable.out
@@ -50,24 +50,24 @@ project (
% .plan # table_name
% rel # name
% clob # type
-% 114 # length
+% 105 # length
project (
| left outer join (
-| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk1_fkey" NOT NULL
JOINIDX sys.fk.fk_fk1_fkey ] COUNT ,
+| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk1_fkey" JOINIDX
sys.fk.fk_fk1_fkey ] COUNT ,
| | table(sys.pk1) [ "pk1"."v1", "pk1"."%TID%" NOT NULL ] COUNT
-| ) [ "fk"."%fk_fk1_fkey" NOT NULL = "pk1"."%TID%" NOT NULL JOINIDX
sys.fk.fk_fk1_fkey ]
+| ) [ "fk"."%fk_fk1_fkey" = "pk1"."%TID%" NOT NULL JOINIDX sys.fk.fk_fk1_fkey ]
) [ "fk"."id" NOT NULL HASHCOL , "pk1"."v1" ] [ "fk"."id" ASC NOT NULL HASHCOL
]
#plan select id , v2 from fk left outer join pk2 on fk.fk2 = pk2.pk2 order by
id;
% .plan # table_name
% rel # name
% clob # type
-% 114 # length
+% 105 # length
project (
| left outer join (
-| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk2_fkey" NOT NULL
JOINIDX sys.fk.fk_fk2_fkey ] COUNT ,
+| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk2_fkey" JOINIDX
sys.fk.fk_fk2_fkey ] COUNT ,
| | table(sys.pk2) [ "pk2"."v2", "pk2"."%TID%" NOT NULL ] COUNT
-| ) [ "fk"."%fk_fk2_fkey" NOT NULL = "pk2"."%TID%" NOT NULL JOINIDX
sys.fk.fk_fk2_fkey ]
+| ) [ "fk"."%fk_fk2_fkey" = "pk2"."%TID%" NOT NULL JOINIDX sys.fk.fk_fk2_fkey ]
) [ "fk"."id" NOT NULL HASHCOL , "pk2"."v2" ] [ "fk"."id" ASC NOT NULL HASHCOL
]
#plan select count(*) from pk1 right outer join fk on fk.fk1 = pk1.pk1;
% .plan # table_name
@@ -91,23 +91,23 @@ project (
% .plan # table_name
% rel # name
% clob # type
-% 113 # length
+% 104 # length
project (
| right outer join (
| | table(sys.pk1) [ "pk1"."v1", "pk1"."%TID%" NOT NULL ] COUNT ,
-| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk1_fkey" NOT NULL
JOINIDX sys.fk.fk_fk1_fkey ] COUNT
-| ) [ "fk"."%fk_fk1_fkey" NOT NULL = "pk1"."%TID%" NOT NULL JOINIDX
sys.fk.fk_fk1_fkey ]
+| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk1_fkey" JOINIDX
sys.fk.fk_fk1_fkey ] COUNT
+| ) [ "fk"."%fk_fk1_fkey" = "pk1"."%TID%" NOT NULL JOINIDX sys.fk.fk_fk1_fkey ]
) [ "fk"."id" NOT NULL HASHCOL , "pk1"."v1" ] [ "fk"."id" ASC NOT NULL HASHCOL
]
#plan select id , v2 from pk2 right outer join fk on fk.fk2 = pk2.pk2 order
by id;
% .plan # table_name
% rel # name
% clob # type
-% 113 # length
+% 104 # length
project (
| right outer join (
| | table(sys.pk2) [ "pk2"."v2", "pk2"."%TID%" NOT NULL ] COUNT ,
-| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk2_fkey" NOT NULL
JOINIDX sys.fk.fk_fk2_fkey ] COUNT
-| ) [ "fk"."%fk_fk2_fkey" NOT NULL = "pk2"."%TID%" NOT NULL JOINIDX
sys.fk.fk_fk2_fkey ]
+| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk2_fkey" JOINIDX
sys.fk.fk_fk2_fkey ] COUNT
+| ) [ "fk"."%fk_fk2_fkey" = "pk2"."%TID%" NOT NULL JOINIDX sys.fk.fk_fk2_fkey ]
) [ "fk"."id" NOT NULL HASHCOL , "pk2"."v2" ] [ "fk"."id" ASC NOT NULL HASHCOL
]
#plan select count(*) from pk1 full outer join fk on fk.fk1 = pk1.pk1;
% .plan # table_name
@@ -131,63 +131,67 @@ project (
% .plan # table_name
% rel # name
% clob # type
-% 113 # length
+% 104 # length
project (
| full outer join (
| | table(sys.pk1) [ "pk1"."v1", "pk1"."%TID%" NOT NULL ] COUNT ,
-| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk1_fkey" NOT NULL
JOINIDX sys.fk.fk_fk1_fkey ] COUNT
-| ) [ "fk"."%fk_fk1_fkey" NOT NULL = "pk1"."%TID%" NOT NULL JOINIDX
sys.fk.fk_fk1_fkey ]
+| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk1_fkey" JOINIDX
sys.fk.fk_fk1_fkey ] COUNT
+| ) [ "fk"."%fk_fk1_fkey" = "pk1"."%TID%" NOT NULL JOINIDX sys.fk.fk_fk1_fkey ]
) [ "fk"."id" HASHCOL , "pk1"."v1" ] [ "fk"."id" ASC HASHCOL ]
#plan select id , v2 from pk2 full outer join fk on fk.fk2 = pk2.pk2 order by
id;
% .plan # table_name
% rel # name
% clob # type
-% 113 # length
+% 104 # length
project (
| full outer join (
| | table(sys.pk2) [ "pk2"."v2", "pk2"."%TID%" NOT NULL ] COUNT ,
-| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk2_fkey" NOT NULL
JOINIDX sys.fk.fk_fk2_fkey ] COUNT
-| ) [ "fk"."%fk_fk2_fkey" NOT NULL = "pk2"."%TID%" NOT NULL JOINIDX
sys.fk.fk_fk2_fkey ]
+| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk2_fkey" JOINIDX
sys.fk.fk_fk2_fkey ] COUNT
+| ) [ "fk"."%fk_fk2_fkey" = "pk2"."%TID%" NOT NULL JOINIDX sys.fk.fk_fk2_fkey ]
) [ "fk"."id" HASHCOL , "pk2"."v2" ] [ "fk"."id" ASC HASHCOL ]
#plan select count(*) from pk1 join fk on fk.fk1 = pk1.pk1;
% .plan # table_name
% rel # name
% clob # type
-% 56 # length
+% 77 # length
project (
| group by (
-| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL ] COUNT
+| | select (
+| | | table(sys.fk) [ "fk"."%fk_fk1_fkey" JOINIDX sys.fk.fk_fk1_fkey ] COUNT
+| | ) [ "fk"."%fk_fk1_fkey" ! * = oid "NULL" ]
| ) [ ] [ sys.count() NOT NULL as "%3"."%3" ]
) [ "%3"."%3" NOT NULL ]
#plan select id from pk1 join fk on fk.fk1 = pk1.pk1 order by id;
% .plan # table_name
% rel # name
% clob # type
-% 69 # length
+% 104 # length
project (
-| table(sys.fk) [ "fk"."id" NOT NULL HASHCOL ] COUNT
+| select (
+| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk1_fkey" JOINIDX
sys.fk.fk_fk1_fkey ] COUNT
+| ) [ "fk"."%fk_fk1_fkey" ! * = oid "NULL" ]
) [ "fk"."id" NOT NULL HASHCOL ] [ "fk"."id" ASC NOT NULL HASHCOL ]
#plan select id , v1 from pk1 join fk on fk.fk1 = pk1.pk1 order by id;
% .plan # table_name
% rel # name
% clob # type
-% 114 # length
+% 105 # length
project (
| join (
-| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk1_fkey" NOT NULL
JOINIDX sys.fk.fk_fk1_fkey ] COUNT ,
+| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk1_fkey" JOINIDX
sys.fk.fk_fk1_fkey ] COUNT ,
| | table(sys.pk1) [ "pk1"."v1", "pk1"."%TID%" NOT NULL ] COUNT
-| ) [ "fk"."%fk_fk1_fkey" NOT NULL = "pk1"."%TID%" NOT NULL JOINIDX
sys.fk.fk_fk1_fkey ]
+| ) [ "fk"."%fk_fk1_fkey" = "pk1"."%TID%" NOT NULL JOINIDX sys.fk.fk_fk1_fkey ]
) [ "fk"."id" NOT NULL HASHCOL , "pk1"."v1" ] [ "fk"."id" ASC NOT NULL HASHCOL
]
#plan select id , v2 from pk2 join fk on fk.fk2 = pk2.pk2 order by id;
% .plan # table_name
% rel # name
% clob # type
-% 114 # length
+% 105 # length
project (
| join (
-| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk2_fkey" NOT NULL
JOINIDX sys.fk.fk_fk2_fkey ] COUNT ,
+| | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk2_fkey" JOINIDX
sys.fk.fk_fk2_fkey ] COUNT ,
| | table(sys.pk2) [ "pk2"."v2", "pk2"."%TID%" NOT NULL ] COUNT
-| ) [ "fk"."%fk_fk2_fkey" NOT NULL = "pk2"."%TID%" NOT NULL JOINIDX
sys.fk.fk_fk2_fkey ]
+| ) [ "fk"."%fk_fk2_fkey" = "pk2"."%TID%" NOT NULL JOINIDX sys.fk.fk_fk2_fkey ]
) [ "fk"."id" NOT NULL HASHCOL , "pk2"."v2" ] [ "fk"."id" ASC NOT NULL HASHCOL
]
# 01:44:33 >
diff --git
a/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-1join-view.stable.out
b/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-1join-view.stable.out
---
a/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-1join-view.stable.out
+++
b/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-1join-view.stable.out
@@ -52,13 +52,13 @@ project (
% .plan # table_name
% rel # name
% clob # type
-% 116 # length
+% 107 # length
project (
| project (
| | left outer join (
-| | | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk1_fkey" NOT
NULL JOINIDX sys.fk.fk_fk1_fkey ] COUNT ,
+| | | table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk1_fkey" JOINIDX
sys.fk.fk_fk1_fkey ] COUNT ,
| | | table(sys.pk1) [ "pk1"."v1", "pk1"."%TID%" NOT NULL ] COUNT
-| | ) [ "fk"."%fk_fk1_fkey" NOT NULL = "pk1"."%TID%" NOT NULL JOINIDX
sys.fk.fk_fk1_fkey ]
+| | ) [ "fk"."%fk_fk1_fkey" = "pk1"."%TID%" NOT NULL JOINIDX
sys.fk.fk_fk1_fkey ]
| ) [ "fk"."id" NOT NULL HASHCOL as "v1"."id", "pk1"."v1" as "v1"."v1" ]
) [ "v1"."id" NOT NULL HASHCOL , "v1"."v1" ] [ "v1"."id" ASC NOT NULL HASHCOL
]
diff --git
a/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-2join-query.stable.out
b/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-2join-query.stable.out
---
a/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-2join-query.stable.out
+++
b/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-2join-query.stable.out
@@ -41,36 +41,36 @@ project (
% .plan # table_name
% rel # name
% clob # type
-% 168 # length
+% 150 # length
project (
-| table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk1_fkey" NOT NULL
JOINIDX sys.fk.fk_fk1_fkey, "fk"."%fk_fk2_fkey" NOT NULL JOINIDX
sys.fk.fk_fk2_fkey ] COUNT
+| table(sys.fk) [ "fk"."id" NOT NULL HASHCOL , "fk"."%fk_fk1_fkey" JOINIDX
sys.fk.fk_fk1_fkey, "fk"."%fk_fk2_fkey" JOINIDX sys.fk.fk_fk2_fkey ] COUNT
) [ "fk"."id" NOT NULL HASHCOL ] [ "fk"."id" ASC NOT NULL HASHCOL ]
#plan select id , v1 from fk left outer join pk1 on fk.fk1 = pk1.pk1 left
outer join pk2 on fk.fk2 = pk2.pk2 order by id;
% .plan # table_name
% rel # name
% clob # type
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
