You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@kylin.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2016/11/18 16:57:58 UTC
[jira] [Commented] (KYLIN-2214) NOT IN result incorrect result
[ https://issues.apache.org/jira/browse/KYLIN-2214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15677135#comment-15677135 ]
Julian Hyde commented on KYLIN-2214:
------------------------------------
Please let me know if this turns out to be a Calcite issue. I am investigating CALCITE-1483, CALCITE-1493, which both relate to NOT IN with sub-queries.
> NOT IN result incorrect result
> ------------------------------
>
> Key: KYLIN-2214
> URL: https://issues.apache.org/jira/browse/KYLIN-2214
> Project: Kylin
> Issue Type: Bug
> Reporter: liyang
>
> 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
(v6.3.4#6332)