Changeset: b76b413d0151 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b76b413d0151 Modified Files: sql/backends/monet5/generator/Tests/joins01.sql Branch: generator Log Message:
Temporal join tests diffs (39 lines): diff --git a/sql/backends/monet5/generator/Tests/joins01.sql b/sql/backends/monet5/generator/Tests/joins01.sql --- a/sql/backends/monet5/generator/Tests/joins01.sql +++ b/sql/backends/monet5/generator/Tests/joins01.sql @@ -1,4 +1,32 @@ -select * from generate_series(0,3,1) as A, generate_series(0,6,2) as B; +-- To be done tests. +-- Using an 'int' rather then a 'tinyint' calls for casting the generated values first +-- The two join cases illustrate how a join could be optimized by 'looking' up the correct value. -select * from generate_series(0,3,1) as A, generate_series(0,6,2) as B -where A.value < B.value; +create table tmp(i timestamp); +insert into tmp values +(timestamp '2008-03-01 00:00'), +(timestamp '2008-03-01 10:00'), +(timestamp '2008-03-01 20:00'); +select * from tmp; + +select * from generate_series(timestamp '2008-03-01 00:00',timestamp '2008-03-04 12:00',cast( '10' as interval hour)) X, tmp Y where X.value = Y.i; +select * from generate_series(timestamp '2008-03-01 00:00',timestamp '2008-03-04 12:00',cast( '10' as interval hour)) X, tmp Y where Y.i = X.value; + +select * from generate_series(timestamp '2008-03-01 00:00',timestamp '2008-03-04 12:00',cast( '10' as interval hour)) X, tmp Y where X.value = Y.i and value < timestamp '2008-03-01 20:00'; +select * from generate_series(timestamp '2008-03-01 00:00',timestamp '2008-03-04 12:00',cast( '10' as interval hour)) X, tmp Y where Y.i = X.value and value < timestamp '2008-03-01 20:00'; + +select * from generate_series(timestamp '2008-03-01 00:00',timestamp '2008-03-04 12:00',cast( '10' as interval hour)) as X, tmp Y where X.value = Y.i and value < timestamp '2008-03-01 20:00' and value > timestamp '200-03-01 00:00'; + +-- negative range +select * from generate_series(timestamp '2008-03-04 18:00',timestamp '2008-03-01 00:00',cast( '-10' as interval hour)) X; +select * from generate_series(timestamp '2008-03-04 18:00',timestamp '2008-03-01 00:00',cast( '-10' as interval hour)) X, tmp Y where X.value = Y.i; +select * from generate_series(timestamp '2008-03-04 18:00',timestamp '2008-03-01 00:00',cast( '-10' as interval hour)) X, tmp Y where Y.i = X.value; + +select * from generate_series(timestamp '2008-03-04 18:00',timestamp '2008-03-01 00:00',cast( '-10' as interval hour)) X, tmp Y where X.value = Y.i +and value > timestamp '2008-03-01 09:00'; + +select * from generate_series(timestamp '2008-03-04 18:00',timestamp '2008-03-01 00:00',cast( '-10' as interval hour)) X, tmp Y where X.value = Y.i +and value > timestamp '2008-03-01 09:00' +and value < timestamp '2008-03-01 20:00'; + +drop table tmp; _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
