Changeset: ff7ffe8c240c for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=ff7ffe8c240c
Modified Files:
sql/server/rel_exp.c
sql/server/rel_exp.h
sql/server/rel_optimizer.c
sql/server/rel_rel.c
sql/test/BugDay_2005-10-06_2.9.3/Tests/simple_union.SF-1005596.stable.out
sql/test/BugTracker-2016/Tests/column_alias_in_where_clause.Bug-3947.stable.out
sql/test/bugs/Tests/subselect_multiple_unionall_where_1=1-bug-sf-1005596.stable.out
sql/test/mergetables/Tests/sqlsmith-exists2.stable.out
sql/test/miscellaneous/Tests/simple_plans.stable.out
Branch: Jun2020
Log Message:
improved dce optimizer in case of projects with self referencing.
improved push project up optimizer, ie project_unsafe now checks for self
references
make sure we don't rewrite 2 select were the later has a function (ie is more
expensive when pushed down anyway, but also fixes issues with division by zero)
diffs (263 lines):
diff --git a/sql/server/rel_exp.c b/sql/server/rel_exp.c
--- a/sql/server/rel_exp.c
+++ b/sql/server/rel_exp.c
@@ -252,7 +252,7 @@ exp_compare_func(mvc *sql, sql_exp *le,
{
sql_subfunc *cmp_func = sql_bind_func(sql->sa, NULL, compareop,
exp_subtype(le), exp_subtype(le), F_FUNC);
sql_exp *e;
-
+
assert(cmp_func);
e = exp_binop(sql->sa, le, re, cmp_func);
if (e) {
@@ -1794,6 +1794,20 @@ exps_have_rel_exp( list *exps)
return 0;
}
+int
+exps_have_func(list *exps)
+{
+ if (list_empty(exps))
+ return 0;
+ for(node *n=exps->h; n; n=n->next) {
+ sql_exp *e = n->data;
+
+ if (exp_has_func(e))
+ return 1;
+ }
+ return 0;
+}
+
static sql_rel *
exps_rel_get_rel(sql_allocator *sa, list *exps )
{
diff --git a/sql/server/rel_exp.h b/sql/server/rel_exp.h
--- a/sql/server/rel_exp.h
+++ b/sql/server/rel_exp.h
@@ -151,6 +151,7 @@ extern int exp_is_null(mvc *sql, sql_exp
extern int exp_is_rel(sql_exp *e);
extern int exp_has_rel(sql_exp *e);
extern int exps_have_rel_exp(list *exps);
+extern int exps_have_func(list *exps);
extern sql_rel *exp_rel_get_rel(sql_allocator *sa, sql_exp *e);
extern sql_exp *exp_rel_update_exp(mvc *sql, sql_exp *e);
extern sql_exp *exp_rel_label(mvc *sql, sql_exp *e);
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
@@ -2968,20 +2968,6 @@ rel_merge_projects(visitor *v, sql_rel *
all = 0;
break;
}
- /*
- if (ne && ne->type == e_column) {
- sql_exp *nne = NULL;
-
- if (ne->l)
- nne = exps_bind_column2(rel->exps,
ne->l, ne->r);
- if (!nne && !ne->l)
- nne = exps_bind_column(rel->exps,
ne->r, NULL, 1);
- if (nne && ne != nne && nne != e) {
- all = 0;
- break;
- }
- }
- */
if (ne) {
if (exp_name(e))
exp_prop_alias(v->sql->sa, ne, e);
@@ -4622,7 +4608,7 @@ rel_push_select_down(visitor *v, sql_rel
/* merge 2 selects */
r = rel->l;
- if (is_select(rel->op) && r && r->exps && is_select(r->op) &&
!(rel_is_ref(r))) {
+ if (is_select(rel->op) && r && r->exps && is_select(r->op) &&
!(rel_is_ref(r)) && !exps_have_func(rel->exps)) {
(void)list_merge(r->exps, rel->exps, (fdup)NULL);
rel->l = NULL;
rel_destroy(rel);
@@ -6820,7 +6806,7 @@ exp_mark_used(sql_rel *subrel, sql_exp *
break;
}
if (ne && e != ne) {
- if (!local_proj || (has_label(ne) || (ne->alias.rname &&
ne->alias.rname[0] == '%')))
+ if (!local_proj || (has_label(ne) || (ne->alias.rname &&
ne->alias.rname[0] == '%')) || (subrel->l && !rel_find_exp(subrel->l, e)))
ne->used = 1;
return ne->used;
}
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
@@ -38,11 +38,14 @@ project_unsafe(sql_rel *rel, int allow_i
if (!sub || (sub && sub->op == op_ddl))
return 1;
for(n = rel->exps->h; n; n = n->next) {
- sql_exp *e = n->data;
+ sql_exp *e = n->data, *ne;
/* aggr func in project ! */
if (exp_unsafe(e, allow_identity))
return 1;
+ ne = rel_find_exp(rel, e);
+ if (ne && ne != e) /* no self referencing */
+ return 1;
}
return 0;
}
diff --git
a/sql/test/BugDay_2005-10-06_2.9.3/Tests/simple_union.SF-1005596.stable.out
b/sql/test/BugDay_2005-10-06_2.9.3/Tests/simple_union.SF-1005596.stable.out
--- a/sql/test/BugDay_2005-10-06_2.9.3/Tests/simple_union.SF-1005596.stable.out
+++ b/sql/test/BugDay_2005-10-06_2.9.3/Tests/simple_union.SF-1005596.stable.out
@@ -25,8 +25,8 @@ stdout of test 'simple_union.SF-1005596`
% .tables, .tables, .tables, .tables, .tables,
.tables, .tables, .tables, .tables, .tables #
table_name
% TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS,
TYPE_CAT, TYPE_SCHEM, TYPE_NAME, SELF_REFERENCING_COL_NAME,
REF_GENERATION # name
-% char, varchar, varchar, char, char, char, char,
char, char, char # type
-% 4, 3, 4, 20, 0, 0, 0, 0, 5, 6 #
length
+% char, varchar, varchar, clob, char, char, char,
char, char, char # type
+% 4, 3, 4, 5, 0, 0, 0, 0, 5, 6 #
length
[ "demo", "sys", "test", "TABLE", "", NULL, NULL, NULL,
"rowid", "SYSTEM" ]
# 12:42:09 >
diff --git
a/sql/test/BugTracker-2016/Tests/column_alias_in_where_clause.Bug-3947.stable.out
b/sql/test/BugTracker-2016/Tests/column_alias_in_where_clause.Bug-3947.stable.out
---
a/sql/test/BugTracker-2016/Tests/column_alias_in_where_clause.Bug-3947.stable.out
+++
b/sql/test/BugTracker-2016/Tests/column_alias_in_where_clause.Bug-3947.stable.out
@@ -117,7 +117,7 @@ stdout of test 'column_alias_in_where_cl
% .t1, sys.t1, sys.t1 # table_name
% CAT, A, C # name
% char, int, varchar # type
-% 1, 1, 0 # length
+% 0, 1, 0 # length
#SELECT * FROM (SELECT cast(null as char(1)) AS "CAT", a AS "A", c AS "C" FROM
t_alias) T1 WHERE "CAT" IS NULL;
% .t1, sys.t1, sys.t1 # table_name
% CAT, A, C # name
@@ -129,14 +129,14 @@ stdout of test 'column_alias_in_where_cl
% .t1, sys.t1, sys.t1 # table_name
% CAT, A, C # name
% char, int, varchar # type
-% 1, 2, 4 # length
+% 0, 2, 4 # length
[ NULL, 1, "tien" ]
[ NULL, 11, "elf" ]
#SELECT * FROM (SELECT cast(null as char(1)) AS "CAT", a AS "A", c AS "C" FROM
t_alias) T1 WHERE "CAT" IS NULL and "CAT" = NULL;
% .t1, sys.t1, sys.t1 # table_name
% CAT, A, C # name
% char, int, varchar # type
-% 1, 1, 0 # length
+% 0, 1, 0 # length
#SELECT a AS "A", b AS "B", c AS "C" FROM t_alias ORDER BY "C", "A", "B";
% sys., sys., sys. # table_name
% A, B, C # name
diff --git
a/sql/test/bugs/Tests/subselect_multiple_unionall_where_1=1-bug-sf-1005596.stable.out
b/sql/test/bugs/Tests/subselect_multiple_unionall_where_1=1-bug-sf-1005596.stable.out
---
a/sql/test/bugs/Tests/subselect_multiple_unionall_where_1=1-bug-sf-1005596.stable.out
+++
b/sql/test/bugs/Tests/subselect_multiple_unionall_where_1=1-bug-sf-1005596.stable.out
@@ -59,8 +59,8 @@ stdout of test 'subselect_multiple_union
[ 2 ]
% .tables, .tables, .tables, .tables, .tables,
.tables, .tables, .tables, .tables, .tables #
table_name
% TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS,
TYPE_CAT, TYPE_SCHEM, TYPE_NAME, SELF_REFERENCING_COL_NAME,
REF_GENERATION # name
-% char, varchar, varchar, char, char, char, char,
char, char, char # type
-% 4, 0, 0, 20, 0, 0, 0, 0, 5, 6 #
length
+% char, varchar, varchar, clob, char, char, char,
char, char, char # type
+% 4, 0, 0, 0, 0, 0, 0, 0, 5, 6 #
length
#SELECT 3 AS number;
% . # table_name
% number # name
@@ -69,8 +69,8 @@ stdout of test 'subselect_multiple_union
[ 3 ]
% .tables, .tables, .tables, .tables, .tables,
.tables, .tables, .tables, .tables, .tables #
table_name
% TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS,
TYPE_CAT, TYPE_SCHEM, TYPE_NAME, SELF_REFERENCING_COL_NAME,
REF_GENERATION # name
-% char, varchar, varchar, char, char, char, char,
char, char, char # type
-% 4, 3, 11, 20, 0, 0, 0, 0, 5, 6 #
length
+% char, varchar, varchar, clob, char, char, char,
char, char, char # type
+% 4, 3, 11, 5, 0, 0, 0, 0, 5, 6 #
length
[ "demo", "sys", "test_update", "TABLE", "", NULL, NULL,
NULL, "rowid", "SYSTEM" ]
# 12:52:40 >
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
@@ -159,8 +159,8 @@ stdout of test 'sqlsmith-exists2` in dir
# 1)
# and (ref_4.i is not null))))
# on (ref_1.bb is null);
-% .%26 # table_name
-% %26 # name
+% .%31 # table_name
+% %31 # name
% tinyint # type
% 1 # length
#select
@@ -190,8 +190,8 @@ stdout of test 'sqlsmith-exists2` in dir
# on (true)
# on (exists (select ref_5.col0, ref_8.col2))
# on (true);
-% .%44 # table_name
-% %44 # name
+% .%46 # table_name
+% %46 # name
% tinyint # type
% 1 # length
[ 1 ]
@@ -463,8 +463,8 @@ stdout of test 'sqlsmith-exists2` in dir
[ 1, 1, 100, 1, 100, 1, 100, 1 ]
#create table myitem (i_current_price decimal(7,2));
#SELECT 1 FROM myitem WHERE i_current_price BETWEEN 64 AND 64 + 10 AND
i_current_price BETWEEN 64 + 1 AND 64 + 15;
-% .%52 # table_name
-% %52 # name
+% .%2 # table_name
+% %2 # name
% tinyint # type
% 1 # length
#SELECT 1 FROM tab0 WHERE col0 BETWEEN 64 AND 64 + 10 AND col0 BETWEEN 64 + 1
AND 64 + 15;
@@ -472,6 +472,39 @@ stdout of test 'sqlsmith-exists2` in dir
% %2 # name
% tinyint # type
% 1 # length
+#select
+# subq_0.c6 as c0,
+# subq_0.c5 as c1,
+# 38 as c2 from
+# (select
+# case when ref_0.i is not null then ref_0.i else ref_0.i end
+# as c0,
+# ref_0.i as c1,
+# ref_0.i as c2,
+# ref_0.i as c3,
+# ref_0.i as c4,
+# ref_0.i as c5,
+# ref_0.i as c6,
+# ref_0.i as c7,
+# ref_0.i as c8,
+# case when ref_0.i is not null then ref_0.i else ref_0.i end
+# as c9
+# from
+# integers as ref_0
+# where (exists (
+# select
+# ref_1.col4 as c0,
+# 28 as c1,
+# ref_1.col2 as c2,
+# ref_1.col3 as c3,
+# 71 as c4
+# from
+# tbl_productsales as ref_1
+% ., ., . # table_name
+% c0, c1, c2 # name
+% int, int, tinyint # type
+% 1, 1, 2 # length
+[ NULL, NULL, 38 ]
#ROLLBACK;
# 22:12:15 >
diff --git a/sql/test/miscellaneous/Tests/simple_plans.stable.out
b/sql/test/miscellaneous/Tests/simple_plans.stable.out
--- a/sql/test/miscellaneous/Tests/simple_plans.stable.out
+++ b/sql/test/miscellaneous/Tests/simple_plans.stable.out
@@ -167,8 +167,8 @@ end user.s20_0;
function user.s22_0():void;
X_4:void := querylog.define("explain select 1 from another_t t1 inner join
another_t t2 on t1.col1 between t2.col1 - 1 and t2.col1 + 1;":str,
"default_pipe":str, 34:int);
barrier X_169:bit := language.dataflow();
- X_48:bat[:str] := bat.pack(".%15":str);
- X_49:bat[:str] := bat.pack("%15":str);
+ X_48:bat[:str] := bat.pack(".%13":str);
+ X_49:bat[:str] := bat.pack("%13":str);
X_50:bat[:str] := bat.pack("tinyint":str);
X_51:bat[:int] := bat.pack(1:int);
X_52:bat[:int] := bat.pack(0:int);
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
