[jira] [Created] (KYLIN-2214) NOT IN result incorrect result

["liyang (JIRA)" <ji...@apache.org>]

liyang created KYLIN-2214:
-----------------------------

             Summary: 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)