kylin-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "lxw" <lxw1...@qq.com>
Subject SQL "NOT IN" returns incorrect result
Date Fri, 18 Nov 2016 06:00:36 GMT
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!
Mime
View raw message