Looking at KYLIN-2212, it is still different from what lwx reports. I created https://issues.apache.org/jira/browse/KYLIN-2214 for lwx's issue.
On Fri, Nov 18, 2016 at 5:28 PM, Li Yang <[email protected]> wrote: > 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. >> > >
