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 -- checkin-list@monetdb.org To unsubscribe send an email to checkin-list-le...@monetdb.org