[
https://issues.apache.org/jira/browse/SPARK-21645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16115385#comment-16115385
]
shining commented on SPARK-21645:
---------------------------------
[~q79969786]
the physical plan by explain :
== Physical Plan ==
{quote}*Project [anchedate#402, womempnumdis#429, holdingsmsgdis#430]
+- SortMergeJoin [ANCHEID#400, S_EXT_NODENUM#426], [ANCHEID#428,
S_EXT_NODENUM#431], LeftOuter
:- *Sort [ANCHEID#400 ASC NULLS FIRST, S_EXT_NODENUM#426 ASC NULLS FIRST],
false, 0
: +- Exchange hashpartitioning(ANCHEID#400, S_EXT_NODENUM#426, 200)
: +- *Filter (isnotnull(ANCHEID#400) && (ANCHEID#400 =
2c9e87ea5bd35458015c2df4003a1025))
: +- *Scan PhoenixRelation(AN_BASEINFO,node1:2181,false)
default.an_baseinfo[ANCHEID#400,ANCHEDATE#402,S_EXT_NODENUM#426] PushedFilters:
[IsNotNull(ANCHEID), EqualTo(ANCHEID,2c9e87ea5bd35458015c2df4003a1025)],
ReadSchema: struct<ANCHEID:string,ANCHEDATE:string,S_EXT_NODENUM:string>
+- *Sort [ANCHEID#428 ASC NULLS FIRST, S_EXT_NODENUM#431 ASC NULLS FIRST],
false, 0
+- Exchange hashpartitioning(ANCHEID#428, S_EXT_NODENUM#431, 200)
+- *Scan PhoenixRelation(AN_SUP_BASEINFO,node1:2181,false)
default.an_sup_baseinfo[ANCHEID#428,WOMEMPNUMDIS#429,HOLDINGSMSGDIS#430,S_EXT_NODENUM#431]
ReadSchema:
struct<ANCHEID:string,WOMEMPNUMDIS:string,HOLDINGSMSGDIS:string,S_EXT_NODENUM:string>{quote}
> SparkSQL Left outer join get the error result when use phoenix spark plugin
> ---------------------------------------------------------------------------
>
> Key: SPARK-21645
> URL: https://issues.apache.org/jira/browse/SPARK-21645
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 2.1.0, 2.2.0
> Environment: spark2.1.0
> hbase 1.1.2
> phoenix4.10
> Reporter: shining
>
> I have two tables in phoenix: AN_BASEINFO and AN_SUP_BASEINFO
> Then I crate the outer datasource table in sparksql through phoenix spark
> plugin.like
> create table AN_BASEINFO
> using org.apache.phoenix.spark
> OPTIONS(table "AN_BASEINFO ", zkUrl "172.16.12.82:2181")
> and
> create table AN_SUP_BASEINFO
> using org.apache.phoenix.spark
> OPTIONS(table "AN_SUP_BASEINFO ", zkUrl "172.16.12.82:2181")
> IN SparkSQL I execute a sql use lef outer join,the sql is :
> {color:red}{color:#f79232}_
> *select
> a.anchedate,b.womempnumdis,b.holdingsmsgdis
> from
> AN_BASEINFO a
> left outer join AN_SUP_BASEINFO b
> on
> a.S_EXT_NODENUM = b.S_EXT_NODENUM and a.ANCHEID =b.ANCHEID
> where
> a.ANCHEID= '2c9e87ea5bd35458015c2df4003a1025';*_{color}{color}
> the result is : 2017-05-22 00:00:00.0 NULL NULL
> But actually, table AN_SUP_BASEINFO exist an record that a.S_EXT_NODENUM =
> b.S_EXT_NODENUM and a.ANCHEID =b.ANCHEID;
> If I add a filter condition b.holdingsmsgdis is not null in the sql, the
> result is right:
> 2017-05-22 00:00:00.0 2 1
> the sql:
> *{color:#d04437}select
> a.anchedate,b.womempnumdis,b.holdingsmsgdis
> from
> AN_BASEINFO a
> left outer join AN_SUP_BASEINFO b
> on
> a.S_EXT_NODENUM = b.S_EXT_NODENUM and a.ANCHEID =b.ANCHEID
> where
> a.ANCHEID= '2c9e87ea5bd35458015c2df4003a1025'{color:#d04437}and
> b.holdingsmsgdis is not null;{color}{color}*
> {color:#d04437}{color:#14892c}result is right: 2017-05-22 00:00:00.0 2
> 1 {color}{color}
> Is there anyone who know this?Please help!
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
