Changeset: 4b068be98071 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=4b068be98071
Added Files:
sql/test/analytics/Tests/analytics05.sql
sql/test/analytics/Tests/analytics05.stable.err
sql/test/analytics/Tests/analytics05.stable.out
Modified Files:
clients/odbc/driver/SQLGetInfo.c
sql/backends/monet5/rel_bin.c
sql/scripts/51_sys_schema_extension.sql
sql/server/rel_select.c
sql/server/rel_updates.c
sql/server/sql_mvc.c
sql/server/sql_mvc.h
sql/server/sql_parser.h
sql/server/sql_parser.y
sql/server/sql_scan.c
sql/server/sql_symbol.c
sql/server/sql_symbol.h
sql/test/analytics/Tests/All
Branch: analytics
Log Message:
Introducing window aliases. After the from clause an optional WINDOW clause
specifies the specification of the windows to be used in the projection clause
(syntax sugar).
For every select command in a single statement, a new stack frame is pushed to
store possible to be used window aliases in the select command.
diffs (truncated from 713 to 300 lines):
diff --git a/clients/odbc/driver/SQLGetInfo.c b/clients/odbc/driver/SQLGetInfo.c
--- a/clients/odbc/driver/SQLGetInfo.c
+++ b/clients/odbc/driver/SQLGetInfo.c
@@ -682,7 +682,7 @@ MNDBGetInfo(ODBCDbc *dbc,
"ROW_NUMBER,SAMPLE,SAVEPOINT,SEQUENCE,SERIAL,"
"SERIALIZABLE,SIMPLE,SPLIT_PART,STDIN,STDOUT,STREAM,"
"STRING,SYMMETRIC,TIES,TINYINT,TRIGGER,TRUNCATE,"
- "UNBOUNDED,UNCOMMITTED,UNENCRYPTED,WHILE,XMLAGG,"
+ "UNBOUNDED,UNCOMMITTED,UNENCRYPTED,WHILE,WINDOW,XMLAGG,"
"XMLATTRIBUTES,XMLCOMMENT,XMLCONCAT,XMLDOCUMENT,"
"XMLELEMENT,XMLFOREST,XMLNAMESPACES,XMLPARSE,XMLPI,"
"XMLQUERY,XMLSCHEMA,XMLTEXT,XMLVALIDATE";
diff --git a/sql/backends/monet5/rel_bin.c b/sql/backends/monet5/rel_bin.c
--- a/sql/backends/monet5/rel_bin.c
+++ b/sql/backends/monet5/rel_bin.c
@@ -504,8 +504,8 @@ exp_bin(backend *be, sql_exp *e, stmt *l
es = stmt_const(be,
bin_first_column(be, left), es);
}
/* last argument is condition, change into
candidate list */
- if (!en->next && !f->func->varres &&
!f->func->vararg && list_length(exps) > list_length(f->func->ops)) {
- if (!strcmp(f->func->base.name,
"window_following_bound") && es->nrcols) {
+ if (f->func->type != F_ANALYTIC && !en->next &&
!f->func->varres && !f->func->vararg && list_length(exps) >
list_length(f->func->ops)) {
+ if (es->nrcols) {
if (!nrcols) {
node *n;
list *nl =
sa_list(sql->sa);
@@ -518,7 +518,7 @@ exp_bin(backend *be, sql_exp *e, stmt *l
}
es = stmt_uselect(be, es,
stmt_bool(be,1), cmp_equal, NULL, 0);
- } else if (f->func->type !=
F_ANALYTIC)/* need a condition */
+ } else /* need a condition */
cond_execution = es;
}
if (es->nrcols > nrcols)
diff --git a/sql/scripts/51_sys_schema_extension.sql
b/sql/scripts/51_sys_schema_extension.sql
--- a/sql/scripts/51_sys_schema_extension.sql
+++ b/sql/scripts/51_sys_schema_extension.sql
@@ -215,6 +215,7 @@ INSERT INTO sys.keywords (keyword) VALUE
('WHEN'),
('WHERE'),
('WHILE'),
+ ('WINDOW'),
('WITH'),
('WORK'),
('WRITE'),
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
@@ -4504,17 +4504,28 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
node *n;
dlist *l = se->data.lval;
symbol *window_function = l->h->data.sym;
- dlist *window_specification = l->h->next->data.lval;
char *aname = NULL, *sname = NULL;
sql_subfunc *wf = NULL;
sql_exp *in = NULL, *pe = NULL, *oe = NULL, *call = NULL, *start =
NULL, *eend = NULL, *fstart = NULL, *fend = NULL;
sql_rel *r = *rel, *p;
list *gbe = NULL, *obe = NULL, *args = NULL, *types = NULL, *fargs =
NULL;
sql_schema *s = sql->session->schema;
- int distinct = 0, project_added = 0, aggr = (window_function->token !=
SQL_RANK), is_last,
- has_order_by = (window_specification->h->next->data.sym !=
NULL),
- frame_type = has_order_by ? FRAME_RANGE : FRAME_ROWS;
dnode *dn = window_function->data.lval->h;
+ int distinct = 0, project_added = 0, aggr = (window_function->token !=
SQL_RANK), is_last, has_order_by, frame_type;
+ dlist *window_specification = NULL;
+
+ if(l->h->next->type == type_list) {
+ window_specification = l->h->next->data.lval;
+ } else if (l->h->next->type == type_string) {
+ const char* window_alias = l->h->next->data.sval;
+ if((window_specification = stack_get_window_def(sql,
window_alias)) == NULL)
+ return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
window '%s' not found on WINDOW specification list", window_alias);
+ } else {
+ assert(0);
+ }
+
+ has_order_by = (window_specification->h->next->data.sym != NULL),
+ frame_type = has_order_by ? FRAME_RANGE : FRAME_ROWS;
aname = qname_fname(dn->data.lval);
sname = qname_schema(dn->data.lval);
@@ -4655,7 +4666,8 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
}
}
}
- (void)distinct;
+ if(distinct)
+ return sql_error(sql, 02, SQLSTATE(42000) "SELECT: DISTINCT
clause is not implemented for window functions");
/* diff for partitions */
if (gbe) {
@@ -5456,6 +5468,20 @@ rel_query(mvc *sql, sql_rel *rel, symbol
if (ek.card != card_relation && sn->orderby)
return sql_error(sql, 01, SQLSTATE(42000) "SELECT: ORDER BY
only allowed on outermost SELECT");
+ if (sn->window) {
+ dlist *wl = sn->window->data.lval;
+ for (dnode *n = wl->h; n ; n = n->next) {
+ dlist *wd = n->data.sym->data.lval;
+ const char *name = wd->h->data.sval;
+ dlist *wdef = wd->h->next->data.lval;
+ if(stack_get_window_def(sql, name)) {
+ return sql_error(sql, 01, SQLSTATE(42000)
"SELECT: Duplicated definition of window '%s'", name);
+ } else if(!stack_push_window_def(sql, name, wdef)) {
+ return sql_error(sql, 02, SQLSTATE(HY001)
MAL_MALLOC_FAIL);
+ }
+ }
+ }
+
sql->use_views = 1;
if (sn->from) { /* keep variable list with tables and names */
dlist *fl = sn->from->data.lval;
@@ -5929,14 +5955,19 @@ rel_selects(mvc *sql, symbol *s)
break;
case SQL_SELECT: {
exp_kind ek = {type_value, card_relation, TRUE};
- SelectNode *sn = (SelectNode *) s;
+ SelectNode *sn = (SelectNode *) s;
+
+ if(!stack_push_frame(sql, "SELECT"))
+ return sql_error(sql, 02, SQLSTATE(HY001)
MAL_MALLOC_FAIL);
if (sn->into) {
sql->type = Q_SCHEMA;
- return rel_select_with_into(sql, s);
+ ret = rel_select_with_into(sql, s);
+ } else {
+ ret = rel_subquery(sql, NULL, s, ek, APPLY_JOIN);
+ sql->type = Q_TABLE;
}
- ret = rel_subquery(sql, NULL, s, ek, APPLY_JOIN);
- sql->type = Q_TABLE;
+ stack_pop_frame(sql);
} break;
case SQL_JOIN:
ret = rel_joinquery(sql, NULL, s);
diff --git a/sql/server/rel_updates.c b/sql/server/rel_updates.c
--- a/sql/server/rel_updates.c
+++ b/sql/server/rel_updates.c
@@ -967,7 +967,7 @@ update_table(mvc *sql, dlist *qname, dli
dlist_append_symbol(sql->sa, selection, a);
}
- sym = newSelectNode(sql->sa, 0, selection, NULL,
symbol_create_list(sql->sa, SQL_FROM, from_list), opt_where, NULL, NULL, NULL,
NULL, NULL, NULL, NULL);
+ sym = newSelectNode(sql->sa, 0, selection, NULL,
symbol_create_list(sql->sa, SQL_FROM, from_list), opt_where, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL);
sq = rel_selects(sql, sym);
if (sq)
sq = rel_optimizer(sql, sq, 0);
diff --git a/sql/server/sql_mvc.c b/sql/server/sql_mvc.c
--- a/sql/server/sql_mvc.c
+++ b/sql/server/sql_mvc.c
@@ -1549,7 +1549,7 @@ mvc_is_sorted(mvc *m, sql_column *col)
/* variable management */
static sql_var*
-stack_set(mvc *sql, int var, const char *name, sql_subtype *type, sql_rel
*rel, sql_table *t, int view, int frame)
+stack_set(mvc *sql, int var, const char *name, sql_subtype *type, sql_rel
*rel, sql_table *t, dlist *wdef, int view, int frame)
{
sql_var *v, *nvars;
int nextsize = sql->sizevars;
@@ -1571,6 +1571,7 @@ stack_set(mvc *sql, int var, const char
v->t = t;
v->view = view;
v->frame = frame;
+ v->wdef = wdef;
if (type) {
int tpe = type->type->localtype;
VALset(&sql->vars[var].a.data, tpe, (ptr) ATOMnilptr(tpe));
@@ -1587,7 +1588,7 @@ stack_set(mvc *sql, int var, const char
sql_var*
stack_push_var(mvc *sql, const char *name, sql_subtype *type)
{
- sql_var* res = stack_set(sql, sql->topvars, name, type, NULL, NULL, 0,
0);
+ sql_var* res = stack_set(sql, sql->topvars, name, type, NULL, NULL,
NULL, 0, 0);
if(res)
sql->topvars++;
return res;
@@ -1596,7 +1597,7 @@ stack_push_var(mvc *sql, const char *nam
sql_var*
stack_push_rel_var(mvc *sql, const char *name, sql_rel *var, sql_subtype *type)
{
- sql_var* res = stack_set(sql, sql->topvars, name, type, var, NULL, 0,
0);
+ sql_var* res = stack_set(sql, sql->topvars, name, type, var, NULL,
NULL, 0, 0);
if(res)
sql->topvars++;
return res;
@@ -1605,7 +1606,7 @@ stack_push_rel_var(mvc *sql, const char
sql_var*
stack_push_table(mvc *sql, const char *name, sql_rel *var, sql_table *t)
{
- sql_var* res = stack_set(sql, sql->topvars, name, NULL, var, t, 0, 0);
+ sql_var* res = stack_set(sql, sql->topvars, name, NULL, var, t, NULL,
0, 0);
if(res)
sql->topvars++;
return res;
@@ -1614,12 +1615,32 @@ stack_push_table(mvc *sql, const char *n
sql_var*
stack_push_rel_view(mvc *sql, const char *name, sql_rel *var)
{
- sql_var* res = stack_set(sql, sql->topvars, name, NULL, var, NULL, 1,
0);
+ sql_var* res = stack_set(sql, sql->topvars, name, NULL, var, NULL,
NULL, 1, 0);
if(res)
sql->topvars++;
return res;
}
+sql_var*
+stack_push_window_def(mvc *sql, const char *name, dlist *wdef)
+{
+ sql_var* res = stack_set(sql, sql->topvars, name, NULL, NULL, NULL,
wdef, 0, 0);
+ if(res)
+ sql->topvars++;
+ return res;
+}
+
+dlist *
+stack_get_window_def(mvc *sql, const char *name)
+{
+ for (int i = sql->topvars-1; i >= 0; i--) {
+ if (!sql->vars[i].frame && sql->vars[i].wdef &&
strcmp(sql->vars[i].name, name)==0) {
+ return sql->vars[i].wdef;
+ }
+ }
+ return NULL;
+}
+
atom *
stack_set_var(mvc *sql, const char *name, ValRecord *v)
{
@@ -1658,7 +1679,7 @@ stack_get_var(mvc *sql, const char *name
sql_var*
stack_push_frame(mvc *sql, const char *name)
{
- sql_var* res = stack_set(sql, sql->topvars, name, NULL, NULL, NULL, 0,
1);
+ sql_var* res = stack_set(sql, sql->topvars, name, NULL, NULL, NULL,
NULL, 0, 1);
if(res) {
sql->topvars++;
sql->frame++;
@@ -1675,6 +1696,7 @@ stack_pop_until(mvc *sql, int top)
c_delete(v->name);
VALclear(&v->a.data);
v->a.data.vtype = 0;
+ v->wdef = NULL;
}
}
@@ -1687,10 +1709,11 @@ stack_pop_frame(mvc *sql)
c_delete(v->name);
VALclear(&v->a.data);
v->a.data.vtype = 0;
- if (v->t && v->view)
+ if (v->t && v->view)
table_destroy(v->t);
else if (v->rel)
rel_destroy(v->rel);
+ v->wdef = NULL;
}
if (sql->topvars && sql->vars[sql->topvars].name)
c_delete(sql->vars[sql->topvars].name);
@@ -1823,7 +1846,7 @@ stack_nr_of_declared_tables(mvc *sql)
for (i = sql->topvars-1; i >= 0; i--) {
if (sql->vars[i].rel && !sql->vars[i].view) {
sql_var *v = &sql->vars[i];
- if (v->t)
+ if (v->t)
dt++;
}
}
diff --git a/sql/server/sql_mvc.h b/sql/server/sql_mvc.h
--- a/sql/server/sql_mvc.h
+++ b/sql/server/sql_mvc.h
@@ -22,6 +22,7 @@
#include "sql_keyword.h"
#include "sql_atom.h"
#include "sql_query.h"
+#include "sql_symbol.h"
#define ERRSIZE 8192
@@ -69,7 +70,8 @@ typedef struct sql_var {
const char *name;
atom a;
sql_table *t;
- sql_rel *rel;
+ sql_rel *rel;
+ dlist *wdef;
char view;
char frame;
} sql_var;
@@ -230,6 +232,8 @@ extern sql_var* stack_push_var(mvc *sql,
extern sql_var* stack_push_rel_var(mvc *sql, const char *name, sql_rel *var,
sql_subtype *type);
extern sql_var* stack_push_table(mvc *sql, const char *name, sql_rel *var,
sql_table *t);
extern sql_var* stack_push_rel_view(mvc *sql, const char *name, sql_rel *view);
+extern sql_var* stack_push_window_def(mvc *sql, const char *name, dlist *sym);
+extern dlist* stack_get_window_def(mvc *sql, const char *name);
extern void stack_update_rel_view(mvc *sql, const char *name, sql_rel *view);
extern sql_var* stack_push_frame(mvc *sql, const char *name);
diff --git a/sql/server/sql_parser.h b/sql/server/sql_parser.h
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
