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

[刘 欢 <bl...@hotmail.com>]

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?

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

[刘 欢 <bl...@hotmail.com>]

Sorry, I know the reason. closed

发件人: 刘 欢 <bl...@hotmail.com>
日期: 2021年1月18日 星期一 下午1:39
收件人: "user@spark.apache.org" <us...@spark.apache.org>
主题: [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?