Changeset: e9afbe5ac9c3 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/e9afbe5ac9c3
Modified Files:
sql/server/rel_optimize_others.c
sql/server/rel_optimizer.c
sql/server/rel_rel.c
sql/server/rel_rel.h
sql/server/rel_select.c
sql/test/BugTracker-2022/Tests/dependencies.Bug-7328.test
sql/test/Dependencies/Tests/dependency_DBobjects.test
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/SQLancer/Tests/sqlancer18.test
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/sql_dump/Tests/dump.test
Branch: Dec2023
Log Message:
improved optimization of relational psm
diffs (truncated from 564 to 300 lines):
diff --git a/sql/server/rel_optimize_others.c b/sql/server/rel_optimize_others.c
--- a/sql/server/rel_optimize_others.c
+++ b/sql/server/rel_optimize_others.c
@@ -1054,6 +1054,7 @@ rel_dce_(mvc *sql, sql_rel *rel)
return rel;
}
+
/* Remove unused expressions */
static sql_rel *
rel_dce(visitor *v, global_props *gp, sql_rel *rel)
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
@@ -607,8 +607,8 @@ run_optimizer_set(visitor *v, sql_optimi
/* 'profile' means to benchmark each individual optimizer run */
/* 'instantiate' means to rewrite logical tables: (merge, remote, replica
tables) */
-sql_rel *
-rel_optimizer(mvc *sql, sql_rel *rel, int profile, int instantiate, int
value_based_opt, int storage_based_opt)
+static sql_rel *
+rel_optimizer_one(mvc *sql, sql_rel *rel, int profile, int instantiate, int
value_based_opt, int storage_based_opt)
{
global_props gp = (global_props) {.cnt = {0}, .instantiate =
(uint8_t)instantiate, .opt_cycle = 0,
.has_special_modify = rel && is_modify(rel->op) && rel->flag&UPD_COMP};
@@ -635,3 +635,32 @@ rel_optimizer(mvc *sql, sql_rel *rel, in
rel = run_optimizer_set(&v, sql->runs, rel, &gp, post_sql_optimizers);
return rel;
}
+
+static sql_exp *
+exp_optimize_one(visitor *v, sql_rel *rel, sql_exp *e, int depth )
+{
+ (void)rel;
+ (void)depth;
+ if (e->type == e_psm && e->flag == PSM_REL && e->l) {
+ e->l = rel_optimizer_one(v->sql, e->l, 0, v->changes,
v->value_based_opt, v->storage_based_opt);
+ }
+ return e;
+}
+
+sql_rel *
+rel_optimizer(mvc *sql, sql_rel *rel, int profile, int instantiate, int
value_based_opt, int storage_based_opt)
+{
+ if (rel && rel->op == op_ddl && rel->flag == ddl_psm) {
+ if (!list_empty(rel->exps)) {
+ bool changed = 0;
+ visitor v = { .sql = sql, .value_based_opt =
value_based_opt, .storage_based_opt = storage_based_opt, .changes = instantiate
};
+ for(node *n = rel->exps->h; n; n = n->next) {
+ sql_exp *e = n->data;
+ exp_visitor(&v, rel, e, 1, exp_optimize_one,
true, true, true, &changed);
+ }
+ }
+ return rel;
+ } else {
+ return rel_optimizer_one(sql, rel, profile, instantiate,
value_based_opt, storage_based_opt);
+ }
+}
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
@@ -2044,17 +2044,17 @@ rel_dependencies(mvc *sql, sql_rel *r)
return l;
}
-static list *exps_exp_visitor(visitor *v, sql_rel *rel, list *exps, int depth,
exp_rewrite_fptr exp_rewriter, bool topdown, bool relations_topdown);
+static list *exps_exp_visitor(visitor *v, sql_rel *rel, list *exps, int depth,
exp_rewrite_fptr exp_rewriter, bool topdown, bool relations_topdown, bool
visit_relations_once);
static inline list *
-exps_exps_exp_visitor(visitor *v, sql_rel *rel, list *lists, int depth,
exp_rewrite_fptr exp_rewriter, bool topdown, bool relations_topdown)
+exps_exps_exp_visitor(visitor *v, sql_rel *rel, list *lists, int depth,
exp_rewrite_fptr exp_rewriter, bool topdown, bool relations_topdown, bool
visit_relations_once)
{
node *n;
if (list_empty(lists))
return lists;
for (n = lists->h; n; n = n->next) {
- if (n->data && (n->data = exps_exp_visitor(v, rel, n->data,
depth, exp_rewriter, topdown, relations_topdown)) == NULL)
+ if (n->data && (n->data = exps_exp_visitor(v, rel, n->data,
depth, exp_rewriter, topdown, relations_topdown, visit_relations_once)) == NULL)
return NULL;
}
return lists;
@@ -2062,8 +2062,8 @@ exps_exps_exp_visitor(visitor *v, sql_re
static sql_rel *rel_exp_visitor(visitor *v, sql_rel *rel, exp_rewrite_fptr
exp_rewriter, bool topdown, bool relations_topdown);
-static sql_exp *
-exp_visitor(visitor *v, sql_rel *rel, sql_exp *e, int depth, exp_rewrite_fptr
exp_rewriter, bool topdown, bool relations_topdown, bool *changed)
+sql_exp *
+exp_visitor(visitor *v, sql_rel *rel, sql_exp *e, int depth, exp_rewrite_fptr
exp_rewriter, bool topdown, bool relations_topdown, bool visit_relations_once,
bool *changed)
{
if (mvc_highwater(v->sql))
return sql_error(v->sql, 10, SQLSTATE(42000) "Query too
complex: running out of stack space");
@@ -2080,59 +2080,59 @@ exp_visitor(visitor *v, sql_rel *rel, sq
case e_column:
break;
case e_convert:
- if ((e->l = exp_visitor(v, rel, e->l, depth+1, exp_rewriter,
topdown, relations_topdown, changed)) == NULL)
+ if ((e->l = exp_visitor(v, rel, e->l, depth+1, exp_rewriter,
topdown, relations_topdown, visit_relations_once, changed)) == NULL)
return NULL;
break;
case e_aggr:
case e_func:
if (e->r) /* rewrite rank -r is list of lists */
- if ((e->r = exps_exps_exp_visitor(v, rel, e->r,
depth+1, exp_rewriter, topdown, relations_topdown)) == NULL)
+ if ((e->r = exps_exps_exp_visitor(v, rel, e->r,
depth+1, exp_rewriter, topdown, relations_topdown, visit_relations_once)) ==
NULL)
return NULL;
if (e->l)
- if ((e->l = exps_exp_visitor(v, rel, e->l, depth+1,
exp_rewriter, topdown, relations_topdown)) == NULL)
+ if ((e->l = exps_exp_visitor(v, rel, e->l, depth+1,
exp_rewriter, topdown, relations_topdown, visit_relations_once)) == NULL)
return NULL;
break;
case e_cmp:
if (e->flag == cmp_or || e->flag == cmp_filter) {
- if ((e->l = exps_exp_visitor(v, rel, e->l, depth+1,
exp_rewriter, topdown, relations_topdown)) == NULL)
+ if ((e->l = exps_exp_visitor(v, rel, e->l, depth+1,
exp_rewriter, topdown, relations_topdown, visit_relations_once)) == NULL)
return NULL;
- if ((e->r = exps_exp_visitor(v, rel, e->r, depth+1,
exp_rewriter, topdown, relations_topdown)) == NULL)
+ if ((e->r = exps_exp_visitor(v, rel, e->r, depth+1,
exp_rewriter, topdown, relations_topdown, visit_relations_once)) == NULL)
return NULL;
} else if (e->flag == cmp_in || e->flag == cmp_notin) {
- if ((e->l = exp_visitor(v, rel, e->l, depth+1,
exp_rewriter, topdown, relations_topdown, changed)) == NULL)
+ if ((e->l = exp_visitor(v, rel, e->l, depth+1,
exp_rewriter, topdown, relations_topdown, visit_relations_once, changed)) ==
NULL)
return NULL;
- if ((e->r = exps_exp_visitor(v, rel, e->r, depth+1,
exp_rewriter, topdown, relations_topdown)) == NULL)
+ if ((e->r = exps_exp_visitor(v, rel, e->r, depth+1,
exp_rewriter, topdown, relations_topdown, visit_relations_once)) == NULL)
return NULL;
} else {
- if ((e->l = exp_visitor(v, rel, e->l, depth+1,
exp_rewriter, topdown, relations_topdown, changed)) == NULL)
+ if ((e->l = exp_visitor(v, rel, e->l, depth+1,
exp_rewriter, topdown, relations_topdown, visit_relations_once, changed)) ==
NULL)
return NULL;
- if ((e->r = exp_visitor(v, rel, e->r, depth+1,
exp_rewriter, topdown, relations_topdown, changed)) == NULL)
+ if ((e->r = exp_visitor(v, rel, e->r, depth+1,
exp_rewriter, topdown, relations_topdown, visit_relations_once, changed)) ==
NULL)
return NULL;
- if (e->f && (e->f = exp_visitor(v, rel, e->f, depth+1,
exp_rewriter, topdown, relations_topdown, changed)) == NULL)
+ if (e->f && (e->f = exp_visitor(v, rel, e->f, depth+1,
exp_rewriter, topdown, relations_topdown, visit_relations_once, changed)) ==
NULL)
return NULL;
}
break;
case e_psm:
if (e->flag & PSM_SET || e->flag & PSM_RETURN || e->flag &
PSM_EXCEPTION) {
- if ((e->l = exp_visitor(v, rel, e->l, depth+1,
exp_rewriter, topdown, relations_topdown, changed)) == NULL)
+ if ((e->l = exp_visitor(v, rel, e->l, depth+1,
exp_rewriter, topdown, relations_topdown, visit_relations_once, changed)) ==
NULL)
return NULL;
} else if (e->flag & PSM_VAR) {
return e;
} else if (e->flag & PSM_WHILE || e->flag & PSM_IF) {
- if ((e->l = exp_visitor(v, rel, e->l, depth+1,
exp_rewriter, topdown, relations_topdown, changed)) == NULL)
+ if ((e->l = exp_visitor(v, rel, e->l, depth+1,
exp_rewriter, topdown, relations_topdown, visit_relations_once, changed)) ==
NULL)
return NULL;
- if ((e->r = exps_exp_visitor(v, rel, e->r, depth+1,
exp_rewriter, topdown, relations_topdown)) == NULL)
+ if ((e->r = exps_exp_visitor(v, rel, e->r, depth+1,
exp_rewriter, topdown, relations_topdown, visit_relations_once)) == NULL)
return NULL;
- if (e->flag == PSM_IF && e->f && (e->f =
exps_exp_visitor(v, rel, e->f, depth+1, exp_rewriter, topdown,
relations_topdown)) == NULL)
+ if (e->flag == PSM_IF && e->f && (e->f =
exps_exp_visitor(v, rel, e->f, depth+1, exp_rewriter, topdown,
relations_topdown, visit_relations_once)) == NULL)
return NULL;
} else if (e->flag & PSM_REL) {
- if ((e->l = rel_exp_visitor(v, e->l, exp_rewriter,
topdown, relations_topdown)) == NULL)
+ if (!visit_relations_once && (e->l = rel_exp_visitor(v,
e->l, exp_rewriter, topdown, relations_topdown)) == NULL)
return NULL;
}
break;
case e_atom:
if (e->f)
- if ((e->f = exps_exp_visitor(v, rel, e->f, depth+1,
exp_rewriter, topdown, relations_topdown)) == NULL)
+ if ((e->f = exps_exp_visitor(v, rel, e->f, depth+1,
exp_rewriter, topdown, relations_topdown, visit_relations_once)) == NULL)
return NULL;
break;
}
@@ -2146,13 +2146,13 @@ exp_visitor(visitor *v, sql_rel *rel, sq
}
static list *
-exps_exp_visitor(visitor *v, sql_rel *rel, list *exps, int depth,
exp_rewrite_fptr exp_rewriter, bool topdown, bool relations_topdown)
+exps_exp_visitor(visitor *v, sql_rel *rel, list *exps, int depth,
exp_rewrite_fptr exp_rewriter, bool topdown, bool relations_topdown, bool
visit_relations_once)
{
bool changed = false;
if (list_empty(exps))
return exps;
for (node *n = exps->h; n; n = n->next)
- if (n->data && (n->data = exp_visitor(v, rel, n->data, depth,
exp_rewriter, topdown, relations_topdown, &changed)) == NULL)
+ if (n->data && (n->data = exp_visitor(v, rel, n->data, depth,
exp_rewriter, topdown, relations_topdown, visit_relations_once, &changed)) ==
NULL)
return NULL;
if (changed && depth == 0) /* only level 0 exps use hash, so remove
only on those */
list_hash_clear(exps);
@@ -2169,9 +2169,9 @@ rel_exp_visitor(visitor *v, sql_rel *rel
return rel;
if (relations_topdown) {
- if (rel->exps && (rel->exps = exps_exp_visitor(v, rel,
rel->exps, 0, exp_rewriter, topdown, relations_topdown)) == NULL)
+ if (rel->exps && (rel->exps = exps_exp_visitor(v, rel,
rel->exps, 0, exp_rewriter, topdown, relations_topdown, false)) == NULL)
return NULL;
- if ((is_groupby(rel->op) || is_simple_project(rel->op)) &&
rel->r && (rel->r = exps_exp_visitor(v, rel, rel->r, 0, exp_rewriter, topdown,
relations_topdown)) == NULL)
+ if ((is_groupby(rel->op) || is_simple_project(rel->op)) &&
rel->r && (rel->r = exps_exp_visitor(v, rel, rel->r, 0, exp_rewriter, topdown,
relations_topdown, false)) == NULL)
return NULL;
}
@@ -2234,9 +2234,9 @@ rel_exp_visitor(visitor *v, sql_rel *rel
}
if (!relations_topdown) {
- if (rel->exps && (rel->exps = exps_exp_visitor(v, rel,
rel->exps, 0, exp_rewriter, topdown, relations_topdown)) == NULL)
+ if (rel->exps && (rel->exps = exps_exp_visitor(v, rel,
rel->exps, 0, exp_rewriter, topdown, relations_topdown, false)) == NULL)
return NULL;
- if ((is_groupby(rel->op) || is_simple_project(rel->op)) &&
rel->r && (rel->r = exps_exp_visitor(v, rel, rel->r, 0, exp_rewriter, topdown,
relations_topdown)) == NULL)
+ if ((is_groupby(rel->op) || is_simple_project(rel->op)) &&
rel->r && (rel->r = exps_exp_visitor(v, rel, rel->r, 0, exp_rewriter, topdown,
relations_topdown, false)) == NULL)
return NULL;
}
@@ -2473,13 +2473,13 @@ rel_visitor_bottomup(visitor *v, sql_rel
list *
exps_exp_visitor_topdown(visitor *v, sql_rel *rel, list *exps, int depth,
exp_rewrite_fptr exp_rewriter, bool relations_topdown)
{
- return exps_exp_visitor(v, rel, exps, depth, exp_rewriter, true,
relations_topdown);
+ return exps_exp_visitor(v, rel, exps, depth, exp_rewriter, true,
relations_topdown, false);
}
list *
exps_exp_visitor_bottomup(visitor *v, sql_rel *rel, list *exps, int depth,
exp_rewrite_fptr exp_rewriter, bool relations_topdown)
{
- return exps_exp_visitor(v, rel, exps, depth, exp_rewriter, false,
relations_topdown);
+ return exps_exp_visitor(v, rel, exps, depth, exp_rewriter, false,
relations_topdown, false);
}
static bool
@@ -2549,7 +2549,7 @@ exp_freevar_offset(mvc *sql, sql_exp *e)
visitor v = { .sql = sql };
(void) changed;
- exp_visitor(&v, NULL, e, 0, &_exp_freevar_offset, true, true, &changed);
+ exp_visitor(&v, NULL, e, 0, &_exp_freevar_offset, true, true, true,
&changed);
/* freevar offset is passed via changes */
return (v.changes);
}
diff --git a/sql/server/rel_rel.h b/sql/server/rel_rel.h
--- a/sql/server/rel_rel.h
+++ b/sql/server/rel_rel.h
@@ -142,6 +142,7 @@ typedef struct visitor {
} visitor;
typedef sql_exp *(*exp_rewrite_fptr)(visitor *v, sql_rel *rel, sql_exp *e, int
depth /* depth of the nested expression */);
+extern sql_exp *exp_visitor(visitor *v, sql_rel *rel, sql_exp *e, int depth,
exp_rewrite_fptr exp_rewriter, bool topdown, bool relations_topdown, bool
visit_relations_once, bool *changed);
extern sql_rel *rel_exp_visitor_topdown(visitor *v, sql_rel *rel,
exp_rewrite_fptr exp_rewriter, bool relations_topdown);
extern sql_rel *rel_exp_visitor_bottomup(visitor *v, sql_rel *rel,
exp_rewrite_fptr exp_rewriter, bool relations_topdown);
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -1309,9 +1309,9 @@ rel_column_ref(sql_query *query, sql_rel
return sql_error(sql, ERR_AMBIGUOUS,
SQLSTATE(42000) "SELECT: identifier '%s' ambiguous", name);
if (v && !exp) {
if (*rel)
- *rel = rel_crossproduct(sql->sa, *rel,
v, op_join);
+ *rel = rel_crossproduct(sql->sa, *rel,
rel_dup(v), op_join);
else
- *rel = v;
+ *rel = rel_dup(v);
exp = rel_bind_column(sql, *rel, name, f, 0);
}
}
diff --git a/sql/test/BugTracker-2022/Tests/dependencies.Bug-7328.test
b/sql/test/BugTracker-2022/Tests/dependencies.Bug-7328.test
--- a/sql/test/BugTracker-2022/Tests/dependencies.Bug-7328.test
+++ b/sql/test/BugTracker-2022/Tests/dependencies.Bug-7328.test
@@ -123,10 +123,6 @@ FROM sys.dependency_columns_on_functions
WHERE function_name LIKE 'mmtest04'
ORDER BY name
----
-id
-mmtest04
-1
-7
name
mmtest04
1
diff --git a/sql/test/Dependencies/Tests/dependency_DBobjects.test
b/sql/test/Dependencies/Tests/dependency_DBobjects.test
--- a/sql/test/Dependencies/Tests/dependency_DBobjects.test
+++ b/sql/test/Dependencies/Tests/dependency_DBobjects.test
@@ -307,7 +307,7 @@ DEP_INDEX
query TTT rowsort
SELECT c.name, f.name, 'DEP_FUNC' from sys.functions as f, sys.columns as c,
sys.dependencies as dep where c.id = dep.id AND f.id = dep.depend_id AND
dep.depend_type = 7 ORDER BY c.name, f.name
----
-309 values hashing to a418d297b4e68ad28b45e6534c82d1a3
+261 values hashing to adb8ebc80dade286b720009b58eebd1e
query TTT rowsort
SELECT c.name, tri.name, 'DEP_TRIGGER' from sys.columns as c, sys.triggers as
tri, sys.dependencies as dep where dep.id = c.id AND dep.depend_id =tri.id AND
dep.depend_type = 8 order by c.name, tri.name
diff --git a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
--- a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
+++ b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
@@ -947,36 +947,21 @@ SELECT c.name, i.name, 'DEP_INDEX' from
query TTT rowsort
SELECT c.name, f.name, 'DEP_FUNC' from sys.functions as f, sys.columns as c,
sys.dependencies as dep where c.id = dep.id AND f.id = dep.depend_id AND
dep.depend_type = 7 ORDER BY c.name, f.name
----
-access
-describe_table
-DEP_FUNC
-authorization
-describe_function
-DEP_FUNC
column_id
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
