Changeset: b30481e0c063 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b30481e0c063
Added Files:
sql/test/analytics/Tests/analytics03.stable.err
sql/test/analytics/Tests/analytics03.stable.out
Modified Files:
sql/server/rel_select.c
sql/test/analytics/Tests/All
sql/test/analytics/Tests/analytics03.sql
Branch: analytics
Log Message:
Approve test and fix range boundary case.
diffs (truncated from 551 to 300 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
@@ -4712,16 +4712,19 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
if(!obe && frame_type == FRAME_GROUPS)
return sql_error(sql, 02, SQLSTATE(42000) "GROUPS frame
requires an order by expression");
if(!obe && frame_type == FRAME_RANGE) {
- atom *a = NULL;
+ bool ok_preceding = false, ok_following = false;
if(d->data.sym->token == SQL_ATOM) {
- a = ((AtomNode*) d->data.sym)->a;
- if(a->data.vtype == TYPE_lng &&
a->data.val.lval == GDK_lng_max)
- return sql_error(sql, 02,
SQLSTATE(42000) "RANGE frame with PRECEDING offset requires an order by
expression");
- } else if(d->next->data.sym->token == SQL_ATOM) {
- a = ((AtomNode*) d->next->data.sym)->a;
- if(a->data.vtype == TYPE_lng &&
a->data.val.lval == GDK_lng_max)
- return sql_error(sql, 02,
SQLSTATE(42000) "RANGE frame with FOLLOWING offset requires an order by
expression");
+ atom *a = ((AtomNode*) d->data.sym)->a;
+ if(a->data.vtype == TYPE_lng &&
(a->data.val.lval == 0 || a->data.val.lval == GDK_lng_max))
+ ok_preceding = true;
}
+ if(d->next->data.sym->token == SQL_ATOM) {
+ atom *a = ((AtomNode*) d->next->data.sym)->a;
+ if(a->data.vtype == TYPE_lng &&
(a->data.val.lval == 0 || a->data.val.lval == GDK_lng_max))
+ ok_following = true;
+ }
+ if(!ok_preceding || !ok_following)
+ return sql_error(sql, 02, SQLSTATE(42000)
"RANGE frame with PRECEDING/FOLLOWING offset requires an order by expression");
frame_type = FRAME_ALL; //special case, iterate the
entire partition
}
diff --git a/sql/test/analytics/Tests/All b/sql/test/analytics/Tests/All
--- a/sql/test/analytics/Tests/All
+++ b/sql/test/analytics/Tests/All
@@ -1,3 +1,4 @@
analytics00
analytics01
analytics02
+analytics03
diff --git a/sql/test/analytics/Tests/analytics03.sql
b/sql/test/analytics/Tests/analytics03.sql
--- a/sql/test/analytics/Tests/analytics03.sql
+++ b/sql/test/analytics/Tests/analytics03.sql
@@ -1,68 +1,60 @@
-start transaction;
+create table rowsvsrangevsgroups (aa int, bb int, cc real);
-create table rowsvsrange (aa int, bb int, cc real);
-insert into rowsvsrange values (1,1,1), (2,1,2), (3,1,3), (1,2,1), (1,2,1),
(1,2,1), (2,2,2), (3,2,3), (4,2,4), (2,2,2);
+start transaction;
+insert into rowsvsrangevsgroups values (1,1,1), (2,1,2), (3,1,3), (1,2,1),
(1,2,1), (1,2,1), (2,2,2), (3,2,3), (4,2,4), (2,2,2);
select cast(sum(aa) over (rows unbounded preceding) as bigint),
cast(sum(aa) over (range unbounded preceding) as bigint),
cast(sum(aa) over (order by aa rows unbounded preceding) as bigint),
cast(sum(aa) over (order by aa range unbounded preceding) as bigint),
+ cast(sum(aa) over (order by aa groups unbounded preceding) as bigint),
cast(sum(aa) over (partition by bb order by bb rows unbounded
preceding) as bigint),
- cast(sum(aa) over (partition by bb order by bb range unbounded
preceding) as bigint) from rowsvsrange;
-
-select sum(cc) over (rows unbounded preceding),
- sum(cc) over (range unbounded preceding),
- sum(cc) over (order by cc rows unbounded preceding),
- sum(cc) over (order by cc range unbounded preceding),
- sum(cc) over (partition by bb order by bb rows unbounded preceding),
- sum(cc) over (partition by bb order by bb range unbounded preceding)
from rowsvsrange;
+ cast(sum(aa) over (partition by bb order by bb range unbounded
preceding) as bigint),
+ cast(sum(aa) over (partition by bb order by bb groups unbounded
preceding) as bigint) from rowsvsrangevsgroups;
-select cast(sum(aa) over (order by aa range between current row and unbounded
following) as bigint) from rowsvsrange;
-select sum(cc) over (order by cc range between current row and unbounded
following) from rowsvsrange;
-select count(*) over (order by cc range between current row and unbounded
following) from rowsvsrange;
-select count(aa) over (order by cc range between current row and unbounded
following) from rowsvsrange;
-select min(aa) over (order by cc range between current row and unbounded
following) from rowsvsrange;
-select max(aa) over (order by cc range between current row and unbounded
following) from rowsvsrange;
-select avg(aa) over (order by cc range between current row and unbounded
following) from rowsvsrange;
-select avg(cc) over (order by aa range between current row and unbounded
following) from rowsvsrange;
+select cast(sum(aa) over (order by aa range between unbounded preceding and
current row) as bigint),
+ cast(sum(cc) over (order by aa range between unbounded preceding and
current row) as bigint),
+ count(*) over (order by aa range between unbounded preceding and
current row),
+ count(aa) over (order by aa range between unbounded preceding and
current row),
+ min(aa) over (order by aa range between unbounded preceding and current
row),
+ max(aa) over (order by aa range between unbounded preceding and current
row),
+ avg(aa) over (order by aa range between unbounded preceding and current
row),
+ avg(cc) over (order by aa range between unbounded preceding and current
row) from rowsvsrangevsgroups;
-create table analytics (aa int, bb int, cc bigint);
-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 (order by aa groups between unbounded preceding and
current row) as bigint),
+ cast(sum(cc) over (order by aa groups between unbounded preceding and
current row) as bigint),
+ count(*) over (order by aa groups between unbounded preceding and
current row),
+ count(aa) over (order by aa groups between unbounded preceding and
current row),
+ min(aa) over (order by aa groups between unbounded preceding and
current row),
+ max(aa) over (order by aa groups between unbounded preceding and
current row),
+ avg(aa) over (order by aa groups between unbounded preceding and
current row),
+ avg(cc) over (order by aa groups between unbounded preceding and
current row) from rowsvsrangevsgroups;
-select count(*) over (rows between current row and unbounded following),
- count(*) over (range between current row and unbounded following),
- count(*) over (order by bb rows between current row and unbounded
following),
- count(*) over (order by bb range between current row and unbounded
following),
- count(*) over (partition by bb order by bb rows unbounded preceding),
- count(*) over (partition by bb order by bb range unbounded preceding)
from analytics;
+select cast(sum(aa) over (order by aa groups between 1 preceding and current
row) as bigint),
+ avg(cc) over (order by aa groups between 1 preceding and current row)
from rowsvsrangevsgroups;
-select count(aa) over (rows between current row and unbounded following),
- count(aa) over (range between current row and unbounded following),
- count(aa) over (order by bb rows between current row and unbounded
following),
- count(aa) over (order by bb range between current row and unbounded
following),
- count(aa) over (partition by bb order by bb rows unbounded preceding),
- count(aa) over (partition by bb order by bb range unbounded preceding)
from analytics;
+delete from rowsvsrangevsgroups where aa = 2;
-select min(aa) over (rows between current row and unbounded following),
- min(aa) over (range between current row and unbounded following),
- min(aa) over (order by bb rows between current row and unbounded
following),
- min(aa) over (order by bb range between current row and unbounded
following),
- min(aa) over (partition by bb order by bb rows unbounded preceding),
- min(aa) over (partition by bb order by bb range unbounded preceding)
from analytics;
+select cast(sum(aa) over (order by aa range between unbounded preceding and
current row) as bigint),
+ cast(sum(cc) over (order by aa range between unbounded preceding and
current row) as bigint),
+ count(*) over (order by aa range between unbounded preceding and
current row),
+ count(aa) over (order by aa range between unbounded preceding and
current row),
+ min(aa) over (order by aa range between unbounded preceding and current
row),
+ max(aa) over (order by aa range between unbounded preceding and current
row),
+ avg(aa) over (order by aa range between unbounded preceding and current
row),
+ avg(cc) over (order by aa range between unbounded preceding and current
row) from rowsvsrangevsgroups;
-select max(aa) over (rows between current row and unbounded following),
- max(aa) over (range between current row and unbounded following),
- max(aa) over (order by bb rows between current row and unbounded
following),
- max(aa) over (order by bb range between current row and unbounded
following),
- max(aa) over (partition by bb order by bb rows unbounded preceding),
- max(aa) over (partition by bb order by bb range unbounded preceding)
from analytics;
+select cast(sum(aa) over (order by aa groups between unbounded preceding and
current row) as bigint),
+ cast(sum(cc) over (order by aa groups between unbounded preceding and
current row) as bigint),
+ count(*) over (order by aa groups between unbounded preceding and
current row),
+ count(aa) over (order by aa groups between unbounded preceding and
current row),
+ min(aa) over (order by aa groups between unbounded preceding and
current row),
+ max(aa) over (order by aa groups between unbounded preceding and
current row),
+ avg(aa) over (order by aa groups between unbounded preceding and
current row),
+ avg(cc) over (order by aa groups between unbounded preceding and
current row) from rowsvsrangevsgroups;
-select avg(aa) over (rows between current row and unbounded following),
- avg(aa) over (range between current row and unbounded following),
- avg(aa) over (order by bb rows between current row and unbounded
following),
- avg(aa) over (order by bb range between current row and unbounded
following),
- avg(aa) over (partition by bb order by bb rows unbounded preceding),
- avg(aa) over (partition by bb order by bb range unbounded preceding)
from analytics;
+select cast(sum(aa) over (order by aa groups between 1 preceding and current
row) as bigint),
+ avg(cc) over (order by aa groups between 1 preceding and current row)
from rowsvsrangevsgroups;
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);
@@ -71,22 +63,36 @@ select count(aa) over (rows between curr
count(aa) over (range between current row and unbounded following),
count(aa) over (order by bb rows between current row and unbounded
following),
count(aa) over (order by bb range between current row and unbounded
following),
+ count(aa) over (order by bb groups between current row and unbounded
following),
count(aa) over (partition by bb order by bb rows unbounded preceding),
- count(aa) over (partition by bb order by bb range unbounded preceding)
from stressme;
+ count(aa) over (partition by bb order by bb range unbounded preceding),
+ count(aa) over (partition by bb order by bb groups unbounded preceding)
from stressme;
select min(aa) over (rows between current row and unbounded following),
min(aa) over (range between current row and unbounded following),
min(aa) over (order by bb rows between current row and unbounded
following),
min(aa) over (order by bb range between current row and unbounded
following),
+ min(aa) over (order by bb groups between current row and unbounded
following),
min(aa) over (partition by bb order by bb rows unbounded preceding),
- min(aa) over (partition by bb order by bb range unbounded preceding)
from stressme;
+ min(aa) over (partition by bb order by bb range unbounded preceding),
+ min(aa) over (partition by bb order by bb groups unbounded preceding)
from stressme;
select max(aa) over (rows between current row and unbounded following),
max(aa) over (range between current row and unbounded following),
max(aa) over (order by bb rows between current row and unbounded
following),
max(aa) over (order by bb range between current row and unbounded
following),
+ max(aa) over (order by bb groups between current row and unbounded
following),
max(aa) over (partition by bb order by bb rows unbounded preceding),
- max(aa) over (partition by bb order by bb range unbounded preceding)
from stressme;
+ max(aa) over (partition by bb order by bb range unbounded preceding),
+ max(aa) over (partition by bb order by bb groups unbounded preceding)
from stressme;
+
+select max(aa) over (order by bb groups between 1 preceding and current row),
+ count(aa) over (order by bb groups between 1 preceding and current row)
from stressme;
+
+delete from stressme where bb = 2;
+
+select max(aa) over (order by bb groups between 1 preceding and current row),
+ count(aa) over (order by bb groups between 1 preceding and current row)
from stressme;
create table overflowme (aa int, bb int);
insert into overflowme values (2147483644, 1), (2147483645, 2), (2147483646,
1), (2147483644, 2), (2147483645, 1), (2147483646, 2);
@@ -99,3 +105,12 @@ select avg(aa) over (rows between curren
avg(aa) over (partition by bb order by bb range unbounded preceding)
from overflowme;
rollback;
+
+select count(*) over (rows between NULL preceding and unbounded following)
from rowsvsrangevsgroups; --error
+select count(*) over (rows between unbounded preceding and -1 following) from
rowsvsrangevsgroups; --error
+select count(*) over (range between 1 preceding and unbounded following) from
rowsvsrangevsgroups; --error
+select count(*) over (range between unbounded preceding and 1 following) from
rowsvsrangevsgroups; --error
+select count(*) over (groups between 1 preceding and 1 following) from
rowsvsrangevsgroups; --error
+select count(*) over (groups between current row and unbounded following) from
rowsvsrangevsgroups; --error
+
+drop table rowsvsrangevsgroups;
diff --git a/sql/test/analytics/Tests/analytics03.stable.err
b/sql/test/analytics/Tests/analytics03.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/analytics/Tests/analytics03.stable.err
@@ -0,0 +1,59 @@
+stderr of test 'analytics03` in directory 'sql/test/analytics` itself:
+
+
+# 17:06:35 >
+# 17:06:35 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=34302" "--set"
"mapi_usock=/var/tmp/mtest-29260/.s.monetdb.34302" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-analytics/BUILD/var/MonetDB/mTests_sql_test_analytics"
"--set" "embedded_c=true"
+# 17:06:35 >
+
+# builtin opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-analytics/BUILD/var/monetdb5/dbfarm/demo
+# builtin opt gdk_debug = 0
+# builtin opt gdk_vmtrim = no
+# builtin opt monet_prompt = >
+# builtin opt monet_daemon = no
+# builtin opt mapi_port = 50000
+# builtin opt mapi_open = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 34302
+# cmdline opt mapi_usock = /var/tmp/mtest-29260/.s.monetdb.34302
+# cmdline opt monet_prompt =
+# cmdline opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-analytics/BUILD/var/MonetDB/mTests_sql_test_analytics
+# cmdline opt embedded_c = true
+# cmdline opt gdk_debug = 553648138
+
+# 17:06:35 >
+# 17:06:35 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-29260" "--port=34302"
+# 17:06:35 >
+
+MAPI = (monetdb) /var/tmp/mtest-29260/.s.monetdb.34302
+QUERY = select count(*) over (rows between NULL preceding and unbounded
following) from rowsvsrangevsgroups; --error
+ERROR = !PRECEDING offset column must be of a countable SQL type
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-29260/.s.monetdb.34302
+QUERY = select count(*) over (rows between unbounded preceding and -1
following) from rowsvsrangevsgroups; --error
+ERROR = !The FOLLOWING boundary must be non-negative
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-29260/.s.monetdb.34302
+QUERY = select count(*) over (range between 1 preceding and unbounded
following) from rowsvsrangevsgroups; --error
+ERROR = !RANGE frame with PRECEDING/FOLLOWING offset requires an order by
expression
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-29260/.s.monetdb.34302
+QUERY = select count(*) over (range between unbounded preceding and 1
following) from rowsvsrangevsgroups; --error
+ERROR = !RANGE frame with PRECEDING/FOLLOWING offset requires an order by
expression
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-29260/.s.monetdb.34302
+QUERY = select count(*) over (groups between 1 preceding and 1 following) from
rowsvsrangevsgroups; --error
+ERROR = !GROUPS frame requires an order by expression
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-29260/.s.monetdb.34302
+QUERY = select count(*) over (groups between current row and unbounded
following) from rowsvsrangevsgroups; --error
+ERROR = !GROUPS frame requires an order by expression
+CODE = 42000
+
+# 17:06:35 >
+# 17:06:35 > "Done."
+# 17:06:35 >
+
diff --git a/sql/test/analytics/Tests/analytics03.stable.out
b/sql/test/analytics/Tests/analytics03.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/analytics/Tests/analytics03.stable.out
@@ -0,0 +1,275 @@
+stdout of test 'analytics03` in directory 'sql/test/analytics` itself:
+
+
+# 17:06:35 >
+# 17:06:35 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=34302" "--set"
"mapi_usock=/var/tmp/mtest-29260/.s.monetdb.34302" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-analytics/BUILD/var/MonetDB/mTests_sql_test_analytics"
"--set" "embedded_c=true"
+# 17:06:35 >
+
+# MonetDB 5 server v11.32.0
+# This is an unreleased version
+# Serving database 'mTests_sql_test_analytics', using 8 threads
+# Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers
+# Found 15.492 GiB available main-memory.
+# Copyright (c) 1993 - July 2008 CWI.
+# Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved
+# Visit https://www.monetdb.org/ for further information
+# Listening for connection requests on mapi:monetdb://wired-142.cwi.nl:34302/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-29260/.s.monetdb.34302
+# MonetDB/GIS module loaded
+# MonetDB/SQL module loaded
+
+Ready.
+
+# 17:06:35 >
+# 17:06:35 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-29260" "--port=34302"
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
