Changeset: b245646e57fc for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b245646e57fc
Modified Files:
sql/server/rel_select.c
sql/test/analytics/Tests/analytics04.sql
sql/test/analytics/Tests/analytics04.stable.err
Branch: analytics
Log Message:
After several days of debugging I officially give up to add support for range
and group framed windows with non-centered bounds.
There are too many corner cases to handle, and if someone is brave enough to do
it, go ahead.
diffs (109 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
@@ -4725,6 +4725,8 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
return sql_error(sql, 02, SQLSTATE(42000) "CURRENT ROW
offset must come after PRECEDING offset");
if(wstart->token == SQL_FOLLOWING && wend->token ==
SQL_CURRENT_ROW)
return sql_error(sql, 02, SQLSTATE(42000) "FOLLOWING
offset must come after CURRENT ROW offset");
+ if(wstart->token == wend->token && (frame_type != FRAME_ROWS &&
frame_type != FRAME_ALL))
+ return sql_error(sql, 02, SQLSTATE(42000) "Non-centered
windows are only supported in row frames");
if(!obe && frame_type == FRAME_RANGE) {
bool ok_preceding = false, ok_following = false;
if(rstart->token == SQL_ATOM) {
diff --git a/sql/test/analytics/Tests/analytics04.sql
b/sql/test/analytics/Tests/analytics04.sql
--- a/sql/test/analytics/Tests/analytics04.sql
+++ b/sql/test/analytics/Tests/analytics04.sql
@@ -35,13 +35,15 @@ select cast(sum(aa) over (partition by b
cast(sum(aa) over (partition by bb order by bb rows between 0 following
and 0 following) as bigint),
cast(sum(aa) over (partition by bb order by bb rows between 0 preceding
and 0 preceding) as bigint) from analytics;
+rollback;
+
select count(*) over (order by bb range between 3 preceding and 2 preceding),
count(*) over (order by bb range between 2 following and 3 following),
count(*) over (order by bb range between 3 preceding and 3 preceding),
count(*) over (order by bb range between 3 preceding and 9 preceding),
count(*) over (order by bb range between current row and current row),
count(*) over (order by bb range between 0 following and 0 following),
- count(*) over (order by bb range between 0 preceding and 0 preceding)
from analytics;
+ count(*) over (order by bb range between 0 preceding and 0 preceding)
from analytics; --TODO someone brave to do add support to this...
select cast(sum(aa) over (partition by bb order by bb range between 3
preceding and 2 preceding) as bigint),
cast(sum(aa) over (partition by bb order by bb range between 2
following and 3 following) as bigint),
@@ -49,7 +51,7 @@ select cast(sum(aa) over (partition by b
cast(sum(aa) over (partition by bb order by bb range between 3
preceding and 9 preceding) as bigint),
cast(sum(aa) over (partition by bb order by bb range between current
row and current row) as bigint),
cast(sum(aa) over (partition by bb order by bb range between 0
following and 0 following) as bigint),
- cast(sum(aa) over (partition by bb order by bb range between 0
preceding and 0 preceding) as bigint) from analytics;
+ cast(sum(aa) over (partition by bb order by bb range between 0
preceding and 0 preceding) as bigint) from analytics; --TODO someone brave to
do add support to this...
select count(*) over (order by bb groups between 3 preceding and 2 preceding),
count(*) over (order by bb groups between 2 following and 3 following),
@@ -57,7 +59,7 @@ select count(*) over (order by bb groups
count(*) over (order by bb groups between 3 preceding and 9 preceding),
count(*) over (order by bb groups between current row and current row),
count(*) over (order by bb groups between 0 following and 0 following),
- count(*) over (order by bb groups between 0 preceding and 0 preceding)
from analytics;
+ count(*) over (order by bb groups between 0 preceding and 0 preceding)
from analytics; --TODO someone brave to do add support to this...
select cast(sum(aa) over (partition by bb order by bb groups between 3
preceding and 2 preceding) as bigint),
cast(sum(aa) over (partition by bb order by bb groups between 2
following and 3 following) as bigint),
@@ -65,9 +67,7 @@ select cast(sum(aa) over (partition by b
cast(sum(aa) over (partition by bb order by bb groups between 3
preceding and 9 preceding) as bigint),
cast(sum(aa) over (partition by bb order by bb groups between current
row and current row) as bigint),
cast(sum(aa) over (partition by bb order by bb groups between 0
following and 0 following) as bigint),
- cast(sum(aa) over (partition by bb order by bb groups between 0
preceding and 0 preceding) as bigint) from analytics;
-
-rollback;
+ cast(sum(aa) over (partition by bb order by bb groups between 0
preceding and 0 preceding) as bigint) from analytics; --TODO someone brave to
do add support to this...
select count(*) over (rows between 3 following and 2 preceding) from
analytics; --error
select count(*) over (rows between current row and 2 preceding) from
analytics; --error
diff --git a/sql/test/analytics/Tests/analytics04.stable.err
b/sql/test/analytics/Tests/analytics04.stable.err
--- a/sql/test/analytics/Tests/analytics04.stable.err
+++ b/sql/test/analytics/Tests/analytics04.stable.err
@@ -28,7 +28,43 @@ stderr of test 'analytics04` in director
# 14:06:28 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-31905" "--port=30029"
# 14:06:28 >
-MAPI = (monetdb) /var/tmp/mtest-31905/.s.monetdb.30029
+MAPI = (monetdb) /var/tmp/mtest-4171/.s.monetdb.32442
+QUERY = select count(*) over (order by bb range between 3 preceding and 2
preceding),
+ count(*) over (order by bb range between 2 following and 3
following),
+ count(*) over (order by bb range between 3 preceding and 3
preceding),
+ count(*) over (order by bb range between 3 preceding and 9
preceding),
+ count(*) over (order by bb range between current row and
current row),
+ count(*) over (order by bb range between 0 following and 0
following),
+ count(*) over (order by bb range between 0 preceding and 0
preceding) from analytics; --TODO someone brave to do add support to this...
+ERROR = !Non-centered windows are only supported in row frames
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-4171/.s.monetdb.32442
+QUERY = select cast(sum(aa) over (partition by bb order by bb range between 3
preceding and 2 preceding) as bigint),
+ cast(sum(aa) over (partition by bb order by bb range between 2
following and 3 following) as bigint),
+ cast(sum(aa) over (partition by bb order by bb range between 3
preceding and 3 preceding) as bigint),
+ cast(sum(aa) over (partition by bb order by bb range between 3
preceding and 9 preceding) as bigint),
+ cast(sum(aa) over (partition by bb order by bb range between
current row and current row) as bigint),
+ERROR = !Non-centered windows are only supported in row frames
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-4171/.s.monetdb.32442
+QUERY = select count(*) over (order by bb groups between 3 preceding and 2
preceding),
+ count(*) over (order by bb groups between 2 following and 3
following),
+ count(*) over (order by bb groups between 3 preceding and 3
preceding),
+ count(*) over (order by bb groups between 3 preceding and 9
preceding),
+ count(*) over (order by bb groups between current row and
current row),
+ count(*) over (order by bb groups between 0 following and 0
following),
+ count(*) over (order by bb groups between 0 preceding and 0
preceding) from analytics; --TODO someone brave to do add support to this...
+ERROR = !Non-centered windows are only supported in row frames
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-4171/.s.monetdb.32442
+QUERY = select cast(sum(aa) over (partition by bb order by bb groups between 3
preceding and 2 preceding) as bigint),
+ cast(sum(aa) over (partition by bb order by bb groups between 2
following and 3 following) as bigint),
+ cast(sum(aa) over (partition by bb order by bb groups between 3
preceding and 3 preceding) as bigint),
+ cast(sum(aa) over (partition by bb order by bb groups between 3
preceding and 9 preceding) as bigint),
+ cast(sum(aa) over (partition by bb order by bb groups between
current row and current row) as bigint),
+ERROR = !Non-centered windows are only supported in row frames
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-4171/.s.monetdb.32442
QUERY = select count(*) over (rows between 3 following and 2 preceding) from
analytics; --error
ERROR = !FOLLOWING offset must come after PRECEDING offset
CODE = 42000
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
