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 ae4351d73236230c41babd54a31e71a1b7b8cdad Author: JackieTien97 <[email protected]> AuthorDate: Wed Jul 24 18:13:24 2024 +0800 done --- .../it/query/old/IoTDBDatetimeFormatIT.java | 10 +-- .../old/alignbydevice/IoTDBAlignByDeviceIT.java | 36 ++++----- .../IoTDBOrderByWithAlignByDeviceIT.java | 32 ++++---- .../scalar/IoTDBDiffFunctionIT.java | 90 +--------------------- 4 files changed, 42 insertions(+), 126 deletions(-) diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/IoTDBDatetimeFormatIT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/IoTDBDatetimeFormatIT.java index bf237caa66d..2491934de14 100644 --- a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/IoTDBDatetimeFormatIT.java +++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/IoTDBDatetimeFormatIT.java @@ -46,7 +46,7 @@ public class IoTDBDatetimeFormatIT { private static final String DATABASE_NAME = "db"; @BeforeClass - public void setUp() throws Exception { + public static void setUp() throws Exception { EnvFactory.getEnv().getConfig().getCommonConfig().setTimestampPrecisionCheckEnabled(false); EnvFactory.getEnv().initClusterEnvironment(); @@ -55,12 +55,12 @@ public class IoTDBDatetimeFormatIT { statement.execute("create database " + DATABASE_NAME); statement.execute("use " + DATABASE_NAME); statement.execute( - "create table table1(device_id STRING ID, s1 INT32 MEASUREMENT, s2 DOUBLE MEASUREMENT);"); + "create table table1(device_id STRING ID, s1 INT32 MEASUREMENT, s2 DOUBLE MEASUREMENT)"); } } @AfterClass - public void tearDown() throws Exception { + public static void tearDown() throws Exception { EnvFactory.getEnv().cleanClusterEnvironment(); } @@ -106,7 +106,7 @@ public class IoTDBDatetimeFormatIT { String insertSql = String.format( "INSERT INTO table1(time, device_id, s1) values (%s, %s, %d)", - datetimeStrings[i], "d1", i); + datetimeStrings[i], "'d1'", i); statement.execute(insertSql); } @@ -135,7 +135,7 @@ public class IoTDBDatetimeFormatIT { statement.execute("use " + DATABASE_NAME); statement.execute( - "insert into table1(time,device_id,s2) values (1618283005586000, 'd1',8.76);"); + "insert into table1(time,device_id,s2) values (1618283005586000, 'd1',8.76)"); try (ResultSet resultSet = statement.executeQuery( diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBAlignByDeviceIT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBAlignByDeviceIT.java index d2f1c32dbe1..d630477511b 100644 --- a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBAlignByDeviceIT.java +++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/alignbydevice/IoTDBAlignByDeviceIT.java @@ -87,8 +87,8 @@ public class IoTDBAlignByDeviceIT { "insert into vehicle(time, device_id, s3) values(2000-01-01T00:00:00+08:00,'d0', 'good')", "insert into vehicle(time, device_id, s4) values(100,'d0', false)", "insert into vehicle(time, device_id, s4) values(100,'d0', true)", - "insert into vehicle(time, device_id, s0) values(1,'d0', 999)", - "insert into vehicle(time, device_id, s0) values(1000,'d0', 888)", + "insert into vehicle(time, device_id, s0) values(1,'d1', 999)", + "insert into vehicle(time, device_id, s0) values(1000,'d1', 888)", }; @BeforeClass @@ -136,7 +136,7 @@ public class IoTDBAlignByDeviceIT { "1970-01-01T00:00:00.105Z,d0,99,199,11.11,null,null,", "1970-01-01T00:00:00.106Z,d0,99,null,null,null,null,", "1970-01-01T00:00:01.000Z,d0,22222,55555,1000.11,null,null,", - "2000-01-01T00:00:00.000Z,d0,null,100,null,good,null,", + "1999-12-31T16:00:00.000Z,d0,null,100,null,good,null,", "1970-01-01T00:00:00.001Z,d1,999,null,null,null,null,", "1970-01-01T00:00:01.000Z,d1,888,null,null,null,null,", }; @@ -170,7 +170,7 @@ public class IoTDBAlignByDeviceIT { String[] retArray = new String[] { "1970-01-01T00:00:00.001Z,d1,999,null,null,null,null,", - "2000-01-01T00:00:00.000Z,d0,null,100,null,good,null,", + "1999-12-31T16:00:00.000Z,d0,null,100,null,good,null,", "1970-01-01T00:00:01.000Z,d0,22222,55555,1000.11,null,null,", "1970-01-01T00:00:00.106Z,d0,99,null,null,null,null,", "1970-01-01T00:00:00.105Z,d0,99,199,11.11,null,null,", @@ -188,7 +188,7 @@ public class IoTDBAlignByDeviceIT { String[] retArray = new String[] { "1970-01-01T00:00:00.001Z,d0,101,101,1101,", - "1970-01-01T00:00:00.002Z,d0,101,10000,40000,", + "1970-01-01T00:00:00.002Z,d0,10000,10000,40000,", "1970-01-01T00:00:00.050Z,d0,10000,10000,50000,", "1970-01-01T00:00:00.100Z,d0,99,99,199,", "1970-01-01T00:00:00.101Z,d0,99,99,199,", @@ -198,7 +198,7 @@ public class IoTDBAlignByDeviceIT { "1970-01-01T00:00:00.105Z,d0,99,99,199,", "1970-01-01T00:00:00.106Z,d0,99,99,null,", "1970-01-01T00:00:01.000Z,d0,22222,22222,55555,", - "2000-01-01T00:00:00.000Z,d0,null,null,100,", + "1999-12-31T16:00:00.000Z,d0,null,null,100,", "1970-01-01T00:00:00.001Z,d1,999,999,null,", "1970-01-01T00:00:01.000Z,d1,888,888,null,", }; @@ -214,7 +214,7 @@ public class IoTDBAlignByDeviceIT { String[] expectedHeader = new String[] {"time", "device_id", "s0", "s0", "s1"}; String[] retArray = new String[] { - "1970-01-01T00:00:00.002Z,d0,101,10000,40000,", + "1970-01-01T00:00:00.002Z,d0,10000,10000,40000,", "1970-01-01T00:00:00.050Z,d0,10000,10000,50000,", "1970-01-01T00:00:00.100Z,d0,99,99,199,", "1970-01-01T00:00:00.101Z,d0,99,99,199,", @@ -264,10 +264,10 @@ public class IoTDBAlignByDeviceIT { "1970-01-01T00:00:00.103Z,d0,99,", "1970-01-01T00:00:00.104Z,d0,90,", "1970-01-01T00:00:00.105Z,d0,99,", - "2000-01-01T00:00:00.000Z,d0,null", + "1999-12-31T16:00:00.000Z,d0,null,", }; tableResultSetEqualTest( - "select time, device_id, s0 from root.vehicle.d0 where s1 < 200 order by device_id", + "select time, device_id, s0 from vehicle where s1 < 200 order by device_id", expectedHeader, retArray, DATABASE_NAME); @@ -282,7 +282,7 @@ public class IoTDBAlignByDeviceIT { "1970-01-01T00:00:00.105Z,d0,99,", }; tableResultSetEqualTest( - "select time, device_id, s0 from root.vehicle.d0 where s1 < 200 and s2 > 10 order by device_id", + "select time, device_id, s0 from vehicle where s1 < 200 and s2 > 10 order by device_id", expectedHeader, retArray, DATABASE_NAME); @@ -441,17 +441,17 @@ public class IoTDBAlignByDeviceIT { public void unusualCaseTest2() { String[] expectedHeader = - new String[] {"time", "device_id", "s0", "s0", "s1", "s0", "s1", "s2", "s3", "s4"}; + new String[] {"s0", "s0", "s1", "time", "device_id", "s0", "s1", "s2", "s3", "s4"}; String[] retArray = new String[] { - "1970-01-01T00:00:00.001Z,d0,101,101,1101,101,1101,null,null,null,", - "1970-01-01T00:00:00.002Z,d0,10000,10000,40000,10000,40000,2.22,null,null,", - "1970-01-01T00:00:00.003Z,d0,null,null,null,null,null,3.33,null,null,", - "1970-01-01T00:00:00.004Z,d0,null,null,null,null,null,4.44,null,null,", - "1970-01-01T00:00:00.001Z,d1,999,999,null,999,null,null,null,null,", + "101,101,1101,1970-01-01T00:00:00.001Z,d0,101,1101,null,null,null,", + "10000,10000,40000,1970-01-01T00:00:00.002Z,d0,10000,40000,2.22,null,null,", + "null,null,null,1970-01-01T00:00:00.003Z,d0,null,null,3.33,null,null,", + "null,null,null,1970-01-01T00:00:00.004Z,d0,null,null,4.44,null,null,", + "999,999,null,1970-01-01T00:00:00.001Z,d1,999,null,null,null,null,", }; tableResultSetEqualTest( - "select * from vehicle where time < 20 and (device_id='d0' or device_id='d1') order by device_id", + "select s0,s0,s1,* from vehicle where time < 20 and (device_id='d0' or device_id='d1') order by device_id", expectedHeader, retArray, DATABASE_NAME); @@ -478,7 +478,7 @@ public class IoTDBAlignByDeviceIT { "1970-01-01T00:00:00.105Z,d0,99,199,11.11,null,null,", "1970-01-01T00:00:00.106Z,d0,99,null,null,null,null,", "1970-01-01T00:00:01.000Z,d0,22222,55555,1000.11,null,null,", - "2000-01-01T00:00:00.000Z,d0,null,100,null,good,null,", + "1999-12-31T16:00:00.000Z,d0,null,100,null,good,null,", "1970-01-01T00:00:00.001Z,d1,999,null,null,null,null,", "1970-01-01T00:00:01.000Z,d1,888,null,null,null,null,", }; 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 index da9b6faba72..e89cc9b4285 100644 --- 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 @@ -104,7 +104,7 @@ public class IoTDBOrderByWithAlignByDeviceIT { statement.execute("use " + DATABASE_NAME); statement.execute( - "create table weather(city STRING ID, precipitation INT64 MEASUREMENT, temperature DOUBLE MEASUREMENT);"); + "create table weather(city STRING ID, precipitation INT64 MEASUREMENT, temperature DOUBLE MEASUREMENT)"); // insert data long start = startTime; @@ -186,7 +186,7 @@ public class IoTDBOrderByWithAlignByDeviceIT { 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);"); + statement.execute("create table overflow(device_id STRING ID, value INT32 MEASUREMENT)"); long startTime = 1; for (int i = 0; i < 20; i++) { String insertTime = @@ -242,7 +242,7 @@ public class IoTDBOrderByWithAlignByDeviceIT { 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("SELECT * FROM weather order by city")) { ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) { @@ -289,6 +289,7 @@ public class IoTDBOrderByWithAlignByDeviceIT { 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("SELECT * FROM weather order by city asc")) { @@ -338,6 +339,7 @@ public class IoTDBOrderByWithAlignByDeviceIT { 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("SELECT * FROM weather order by city desc")) { @@ -399,7 +401,7 @@ public class IoTDBOrderByWithAlignByDeviceIT { expectedHeader); orderByTimeTest1( - "SELECT * FROM weather ORDER BY time ASC, city ASC LIMIT 100E", 100, expectedHeader); + "SELECT * FROM weather ORDER BY time ASC, city ASC LIMIT 100", 100, expectedHeader); } @Test @@ -439,7 +441,9 @@ public class IoTDBOrderByWithAlignByDeviceIT { expectedHeader); orderByExpressionTest1( - "SELECT * FROM weather ORDER BY precipitation DESC, time DESC, city", 100, expectedHeader); + "SELECT * FROM weather ORDER BY precipitation DESC, time DESC, city limit 100", + 100, + expectedHeader); } @Test @@ -527,12 +531,12 @@ public class IoTDBOrderByWithAlignByDeviceIT { String[] expectedHeader = new String[] {"time", "city", "precipitation", "temperature"}; orderByTimeDeviceTest4( - "SELECT * FROM weather ORDER BY time DESC,DEVICE ASC", + "SELECT * FROM weather ORDER BY time DESC,city ASC", numOfPointsInDevice * places.length, expectedHeader); orderByTimeDeviceTest4( - "SELECT * FROM weather ORDER BY time DESC,DEVICE ASC LIMIT 100", 100, expectedHeader); + "SELECT * FROM weather ORDER BY time DESC,city ASC LIMIT 100", 100, expectedHeader); } public static void orderByTimeTest1(String sql, int count, String[] expectedHeader) { @@ -1531,7 +1535,7 @@ public class IoTDBOrderByWithAlignByDeviceIT { // 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);", + "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')", @@ -1547,12 +1551,12 @@ public class IoTDBOrderByWithAlignByDeviceIT { new String[] {"time", "plant_id", "device_id", "temperature", "status", "hardware"}; String[] retArray = new String[] { - "2017-10-31T16:01:00.000Z,wf02,wt02,null,true,v2", - "2017-10-31T16:01:00.000Z,wf01,wt01,24.36,true,null", - "2017-10-31T16:00:00.000Z,wf02,wt02,null,false,v2", - "2017-10-31T16:00:00.000Z,wf01,wt01,25.96,true,null", - "1970-01-01T00:00:00.002Z,wf02,wt02,null,false,v2", - "1970-01-01T00:00:00.001Z,wf02,wt02,null,true,v1", + "2017-10-31T16:01:00.000Z,wf02,wt02,null,true,v2,", + "2017-10-31T16:01:00.000Z,wf01,wt01,24.36,true,null,", + "2017-10-31T16:00:00.000Z,wf02,wt02,null,false,v2,", + "2017-10-31T16:00:00.000Z,wf01,wt01,25.96,true,null,", + "1970-01-01T00:00:00.002Z,wf02,wt02,null,false,v2,", + "1970-01-01T00:00:00.001Z,wf02,wt02,null,true,v1,", }; tableResultSetEqualTest( diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/builtinfunction/scalar/IoTDBDiffFunctionIT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/builtinfunction/scalar/IoTDBDiffFunctionIT.java index 3d63a419f96..bb34853abf0 100644 --- a/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/builtinfunction/scalar/IoTDBDiffFunctionIT.java +++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/query/old/builtinfunction/scalar/IoTDBDiffFunctionIT.java @@ -47,7 +47,7 @@ public class IoTDBDiffFunctionIT { new String[] { "CREATE DATABASE " + DATABASE_NAME, "use " + DATABASE_NAME, - "create table table1(device_id STRING ID, s1 INT32 MEASUREMENT, s2 INT32 MEASUREMENT)", + "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, 1)", "INSERT INTO table1(time,device_id,s1) values(2, 'd1', 2)", "INSERT INTO table1(time,device_id,s2) values(3, 'd1', 3)", @@ -106,51 +106,6 @@ public class IoTDBDiffFunctionIT { expectedHeader, retArray, DATABASE_NAME); - - // align by device - expectedHeader = new String[] {"time", "device_id", "_col1", "_col2"}; - retArray = - new String[] { - "1970-01-01T00:00:00.001Z,d1,null,null,", - "1970-01-01T00:00:00.002Z,d1,1.0,null,", - "1970-01-01T00:00:00.003Z,d1,null,2.0,", - "1970-01-01T00:00:00.004Z,d1,2.0,null,", - "1970-01-01T00:00:00.005Z,d1,1.0,2.0,", - "1970-01-01T00:00:00.006Z,d1,null,1.0,", - "1970-02-27T20:53:20.000Z,d1,null,1.0,", - "1970-02-27T20:53:20.001Z,d1,3.0,null,", - "1970-01-01T00:00:00.001Z,d2,-7.0,-6.0", - "1970-01-01T00:00:00.002Z,d2,1.0,1.0,", - "1970-02-27T20:53:20.000Z,d2,null,1.0,", - "1970-02-27T20:53:20.001Z,d2,2.0,null,", - }; - tableResultSetEqualTest( - "select time, device_id, Diff(s1), diff(s2) from table1 where device_id='d1' or device_id='d2' order by device_id", - expectedHeader, - retArray, - DATABASE_NAME); - - // align by device + order by time - retArray = - new String[] { - "1970-01-01T00:00:00.001Z,d1,null,null,", - "1970-01-01T00:00:00.001Z,d2,null,null", - "1970-01-01T00:00:00.002Z,d1,1.0,null,", - "1970-01-01T00:00:00.002Z,d2,1.0,1.0,", - "1970-01-01T00:00:00.003Z,d1,null,2.0,", - "1970-01-01T00:00:00.004Z,d1,2.0,null,", - "1970-01-01T00:00:00.005Z,d1,1.0,2.0,", - "1970-01-01T00:00:00.006Z,d1,null,1.0,", - "1970-02-27T20:53:20.000Z,d1,null,1.0,", - "1970-02-27T20:53:20.000Z,d2,null,1.0,", - "1970-02-27T20:53:20.001Z,d1,3.0,null,", - "1970-02-27T20:53:20.001Z,d2,2.0,null,", - }; - tableResultSetEqualTest( - "select time, device_id, Diff(s1), diff(s2) from table1 where device_id='d1' or device_id='d2' order by time,device_id", - expectedHeader, - retArray, - DATABASE_NAME); } @Test @@ -173,50 +128,7 @@ public class IoTDBDiffFunctionIT { retArray, DATABASE_NAME); - // align by device - expectedHeader = new String[] {"time", "device_id", "_col1", "_col2"}; - retArray = - new String[] { - "1970-01-01T00:00:00.001Z,d1,null,null,", - "1970-01-01T00:00:00.002Z,d1,1.0,null,", - "1970-01-01T00:00:00.003Z,d1,null,null,", - "1970-01-01T00:00:00.004Z,d1,null,null,", - "1970-01-01T00:00:00.005Z,d1,1.0,null,", - "1970-01-01T00:00:00.006Z,d1,null,1.0,", - "1970-02-27T20:53:20.000Z,d1,null,1.0,", - "1970-02-27T20:53:20.001Z,d1,null,null,", - "1970-01-01T00:00:00.001Z,d2,null,null", - "1970-01-01T00:00:00.002Z,d2,1.0,1.0,", - "1970-02-27T20:53:20.000Z,d2,null,1.0,", - "1970-02-27T20:53:20.001Z,d2,null,null,", - }; - tableResultSetEqualTest( - "select time, device_id, Diff(s1, false), diff(s2, false) from table1 where device_id='d1' or device_id='d2' order by device_id", - expectedHeader, - retArray, - DATABASE_NAME); - // align by device + order by time - retArray = - new String[] { - "1970-01-01T00:00:00.001Z,d1,null,null,", - "1970-01-01T00:00:00.001Z,d2,null,null", - "1970-01-01T00:00:00.002Z,d1,1.0,null,", - "1970-01-01T00:00:00.002Z,d2,1.0,1.0,", - "1970-01-01T00:00:00.003Z,d1,null,null,", - "1970-01-01T00:00:00.004Z,d1,null,null,", - "1970-01-01T00:00:00.005Z,d1,1.0,null,", - "1970-01-01T00:00:00.006Z,d1,null,1.0,", - "1970-02-27T20:53:20.000Z,d1,null,1.0,", - "1970-02-27T20:53:20.000Z,d2,null,1.0,", - "1970-02-27T20:53:20.001Z,d1,null,null,", - "1970-02-27T20:53:20.001Z,d2,null,null,", - }; - tableResultSetEqualTest( - "select time, device_id, Diff(s1, false), diff(s2, false) where device_id='d1' or device_id='d2' order by time,device_id", - expectedHeader, - retArray, - DATABASE_NAME); } @Test
