[
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)
