Changeset: e00638f401c6 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=e00638f401c6
Modified Files:
        sql/server/rel_rel.h
        sql/server/rel_select.c
        sql/test/analytics/Tests/analytics09.sql
        sql/test/analytics/Tests/analytics09.stable.err
        sql/test/analytics/Tests/analytics09.stable.out
Branch: Nov2019
Log Message:

Disallow nesting window functions


diffs (truncated from 373 to 300 lines):

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
@@ -21,6 +21,7 @@
 #define sql_partitionby 64 //ORed
 #define sql_aggr 128 //ORed
 #define sql_farg 256 //ORed
+#define sql_window 512 //ORed
 
 #define is_sql_from(X)    ((X & sql_from) == sql_from)
 #define is_sql_where(X)   ((X & sql_where) == sql_where)
@@ -31,6 +32,7 @@
 #define is_sql_partitionby(X) ((X & sql_partitionby) == sql_partitionby)
 #define is_sql_aggr(X)    ((X & sql_aggr) == sql_aggr)
 #define is_sql_farg(X)    ((X & sql_farg) == sql_farg)
+#define is_sql_window(X)  ((X & sql_window) == sql_window)
 
 #define rel_groupby_gbe(m,r,e) rel_groupby(m, r, append(new_exp_list(m->sa), 
