Changeset: 54a8c9d99dc3 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=54a8c9d99dc3 Added Files: sql/test/BugTracker-2017/Tests/nestedoperators.Bug-6292.sql Modified Files: sql/test/BugTracker-2017/Tests/All Branch: default Log Message:
Merge with Dec2016 branch. diffs (57 lines): diff --git a/sql/test/BugTracker-2017/Tests/All b/sql/test/BugTracker-2017/Tests/All --- a/sql/test/BugTracker-2017/Tests/All +++ b/sql/test/BugTracker-2017/Tests/All @@ -45,3 +45,4 @@ wrong_aggregation_count.Bug-6257 select.Bug-6259 function_and_mergetable.Bug-6288 crash_in_in_handling.Bug-6260 +nestedoperators.Bug-6292 diff --git a/sql/test/BugTracker-2017/Tests/nestedoperators.Bug-6292.sql b/sql/test/BugTracker-2017/Tests/nestedoperators.Bug-6292.sql new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2017/Tests/nestedoperators.Bug-6292.sql @@ -0,0 +1,44 @@ +CREATE TABLE rooms( + time timestamp, + room string, + level integer, + temp double, + PRIMARY KEY(time, room,level) ); + +insert into rooms values +(timestamp '2017/01/01 09:00:00.000', 'L302', 3, 21.3), +(timestamp '2017/01/01 09:00:15.000', 'L302', 3, 21.3), +(timestamp '2017/01/01 09:00:30.000', 'L302', 3, 21.4), +(timestamp '2017/01/01 09:00:45.000', 'L302', 3, 21.5), +(timestamp '2017/01/01 10:00:00.000', 'L302', 3, 21.4), +(timestamp '2017/01/01 10:00:15.000', 'L302', 3, 21.4), +(timestamp '2017/01/01 10:00:30.000', 'L302', 3, 21.4), +(timestamp '2017/01/01 10:00:45.000', 'L302', 3, 21.5), +(timestamp '2017/01/01 11:00:00.000', 'L302', 3, 21.4), +(timestamp '2017/01/01 11:00:15.000', 'L302', 3, 21.4), +(timestamp '2017/01/01 11:00:30.000', 'L302', 3, 21.4), +(timestamp '2017/01/01 11:00:45.000', 'L302', 3, 21.5); + +--derivative +WITH bounds(first, last, period) +AS (SELECT min(time) AS mintime, max(time) as maxtime, epoch(time)/60 AS period FROM rooms GROUP BY period) +SELECT r2.time, r2.room, r2.level, (r2.temp - r1.temp)/ (epoch(bounds.last) - epoch(bounds.first)) FROM bounds, rooms r1, rooms r2 +WHERE r1.time = bounds.first and r2.time = bounds.last and r1.room = r2.room and r1.level = r2.level; + +--derivative function with hardcoded stride +--causes an infinite loop. +CREATE FUNCTION rooms_derivative( stride bigint) +RETURNS TABLE( + time timestamp, + room string, + level integer, + temp double) +BEGIN + RETURN + WITH bounds(first, last, period) + AS (SELECT min(time) AS mintime, max(time) as maxtime, epoch(time)/60 AS period FROM rooms GROUP BY period) + SELECT r2.time, r2.room, r2.level, (r2.temp - r1.temp)/ (epoch(bounds.last) - epoch(bounds.first)) FROM bounds, rooms r1, rooms r2 + WHERE r1.time = bounds.first and r2.time = bounds.last and r1.room = r2.room and r1.level = r2.level; +END; + +DROP TABLE rooms; _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
