回复：when query a dimension having null value with not equal or not in get wrong result

[冯志山]

在写SQL 条件语句是经常用到 不等于‘<>’的筛选条件，此时要注意此条件会将字段为null的数据也当做满足不等于的条件而将数据筛选掉。


------------------------------------------------------------------
发件人：lk_hadoop <lk_had...@163.com>
发送时间：2020年2月12日(星期三) 19:15
收件人：user <user@kylin.apache.org>; dev <d...@kylin.apache.org>
主　题：when query a dimension having null value with not equal or not in get wrong 
result

hi,all:
      when I query with this sql : 

      SELECT NATION, DATES, IS_JOIN ,SUM(REC_AMT) AS sum_REC_AMT  FROM 
GJST.SH_FETCH_SALE_BASE_FACT_ALL_NEW
      WHERE NATION IN ('控股') AND DATES IN ('2020-02-11') GROUP BY NATION, 
DATES, IS_JOIN;
      I can see there is a null value in IS_JOIN column.



     but when I query with :
     SELECT NATION, DATES, IS_JOIN ,SUM(REC_AMT) AS sum_REC_AMT FROM 
GJST.SH_FETCH_SALE_BASE_FACT_ALL_NEW
     WHERE NATION IN ('控股')
     AND DATES IN ('2020-02-11')
     AND IS_JOIN <> '加盟'
     GROUP BY NATION, DATES, IS_JOIN;

     the null value is not in the result set:

    is this a bug ? I'm using apache-kylin-2.6.3-bin-cdh57

image.png
Description: Binary data

image.png
Description: Binary data