You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@iotdb.apache.org by ja...@apache.org on 2024/07/22 12:08:15 UTC
(iotdb) 01/01: Add IoTDBOrderByLimitOffsetAlignByDeviceIT and IoTDBOrderByWithAlignByDeviceIT
This is an automated email from the ASF dual-hosted git repository.
jackietien pushed a commit to branch ty/TableIT
in repository https://gitbox.apache.org/repos/asf/iotdb.git
commit 54ace1f26e01fa924a607216c95db57b807e02ac
Author: JackieTien97 <ja...@gmail.com>
AuthorDate: Mon Jul 22 20:08:01 2024 +0800
Add IoTDBOrderByLimitOffsetAlignByDeviceIT and IoTDBOrderByWithAlignByDeviceIT
---
.../org/apache/iotdb/db/it/utils/TestUtils.java | 10 +
.../IoTDBAlignByDeviceWithTemplate2IT.java | 47 +
.../IoTDBAlignByDeviceWithTemplateIT.java | 2 +-
.../IoTDBOrderByLimitOffsetAlignByDevice2IT.java | 47 +
.../IoTDBOrderByLimitOffsetAlignByDeviceIT.java | 146 ++
.../IoTDBOrderByWithAlignByDevice2IT.java | 46 +
.../IoTDBOrderByWithAlignByDevice3IT.java | 46 +
.../IoTDBOrderByWithAlignByDeviceIT.java | 1564 ++++++++++++++++++++
8 files changed, 1907 insertions(+), 1 deletion(-)
diff --git a/integration-test/src/test/java/org/apache/iotdb/db/it/utils/TestUtils.java b/integration-test/src/test/java/org/apache/iotdb/db/it/utils/TestUtils.java
index d2712df7f59..d664ec07671 100644
--- a/integration-test/src/test/java/org/apache/iotdb/db/it/utils/TestUtils.java
+++ b/integration-test/src/test/java/org/apache/iotdb/db/it/utils/TestUtils.java
@@ -40,6 +40,8 @@ import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
+import java.time.ZoneId;
+import java.time.ZoneOffset;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
@@ -63,6 +65,14 @@ public class TestUtils {
private static final Logger LOGGER = LoggerFactory.getLogger(TestUtils.class);
+ public static final ZoneId DEFAULT_ZONE_ID = ZoneId.ofOffset("UTC", ZoneOffset.of("Z"));
+
+ public static final String TIME_PRECISION_IN_MS = "ms";
+
+ public static final String TIME_PRECISION_IN_US = "us";
+
+ public static final String TIME_PRECISION_IN_NS = "ns";
+
public static void prepareData(String[] sqls) {
try (Connection connection = EnvFactory.getEnv().getConnection();
Statement statement = connection.createStatement()) {
diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBAlignByDeviceWithTemplate2IT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBAlignByDeviceWithTemplate2IT.java
new file mode 100644
index 00000000000..f19a20779e9
--- /dev/null
+++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBAlignByDeviceWithTemplate2IT.java
@@ -0,0 +1,47 @@
+/*
+ * 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.relational.it.query.old.alignbydevice;
+
+import org.apache.iotdb.it.env.EnvFactory;
+import org.apache.iotdb.it.framework.IoTDBTestRunner;
+import org.apache.iotdb.itbase.category.ClusterIT;
+import org.apache.iotdb.itbase.category.LocalStandaloneIT;
+
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+
+@RunWith(IoTDBTestRunner.class)
+@Category({LocalStandaloneIT.class, ClusterIT.class})
+public class IoTDBAlignByDeviceWithTemplate2IT extends IoTDBAlignByDeviceWithTemplateIT {
+
+ @BeforeClass
+ public static void setUp() throws Exception {
+ EnvFactory.getEnv().getConfig().getCommonConfig().setSeriesSlotNum(1);
+ EnvFactory.getEnv().initClusterEnvironment();
+ insertData();
+ }
+
+ @AfterClass
+ public static void tearDown() throws Exception {
+ EnvFactory.getEnv().cleanClusterEnvironment();
+ }
+}
diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBAlignByDeviceWithTemplateIT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBAlignByDeviceWithTemplateIT.java
index cd6154aea78..59942d3acc1 100644
--- a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBAlignByDeviceWithTemplateIT.java
+++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBAlignByDeviceWithTemplateIT.java
@@ -501,7 +501,7 @@ public class IoTDBAlignByDeviceWithTemplateIT {
}
protected static void insertData() {
- try (Connection connection = EnvFactory.getEnv().getConnection();
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
Statement statement = connection.createStatement()) {
for (String sql : sqls) {
diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByLimitOffsetAlignByDevice2IT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByLimitOffsetAlignByDevice2IT.java
new file mode 100644
index 00000000000..bae442fd086
--- /dev/null
+++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByLimitOffsetAlignByDevice2IT.java
@@ -0,0 +1,47 @@
+/*
+ * 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.relational.it.query.old.alignbydevice;
+
+import org.apache.iotdb.it.env.EnvFactory;
+import org.apache.iotdb.it.framework.IoTDBTestRunner;
+import org.apache.iotdb.itbase.category.ClusterIT;
+import org.apache.iotdb.itbase.category.LocalStandaloneIT;
+
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+
+@RunWith(IoTDBTestRunner.class)
+@Category({LocalStandaloneIT.class, ClusterIT.class})
+public class IoTDBOrderByLimitOffsetAlignByDevice2IT
+ extends IoTDBOrderByLimitOffsetAlignByDeviceIT {
+ @BeforeClass
+ public static void setUp() throws Exception {
+ EnvFactory.getEnv().getConfig().getCommonConfig().setSeriesSlotNum(1);
+ EnvFactory.getEnv().initClusterEnvironment();
+ insertData3();
+ }
+
+ @AfterClass
+ public static void tearDown() throws Exception {
+ EnvFactory.getEnv().cleanClusterEnvironment();
+ }
+}
diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByLimitOffsetAlignByDeviceIT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByLimitOffsetAlignByDeviceIT.java
new file mode 100644
index 00000000000..3eb24bf86dc
--- /dev/null
+++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByLimitOffsetAlignByDeviceIT.java
@@ -0,0 +1,146 @@
+/*
+ * 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.relational.it.query.old.alignbydevice;
+
+import org.apache.iotdb.it.env.EnvFactory;
+import org.apache.iotdb.it.framework.IoTDBTestRunner;
+import org.apache.iotdb.itbase.category.ClusterIT;
+import org.apache.iotdb.itbase.category.LocalStandaloneIT;
+
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+
+import java.sql.Connection;
+import java.sql.Statement;
+
+import static org.apache.iotdb.db.it.utils.TestUtils.tableResultSetEqualTest;
+
+@RunWith(IoTDBTestRunner.class)
+@Category({LocalStandaloneIT.class, ClusterIT.class})
+public class IoTDBOrderByLimitOffsetAlignByDeviceIT {
+
+ @BeforeClass
+ public static void setUp() throws Exception {
+ EnvFactory.getEnv().initClusterEnvironment();
+ insertData3();
+ }
+
+ @AfterClass
+ public static void tearDown() throws Exception {
+ EnvFactory.getEnv().cleanClusterEnvironment();
+ }
+
+ @Test
+ public void orderByCanNotPushLimitTest() {
+ // 1. value filter, can not push down LIMIT
+ String[] expectedHeader = new String[] {"Time", "device_id", "s1"};
+ String[] retArray = new String[] {"1997-01-01T08:00:00.003Z,d1,111,"};
+ tableResultSetEqualTest(
+ "SELECT * FROM table1 WHERE s1>40 ORDER BY Time LIMIT 1",
+ expectedHeader,
+ retArray,
+ DATABASE_NAME);
+
+ // 2. order by expression, can not push down LIMIT
+ retArray = new String[] {"1997-01-01T08:00:00.003Z,d3,333,"};
+ tableResultSetEqualTest(
+ "SELECT * FROM table1 ORDER BY s1 DESC LIMIT 1", expectedHeader, retArray, DATABASE_NAME);
+
+ // 3. time filter, can push down LIMIT
+ retArray = new String[] {"1997-01-01T08:00:00.002Z,d3,33,", "1997-01-01T08:00:00.002Z,d2,22,"};
+ tableResultSetEqualTest(
+ "SELECT * FROM table1 WHERE time>1 and time<3 ORDER BY device_id DESC LIMIT 2;",
+ expectedHeader,
+ retArray,
+ DATABASE_NAME);
+
+ // 4. both exist OFFSET and LIMIT, can push down LIMIT as OFFSET+LIMIT
+ retArray = new String[] {"1997-01-01T08:00:00.003Z,d2,222,"};
+ tableResultSetEqualTest(
+ "SELECT * FROM table1 ORDER BY Time DESC OFFSET 1 LIMIT 1",
+ expectedHeader,
+ retArray,
+ DATABASE_NAME);
+ }
+
+ //
+ // @Test
+ // public void aggregationWithHavingTest() {
+ // // when aggregation with having, can only use MergeSortNode but not use TopKNode
+ // String[] expectedHeader = new String[] {"Time,Device,sum(s1)"};
+ // String[] retArray = new String[] {"3,root.db.d2,222.0,", "3,root.db.d3,333.0,"};
+ // resultSetEqualTest(
+ // "select sum(s1) from root.db.** group by ((1,5],1ms) having(sum(s1)>111) order by time
+ // limit 2 align by device;",
+ // expectedHeader,
+ // retArray);
+ // }
+ //
+ // @Test
+ // public void fillTest() {
+ // // linear fill can not use TopKNode
+ // String[] expectedHeader = new String[] {"Time,Device,s1,s2"};
+ // String[] retArray =
+ // new String[] {
+ // "1,root.fill.d1,1,null,",
+ // "1,root.fill.d2,2,null,",
+ // "1,root.fill.d3,3,null,",
+ // "2,root.fill.d1,22,11.0,",
+ // };
+ // resultSetEqualTest(
+ // "select * from root.fill.** order by time fill(linear) limit 4 align by device;",
+ // expectedHeader,
+ // retArray);
+ // }
+
+ private static final String DATABASE_NAME = "db";
+
+ private static final String DATABASE_FILL_NAME = "fill";
+
+ private static final String[] SQL_LIST =
+ new String[] {
+ "CREATE DATABASE " + DATABASE_NAME,
+ "USE " + DATABASE_NAME,
+ "create table table1(device_id STRING ID, s1 INT32 MEASUREMENT)",
+ "INSERT INTO table1(Time, device_id, s1) VALUES(1, 'd1', 1), (2, 'd1', 11), (3, 'd1', 111);",
+ "INSERT INTO table1(Time, device_id, s1) VALUES(1, 'd2', 2), (2, 'd2', 22), (3, 'd2', 222);",
+ "INSERT INTO table1(Time, device_id, s1) VALUES(1, 'd3', 3), (2, 'd3', 33), (3, 'd3', 333);",
+ "CREATE DATABASE " + DATABASE_FILL_NAME,
+ "USE " + DATABASE_FILL_NAME,
+ "create table table1(device_id STRING ID, s1 INT32 MEASUREMENT, s2 FLOAT MEASUREMENT)",
+ "INSERT INTO table1(Time,device_id,s1,s2) VALUES(1, 'd1', 1, null), (2, 'd1', null, 11), (3, 'd1', 111, 111.1);",
+ "INSERT INTO table1(Time,device_id,s1,s2) VALUES(1, 'd2', 2, null), (2, 'd2', 22, 22.2), (3, 'd2', 222, null);",
+ "INSERT INTO table1(Time,device_id,s1,s2) VALUES(1, 'd3', 3, null), (2, 'd3', 33, null), (3, 'd3', 333, 333.3);",
+ };
+
+ protected static void insertData3() {
+ try (Connection iotDBConnection = EnvFactory.getEnv().getConnection();
+ Statement statement = iotDBConnection.createStatement()) {
+ for (String sql : SQL_LIST) {
+ statement.execute(sql);
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+}
diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByWithAlignByDevice2IT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByWithAlignByDevice2IT.java
new file mode 100644
index 00000000000..13f82c69f1e
--- /dev/null
+++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByWithAlignByDevice2IT.java
@@ -0,0 +1,46 @@
+/*
+ * 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.relational.it.query.old.alignbydevice;
+
+import org.apache.iotdb.it.env.EnvFactory;
+import org.apache.iotdb.it.framework.IoTDBTestRunner;
+import org.apache.iotdb.itbase.category.ClusterIT;
+import org.apache.iotdb.itbase.category.LocalStandaloneIT;
+
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+
+@RunWith(IoTDBTestRunner.class)
+@Category({LocalStandaloneIT.class, ClusterIT.class})
+public class IoTDBOrderByWithAlignByDevice2IT extends IoTDBOrderByWithAlignByDeviceIT {
+ @BeforeClass
+ public static void setUp() throws Exception {
+ EnvFactory.getEnv().getConfig().getCommonConfig().setDegreeOfParallelism(4);
+ EnvFactory.getEnv().initClusterEnvironment();
+ insertData();
+ }
+
+ @AfterClass
+ public static void tearDown() throws Exception {
+ EnvFactory.getEnv().cleanClusterEnvironment();
+ }
+}
diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByWithAlignByDevice3IT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByWithAlignByDevice3IT.java
new file mode 100644
index 00000000000..c799cb22b21
--- /dev/null
+++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByWithAlignByDevice3IT.java
@@ -0,0 +1,46 @@
+/*
+ * 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.relational.it.query.old.alignbydevice;
+
+import org.apache.iotdb.it.env.EnvFactory;
+import org.apache.iotdb.it.framework.IoTDBTestRunner;
+import org.apache.iotdb.itbase.category.ClusterIT;
+import org.apache.iotdb.itbase.category.LocalStandaloneIT;
+
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+
+@RunWith(IoTDBTestRunner.class)
+@Category({LocalStandaloneIT.class, ClusterIT.class})
+public class IoTDBOrderByWithAlignByDevice3IT extends IoTDBOrderByWithAlignByDeviceIT {
+ @BeforeClass
+ public static void setUp() throws Exception {
+ EnvFactory.getEnv().getConfig().getCommonConfig().setSeriesSlotNum(1);
+ EnvFactory.getEnv().initClusterEnvironment();
+ insertData();
+ }
+
+ @AfterClass
+ public static void tearDown() throws Exception {
+ EnvFactory.getEnv().cleanClusterEnvironment();
+ }
+}
diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByWithAlignByDeviceIT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByWithAlignByDeviceIT.java
new file mode 100644
index 00000000000..538483e2c93
--- /dev/null
+++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBOrderByWithAlignByDeviceIT.java
@@ -0,0 +1,1564 @@
+/*
+ * 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.relational.it.query.old.alignbydevice;
+
+import org.apache.iotdb.it.env.EnvFactory;
+import org.apache.iotdb.it.framework.IoTDBTestRunner;
+import org.apache.iotdb.itbase.category.ClusterIT;
+import org.apache.iotdb.itbase.category.LocalStandaloneIT;
+import org.apache.iotdb.itbase.env.BaseEnv;
+import org.apache.iotdb.rpc.RpcUtils;
+
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Arrays;
+import java.util.Comparator;
+import java.util.HashMap;
+import java.util.Map;
+
+import static org.apache.iotdb.db.it.utils.TestUtils.DEFAULT_ZONE_ID;
+import static org.apache.iotdb.db.it.utils.TestUtils.TIME_PRECISION_IN_MS;
+import static org.apache.iotdb.db.it.utils.TestUtils.tableResultSetEqualTest;
+import static org.apache.iotdb.rpc.RpcUtils.DEFAULT_TIME_FORMAT;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+@RunWith(IoTDBTestRunner.class)
+@Category({LocalStandaloneIT.class, ClusterIT.class})
+public class IoTDBOrderByWithAlignByDeviceIT {
+ protected static final String DATABASE_NAME = "db";
+ protected static final String[] places =
+ new String[] {
+ "London",
+ "Edinburgh",
+ "Belfast",
+ "Birmingham",
+ "Liverpool",
+ "Derby",
+ "Durham",
+ "Hereford",
+ "Manchester",
+ "Oxford"
+ };
+ protected static final long startPrecipitation = 200;
+ protected static final double startTemperature = 20.0;
+ protected static final long startTime = 1668960000000L;
+ protected static final int numOfPointsInDevice = 20;
+ protected static final long timeGap = 100L;
+ protected static final Map<String, Long> deviceToStartTimestamp = new HashMap<>();
+ public static final Map<String, double[]> deviceToMaxTemperature = new HashMap<>();
+ public static final Map<String, double[]> deviceToAvgTemperature = new HashMap<>();
+ public static final Map<String, long[]> deviceToMaxPrecipitation = new HashMap<>();
+ public static final Map<String, double[]> deviceToAvgPrecipitation = new HashMap<>();
+
+ @BeforeClass
+ public static void setUp() throws Exception {
+ EnvFactory.getEnv().initClusterEnvironment();
+ insertData();
+ insertData2();
+ }
+
+ @AfterClass
+ public static void tearDown() throws Exception {
+ EnvFactory.getEnv().cleanClusterEnvironment();
+ }
+
+ /**
+ * This method generate test data with crossing time.
+ *
+ * <p>The data can be viewed in online doc:
+ *
+ * <p>https://docs.google.com/spreadsheets/d/18XlOIi27ZIIdRnar2WNXVMxkZwjgwlPZmzJLVpZRpAA/edit#gid=0
+ */
+ protected static void insertData() {
+ try (Connection iotDBConnection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = iotDBConnection.createStatement()) {
+ statement.execute("create database " + DATABASE_NAME);
+ statement.execute("use " + DATABASE_NAME);
+
+ statement.execute(
+ "create table weather(city STRING ID, precipitation INT64 MEASUREMENT, temperature DOUBLE MEASUREMENT);");
+
+ // insert data
+ long start = startTime;
+ double[][] temperatures = new double[places.length][29];
+ long[][] precipitations = new long[places.length][29];
+ for (int index = 0; index < places.length; index++) {
+ String place = places[index];
+
+ for (int i = 0; i < numOfPointsInDevice; i++) {
+ long precipitation = startPrecipitation + place.hashCode() + (start + i * timeGap);
+ double temperature = startTemperature + place.hashCode() + (start + i * timeGap);
+ precipitations[index][(int) ((start - startTime) / timeGap) + i] = precipitation;
+ temperatures[index][(int) ((start - startTime) / timeGap) + i] = temperature;
+ String insertUniqueTime =
+ "INSERT INTO weather"
+ + "(Time,city,precipitation,temperature) VALUES("
+ + (start + i * timeGap)
+ + ","
+ + String.format("'%s'", place)
+ + ","
+ + precipitation
+ + ","
+ + temperature
+ + ")";
+ statement.addBatch(insertUniqueTime);
+ if (i == 0) {
+ deviceToStartTimestamp.put(place, start);
+ }
+ }
+ statement.executeBatch();
+ statement.clearBatch();
+ start += timeGap;
+ }
+
+ for (int i = 0; i < places.length; i++) {
+ double[] aT = new double[3];
+ double[] aP = new double[3];
+ double[] mT = new double[3];
+ long[] mP = new long[3];
+ double totalTemperature = 0;
+ long totalPrecipitation = 0;
+ double maxTemperature = -1;
+ long maxPrecipitation = -1;
+ int cnt = 0;
+ for (int j = 0; j < precipitations[i].length; j++) {
+ totalTemperature += temperatures[i][j];
+ totalPrecipitation += precipitations[i][j];
+ maxPrecipitation = Math.max(maxPrecipitation, precipitations[i][j]);
+ maxTemperature = Math.max(maxTemperature, temperatures[i][j]);
+ if ((j + 1) % 10 == 0 || j == precipitations[i].length - 1) {
+ aT[cnt] = totalTemperature / 10;
+ aP[cnt] = (double) totalPrecipitation / 10;
+ mP[cnt] = maxPrecipitation;
+ mT[cnt] = maxTemperature;
+ cnt++;
+ totalTemperature = 0;
+ totalPrecipitation = 0;
+ maxTemperature = -1;
+ maxPrecipitation = -1;
+ }
+ }
+ deviceToMaxTemperature.put(places[i], mT);
+ deviceToMaxPrecipitation.put(places[i], mP);
+ deviceToAvgTemperature.put(places[i], aT);
+ deviceToAvgPrecipitation.put(places[i], aP);
+ }
+
+ for (String sql : optimizedSQL) {
+ statement.execute(sql);
+ }
+
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ // use to test if the compare result of time will overflow.
+ protected static void insertData2() {
+ try (Connection iotDBConnection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = iotDBConnection.createStatement()) {
+ statement.execute("use " + DATABASE_NAME);
+ statement.execute("create table overflow(device_id STRING ID, value INT32 MEASUREMENT);");
+ long startTime = 1;
+ for (int i = 0; i < 20; i++) {
+ String insertTime =
+ "INSERT INTO "
+ + "overflow"
+ + "(Time,device_id,value) VALUES("
+ + (startTime + 2147483648L)
+ + ","
+ + "'virtual_device'"
+ + ","
+ + i
+ + ")";
+ statement.addBatch(insertTime);
+ startTime += 2147483648L;
+ }
+ statement.executeBatch();
+ statement.clearBatch();
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ @Test
+ public void overFlowTest() {
+
+ String[] expectedHeader = new String[] {"Time", "value"};
+ String[] retArray = new String[20];
+
+ long startTime = 1;
+ for (int i = 0; i < 20; i++) {
+ long time = startTime + 2147483648L;
+ retArray[i] =
+ String.format(
+ "%s,%d,",
+ RpcUtils.formatDatetime(
+ DEFAULT_TIME_FORMAT, TIME_PRECISION_IN_MS, time, DEFAULT_ZONE_ID),
+ i);
+ startTime += 2147483648L;
+ }
+
+ tableResultSetEqualTest(
+ "SELECT Time, value FROM overflow", expectedHeader, retArray, DATABASE_NAME);
+ }
+
+ // ORDER BY DEVICE
+ @Test
+ public void orderByDeviceTest1() {
+
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+
+ String[] expectedDevice = Arrays.stream(places.clone()).sorted().toArray(String[]::new);
+
+ int index = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+
+ try (ResultSet resultSet = statement.executeQuery("SELECT * FROM weather order by city")) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+
+ int cnt = 0;
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ String actualDevice = resultSet.getString(2);
+ assertEquals(expectedDevice[index], actualDevice);
+ assertEquals(deviceToStartTimestamp.get(actualDevice) + cnt * timeGap, actualTimeStamp);
+
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ startTemperature + actualDevice.hashCode() + actualTimeStamp - actualTemperature
+ < 0.00001);
+
+ cnt++;
+ if (cnt % numOfPointsInDevice == 0) {
+ index++;
+ cnt = 0;
+ }
+ }
+ assertEquals(10, index);
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ @Test
+ public void orderByDeviceTest2() {
+
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+
+ String[] expectedDevice = Arrays.stream(places.clone()).sorted().toArray(String[]::new);
+
+ int index = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+
+ try (ResultSet resultSet =
+ statement.executeQuery("SELECT * FROM weather order by city asc")) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+
+ int cnt = 0;
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ String actualDevice = resultSet.getString(2);
+ assertEquals(expectedDevice[index], actualDevice);
+ assertEquals(deviceToStartTimestamp.get(actualDevice) + cnt * timeGap, actualTimeStamp);
+
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ startTemperature + actualDevice.hashCode() + actualTimeStamp - actualTemperature
+ < 0.00001);
+
+ cnt++;
+ if (cnt % numOfPointsInDevice == 0) {
+ index++;
+ cnt = 0;
+ }
+ }
+ assertEquals(10, index);
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ @Test
+ public void orderByDeviceTest3() {
+
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+
+ String[] expectedDevice =
+ Arrays.stream(places.clone()).sorted(Comparator.reverseOrder()).toArray(String[]::new);
+
+ int index = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+
+ try (ResultSet resultSet =
+ statement.executeQuery("SELECT * FROM weather order by city desc")) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+
+ int cnt = 0;
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ String actualDevice = resultSet.getString(2);
+ assertEquals(expectedDevice[index], actualDevice);
+ assertEquals(deviceToStartTimestamp.get(actualDevice) + cnt * timeGap, actualTimeStamp);
+
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ startTemperature + actualDevice.hashCode() + actualTimeStamp - actualTemperature
+ < 0.00001);
+
+ cnt++;
+ if (cnt % numOfPointsInDevice == 0) {
+ index++;
+ cnt = 0;
+ }
+ }
+ assertEquals(10, index);
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ // ORDER BY TIME
+
+ @Test
+ public void orderByTimeTest1() {
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+
+ orderByTimeTest1(
+ "SELECT * FROM weather ORDER BY TIME, city",
+ numOfPointsInDevice * places.length,
+ expectedHeader);
+
+ orderByTimeTest1("SELECT * FROM weather ORDER BY TIME, city LIMIT 100", 100, expectedHeader);
+ }
+
+ @Test
+ public void orderByTimeTest2() {
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+ orderByTimeTest1(
+ "SELECT * FROM weather ORDER BY TIME ASC, city",
+ numOfPointsInDevice * places.length,
+ expectedHeader);
+
+ orderByTimeTest1(
+ "SELECT * FROM weather ORDER BY TIME ASC, city ASC LIMIT 100E", 100, expectedHeader);
+ }
+
+ @Test
+ public void orderByTimeTest3() {
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+ orderByTimeTest3(
+ "SELECT * FROM weather ORDER BY TIME DESC, city ASC",
+ numOfPointsInDevice * places.length,
+ expectedHeader);
+
+ orderByTimeTest3(
+ "SELECT * FROM weather ORDER BY TIME DESC, city LIMIT 100", 100, expectedHeader);
+ }
+
+ @Test
+ public void orderByTimeExpressionTest1() {
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+
+ orderByTimeExpressionTest1(
+ "SELECT * FROM weather ORDER BY TIME DESC, precipitation DESC, city",
+ numOfPointsInDevice * places.length,
+ expectedHeader);
+
+ orderByTimeExpressionTest1(
+ "SELECT * FROM weather ORDER BY TIME DESC, precipitation DESC, city asc LIMIT 100",
+ 100,
+ expectedHeader);
+ }
+
+ @Test
+ public void orderExpressionTest1() {
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+
+ orderByExpressionTest1(
+ "SELECT * FROM weather ORDER BY precipitation DESC, time DESC, city asc",
+ numOfPointsInDevice * places.length,
+ expectedHeader);
+
+ orderByExpressionTest1(
+ "SELECT * FROM weather ORDER BY precipitation DESC, time DESC, city", 100, expectedHeader);
+ }
+
+ @Test
+ public void orderByDeviceTimeTest1() {
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+
+ orderByDeviceTimeTest1("SELECT * FROM weather ORDER BY city ASC,Time DESC", 10, expectedHeader);
+
+ orderByDeviceTimeTest1(
+ "SELECT * FROM weather ORDER BY city ASC,Time DESC LIMIT 100", 5, expectedHeader);
+ }
+
+ @Test
+ public void orderByDeviceTimeTest2() {
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+
+ orderByDeviceTimeTest2("SELECT * FROM weather ORDER BY city ASC,Time ASC", 10, expectedHeader);
+
+ orderByDeviceTimeTest2(
+ "SELECT * FROM weather ORDER BY city ASC,Time ASC LIMIT 100", 5, expectedHeader);
+ }
+
+ @Test
+ public void orderByDeviceTimeTest3() {
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+
+ orderByDeviceTimeTest3(
+ "SELECT * FROM weather ORDER BY city DESC,Time DESC", 10, expectedHeader);
+
+ orderByDeviceTimeTest3(
+ "SELECT * FROM weather ORDER BY city DESC,Time DESC LIMIT 100", 5, expectedHeader);
+ }
+
+ @Test
+ public void orderByDeviceTimeTest4() {
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+
+ orderByDeviceTimeTest4("SELECT * FROM weather ORDER BY city DESC,Time ASC", 10, expectedHeader);
+
+ orderByDeviceTimeTest4(
+ "SELECT * FROM weather ORDER BY city DESC,Time ASC LIMIT 100", 5, expectedHeader);
+ }
+
+ @Test
+ public void orderByTimeDeviceTest1() {
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+
+ orderByTimeDeviceTest1(
+ "SELECT * FROM weather ORDER BY Time ASC,city DESC",
+ numOfPointsInDevice * places.length,
+ expectedHeader);
+
+ orderByTimeDeviceTest1(
+ "SELECT * FROM weather ORDER BY Time ASC,city DESC LIMIT 100", 100, expectedHeader);
+ }
+
+ @Test
+ public void orderByTimeDeviceTest2() {
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+
+ orderByTimeDeviceTest2(
+ "SELECT * FROM weather ORDER BY Time ASC,city ASC",
+ numOfPointsInDevice * places.length,
+ expectedHeader);
+
+ orderByTimeDeviceTest2(
+ "SELECT * FROM weather ORDER BY Time ASC,city ASC LIMIT 100", 100, expectedHeader);
+ }
+
+ @Test
+ public void orderByTimeDeviceTest3() {
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+
+ orderByTimeDeviceTest3(
+ "SELECT * FROM weather ORDER BY Time DESC,city DESC",
+ numOfPointsInDevice * places.length,
+ expectedHeader);
+
+ orderByTimeDeviceTest3(
+ "SELECT * FROM weather ORDER BY Time DESC,city DESC LIMIT 100", 100, expectedHeader);
+ }
+
+ @Test
+ public void orderByTimeDeviceTest4() {
+ String[] expectedHeader = new String[] {"Time", "city", "precipitation", "temperature"};
+
+ orderByTimeDeviceTest4(
+ "SELECT * FROM weather ORDER BY Time DESC,DEVICE ASC",
+ numOfPointsInDevice * places.length,
+ expectedHeader);
+
+ orderByTimeDeviceTest4(
+ "SELECT * FROM weather ORDER BY Time DESC,DEVICE ASC LIMIT 100", 100, expectedHeader);
+ }
+
+ public static void orderByTimeTest1(String sql, int count, String[] expectedHeader) {
+ int total = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+ statement.execute("use " + DATABASE_NAME);
+ try (ResultSet resultSet = statement.executeQuery(sql)) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+
+ long lastTimeStamp = -1;
+ String lastDevice = "";
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ assertTrue(actualTimeStamp >= lastTimeStamp);
+ String actualDevice = resultSet.getString(2);
+ if (!lastDevice.isEmpty() && actualTimeStamp == lastTimeStamp) {
+ assertTrue(actualDevice.compareTo(lastDevice) >= 0);
+ }
+ lastDevice = actualDevice;
+ lastTimeStamp = actualTimeStamp;
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ startTemperature + actualDevice.hashCode() + actualTimeStamp - actualTemperature
+ < 0.00001);
+ total++;
+ }
+ assertEquals(count, total);
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ public static void orderByTimeTest3(String sql, int count, String[] expectedHeader) {
+
+ int total = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+ statement.execute("use " + DATABASE_NAME);
+ try (ResultSet resultSet = statement.executeQuery(sql)) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+
+ long lastTimeStamp = Long.MAX_VALUE;
+ String lastDevice = "";
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ assertTrue(actualTimeStamp <= lastTimeStamp);
+ String actualDevice = resultSet.getString(2);
+ if (!lastDevice.equals("") && actualTimeStamp == lastTimeStamp) {
+ assertTrue(actualDevice.compareTo(lastDevice) >= 0);
+ }
+ lastDevice = actualDevice;
+ lastTimeStamp = actualTimeStamp;
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ startTemperature + actualDevice.hashCode() + actualTimeStamp - actualTemperature
+ < 0.00001);
+ total++;
+ }
+ assertEquals(count, total);
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ public static void orderByTimeExpressionTest1(String sql, int count, String[] expectedHeader) {
+ int total = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+ statement.execute("use " + DATABASE_NAME);
+ try (ResultSet resultSet = statement.executeQuery(sql)) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+
+ long lastTimeStamp = Long.MAX_VALUE;
+ long lastPrecipitation = Long.MAX_VALUE;
+ String lastDevice = "";
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ assertTrue(actualTimeStamp <= lastTimeStamp);
+ String actualDevice = resultSet.getString(2);
+
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ startTemperature + actualDevice.hashCode() + actualTimeStamp - actualTemperature
+ < 0.00001);
+ if (actualDevice.equals(lastDevice) && actualTimeStamp == lastTimeStamp) {
+ assertTrue(actualPrecipitation <= lastPrecipitation);
+ lastPrecipitation = actualPrecipitation;
+ }
+ lastDevice = actualDevice;
+ lastTimeStamp = actualTimeStamp;
+ total++;
+ }
+ assertEquals(count, total);
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ public static void orderByExpressionTest1(String sql, int count, String[] expectedHeader) {
+
+ int total = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+ statement.execute("use " + DATABASE_NAME);
+ try (ResultSet resultSet = statement.executeQuery(sql)) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+
+ long lastTimeStamp = Long.MAX_VALUE;
+ long lastPrecipitation = Long.MAX_VALUE;
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ String actualDevice = resultSet.getString(2);
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ startTemperature + actualDevice.hashCode() + actualTimeStamp - actualTemperature
+ < 0.00001);
+ assertTrue(actualPrecipitation <= lastPrecipitation);
+ if (actualPrecipitation == lastPrecipitation) {
+ assertTrue(actualTimeStamp <= lastTimeStamp);
+ }
+ lastPrecipitation = actualPrecipitation;
+ lastTimeStamp = actualTimeStamp;
+ total++;
+ }
+ assertEquals(count, total);
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ // ORDER BY DEVICE,TIME
+ public static void orderByDeviceTimeTest1(String sql, int count, String[] expectedHeader) {
+ String[] expectedDevice = Arrays.stream(places.clone()).sorted().toArray(String[]::new);
+ int index = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+ statement.execute("use " + DATABASE_NAME);
+ try (ResultSet resultSet = statement.executeQuery(sql)) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+
+ int cnt = 0;
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ String actualDevice = resultSet.getString(2);
+ assertEquals(expectedDevice[index], actualDevice);
+ assertEquals(
+ deviceToStartTimestamp.get(actualDevice) + timeGap * (numOfPointsInDevice - cnt - 1),
+ actualTimeStamp);
+
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ (startTemperature + actualDevice.hashCode() + actualTimeStamp) - actualTemperature
+ < 0.00001);
+
+ cnt++;
+ if (cnt % numOfPointsInDevice == 0) {
+ index++;
+ cnt = 0;
+ }
+ }
+ assertEquals(count, index);
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ public static void orderByDeviceTimeTest2(String sql, int count, String[] expectedHeader) {
+ String[] expectedDevice = Arrays.stream(places.clone()).sorted().toArray(String[]::new);
+ int index = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+ statement.execute("use " + DATABASE_NAME);
+ try (ResultSet resultSet = statement.executeQuery(sql)) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+
+ int cnt = 0;
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ String actualDevice = resultSet.getString(2);
+ assertEquals(expectedDevice[index], actualDevice);
+ assertEquals(deviceToStartTimestamp.get(actualDevice) + cnt * timeGap, actualTimeStamp);
+
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ (startTemperature + actualDevice.hashCode() + actualTimeStamp) - actualTemperature
+ < 0.00001);
+
+ cnt++;
+ if (cnt % numOfPointsInDevice == 0) {
+ index++;
+ cnt = 0;
+ }
+ }
+ assertEquals(count, index);
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ public static void orderByDeviceTimeTest3(String sql, int count, String[] expectedHeader) {
+ String[] expectedDevice =
+ Arrays.stream(places.clone()).sorted(Comparator.reverseOrder()).toArray(String[]::new);
+ int index = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+ statement.execute("use " + DATABASE_NAME);
+ try (ResultSet resultSet = statement.executeQuery(sql)) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+
+ int cnt = 0;
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ String actualDevice = resultSet.getString(2);
+ assertEquals(expectedDevice[index], actualDevice);
+ assertEquals(
+ deviceToStartTimestamp.get(actualDevice) + timeGap * (numOfPointsInDevice - cnt - 1),
+ actualTimeStamp);
+
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ (startTemperature + actualDevice.hashCode() + actualTimeStamp) - actualTemperature
+ < 0.00001);
+
+ cnt++;
+ if (cnt % numOfPointsInDevice == 0) {
+ index++;
+ cnt = 0;
+ }
+ }
+ assertEquals(count, index);
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ public static void orderByDeviceTimeTest4(String sql, int count, String[] expectedHeader) {
+ String[] expectedDevice =
+ Arrays.stream(places.clone()).sorted(Comparator.reverseOrder()).toArray(String[]::new);
+ int index = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+ statement.execute("use " + DATABASE_NAME);
+ try (ResultSet resultSet = statement.executeQuery(sql)) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+
+ int cnt = 0;
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ String actualDevice = resultSet.getString(2);
+ assertEquals(expectedDevice[index], actualDevice);
+ assertEquals(deviceToStartTimestamp.get(actualDevice) + cnt * timeGap, actualTimeStamp);
+
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ (startTemperature + actualDevice.hashCode() + actualTimeStamp) - actualTemperature
+ < 0.00001);
+
+ cnt++;
+ if (cnt % numOfPointsInDevice == 0) {
+ index++;
+ cnt = 0;
+ }
+ }
+ assertEquals(count, index);
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ // ORDER BY TIME,DEVICE
+ public static void orderByTimeDeviceTest1(String sql, int count, String[] expectedHeader) {
+ int total = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+ statement.execute("use " + DATABASE_NAME);
+ try (ResultSet resultSet = statement.executeQuery(sql)) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+
+ long lastTimeStamp = -1;
+ String lastDevice = "";
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ assertTrue(actualTimeStamp >= lastTimeStamp);
+ String actualDevice = resultSet.getString(2);
+ if (!lastDevice.equals("") && actualTimeStamp == lastTimeStamp) {
+ assertTrue(actualDevice.compareTo(lastDevice) <= 0);
+ }
+ lastDevice = actualDevice;
+ lastTimeStamp = actualTimeStamp;
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ startTemperature + actualDevice.hashCode() + actualTimeStamp - actualTemperature
+ < 0.00001);
+ total++;
+ }
+ assertEquals(count, total);
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ public static void orderByTimeDeviceTest2(String sql, int count, String[] expectedHeader) {
+ int total = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+ statement.execute("use " + DATABASE_NAME);
+ try (ResultSet resultSet = statement.executeQuery(sql)) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+ long lastTimeStamp = -1;
+ String lastDevice = "";
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ assertTrue(actualTimeStamp >= lastTimeStamp);
+ String actualDevice = resultSet.getString(2);
+ if (!lastDevice.equals("") && actualTimeStamp == lastTimeStamp) {
+ assertTrue(actualDevice.compareTo(lastDevice) >= 0);
+ }
+ lastDevice = actualDevice;
+ lastTimeStamp = actualTimeStamp;
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ startTemperature + actualDevice.hashCode() + actualTimeStamp - actualTemperature
+ < 0.00001);
+ total++;
+ }
+ assertEquals(count, total);
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ public static void orderByTimeDeviceTest3(String sql, int count, String[] expectedHeader) {
+ int total = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+ statement.execute("use " + DATABASE_NAME);
+ try (ResultSet resultSet = statement.executeQuery(sql)) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+ long lastTimeStamp = Long.MAX_VALUE;
+ String lastDevice = "";
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ assertTrue(actualTimeStamp <= lastTimeStamp);
+ String actualDevice = resultSet.getString(2);
+ if (!lastDevice.equals("") && actualTimeStamp == lastTimeStamp) {
+ assertTrue(actualDevice.compareTo(lastDevice) <= 0);
+ }
+ lastDevice = actualDevice;
+ lastTimeStamp = actualTimeStamp;
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ startTemperature + actualDevice.hashCode() + actualTimeStamp - actualTemperature
+ < 0.00001);
+ total++;
+ }
+ assertEquals(count, total);
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ public static void orderByTimeDeviceTest4(String sql, int count, String[] expectedHeader) {
+ int total = 0;
+ try (Connection connection = EnvFactory.getEnv().getConnection(BaseEnv.TABLE_SQL_DIALECT);
+ Statement statement = connection.createStatement()) {
+ statement.execute("use " + DATABASE_NAME);
+ try (ResultSet resultSet = statement.executeQuery(sql)) {
+ ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
+ assertEquals(expectedHeader[i - 1], resultSetMetaData.getColumnName(i));
+ }
+ assertEquals(expectedHeader.length, resultSetMetaData.getColumnCount());
+ long lastTimeStamp = Long.MAX_VALUE;
+ String lastDevice = "";
+ while (resultSet.next()) {
+ long actualTimeStamp = resultSet.getLong(1);
+ assertTrue(actualTimeStamp <= lastTimeStamp);
+ String actualDevice = resultSet.getString(2);
+ if (!lastDevice.equals("") && actualTimeStamp == lastTimeStamp) {
+ assertTrue(actualDevice.compareTo(lastDevice) >= 0);
+ }
+ lastDevice = actualDevice;
+ lastTimeStamp = actualTimeStamp;
+ long actualPrecipitation = resultSet.getLong(3);
+ double actualTemperature = resultSet.getDouble(4);
+ assertEquals(
+ startPrecipitation + actualDevice.hashCode() + actualTimeStamp, actualPrecipitation);
+ assertTrue(
+ startTemperature + actualDevice.hashCode() + actualTimeStamp - actualTemperature
+ < 0.00001);
+ total++;
+ }
+ assertEquals(count, total);
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ fail(e.getMessage());
+ }
+ }
+
+ //
+ // // aggregation query
+ // private static final int[][] countIn1000MSFiledWith100MSTimeGap =
+ // new int[][] {
+ // {10, 10, 0},
+ // {9, 10, 1},
+ // {8, 10, 2},
+ // {7, 10, 3},
+ // {6, 10, 4},
+ // {5, 10, 5},
+ // {4, 10, 6},
+ // {3, 10, 7},
+ // {2, 10, 8},
+ // {1, 10, 9}
+ // };
+ //
+ // private static int getCountNum(String device, int cnt) {
+ // int index = 0;
+ // for (int i = 0; i < places.length; i++) {
+ // if (places[i].equals(device)) {
+ // index = i;
+ // break;
+ // }
+ // }
+ //
+ // return countIn1000MSFiledWith100MSTimeGap[index][cnt];
+ // }
+ //
+ // @Test
+ // public void groupByTimeOrderByDeviceTest1() {
+ // String sql =
+ // "SELECT AVG(*),COUNT(*),MAX_VALUE(*) FROM weather GROUP
+ // BY([2022-11-21T00:00:00.000+08:00,2022-11-21T00:00:02.801+08:00),1000msC";
+ // try (Connection connection = EnvFactory.getEnv().getConnection();
+ // Statement statement = connection.createStatement()) {
+ // long lastTime = -1;
+ // String lastDevice = "";
+ // long[] expectedTime = new long[] {startTime, startTime + 1000, startTime + 1000 * 2};
+ // try (ResultSet resultSet = statement.executeQuery(sql)) {
+ // ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ // checkHeader(
+ // resultSetMetaData,
+ //
+ // "Time,Device,AVG(precipitation),AVG(temperature),COUNT(precipitation),COUNT(temperature),MAX_VALUE(precipitation),MAX_VALUE(temperature)");
+ // int cnt = 0;
+ // while (resultSet.next()) {
+ //
+ // long actualTime = resultSet.getLong(1);
+ // String actualDevice = resultSet.getString(2);
+ // assertEquals(expectedTime[cnt], actualTime);
+ // if (!Objects.equals(lastDevice, "") && Objects.equals(actualDevice, lastDevice)) {
+ // assertTrue(actualTime >= lastTime);
+ // }
+ // if (!Objects.equals(lastDevice, "")) {
+ // assertTrue(actualDevice.compareTo(lastDevice) >= 0);
+ // }
+ // lastTime = actualTime;
+ // lastDevice = actualDevice;
+ //
+ // double avgPrecipitation = resultSet.getDouble(3);
+ // assertTrue(deviceToAvgPrecipitation.get(actualDevice)[cnt] - avgPrecipitation <
+ // 0.00001);
+ //
+ // double avgTemperature = resultSet.getDouble(4);
+ // assertTrue(deviceToAvgTemperature.get(actualDevice)[cnt] - avgTemperature < 0.00001);
+ //
+ // int countPrecipitation = resultSet.getInt(5);
+ // assertEquals(getCountNum(actualDevice, cnt), countPrecipitation);
+ // int countTemperature = resultSet.getInt(6);
+ // assertEquals(getCountNum(actualDevice, cnt), countTemperature);
+ //
+ // long maxPrecipitation = resultSet.getLong(7);
+ // assertEquals(deviceToMaxPrecipitation.get(actualDevice)[cnt], maxPrecipitation);
+ //
+ // double maxTemperature = resultSet.getDouble(8);
+ // assertTrue(deviceToMaxTemperature.get(actualDevice)[cnt] - maxTemperature < 0.00001);
+ // cnt++;
+ // if (cnt % 3 == 0) {
+ // cnt = 0;
+ // }
+ // }
+ // }
+ // } catch (Exception e) {
+ // e.printStackTrace();
+ // fail(e.getMessage());
+ // }
+ // }
+ //
+ // @Test
+ // public void groupByTimeOrderByDeviceTest2() {
+ // String sql =
+ // "SELECT AVG(*),COUNT(*),MAX_VALUE(*) FROM weather GROUP
+ // BY([2022-11-21T00:00:00.000+08:00,2022-11-21T00:00:02.801+08:00),1000ms) ORDER BY DEVICE DESC";
+ // try (Connection connection = EnvFactory.getEnv().getConnection();
+ // Statement statement = connection.createStatement()) {
+ // long lastTime = -1;
+ // String lastDevice = "";
+ // long[] expectedTime = new long[] {startTime, startTime + 1000, startTime + 1000 * 2};
+ // try (ResultSet resultSet = statement.executeQuery(sql)) {
+ // ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ // checkHeader(
+ // resultSetMetaData,
+ //
+ // "Time,Device,AVG(precipitation),AVG(temperature),COUNT(precipitation),COUNT(temperature),MAX_VALUE(precipitation),MAX_VALUE(temperature)");
+ // int cnt = 0;
+ // while (resultSet.next()) {
+ //
+ // long actualTime = resultSet.getLong(1);
+ // String actualDevice = resultSet.getString(2);
+ // assertEquals(expectedTime[cnt], actualTime);
+ // if (!Objects.equals(lastDevice, "") && Objects.equals(actualDevice, lastDevice)) {
+ // assertTrue(actualTime >= lastTime);
+ // }
+ // if (!Objects.equals(lastDevice, "")) {
+ // assertTrue(actualDevice.compareTo(lastDevice) <= 0);
+ // }
+ // lastTime = actualTime;
+ // lastDevice = actualDevice;
+ //
+ // double avgPrecipitation = resultSet.getDouble(3);
+ // assertTrue(deviceToAvgPrecipitation.get(actualDevice)[cnt] - avgPrecipitation <
+ // 0.00001);
+ //
+ // double avgTemperature = resultSet.getDouble(4);
+ // assertTrue(deviceToAvgTemperature.get(actualDevice)[cnt] - avgTemperature < 0.00001);
+ //
+ // int countPrecipitation = resultSet.getInt(5);
+ // assertEquals(getCountNum(actualDevice, cnt), countPrecipitation);
+ // int countTemperature = resultSet.getInt(6);
+ // assertEquals(getCountNum(actualDevice, cnt), countTemperature);
+ //
+ // long maxPrecipitation = resultSet.getLong(7);
+ // assertEquals(deviceToMaxPrecipitation.get(actualDevice)[cnt], maxPrecipitation);
+ //
+ // double maxTemperature = resultSet.getDouble(8);
+ // assertTrue(deviceToMaxTemperature.get(actualDevice)[cnt] - maxTemperature < 0.00001);
+ // cnt++;
+ // if (cnt % 3 == 0) {
+ // cnt = 0;
+ // }
+ // }
+ // }
+ // } catch (Exception e) {
+ // e.printStackTrace();
+ // fail(e.getMessage());
+ // }
+ // }
+ //
+ // @Test
+ // public void groupByTimeOrderByDeviceTest3() {
+ // String sql =
+ // "SELECT AVG(*),COUNT(*),MAX_VALUE(*) FROM weather GROUP
+ // BY([2022-11-21T00:00:00.000+08:00,2022-11-21T00:00:02.801+08:00),1000ms) ORDER BY DEVICE
+ // DESC,TIME DESC";
+ // try (Connection connection = EnvFactory.getEnv().getConnection();
+ // Statement statement = connection.createStatement()) {
+ // long lastTime = -1;
+ // String lastDevice = "";
+ // long[] expectedTime = new long[] {startTime, startTime + 1000, startTime + 1000 * 2};
+ // try (ResultSet resultSet = statement.executeQuery(sql)) {
+ // ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ // checkHeader(
+ // resultSetMetaData,
+ //
+ // "Time,Device,AVG(precipitation),AVG(temperature),COUNT(precipitation),COUNT(temperature),MAX_VALUE(precipitation),MAX_VALUE(temperature)");
+ // int cnt = 2;
+ // while (resultSet.next()) {
+ // long actualTime = resultSet.getLong(1);
+ // String actualDevice = resultSet.getString(2);
+ // assertEquals(expectedTime[cnt], actualTime);
+ // if (!Objects.equals(lastDevice, "") && Objects.equals(actualDevice, lastDevice)) {
+ // assertTrue(actualTime <= lastTime);
+ // }
+ // if (!Objects.equals(lastDevice, "")) {
+ // assertTrue(actualDevice.compareTo(lastDevice) <= 0);
+ // }
+ // lastTime = actualTime;
+ // lastDevice = actualDevice;
+ //
+ // double avgPrecipitation = resultSet.getDouble(3);
+ // assertTrue(deviceToAvgPrecipitation.get(actualDevice)[cnt] - avgPrecipitation <
+ // 0.00001);
+ //
+ // double avgTemperature = resultSet.getDouble(4);
+ // assertTrue(deviceToAvgTemperature.get(actualDevice)[cnt] - avgTemperature < 0.00001);
+ //
+ // int countPrecipitation = resultSet.getInt(5);
+ // assertEquals(getCountNum(actualDevice, cnt), countPrecipitation);
+ // int countTemperature = resultSet.getInt(6);
+ // assertEquals(getCountNum(actualDevice, cnt), countTemperature);
+ //
+ // long maxPrecipitation = resultSet.getLong(7);
+ // assertEquals(deviceToMaxPrecipitation.get(actualDevice)[cnt], maxPrecipitation);
+ //
+ // double maxTemperature = resultSet.getDouble(8);
+ // assertTrue(deviceToMaxTemperature.get(actualDevice)[cnt] - maxTemperature < 0.00001);
+ // cnt--;
+ // if (cnt < 0) {
+ // cnt = 2;
+ // }
+ // }
+ // }
+ // } catch (Exception e) {
+ // e.printStackTrace();
+ // fail(e.getMessage());
+ // }
+ // }
+ //
+ // @Test
+ // public void groupByTimeOrderByDeviceTest4() {
+ // String sql =
+ // "SELECT AVG(*),COUNT(*),MAX_VALUE(*) FROM weather GROUP
+ // BY([2022-11-21T00:00:00.000+08:00,2022-11-21T00:00:02.801+08:00),1000ms) ORDER BY DEVICE
+ // ASC,TIME DESC";
+ // try (Connection connection = EnvFactory.getEnv().getConnection();
+ // Statement statement = connection.createStatement()) {
+ // long lastTime = -1;
+ // String lastDevice = "";
+ // long[] expectedTime = new long[] {startTime, startTime + 1000, startTime + 1000 * 2};
+ // try (ResultSet resultSet = statement.executeQuery(sql)) {
+ // ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ // checkHeader(
+ // resultSetMetaData,
+ //
+ // "Time,Device,AVG(precipitation),AVG(temperature),COUNT(precipitation),COUNT(temperature),MAX_VALUE(precipitation),MAX_VALUE(temperature)");
+ // int cnt = 2;
+ // while (resultSet.next()) {
+ //
+ // long actualTime = resultSet.getLong(1);
+ // String actualDevice = resultSet.getString(2);
+ // assertEquals(expectedTime[cnt], actualTime);
+ // if (!Objects.equals(lastDevice, "") && Objects.equals(actualDevice, lastDevice)) {
+ // assertTrue(actualTime <= lastTime);
+ // }
+ // if (!Objects.equals(lastDevice, "")) {
+ // assertTrue(actualDevice.compareTo(lastDevice) >= 0);
+ // }
+ // lastTime = actualTime;
+ // lastDevice = actualDevice;
+ //
+ // double avgPrecipitation = resultSet.getDouble(3);
+ // assertTrue(deviceToAvgPrecipitation.get(actualDevice)[cnt] - avgPrecipitation <
+ // 0.00001);
+ //
+ // double avgTemperature = resultSet.getDouble(4);
+ // assertTrue(deviceToAvgTemperature.get(actualDevice)[cnt] - avgTemperature < 0.00001);
+ //
+ // int countPrecipitation = resultSet.getInt(5);
+ // assertEquals(getCountNum(actualDevice, cnt), countPrecipitation);
+ // int countTemperature = resultSet.getInt(6);
+ // assertEquals(getCountNum(actualDevice, cnt), countTemperature);
+ //
+ // long maxPrecipitation = resultSet.getLong(7);
+ // assertEquals(deviceToMaxPrecipitation.get(actualDevice)[cnt], maxPrecipitation);
+ //
+ // double maxTemperature = resultSet.getDouble(8);
+ // assertTrue(deviceToMaxTemperature.get(actualDevice)[cnt] - maxTemperature < 0.00001);
+ // cnt--;
+ // if (cnt < 0) {
+ // cnt = 2;
+ // }
+ // }
+ // }
+ // } catch (Exception e) {
+ // e.printStackTrace();
+ // fail(e.getMessage());
+ // }
+ // }
+ //
+ // @Test
+ // public void groupByTimeOrderByTimeTest1() {
+ // String sql =
+ // "SELECT AVG(*),COUNT(*),MAX_VALUE(*) FROM weather GROUP
+ // BY([2022-11-21T00:00:00.000+08:00,2022-11-21T00:00:02.801+08:00),1000ms) ORDER BY TIME";
+ // try (Connection connection = EnvFactory.getEnv().getConnection();
+ // Statement statement = connection.createStatement()) {
+ // long lastTime = -1;
+ // String lastDevice = "";
+ // int index = 0;
+ // try (ResultSet resultSet = statement.executeQuery(sql)) {
+ // ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ // checkHeader(
+ // resultSetMetaData,
+ //
+ // "Time,Device,AVG(precipitation),AVG(temperature),COUNT(precipitation),COUNT(temperature),MAX_VALUE(precipitation),MAX_VALUE(temperature)");
+ // int cnt = 0;
+ // while (resultSet.next()) {
+ // long actualTime = resultSet.getLong(1);
+ // String actualDevice = resultSet.getString(2);
+ // assertTrue(actualTime >= lastTime);
+ // if (!Objects.equals(lastDevice, "") && actualTime == lastTime) {
+ // assertTrue(actualDevice.compareTo(lastDevice) >= 0);
+ // }
+ // lastTime = actualTime;
+ // lastDevice = actualDevice;
+ // double avgPrecipitation = resultSet.getDouble(3);
+ // assertTrue(deviceToAvgPrecipitation.get(actualDevice)[cnt] - avgPrecipitation <
+ // 0.00001);
+ //
+ // double avgTemperature = resultSet.getDouble(4);
+ // assertTrue(deviceToAvgTemperature.get(actualDevice)[cnt] - avgTemperature < 0.00001);
+ //
+ // int countPrecipitation = resultSet.getInt(5);
+ // assertEquals(getCountNum(actualDevice, cnt), countPrecipitation);
+ // int countTemperature = resultSet.getInt(6);
+ // assertEquals(getCountNum(actualDevice, cnt), countTemperature);
+ //
+ // long maxPrecipitation = resultSet.getLong(7);
+ // assertEquals(deviceToMaxPrecipitation.get(actualDevice)[cnt], maxPrecipitation);
+ //
+ // double maxTemperature = resultSet.getDouble(8);
+ // assertTrue(deviceToMaxTemperature.get(actualDevice)[cnt] - maxTemperature < 0.00001);
+ // index++;
+ // if (index % 10 == 0) {
+ // cnt++;
+ // }
+ // }
+ // assertEquals(30, index);
+ // }
+ // } catch (Exception e) {
+ // e.printStackTrace();
+ // fail(e.getMessage());
+ // }
+ // }
+ //
+ // @Test
+ // public void groupByTimeOrderByTimeTest2() {
+ // String sql =
+ // "SELECT AVG(*),COUNT(*),MAX_VALUE(*) FROM weather GROUP
+ // BY([2022-11-21T00:00:00.000+08:00,2022-11-21T00:00:02.801+08:00),1000ms) ORDER BY TIME DESC";
+ // try (Connection connection = EnvFactory.getEnv().getConnection();
+ // Statement statement = connection.createStatement()) {
+ // long lastTime = Long.MAX_VALUE;
+ // String lastDevice = "";
+ // int index = 0;
+ // try (ResultSet resultSet = statement.executeQuery(sql)) {
+ // ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ // checkHeader(
+ // resultSetMetaData,
+ //
+ // "Time,Device,AVG(precipitation),AVG(temperature),COUNT(precipitation),COUNT(temperature),MAX_VALUE(precipitation),MAX_VALUE(temperature)");
+ // int cnt = 2;
+ // while (resultSet.next()) {
+ // long actualTime = resultSet.getLong(1);
+ // String actualDevice = resultSet.getString(2);
+ // assertTrue(actualTime <= lastTime);
+ // if (!Objects.equals(lastDevice, "") && actualTime == lastTime) {
+ // assertTrue(actualDevice.compareTo(lastDevice) >= 0);
+ // }
+ // lastTime = actualTime;
+ // lastDevice = actualDevice;
+ // double avgPrecipitation = resultSet.getDouble(3);
+ // assertTrue(deviceToAvgPrecipitation.get(actualDevice)[cnt] - avgPrecipitation <
+ // 0.00001);
+ //
+ // double avgTemperature = resultSet.getDouble(4);
+ // assertTrue(deviceToAvgTemperature.get(actualDevice)[cnt] - avgTemperature < 0.00001);
+ //
+ // int countPrecipitation = resultSet.getInt(5);
+ // assertEquals(getCountNum(actualDevice, cnt), countPrecipitation);
+ // int countTemperature = resultSet.getInt(6);
+ // assertEquals(getCountNum(actualDevice, cnt), countTemperature);
+ //
+ // long maxPrecipitation = resultSet.getLong(7);
+ // assertEquals(deviceToMaxPrecipitation.get(actualDevice)[cnt], maxPrecipitation);
+ //
+ // double maxTemperature = resultSet.getDouble(8);
+ // assertTrue(deviceToMaxTemperature.get(actualDevice)[cnt] - maxTemperature < 0.00001);
+ // index++;
+ // if (index % 10 == 0) {
+ // cnt--;
+ // }
+ // }
+ // assertEquals(30, index);
+ // }
+ // } catch (Exception e) {
+ // e.printStackTrace();
+ // fail(e.getMessage());
+ // }
+ // }
+ //
+ // @Test
+ // public void groupByTimeOrderByTimeTest3() {
+ // String sql =
+ // "SELECT AVG(*),COUNT(*),MAX_VALUE(*) FROM weather GROUP
+ // BY([2022-11-21T00:00:00.000+08:00,2022-11-21T00:00:02.801+08:00),1000ms) ORDER BY TIME
+ // ASC,DEVICE DESC";
+ // try (Connection connection = EnvFactory.getEnv().getConnection();
+ // Statement statement = connection.createStatement()) {
+ // long lastTime = -1;
+ // String lastDevice = "";
+ // int index = 0;
+ // try (ResultSet resultSet = statement.executeQuery(sql)) {
+ // ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ // checkHeader(
+ // resultSetMetaData,
+ //
+ // "Time,Device,AVG(precipitation),AVG(temperature),COUNT(precipitation),COUNT(temperature),MAX_VALUE(precipitation),MAX_VALUE(temperature)");
+ // int cnt = 0;
+ // while (resultSet.next()) {
+ // long actualTime = resultSet.getLong(1);
+ // String actualDevice = resultSet.getString(2);
+ // assertTrue(actualTime >= lastTime);
+ // if (!Objects.equals(lastDevice, "") && actualTime == lastTime) {
+ // assertTrue(actualDevice.compareTo(lastDevice) <= 0);
+ // }
+ // lastTime = actualTime;
+ // lastDevice = actualDevice;
+ // double avgPrecipitation = resultSet.getDouble(3);
+ // assertTrue(deviceToAvgPrecipitation.get(actualDevice)[cnt] - avgPrecipitation <
+ // 0.00001);
+ //
+ // double avgTemperature = resultSet.getDouble(4);
+ // assertTrue(deviceToAvgTemperature.get(actualDevice)[cnt] - avgTemperature < 0.00001);
+ //
+ // int countPrecipitation = resultSet.getInt(5);
+ // assertEquals(getCountNum(actualDevice, cnt), countPrecipitation);
+ // int countTemperature = resultSet.getInt(6);
+ // assertEquals(getCountNum(actualDevice, cnt), countTemperature);
+ //
+ // long maxPrecipitation = resultSet.getLong(7);
+ // assertEquals(deviceToMaxPrecipitation.get(actualDevice)[cnt], maxPrecipitation);
+ //
+ // double maxTemperature = resultSet.getDouble(8);
+ // assertTrue(deviceToMaxTemperature.get(actualDevice)[cnt] - maxTemperature < 0.00001);
+ // index++;
+ // if (index % 10 == 0) {
+ // cnt++;
+ // }
+ // }
+ // assertEquals(30, index);
+ // }
+ // } catch (Exception e) {
+ // e.printStackTrace();
+ // fail(e.getMessage());
+ // }
+ // }
+ //
+ // @Test
+ // public void groupByTimeOrderByTimeTest4() {
+ // String sql =
+ // "SELECT AVG(*),COUNT(*),MAX_VALUE(*) FROM weather GROUP
+ // BY([2022-11-21T00:00:00.000+08:00,2022-11-21T00:00:02.801+08:00),1000ms) ORDER BY TIME
+ // DESC,DEVICE DESC";
+ // try (Connection connection = EnvFactory.getEnv().getConnection();
+ // Statement statement = connection.createStatement()) {
+ // long lastTime = Long.MAX_VALUE;
+ // String lastDevice = "";
+ // int index = 0;
+ // try (ResultSet resultSet = statement.executeQuery(sql)) {
+ // ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
+ // checkHeader(
+ // resultSetMetaData,
+ //
+ // "Time,Device,AVG(precipitation),AVG(temperature),COUNT(precipitation),COUNT(temperature),MAX_VALUE(precipitation),MAX_VALUE(temperature)");
+ // int cnt = 2;
+ // while (resultSet.next()) {
+ // long actualTime = resultSet.getLong(1);
+ // String actualDevice = resultSet.getString(2);
+ // assertTrue(actualTime <= lastTime);
+ // if (!Objects.equals(lastDevice, "") && actualTime == lastTime) {
+ // assertTrue(actualDevice.compareTo(lastDevice) <= 0);
+ // }
+ // lastTime = actualTime;
+ // lastDevice = actualDevice;
+ // double avgPrecipitation = resultSet.getDouble(3);
+ // assertTrue(deviceToAvgPrecipitation.get(actualDevice)[cnt] - avgPrecipitation <
+ // 0.00001);
+ //
+ // double avgTemperature = resultSet.getDouble(4);
+ // assertTrue(deviceToAvgTemperature.get(actualDevice)[cnt] - avgTemperature < 0.00001);
+ //
+ // int countPrecipitation = resultSet.getInt(5);
+ // assertEquals(getCountNum(actualDevice, cnt), countPrecipitation);
+ // int countTemperature = resultSet.getInt(6);
+ // assertEquals(getCountNum(actualDevice, cnt), countTemperature);
+ //
+ // long maxPrecipitation = resultSet.getLong(7);
+ // assertEquals(deviceToMaxPrecipitation.get(actualDevice)[cnt], maxPrecipitation);
+ //
+ // double maxTemperature = resultSet.getDouble(8);
+ // assertTrue(deviceToMaxTemperature.get(actualDevice)[cnt] - maxTemperature < 0.00001);
+ // index++;
+ // if (index % 10 == 0) {
+ // cnt--;
+ // }
+ // }
+ // assertEquals(30, index);
+ // }
+ // } catch (Exception e) {
+ // e.printStackTrace();
+ // fail(e.getMessage());
+ // }
+ // }
+
+ // test the optimized plan
+ public static String[] optimizedSQL =
+ new String[] {
+ "create table optimize(plant_id STRING ID, device_id STRING ID, temperature DOUBLE MEASUREMENT, status BOOLEAN MEASUREMENT, hardware STRING MEASUREMENT);",
+ "insert into optimize(Time, plant_id, device_id, temperature, status) values(2017-11-01T00:00:00.000+08:00, 'wf01', 'wt01', 25.96, true)",
+ "insert into optimize(Time, plant_id, device_id, temperature, status) values(2017-11-01T00:01:00.000+08:00, 'wf01', 'wt01', 24.36, true)",
+ "insert into optimize(Time, plant_id, device_id, status, hardware) values(1970-01-01T08:00:00.001+08:00, 'wf02', 'wt02', true, 'v1')",
+ "insert into optimize(Time, plant_id, device_id, status, hardware) values(1970-01-01T08:00:00.002+08:00, 'wf02', 'wt02', false, 'v2')",
+ "insert into optimize(Time, plant_id, device_id, status, hardware) values(2017-11-01T00:00:00.000+08:00, 'wf02', 'wt02', false, 'v2')",
+ "insert into optimize(Time, plant_id, device_id, status, hardware) values(2017-11-01T00:01:00.000+08:00, 'wf02', 'wt02', true, 'v2')",
+ };
+
+ @Test
+ public void optimizedPlanTest() {
+
+ String[] expectedHeader =
+ new String[] {"Time", "plant_id", "device_id", "temperature", "status", "hardware"};
+ String[] retArray =
+ new String[] {
+ "2017-11-01T00:01:00.000Z,wf02,wt02,null,true,v2",
+ "2017-11-01T00:01:00.000Z,wf01,wt01,24.36,true,null",
+ "2017-11-01T00:00:00.000Z,wf02,wt02,null,false,v2",
+ "2017-11-01T00:00:00.000Z,wf01,wt01,25.96,true,null",
+ "1970-01-01T08:00:00.002Z,wf02,wt02,null,false,v2",
+ "1970-01-01T08:00:00.001Z,wf02,wt02,null,true,v1",
+ };
+
+ tableResultSetEqualTest(
+ "SELECT * FROM optimize ORDER BY Time DESC,plant_id DESC,device_id desc",
+ expectedHeader,
+ retArray,
+ DATABASE_NAME);
+ }
+}