Changeset: b1e60ea29862 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b1e60ea29862 Added Files: sql/test/miscellaneous/Tests/vessels.sql sql/test/miscellaneous/Tests/vessels.stable.err sql/test/miscellaneous/Tests/vessels.stable.out Modified Files: sql/test/miscellaneous/Tests/All Branch: Jun2020 Log Message:
Approved output diffs (120 lines): diff --git a/sql/test/miscellaneous/Tests/All b/sql/test/miscellaneous/Tests/All --- a/sql/test/miscellaneous/Tests/All +++ b/sql/test/miscellaneous/Tests/All @@ -16,3 +16,4 @@ deallocate table_udf_missing_var create_func_temp simple_plans +vessels diff --git a/sql/test/miscellaneous/Tests/vessels.sql b/sql/test/miscellaneous/Tests/vessels.sql new file mode 100644 --- /dev/null +++ b/sql/test/miscellaneous/Tests/vessels.sql @@ -0,0 +1,34 @@ +start transaction; +create table vessels (type int, id int, lat real, log real, nav_status int, sog int, rot int, timestamp timestamp, insertion_time timestamp); +CREATE VIEW distinct_vessels AS + SELECT type, id, lat, log, nav_status, sog, rot, timestamp, insertion_time from vessels + WHERE (timestamp, id) IN (SELECT max(timestamp), id FROM vessels GROUP BY id); + +CREATE FUNCTION distance_in_km_quick (p1_lat REAL, p1_log REAL, p2_lat REAL, p2_log REAL) +RETURNS REAL +BEGIN + RETURN 111.319 * + SQRT( + (p2_lat-p1_lat) * + (p2_lat-p1_lat) + + ((p2_log-p1_log) * cos((p2_lat+p1_lat)*0.00872664626)) * + ((p2_log-p1_log) * cos((p2_lat+p1_lat)*0.00872664626)) + ); +END; + +WITH distance AS ( + SELECT v1.id AS ship, v2.id AS neighbour_ship, distance_in_km_quick(v1.lat,v1.log,v2.lat,v2.log) AS distance_in_km + FROM distinct_vessels AS v1 + JOIN distinct_vessels AS v2 ON v1.id <> v2.id + ) + SELECT current_timestamp AS calc_time, ship, neighbour_ship, distance_in_km FROM distance + WHERE (distance_in_km) IN (SELECT min(distance_in_km) FROM distance GROUP BY ship); + +WITH distance AS ( + SELECT v1.id AS ship, v2.id AS neighbour_ship, distance_in_km_quick(v1.lat,v1.log,v2.lat,v2.log) AS distance_in_km + FROM distinct_vessels AS v1 + JOIN distinct_vessels AS v2 ON v1.id <> v2.id + ) + SELECT current_timestamp AS calc_time, ship, neighbour_ship, distance_in_km FROM distance + WHERE (ship, distance_in_km) IN (SELECT ship, min(distance_in_km) FROM distance GROUP BY ship); +rollback; diff --git a/sql/test/miscellaneous/Tests/vessels.stable.err b/sql/test/miscellaneous/Tests/vessels.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/miscellaneous/Tests/vessels.stable.err @@ -0,0 +1,12 @@ +stderr of test 'vessels` in directory 'sql/test/miscellaneous` itself: + + +# 17:20:32 > +# 17:20:32 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-683674" "--port=37229" +# 17:20:32 > + + +# 17:20:32 > +# 17:20:32 > "Done." +# 17:20:32 > + diff --git a/sql/test/miscellaneous/Tests/vessels.stable.out b/sql/test/miscellaneous/Tests/vessels.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/miscellaneous/Tests/vessels.stable.out @@ -0,0 +1,51 @@ +stdout of test 'vessels` in directory 'sql/test/miscellaneous` itself: + + +# 17:20:32 > +# 17:20:32 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-683674" "--port=37229" +# 17:20:32 > + +#start transaction; +#create table vessels (type int, id int, lat real, log real, nav_status int, sog int, rot int, timestamp timestamp, insertion_time timestamp); +#CREATE VIEW distinct_vessels AS +# SELECT type, id, lat, log, nav_status, sog, rot, timestamp, insertion_time from vessels +# WHERE (timestamp, id) IN (SELECT max(timestamp), id FROM vessels GROUP BY id); +#CREATE FUNCTION distance_in_km_quick (p1_lat REAL, p1_log REAL, p2_lat REAL, p2_log REAL) +#RETURNS REAL +#BEGIN +# RETURN 111.319 * +# SQRT( +# (p2_lat-p1_lat) * +# (p2_lat-p1_lat) + +# ((p2_log-p1_log) * cos((p2_lat+p1_lat)*0.00872664626)) * +# ((p2_log-p1_log) * cos((p2_lat+p1_lat)*0.00872664626)) +# ); +#END; +#WITH distance AS ( +# SELECT v1.id AS ship, v2.id AS neighbour_ship, distance_in_km_quick(v1.lat,v1.log,v2.lat,v2.log) AS distance_in_km +# FROM distinct_vessels AS v1 +# JOIN distinct_vessels AS v2 ON v1.id <> v2.id +# ) +# SELECT current_timestamp AS calc_time, ship, neighbour_ship, distance_in_km FROM distance +# WHERE (distance_in_km) IN (SELECT min(distance_in_km) FROM distance GROUP BY ship); +% ., sys.distance, sys.distance, sys.distance # table_name +% calc_time, ship, neighbour_ship, distance_in_km # name +% timestamptz, int, int, real # type +% 32, 1, 1, 15 # length +#WITH distance AS ( +# SELECT v1.id AS ship, v2.id AS neighbour_ship, distance_in_km_quick(v1.lat,v1.log,v2.lat,v2.log) AS distance_in_km +# FROM distinct_vessels AS v1 +# JOIN distinct_vessels AS v2 ON v1.id <> v2.id +# ) +# SELECT current_timestamp AS calc_time, ship, neighbour_ship, distance_in_km FROM distance +# WHERE (ship, distance_in_km) IN (SELECT ship, min(distance_in_km) FROM distance GROUP BY ship); +% ., sys.distance, sys.distance, sys.distance # table_name +% calc_time, ship, neighbour_ship, distance_in_km # name +% timestamptz, int, int, real # type +% 32, 1, 1, 15 # length +#rollback; + +# 17:20:32 > +# 17:20:32 > "Done." +# 17:20:32 > + _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
