Fix multi table joins error and add more integration tests
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 1f414e4..110fc34 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 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 @@
"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 @@
"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 @@
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 @@
// 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 @@
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/node/JoinNode.java b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/JoinNode.java
index 0775075..3546c9a 100644
--- a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/JoinNode.java
+++ b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/node/JoinNode.java
@@ -44,6 +44,7 @@
import static com.google.common.base.Preconditions.checkArgument;
import static java.lang.String.format;
import static java.util.Objects.requireNonNull;
+import static org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.INNER;
public class JoinNode extends TwoChildProcessNode {
@@ -292,6 +293,10 @@
return spillable;
}
+ public boolean isCrossJoin() {
+ return criteria.isEmpty() && !filter.isPresent() && joinType == INNER;
+ }
+
@Override
public String toString() {
return "JoinNode-" + this.getPlanNodeId();
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 f9d2d5e..2f78669 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.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;
@@ -640,6 +641,11 @@
node.isSpillable());
}
+ if (((JoinNode) output).isCrossJoin()) {
+ throw new SemanticException(
+ "Cross join is not supported in current version, each table must have at least one equiJoinClause");
+ }
+
JoinNode.EquiJoinClause joinCriteria = ((JoinNode) output).getCriteria().get(0);
OrderingScheme leftOrderingScheme =
new OrderingScheme(
