You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by lk_hadoop <lk...@163.com> on 2020/02/12 11:14:29 UTC
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
Re:when query a dimension having null value with not equal or not
in get wrong result
Posted by Xiaoxiang Yu <xx...@apache.org>.
Hi lk_hadoop and zhishan,
Thank you for your clarification and verification. It is great.
--
Best wishes to you !
From ：Xiaoxiang Yu
At 2020-02-16 12:37:52, "lk_hadoop" <lk...@163.com> wrote:
hi,all :
This is not kylin's bug but a common knowledge about modern-sql, I have tried the same sql with hive、presto, all return the same result as kylin return.
One of the kylin's developer @Xiaoxiang Yu give the clear explain :
At 2020-02-12 19:14:29, "lk_hadoop" <lk...@163.com> wrote:
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
Re:when query a dimension having null value with not equal or not
in get wrong result
Posted by lk_hadoop <lk...@163.com>.
hi,all :
This is not kylin's bug but a common knowledge about modern-sql, I have tried the same sql with hive、presto, all return the same result as kylin return.
One of the kylin's developer @Xiaoxiang Yu give the clear explain :
At 2020-02-12 19:14:29, "lk_hadoop" <lk...@163.com> wrote:
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
Re:when query a dimension having null value with not equal or not
in get wrong result
Posted by lk_hadoop <lk...@163.com>.
hi,all :
This is not kylin's bug but a common knowledge about modern-sql, I have tried the same sql with hive、presto, all return the same result as kylin return.
One of the kylin's developer @Xiaoxiang Yu give the clear explain :
At 2020-02-12 19:14:29, "lk_hadoop" <lk...@163.com> wrote:
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
回复：when query a dimension having null value with not equal or not in get wrong result
Posted by 冯志山 <fe...@teld.cn>.
在写SQL 条件语句是经常用到 不等于‘<>’的筛选条件，此时要注意此条件会将字段为null的数据也当做满足不等于的条件而将数据筛选掉。
------------------------------------------------------------------
发件人：lk_hadoop <lk...@163.com>
发送时间：2020年2月12日(星期三) 19:15
收件人：user <us...@kylin.apache.org>; dev <de...@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