spark-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "shining (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (SPARK-21645) SparkSQL Left outer join get the error result when use phoenix spark plugin
Date Sat, 05 Aug 2017 08:23:00 GMT

     [ https://issues.apache.org/jira/browse/SPARK-21645?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

shining updated SPARK-21645:
----------------------------
    Description: 
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!


  was:
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:
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}
result is right: 2017-05-22 00:00:00.0   2       1 

Is there anyone who know this?Please help!



> 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: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org


Mime
View raw message