Hi,
I have a verify in my test env. Surely what you report is right.
SELECT SUM(INTEREST_SCORE) AS A, AAA as B, AAA + SUM(INTEREST_SCORE) as C
FROM USERACTION
LEFT JOIN (
SELECT SUM(INTEREST_SCORE) AS AAA
FROM USERACTION
WHERE DT = '2012-01-02'
) A on 1 = 1
WHERE DT = '2012-01-01'
GROUP BY AAA
What kylin returns looks like the following:
| A | B | C |
| 5.85 | | |
But using the same sql in Hive CLI(1.2.X), result is the same:
OK
5.850000000000001NULLNULL
So kylin provided the same result as Hive return. Kylin looks make no
mistakes. And I have check other query releated with NULL in kylin, I find no
mistake.
-----------------
-----------------
Best wishes to you !
From ：Xiaoxiang Yu
在 2019-06-25 10:58:22，"肖孟华" <[email protected]> 写道：
>执行：select sum( TRAAMT ) AAA from TM_SR_SKY_T31293716 where AREA_CODE ='370799'
>查询结果集为空，TRAAMT 为number类型；
>
>
>执行：select sum( TRAAMT ) AAA from TM_SR_SKY_T31293716 where AREA_CODE ='370783'
>查询结果集不为空，有数值，TRAAMT 为number类型；
>
>
>执行：select sum( TRAAMT ) A , AAA AS B, sum( TRAAMT ) + AAA AS C from
>TM_SR_SKY_T31293716
>left join
>(select sum( TRAAMT ) AAA from TM_SR_SKY_T31293716 where AREA_CODE ='370799')
>A
>on 1=1
>where AREA_CODE ='370783' group by aaa
>执行成功后，A结果不为空，B结果集为空，A与B相加后结果集C为空。
>
>
>问题：当空值与非空值进行加减乘除四则运算时，结果集均为空值，sum()函数在使用过程中也存在这样的问题，能否实现在运算前对空值在sql中进行特殊处理，将空值转换为0后在进行运算？若能实现，如何实现？需要如何处理？
>
>
>
>
>发件人：肖孟华
>联系电话：(+86)17616716362
>地址：（中国）山东省潍坊市高新区健康街潍坊软件园
>
>
>
>
>
