kylin-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Donald,Zheng(vip.com)" <donald.zh...@vipshop.com>
Subject 答复: SQL "NOT IN" returns incorrect result
Date Fri, 18 Nov 2016 07:17:00 GMT
We meet the same problem. I have logged a JIRA issue  https://issues.apache.org/jira/browse/KYLIN-2212

发件人: lxw [mailto:lxw1234@qq.com]
发送时间: 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.
Mime
View raw message