[ https://issues.apache.org/jira/browse/PHOENIX-2755?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James Taylor updated PHOENIX-2755: ---------------------------------- Assignee: Maryann Xue > Right join does not work > ------------------------ > > Key: PHOENIX-2755 > URL: https://issues.apache.org/jira/browse/PHOENIX-2755 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.4.0, 4.6.0, 4.8.0 > Reporter: Ziyang Zhao > Assignee: Maryann Xue > > We are using two identical test data table: t2 and t3. > NUM_COL1(DECIMAL(12, 2)) LOG_COL2(BOOLEAN) COL3(CHAR(10)) > DATE_COL4(DATE) COL5(VARCHAR(32768)) TINT_COL6(TINYINT) LVB_COL7(VARBINARY) > INT_COL8(INTEGER) SINT_COL9(SMALLINT) RL_COL10(FLOAT) DBL_COL11(DOUBLE) > TM_COL12(TIME) TS_COL13(TIMESTAMP PRIMARY KEY) VC_COL14 (VARCHAR(100)) > 100.25 1 Char 1 5/1/1995 > Memo field 1 10 100 1 > 111.1100006 11.1 1:01:01 5/1/1995 > 1:01 Varchar 1 > 200.25 0 Char 2 5/2/1995 > Memo field 2 20 200 2 > 222.2200012 22.2 2:02:02 5/2/1995 > 2:02 Varchar 2 > 300.25 1 Char 3 5/3/1995 > Memo field 3 30 300 3 > 333.3299866 33.3 3:03:03 5/3/1995 > 3:03 Varchar 3 > 400.25 0 Char 4 5/4/1995 > Memo field 4 40 400 4 > 444.4400024 44.4 4:04:04 5/4/1995 > 4:04 Varchar 4 > 500.25 1 Char 5 5/5/1995 > Memo field 5 50 500 5 > 555.5499878 55.5 5:05:05 5/5/1995 > 5:05 Varchar 5 > Left join: "select * from t2 left join t3 on t2.int_col8=t3.int_col8" works > fine under all environments. > While for the right join: "select * from t2 right join t3 on > t2.int_col8=t3.int_col8" gave out wrong results. > 1. Phoenix 4.4, serialization=PROTOBUF in hdp 2.3.4 > It gave out the wrong result: > "NUM_COL1", "LOG_COL2", "COL3", "DATE_COL4", "COL5", "TINT_COL6", "LVB_COL7", > "INT_COL8", "SINT_COL9", "RL_COL10", "DBL_COL11", "TM_COL12", "TS_COL13", > "VC_COL14", "NUM_COL1", "LOG_COL2", "COL3", "DATE_COL4", "COL5", "TINT_COL6", > "LVB_COL7", "INT_COL8", "SINT_COL9", "RL_COL10", "DBL_COL11", "TM_COL12", > "TS_COL13", "VC_COL14" > <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, > <Null>, <Null>, <Null>, <Null>, <Null>, 100.25, 1, "Char 1 ", 1995-05-01, > "Memo field 1", 10, 0x1123456789, 100, 1, 111.110000610352, 11.1, 01:01:01, > 1995-05-01 01:01:01, "Varchar 1" > <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, > <Null>, <Null>, <Null>, <Null>, <Null>, 200.25, 0, "Char 2 ", 1995-05-02, > "Memo field 2", 20, 0x2123456789, 200, 2, 222.220001220703, 22.2, 02:02:02, > 1995-05-02 02:02:02, "Varchar 2" > <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, > <Null>, <Null>, <Null>, <Null>, <Null>, 300.25, 1, "Char 3 ", 1995-05-03, > "Memo field 3", 30, 0x3123456789, 300, 3, 333.329986572266, 33.3, 03:03:03, > 1995-05-03 03:03:03, "Varchar 3" > <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, > <Null>, <Null>, <Null>, <Null>, <Null>, 400.25, 0, "Char 4 ", 1995-05-04, > "Memo field 4", 40, 0x4123456789, 400, 4, 444.440002441406, 44.4, 04:04:04, > 1995-05-04 04:04:04, "Varchar 4" > <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, > <Null>, <Null>, <Null>, <Null>, <Null>, 500.25, 1, "Char 5 ", 1995-05-05, > "Memo field 5", 50, 0x5123456789, 500, 5, 555.549987792969, 55.5, 05:05:05, > 1995-05-05 05:05:05, "Varchar 5" > 2. Phoenix 4.6, serialization=JSON in docker > It generated errors: > "Error: error while executing SQL "select * from t2 right join t3 on > t2.int_col8=t3.int_col8": response code 500 (state=,code=0) > java.sql.SQLException: error while executing SQL "select * from t2 right join > t3 on t2.int_col8=t3.int_col8": response code 500 > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > at > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:112) > at > org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:121) > at sqlline.Commands.execute(Commands.java:822) > at sqlline.Commands.sql(Commands.java:732) > at sqlline.SqlLine.dispatch(SqlLine.java:808) > at sqlline.SqlLine.begin(SqlLine.java:681) > at sqlline.SqlLine.start(SqlLine.java:398) > at sqlline.SqlLine.main(SqlLine.java:292) > Caused by: java.lang.RuntimeException: response code 500 > at > org.apache.calcite.avatica.remote.RemoteService.apply(RemoteService.java:45) > at > org.apache.calcite.avatica.remote.JsonService.apply(JsonService.java:207) > at > org.apache.calcite.avatica.remote.RemoteMeta.prepareAndExecute(RemoteMeta.java:169) > at > org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:477) > at > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:109) > ... 7 more > " > 3. Phoenix 4.8, serialization=JSON in docker > It generates the wrong result: > "NUM_COL1", "LOG_COL2", "COL3", "DATE_COL4", "COL5", "TINT_COL6", "LVB_COL7", > "INT_COL8", "SINT_COL9", "RL_COL10", "DBL_COL11", "TM_COL12", "TS_COL13", > "VC_COL14", "NUM_COL1", "LOG_COL2", "COL3", "DATE_COL4", "COL5", "TINT_COL6", > "LVB_COL7", "INT_COL8", "SINT_COL9", "RL_COL10", "DBL_COL11", "TM_COL12", > "TS_COL13", "VC_COL14" > <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, > <Null>, <Null>, <Null>, <Null>, <Null>, 100.25, 1, "Char 1 ", 1995-05-01, > "Memo field 1", 10, <Null>, 100, 1, 111.110000610352, 11.1, 01:01:01, > 1995-05-01 01:01:01, "Varchar 1" > <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, > <Null>, <Null>, <Null>, <Null>, <Null>, 200.25, 0, "Char 2 ", 1995-05-02, > "Memo field 2", 20, <Null>, 200, 2, 222.220001220703, 22.2, 02:02:02, > 1995-05-02 02:02:02, "Varchar 2" > <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, > <Null>, <Null>, <Null>, <Null>, <Null>, 300.25, 1, "Char 3 ", 1995-05-03, > "Memo field 3", 30, <Null>, 300, 3, 333.329986572266, 33.3, 03:03:03, > 1995-05-03 03:03:03, "Varchar 3" > <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, > <Null>, <Null>, <Null>, <Null>, <Null>, 400.25, 0, "Char 4 ", 1995-05-04, > "Memo field 4", 40, <Null>, 400, 4, 444.440002441406, 44.4, 04:04:04, > 1995-05-04 04:04:04, "Varchar 4" > <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, <Null>, > <Null>, <Null>, <Null>, <Null>, <Null>, 500.25, 1, "Char 5 ", 1995-05-05, > "Memo field 5", 50, <Null>, 500, 5, 555.549987792969, 55.5, 05:05:05, > 1995-05-05 05:05:05, "Varchar 5" > Right join on any column except for the first column will generate the above > result. -- This message was sent by Atlassian JIRA (v6.3.4#6332)