Changeset: 8eeddbbfc093 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=8eeddbbfc093
Added Files:
sql/test/analytics/Tests/analytics08.sql
sql/test/analytics/Tests/analytics08.stable.err
sql/test/analytics/Tests/analytics08.stable.out
Modified Files:
sql/server/rel_select.c
sql/server/sql_mvc.c
sql/server/sql_mvc.h
sql/server/sql_parser.y
sql/test/analytics/Tests/All
sql/test/analytics/Tests/analytics04.sql
sql/test/analytics/Tests/analytics04.stable.err
sql/test/analytics/Tests/analytics05.sql
sql/test/analytics/Tests/analytics05.stable.err
Branch: analytics
Log Message:
Added possibility to extend the definition of an existing window on the
projection clause (SQL standard feature).
Exemplifying query:
select first_value(col3) over (w order by col2) from t window w as (partition
by col1);
diffs (truncated from 521 to 300 lines):
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
@@ -4486,7 +4486,7 @@ generate_window_bound_call(mvc *sql, sql
*estart = exp_op(sql->sa, rargs1, dc1);
*eend = exp_op(sql->sa, rargs2, dc2);
- return e; //return something to say there were no errors b:any_1,
unit:int, excl:int, start:int
+ return e; //return something to say there were no errors
}
static sql_exp*
@@ -4551,6 +4551,47 @@ calculate_window_bound(mvc *sql, sql_rel
return res;
}
+static dlist*
+get_window_clauses(mvc *sql, char* ident, symbol **partition_by_clause, symbol
**order_by_clause, symbol **frame_clause)
+{
+ dlist *window_specification = NULL;
+ char *window_ident;
+ int pos;
+
+ if (THRhighwater())
+ return sql_error(sql, 10, SQLSTATE(42000) "SELECT: too many
nested window definitions");
+
+ if((window_specification = stack_get_window_def(sql, ident, &pos)) ==
NULL)
+ return sql_error(sql, 02, SQLSTATE(42000) "SELECT: window '%s'
not found", ident);
+
+ //avoid infinite lookups
+ if(stack_check_var_visited(sql, pos))
+ return sql_error(sql, 02, SQLSTATE(42000) "SELECT: cyclic
references to window '%s' found", ident);
+ stack_set_var_visited(sql, pos);
+
+ if(window_specification->h->next->data.sym) {
+ if(*partition_by_clause)
+ return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
redefinition of PARTITION BY clause from window '%s'", ident);
+ *partition_by_clause = window_specification->h->next->data.sym;
+ }
+ if(window_specification->h->next->next->data.sym) {
+ if(*order_by_clause)
+ return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
redefinition of ORDER BY clause from window '%s'", ident);
+ *order_by_clause =
window_specification->h->next->next->data.sym;
+ }
+ if(window_specification->h->next->next->next->data.sym) {
+ if(*frame_clause)
+ return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
redefinition of frame clause from window '%s'", ident);
+ *frame_clause =
window_specification->h->next->next->next->data.sym;
+ }
+
+ window_ident = window_specification->h->data.sval;
+ if(window_ident && !get_window_clauses(sql, window_ident,
partition_by_clause, order_by_clause, frame_clause))
+ return NULL; //the error was already set
+
+ return window_specification; //return something to say there were no
errors
+}
+
/*
* select x, y, rank_op() over (partition by x order by y) as, ...
aggr_op(z) over (partition by y order by x) as, ...
@@ -4570,32 +4611,40 @@ static sql_exp *
rel_rankop(mvc *sql, sql_rel **rel, symbol *se, int f)
{
node *n;
- dlist *l = se->data.lval;
- symbol *window_function = l->h->data.sym;
- char *aname = NULL, *sname = NULL;
+ dlist *l = se->data.lval, *window_specification = NULL;
+ symbol *window_function = l->h->data.sym, *partition_by_clause = NULL,
*order_by_clause = NULL, *frame_clause = NULL;
+ char *aname = NULL, *sname = NULL, *window_ident = 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;
dnode *dn = window_function->data.lval->h;
- int distinct = 0, project_added = 0, is_last, has_order_by, frame_type;
- dlist *window_specification = NULL;
+ int distinct = 0, project_added = 0, is_last, frame_type, pos;
bool is_nth_value, supports_frames;
+ stack_clear_frame_visited_flag(sql); //clear visited flags before
iterating
+
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);
+ if((window_specification = stack_get_window_def(sql,
window_alias, &pos)) == NULL)
+ return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
window '%s' not found", window_alias);
+ stack_set_var_visited(sql, pos);
} else {
assert(0);
}
- has_order_by = (window_specification->h->next->data.sym != NULL),
- frame_type = has_order_by ? FRAME_RANGE : FRAME_ROWS;
-
+ window_ident = window_specification->h->data.sval;
+ partition_by_clause = window_specification->h->next->data.sym;
+ order_by_clause = window_specification->h->next->next->data.sym;
+ frame_clause = window_specification->h->next->next->next->data.sym;
+
+ if(window_ident && !get_window_clauses(sql, window_ident,
&partition_by_clause, &order_by_clause, &frame_clause))
+ return NULL;
+
+ frame_type = order_by_clause ? FRAME_RANGE : FRAME_ROWS;
aname = qname_fname(dn->data.lval);
sname = qname_schema(dn->data.lval);
@@ -4629,8 +4678,8 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
reset_processed(p);
/* Partition By */
- if (window_specification->h->data.sym) {
- gbe = rel_group_by(sql, &p, window_specification->h->data.sym,
NULL /* cannot use (selection) column references, as this result is a selection
column */, f );
+ if (partition_by_clause) {
+ gbe = rel_group_by(sql, &p, partition_by_clause, NULL /* cannot
use (selection) column references, as this result is a selection column */, f );
if (!gbe)
return NULL;
for(n = gbe->h ; n ; n = n->next) {
@@ -4640,9 +4689,9 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
p->r = gbe;
}
/* Order By */
- if (has_order_by) {
+ if (order_by_clause) {
sql_rel *g;
- obe = rel_order_by(sql, &p,
window_specification->h->next->data.sym, f);
+ obe = rel_order_by(sql, &p, order_by_clause, f);
if (!obe)
return NULL;
/* conditionally? */
@@ -4802,8 +4851,8 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
}
/* Frame */
- if(window_specification->h->next->next->data.sym) {
- dnode *d =
window_specification->h->next->next->data.sym->data.lval->h;
+ if(frame_clause) {
+ dnode *d = frame_clause->data.lval->h;
symbol *wstart = d->data.sym, *wend = d->next->data.sym,
*rstart = wstart->data.lval->h->data.sym,
*rend = wend->data.lval->h->data.sym;
int excl = d->next->next->next->data.i_val;
@@ -4852,13 +4901,13 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
if(sclass == EC_POS || sclass == EC_NUM || sclass == EC_DEC ||
EC_INTERVAL(sclass)) {
fstart = exp_atom(sql->sa, atom_absolute_max(sql->sa,
exp_subtype(ie)));
- if(has_order_by)
+ if(order_by_clause)
fend = exp_atom(sql->sa,
atom_zero_value(sql->sa, exp_subtype(ie)));
else
fend = exp_atom(sql->sa,
atom_absolute_max(sql->sa, exp_subtype(ie)));
} else {
fstart = exp_atom(sql->sa, atom_absolute_max(sql->sa,
it));
- if(has_order_by)
+ if(order_by_clause)
fend = exp_atom(sql->sa,
atom_zero_value(sql->sa, it));
else
fend = exp_atom(sql->sa,
atom_absolute_max(sql->sa, it));
@@ -5565,8 +5614,8 @@ rel_query(mvc *sql, sql_rel *rel, symbol
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);
+ if(stack_get_window_def(sql, name, NULL)) {
+ return sql_error(sql, 01, SQLSTATE(42000)
"SELECT: Redefinition of window '%s'", name);
} else if(!stack_push_window_def(sql, name, wdef)) {
return sql_error(sql, 02, SQLSTATE(HY001)
MAL_MALLOC_FAIL);
}
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,6 +1549,7 @@ stack_set(mvc *sql, int var, const char
v->t = t;
v->view = view;
v->frame = frame;
+ v->visited = 0;
v->wdef = wdef;
if (type) {
int tpe = type->type->localtype;
@@ -1609,16 +1610,43 @@ stack_push_window_def(mvc *sql, const ch
}
dlist *
-stack_get_window_def(mvc *sql, const char *name)
+stack_get_window_def(mvc *sql, const char *name, int *pos)
{
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) {
+ if(pos)
+ *pos = i;
return sql->vars[i].wdef;
}
}
return NULL;
}
+/* There could a possibility that this is vulnerable to a time-of-check,
time-of-use race condition.
+ * However this should never happen in the SQL compiler */
+char
+stack_check_var_visited(mvc *sql, int i)
+{
+ if(i < 0 || i >= sql->topvars)
+ return 0;
+ return sql->vars[i].visited;
+}
+
+void
+stack_set_var_visited(mvc *sql, int i)
+{
+ if(i < 0 || i >= sql->topvars)
+ return;
+ sql->vars[i].visited = 1;
+}
+
+void
+stack_clear_frame_visited_flag(mvc *sql)
+{
+ for (int i = sql->topvars-1; i >= 0 && !sql->vars[i].frame; i--)
+ sql->vars[i].visited = 0;
+}
+
atom *
stack_set_var(mvc *sql, const char *name, ValRecord *v)
{
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
@@ -74,6 +74,7 @@ typedef struct sql_var {
dlist *wdef;
char view;
char frame;
+ char visited; //used for window definitions lookup
} sql_var;
typedef struct sql_subquery {
@@ -234,9 +235,13 @@ extern sql_var* stack_push_rel_var(mvc *
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 dlist* stack_get_window_def(mvc *sql, const char *name, int *pos);
extern void stack_update_rel_view(mvc *sql, const char *name, sql_rel *view);
+extern char stack_check_var_visited(mvc *sql, int i);
+extern void stack_set_var_visited(mvc *sql, int i);
+extern void stack_clear_frame_visited_flag(mvc *sql);
+
extern sql_var* stack_push_frame(mvc *sql, const char *name);
extern void stack_pop_frame(mvc *sql);
extern void stack_pop_until(mvc *sql, int top);
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
@@ -348,6 +348,7 @@ int yydebug=1;
XML_namespace_prefix
XML_PI_target
function_body
+ window_ident_clause
%type <l>
passwd_schema
@@ -4233,8 +4234,13 @@ window_function_type:
;
window_specification:
- window_partition_clause window_order_clause window_frame_clause
- { $$ = append_symbol(append_symbol(append_symbol(L(), $1), $2), $3); }
+ window_ident_clause window_partition_clause window_order_clause
window_frame_clause
+ { $$ = append_symbol(append_symbol(append_symbol(append_string(L(),
$1), $2), $3), $4); }
+ ;
+
+window_ident_clause:
+ /* empty */ { $$ = NULL; }
+ | ident { $$ = $1; }
;
window_partition_clause:
diff --git a/sql/test/analytics/Tests/All b/sql/test/analytics/Tests/All
--- a/sql/test/analytics/Tests/All
+++ b/sql/test/analytics/Tests/All
@@ -6,3 +6,4 @@ analytics04
analytics05
analytics06
analytics07
+analytics08
diff --git a/sql/test/analytics/Tests/analytics04.sql
b/sql/test/analytics/Tests/analytics04.sql
--- a/sql/test/analytics/Tests/analytics04.sql
+++ b/sql/test/analytics/Tests/analytics04.sql
@@ -72,5 +72,6 @@ select cast(sum(aa) over (partition by b
select count(*) over (rows between 3 following and 2 preceding) from
analytics; --error
select count(*) over (rows between current row and 2 preceding) from
analytics; --error
select count(*) over (rows between 3 following and current row) from
analytics; --error
+select count(distinct aa) over (rows between 1 preceding and current row) from
analytics; --error, distinct not implemented
drop table analytics;
diff --git a/sql/test/analytics/Tests/analytics04.stable.err
b/sql/test/analytics/Tests/analytics04.stable.err
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
