This is an automated email from the ASF dual-hosted git repository. xiangweiwei pushed a commit to branch groupbymonth in repository https://gitbox.apache.org/repos/asf/iotdb.git
commit 0339f3405e6796a5fa394fc37397328932dcae49 Author: Alima777 <[email protected]> AuthorDate: Thu Apr 1 22:33:22 2021 +0800 fix group by month bug --- .../iotdb/db/query/executor/QueryRouter.java | 27 ++-- .../iotdb/db/integration/IOTDBGroupByIT.java | 24 ++-- .../iotdb/tsfile/read/filter/GroupByFilter.java | 11 +- .../tsfile/read/filter/GroupByMonthFilter.java | 145 +++++++++++++++++++++ .../tsfile/read/filter/GroupByMonthFilterTest.java | 126 ++++++++++++++++++ 5 files changed, 311 insertions(+), 22 deletions(-) diff --git a/server/src/main/java/org/apache/iotdb/db/query/executor/QueryRouter.java b/server/src/main/java/org/apache/iotdb/db/query/executor/QueryRouter.java index 3478c6c..287856e 100644 --- a/server/src/main/java/org/apache/iotdb/db/query/executor/QueryRouter.java +++ b/server/src/main/java/org/apache/iotdb/db/query/executor/QueryRouter.java @@ -44,6 +44,7 @@ import org.apache.iotdb.tsfile.read.expression.impl.BinaryExpression; import org.apache.iotdb.tsfile.read.expression.impl.GlobalTimeExpression; import org.apache.iotdb.tsfile.read.expression.util.ExpressionOptimizer; import org.apache.iotdb.tsfile.read.filter.GroupByFilter; +import org.apache.iotdb.tsfile.read.filter.GroupByMonthFilter; import org.apache.iotdb.tsfile.read.query.dataset.QueryDataSet; import org.slf4j.Logger; @@ -154,16 +155,9 @@ public class QueryRouter implements IQueryRouter { } GroupByEngineDataSet dataSet = null; - long unit = groupByTimePlan.getInterval(); - long slidingStep = groupByTimePlan.getSlidingStep(); - long startTime = groupByTimePlan.getStartTime(); - long endTime = groupByTimePlan.getEndTime(); - IExpression expression = groupByTimePlan.getExpression(); List<PartialPath> selectedSeries = groupByTimePlan.getDeduplicatedPaths(); - - GlobalTimeExpression timeExpression = - new GlobalTimeExpression(new GroupByFilter(unit, slidingStep, startTime, endTime)); + GlobalTimeExpression timeExpression = getTimeExpression(groupByTimePlan); if (expression == null) { expression = timeExpression; @@ -191,6 +185,23 @@ public class QueryRouter implements IQueryRouter { return dataSet; } + private GlobalTimeExpression getTimeExpression(GroupByTimePlan plan) { + if (plan.isSlidingStepByMonth() || plan.isIntervalByMonth()) { + return new GlobalTimeExpression( + (new GroupByMonthFilter( + plan.getInterval(), + plan.getSlidingStep(), + plan.getStartTime(), + plan.getEndTime(), + plan.isSlidingStepByMonth(), + plan.isIntervalByMonth()))); + } else { + return new GlobalTimeExpression( + new GroupByFilter( + plan.getInterval(), plan.getSlidingStep(), plan.getStartTime(), plan.getEndTime())); + } + } + protected GroupByWithoutValueFilterDataSet getGroupByWithoutValueFilterDataSet( QueryContext context, GroupByTimePlan plan) throws StorageEngineException, QueryProcessException { diff --git a/server/src/test/java/org/apache/iotdb/db/integration/IOTDBGroupByIT.java b/server/src/test/java/org/apache/iotdb/db/integration/IOTDBGroupByIT.java index 04a04ba..f23e0ad 100644 --- a/server/src/test/java/org/apache/iotdb/db/integration/IOTDBGroupByIT.java +++ b/server/src/test/java/org/apache/iotdb/db/integration/IOTDBGroupByIT.java @@ -29,14 +29,23 @@ import org.junit.Assert; import org.junit.Before; import org.junit.Test; -import java.sql.*; +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.ResultSet; +import java.sql.Statement; import java.text.DateFormat; import java.text.SimpleDateFormat; -import java.util.ArrayList; -import java.util.List; import java.util.TimeZone; -import static org.apache.iotdb.db.constant.TestConstant.*; +import static org.apache.iotdb.db.constant.TestConstant.avg; +import static org.apache.iotdb.db.constant.TestConstant.count; +import static org.apache.iotdb.db.constant.TestConstant.first_value; +import static org.apache.iotdb.db.constant.TestConstant.last_value; +import static org.apache.iotdb.db.constant.TestConstant.max_time; +import static org.apache.iotdb.db.constant.TestConstant.max_value; +import static org.apache.iotdb.db.constant.TestConstant.min_time; +import static org.apache.iotdb.db.constant.TestConstant.min_value; +import static org.apache.iotdb.db.constant.TestConstant.sum; import static org.junit.Assert.fail; public class IOTDBGroupByIT { @@ -973,15 +982,14 @@ public class IOTDBGroupByIT { "11/30/2019:19:57:18", "10.0", "12/31/2019:19:57:18", - "9.0", + "10.0", "01/31/2020:19:57:18", - "8.0", + "10.0", "02/29/2020:19:57:18", - "9.0", + "10.0", "03/31/2020:19:57:18", "1.0" }; - List<String> start = new ArrayList<>(); for (long i = startTime; i <= endTime; i += 86400_000L) { statement.execute("insert into root.sg1.d1(timestamp, temperature) values (" + i + ", 1)"); diff --git a/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/GroupByFilter.java b/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/GroupByFilter.java index 8018e92..d53b450 100644 --- a/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/GroupByFilter.java +++ b/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/GroupByFilter.java @@ -31,10 +31,10 @@ import java.util.Objects; public class GroupByFilter implements Filter, Serializable { private static final long serialVersionUID = -1211805021419281440L; - private long interval; - private long slidingStep; - private long startTime; - private long endTime; + protected long interval; + protected long slidingStep; + protected long startTime; + protected long endTime; public GroupByFilter(long interval, long slidingStep, long startTime, long endTime) { this.interval = interval; @@ -58,9 +58,8 @@ public class GroupByFilter implements Filter, Serializable { @Override public boolean satisfyStartEndTime(long startTime, long endTime) { - if (endTime < this.startTime) return false; + if (endTime < this.startTime || startTime >= this.endTime) return false; else if (startTime <= this.startTime) return true; - else if (startTime >= this.endTime) return false; else { long minTime = startTime - this.startTime; long count = minTime / slidingStep; diff --git a/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/GroupByMonthFilter.java b/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/GroupByMonthFilter.java new file mode 100644 index 0000000..68a69d3 --- /dev/null +++ b/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/GroupByMonthFilter.java @@ -0,0 +1,145 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.iotdb.tsfile.read.filter; + +import java.util.Calendar; + +/** + * GroupByMonthFilter is used to handle natural month slidingStep and interval by generating + * dynamically. Attention: it's only supported to access in ascending order now. + */ +public class GroupByMonthFilter extends GroupByFilter { + + private final boolean isSlidingStepByMonth; + private final boolean isIntervalByMonth; + private int slidingStepsInMo; + private int intervalInMo; + private final Calendar calendar = Calendar.getInstance(); + private final long MS_TO_MONTH = 30 * 86400_000L; + private int intervalCnt = 0; + /** 10.31 -> 11.30 -> 12.31, not 10.31 -> 11.30 -> 12.30 */ + private final long initialStartTime; + + public GroupByMonthFilter( + long interval, + long slidingStep, + long startTime, + long endTime, + boolean isSlidingStepByMonth, + boolean isIntervalByMonth) { + super(interval, slidingStep, startTime, endTime); + initialStartTime = startTime; + calendar.setTimeInMillis(startTime); + this.isIntervalByMonth = isIntervalByMonth; + this.isSlidingStepByMonth = isSlidingStepByMonth; + if (isIntervalByMonth) { + // TODO: 1mo1d + intervalInMo = (int) (interval / MS_TO_MONTH); + } + if (isSlidingStepByMonth) { + slidingStepsInMo = (int) (slidingStep / MS_TO_MONTH); + } + getNextIntervalAndSlidingStep(); + } + + // TODO: time descending order + @Override + public boolean satisfy(long time, Object value) { + if (time < startTime || time >= endTime) { + return false; + } else if (time - startTime < interval) { + return true; + } else { + this.startTime = calendar.getTimeInMillis(); + getNextIntervalAndSlidingStep(); + return satisfy(time, value); + } + } + + @Override + public boolean satisfyStartEndTime(long startTime, long endTime) { + boolean isSatisfy = satisfyCurrentInterval(startTime, endTime); + if (isSatisfy) { + return true; + } else { + long beforeStartTime = this.startTime; + int beforeIntervalCnt = this.intervalCnt; + // TODO: optimize to jump but not one by one + while (endTime >= this.startTime && !isSatisfy) { + this.startTime = calendar.getTimeInMillis(); + getNextIntervalAndSlidingStep(); + isSatisfy = satisfyCurrentInterval(startTime, endTime); + } + // recover the initial state + this.intervalCnt = beforeIntervalCnt - 1; + this.startTime = beforeStartTime; + getNextIntervalAndSlidingStep(); + return isSatisfy; + } + } + + private boolean satisfyCurrentInterval(long startTime, long endTime) { + if (endTime < this.startTime || startTime >= this.endTime) { + return false; + } else return startTime <= this.startTime || startTime - this.startTime < interval; + } + + @Override + public boolean containStartEndTime(long startTime, long endTime) { + boolean isContained = isContainedByCurrentInterval(startTime, endTime); + if (isContained) { + return true; + } else { + long beforeStartTime = this.startTime; + int beforeIntervalCnt = this.intervalCnt; + while (!isContained && startTime >= this.startTime) { + this.startTime = calendar.getTimeInMillis(); + getNextIntervalAndSlidingStep(); + isContained = isContainedByCurrentInterval(startTime, endTime); + } + // recover the initial state + this.intervalCnt = beforeIntervalCnt - 1; + this.startTime = beforeStartTime; + getNextIntervalAndSlidingStep(); + return isContained; + } + } + + private boolean isContainedByCurrentInterval(long startTime, long endTime) { + if (startTime < this.startTime || endTime > this.endTime) { + return false; + } else { + return startTime - this.startTime < interval && endTime - this.startTime < interval; + } + } + + private void getNextIntervalAndSlidingStep() { + intervalCnt++; + if (isIntervalByMonth) { + calendar.setTimeInMillis(initialStartTime); + calendar.add(Calendar.MONTH, slidingStepsInMo * (intervalCnt - 1) + intervalInMo); + this.interval = calendar.getTimeInMillis() - startTime; + } + if (isSlidingStepByMonth) { + calendar.setTimeInMillis(initialStartTime); + calendar.add(Calendar.MONTH, slidingStepsInMo * intervalCnt); + this.slidingStep = calendar.getTimeInMillis() - startTime; + } + } +} diff --git a/tsfile/src/test/java/org/apache/iotdb/tsfile/read/filter/GroupByMonthFilterTest.java b/tsfile/src/test/java/org/apache/iotdb/tsfile/read/filter/GroupByMonthFilterTest.java new file mode 100644 index 0000000..0f34c8f --- /dev/null +++ b/tsfile/src/test/java/org/apache/iotdb/tsfile/read/filter/GroupByMonthFilterTest.java @@ -0,0 +1,126 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.iotdb.tsfile.read.filter; + +import org.junit.Test; + +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertTrue; + +public class GroupByMonthFilterTest { + + // The number of milliseconds in 30 days + private final long MS_TO_DAY = 86400_000L; + private final long MS_TO_MONTH = 30 * MS_TO_DAY; + // 1970-12-31 23:59:59 + private final long END_TIME = 31507199000L; + + /** Test filter with slidingStep = 2 month, and timeInterval = 1 month */ + @Test + public void TestSatisfy1() { + GroupByMonthFilter filter = + new GroupByMonthFilter(MS_TO_MONTH, 2 * MS_TO_MONTH, 0, END_TIME, true, true); + + // 1970-01-01 08:00:00 + assertTrue(filter.satisfy(0, null)); + + // 1970-02-01 07:59:59 + assertTrue(filter.satisfy(2678399000L, null)); + + // 1970-02-01 08:00:00 + assertFalse(filter.satisfy(2678400000L, null)); + + // 1970-03-01 07:59:59 + assertFalse(filter.satisfy(5097599000L, null)); + + // 1970-03-01 08:00:00 + assertTrue(filter.satisfy(5097600000L, null)); + + // 1970-04-05 00:00:00 + assertFalse(filter.satisfy(8092800000L, null)); + + // 1970-07-01 07:59:59 + assertFalse(filter.satisfy(15638399000L, null)); + + // 1970-12-31 23:59:58 + assertFalse(filter.satisfy(31507198000L, null)); + + // 1970-12-31 23:59:59 + assertFalse(filter.satisfy(31507199000L, null)); + } + + /** Test filter with slidingStep = 1 month, and timeInterval = 1 month */ + @Test + public void TestSatisfy2() { + GroupByMonthFilter filter = + new GroupByMonthFilter(MS_TO_MONTH, MS_TO_MONTH, 0, END_TIME, true, true); + + // 1970-01-01 08:00:00 + assertTrue(filter.satisfy(0, null)); + + // 1970-02-01 07:59:59 + assertTrue(filter.satisfy(2678399000L, null)); + + // 1970-02-01 08:00:00 + assertTrue(filter.satisfy(2678400000L, null)); + + // 1970-03-01 07:59:59 + assertTrue(filter.satisfy(5097599000L, null)); + + // 1970-03-01 08:00:00 + assertTrue(filter.satisfy(5097600000L, null)); + + // 1970-12-31 23:59:58 + assertTrue(filter.satisfy(31507198000L, null)); + + // 1970-12-31 23:59:59 + assertFalse(filter.satisfy(31507199000L, null)); + } + + /** Test filter with slidingStep = 1 month, and timeInterval = 1 day */ + @Test + public void TestSatisfy3() { + GroupByMonthFilter filter = + new GroupByMonthFilter(MS_TO_DAY, MS_TO_MONTH, 0, END_TIME, true, false); + + // 1970-01-01 08:00:00 + assertTrue(filter.satisfy(0, null)); + + // 1970-01-02 07:59:59 + assertTrue(filter.satisfy(86399000L, null)); + + // 1970-01-02 08:00:00 + assertFalse(filter.satisfy(86400000L, null)); + + // 1970-02-01 07:59:59 + assertFalse(filter.satisfy(2678399000L, null)); + + // 1970-02-01 08:00:00 + assertTrue(filter.satisfy(2678400000L, null)); + + // 1970-03-01 08:00:00 + assertTrue(filter.satisfy(5097600000L, null)); + + // 1970-12-01 08:00:00 + assertTrue(filter.satisfy(28857600000L, null)); + + // 1970-12-31 23:59:59 + assertFalse(filter.satisfy(31507199000L, null)); + } +}
