Changeset: 267b0fb4961f for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=267b0fb4961f
Modified Files:
sql/test/analytics/Tests/analytics09.sql
sql/test/analytics/Tests/analytics09.stable.err
sql/test/analytics/Tests/analytics09.stable.out
Branch: default
Log Message:
Approved test output.
diffs (truncated from 328 to 300 lines):
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
@@ -29,15 +29,17 @@ select avg(sum(aa)) over (rows unbounded
select avg(sum(aa)) over (range unbounded preceding) from analytics;
-select avg(sum(aa)) over (), avg(avg(aa)) over () from analytics; --TODO
support multiple aggregations within the same projection
+select avg(sum(aa)) over (), avg(avg(aa)) over () from analytics;
select avg(sum(aa)) over (),
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; --TODO
support multiple aggregations within the same projection
+ avg(sum(aa)) over (range unbounded preceding) from analytics;
select avg(sum(aa)) over () from analytics group by aa;
+select cast(sum(aa) * count(aa) / avg(aa) over (rows between current row and
unbounded following) as bigint) from analytics group by aa;
+
select avg(sum(aa)) over (),
avg(sum(aa)) over (rows unbounded preceding),
cast(sum(aa) * count(aa) / avg(aa) over (rows between current row and
unbounded following) as bigint),
@@ -59,21 +61,21 @@ select rank() over (partition by sum(aa)
select rank() over (partition by 12*sum(aa)) from analytics; --TODO
-select rank() over (partition by sum(aa)) from analytics group by aa; --TODO
+select rank() over (partition by sum(aa)) from analytics group by aa;
-select rank() over (partition by sum(aa)) from analytics group by bb; --TODO
+select rank() over (partition by sum(aa)) from analytics group by bb;
select rank() over (partition by sum(aa)*sum(bb)) from analytics; --TODO
select rank() over (partition by sum(aa), sum(bb)) from analytics; --TODO
-select rank() over (partition by sum(aa), sum(bb)) from analytics group by aa;
--TODO
+select rank() over (partition by sum(aa), sum(bb)) from analytics group by aa;
-select rank() over (partition by sum(aa), bb) from analytics group by aa;
--TODO should be an error
+select rank() over (partition by sum(aa), bb) from analytics group by aa;
--error
-select min(aa) over (partition by sum(bb)) from analytics; --TODO should be an
error
+select min(aa) over (partition by sum(bb)) from analytics; --error
-select min(aa) over (partition by sum(aa)) from analytics; ---TODO should be
an error
+select min(aa) over (partition by sum(aa)) from analytics; ---error
select rank() over (order by sum(aa)) from analytics; --TODO
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,23 +28,12 @@ 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-1389/.s.monetdb.37694
-QUERY = select avg(sum(aa)) over (), avg(avg(aa)) over () from analytics;
--TODO support multiple aggregations within the same projection
-ERROR = !SELECT: cannot use non GROUP BY column 'aa' in query results without
an aggregate function
-CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-1389/.s.monetdb.37694
-QUERY = select avg(sum(aa)) over (),
- 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;
--TODO support multiple aggregations within the same projection
-ERROR = !SELECT: cannot use non GROUP BY column 'aa' in query results without
an aggregate function
-CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-1389/.s.monetdb.37694
+MAPI = (monetdb) /var/tmp/mtest-7775/.s.monetdb.35563
QUERY = select avg(sum(aa)) over (),
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; --TODO support multiple aggregations within the same projection
-ERROR = !Cannot use non GROUP BY column 'L4' in query results without an
aggregate function
+ERROR = !Cannot use non GROUP BY column 'L3' in query results without an
aggregate function
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-1389/.s.monetdb.37694
QUERY = select cast(sum(aa) * 100 / sum(sum(aa)) over (partition by bb) as
bigint) from analytics; --error, nesting aggregation functions
@@ -56,43 +45,31 @@ ERROR = !SELECT: subquery result missing
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-11873/.s.monetdb.31832
QUERY = select rank() over (partition by sum(aa)) from analytics; --TODO we
don't support expressions in partition by as well group by
-ERROR = !SUM: aggregate function 'sum' not allowed in PARTITION BY clause
+ERROR = !Cannot use non GROUP BY column 'aa' in query results without an
aggregate function
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-10156/.s.monetdb.31763
QUERY = select rank() over (partition by 12*sum(aa)) from analytics; --TODO
-ERROR = !SUM: aggregate function 'sum' not allowed in PARTITION BY clause
-CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-10156/.s.monetdb.31763
-QUERY = select rank() over (partition by sum(aa)) from analytics group by aa;
--TODO
-ERROR = !SUM: aggregate function 'sum' not allowed in PARTITION BY clause
-CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-10156/.s.monetdb.31763
-QUERY = select rank() over (partition by sum(aa)) from analytics group by bb;
--TODO
-ERROR = !SUM: aggregate function 'sum' not allowed in PARTITION BY clause
+ERROR = !Cannot use non GROUP BY column 'aa' in query results without an
aggregate function
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-10156/.s.monetdb.31763
QUERY = select rank() over (partition by sum(aa)*sum(bb)) from analytics;
--TODO
-ERROR = !SUM: aggregate function 'sum' not allowed in PARTITION BY clause
+ERROR = !Cannot use non GROUP BY column 'aa' in query results without an
aggregate function
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-10156/.s.monetdb.31763
QUERY = select rank() over (partition by sum(aa), sum(bb)) from analytics;
--TODO
-ERROR = !SUM: aggregate function 'sum' not allowed in PARTITION BY clause
+ERROR = !Cannot use non GROUP BY column 'aa' in query results without an
aggregate function
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-10156/.s.monetdb.31763
-QUERY = select rank() over (partition by sum(aa), sum(bb)) from analytics
group by aa; --TODO
-ERROR = !SUM: aggregate function 'sum' not allowed in PARTITION BY clause
+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
MAPI = (monetdb) /var/tmp/mtest-10156/.s.monetdb.31763
-QUERY = select rank() over (partition by sum(aa), bb) from analytics group by
aa; --TODO should be an error
-ERROR = !SUM: aggregate function 'sum' not allowed in PARTITION BY clause
+QUERY = select min(aa) over (partition by sum(bb)) from analytics; --error
+ERROR = !Cannot use non GROUP BY column 'aa' in query results without an
aggregate function
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-10156/.s.monetdb.31763
-QUERY = select min(aa) over (partition by sum(bb)) from analytics; --TODO
should be an error
-ERROR = !SUM: aggregate function 'sum' not allowed in PARTITION BY clause
-CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-10156/.s.monetdb.31763
-QUERY = select min(aa) over (partition by sum(aa)) from analytics; ---TODO
should be an error
-ERROR = !SUM: aggregate function 'sum' not allowed in PARTITION BY clause
+MAPI = (monetdb) /var/tmp/mtest-9405/.s.monetdb.32071
+QUERY = select min(aa) over (partition by sum(aa)) from analytics; ---error
+ERROR = !Cannot use non GROUP BY column 'aa' in query results without an
aggregate function
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-10884/.s.monetdb.34034
QUERY = select rank() over (order by sum(aa)) from analytics; --TODO
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
@@ -28,8 +28,8 @@ Ready.
#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
();
-% .L6, .L11, .L15 # table_name
-% L6, L11, L15 # name
+% .L5, .L10, .L14 # table_name
+% L5, L10, L14 # name
% bigint, int, tinyint # type
% 1, 1, 1 # length
[ 1, 1, 1 ]
@@ -52,8 +52,8 @@ Ready.
% 2 # length
[ 46 ]
#select cast(aa * count(1) over () as bigint) from analytics;
-% sys.L6 # table_name
-% L6 # name
+% sys.L5 # table_name
+% L5 # name
% bigint # type
% 3 # length
[ 150 ]
@@ -79,14 +79,14 @@ Ready.
% 3 # length
[ 368 ]
#select avg(sum(aa)) over () from analytics;
-% sys.L6 # table_name
-% L6 # name
+% sys.L5 # table_name
+% L5 # name
% double # type
% 24 # length
[ 46 ]
#select sum(cast(aa as double)) over (rows unbounded preceding) from analytics;
-% sys.L5 # table_name
-% L5 # name
+% sys.L4 # table_name
+% L4 # name
% double # type
% 24 # length
[ 15 ]
@@ -100,8 +100,8 @@ Ready.
[ 46 ]
[ 46 ]
#select sum(cast(aa as double)) over (range unbounded preceding) from
analytics;
-% sys.L5 # table_name
-% L5 # name
+% sys.L4 # table_name
+% L4 # name
% double # type
% 24 # length
[ 46 ]
@@ -115,32 +115,47 @@ Ready.
[ 46 ]
[ 46 ]
#select avg(avg(aa)) over (rows unbounded preceding) from analytics;
-% sys.L6 # table_name
-% L6 # name
+% sys.L5 # table_name
+% L5 # name
% double # type
% 24 # length
[ 5.75 ]
#select avg(avg(aa)) over (range unbounded preceding) from analytics;
-% sys.L6 # table_name
-% L6 # name
+% sys.L5 # table_name
+% L5 # name
% double # type
% 24 # length
[ 5.75 ]
#select avg(sum(aa)) over (rows unbounded preceding) from analytics;
-% sys.L6 # table_name
-% L6 # name
+% sys.L5 # table_name
+% L5 # name
% double # type
% 24 # length
[ 46 ]
#select avg(sum(aa)) over (range unbounded preceding) from analytics;
-% sys.L6 # table_name
-% L6 # name
+% sys.L5 # table_name
+% L5 # name
% double # type
% 24 # length
[ 46 ]
+#select avg(sum(aa)) over (), avg(avg(aa)) over () from analytics;
+% sys.L5, sys.L11 # table_name
+% L5, L11 # name
+% double, double # type
+% 24, 24 # length
+[ 46, 5.75 ]
+#select avg(sum(aa)) over (),
+# 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.L5, sys.L16, sys.L21, sys.L25 # table_name
+% L5, L16, L21, L25 # 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.L4 # table_name
-% L4 # name
+% sys.L3 # table_name
+% L3 # name
% double # type
% 24 # length
[ 6.571428571 ]
@@ -151,6 +166,19 @@ Ready.
[ 6.571428571 ]
[ 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.L6 # table_name
+% L6 # name
+% bigint # type
+% 1 # length
+[ 2 ]
+[ 3 ]
+[ 0 ]
+[ 1 ]
+[ NULL ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
#select avg(sum(aa)) over (partition by bb) from analytics group by bb;
% sys.L4 # table_name
% L4 # name
@@ -161,8 +189,8 @@ Ready.
[ 26 ]
[ NULL ]
#select cast(sum(aa) * 100 / sum(sum(aa)) over () as bigint) from analytics;
-% sys.L10 # table_name
-% L10 # name
+% sys.L7 # table_name
+% L7 # name
% bigint # type
% 3 # length
[ 100 ]
@@ -176,6 +204,21 @@ Ready.
[ 100 ]
[ NULL ]
#select rank() over (partition by case when aa > 5 then aa else aa + 5 end)
from analytics;
+% sys.L3 # table_name
+% L3 # name
+% int # type
+% 1 # length
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+#select rank() over (partition by sum(aa)) from analytics group by aa;
% sys.L4 # table_name
% L4 # name
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
