Changeset: d3d1ccb1416e for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/d3d1ccb1416e
Modified Files:
sql/backends/monet5/sql_cat.c
sql/include/sql_catalog.h
sql/include/sql_relation.h
sql/scripts/10_sys_schema_extension.sql
sql/server/rel_exp.c
sql/server/rel_exp.h
sql/server/rel_optimize_proj.c
sql/server/rel_optimize_sel.c
sql/server/rel_psm.c
sql/server/sql_parser.y
sql/storage/store.c
sql/test/BugTracker-2021/Tests/plan-not-optimal-view.Bug-7140.test
sql/test/BugTracker-2025/Tests/7691-slow-query-on-allways-false.test
sql/test/Tests/select_window_pushdown.test
sql/test/bugs/Tests/rtrim_bug.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/miscellaneous/Tests/unique_keys.test
sql/test/rel-optimizers/Tests/merge-unions.test
Branch: default
Log Message:
added group filters, which aren't treated like equi-join operators
diffs (truncated from 496 to 300 lines):
diff --git a/sql/backends/monet5/sql_cat.c b/sql/backends/monet5/sql_cat.c
--- a/sql/backends/monet5/sql_cat.c
+++ b/sql/backends/monet5/sql_cat.c
@@ -1031,14 +1031,14 @@ create_func(mvc *sql, char *sname, char
}
}
- if ((sf = sql_bind_func_(sql, s->base.name, fname, tl, f->type,
false, true)) != NULL) {
+ if ((sf = sql_bind_func_(sql, s->base.name, fname, tl,
f->group?F_GROUPFILT:f->type, false, true)) != NULL) {
sql_func *sff = sf->func;
if (!sff->s || sff->system)
throw(SQL,"sql.create_func", SQLSTATE(42000)
"%s %s: not allowed to replace system %s %s;", base, F, fn, sff->base.name);
/* if all function parameters are the same, return */
- if (sff->lang == f->lang && sff->type == f->type &&
+ if (sff->lang == f->lang && sff->type == f->type &&
sff->group == f->group &&
sff->varres == f->varres && sff->vararg ==
f->vararg &&
((!sff->query && !f->query) || (sff->query &&
f->query && strcmp(sff->query, f->query) == 0)) &&
list_cmp(sff->res, f->res, (fcmp) &args_cmp) ==
0 &&
@@ -1061,7 +1061,7 @@ create_func(mvc *sql, char *sname, char
sql->errstr[0] = '\0';
}
}
- switch (mvc_create_func(&nf, sql, NULL, s, f->base.name, f->ops,
f->res, f->type, f->lang, f->mod, f->imp, f->query, f->varres, f->vararg,
f->system, f->side_effect, f->order_required, f->opt_order)) {
+ switch (mvc_create_func(&nf, sql, NULL, s, f->base.name, f->ops,
f->res, f->group?F_GROUPFILT:f->type, f->lang, f->mod, f->imp, f->query,
f->varres, f->vararg, f->system, f->side_effect, f->order_required,
f->opt_order)) {
case -1:
throw(SQL,"sql.create_func", SQLSTATE(HY013)
MAL_MALLOC_FAIL);
case -2:
diff --git a/sql/include/sql_catalog.h b/sql/include/sql_catalog.h
--- a/sql/include/sql_catalog.h
+++ b/sql/include/sql_catalog.h
@@ -424,7 +424,8 @@ typedef enum sql_ftype {
F_FILT = 4,
F_UNION = 5,
F_ANALYTIC = 6,
- F_LOADER = 7
+ F_LOADER = 7,
+ F_GROUPFILT = 8
} sql_ftype;
#define IS_FUNC(f) ((f)->type == F_FUNC)
@@ -434,6 +435,7 @@ typedef enum sql_ftype {
#define IS_UNION(f) ((f)->type == F_UNION)
#define IS_ANALYTIC(f) ((f)->type == F_ANALYTIC)
#define IS_LOADER(f) ((f)->type == F_LOADER)
+#define IS_GROUPFILT(f) ((f)->type == F_GROUPFILT)
#define FUNC_TYPE_STR(type, F, fn) \
switch (type) { \
@@ -465,6 +467,10 @@ typedef enum sql_ftype {
F = "LOADER FUNCTION"; \
fn = "loader function"; \
break; \
+ case F_GROUPFILT: \
+ F = "GROUP FILTER FUNCTION"; \
+ fn = "group filter function"; \
+ break; \
default: \
assert(0); \
}
@@ -511,7 +517,8 @@ typedef struct sql_func {
instantiated:1, /* if the function is instantiated */
private:1, /* certain functions cannot be bound from user queries
*/
order_required:1, /* some aggregate functions require an order */
- opt_order:1; /* some aggregate functions could have the inputs
sorted */
+ opt_order:1,
+ group:1; /* some filter functions behave like group join */
short fix_scale;
/*
diff --git a/sql/include/sql_relation.h b/sql/include/sql_relation.h
--- a/sql/include/sql_relation.h
+++ b/sql/include/sql_relation.h
@@ -278,6 +278,7 @@ typedef enum operator_type {
#define reset_single(rel) (rel)->single = 0
#define set_recursive(rel) (rel)->recursive = 1
#define is_recursive(rel) ((rel)->recursive)
+#define set_dynamic(rel) (rel)->dynamic = 1
#define is_dynamic(rel) ((rel)->dynamic)
#define is_freevar(e) ((e)->freevar)
diff --git a/sql/scripts/10_sys_schema_extension.sql
b/sql/scripts/10_sys_schema_extension.sql
--- a/sql/scripts/10_sys_schema_extension.sql
+++ b/sql/scripts/10_sys_schema_extension.sql
@@ -355,7 +355,7 @@ CREATE TABLE sys.function_types (
function_type_keyword VARCHAR(30) NOT NULL);
-- Values taken from sql/include/sql_catalog.h see: #define F_FUNC 1,
--- F_PROC 2, F_AGGR 3, F_FILT 4, F_UNION 5, F_ANALYTIC 6, F_LOADER 7.
+-- F_PROC 2, F_AGGR 3, F_FILT 4, F_UNION 5, F_ANALYTIC 6, F_LOADER 7,
F_GROUPFILT 8.
INSERT INTO sys.function_types (function_type_id, function_type_name,
function_type_keyword) VALUES
(1, 'Scalar function', 'FUNCTION'),
(2, 'Procedure', 'PROCEDURE'),
@@ -363,7 +363,8 @@ INSERT INTO sys.function_types (function
(4, 'Filter function', 'FILTER FUNCTION'),
(5, 'Function returning a table', 'FUNCTION'),
(6, 'Analytic function', 'WINDOW'),
- (7, 'Loader function', 'LOADER');
+ (7, 'Loader function', 'LOADER'),
+ (8, 'Group filter function', 'GROUP FILTER FUNCTION');
ALTER TABLE sys.function_types SET READ ONLY;
GRANT SELECT ON sys.function_types TO PUBLIC;
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
@@ -4185,3 +4185,20 @@ free_exp(allocator *sa, sql_exp *e)
}
_free_exp_internal(sa, e);
}
+
+bool
+exps_has_group_filter(list *exps)
+{
+ if (list_empty(exps))
+ return false;
+ for(node *n = exps->h; n; n = n->next) {
+ sql_exp *e = n->data;
+ if (e->type == e_cmp && e->flag == cmp_filter) {
+ sql_subfunc *sf = e->f;
+ if (sf->func->group)
+ return true;
+ }
+ }
+ return false;
+}
+
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
@@ -233,6 +233,7 @@ extern sql_exp *exp_numeric_supertype(mv
extern sql_exp *exp_values_set_supertype(mvc *sql, sql_exp *values,
sql_subtype *opt_super);
extern void free_exp(allocator *sa, sql_exp *e);
extern void free_exps(allocator *sa, list *exps);
+extern bool exps_has_group_filter(list *exps);
extern int exp_is_rename(sql_exp *e);
extern int exp_is_useless_rename(sql_exp *e);
diff --git a/sql/server/rel_optimize_proj.c b/sql/server/rel_optimize_proj.c
--- a/sql/server/rel_optimize_proj.c
+++ b/sql/server/rel_optimize_proj.c
@@ -432,10 +432,12 @@ exp_match_exp_cmp( sql_exp *e1, sql_exp
/* Pushing projects up the tree. Done very early in the optimizer.
* Makes later steps easier.
*/
+static sql_rel * rel_project_select_exp(visitor *v, sql_rel *rel);
extern void _rel_print(mvc *sql, sql_rel *rel);
static sql_rel *
rel_push_project_up_(visitor *v, sql_rel *rel)
{
+ rel = rel_project_select_exp(v, rel);
if (is_simple_project(rel->op) && rel->l && !rel_is_ref(rel)) {
sql_rel *l = rel->l;
if (is_simple_project(l->op))
@@ -3165,6 +3167,18 @@ rel_project_select_exp(visitor *v, sql_r
for(node *n = rel->exps->h; n; n = n->next) {
sql_exp *col = n->data;
if (col->type == e_column) {
+ sql_rel *ll = l->l;
+ /* first check if lower project is
const */
+ if (is_simple_project(ll->op) &&
!ll->dynamic /* not base part of recusive union */) {
+ sql_exp *i = rel_find_exp(l,
col);
+ if (i && exp_is_atom(i) &&
!i->f) {
+ sql_exp *e = n->data =
exp_copy(v->sql, i);
+ exp_setalias(e,
col->alias.label, exp_relname(col), exp_name(col));
+
exp_propagate(v->sql->sa, e, col);
+
list_hash_clear(rel->exps);
+ continue;
+ }
+ }
for(node *m = l->exps->h; m; m =
m->next) {
sql_exp *cmp = m->data;
if (cmp->type == e_cmp &&
cmp->flag == cmp_equal && !is_anti(cmp) && !is_semantics(cmp) &&
exp_is_atom(cmp->r)) {
@@ -3381,7 +3395,7 @@ rel_push_join_down_munion(visitor *v, sq
list *exps = rel->exps, *attr = rel->attr;
sql_exp *je = NULL;
- if (is_recursive(l) || is_recursive(r))
+ if (is_recursive(l) || is_recursive(r) ||
exps_has_group_filter(rel->exps) /* we cannot push group join like filters down
*/)
return rel;
/* we would like to optimize in place reference rels which point
* to replica tables and let the replica optimizer handle those
diff --git a/sql/server/rel_optimize_sel.c b/sql/server/rel_optimize_sel.c
--- a/sql/server/rel_optimize_sel.c
+++ b/sql/server/rel_optimize_sel.c
@@ -186,6 +186,8 @@ bind_remove_redundant_join(visitor *v, g
static list *
exp_merge_range(visitor *v, sql_rel *rel, list *exps)
{
+ if (!exps)
+ return exps;
node *n, *m;
for (n=exps->h; n; n = n->next) {
sql_exp *e = n->data;
@@ -1111,6 +1113,7 @@ rel_merge_select_rse(visitor *v, sql_rel
return rel;
}
+static sql_rel * rel_push_select_down(visitor *v, sql_rel *rel);
/* pack optimizers into a single function call to avoid iterations in the AST
*/
static sql_rel *
rel_optimize_select_and_joins_bottomup_(visitor *v, sql_rel *rel)
@@ -1119,6 +1122,7 @@ rel_optimize_select_and_joins_bottomup_(
return rel;
uint8_t cycle = *(uint8_t*) v->data;
+ rel = rel_push_select_down(v, rel);
rel->exps = exp_merge_range(v, rel, rel->exps);
rel = rel_select_cse(v, rel);
if (cycle == 1)
@@ -3906,7 +3910,8 @@ rel_push_select_down(visitor *v, sql_rel
set_distinct(rel);
v->changes++;
}
- if (is_select(rel->op) && r && is_munion(r->op) && !is_recursive(r) &&
!list_empty(r->exps) && !rel_is_ref(r) && !is_single(r) && !list_empty(exps)) {
+ if (is_select(rel->op) && !exps_has_group_filter(rel->exps) &&
+ r && is_munion(r->op) && !is_recursive(r) && !list_empty(r->exps)
&& !rel_is_ref(r) && !is_single(r) && !list_empty(exps)) {
sql_rel *u = r;
list *rels = u->l, *nrels = sa_list(v->sql->sa);
for(node *n = rels->h; n; n = n->next) {
diff --git a/sql/server/rel_psm.c b/sql/server/rel_psm.c
--- a/sql/server/rel_psm.c
+++ b/sql/server/rel_psm.c
@@ -919,9 +919,9 @@ rel_create_func(sql_query *query, dlist
return sql_error(sql, 02, SQLSTATE(42000) "CREATE %s: %ss
cannot return tables", F, fn);
else if (res && type == F_PROC)
return sql_error(sql, 02, SQLSTATE(42000) "CREATE %s:
procedures cannot have return parameters", F);
- else if (res && (type == F_FILT || type == F_LOADER))
+ else if (res && (type == F_FILT || type == F_LOADER || type ==
F_GROUPFILT))
return sql_error(sql, 02, SQLSTATE(42000) "CREATE %s: %s
functions don't have to specify a return type", F, fn);
- else if (!res && !(type == F_PROC || type == F_FILT || type ==
F_LOADER))
+ else if (!res && !(type == F_PROC || type == F_FILT || type == F_LOADER
|| type == F_GROUPFILT))
return sql_error(sql, 02, SQLSTATE(42000) "CREATE %s: %ss
require a return type", F, fn);
else if (lang == FUNC_LANG_MAL && type == F_LOADER)
return sql_error(sql, 02, SQLSTATE(42000) "CREATE %s: %s
functions creation via MAL not supported", F, fn);
@@ -938,7 +938,7 @@ rel_create_func(sql_query *query, dlist
type_list = create_type_list(sql, params, 1);
- if ((sf = sql_bind_func_(sql, s->base.name, fname, type_list, type,
true, true)) != NULL && create) {
+ if ((sf = sql_bind_func_(sql, s->base.name, fname, type_list,
type==F_GROUPFILT?F_FILT:type, true, true)) != NULL && create) {
if (sf->func->private) { /* cannot create a function using a
private name or replace a existing one */
list_destroy(type_list);
return sql_error(sql, 02, SQLSTATE(42000) "CREATE %s:
name '%s' cannot be used", F, fname);
@@ -974,9 +974,9 @@ rel_create_func(sql_query *query, dlist
sql->errstr[0] = '\0';
}
- if (create && (type == F_FUNC || type == F_AGGR || type == F_FILT)) {
+ if (create && (type == F_FUNC || type == F_AGGR || type == F_FILT ||
type == F_GROUPFILT)) {
sql_subfunc *found = NULL;
- if ((found = sql_bind_func_(sql, s->base.name, fname,
type_list, (type == F_FUNC || type == F_FILT) ? F_AGGR : F_FUNC, true, true))) {
+ if ((found = sql_bind_func_(sql, s->base.name, fname,
type_list, (type == F_FUNC || type == F_FILT || type == F_GROUPFILT) ? F_AGGR :
F_FUNC, true, true))) {
list_destroy(type_list);
if (found->func->private) /* cannot create a function
using a private name or replace a existing one */
return sql_error(sql, 02, SQLSTATE(42000)
"CREATE %s: name '%s' cannot be used", F, fname);
@@ -1251,7 +1251,7 @@ resolve_func(mvc *sql, const char *sname
e = sql_error(sql, ERR_NOTFOUND,
SQLSTATE(42000) "%s %s: no such %s '%s'", op, F, fn, name);
return e;
}
- } else if (((is_func && type != F_FILT) && !func->res) || (!is_func &&
func->res)) {
+ } else if (((is_func && type != F_FILT && type != F_GROUPFILT) &&
!func->res) || (!is_func && func->res)) {
list_destroy(list_func);
list_destroy(type_list);
return sql_error(sql, ERR_NOTFOUND, SQLSTATE(42000) "%s %s:
cannot drop %s '%s'", op, F, fn, name);
diff --git a/sql/server/sql_parser.y b/sql/server/sql_parser.y
--- a/sql/server/sql_parser.y
+++ b/sql/server/sql_parser.y
@@ -2562,6 +2562,8 @@ func_def_type_no_proc:
| AGGREGATE FUNCTION { $$ = F_AGGR; }
| FILTER { $$ = F_FILT; }
| FILTER FUNCTION { $$ = F_FILT; }
+ | sqlGROUP FILTER { $$ = F_GROUPFILT; }
+ | sqlGROUP FILTER FUNCTION { $$ = F_GROUPFILT; }
| WINDOW { $$ = F_ANALYTIC; }
| WINDOW FUNCTION { $$ = F_ANALYTIC; }
| sqlLOADER { $$ = F_LOADER; }
diff --git a/sql/storage/store.c b/sql/storage/store.c
--- a/sql/storage/store.c
+++ b/sql/storage/store.c
@@ -1004,6 +1004,10 @@ load_func(sql_trans *tr, sql_schema *s,
t->lang = (sql_flang) store->table_api.column_find_int(tr,
find_sql_column(funcs, "language"), rid);
t->instantiated = t->lang != FUNC_LANG_SQL && t->lang != FUNC_LANG_MAL;
t->type = (sql_ftype) store->table_api.column_find_int(tr,
find_sql_column(funcs, "type"), rid);
+ if (t->type == F_GROUPFILT) {
+ t->group = true;
+ t->type = F_FILT;
+ }
t->side_effect = (bool) store->table_api.column_find_bte(tr,
find_sql_column(funcs, "side_effect"), rid);
t->varres = (bool) store->table_api.column_find_bte(tr,
find_sql_column(funcs, "varres"), rid);
t->vararg = (bool) store->table_api.column_find_bte(tr,
find_sql_column(funcs, "vararg"), rid);
@@ -5359,6 +5363,10 @@ create_sql_func(sqlstore *store, allocat
t->imp = (impl)?SA_STRDUP(sa, impl):NULL;
t->mod = SA_STRDUP(sa, mod);
t->type = type;
+ if (t->type == F_GROUPFILT) {
+ t->group = true;
+ t->type = F_FILT;
+ }
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