e))
 #define new_rel_list(sa) sa_list(sa)
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
@@ -5341,7 +5341,7 @@ rel_rankop(sql_query *query, sql_rel **r
 
        stack_clear_frame_visited_flag(sql); //clear visited flags before 
iterating
 
-       if(l->h->next->type == type_list) {
+       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;
@@ -5357,7 +5357,7 @@ rel_rankop(sql_query *query, sql_rel **r
        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))
+       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;
@@ -5379,6 +5379,13 @@ rel_rankop(sql_query *query, sql_rel **r
                if (uaname)
                        GDKfree(uaname);
                return NULL;
+       } else if (is_sql_window(f)) {
+               char *uaname = GDKmalloc(strlen(aname) + 1);
+               (void) sql_error(sql, 02, SQLSTATE(42000) "%s: window functions 
cannot be nested",
+                                                uaname ? toUpperCopy(uaname, 
aname) : aname);
+               if (uaname)
+                       GDKfree(uaname);
+               return NULL;
        }
 
        /* 
@@ -5403,7 +5410,7 @@ rel_rankop(sql_query *query, sql_rel **r
         * op used to reset p
         */
        p = r->l;
-       if(!p || (!is_joinop(p->op) && !p->exps->h)) { //no from clause, use a 
constant as the expression to project
+       if (!p || (!is_joinop(p->op) && !p->exps->h)) { //no from clause, use a 
constant as the expression to project
                sql_exp *exp = exp_atom_lng(sql->sa, 0);
                exp_label(sql->sa, exp, ++sql->label);
                if (!p) {
@@ -5420,7 +5427,7 @@ rel_rankop(sql_query *query, sql_rel **r
        pp = p;
 
        g = p;
-       while(g && !group) {
+       while (g && !group) {
                if (g && g->op == op_groupby) {
                        group = 1;
                } else if (g->l && !is_processed(g) && !is_base(g->op)) {
@@ -5431,14 +5438,14 @@ rel_rankop(sql_query *query, sql_rel **r
        }
        /* Partition By */
        if (partition_by_clause) {
-               gbe = rel_group_by(query, &pp, partition_by_clause, NULL /* 
cannot use (selection) column references, as this result is a selection column 
*/, nf);
+               gbe = rel_group_by(query, &pp, partition_by_clause, NULL /* 
cannot use (selection) column references, as this result is a selection column 
*/, nf | sql_window);
                if (!gbe && !group) { /* try with implicit groupby */
                        /* reset error */
                        sql->session->status = 0;
                        sql->errstr[0] = '\0';
                        p = pp = rel_project(sql->sa, p, sa_list(sql->sa));
                        reset_processed(p);
-                       gbe = rel_group_by(query, &p, partition_by_clause, NULL 
/* cannot use (selection) column references, as this result is a selection 
column */, f);
+                       gbe = rel_group_by(query, &p, partition_by_clause, NULL 
/* cannot use (selection) column references, as this result is a selection 
column */, f | sql_window);
                }
                if (!gbe)
                        return NULL;
@@ -5451,7 +5458,7 @@ rel_rankop(sql_query *query, sql_rel **r
                        reset_processed(p);
                }
 
-               for(n = gbe->h ; n ; n = n->next) {
+               for (n = gbe->h ; n ; n = n->next) {
                        sql_exp *en = n->data;
 
                        n->data = en = opt_groupby_add_exp(sql, p, group?g:pp, 
en);
@@ -5461,14 +5468,14 @@ rel_rankop(sql_query *query, sql_rel **r
        }
        /* Order By */
        if (order_by_clause) {
-               obe = rel_order_by(query, &pp, order_by_clause, nf);
+               obe = rel_order_by(query, &pp, order_by_clause, nf | 
sql_window);
                if (!obe && !group && !gbe) { /* try with implicit groupby */
                        /* reset error */
                        sql->session->status = 0;
                        sql->errstr[0] = '\0';
                        p = pp = rel_project(sql->sa, p, sa_list(sql->sa));
                        reset_processed(p);
-                       obe = rel_order_by(query, &p, order_by_clause, f);
+                       obe = rel_order_by(query, &p, order_by_clause, f | 
sql_window);
                }
                if (!obe)
                        return NULL;
@@ -5481,7 +5488,7 @@ rel_rankop(sql_query *query, sql_rel **r
                        reset_processed(p);
                }
 
-               for(n = obe->h ; n ; n = n->next) {
+               for (n = obe->h ; n ; n = n->next) {
                        sql_exp *oexp = n->data, *nexp;
 
                        if (is_sql_sel(f) && pp->op == op_project && 
!is_processed(pp) && !rel_find_exp(pp, oexp)) {
@@ -5529,7 +5536,7 @@ rel_rankop(sql_query *query, sql_rel **r
                         is_lead = (strcmp(s->base.name, "sys") == 0 && 
strcmp(aname, "lead") == 0);
                int nfargs = 0;
 
-               if(!dnn || is_ntile) { //pass an input column for analytic 
functions that don't require it
+               if (!dnn || is_ntile) { //pass an input column for analytic 
functions that don't require it
                        sql_rel *lr = p;
 
                        if (!lr || !is_project(lr->op)) {
@@ -5544,11 +5551,11 @@ rel_rankop(sql_query *query, sql_rel **r
                        append(fargs, in);
                        nfargs++;
                }
-               if(dnn) {
+               if (dnn) {
                        for(dnode *nn = dnn->h ; nn ; nn = nn->next) {
                                is_last = 0;
                                exp_kind ek = {type_value, card_column, FALSE};
-                               in = rel_value_exp2(query, &p, nn->data.sym, f, 
ek, &is_last);
+                               in = rel_value_exp2(query, &p, nn->data.sym, f 
| sql_window, ek, &is_last);
                                if(!in)
                                        return NULL;
                                if(is_ntile && nfargs == 1) { //ntile first 
argument null handling case
@@ -5600,14 +5607,14 @@ rel_rankop(sql_query *query, sql_rel **r
                                 * all aggregations implemented in a window 
have 1 and only 1 argument only, so for now no further
                                 * symbol compilation is required
                                 */
-                               in = rel_value_exp2(query, &p, 
n->next->data.sym, f, ek, &is_last);
+                               in = rel_value_exp2(query, &p, 
n->next->data.sym, f | sql_window, ek, &is_last);
                                if (!in && !group && !obe && !gbe) { /* try 
with implicit groupby */
                                        /* reset error */
                                        sql->session->status = 0;
                                        sql->errstr[0] = '\0';
                                        p = rel_project(sql->sa, lop, 
sa_list(sql->sa));
                                        reset_processed(p);
-                                       in = rel_value_exp2(query, &p, 
n->next->data.sym, f, ek, &is_last);
+                                       in = rel_value_exp2(query, &p, 
n->next->data.sym, f | sql_window, ek, &is_last);
                                }
                                if(!in)
                                        return NULL;
@@ -5684,7 +5691,7 @@ rel_rankop(sql_query *query, sql_rel **r
        }
 
        /* Frame */
-       if(frame_clause) {
+       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;
@@ -5720,9 +5727,9 @@ rel_rankop(sql_query *query, sql_rel **r
                        frame_type = FRAME_ALL; //special case, iterate the 
entire partition
                }
 
-               if((fstart = calculate_window_bound(query, p, wstart->token, 
rstart, ie, frame_type, f)) == NULL)
+               if((fstart = calculate_window_bound(query, p, wstart->token, 
rstart, ie, frame_type, f | sql_window)) == NULL)
                        return NULL;
-               if((fend = calculate_window_bound(query, p, wend->token, rend, 
ie, frame_type, f)) == NULL)
+               if((fend = calculate_window_bound(query, p, wend->token, rend, 
ie, frame_type, f | sql_window)) == NULL)
                        return NULL;
                if(generate_window_bound_call(sql, &start, &eend, s, gbe ? pe : 
NULL, ie, fstart, fend, frame_type, excl,
                                                                          
wstart->token, wend->token) == NULL)
@@ -5758,7 +5765,7 @@ rel_rankop(sql_query *query, sql_rel **r
        if (!pe || !oe)
                return NULL;
 
-       if(!supports_frames) {
+       if (!supports_frames) {
                append(fargs, pe);
                append(fargs, oe);
        }
diff --git a/sql/test/analytics/Tests/analytics09.sql 
b/sql/test/analytics/Tests/analytics09.sql
--- a/sql/test/analytics/Tests/analytics09.sql
+++ b/sql/test/analytics/Tests/analytics09.sql
@@ -72,6 +72,8 @@ select cast(sum(aa) * 100 / sum(sum(aa))
 
 select cast(prod(sum(aa)) * count(1 + aa) / avg(null) over () as bigint) from 
analytics; --error, nesting aggregation functions
 
+select avg(sum(aa) over ()) over () from analytics; --error, nesting window 
functions
+
 select rank() over (partition by case when aa > 5 then aa else aa + 5 end) 
from analytics;
 
 select rank() over (partition by sum(aa)) from analytics;
diff --git a/sql/test/analytics/Tests/analytics09.stable.err 
b/sql/test/analytics/Tests/analytics09.stable.err
--- a/sql/test/analytics/Tests/analytics09.stable.err
+++ b/sql/test/analytics/Tests/analytics09.stable.err
@@ -36,7 +36,11 @@ MAPI  = (monetdb) /var/tmp/mtest-1389/.s
 QUERY = select cast(prod(sum(aa)) * count(1 + aa) / avg(null) over () as 
bigint) from analytics; --error, nesting aggregation functions
 ERROR = !SUM: aggregate functions cannot be nested
 CODE  = 42000
-MAPI  = (monetdb) /var/tmp/mtest-17541/.s.monetdb.31437
+MAPI  = (monetdb) /var/tmp/mtest-13033/.s.monetdb.39583
+QUERY = select avg(sum(aa) over ()) over () from analytics; --error, nesting 
window functions
+ERROR = !SUM: window functions cannot be nested
+CODE  = 42000
+MAPI  = (monetdb) /var/tmp/mtest-13033/.s.monetdb.39583
 QUERY = select rank() over (partition by sum(aa), bb) from analytics group by 
aa; --error
 ERROR = !SELECT: cannot use non GROUP BY column 'bb' in query results without 
an aggregate function
 CODE  = 42000
diff --git a/sql/test/analytics/Tests/analytics09.stable.out 
b/sql/test/analytics/Tests/analytics09.stable.out
--- a/sql/test/analytics/Tests/analytics09.stable.out
+++ b/sql/test/analytics/Tests/analytics09.stable.out
@@ -27,14 +27,14 @@ stdout of test 'analytics09` in director
 #insert into analytics values (15, 3), (3, 1), (2, 1), (5, 3), (NULL, 2), (3, 
2), (4, 1), (6, 3), (8, 2), (NULL, 4);
 [ 10   ]
 #select cast(sum(1) over () as bigint), rank() over (), nth_value(1, 1) over 
();
-% .L5, .L10,   .L14 # table_name
-% L5,  L10,    L14 # name
+% .L5, .L7,    .L12 # table_name
+% L5,  L7,     L12 # name
 % bigint,      int,    tinyint # type
 % 1,   1,      1 # length
 [ 1,   1,      1       ]
 #select avg(sum(aa) over ()) from analytics;
-% sys.L7 # table_name
-% L7 # name
+% sys.L6 # table_name
+% L6 # name
 % double # type
 % 24 # length
 [ 46   ]
@@ -66,14 +66,14 @@ stdout of test 'analytics09` in director
 [ 80   ]
 [ NULL ]
 #select cast(sum(aa) * count(1) over () as bigint) from analytics;
-% sys.L6 # table_name
-% L6 # name
+% sys.L7 # table_name
+% L7 # name
 % bigint # type
 % 2 # length
 [ 46   ]
 #select cast(sum(aa) * count(1 + aa) / avg(1) over () as bigint) from 
analytics;
-% sys.L7 # table_name
-% L7 # name
+% sys.L10 # table_name
+% L10 # name
 % bigint # type
 % 3 # length
 [ 368  ]
@@ -153,8 +153,8 @@ stdout of test 'analytics09` in director
 % 24,  3,      24,     24 # length
 [ 46,  460,    46,     46      ]
 #select avg(sum(aa)) over () from analytics group by aa;
-% sys.L2 # table_name
-% L2 # name
+% sys.L3 # table_name
+% L3 # name
 % double # type
 % 24 # length
 [ 6.571428571  ]
@@ -166,8 +166,8 @@ stdout of test 'analytics09` in director
 [ 6.571428571  ]
 [ 6.571428571  ]
 #select cast(sum(aa) * count(aa) / avg(aa) over (rows between current row and 
unbounded following) as bigint) from analytics group by aa;
-% sys.L5 # table_name
-% L5 # name
+% sys.L6 # table_name
+% L6 # name
 % bigint # type
 % 1 # length
 [ 2    ]
@@ -182,8 +182,8 @@ stdout of test 'analytics09` in director
 #       avg(sum(aa)) over (rows unbounded preceding),
 #       cast(sum(aa) * count(aa) / avg(aa) over (rows between current row and 
unbounded following) as bigint),
 #       avg(sum(aa)) over (range unbounded preceding) from analytics group by 
aa;
-% sys.L2,      sys.L5, sys.L13,        sys.L15 # table_name
-% L2,  L5,     L13,    L15 # name
+% sys.L3,      sys.L6, sys.L14,        sys.L16 # table_name
+% L3,  L6,     L14,    L16 # name
 % double,      double, bigint, double # type
 % 24,  24,     1,      24 # length
 [ 6.571428571, 15,     2,      6.571428571     ]
@@ -196,8 +196,8 @@ stdout of test 'analytics09` in director
 [ 6.571428571, 6.571428571,    1,      6.571428571     ]
 #select cast(sum(aa) * count(aa) over () as bigint),
 #       cast(sum(aa) over () as bigint) from analytics group by aa;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list

Reply via email to