Changeset: a461dd30c634 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=a461dd30c634
Modified Files:
sql/test/analytics/Tests/analytics02.sql
sql/test/analytics/Tests/analytics02.stable.err
sql/test/analytics/Tests/analytics02.stable.out
Branch: analytics
Log Message:
Approved test.
diffs (truncated from 544 to 300 lines):
diff --git a/sql/test/analytics/Tests/analytics02.sql
b/sql/test/analytics/Tests/analytics02.sql
--- a/sql/test/analytics/Tests/analytics02.sql
+++ b/sql/test/analytics/Tests/analytics02.sql
@@ -1,6 +1,6 @@
-start transaction;
+create table analytics (aa int, bb int, cc bigint);
-create table analytics (aa int, bb int, cc bigint);
+start transaction;
insert into analytics values (15, 3, 15), (3, 1, 3), (2, 1, 2), (5, 3, 5),
(NULL, 2, NULL), (3, 2, 3), (4, 1, 4), (6, 3, 6), (8, 2, 8), (NULL, 4, NULL);
select cast(sum(aa) over (rows between 5 preceding and 0 following) as bigint)
from analytics;
@@ -60,8 +60,46 @@ select count(aa) over (partition by bb o
select count(bb) over (rows between unbounded preceding and unbounded
following) from analytics;
select count(bb) over (partition by aa order by aa rows between unbounded
preceding and unbounded following) from analytics;
+select count(*) over (rows unbounded preceding);
+select count(*) over (rows 200 preceding);
select count(*) over (rows between 5 preceding and 0 following);
+select min(aa) over (rows unbounded preceding) from analytics;
+select min(aa) over (partition by bb order by bb rows unbounded preceding)
from analytics;
+select max(aa) over (rows unbounded preceding) from analytics;
+select max(aa) over (partition by bb order by bb rows unbounded preceding)
from analytics;
+
+select cast(sum(aa) over (rows unbounded preceding) as bigint) from analytics;
+select cast(sum(aa) over (partition by bb order by bb rows unbounded
preceding) as bigint) from analytics;
+select cast(prod(aa) over (rows unbounded preceding) as bigint) from analytics;
+select cast(prod(aa) over (partition by bb order by bb rows unbounded
preceding) as bigint) from analytics;
+
+select avg(aa) over (rows unbounded preceding) from analytics;
+select avg(aa) over (partition by bb order by bb rows unbounded preceding)
from analytics;
+
+select count(*) over (rows unbounded preceding) from analytics;
+select count(*) over (partition by bb order by bb rows unbounded preceding)
from analytics;
+select count(aa) over (rows unbounded preceding) from analytics;
+select count(aa) over (partition by bb order by bb rows unbounded preceding)
from analytics;
+
+select min(aa) over (rows 2 preceding) from analytics;
+select min(aa) over (partition by bb order by bb rows 2 preceding) from
analytics;
+select max(aa) over (rows 2 preceding) from analytics;
+select max(aa) over (partition by bb order by bb rows 2 preceding) from
analytics;
+
+select cast(sum(aa) over (rows 2 preceding) as bigint) from analytics;
+select cast(sum(aa) over (partition by bb order by bb rows 2 preceding) as
bigint) from analytics;
+select cast(prod(aa) over (rows 2 preceding) as bigint) from analytics;
+select cast(prod(aa) over (partition by bb order by bb rows 2 preceding) as
bigint) from analytics;
+
+select avg(aa) over (rows 2 preceding) from analytics;
+select avg(aa) over (partition by bb order by bb rows 2 preceding) from
analytics;
+
+select count(*) over (rows 2 preceding) from analytics;
+select count(*) over (partition by bb order by bb rows 2 preceding) from
analytics;
+select count(aa) over (rows 2 preceding) from analytics;
+select count(aa) over (partition by bb order by bb rows 2 preceding) from
analytics;
+
create table stressme (aa varchar(64), bb int);
insert into stressme values ('one', 1), ('another', 1), ('stress', 1), (NULL,
2), ('ok', 2), ('check', 3), ('me', 3), ('please', 3), (NULL, 4);
@@ -112,3 +150,10 @@ select avg(a) over (rows between 2 prece
select avg(a) over (rows between 2 preceding and 2 following) from overflowme;
rollback;
+
+select rank() over (rows unbounded preceding) from analytics; --error
+select dense_rank() over (rows 200 preceding) from analytics; --error
+select ntile(1) over (rows 200 preceding) from analytics; --error
+select first_value(aa) over (partition by bb order by bb rows between 2
preceding and 0 following) from analytics; --error
+
+drop table analytics;
diff --git a/sql/test/analytics/Tests/analytics02.stable.err
b/sql/test/analytics/Tests/analytics02.stable.err
--- a/sql/test/analytics/Tests/analytics02.stable.err
+++ b/sql/test/analytics/Tests/analytics02.stable.err
@@ -28,6 +28,22 @@ stderr of test 'analytics02` in director
# 12:55:22 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-8804" "--port=38610"
# 12:55:22 >
+MAPI = (monetdb) /var/tmp/mtest-21459/.s.monetdb.39691
+QUERY = select rank() over (rows unbounded preceding) from analytics; --error
+ERROR = !OVER: frame extend only possible with aggregation
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-21459/.s.monetdb.39691
+QUERY = select dense_rank() over (rows 200 preceding) from analytics; --error
+ERROR = !OVER: frame extend only possible with aggregation
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-21459/.s.monetdb.39691
+QUERY = select ntile(1) over (rows 200 preceding) from analytics; --error
+ERROR = !OVER: frame extend only possible with aggregation
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-21459/.s.monetdb.39691
+QUERY = select first_value(aa) over (partition by bb order by bb rows between
2 preceding and 0 following) from analytics; --error
+ERROR = !OVER: frame extend only possible with aggregation
+CODE = 42000
# 12:55:22 >
# 12:55:22 > "Done."
diff --git a/sql/test/analytics/Tests/analytics02.stable.out
b/sql/test/analytics/Tests/analytics02.stable.out
--- a/sql/test/analytics/Tests/analytics02.stable.out
+++ b/sql/test/analytics/Tests/analytics02.stable.out
@@ -718,12 +718,444 @@ Ready.
[ 1 ]
[ 1 ]
[ 1 ]
+#select count(*) over (rows unbounded preceding);
+% .L4 # table_name
+% L4 # name
+% bigint # type
+% 1 # length
+[ 1 ]
+#select count(*) over (rows 200 preceding);
+% .L4 # table_name
+% L4 # name
+% bigint # type
+% 1 # length
+[ 1 ]
#select count(*) over (rows between 5 preceding and 0 following);
% .L4 # table_name
% L4 # name
% bigint # type
% 1 # length
[ 1 ]
+#select min(aa) over (rows unbounded preceding) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% int # type
+% 2 # length
+[ 15 ]
+[ 3 ]
+[ 2 ]
+[ 2 ]
+[ 2 ]
+[ 2 ]
+[ 2 ]
+[ 2 ]
+[ 2 ]
+[ 2 ]
+#select min(aa) over (partition by bb order by bb rows unbounded preceding)
from analytics;
+% sys.L4 # table_name
+% L4 # name
+% int # type
+% 2 # length
+[ 3 ]
+[ 2 ]
+[ 2 ]
+[ 3 ]
+[ 3 ]
+[ 3 ]
+[ 15 ]
+[ 5 ]
+[ 5 ]
+[ NULL ]
+#select max(aa) over (rows unbounded preceding) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% int # type
+% 2 # length
+[ 15 ]
+[ 15 ]
+[ 15 ]
+[ 15 ]
+[ 15 ]
+[ 15 ]
+[ 15 ]
+[ 15 ]
+[ 15 ]
+[ 15 ]
+#select max(aa) over (partition by bb order by bb rows unbounded preceding)
from analytics;
+% sys.L4 # table_name
+% L4 # name
+% int # type
+% 2 # length
+[ 3 ]
+[ 3 ]
+[ 4 ]
+[ 3 ]
+[ 3 ]
+[ 8 ]
+[ 15 ]
+[ 15 ]
+[ 15 ]
+[ NULL ]
+#select cast(sum(aa) over (rows unbounded preceding) as bigint) from analytics;
+% sys.L5 # table_name
+% L5 # name
+% bigint # type
+% 2 # length
+[ 15 ]
+[ 18 ]
+[ 20 ]
+[ 25 ]
+[ 25 ]
+[ 28 ]
+[ 32 ]
+[ 38 ]
+[ 46 ]
+[ 46 ]
+#select cast(sum(aa) over (partition by bb order by bb rows unbounded
preceding) as bigint) from analytics;
+% sys.L5 # table_name
+% L5 # name
+% bigint # type
+% 2 # length
+[ 3 ]
+[ 5 ]
+[ 9 ]
+[ 3 ]
+[ 3 ]
+[ 11 ]
+[ 15 ]
+[ 20 ]
+[ 26 ]
+[ NULL ]
+#select cast(prod(aa) over (rows unbounded preceding) as bigint) from
analytics;
+% sys.L5 # table_name
+% L5 # name
+% bigint # type
+% 6 # length
+[ 15 ]
+[ 45 ]
+[ 90 ]
+[ 450 ]
+[ 450 ]
+[ 1350 ]
+[ 5400 ]
+[ 32400 ]
+[ 259200 ]
+[ 259200 ]
+#select cast(prod(aa) over (partition by bb order by bb rows unbounded
preceding) as bigint) from analytics;
+% sys.L5 # table_name
+% L5 # name
+% bigint # type
+% 3 # length
+[ 3 ]
+[ 6 ]
+[ 24 ]
+[ 3 ]
+[ 3 ]
+[ 24 ]
+[ 15 ]
+[ 75 ]
+[ 450 ]
+[ NULL ]
+#select avg(aa) over (rows unbounded preceding) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% double # type
+% 24 # length
+[ 15 ]
+[ 9 ]
+[ 6.666666667 ]
+[ 6.25 ]
+[ 6.25 ]
+[ 5.6 ]
+[ 5.333333333 ]
+[ 5.428571429 ]
+[ 5.75 ]
+[ 5.75 ]
+#select avg(aa) over (partition by bb order by bb rows unbounded preceding)
from analytics;
+% sys.L4 # table_name
+% L4 # name
+% double # type
+% 24 # length
+[ 3 ]
+[ 2.5 ]
+[ 3 ]
+[ 3 ]
+[ 3 ]
+[ 5.5 ]
+[ 15 ]
+[ 10 ]
+[ 8.666666667 ]
+[ NULL ]
+#select count(*) over (rows unbounded preceding) from analytics;
+% sys.L4 # table_name
+% L4 # name
+% bigint # type
+% 2 # length
+[ 1 ]
+[ 2 ]
+[ 3 ]
+[ 4 ]
+[ 5 ]
+[ 6 ]
+[ 7 ]
+[ 8 ]
+[ 9 ]
+[ 10 ]
+#select count(*) over (partition by bb order by bb rows unbounded preceding)
from analytics;
+% sys.L4 # table_name
+% L4 # name
+% bigint # type
+% 1 # length
+[ 1 ]
+[ 2 ]
+[ 3 ]
+[ 1 ]
+[ 2 ]
+[ 3 ]
+[ 1 ]
+[ 2 ]
+[ 3 ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
