kylin-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Li Yang <liy...@apache.org>
Subject Re: 答复: SQL "NOT IN" returns incorrect result
Date Fri, 18 Nov 2016 09:28:50 GMT
Nice catch! Thanks!

On Fri, Nov 18, 2016 at 3:17 PM, Donald,Zheng(vip.com) <
donald.zheng@vipshop.com> wrote:

> 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