This is an automated email from the ASF dual-hosted git repository. caogaofei pushed a commit to branch fix_join in repository https://gitbox.apache.org/repos/asf/iotdb.git
commit 300c514405145cfdcdb3d607643992773ed7185b Author: Beyyes <[email protected]> AuthorDate: Sat Nov 23 21:14:04 2024 +0800 add it --- .../db/it/IoTDBMultiIDsWithAttributesTableIT.java | 94 +++++++++++++++++----- .../optimizations/PushPredicateIntoTableScan.java | 3 +- 2 files changed, 76 insertions(+), 21 deletions(-) diff --git a/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiIDsWithAttributesTableIT.java b/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiIDsWithAttributesTableIT.java index 1f414e41e0c..110fc343b39 100644 --- a/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiIDsWithAttributesTableIT.java +++ b/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiIDsWithAttributesTableIT.java @@ -32,7 +32,9 @@ import org.junit.runner.RunWith; import java.sql.Connection; import java.sql.Statement; +import java.util.Arrays; +import static org.apache.iotdb.db.it.utils.TestUtils.tableAssertTestFail; import static org.apache.iotdb.db.it.utils.TestUtils.tableResultSetEqualTest; import static org.junit.Assert.fail; @@ -86,15 +88,6 @@ public class IoTDBMultiIDsWithAttributesTableIT { "insert into table0(device,level,time,num,bigNum,floatNum,str,bool,date) values('d2','l5',51536000000,15,3147483648,235.213,'watermelon',TRUE,'2023-01-01')" }; - // public static void main(String[] args) { - // for (String sql : sql1) { - // System.out.println(sql+";"); - // } - // for (String sql : sql2) { - // System.out.println(sql+";"); - // } - // } - private static final String[] sql3 = new String[] { "CREATE TABLE table1 (device string id, level string id, attr1 string attribute, attr2 string attribute, num int32 measurement, bigNum int64 measurement, " @@ -120,6 +113,35 @@ public class IoTDBMultiIDsWithAttributesTableIT { "insert into table1(time, device, level, attr1, attr2, num,bigNum,floatNum,str,bool) values(40, 'd11', 'l11', 'c', 'd', 3, 2947483648, 231.2121, 'coconut', FALSE)" }; + private static final String[] sql4 = + new String[] { + "create table students(region STRING ID, student_id INT32 MEASUREMENT, name STRING MEASUREMENT, genders text MEASUREMENT, date_of_birth DATE MEASUREMENT)", + "create table teachers(region STRING ID, teacher_id INT32 MEASUREMENT, course_id INT32 MEASUREMENT, age INT32 MEASUREMENT)", + "create table courses(course_id STRING ID, course_name STRING MEASUREMENT, teacher_id INT32 MEASUREMENT)", + "create table grades(grade_id STRING ID, course_id INT32 MEASUREMENT, student_id INT32 MEASUREMENT, score INT32 MEASUREMENT)", + "insert into students(time,region,student_id,name,genders,date_of_birth) values" + + "(1,'haidian',1,'Lucy','女','2015-10-10'),(2,'haidian',2,'Jack','男','2015-09-24'),(3,'chaoyang',3,'Sam','男','2014-07-20'),(4,'chaoyang',4,'Lily','女','2015-03-28')," + + "(5,'xicheng',5,'Helen','女','2016-01-22'),(6,'changping',6,'Nancy','女','2017-12-20'),(7,'changping',7,'Mike','男','2016-11-22'),(8,'shunyi',8,'Bob','男','2016-05-12')", + "insert into teachers(time,region,teacher_id,course_id,age) values" + + "(1,'haidian',1001,10000001,25),(2,'haidian',1002,10000002,26),(3,'chaoyang',1003,10000003,28)," + + "(4,'chaoyang',1004,10000004,27),(5,'xicheng',1005,10000005,26)", + "insert into courses(time,course_id,course_name,teacher_id) values" + + "(1,10000001,'数学',1001),(2,10000002,'语文',1002),(3,10000003,'英语',1003)," + + "(4,10000004,'体育',1004),(5,10000005,'历史',1005)", + "insert into grades(time,grade_id,course_id,student_id,score) values" + + "(1,1111,10000001,1,99),(2,1112,10000002,2,90),(3,1113,10000003,3,85),(4,1114,10000004,4,89),(5,1115,10000005,5,98)," + + "(6,1113,10000003,6,55),(7,1114,10000004,7,60),(8,1115,10000005,8,100),(9,1114,10000001,2,99),(10,1115,10000002,1,95)" + }; + + // public static void main(String[] args) { + // for (String sql : sql1) { + // System.out.println(sql+";"); + // } + // for (String sql : sql2) { + // System.out.println(sql+";"); + // } + // } + String[] expectedHeader; String[] retArray; String sql; @@ -144,15 +166,10 @@ public class IoTDBMultiIDsWithAttributesTableIT { private static void insertData() { try (Connection connection = EnvFactory.getEnv().getTableConnection(); Statement statement = connection.createStatement()) { - - for (String sql : sql1) { - statement.execute(sql); - } - for (String sql : sql2) { - statement.execute(sql); - } - for (String sql : sql3) { - statement.execute(sql); + for (String[] sqlList : Arrays.asList(sql1, sql2, sql3, sql4)) { + for (String sql : sqlList) { + statement.execute(sql); + } } } catch (Exception e) { e.printStackTrace(); @@ -1579,9 +1596,9 @@ public class IoTDBMultiIDsWithAttributesTableIT { // has filter @Test public void fullOuterJoinTest2() { - String[] expectedHeader = + expectedHeader = new String[] {"time", "device", "level", "t1_num_add", "device", "attr2", "num", "str"}; - String[] retArray = + retArray = new String[] { "1970-01-01T00:00:00.000Z,null,null,null,d1,d,3,coconut,", "1970-01-01T00:00:00.000Z,null,null,null,d2,c,3,coconut,", @@ -1636,6 +1653,43 @@ public class IoTDBMultiIDsWithAttributesTableIT { tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME); } + @Test + public void fourTableJoinTest() { + expectedHeader = + new String[] { + "time", "s_id", "s_name", "s_birth", "t_id", "t_c_id", "c_name", "g_id", "score" + }; + retArray = + new String[] { + "1970-01-01T00:00:00.001Z,1,Lucy,2015-10-10,1001,10000001,数学,1111,99,", + "1970-01-01T00:00:00.002Z,2,Jack,2015-09-24,1002,10000002,语文,1112,90,", + "1970-01-01T00:00:00.003Z,3,Sam,2014-07-20,1003,10000003,英语,1113,85,", + "1970-01-01T00:00:00.004Z,4,Lily,2015-03-28,1004,10000004,体育,1114,89,", + }; + sql = + "select s.time," + + " s.student_id as s_id, s.name as s_name, s.date_of_birth as s_birth," + + " t.teacher_id as t_id, t.course_id as t_c_id," + + " c.course_name as c_name," + + " g.grade_id as g_id, g.score as score " + + "from students s, teachers t, courses c, grades g " + + "where s.time=t.time AND c.time=g.time AND s.time=c.time " + + "order by s.student_id, t.teacher_id, c.course_id,g.grade_id"; + tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME); + + sql = + "select s.region, s.name," + + " t.teacher_id," + + " c.course_name," + + " g.score " + + "from students s, teachers t, courses c, grades g " + + "where s.time=c.time and c.time=g.time"; + tableAssertTestFail( + sql, + "701: Cross join is not supported in current version, each table must have at least one equiJoinClause", + DATABASE_NAME); + } + public static String[] buildHeaders(int length) { String[] expectedHeader = new String[length]; for (int i = 0; i < length; i++) { diff --git a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/PushPredicateIntoTableScan.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/PushPredicateIntoTableScan.java index c23a97d3650..2f78669365f 100644 --- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/PushPredicateIntoTableScan.java +++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/optimizations/PushPredicateIntoTableScan.java @@ -24,6 +24,7 @@ import org.apache.iotdb.commons.partition.DataPartition; import org.apache.iotdb.commons.partition.DataPartitionQueryParam; import org.apache.iotdb.db.conf.IoTDBConfig; import org.apache.iotdb.db.conf.IoTDBDescriptor; +import org.apache.iotdb.db.exception.sql.SemanticException; import org.apache.iotdb.db.queryengine.common.MPPQueryContext; import org.apache.iotdb.db.queryengine.common.QueryId; import org.apache.iotdb.db.queryengine.metric.QueryPlanCostMetricSet; @@ -641,7 +642,7 @@ public class PushPredicateIntoTableScan implements PlanOptimizer { } if (((JoinNode) output).isCrossJoin()) { - throw new IllegalStateException( + throw new SemanticException( "Cross join is not supported in current version, each table must have at least one equiJoinClause"); }
