Changeset: 22a4fdeda861 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=22a4fdeda861 Added Files: sql/backends/monet5/k3m/Tests/k3m.inserttree.sql sql/backends/monet5/k3m/Tests/k3m.inserttree.stable.err sql/backends/monet5/k3m/Tests/k3m.inserttree.stable.out sql/backends/monet5/k3m/Tests/k3m.singletable.sql sql/backends/monet5/k3m/Tests/k3m.singletable.stable.err sql/backends/monet5/k3m/Tests/k3m.singletable.stable.out Removed Files: sql/backends/monet5/k3m/Tests/k3m.sql sql/backends/monet5/k3m/Tests/k3m2.sql Modified Files: sql/backends/monet5/k3m/Tests/All Branch: k3match Log Message:
test cases and approved output diffs (truncated from 1713 to 300 lines): diff --git a/sql/backends/monet5/k3m/Tests/All b/sql/backends/monet5/k3m/Tests/All --- a/sql/backends/monet5/k3m/Tests/All +++ b/sql/backends/monet5/k3m/Tests/All @@ -1,1 +1,2 @@ -k3m +k3m.singletable +k3m.inserttree diff --git a/sql/backends/monet5/k3m/Tests/k3m.inserttree.sql b/sql/backends/monet5/k3m/Tests/k3m.inserttree.sql new file mode 100644 --- /dev/null +++ b/sql/backends/monet5/k3m/Tests/k3m.inserttree.sql @@ -0,0 +1,163 @@ +start transaction; + +DECLARE ds_deg, ds_rad, ds_rad_squared, isint2 DOUBLE; + +SET ds_deg = CAST(1 AS DOUBLE); /* units in degrees */ +SET ds_rad = PI() * ds_deg / 180; +SET ds_rad_squared = ds_rad * ds_rad; +SET isint2 = 4 * SIN ( RADIANS (0.5 * ds_deg )) * SIN ( RADIANS (0.5 * ds_deg )); + +SELECT ds_deg AS ds_deg + ,3600 * ds_deg AS ds_arcsec + ,ds_rad AS ds_rad + ,ds_rad_squared AS "ds_rad_squared (input arg)" + ,isint2 AS "4 sin^2 \theta" +; + +create table catalog(id int, ra double, decl double); +insert into catalog values (1, 222.3, 79.5 ), (2, 122.3, 88.5), (3, 22.3, 79.5 ), (4, 88.0, 38.0); + +create table sourcelist(id int, ra double, decl double); +insert into sourcelist values (11, 22.305, 79.499 ), (12,122.305, 88.499), (13, 222.305, 79.499 ), (14, 98.05, 47.99 ); + +-- we need the seq pipe for freeing and building +set optimizer='sequential_pipe'; +select * from k3m_free(); + +select * from k3m_build((select id, ra*PI()/180, decl*PI()/180 from catalog as s)); + +-- After tree has been built we reset to def pipe +set optimizer='default_pipe'; + +select * from catalog; +select * from sourcelist; + +-- The counterparts, by k3m_query +select * + ,sqrt(dist) as dist_rad + ,180*sqrt(dist)/pi() as dist_deg + ,3600*180*sqrt(dist)/pi() as dist_arcsec + from k3m_query((select id, ra*PI()/180, decl*PI()/180, ds_rad_squared from sourcelist)) +order by idc; + +-- The counterparts, by plain sql. Apart from rounding errors, the results should be identical +select cid + ,sid + ,power(dist_rad,2) as dist + ,dist_rad + ,degrees(dist_rad ) AS dist_deg + ,3600*degrees(dist_rad ) AS dist_arcsec + from (select c.id as cid + ,s.id as sid + ,2 * ASIN(SQRT( power( (COS(RADIANS(c.decl)) * COS(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * COS(RADIANS(s.ra))), 2) + + power( (COS(RADIANS(c.decl)) * SIN(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * SIN(RADIANS(s.ra))), 2) + + power( (SIN(RADIANS(c.decl)) - SIN(RADIANS(s.decl))), 2) + ) / 2) as dist_rad + from catalog c + ,sourcelist s + where power( (COS(RADIANS(c.decl)) * COS(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * COS(RADIANS(s.ra))) , 2) + + power( (COS(RADIANS(c.decl)) * SIN(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * SIN(RADIANS(s.ra))), 2) + + power( (SIN(RADIANS(c.decl)) - SIN(RADIANS(s.decl))), 2) < isint2 + ) t +order by cid +; + +-- Now we add new sources to the existing tree +create table catalog2(id int, ra double, decl double); +insert into catalog2 values (5, 122.3, 89.5 ), (6, 32.3, 98.5), (7, 32.3, 89.5 ), (8, 98.0, 48.0); + +set optimizer='sequential_pipe'; +select * from k3m_build((select id, ra*PI()/180, decl*PI()/180 from catalog2 as s)); + +-- After tree has been expanded we reset to def pipe +set optimizer='default_pipe'; + +-- The counterparts, by k3m_query +select * + ,sqrt(dist) as dist_rad + ,180*sqrt(dist)/pi() as dist_deg + ,3600*180*sqrt(dist)/pi() as dist_arcsec + from k3m_query((select id, ra*PI()/180, decl*PI()/180, ds_rad_squared from sourcelist)) +order by idc; + +-- The counterparts, by plain sql. Apart from rounding errors, the results should be identical +select cid + ,sid + ,power(dist_rad,2) as dist + ,dist_rad + ,degrees(dist_rad ) AS dist_deg + ,3600*degrees(dist_rad ) AS dist_arcsec + from (select c.id as cid + ,s.id as sid + ,2 * ASIN(SQRT( power( (COS(RADIANS(c.decl)) * COS(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * COS(RADIANS(s.ra))), 2) + + power( (COS(RADIANS(c.decl)) * SIN(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * SIN(RADIANS(s.ra))), 2) + + power( (SIN(RADIANS(c.decl)) - SIN(RADIANS(s.decl))), 2) + ) / 2) as dist_rad + from catalog c + ,sourcelist s + where power( (COS(RADIANS(c.decl)) * COS(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * COS(RADIANS(s.ra))) , 2) + + power( (COS(RADIANS(c.decl)) * SIN(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * SIN(RADIANS(s.ra))), 2) + + power( (SIN(RADIANS(c.decl)) - SIN(RADIANS(s.decl))), 2) < isint2 + union all + select c.id as cid + ,s.id as sid + ,2 * ASIN(SQRT( power( (COS(RADIANS(c.decl)) * COS(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * COS(RADIANS(s.ra))), 2) + + power( (COS(RADIANS(c.decl)) * SIN(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * SIN(RADIANS(s.ra))), 2) + + power( (SIN(RADIANS(c.decl)) - SIN(RADIANS(s.decl))), 2) + ) / 2) as dist_rad + from catalog2 c + ,sourcelist s + where power( (COS(RADIANS(c.decl)) * COS(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * COS(RADIANS(s.ra))) , 2) + + power( (COS(RADIANS(c.decl)) * SIN(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * SIN(RADIANS(s.ra))), 2) + + power( (SIN(RADIANS(c.decl)) - SIN(RADIANS(s.decl))), 2) < isint2 + ) t +order by cid +; + +create table catalog_union as select id, ra, decl from catalog + union all + select id, ra, decl from catalog2 with data; + +-- we need the seq pipe for freeing and building +set optimizer='sequential_pipe'; +select * from k3m_free(); + +select * from k3m_build((select id, ra*PI()/180, decl*PI()/180 from catalog_union as s)); + +-- After tree has been built we reset to def pipe +set optimizer='default_pipe'; + +select * from catalog_union; +select * from sourcelist; + +-- The counterparts, by k3m_query +select * + ,sqrt(dist) as dist_rad + ,180*sqrt(dist)/pi() as dist_deg + ,3600*180*sqrt(dist)/pi() as dist_arcsec + from k3m_query((select id, ra*PI()/180, decl*PI()/180, ds_rad_squared from sourcelist)) +order by idc; + +-- The counterparts, by plain sql. Apart from rounding errors, the results should be identical +select cid + ,sid + ,power(dist_rad,2) as dist + ,dist_rad + ,degrees(dist_rad ) AS dist_deg + ,3600*degrees(dist_rad ) AS dist_arcsec + from (select c.id as cid + ,s.id as sid + ,2 * ASIN(SQRT( power( (COS(RADIANS(c.decl)) * COS(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * COS(RADIANS(s.ra))), 2) + + power( (COS(RADIANS(c.decl)) * SIN(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * SIN(RADIANS(s.ra))), 2) + + power( (SIN(RADIANS(c.decl)) - SIN(RADIANS(s.decl))), 2) + ) / 2) as dist_rad + from catalog_union c + ,sourcelist s + where power( (COS(RADIANS(c.decl)) * COS(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * COS(RADIANS(s.ra))) , 2) + + power( (COS(RADIANS(c.decl)) * SIN(RADIANS(c.ra)) - COS(RADIANS(s.decl)) * SIN(RADIANS(s.ra))), 2) + + power( (SIN(RADIANS(c.decl)) - SIN(RADIANS(s.decl))), 2) < isint2 + ) t +order by cid +; + +ROLLBACK; diff --git a/sql/backends/monet5/k3m/Tests/k3m.inserttree.stable.err b/sql/backends/monet5/k3m/Tests/k3m.inserttree.stable.err new file mode 100644 --- /dev/null +++ b/sql/backends/monet5/k3m/Tests/k3m.inserttree.stable.err @@ -0,0 +1,35 @@ +stderr of test 'k3m.inserttree` in directory 'sql/backends/monet5/k3m` itself: + + +# 14:52:04 > +# 14:52:04 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=37509" "--set" "mapi_usock=/var/tmp/mtest-68304/.s.monetdb.37509" "--set" "monet_prompt=" "--forcemito" "--dbpath=/tmp/fuckit/var/MonetDB/mTests_sql_backends_monet5_k3m" "--set" "embedded_r=yes" +# 14:52:04 > + +# builtin opt gdk_dbpath = /tmp/fuckit/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 = 37509 +# cmdline opt mapi_usock = /var/tmp/mtest-68304/.s.monetdb.37509 +# cmdline opt monet_prompt = +# cmdline opt gdk_dbpath = /tmp/fuckit/var/MonetDB/mTests_sql_backends_monet5_k3m +# cmdline opt embedded_r = yes +# cmdline opt gdk_debug = 536870922 + +# 14:52:04 > +# 14:52:04 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-68304" "--port=37509" +# 14:52:04 > + + +# 14:52:05 > +# 14:52:05 > "Done." +# 14:52:05 > + diff --git a/sql/backends/monet5/k3m/Tests/k3m.inserttree.stable.out b/sql/backends/monet5/k3m/Tests/k3m.inserttree.stable.out new file mode 100644 --- /dev/null +++ b/sql/backends/monet5/k3m/Tests/k3m.inserttree.stable.out @@ -0,0 +1,897 @@ +stdout of test 'k3m.inserttree` in directory 'sql/backends/monet5/k3m` itself: + + +# 14:52:04 > +# 14:52:04 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=37509" "--set" "mapi_usock=/var/tmp/mtest-68304/.s.monetdb.37509" "--set" "monet_prompt=" "--forcemito" "--dbpath=/tmp/fuckit/var/MonetDB/mTests_sql_backends_monet5_k3m" "--set" "embedded_r=yes" +# 14:52:04 > + +# MonetDB 5 server v11.22.0 +# This is an unreleased version +# Serving database 'mTests_sql_backends_monet5_k3m', using 4 threads +# Compiled for x86_64-apple-darwin15.3.0/64bit with 64bit OIDs and 128bit integers dynamically linked +# Found 16.000 GiB available main-memory. +# Copyright (c) 1993-July 2008 CWI. +# Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved +# Visit http://www.monetdb.org/ for further information +# Listening for connection requests on mapi:monetdb://dakar.da.cwi.nl:37509/ +# Listening for UNIX domain connection requests on mapi:monetdb:///var/tmp/mtest-68304/.s.monetdb.37509 +# MonetDB/GIS module loaded +# Start processing logs sql/sql_logs version 52200 +# Start reading the write-ahead log 'sql_logs/sql/log.3' +# Finished reading the write-ahead log 'sql_logs/sql/log.3' +# Finished processing logs sql/sql_logs +# MonetDB/SQL module loaded +# MonetDB/R module loaded + +Ready. +#WARNING To speedup calc.* a bulk operator implementation is needed +# X_234:bat[:dbl] := mal.multiplex("calc":str,"*":str,X_214:bat[:dbl],X_230:bat[:dbl]); +#WARNING To speedup calc.* a bulk operator implementation is needed +# X_278:bat[:dbl] := mal.multiplex("calc":str,"*":str,X_254:bat[:dbl],X_274:bat[:dbl]); +#WARNING To speedup calc.- a bulk operator implementation is needed +# X_282:bat[:dbl] := mal.multiplex("calc":str,"-":str,X_234:bat[:dbl],X_278:bat[:dbl]); +#WARNING To speedup calc.* a bulk operator implementation is needed +# X_314:bat[:dbl] := mal.multiplex("calc":str,"*":str,X_298:bat[:dbl],X_310:bat[:dbl]); +#WARNING To speedup calc.* a bulk operator implementation is needed +# X_342:bat[:dbl] := mal.multiplex("calc":str,"*":str,X_326:bat[:dbl],X_338:bat[:dbl]); +#WARNING To speedup calc.- a bulk operator implementation is needed +# X_346:bat[:dbl] := mal.multiplex("calc":str,"-":str,X_314:bat[:dbl],X_342:bat[:dbl]); +#WARNING To speedup calc.+ a bulk operator implementation is needed +# X_354:bat[:dbl] := mal.multiplex("calc":str,"+":str,X_286:bat[:dbl],X_350:bat[:dbl]); +#WARNING To speedup calc.- a bulk operator implementation is needed +# X_382:bat[:dbl] := mal.multiplex("calc":str,"-":str,X_366:bat[:dbl],X_378:bat[:dbl]); +#WARNING To speedup calc.+ a bulk operator implementation is needed +# X_390:bat[:dbl] := mal.multiplex("calc":str,"+":str,X_354:bat[:dbl],X_386:bat[:dbl]); +#WARNING To speedup calc.* a bulk operator implementation is needed +# X_233:bat[:dbl] := mal.multiplex("calc":str,"*":str,X_213:bat[:dbl],X_229:bat[:dbl]); +#WARNING To speedup calc.* a bulk operator implementation is needed +# X_277:bat[:dbl] := mal.multiplex("calc":str,"*":str,X_253:bat[:dbl],X_273:bat[:dbl]); +#WARNING To speedup calc.- a bulk operator implementation is needed +# X_281:bat[:dbl] := mal.multiplex("calc":str,"-":str,X_233:bat[:dbl],X_277:bat[:dbl]); +#WARNING To speedup calc.* a bulk operator implementation is needed +# X_313:bat[:dbl] := mal.multiplex("calc":str,"*":str,X_297:bat[:dbl],X_309:bat[:dbl]); +#WARNING To speedup calc.* a bulk operator implementation is needed +# X_341:bat[:dbl] := mal.multiplex("calc":str,"*":str,X_325:bat[:dbl],X_337:bat[:dbl]); +#WARNING To speedup calc.- a bulk operator implementation is needed +# X_345:bat[:dbl] := mal.multiplex("calc":str,"-":str,X_313:bat[:dbl],X_341:bat[:dbl]); +#WARNING To speedup calc.+ a bulk operator implementation is needed +# X_353:bat[:dbl] := mal.multiplex("calc":str,"+":str,X_285:bat[:dbl],X_349:bat[:dbl]); +#WARNING To speedup calc.- a bulk operator implementation is needed +# X_381:bat[:dbl] := mal.multiplex("calc":str,"-":str,X_365:bat[:dbl],X_377:bat[:dbl]); +#WARNING To speedup calc.+ a bulk operator implementation is needed +# X_389:bat[:dbl] := mal.multiplex("calc":str,"+":str,X_353:bat[:dbl],X_385:bat[:dbl]); +#WARNING To speedup calc.* a bulk operator implementation is needed +# X_232:bat[:dbl] := mal.multiplex("calc":str,"*":str,X_212:bat[:dbl],X_228:bat[:dbl]); +#WARNING To speedup calc.* a bulk operator implementation is needed +# X_276:bat[:dbl] := mal.multiplex("calc":str,"*":str,X_252:bat[:dbl],X_272:bat[:dbl]); +#WARNING To speedup calc.- a bulk operator implementation is needed +# X_280:bat[:dbl] := mal.multiplex("calc":str,"-":str,X_232:bat[:dbl],X_276:bat[:dbl]); +#WARNING To speedup calc.* a bulk operator implementation is needed +# X_312:bat[:dbl] := mal.multiplex("calc":str,"*":str,X_296:bat[:dbl],X_308:bat[:dbl]); +#WARNING To speedup calc.* a bulk operator implementation is needed +# X_340:bat[:dbl] := mal.multiplex("calc":str,"*":str,X_324:bat[:dbl],X_336:bat[:dbl]); +#WARNING To speedup calc.- a bulk operator implementation is needed +# X_344:bat[:dbl] := mal.multiplex("calc":str,"-":str,X_312:bat[:dbl],X_340:bat[:dbl]); +#WARNING To speedup calc.+ a bulk operator implementation is needed +# X_352:bat[:dbl] := mal.multiplex("calc":str,"+":str,X_284:bat[:dbl],X_348:bat[:dbl]); +#WARNING To speedup calc.- a bulk operator implementation is needed +# X_380:bat[:dbl] := mal.multiplex("calc":str,"-":str,X_364:bat[:dbl],X_376:bat[:dbl]); +#WARNING To speedup calc.+ a bulk operator implementation is needed +# X_388:bat[:dbl] := mal.multiplex("calc":str,"+":str,X_352:bat[:dbl],X_384:bat[:dbl]); _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
