[
https://issues.apache.org/jira/browse/KYLIN-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Xiaoxiang Yu closed KYLIN-2214.
-------------------------------
Resolved in release 3.1.0 (2020-07-03)
> NOT IN result incorrect result
> ------------------------------
>
> Key: KYLIN-2214
> URL: https://issues.apache.org/jira/browse/KYLIN-2214
> Project: Kylin
> Issue Type: Bug
> Components: Query Engine
> Reporter: liyang
> Assignee: Zhichao Zhang
> Priority: Major
> Labels: scope
> Fix For: v3.1.0
>
>
> Hi,
> When I use "NOT IN" in where clause, it returns incorrect result, and
> instead use "<>", then result is OK.
> Raw data;
> SELECT
> c.ad_place_type,
> COUNT(1) as cnt
> FROM fact_table a
> LEFT JOIN dim_table as c ON (ad_id = crt_id AND mtid = mtid_)
> WHERE pt = '2016-11-17'
> GROUP by c.ad_place_type;
> --results
> wap 64578476
> app 70764413
> pc 3398137
> unknown 419942
> SQL1(correct) :
> SELECT
> c.ad_place_type,
> COUNT(1) as cnt
> FROM fact_table a
> LEFT JOIN dim_table as c ON (ad_id = crt_id AND mtid = mtid_)
> WHERE pt = '2016-11-17'
> AND c.ad_place_type <> 'pc'
> GROUP by c.ad_place_type;
> --
> wap 64578476
> app 70764413
> unknown 419942
> SQL2(incorrect)：
> SELECT
> c.ad_place_type,
> COUNT(1) as cnt
> FROM fact_table a
> LEFT JOIN dim_table as c ON (ad_id = crt_id AND mtid = mtid_)
> WHERE pt = '2016-11-17'
> AND c.ad_place_type NOT IN ('pc')
> GROUP by c.ad_place_type;
> --
> wap 4718980
> app 33253424
> unknown 90533
> SQL3(incorrect)：
> SELECT
> c.ad_place_type,
> COUNT(1) as cnt
> FROM fact_table a
> LEFT JOIN dim_table as c ON (ad_id = crt_id AND mtid = mtid_)
> WHERE pt = '2016-11-17'
> AND c.ad_place_type NOT IN ('app','wap')
> GROUP by c.ad_place_type;
> --
> result(0) ,
> The correct result should be "pc 3398137" and "unknown 419942",
> when instead use "(c.ad_place_type <> 'app' AND c.ad_place_type <> 'wap')",
> the result is OK.
> Who can help me to explain this, thanks!
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
