Changeset: 2adce15773af for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2adce15773af
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:
Window functions are not allowed inside aggregates. I committed on the wrong
branch.
diffs (79 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
@@ -5251,6 +5251,13 @@ rel_rankop(sql_query *query, sql_rel **r
if (uaname)
GDKfree(uaname);
return NULL;
+ } else if (is_sql_aggr(f)) {
+ char *uaname = GDKmalloc(strlen(aname) + 1);
+ (void) sql_error(sql, 02, SQLSTATE(42000) "%s: window functions
not allowed inside aggregation functions",
+ uaname ? toUpperCopy(uaname,
aname) : aname);
+ 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",
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
@@ -3,7 +3,7 @@ insert into analytics values (15, 3), (3
select cast(sum(1) over () as bigint), rank() over (), nth_value(1, 1) over ();
-select avg(sum(aa) over ()) from analytics;
+select avg(sum(aa) over ()) from analytics; --error, window functions not
allowed inside aggregates
select cast(sum(1) * count(*) over () as bigint);
@@ -115,4 +115,6 @@ select 1 from analytics order by sum(sum
select 1 from analytics having sum(aa) over (); --error, window function not
allowed in having clause
+select sum(avg(aa) over ()) from analytics; --error, window functions not
allowed inside aggregates
+
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
@@ -28,7 +28,11 @@ stderr of test 'analytics09` in director
# 13:13:17 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-1389" "--port=37694"
# 13:13:17 >
-MAPI = (monetdb) /var/tmp/mtest-17924/.s.monetdb.36475
+MAPI = (monetdb) /var/tmp/mtest-155309/.s.monetdb.35365
+QUERY = select avg(sum(aa) over ()) from analytics; --error, window functions
not allowed inside aggregates
+ERROR = !SUM: window functions not allowed inside aggregates
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-155309/.s.monetdb.35365
QUERY = select cast(sum(aa) * 100 / sum(sum(aa)) over (partition by bb) as
bigint) from analytics; --error, nesting aggregation functions
ERROR = !SELECT: cannot use non GROUP BY column 'bb' in query results without
an aggregate function
CODE = 42000
@@ -68,6 +72,10 @@ MAPI = (monetdb) /var/tmp/mtest-4073/.s
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
+MAPI = (monetdb) /var/tmp/mtest-155309/.s.monetdb.35365
+QUERY = select sum(avg(aa) over ()) from analytics; --error, window functions
not allowed inside aggregates
+ERROR = !AVG: window functions not allowed inside aggregates
+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
@@ -32,12 +32,6 @@ stdout of test 'analytics09` in director
% bigint, int, tinyint # type
% 1, 1, 1 # length
[ 1, 1, 1 ]
-#select avg(sum(aa) over ()) from analytics;
-% sys.L6 # table_name
-% L6 # name
-% double # type
-% 24 # length
-[ 46 ]
#select cast(sum(1) * count(*) over () as bigint);
% .L7 # table_name
% L7 # name
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
