[SparkSQL] Full Join Return Null Value For Funtion-Based Column

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[SparkSQL] Full Join Return Null Value For Funtion-Based Column

刘 欢

Hi All:

    Here I got two tables:

 

    Table A

name

num

tom

2

jerry

3

jerry

4

null

null

 

 

 

 

 

 

    Table B

name

score

tom

12

jerry

10

jerry

8

null

null

 

 

 

 

 

 

    When i use spark.sql() to get result from A and B with sql :

 

select 

  a.name as aName,

  a.date,

  b.name as bName

from

    (

    select

      name,

      date_format(now(),'yyyy-MM-dd') AS date

    from

      A

    group by

      name

    ) a

FULL JOIN

    (

    select

      name

    from

      B

    group by

      name

    ) b

ON a.name = b.name

 

I got results contain ALL NULL VALUE ROW like:

 

aName

date

bName

null

null

null

 

Can anyone explains why all null value row appears?

 

 

 

 

Reply | Threaded
Open this post in threaded view
|

Re: [SparkSQL] Full Join Return Null Value For Funtion-Based Column

刘 欢

Sorry, I know the reason. closed

 

发件人: <[hidden email]>
日期: 2021118 星期一 下午1:39
收件人: "[hidden email]" <[hidden email]>
主题: [SparkSQL] Full Join Return Null Value For Funtion-Based Column

 

Hi All:

    Here I got two tables:

 

    Table A

name

num

tom

2

jerry

3

jerry

4

null

null

 

 

 

 

 

 

    Table B

name

score

tom

12

jerry

10

jerry

8

null

null

 

 

 

 

 

 

    When i use spark.sql() to get result from A and B with sql :

 

select 

  a.name as aName,

  a.date,

  b.name as bName

from

    (

    select

      name,

      date_format(now(),'yyyy-MM-dd') AS date

    from

      A

    group by

      name

    ) a

FULL JOIN

    (

    select

      name

    from

      B

    group by

      name

    ) b

ON a.name = b.name

 

I got results contain ALL NULL VALUE ROW like:

 

aName

date

bName

null

null

null

 

Can anyone explains why all null value row appears?