@Test
  public void timeColumnOuterJoinTest1() {
    expectedHeader =
        new String[] {"time", "device", "level", "num", "device", "attr2", "num", "str"};
    retArray =
        new String[] {
          "1970-01-01T00:00:00.000Z,d1,l1,3,d1,d,3,coconut,",
          "1970-01-01T00:00:00.000Z,d1,l1,3,d2,c,3,coconut,",
          "1970-01-01T00:00:00.000Z,d2,l1,3,d1,d,3,coconut,",
          "1970-01-01T00:00:00.000Z,d2,l1,3,d2,c,3,coconut,",
          "1970-01-01T00:00:00.020Z,d1,l2,2,d1,zz,2,pineapple,",
          "1970-01-01T00:00:00.020Z,d1,l2,2,d2,null,2,pineapple,",
          "1970-01-01T00:00:00.020Z,d2,l2,2,d1,zz,2,pineapple,",
          "1970-01-01T00:00:00.020Z,d2,l2,2,d2,null,2,pineapple,",
          "1970-01-01T00:00:00.040Z,d1,l3,1,d1,a,1,apricot,",
          "1970-01-01T00:00:00.040Z,d1,l3,1,d2,null,1,apricot,",
          "1970-01-01T00:00:00.040Z,d2,l3,1,d1,a,1,apricot,",
          "1970-01-01T00:00:00.040Z,d2,l3,1,d2,null,1,apricot,",
          "1970-01-01T00:00:00.080Z,d1,l4,9,d1,null,9,apple,",
          "1970-01-01T00:00:00.080Z,d1,l4,9,d2,null,9,apple,",
          "1970-01-01T00:00:00.080Z,d2,l4,9,d1,null,9,apple,",
          "1970-01-01T00:00:00.080Z,d2,l4,9,d2,null,9,apple,",
          "1970-01-01T00:00:00.100Z,d1,l5,8,d1,null,8,papaya,",
          "1970-01-01T00:00:00.100Z,d1,l5,8,d2,null,8,papaya,",
          "1970-01-01T00:00:00.100Z,d2,l5,8,d1,null,8,papaya,",
          "1970-01-01T00:00:00.100Z,d2,l5,8,d2,null,8,papaya,",
          "1971-01-01T00:00:00.000Z,d1,l1,6,d1,d,6,banana,",
          "1971-01-01T00:00:00.000Z,d1,l1,6,d2,c,6,banana,",
          "1971-01-01T00:00:00.000Z,d2,l1,6,d1,d,6,banana,",
          "1971-01-01T00:00:00.000Z,d2,l1,6,d2,c,6,banana,",
          "1971-01-01T00:00:00.100Z,d1,l2,10,d1,zz,10,pumelo,",
          "1971-01-01T00:00:00.100Z,d1,l2,10,d2,null,10,pumelo,",
          "1971-01-01T00:00:00.100Z,d2,l2,10,d1,zz,10,pumelo,",
          "1971-01-01T00:00:00.100Z,d2,l2,10,d2,null,10,pumelo,",
          "1971-01-01T00:00:00.500Z,d1,l3,4,d1,a,4,peach,",
          "1971-01-01T00:00:00.500Z,d1,l3,4,d2,null,4,peach,",
          "1971-01-01T00:00:00.500Z,d2,l3,4,d1,a,4,peach,",
          "1971-01-01T00:00:00.500Z,d2,l3,4,d2,null,4,peach,",
          "1971-01-01T00:00:01.000Z,d1,l4,5,d1,null,5,orange,",
          "1971-01-01T00:00:01.000Z,d1,l4,5,d2,null,5,orange,",
          "1971-01-01T00:00:01.000Z,d2,l4,5,d1,null,5,orange,",
          "1971-01-01T00:00:01.000Z,d2,l4,5,d2,null,5,orange,",
          "1971-01-01T00:00:10.000Z,d1,l5,7,d1,null,7,lemon,",
          "1971-01-01T00:00:10.000Z,d1,l5,7,d2,null,7,lemon,",
          "1971-01-01T00:00:10.000Z,d2,l5,7,d1,null,7,lemon,",
          "1971-01-01T00:00:10.000Z,d2,l5,7,d2,null,7,lemon,",
          "1971-01-01T00:01:40.000Z,d1,l1,11,d1,d,11,pitaya,",
          "1971-01-01T00:01:40.000Z,d1,l1,11,d2,c,11,pitaya,",
          "1971-01-01T00:01:40.000Z,d2,l1,11,d1,d,11,pitaya,",
          "1971-01-01T00:01:40.000Z,d2,l1,11,d2,c,11,pitaya,",
          "1971-04-26T17:46:40.000Z,d1,l2,12,d1,zz,12,strawberry,",
          "1971-04-26T17:46:40.000Z,d1,l2,12,d2,null,12,strawberry,",
          "1971-04-26T17:46:40.000Z,d2,l2,12,d1,zz,12,strawberry,",
          "1971-04-26T17:46:40.000Z,d2,l2,12,d2,null,12,strawberry,",
          "1971-04-26T17:46:40.020Z,d1,l3,14,d1,a,14,cherry,",
          "1971-04-26T17:46:40.020Z,d1,l3,14,d2,null,14,cherry,",
          "1971-04-26T17:46:40.020Z,d2,l3,14,d1,a,14,cherry,",
          "1971-04-26T17:46:40.020Z,d2,l3,14,d2,null,14,cherry,",
          "1971-04-26T18:01:40.000Z,d1,l4,13,d1,null,13,lychee,",
          "1971-04-26T18:01:40.000Z,d1,l4,13,d2,null,13,lychee,",
          "1971-04-26T18:01:40.000Z,d2,l4,13,d1,null,13,lychee,",
          "1971-04-26T18:01:40.000Z,d2,l4,13,d2,null,13,lychee,",
          "1971-08-20T11:33:20.000Z,d1,l5,15,d1,null,15,watermelon,",
          "1971-08-20T11:33:20.000Z,d1,l5,15,d2,null,15,watermelon,",
          "1971-08-20T11:33:20.000Z,d2,l5,15,d1,null,15,watermelon,",
          "1971-08-20T11:33:20.000Z,d2,l5,15,d2,null,15,watermelon,",
        };

    // 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 %s JOIN table0 t2 ON t1.time = t2.time \n"
            + "ORDER BY t1.time, t1.device, t2.device";

    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 %s JOIN table0 t2 USING(time)\n"
            + "ORDER BY time, t1.device, t2.device";

    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 %s join tableB t2 on t1.time = t2.time "
            + "order by COALESCE(time1, time2),device1,device2";
    retArray =
        new String[] {
          "2020-01-01T00:00:01.000Z,null,d1,1,null,null,",
          "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,",
          "2020-01-01T00:00:07.000Z,null,d2,7,null,null,",
        };
    expectedHeader = new String[] {"time1", "time2", "device1", "value1", "device2", "value2"};

    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 %s join tableB t2 USING(time) "
            + "order by time,device1,device2";
    retArray =
        new String[] {
          "2020-01-01T00:00:01.000Z,d1,1,null,null,",
          "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,",
          "2020-01-01T00:00:07.000Z,d2,7,null,null,",
        };
    expectedHeader = new String[] {"time", "device1", "value1", "device2", "value2"};

    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 =
        "select time, "
            + "  t1.device as device1, "
            + "  t1.value as value1, "
            + "  t2.device as device2, "
            + "  t2.value as value2  from tableC t1 %s join tableB t2 USING(time) "
            + "order by time,device1,device2";
    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,",
        };
    expectedHeader = new String[] {"time", "device1", "value1", "device2", "value2"};

    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, "
            + "  t1.device as device1, "
            + "  t1.value as value1, "
            + "  t2.device as device2, "
            + "  t2.value as value2 "
            + "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,",
          "2020-01-01T00:00:03.000Z,d1,3,null,null,",
          "2020-01-01T00:00:04.000Z,null,null,d2,40,",
          "2020-01-01T00:00:05.000Z,null,null,d2,50,",
        };
    expectedHeader = new String[] {"time", "device1", "value1", "device2", "value2"};

    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);
  }
In this IT, table has more than one TAGs and Attributes.
integration-test/src/test/java/org/apache/iotdb/relational/it/db/it/IoTDBMultiTAGsWithAttributesTableIT.java