[KYLIN-3661] query return inconsistent result

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: v2.4.0
• Assignee: Unassigned
• Reporter: wangxianbin
• Created: 01/Nov/18 06:11
• Updated: 01/Nov/18 06:11

Description

three queries on same cube. obviously,the differ between query one and two is "group by os", however the second query have smaller "uv" and "pv", which is wrong, and query three return correct result.

query one

SELECT
a.os,
count(DISTINCT a.DUID) AS "uv",
CASE WHEN sum(a.pv) IS NULL THEN 0 ELSE sum(a.pv) END AS "pv"
FROM dw_netflow.visit_all a
JOIN DW_NETFLOW.DW_DIM_DATE b ON a.dt = b.day_name
WHERE a.dt = '2018-10-13'
AND a.LABEL_TYPE = 'EVENT'
group by a.os

result

OS	uv	pv
other	4657	869656
android	1713172	198955150
ios	118205	8438544

 

query two

SELECT
count(DISTINCT a.DUID) AS "uv",
CASE WHEN sum(a.pv) IS NULL THEN 0 ELSE sum(a.pv) END AS "pv"
FROM dw_netflow.visit_all a
JOIN DW_NETFLOW.DW_DIM_DATE b ON a.dt = b.day_name
WHERE a.dt = '2018-10-13'
AND a.LABEL_TYPE = 'EVENT'

result

uv	pv
699022	30428195

 

query three

SELECT
count(DISTINCT a.DUID) AS "uv",
CASE WHEN sum(a.pv) IS NULL THEN 0 ELSE sum(a.pv) END AS "pv"
FROM dw_netflow.visit_all a
JOIN DW_NETFLOW.DW_DIM_DATE b ON a.dt = b.day_name
WHERE a.dt = '2018-10-13'
AND a.LABEL_TYPE = 'EVENT'
AND a.os in ('ios','android','other')

result

uv	pv
1830387	208263350