[jira] [Commented] (SPARK-21645) SparkSQL Left outer join get the error result when use phoenix spark plugin
[
https://issues.apache.org/jira/browse/SPARK-21645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16453410#comment-16453410
]
shining commented on SPARK-21645:
-
When left outer join occurs between table a and table b, the filter of table a
should be synchronized to table b if the filter column is contained in join
condition.
> 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
>Priority: Major
>
> 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 NULLNULL
> 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
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-21645) SparkSQL Left outer join get the error result when use phoenix spark plugin
[
https://issues.apache.org/jira/browse/SPARK-21645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16453405#comment-16453405
]
Apache Spark commented on SPARK-21645:
--
User 'shining1989' has created a pull request for this issue:
https://github.com/apache/spark/pull/21161
> 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
>Priority: Major
>
> 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 NULLNULL
> 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
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-21645) SparkSQL Left outer join get the error result when use phoenix spark plugin
[
https://issues.apache.org/jira/browse/SPARK-21645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16297252#comment-16297252
]
Sunitha Kambhampati commented on SPARK-21645:
-
I tried to see if I can reproduce this issue with a jdbc datasource but I was
not able to on trunk. It looks like there are some filter isnotnull issues
that have been resolved.
Can you test your scenario with the latest trunk (or even the released 2.2.1)
if possible and see if you are still seeing the issue. Or Is there any other
details that you can provide to repro this issue. Thanks.
> 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 NULLNULL
> 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: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-21645) SparkSQL Left outer join get the error result when use phoenix spark plugin
[
https://issues.apache.org/jira/browse/SPARK-21645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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
+- *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{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 NULLNULL
> 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: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-21645) SparkSQL Left outer join get the error result when use phoenix spark plugin
[
https://issues.apache.org/jira/browse/SPARK-21645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16115365#comment-16115365
]
Yuming Wang commented on SPARK-21645:
-
Can you paste the SQL execution plan:
{code:sql}
EXPLAIN SELECT a.anchedate, b.womempnumdis, b.holdingsmsgdis
FROM AN_BASEINFO a
LEFT JOIN AN_SUP_BASEINFO b ON a.S_EXT_NODENUM = b.S_EXT_NODENUM
AND a.ANCHEID = b.ANCHEID
WHERE a.ANCHEID = '2c9e87ea5bd35458015c2df4003a1025';
{code}
> 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 NULLNULL
> 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: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
