[KYLIN-2214] NOT IN result incorrect result

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: v3.1.0
• Component/s: Query Engine
• Labels: scope
• Sprint: Sprint 50
• Assignee: Zhichao Zhang
• Reporter: liyang
• Created: 18/Nov/16 09:31
• Updated: 31/Jul/20 12:00
• Resolved: 14/May/20 01:29

Description

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!