Changeset: 768071ed3779 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=768071ed3779
Added Files:
sql/test/analytics/Tests/analytics06.sql
sql/test/analytics/Tests/analytics06.stable.err
sql/test/analytics/Tests/analytics06.stable.out
Modified Files:
gdk/gdk_analytic_bounds.c
sql/server/rel_select.c
sql/test/analytics/Tests/All
Branch: analytics
Log Message:
Approve new test.
diffs (232 lines):
diff --git a/gdk/gdk_analytic_bounds.c b/gdk/gdk_analytic_bounds.c
--- a/gdk/gdk_analytic_bounds.c
+++ b/gdk/gdk_analytic_bounds.c
@@ -761,7 +761,8 @@ bound_not_supported:
}
gdk_return
-GDKanalyticalwindowbounds(BAT *r, BAT *b, BAT *p, BAT *l, const void* restrict
bound, int tp1, int tp2, int unit, bool preceding, lng first_half)
+GDKanalyticalwindowbounds(BAT *r, BAT *b, BAT *p, BAT *l, const void* restrict
bound, int tp1, int tp2, int unit,
+ bool preceding, lng
first_half)
{
switch(unit) {
case 0:
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
@@ -4752,6 +4752,7 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
frame_type = FRAME_ALL; //special case, iterate the
entire partition
}
+ //SQL keyword case
if((rstart->token == SQL_PRECEDING || rstart->token ==
SQL_FOLLOWING || rstart->token == SQL_CURRENT_ROW) && rstart->type == type_int)
{
atom *a = NULL;
st = exp_subtype(ie);
@@ -4771,7 +4772,7 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
assert(0);
}
fstart = exp_atom(sql->sa, a);
- } else {
+ } else { //arbitrary expression case
is_last = 0;
fstart = rel_value_exp2(sql, &p, rstart, f, ek,
&is_last);
if(!fstart)
@@ -4817,7 +4818,7 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
if(calculate_window_bounds(sql, &start, &eend, s, gbe ? pe :
NULL, ie, fstart, fend, frame_type, excl,
wstart->token, wend->token) == NULL)
return NULL;
- } else if (aggr) {
+ } else if (aggr) { //for aggregations with no frame clause, we use the
standard default values
sql_exp *ie = obe ? obe->t->data : in;
sql_subtype *it = sql_bind_localtype("int"), *st =
exp_subtype(ie);
unsigned char sclass = st->type->eclass;
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
@@ -4,3 +4,4 @@ analytics02
analytics03
analytics04
analytics05
+analytics06
diff --git a/sql/test/analytics/Tests/analytics06.sql
b/sql/test/analytics/Tests/analytics06.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/analytics/Tests/analytics06.sql
@@ -0,0 +1,19 @@
+create table testing (aa int, bb int, cc bigint);
+insert into testing values (15, 3, 15), (3, 1, 3), (2, 1, 2), (5, 3, 5),
(NULL, 0, NULL), (3, 0, 3), (4, 1, 4), (6, 3, 6), (8, 0, 8), (NULL, 4, NULL);
+
+start transaction;
+
+select count(aa) over (partition by bb), 75 + count(aa) over (partition by bb)
from testing where bb <> 1;
+
+with relation as (select row_number() over () as dd, aa, bb from testing where
bb <> 1)
+select aa, bb, dd,
+ count(aa) over (partition by bb rows between dd preceding and current
row),
+ count(aa) over (partition by bb rows between dd preceding and dd
following),
+ count(aa) over (partition by bb rows between dd + 1 preceding and dd
preceding) from relation where bb <> 1;
+
+rollback;
+
+select max(aa) over (partition by bb rows 'something' preceding) from testing;
--error
+select max(distinct aa) over (partition by bb) from testing; --error
+
+drop table testing;
diff --git a/sql/test/analytics/Tests/analytics06.stable.err
b/sql/test/analytics/Tests/analytics06.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/analytics/Tests/analytics06.stable.err
@@ -0,0 +1,43 @@
+stderr of test 'analytics06` in directory 'sql/test/analytics` itself:
+
+
+# 15:51:43 >
+# 15:51:43 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=37407" "--set"
"mapi_usock=/var/tmp/mtest-20542/.s.monetdb.37407" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-analytics/BUILD/var/MonetDB/mTests_sql_test_analytics"
"--set" "embedded_c=true"
+# 15:51:43 >
+
+# 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 = 37407
+# cmdline opt mapi_usock = /var/tmp/mtest-20542/.s.monetdb.37407
+# 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
+
+# 15:51:44 >
+# 15:51:44 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-20542" "--port=37407"
+# 15:51:44 >
+
+MAPI = (monetdb) /var/tmp/mtest-20542/.s.monetdb.37407
+QUERY = select max(aa) over (partition by bb rows 'something' preceding) from
testing; --error
+ERROR = !PRECEDING offset must be of a countable SQL type
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-20542/.s.monetdb.37407
+QUERY = select max(distinct aa) over (partition by bb) from testing; --error
+ERROR = !SELECT: DISTINCT clause is not implemented for window functions
+CODE = 42000
+
+# 15:51:44 >
+# 15:51:44 > "Done."
+# 15:51:44 >
+
diff --git a/sql/test/analytics/Tests/analytics06.stable.out
b/sql/test/analytics/Tests/analytics06.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/analytics/Tests/analytics06.stable.out
@@ -0,0 +1,105 @@
+stdout of test 'analytics06` in directory 'sql/test/analytics` itself:
+
+
+# 15:51:43 >
+# 15:51:43 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=37407" "--set"
"mapi_usock=/var/tmp/mtest-20542/.s.monetdb.37407" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-analytics/BUILD/var/MonetDB/mTests_sql_test_analytics"
"--set" "embedded_c=true"
+# 15:51:43 >
+
+# 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:37407/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-20542/.s.monetdb.37407
+# MonetDB/GIS module loaded
+# SQL catalog created, loading sql scripts once
+# loading sql script: 09_like.sql
+# loading sql script: 10_math.sql
+# loading sql script: 11_times.sql
+# loading sql script: 12_url.sql
+# loading sql script: 13_date.sql
+# loading sql script: 14_inet.sql
+# loading sql script: 15_querylog.sql
+# loading sql script: 16_tracelog.sql
+# loading sql script: 17_temporal.sql
+# loading sql script: 18_index.sql
+# loading sql script: 20_vacuum.sql
+# loading sql script: 21_dependency_views.sql
+# loading sql script: 22_clients.sql
+# loading sql script: 23_skyserver.sql
+# loading sql script: 25_debug.sql
+# loading sql script: 26_sysmon.sql
+# loading sql script: 27_rejects.sql
+# loading sql script: 39_analytics.sql
+# loading sql script: 39_analytics_hge.sql
+# loading sql script: 40_geom.sql
+# loading sql script: 40_json.sql
+# loading sql script: 40_json_hge.sql
+# loading sql script: 41_md5sum.sql
+# loading sql script: 45_uuid.sql
+# loading sql script: 46_profiler.sql
+# loading sql script: 51_sys_schema_extension.sql
+# loading sql script: 60_wlcr.sql
+# loading sql script: 72_fits.sql
+# loading sql script: 74_netcdf.sql
+# loading sql script: 75_lidar.sql
+# loading sql script: 75_shp.sql
+# loading sql script: 75_storagemodel.sql
+# loading sql script: 80_statistics.sql
+# loading sql script: 80_udf.sql
+# loading sql script: 80_udf_hge.sql
+# loading sql script: 85_bam.sql
+# loading sql script: 90_generator.sql
+# loading sql script: 90_generator_hge.sql
+# loading sql script: 99_system.sql
+# MonetDB/SQL module loaded
+
+Ready.
+
+# 15:51:44 >
+# 15:51:44 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-20542" "--port=37407"
+# 15:51:44 >
+
+#create table testing (aa int, bb int, cc bigint);
+#insert into testing values (15, 3, 15), (3, 1, 3), (2, 1, 2), (5, 3, 5),
(NULL, 0, NULL), (3, 0, 3), (4, 1, 4), (6, 3, 6), (8, 0, 8), (NULL, 4, NULL);
+[ 10 ]
+#start transaction;
+#select count(aa) over (partition by bb), 75 + count(aa) over (partition by
bb) from testing where bb <> 1;
+% sys.L4, sys.L11 # table_name
+% L4, L11 # name
+% bigint, hugeint # type
+% 1, 2 # length
+[ 2, 77 ]
+[ 2, 77 ]
+[ 2, 77 ]
+[ 3, 78 ]
+[ 3, 78 ]
+[ 3, 78 ]
+[ 0, 75 ]
+#with relation as (select row_number() over () as dd, aa, bb from testing
where bb <> 1)
+#select aa, bb, dd,
+# count(aa) over (partition by bb rows between dd preceding and current
row),
+# count(aa) over (partition by bb rows between dd preceding and dd
following),
+# count(aa) over (partition by bb rows between dd + 1 preceding and dd
preceding) from relation where bb <> 1;
+% sys.relation, sys.relation, sys.relation, sys.L20,
sys.L24, sys.L30 # table_name
+% aa, bb, dd, L20, L24, L30 # name
+% int, int, int, bigint, bigint, bigint # type
+% 2, 1, 1, 1, 1, 1 # length
+[ 15, 0, 1, 1, 2, 0 ]
+[ 15, 0, 1, 2, 3, 1 ]
+[ 5, 0, 2, 3, 3, 1 ]
+[ 15, 3, 1, 1, 2, 0 ]
+[ 15, 3, 1, 2, 3, 1 ]
+[ 15, 3, 1, 2, 2, 2 ]
+[ NULL, 4, 3, 0, 0, 0 ]
+#rollback;
+#drop table testing;
+
+# 15:51:44 >
+# 15:51:44 > "Done."
+# 15:51:44 >
+
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
