Changeset: 2f87799f6ece for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2f87799f6ece
Modified Files:
sql/server/rel_optimizer.c
sql/server/rel_unnest.c
sql/test/mergetables/Tests/sqlsmith-exists2.stable.out
sql/test/subquery/Tests/subquery5.stable.out
Branch: Jun2020
Log Message:
add more projects around relations when introduction set operators
reduce explosion of relations when rewriting outer joins into inner + union
diffs (215 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
@@ -7701,7 +7701,6 @@ rel_simplify_predicates(mvc *sql, sql_re
} else if (is_atom(r->type) && r->l) {
/* direct literal */
atom *a = r->l;
int flag = a->data.val.bval;
- list *args = l->l;
assert(list_length(args) == 1);
l = args->h->data;
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
@@ -2966,7 +2966,7 @@ include_tid(sql_rel *r)
static sql_rel *
rewrite_outer2inner_union(mvc *sql, sql_rel *rel, int *changes)
{
- if (is_outerjoin(rel->op) && !list_empty(rel->exps) &&
rel_has_freevar(sql,rel)) {
+ if (is_outerjoin(rel->op) && !list_empty(rel->exps) &&
(rel_has_freevar(sql,rel->l) || rel_has_freevar(sql,rel->r) ||
exps_have_rel_exp(rel->exps))) {
sql_exp *f = exp_atom_bool(sql->sa, 0);
int nrcols = rel->nrcols;
@@ -2975,37 +2975,57 @@ rewrite_outer2inner_union(mvc *sql, sql_
nrcols += include_tid(rel->r);
rel->nrcols = nrcols;
if (is_left(rel->op)) {
- sql_rel *except = rel_setop(sql->sa, rel_dup(rel->l),
rel_project(sql->sa, rel_dup(rel), rel_projections(sql, rel->l, NULL, 1, 1)),
op_except);
+ sql_rel *except = rel_setop(sql->sa,
+ rel_project(sql->sa, rel_dup(rel->l),
rel_projections(sql, rel->l, NULL, 1, 1)),
+ rel_project(sql->sa, rel_dup(rel),
rel_projections(sql, rel->l, NULL, 1, 1)), op_except);
except->exps = rel_projections(sql, rel->l, NULL, 1, 1);
sql_rel *nrel = rel_crossproduct(sql->sa, except,
rel_dup(rel->r), op_left);
rel_join_add_exp(sql->sa, nrel, f);
rel->op = op_join;
- nrel = rel_setop(sql->sa, rel, nrel, op_union);
+ nrel = rel_setop(sql->sa,
+ rel_project(sql->sa, rel,
rel_projections(sql, rel, NULL, 1, 1)),
+ rel_project(sql->sa, nrel,
rel_projections(sql, nrel, NULL, 1, 1)),
+ op_union);
rel_set_exps(nrel, rel_projections(sql, rel, NULL, 1,
1));
return nrel;
} else if (is_right(rel->op)) {
- sql_rel *except = rel_setop(sql->sa, rel_dup(rel->r),
rel_project(sql->sa, rel_dup(rel), rel_projections(sql, rel->r, NULL, 1, 1)),
op_except);
+ sql_rel *except = rel_setop(sql->sa,
+ rel_project(sql->sa, rel_dup(rel->r),
rel_projections(sql, rel->r, NULL, 1, 1)),
+ rel_project(sql->sa, rel_dup(rel),
rel_projections(sql, rel->r, NULL, 1, 1)), op_except);
except->exps = rel_projections(sql, rel->r, NULL, 1, 1);
sql_rel *nrel = rel_crossproduct(sql->sa,
rel_dup(rel->l), except, op_right);
rel_join_add_exp(sql->sa, nrel, f);
rel->op = op_join;
- nrel = rel_setop(sql->sa, rel, nrel, op_union);
+ nrel = rel_setop(sql->sa,
+ rel_project(sql->sa, rel,
rel_projections(sql, rel, NULL, 1, 1)),
+ rel_project(sql->sa, nrel,
rel_projections(sql, nrel, NULL, 1, 1)),
+ op_union);
rel_set_exps(nrel, rel_projections(sql, rel, NULL, 1,
1));
return nrel;
} else if (is_full(rel->op)) {
- sql_rel *except = rel_setop(sql->sa, rel_dup(rel->l),
rel_project(sql->sa, rel_dup(rel), rel_projections(sql, rel->l, NULL, 1, 1)),
op_except);
+ sql_rel *except = rel_setop(sql->sa,
+ rel_project(sql->sa, rel_dup(rel->l),
rel_projections(sql, rel->l, NULL, 1, 1)),
+ rel_project(sql->sa, rel_dup(rel),
rel_projections(sql, rel->l, NULL, 1, 1)), op_except);
except->exps = rel_projections(sql, rel->l, NULL, 1, 1);
sql_rel *lrel = rel_crossproduct(sql->sa, except,
rel_dup(rel->r), op_left);
rel_join_add_exp(sql->sa, lrel, f);
- except = rel_setop(sql->sa, rel_dup(rel->r),
rel_project(sql->sa, rel_dup(rel), rel_projections(sql, rel->r, NULL, 1, 1)),
op_except);
+ except = rel_setop(sql->sa,
+ rel_project(sql->sa, rel_dup(rel->r),
rel_projections(sql, rel->r, NULL, 1, 1)),
+ rel_project(sql->sa, rel_dup(rel),
rel_projections(sql, rel->r, NULL, 1, 1)), op_except);
except->exps = rel_projections(sql, rel->r, NULL, 1, 1);
sql_rel *rrel = rel_crossproduct(sql->sa,
rel_dup(rel->l), except, op_right);
rel_join_add_exp(sql->sa, rrel, f);
- lrel = rel_setop(sql->sa, lrel, rrel, op_union);
+ lrel = rel_setop(sql->sa,
+ rel_project(sql->sa, lrel,
rel_projections(sql, lrel, NULL, 1, 1)),
+ rel_project(sql->sa, rrel,
rel_projections(sql, rrel, NULL, 1, 1)),
+ op_union);
rel_set_exps(lrel, rel_projections(sql, rel, NULL, 1,
1));
rel->op = op_join;
- lrel = rel_setop(sql->sa, rel, lrel, op_union);
+ lrel = rel_setop(sql->sa,
+ rel_project(sql->sa, rel,
rel_projections(sql, rel, NULL, 1, 1)),
+ rel_project(sql->sa, lrel,
rel_projections(sql, lrel, NULL, 1, 1)),
+ op_union);
rel_set_exps(lrel, rel_projections(sql, rel, NULL, 1,
1));
return lrel;
}
diff --git a/sql/test/mergetables/Tests/sqlsmith-exists2.stable.out
b/sql/test/mergetables/Tests/sqlsmith-exists2.stable.out
--- a/sql/test/mergetables/Tests/sqlsmith-exists2.stable.out
+++ b/sql/test/mergetables/Tests/sqlsmith-exists2.stable.out
@@ -298,6 +298,108 @@ stdout of test 'sqlsmith-exists2` in dir
[ "Ball", 0, NULL ]
[ "Ball", 0, NULL ]
[ "Ball", 0, NULL ]
+#select
+# subq_1.c0 as c0,
+# subq_1.c0 as c1,
+# subq_1.c0 as c2,
+# subq_0.c0 as c3,
+# subq_0.c1 as c4,
+# subq_0.c0 as c5,
+# subq_0.c0 as c6
+#from
+# (select
+# ref_0.col0 as c0,
+# ref_1.bb as c1
+# from
+# tab0 as ref_0
+# right join analytics as ref_1
+# on (exists (
+# select
+# ref_1.aa as c0
+# from
+# longtable as ref_2
+# where ref_0.col2 is not null))
+# where ((ref_1.aa is null)
+# and ((false)
+# and (ref_0.col2 is not null)))
+# or (ref_0.col2 is not null)) as subq_0
+# inner join (select
+# ref_3.col3 as c0
+# from
+# tbl_productsales as ref_3
+% ., ., ., ., ., ., . # table_name
+% c0, c1, c2, c3, c4, c5, c6 # name
+% varchar, varchar, varchar, int, int, int, int #
type
+% 0, 0, 0, 1, 1, 1, 1 # length
+#select
+# ref_0.col2 as c0,
+# cast(nullif(ref_0.col1,
+# ref_0.col0) as int) as c1,
+# 29 as c2,
+# ref_0.col1 as c3,
+# ref_0.col1 as c4,
+# ref_0.col0 as c5
+#from
+# tab1 as ref_0
+#where (((true)
+# and ((ref_0.col0 is not null)
+# and (ref_0.col2 is null)))
+# or (false))
+# or (exists (
+# select
+# ref_1.col2 as c0,
+# subq_0.c0 as c1,
+# ref_1.col1 as c2,
+# subq_0.c6 as c3,
+# ref_0.col1 as c4,
+# ref_1.col2 as c5,
+# ref_1.col1 as c6,
+# ref_1.col0 as c7,
+# ref_0.col2 as c8,
+# ref_1.col1 as c9,
+# ref_1.col0 as c10,
+# subq_0.c1 as c11,
+% ., ., ., ., ., . # table_name
+% c0, c1, c2, c3, c4, c5 # name
+% int, int, tinyint, int, int, int # type
+% 2, 2, 2, 2, 2, 2 # length
+[ 96, 14, 29, 14, 14, 51 ]
+[ 59, 5, 29, 5, 5, 85 ]
+[ 68, 47, 29, 47, 47, 91 ]
+#select
+# cast(coalesce(subq_1.c0,
+# cast(nullif(subq_0.c0,
+# 93) as int)) as int) as c0,
+# subq_1.c0 as c1
+#from
+# (select
+# ref_0.col4 as c0,
+# ref_0.col7 as c1,
+# ref_0.col4 as c2
+# from
+# another_t as ref_0
+# where false
+# limit 139) as subq_0
+# inner join (select
+# ref_1.col1 as c0
+# from
+# longtable as ref_1
+# where 7 is not null
+# limit 114) as subq_1
+# on (exists (
+# select
+# ref_3.col1 as c0,
+# subq_1.c0 as c1,
+# ref_3.col0 as c2,
+# ref_2.bb as c3,
+# ref_4.col8 as c4
+# from
+# analytics as ref_2
+# left join tab2 as ref_3
+% ., . # table_name
+% c0, c1 # name
+% int, int # type
+% 1, 1 # length
#ROLLBACK;
# 22:12:15 >
diff --git a/sql/test/subquery/Tests/subquery5.stable.out
b/sql/test/subquery/Tests/subquery5.stable.out
--- a/sql/test/subquery/Tests/subquery5.stable.out
+++ b/sql/test/subquery/Tests/subquery5.stable.out
@@ -208,8 +208,8 @@ stdout of test 'subquery5` in directory
[ NULL, 3 ]
[ NULL, NULL ]
#SELECT 1 FROM integers i1 RIGHT OUTER JOIN integers i2 ON NOT EXISTS(SELECT
1);
-% . # table_name
-% single_value # name
+% .%17 # table_name
+% %17 # name
% tinyint # type
% 1 # length
[ 1 ]
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
