Ziyang Zhao created PHOENIX-2755:
------------------------------------
Summary: 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.6.0, 4.4.0, 4.8.0
Reporter: Ziyang Zhao
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)
