Changeset: 23c953f8e7ba for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=23c953f8e7ba
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: default
Log Message:
Merge with Nov2019
diffs (295 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
@@ -4510,7 +4510,7 @@ rel_rankop(sql_query *query, sql_rel **r
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 aggregates",
+ (void) sql_error(sql, 02, SQLSTATE(42000) "%s: window functions
not allowed inside aggregation functions",
uaname ? toUpperCopy(uaname,
aname) : aname);
if (uaname)
GDKfree(uaname);
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,6 +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 ()) over () from analytics; --error, window functions
inside aggregate
+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 aggregation functions
+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 aggregation functions
+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
@@ -27,31 +27,25 @@ 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
();
-% .%1, .%3, .%4 # table_name
-% %1, %3, %4 # name
+% ., ., . # table_name
+% %1, %2, %3 # name
% bigint, int, tinyint # type
% 1, 1, 1 # length
[ 1, 1, 1 ]
-#select avg(sum(aa) over ()) from analytics;
-% sys.%1 # table_name
-% %1 # name
-% double # type
-% 24 # length
-[ 46 ]
#select cast(sum(1) * count(*) over () as bigint);
-% .%3 # table_name
+% . # table_name
% %3 # name
% bigint # type
% 1 # length
[ 1 ]
#select cast(sum(aa) * count(*) over () as bigint) from analytics;
-% sys.%3 # table_name
+% sys. # table_name
% %3 # name
% bigint # type
% 2 # length
[ 46 ]
#select cast(aa * count(1) over () as bigint) from analytics;
-% sys.%1 # table_name
+% sys. # table_name
% %1 # name
% bigint # type
% 3 # length
@@ -66,26 +60,26 @@ stdout of test 'analytics09` in director
[ 80 ]
[ NULL ]
#select cast(sum(aa) * count(1) over () as bigint) from analytics;
-% sys.%2 # table_name
+% sys. # table_name
% %2 # name
% bigint # type
% 2 # length
[ 46 ]
#select cast(sum(aa) * count(1 + aa) / avg(1) over () as bigint) from
analytics;
-% sys.%3 # table_name
-% %3 # name
+% sys. # table_name
+% %5 # name
% bigint # type
% 3 # length
[ 368 ]
#select avg(sum(aa)) over () from analytics;
-% sys.%2 # table_name
+% sys. # table_name
% %2 # name
% double # type
% 24 # length
[ 46 ]
#select sum(cast(aa as double)) over (rows unbounded preceding) from analytics;
-% sys.%2 # table_name
-% %2 # name
+% sys. # table_name
+% %3 # name
% double # type
% 24 # length
[ 15 ]
@@ -99,8 +93,8 @@ stdout of test 'analytics09` in director
[ 46 ]
[ 46 ]
#select sum(cast(aa as double)) over (range unbounded preceding) from
analytics;
-% sys.%2 # table_name
-% %2 # name
+% sys. # table_name
+% %3 # name
% double # type
% 24 # length
[ 46 ]
@@ -114,31 +108,31 @@ stdout of test 'analytics09` in director
[ 46 ]
[ 46 ]
#select avg(avg(aa)) over (rows unbounded preceding) from analytics;
-% sys.%2 # table_name
+% sys. # table_name
% %2 # name
% double # type
% 24 # length
[ 5.75 ]
#select avg(avg(aa)) over (range unbounded preceding) from analytics;
-% sys.%2 # table_name
+% sys. # table_name
% %2 # name
% double # type
% 24 # length
[ 5.75 ]
#select avg(sum(aa)) over (rows unbounded preceding) from analytics;
-% sys.%2 # table_name
+% sys. # table_name
% %2 # name
% double # type
% 24 # length
[ 46 ]
#select avg(sum(aa)) over (range unbounded preceding) from analytics;
-% sys.%2 # table_name
+% sys. # table_name
% %2 # name
% double # type
% 24 # length
[ 46 ]
#select avg(sum(aa)) over (), avg(avg(aa)) over () from analytics;
-% sys.%3, sys.%4 # table_name
+% sys., sys. # table_name
% %3, %4 # name
% double, double # type
% 24, 24 # length
@@ -147,13 +141,13 @@ stdout of test 'analytics09` in director
# cast(sum(aa) * count(case when bb < 2 then bb - 1 else bb + 1 end) /
avg(1) over (rows between current row and current row) as bigint),
# avg(sum(aa)) over (rows unbounded preceding),
# avg(sum(aa)) over (range unbounded preceding) from analytics;
-% sys.%10, sys.%4, sys.%12, sys.%13 # table_name
-% %10, %4, %12, %13 # name
+% sys., sys., sys., sys. # table_name
+% %7, %10, %11, %12 # name
% double, bigint, double, double # type
% 24, 3, 24, 24 # length
[ 46, 460, 46, 46 ]
#select avg(sum(aa)) over () from analytics group by aa;
-% sys.%2 # table_name
+% sys. # table_name
% %2 # name
% double # type
% 24 # length
@@ -166,7 +160,7 @@ 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.%3 # table_name
+% sys. # table_name
% %3 # name
% bigint # type
% 1 # length
@@ -182,8 +176,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.%7, sys.%10, sys.%5, sys.%12 # table_name
-% %7, %10, %5, %12 # name
+% sys., sys., sys., sys. # table_name
+% %6, %7, %10, %11 # name
% double, double, bigint, double # type
% 24, 24, 1, 24 # length
[ 6.571428571, 15, 2, 6.571428571 ]
@@ -196,7 +190,7 @@ 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;
-% sys.%2, sys.%3 # table_name
+% sys., sys. # table_name
% %2, %3 # name
% bigint, bigint # type
% 3, 2 # length
@@ -210,8 +204,8 @@ stdout of test 'analytics09` in director
[ 56, 43 ]
#select cast(sum(sum(aa)) over () as bigint),
# cast(sum(aa) * count(count(aa)) over () as bigint) from analytics
group by aa;
-% sys.%2, sys.%5 # table_name
-% %2, %5 # name
+% sys., sys. # table_name
+% %4, %5 # name
% bigint, bigint # type
% 2, 3 # length
[ 46, 120 ]
@@ -224,7 +218,7 @@ stdout of test 'analytics09` in director
[ 46, 64 ]
#select count(aa) over (),
# avg(aa) over () * count(aa) from analytics group by aa;
-% sys.%2, sys.%3 # table_name
+% sys., sys. # table_name
% %2, %3 # name
% bigint, double # type
% 1, 24 # length
@@ -239,8 +233,8 @@ stdout of test 'analytics09` in director
#select cast(sum(aa) over () as bigint),
# cast(sum(aa) over () as bigint),
# cast(sum(aa) * count(aa) over () as bigint) from analytics group by aa;
-% sys.%1, sys.%2, sys.%4 # table_name
-% %1, %2, %4 # name
+% sys., sys., sys. # table_name
+% %2, %3, %4 # name
% bigint, bigint, bigint # type
% 2, 2, 3 # length
[ 43, 43, 105 ]
@@ -254,8 +248,8 @@ stdout of test 'analytics09` in director
#select 21 - avg(sum(aa)) over (),
# avg(45 * count(aa) + sum(aa)) over (),
# cast(sum(aa) * count(aa) over () as bigint) from analytics group by aa;
-% .%6, sys.%7, sys.%5 # table_name
-% %6, %7, %5 # name
+% ., sys., sys. # table_name
+% %10, %11, %12 # name
% double, double, bigint # type
% 24, 24, 3 # length
[ 14.42857143, 58, 105 ]
@@ -276,14 +270,14 @@ stdout of test 'analytics09` in director
[ 26 ]
[ NULL ]
#select cast(sum(aa) * 100 / sum(sum(aa)) over () as bigint) from analytics;
-% sys.%3 # table_name
+% sys. # table_name
% %3 # name
% bigint # type
% 3 # length
[ 100 ]
#select cast(sum(aa) * 100 / sum(sum(aa)) over (partition by bb) as bigint)
from analytics group by bb;
-% sys.%3 # table_name
-% %3 # name
+% sys. # table_name
+% %6 # name
% bigint # type
% 3 # length
[ 100 ]
@@ -384,7 +378,7 @@ stdout of test 'analytics09` in director
[ 1 ]
#select avg(sum(aa)) over (rows unbounded preceding),
# rank() over (partition by sum(aa)) from analytics;
-% sys.%7, sys.%6 # table_name
+% sys., sys.%6 # table_name
% %7, %6 # name
% double, int # type
% 24, 1 # length
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
