This is an automated email from the ASF dual-hosted git repository.
jackietien pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/iotdb.git
The following commit(s) were added to refs/heads/master by this push:
new 8db63c19257 Support LEFT and RIGHT JOIN in TableModel
8db63c19257 is described below
commit 8db63c192576bebbf34ae7bec182eb38a1e33d8e
Author: Weihao Li <[email protected]>
AuthorDate: Fri May 9 09:09:06 2025 +0800
Support LEFT and RIGHT JOIN in TableModel
---
.../db/it/IoTDBMultiTAGsWithAttributesTableIT.java | 367 +++++++++++++++++++--
.../relational/analyzer/StatementAnalyzer.java | 7 +-
.../plan/relational/planner/RelationPlanner.java | 18 +-
.../plan/relational/planner/node/JoinNode.java | 39 +++
.../plan/relational/analyzer/JoinTest.java | 15 -
5 files changed, 389 insertions(+), 57 deletions(-)
diff --git
a/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiTAGsWithAttributesTableIT.java
b/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiTAGsWithAttributesTableIT.java
index de1215ee979..ea0628dd696 100644
---
a/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiTAGsWithAttributesTableIT.java
+++
b/integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiTAGsWithAttributesTableIT.java
@@ -37,6 +37,10 @@ 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.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.FULL;
+import static
org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.INNER;
+import static
org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.LEFT;
+import static
org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.RIGHT;
import static
org.apache.iotdb.db.queryengine.plan.relational.planner.optimizations.JoinUtils.ONLY_SUPPORT_EQUI_JOIN;
import static org.junit.Assert.fail;
@@ -1632,7 +1636,7 @@ public class IoTDBMultiTAGsWithAttributesTableIT {
// no filter
@Test
- public void timeColumnFullOuterJoinTest1() {
+ public void timeColumnOuterJoinTest1() {
expectedHeader =
new String[] {"time", "device", "level", "num", "device", "attr2",
"num", "str"};
retArray =
@@ -1702,35 +1706,37 @@ public class IoTDBMultiTAGsWithAttributesTableIT {
// join on
String sql =
"SELECT t1.time as time, t1.device, t1.level, t1.num, t2.device,
t2.attr2, t2.num, t2.str\n"
- + "FROM table0 t1 FULL JOIN table0 t2 ON t1.time = t2.time \n"
+ + "FROM table0 t1 %s JOIN table0 t2 ON t1.time = t2.time \n"
+ "ORDER BY t1.time, t1.device, t2.device";
- tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
- sql =
- "SELECT t1.time as time, t1.device, t1.level, t1.num, t2.device,
t2.attr2, t2.num, t2.str\n"
- + "FROM table0 t1 INNER JOIN table0 t2 ON t1.time = t2.time \n"
- + "ORDER BY t1.time, t1.device, t2.device";
- tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
+ tableResultSetEqualTest(String.format(sql, FULL), expectedHeader,
retArray, DATABASE_NAME);
+
+ tableResultSetEqualTest(String.format(sql, LEFT), expectedHeader,
retArray, DATABASE_NAME);
+
+ tableResultSetEqualTest(String.format(sql, RIGHT), expectedHeader,
retArray, DATABASE_NAME);
+
+ tableResultSetEqualTest(String.format(sql, INNER), expectedHeader,
retArray, DATABASE_NAME);
// join using
sql =
"SELECT time, t1.device, t1.level, t1.num, t2.device, t2.attr2,
t2.num, t2.str\n"
- + "FROM table0 t1 FULL OUTER JOIN table0 t2 USING(time)\n"
+ + "FROM table0 t1 %s JOIN table0 t2 USING(time)\n"
+ "ORDER BY time, t1.device, t2.device";
- tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
- sql =
- "SELECT time, t1.device, t1.level, t1.num, t2.device, t2.attr2,
t2.num, t2.str\n"
- + "FROM table0 t1 INNER JOIN table0 t2 USING(time)\n"
- + "ORDER BY time, t1.device, t2.device";
- tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
+ tableResultSetEqualTest(String.format(sql, FULL), expectedHeader,
retArray, DATABASE_NAME);
+
+ tableResultSetEqualTest(String.format(sql, LEFT), expectedHeader,
retArray, DATABASE_NAME);
+
+ tableResultSetEqualTest(String.format(sql, RIGHT), expectedHeader,
retArray, DATABASE_NAME);
+
+ tableResultSetEqualTest(String.format(sql, INNER), expectedHeader,
retArray, DATABASE_NAME);
sql =
"select t1.time as time1, t2.time as time2, "
+ " t1.device as device1, "
+ " t1.value as value1, "
+ " t2.device as device2, "
- + " t2.value as value2 from tableA t1 full join tableB t2 on
t1.time = t2.time "
+ + " t2.value as value2 from tableA t1 %s join tableB t2 on
t1.time = t2.time "
+ "order by COALESCE(time1, time2),device1,device2";
retArray =
new String[] {
@@ -1743,14 +1749,43 @@ public class IoTDBMultiTAGsWithAttributesTableIT {
"2020-01-01T00:00:07.000Z,null,d2,7,null,null,",
};
expectedHeader = new String[] {"time1", "time2", "device1", "value1",
"device2", "value2"};
- tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
+
+ tableResultSetEqualTest(String.format(sql, FULL), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray =
+ new String[] {
+ "2020-01-01T00:00:01.000Z,null,d1,1,null,null,",
+ "2020-01-01T00:00:03.000Z,2020-01-01T00:00:03.000Z,d1,3,d1,30,",
+ "2020-01-01T00:00:03.000Z,2020-01-01T00:00:03.000Z,d1,3,d333,333,",
+ "2020-01-01T00:00:05.000Z,2020-01-01T00:00:05.000Z,d2,5,d2,50,",
+ "2020-01-01T00:00:07.000Z,null,d2,7,null,null,",
+ };
+ tableResultSetEqualTest(String.format(sql, LEFT), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray =
+ new String[] {
+ "null,2020-01-01T00:00:02.000Z,null,null,d1,20,",
+ "2020-01-01T00:00:03.000Z,2020-01-01T00:00:03.000Z,d1,3,d1,30,",
+ "2020-01-01T00:00:03.000Z,2020-01-01T00:00:03.000Z,d1,3,d333,333,",
+ "null,2020-01-01T00:00:04.000Z,null,null,d2,40,",
+ "2020-01-01T00:00:05.000Z,2020-01-01T00:00:05.000Z,d2,5,d2,50,",
+ };
+ tableResultSetEqualTest(String.format(sql, RIGHT), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray =
+ new String[] {
+ "2020-01-01T00:00:03.000Z,2020-01-01T00:00:03.000Z,d1,3,d1,30,",
+ "2020-01-01T00:00:03.000Z,2020-01-01T00:00:03.000Z,d1,3,d333,333,",
+ "2020-01-01T00:00:05.000Z,2020-01-01T00:00:05.000Z,d2,5,d2,50,",
+ };
+ tableResultSetEqualTest(String.format(sql, INNER), expectedHeader,
retArray, DATABASE_NAME);
sql =
"select time, "
+ " t1.device as device1, "
+ " t1.value as value1, "
+ " t2.device as device2, "
- + " t2.value as value2 from tableA t1 full join tableB t2
USING(time) "
+ + " t2.value as value2 from tableA t1 %s join tableB t2
USING(time) "
+ "order by time,device1,device2";
retArray =
new String[] {
@@ -1763,7 +1798,36 @@ public class IoTDBMultiTAGsWithAttributesTableIT {
"2020-01-01T00:00:07.000Z,d2,7,null,null,",
};
expectedHeader = new String[] {"time", "device1", "value1", "device2",
"value2"};
- tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
+
+ tableResultSetEqualTest(String.format(sql, FULL), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray =
+ new String[] {
+ "2020-01-01T00:00:01.000Z,d1,1,null,null,",
+ "2020-01-01T00:00:03.000Z,d1,3,d1,30,",
+ "2020-01-01T00:00:03.000Z,d1,3,d333,333,",
+ "2020-01-01T00:00:05.000Z,d2,5,d2,50,",
+ "2020-01-01T00:00:07.000Z,d2,7,null,null,",
+ };
+ tableResultSetEqualTest(String.format(sql, LEFT), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray =
+ new String[] {
+ "2020-01-01T00:00:02.000Z,null,null,d1,20,",
+ "2020-01-01T00:00:03.000Z,d1,3,d1,30,",
+ "2020-01-01T00:00:03.000Z,d1,3,d333,333,",
+ "2020-01-01T00:00:04.000Z,null,null,d2,40,",
+ "2020-01-01T00:00:05.000Z,d2,5,d2,50,",
+ };
+ tableResultSetEqualTest(String.format(sql, RIGHT), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray =
+ new String[] {
+ "2020-01-01T00:00:03.000Z,d1,3,d1,30,",
+ "2020-01-01T00:00:03.000Z,d1,3,d333,333,",
+ "2020-01-01T00:00:05.000Z,d2,5,d2,50,",
+ };
+ tableResultSetEqualTest(String.format(sql, INNER), expectedHeader,
retArray, DATABASE_NAME);
// empty table join non-empty table
sql =
@@ -1771,7 +1835,7 @@ public class IoTDBMultiTAGsWithAttributesTableIT {
+ " t1.device as device1, "
+ " t1.value as value1, "
+ " t2.device as device2, "
- + " t2.value as value2 from tableC t1 full join tableB t2
USING(time) "
+ + " t2.value as value2 from tableC t1 %s join tableB t2
USING(time) "
+ "order by time,device1,device2";
retArray =
new String[] {
@@ -1782,7 +1846,24 @@ public class IoTDBMultiTAGsWithAttributesTableIT {
"2020-01-01T00:00:05.000Z,null,null,d2,50,",
};
expectedHeader = new String[] {"time", "device1", "value1", "device2",
"value2"};
- tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
+
+ tableResultSetEqualTest(String.format(sql, FULL), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray = new String[] {};
+ tableResultSetEqualTest(String.format(sql, LEFT), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray =
+ new String[] {
+ "2020-01-01T00:00:02.000Z,null,null,d1,20,",
+ "2020-01-01T00:00:03.000Z,null,null,d1,30,",
+ "2020-01-01T00:00:03.000Z,null,null,d333,333,",
+ "2020-01-01T00:00:04.000Z,null,null,d2,40,",
+ "2020-01-01T00:00:05.000Z,null,null,d2,50,",
+ };
+ tableResultSetEqualTest(String.format(sql, RIGHT), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray = new String[] {};
+ tableResultSetEqualTest(String.format(sql, INNER), expectedHeader,
retArray, DATABASE_NAME);
sql =
"select time, "
@@ -1790,7 +1871,7 @@ public class IoTDBMultiTAGsWithAttributesTableIT {
+ " t1.value as value1, "
+ " t2.device as device2, "
+ " t2.value as value2 "
- + "from (select * from tableA where device='d1') t1 full join
(select * from tableB where device='d2') t2 USING(time) order by time, device1,
device2";
+ + "from (select * from tableA where device='d1') t1 %s join
(select * from tableB where device='d2') t2 USING(time) order by time, device1,
device2";
retArray =
new String[] {
"2020-01-01T00:00:01.000Z,d1,1,null,null,",
@@ -1799,12 +1880,28 @@ public class IoTDBMultiTAGsWithAttributesTableIT {
"2020-01-01T00:00:05.000Z,null,null,d2,50,",
};
expectedHeader = new String[] {"time", "device1", "value1", "device2",
"value2"};
- tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
+
+ tableResultSetEqualTest(String.format(sql, FULL), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray =
+ new String[] {
+ "2020-01-01T00:00:01.000Z,d1,1,null,null,",
"2020-01-01T00:00:03.000Z,d1,3,null,null,",
+ };
+ tableResultSetEqualTest(String.format(sql, LEFT), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray =
+ new String[] {
+ "2020-01-01T00:00:04.000Z,null,null,d2,40,",
"2020-01-01T00:00:05.000Z,null,null,d2,50,",
+ };
+ tableResultSetEqualTest(String.format(sql, RIGHT), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray = new String[] {};
+ tableResultSetEqualTest(String.format(sql, INNER), expectedHeader,
retArray, DATABASE_NAME);
}
// has filter
@Test
- public void timeColumnFullOuterJoinTest2() {
+ public void timeColumnOuterJoinTest2() {
expectedHeader =
new String[] {"time", "device", "level", "t1_num_add", "device",
"attr2", "num", "str"};
retArray =
@@ -1846,22 +1943,84 @@ public class IoTDBMultiTAGsWithAttributesTableIT {
};
// join using
- String sql =
+ String sql1 =
"SELECT time, t1.device, t1.level, t1_num_add, t2.device, t2.attr2,
t2.num, t2.str\n"
+ "FROM (SELECT *,num+1 as t1_num_add FROM table0 WHERE TIME>=80
AND level!='l1' AND cast(num as double)>0) t1 \n"
- + "FULL JOIN (SELECT * FROM table0 WHERE TIME<=31536001000 AND
floatNum<1000 AND device in ('d1','d2')) t2 \n"
+ + "%s JOIN (SELECT * FROM table0 WHERE TIME<=31536001000 AND
floatNum<1000 AND device in ('d1','d2')) t2 \n"
+ "USING(time) \n"
+ "ORDER BY time, t1.device, t2.device";
- tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
+ tableResultSetEqualTest(String.format(sql1, FULL), expectedHeader,
retArray, DATABASE_NAME);
// join on
- sql =
+ String sql2 =
"SELECT COALESCE(t1.time, t2.time) as time, t1.device, t1.level,
t1_num_add, t2.device, t2.attr2, t2.num, t2.str\n"
+ "FROM (SELECT *,num+1 as t1_num_add FROM table0 WHERE TIME>=80
AND level!='l1' AND cast(num as double)>0) t1 \n"
- + "FULL JOIN (SELECT * FROM table0 WHERE TIME<=31536001000 AND
floatNum<1000 AND device in ('d1','d2')) t2 \n"
+ + "%s JOIN (SELECT * FROM table0 WHERE TIME<=31536001000 AND
floatNum<1000 AND device in ('d1','d2')) t2 \n"
+ "ON t1.time = t2.time \n"
+ "ORDER BY time, t1.device, t2.device";
- tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
+ tableResultSetEqualTest(String.format(sql2, FULL), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray =
+ new String[] {
+ "1970-01-01T00:00:00.080Z,d1,l4,10,d1,null,9,apple,",
+ "1970-01-01T00:00:00.080Z,d1,l4,10,d2,null,9,apple,",
+ "1970-01-01T00:00:00.080Z,d2,l4,10,d1,null,9,apple,",
+ "1970-01-01T00:00:00.080Z,d2,l4,10,d2,null,9,apple,",
+ "1970-01-01T00:00:00.100Z,d1,l5,9,null,null,null,null,",
+ "1970-01-01T00:00:00.100Z,d2,l5,9,null,null,null,null,",
+ "1971-01-01T00:00:00.100Z,d1,l2,11,d1,zz,10,pumelo,",
+ "1971-01-01T00:00:00.100Z,d1,l2,11,d2,null,10,pumelo,",
+ "1971-01-01T00:00:00.100Z,d2,l2,11,d1,zz,10,pumelo,",
+ "1971-01-01T00:00:00.100Z,d2,l2,11,d2,null,10,pumelo,",
+ "1971-01-01T00:00:00.500Z,d1,l3,5,d1,a,4,peach,",
+ "1971-01-01T00:00:00.500Z,d1,l3,5,d2,null,4,peach,",
+ "1971-01-01T00:00:00.500Z,d2,l3,5,d1,a,4,peach,",
+ "1971-01-01T00:00:00.500Z,d2,l3,5,d2,null,4,peach,",
+ "1971-01-01T00:00:01.000Z,d1,l4,6,d1,null,5,orange,",
+ "1971-01-01T00:00:01.000Z,d1,l4,6,d2,null,5,orange,",
+ "1971-01-01T00:00:01.000Z,d2,l4,6,d1,null,5,orange,",
+ "1971-01-01T00:00:01.000Z,d2,l4,6,d2,null,5,orange,",
+ "1971-01-01T00:00:10.000Z,d1,l5,8,null,null,null,null,",
+ "1971-01-01T00:00:10.000Z,d2,l5,8,null,null,null,null,",
+ "1971-04-26T17:46:40.000Z,d1,l2,13,null,null,null,null,",
+ "1971-04-26T17:46:40.000Z,d2,l2,13,null,null,null,null,",
+ "1971-04-26T17:46:40.020Z,d1,l3,15,null,null,null,null,",
+ "1971-04-26T17:46:40.020Z,d2,l3,15,null,null,null,null,",
+ "1971-04-26T18:01:40.000Z,d1,l4,14,null,null,null,null,",
+ "1971-04-26T18:01:40.000Z,d2,l4,14,null,null,null,null,",
+ "1971-08-20T11:33:20.000Z,d1,l5,16,null,null,null,null,",
+ "1971-08-20T11:33:20.000Z,d2,l5,16,null,null,null,null,",
+ };
+ tableResultSetEqualTest(String.format(sql1, LEFT), expectedHeader,
retArray, DATABASE_NAME);
+ tableResultSetEqualTest(String.format(sql2, LEFT), expectedHeader,
retArray, DATABASE_NAME);
+
+ 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,",
+ "1970-01-01T00:00:00.020Z,null,null,null,d1,zz,2,pineapple,",
+ "1970-01-01T00:00:00.020Z,null,null,null,d2,null,2,pineapple,",
+ "1970-01-01T00:00:00.040Z,null,null,null,d1,a,1,apricot,",
+ "1970-01-01T00:00:00.040Z,null,null,null,d2,null,1,apricot,",
+ "1970-01-01T00:00:00.080Z,d1,l4,10,d1,null,9,apple,",
+ "1970-01-01T00:00:00.080Z,d1,l4,10,d2,null,9,apple,",
+ "1970-01-01T00:00:00.080Z,d2,l4,10,d1,null,9,apple,",
+ "1970-01-01T00:00:00.080Z,d2,l4,10,d2,null,9,apple,",
+ "1971-01-01T00:00:00.100Z,d1,l2,11,d1,zz,10,pumelo,",
+ "1971-01-01T00:00:00.100Z,d1,l2,11,d2,null,10,pumelo,",
+ "1971-01-01T00:00:00.100Z,d2,l2,11,d1,zz,10,pumelo,",
+ "1971-01-01T00:00:00.100Z,d2,l2,11,d2,null,10,pumelo,",
+ "1971-01-01T00:00:00.500Z,d1,l3,5,d1,a,4,peach,",
+ "1971-01-01T00:00:00.500Z,d1,l3,5,d2,null,4,peach,",
+ "1971-01-01T00:00:00.500Z,d2,l3,5,d1,a,4,peach,",
+ "1971-01-01T00:00:00.500Z,d2,l3,5,d2,null,4,peach,",
+ "1971-01-01T00:00:01.000Z,d1,l4,6,d1,null,5,orange,",
+ "1971-01-01T00:00:01.000Z,d1,l4,6,d2,null,5,orange,",
+ "1971-01-01T00:00:01.000Z,d2,l4,6,d1,null,5,orange,",
+ "1971-01-01T00:00:01.000Z,d2,l4,6,d2,null,5,orange,",
+ };
+ tableResultSetEqualTest(String.format(sql1, RIGHT), expectedHeader,
retArray, DATABASE_NAME);
+ tableResultSetEqualTest(String.format(sql2, RIGHT), expectedHeader,
retArray, DATABASE_NAME);
}
@Test
@@ -2040,10 +2199,10 @@ public class IoTDBMultiTAGsWithAttributesTableIT {
}
@Test
- public void fullJoinTest() {
+ public void outerJoinTest() {
expectedHeader = new String[] {"date", "date"};
sql =
- "select t0.date, t1.date from table0 t0 full join table1 t1 on
t0.date=t1.date order by t0.date, t1.date";
+ "select t0.date, t1.date from table0 t0 %s join table1 t1 on
t0.date=t1.date order by t0.date, t1.date";
retArray =
new String[] {
"2022-01-01,null,",
@@ -2089,11 +2248,62 @@ public class IoTDBMultiTAGsWithAttributesTableIT {
"null,null,",
"null,null,",
};
- tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
+ tableResultSetEqualTest(String.format(sql, FULL), expectedHeader,
retArray, DATABASE_NAME);
+ retArray =
+ new String[] {
+ "2022-01-01,null,",
+ "2023-01-01,2023-01-01,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,"
+ };
+ tableResultSetEqualTest(String.format(sql, LEFT), expectedHeader,
retArray, DATABASE_NAME);
+ retArray =
+ new String[] {
+ "2023-01-01,2023-01-01,",
+ "null,2023-05-01,",
+ "null,2023-10-01,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ "null,null,",
+ };
+ tableResultSetEqualTest(String.format(sql, RIGHT), expectedHeader,
retArray, DATABASE_NAME);
expectedHeader = new String[] {"attr1", "attr2", "attr1", "attr2"};
sql =
- "select t0.attr1,t0.attr2,t1.attr1,t1.attr2 from table0 t0 full join
table1 t1 on t0.attr1=t1.attr1 AND t0.attr2=t1.attr2 order by
t0.attr1,t0.attr2,t1.attr1,t1.attr2";
+ "select t0.attr1,t0.attr2,t1.attr1,t1.attr2 from table0 t0 %s join
table1 t1 on t0.attr1=t1.attr1 AND t0.attr2=t1.attr2 order by
t0.attr1,t0.attr2,t1.attr1,t1.attr2";
retArray =
new String[] {
"c,d,c,d,",
@@ -2150,7 +2360,94 @@ public class IoTDBMultiTAGsWithAttributesTableIT {
"null,null,null,null,",
"null,null,null,null,",
};
- tableResultSetEqualTest(sql, expectedHeader, retArray, DATABASE_NAME);
+ tableResultSetEqualTest(String.format(sql, FULL), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray =
+ new String[] {
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "d,c,null,null,",
+ "d,c,null,null,",
+ "d,c,null,null,",
+ "t,a,t,a,",
+ "t,a,t,a,",
+ "t,a,t,a,",
+ "vv,null,null,null,",
+ "vv,null,null,null,",
+ "vv,null,null,null,",
+ "yy,zz,null,null,",
+ "yy,zz,null,null,",
+ "yy,zz,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ };
+ tableResultSetEqualTest(String.format(sql, LEFT), expectedHeader,
retArray, DATABASE_NAME);
+
+ retArray =
+ new String[] {
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "c,d,c,d,",
+ "t,a,t,a,",
+ "t,a,t,a,",
+ "t,a,t,a,",
+ "null,null,y,z,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,",
+ "null,null,null,null,"
+ };
+ tableResultSetEqualTest(String.format(sql, RIGHT), expectedHeader,
retArray, DATABASE_NAME);
}
@Test
diff --git
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
index 4ad12fd2839..e99a7f62a79 100644
---
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
+++
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/StatementAnalyzer.java
@@ -2986,12 +2986,7 @@ public class StatementAnalyzer {
createAndAssignScope(
node, scope,
left.getRelationType().joinWith(right.getRelationType()));
- if (node.getType() == LEFT || node.getType() == RIGHT) {
- throw new SemanticException(
- String.format(
- "%s JOIN is not supported, only support INNER JOIN in current
version.",
- node.getType()));
- } else if (node.getType() == Join.Type.CROSS || node.getType() ==
Join.Type.IMPLICIT) {
+ if (node.getType() == Join.Type.CROSS || node.getType() ==
Join.Type.IMPLICIT) {
return output;
}
if (criteria instanceof JoinOn) {
diff --git
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/RelationPlanner.java
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/RelationPlanner.java
index 6cfa93821bc..8f7d9817f3c 100644
---
a/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/RelationPlanner.java
+++
b/iotdb-core/datanode/src/main/java/org/apache/iotdb/db/queryengine/plan/relational/planner/RelationPlanner.java
@@ -120,6 +120,8 @@ import static
org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Join.Type.
import static
org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Join.Type.FULL;
import static
org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Join.Type.IMPLICIT;
import static
org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Join.Type.INNER;
+import static
org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Join.Type.LEFT;
+import static
org.apache.iotdb.db.queryengine.plan.relational.sql.ast.Join.Type.RIGHT;
public class RelationPlanner extends AstVisitor<RelationPlan, Void> {
@@ -384,6 +386,10 @@ public class RelationPlanner extends
AstVisitor<RelationPlan, Void> {
rightCoercion.getOutputSymbols(),
Optional.empty(),
Optional.empty());
+ // Transform RIGHT JOIN to LEFT
+ if (join.getJoinType() == JoinNode.JoinType.RIGHT) {
+ join = join.flip();
+ }
// Add a projection to produce the outputs of the columns in the USING
clause,
// which are defined as coalesce(l.k, r.k)
@@ -393,7 +399,7 @@ public class RelationPlanner extends
AstVisitor<RelationPlan, Void> {
for (Identifier column : joinColumns) {
Symbol output = symbolAllocator.newSymbol(column,
analysis.getType(column));
outputs.add(output);
- if (node.getType() == INNER) {
+ if (node.getType() == INNER || node.getType() == LEFT) {
assignments.put(output,
leftJoinColumns.get(column).toSymbolReference());
} else if (node.getType() == FULL) {
assignments.put(
@@ -401,6 +407,10 @@ public class RelationPlanner extends
AstVisitor<RelationPlan, Void> {
new CoalesceExpression(
leftJoinColumns.get(column).toSymbolReference(),
rightJoinColumns.get(column).toSymbolReference()));
+ } else if (node.getType() == RIGHT) {
+ assignments.put(output,
rightJoinColumns.get(column).toSymbolReference());
+ } else {
+ throw new IllegalStateException("Unexpected Join Type: " +
node.getType());
}
}
@@ -593,6 +603,9 @@ public class RelationPlanner extends
AstVisitor<RelationPlan, Void> {
rightPlanBuilder.getRoot().getOutputSymbols(),
Optional.empty(),
Optional.empty());
+ if (type == RIGHT && asofCriteria == null) {
+ root = ((JoinNode) root).flip();
+ }
if (type != INNER) {
for (Expression complexExpression : complexJoinExpressions) {
@@ -645,6 +658,9 @@ public class RelationPlanner extends
AstVisitor<RelationPlan, Void> {
.map(e -> coerceIfNecessary(analysis, e,
translationMap.rewrite(e)))
.collect(Collectors.toList()))),
Optional.empty());
+ if (type == RIGHT && asofCriteria == null) {
+ root = ((JoinNode) root).flip();
+ }
}
if (type == INNER) {
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 3de9076865b..5e6cc5818ac 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
@@ -45,6 +45,7 @@ import java.util.Set;
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.EquiJoinClause.flipBatch;
import static
org.apache.iotdb.db.queryengine.plan.relational.planner.node.JoinNode.JoinType.INNER;
public class JoinNode extends TwoChildProcessNode {
@@ -155,6 +156,23 @@ public class JoinNode extends TwoChildProcessNode {
this.asofCriteria = asofCriteria;
}
+ /**
+ * @return a new JoinNode with the flipped attributes
+ */
+ public JoinNode flip() {
+ return new JoinNode(
+ id,
+ joinType.flip(),
+ rightChild,
+ leftChild,
+ flipBatch(criteria),
+ asofCriteria,
+ rightOutputSymbols,
+ leftOutputSymbols,
+ filter,
+ spillable);
+ }
+
@Override
public <R, C> R accept(PlanVisitor<R, C> visitor, C context) {
return visitor.visitJoin(this, context);
@@ -374,6 +392,12 @@ public class JoinNode extends TwoChildProcessNode {
return new EquiJoinClause(right, left);
}
+ public static List<EquiJoinClause> flipBatch(List<EquiJoinClause> input) {
+ ImmutableList.Builder<EquiJoinClause> builder = ImmutableList.builder();
+ input.forEach(clause -> builder.add(clause.flip()));
+ return builder.build();
+ }
+
@Override
public boolean equals(Object obj) {
if (this == obj) {
@@ -488,5 +512,20 @@ public class JoinNode extends TwoChildProcessNode {
public String getJoinLabel() {
return joinLabel;
}
+
+ public JoinType flip() {
+ switch (this) {
+ case INNER:
+ return INNER;
+ case FULL:
+ return FULL;
+ case LEFT:
+ return RIGHT;
+ case RIGHT:
+ return LEFT;
+ default:
+ }
+ throw new IllegalArgumentException("Unsupported join type: " + this);
+ }
}
}
diff --git
a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/JoinTest.java
b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/JoinTest.java
index f13500196c6..42436d1b477 100644
---
a/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/JoinTest.java
+++
b/iotdb-core/datanode/src/test/java/org/apache/iotdb/db/queryengine/plan/relational/analyzer/JoinTest.java
@@ -62,7 +62,6 @@ import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils
import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.SESSION_INFO;
import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.SHENZHEN_DEVICE_ENTRIES;
import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.TEST_MATADATA;
-import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.assertAnalyzeSemanticException;
import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.assertJoinNodeEquals;
import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.assertNodeMatches;
import static
org.apache.iotdb.db.queryengine.plan.relational.analyzer.TestUtils.assertTableScan;
@@ -620,18 +619,4 @@ public class JoinTest {
+ "ON t1.time = t2.time ORDER BY t1.tag1 OFFSET 3 LIMIT 6",
false);
}
-
- // ========== unsupported test ===============
- @Test
- public void unsupportedJoinTest() {
- // LEFT JOIN
- assertAnalyzeSemanticException(
- "SELECT * FROM table1 t1 LEFT JOIN table1 t2 ON t1.time=t2.time",
- "LEFT JOIN is not supported, only support INNER JOIN in current
version");
-
- // RIGHT JOIN
- assertAnalyzeSemanticException(
- "SELECT * FROM table1 t1 RIGHT JOIN table1 t2 ON t1.time=t2.time",
- "RIGHT JOIN is not supported, only support INNER JOIN in current
version");
- }
}
