Nice catch! Thanks! On Fri, Nov 18, 2016 at 3:17 PM, Donald,Zheng(vip.com) < [email protected]> wrote:
> We meet the same problem. I have logged a JIRA issue > https://issues.apache.org/jira/browse/KYLIN-2212 > > 发件人: lxw [mailto:[email protected]] > 发送时间: 2016年11月18日 14:01 > 收件人: dev; user > 主题: SQL "NOT IN" returns incorrect result > > 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 communication is intended only for the addressee(s) and may contain > information that is privileged and confidential. You are hereby notified > that, if you are not an intended recipient listed above, or an authorized > employee or agent of an addressee of this communication responsible for > delivering e-mail messages to an intended recipient, any dissemination, > distribution or reproduction of this communication (including any > attachments hereto) is strictly prohibited. If you have received this > communication in error, please notify us immediately by a reply e-mail > addressed to the sender and permanently delete the original e-mail > communication and any attachments from all storage devices without making > or otherwise retaining a copy. >
