Changeset: 89dcc0c35652 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=89dcc0c35652
Added Files:
sql/test/analytics/Tests/analytics14.sql
sql/test/analytics/Tests/analytics14.stable.err
sql/test/analytics/Tests/analytics14.stable.out
Modified Files:
sql/test/analytics/Tests/All
Branch: statistics-analytics
Log Message:
Approved output
diffs (279 lines):
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
@@ -15,3 +15,6 @@ analytics10
analytics11
analytics12
analytics13
+
+# Statistical window function tests
+analytics14
diff --git a/sql/test/analytics/Tests/analytics14.sql
b/sql/test/analytics/Tests/analytics14.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/analytics/Tests/analytics14.sql
@@ -0,0 +1,20 @@
+start transaction;
+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 stddev_samp(aa) over (partition by bb) from analytics;
+select stddev_samp(aa) over (partition by bb order by bb asc) from analytics;
+select stddev_samp(aa) over (partition by bb order by bb desc) from analytics;
+select stddev_samp(aa) over (order by bb desc) from analytics;
+
+select stddev_samp(cc) over (partition by bb) from analytics;
+select stddev_samp(cc) over (partition by bb order by bb asc) from analytics;
+select stddev_samp(cc) over (partition by bb order by bb desc) from analytics;
+select stddev_samp(cc) over (order by bb desc) 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);
+
+select stddev_samp(aa) over (partition by bb) from stressme; --error,
stddev_samp not available for string type
+
+rollback;
diff --git a/sql/test/analytics/Tests/analytics14.stable.err
b/sql/test/analytics/Tests/analytics14.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/analytics/Tests/analytics14.stable.err
@@ -0,0 +1,36 @@
+stderr of test 'analytics14` in directory 'sql/test/analytics` itself:
+
+
+# 11:24:48 >
+# 11:24:48 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=32728" "--set"
"mapi_usock=/var/tmp/mtest-97238/.s.monetdb.32728" "--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-statistics-analytics/BUILD/var/MonetDB/mTests_sql_test_analytics"
"--set" "embedded_c=true"
+# 11:24:48 >
+
+# builtin opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-statistics-analytics/BUILD/var/monetdb5/dbfarm/demo
+# builtin opt mapi_port = 50000
+# builtin opt mapi_open = false
+# builtin opt mapi_ipv6 = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# builtin opt raw_strings = false
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 32728
+# cmdline opt mapi_usock = /var/tmp/mtest-97238/.s.monetdb.32728
+# cmdline opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-statistics-analytics/BUILD/var/MonetDB/mTests_sql_test_analytics
+# cmdline opt embedded_c = true
+#client2:!ERROR:ParseException:SQLparser:42000!SELECT: function 'stddev_samp'
not found
+
+# 11:24:49 >
+# 11:24:49 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-97238" "--port=32728"
+# 11:24:49 >
+
+MAPI = (monetdb) /var/tmp/mtest-97238/.s.monetdb.32728
+QUERY = select stddev_samp(aa) over (partition by bb) from stressme; --error,
stddev_samp not available for string type
+ERROR = !SELECT: function 'stddev_samp' not found
+CODE = 42000
+
+# 11:24:49 >
+# 11:24:49 > "Done."
+# 11:24:49 >
+
diff --git a/sql/test/analytics/Tests/analytics14.stable.out
b/sql/test/analytics/Tests/analytics14.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/analytics/Tests/analytics14.stable.out
@@ -0,0 +1,198 @@
+stdout of test 'analytics14` in directory 'sql/test/analytics` itself:
+
+
+# 11:24:48 >
+# 11:24:48 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=32728" "--set"
"mapi_usock=/var/tmp/mtest-97238/.s.monetdb.32728" "--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-statistics-analytics/BUILD/var/MonetDB/mTests_sql_test_analytics"
"--set" "embedded_c=true"
+# 11:24:48 >
+
+# MonetDB 5 server v11.36.0 (hg id: 928df7505c1a)
+# 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.498 GiB available main-memory of which we use 12.631 GiB
+# Copyright (c) 1993 - July 2008 CWI.
+# Copyright (c) August 2008 - 2020 MonetDB B.V., all rights reserved
+# Visit https://www.monetdb.org/ for further information
+# Listening for connection requests on
mapi:monetdb://localhost.localdomain:32728/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-97238/.s.monetdb.32728
+# 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: 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: 58_hot_snapshot.sql
+# loading sql script: 60_wlcr.sql
+# loading sql script: 61_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
+
+# 11:24:49 >
+# 11:24:49 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-97238" "--port=32728"
+# 11:24:49 >
+
+#start transaction;
+#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);
+[ 10 ]
+#select stddev_samp(aa) over (partition by bb) from analytics;
+% sys.%3 # table_name
+% %3 # name
+% double # type
+% 24 # length
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 3.535533906 ]
+[ 3.535533906 ]
+[ 3.535533906 ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ NULL ]
+#select stddev_samp(aa) over (partition by bb order by bb asc) from analytics;
+% sys.%3 # table_name
+% %3 # name
+% double # type
+% 24 # length
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 3.535533906 ]
+[ 3.535533906 ]
+[ 3.535533906 ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ NULL ]
+#select stddev_samp(aa) over (partition by bb order by bb desc) from analytics;
+% sys.%3 # table_name
+% %3 # name
+% double # type
+% 24 # length
+[ NULL ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ 3.535533906 ]
+[ 3.535533906 ]
+[ 3.535533906 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+#select stddev_samp(aa) over (order by bb desc) from analytics;
+% sys.%3 # table_name
+% %3 # name
+% double # type
+% 24 # length
+[ NULL ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ 4.615192304 ]
+[ 4.615192304 ]
+[ 4.615192304 ]
+[ 4.200340122 ]
+[ 4.200340122 ]
+[ 4.200340122 ]
+#select stddev_samp(cc) over (partition by bb) from analytics;
+% sys.%3 # table_name
+% %3 # name
+% double # type
+% 24 # length
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 3.535533906 ]
+[ 3.535533906 ]
+[ 3.535533906 ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ NULL ]
+#select stddev_samp(cc) over (partition by bb order by bb asc) from analytics;
+% sys.%3 # table_name
+% %3 # name
+% double # type
+% 24 # length
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 3.535533906 ]
+[ 3.535533906 ]
+[ 3.535533906 ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ NULL ]
+#select stddev_samp(cc) over (partition by bb order by bb desc) from analytics;
+% sys.%3 # table_name
+% %3 # name
+% double # type
+% 24 # length
+[ NULL ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ 3.535533906 ]
+[ 3.535533906 ]
+[ 3.535533906 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+#select stddev_samp(cc) over (order by bb desc) from analytics;
+% sys.%3 # table_name
+% %3 # name
+% double # type
+% 24 # length
+[ NULL ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ 5.507570547 ]
+[ 4.615192304 ]
+[ 4.615192304 ]
+[ 4.615192304 ]
+[ 4.200340122 ]
+[ 4.200340122 ]
+[ 4.200340122 ]
+#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);
+[ 9 ]
+#rollback;
+
+# 11:24:49 >
+# 11:24:49 > "Done."
+# 11:24:49 >
+
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
