Changeset: 280cb9553f4f for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=280cb9553f4f
Modified Files:
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:
Allow window functions in order by clause
diffs (85 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
@@ -5371,7 +5371,7 @@ rel_rankop(sql_query *query, sql_rel **r
supports_frames = (window_function->token != SQL_RANK) || is_nth_value
||
(strcmp(s->base.name, "sys") == 0 &&
((strcmp(aname, "first_value") == 0) || strcmp(aname, "last_value") == 0));
- if (is_sql_where(f) || is_sql_groupby(f) || is_sql_having(f) ||
is_sql_orderby(f) || is_sql_partitionby(f)) {
+ if (is_sql_where(f) || is_sql_groupby(f) || is_sql_having(f) ||
is_sql_partitionby(f)) {
char *uaname = GDKmalloc(strlen(aname) + 1);
const char *clause =
is_sql_where(f)?"WHERE":is_sql_groupby(f)?"GROUP
BY":is_sql_having(f)?"HAVING":is_sql_orderby(f)?"ORDER BY":"PARTITION BY";
(void) sql_error(sql, 02, SQLSTATE(42000) "%s: window function
'%s' not allowed in %s clause",
@@ -5400,7 +5400,7 @@ rel_rankop(sql_query *query, sql_rel **r
reset_processed(r);
project_added = 1;
}
- if (!is_sql_sel(f) || !r || r->op != op_project || is_processed(r))
+ if (!is_sql_sel(f) || !r || !is_project(r->op))
return sql_error(sql, 02, SQLSTATE(42000) "OVER: only possible
within the selection");
/* outer project (*rel) r r->l will be rewritten!
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
@@ -74,6 +74,8 @@ select cast(prod(sum(aa)) * count(1 + aa
select avg(sum(aa) over ()) over () from analytics; --error, nesting window
functions
+select avg(aa) over (partition by sum(aa) over ()) from analytics; --error,
window function in partition by
+
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;
@@ -109,4 +111,8 @@ select dense_rank() over (partition by s
select avg(sum(aa)) over (rows unbounded preceding),
rank() over (partition by sum(aa)) from analytics;
+select 1 from analytics order by sum(sum(aa)) over ();
+
+select 1 from analytics having sum(aa) over (); --error, window function not
allowed in having clause
+
drop table 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
@@ -40,7 +40,11 @@ MAPI = (monetdb) /var/tmp/mtest-13033/.
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
+MAPI = (monetdb) /var/tmp/mtest-31020/.s.monetdb.34916
+QUERY = select avg(aa) over (partition by sum(aa) over ()) from analytics;
--error, window function in partition by
+ERROR = !SUM: window functions cannot be nested
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-31020/.s.monetdb.34916
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
@@ -60,6 +64,10 @@ MAPI = (monetdb) /var/tmp/mtest-9623/.s
QUERY = select min(aa) over (order by sum(bb)) from analytics; --error
ERROR = !SELECT: cannot use non GROUP BY column 'aa' in query results without
an aggregate function
CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-4073/.s.monetdb.36366
+QUERY = select 1 from analytics having sum(aa) over (); --error, window
function not allowed in having clause
+ERROR = !SUM: window function 'sum' not allowed in HAVING clause
+CODE = 42000
# 13:13:17 >
# 13:13:17 > "Done."
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
@@ -389,6 +389,12 @@ stdout of test 'analytics09` in director
% double, int # type
% 24, 1 # length
[ 46, 1 ]
+#select 1 from analytics order by sum(sum(aa)) over ();
+% .L1 # table_name
+% L1 # name
+% tinyint # type
+% 1 # length
+[ 1 ]
#drop table analytics;
# 13:13:17 >
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
