Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Phoenix group by queries returning non-distinct group by results

Highlighted

Phoenix group by queries returning non-distinct group by results

Explorer

We have a query that looks like this:

SELECT security, count(*)
FROM assets
WHERE
	src = 333 AND
	account_id in ('15784','15542','15662','15660') AND
	effective_date = TO_DATE('20170109','yyyyMMdd' ) AND
	security in (10202, 23423, 54564)
GROUP BY security;

This is a pretty simple group by query. The odd thing is that this returns the same security value multiple times. Has anyone ever seen this?

securitycount(*)
10202102
2342310
545648
102023
234234
545646
102023
234232
545641

Here's the query plan which is what I would expect with the distinct.

PLAN
CLIENT 20-CHUNK PARALLEL 20-WAY POINT LOOKUP ON 675 KEYS OVER ASSETS
	SERVER FILTER BY FIRST KEY ONLY
    	SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [SECURITY]
CLIENT MERGE SORT
2 REPLIES 2
Highlighted

Re: Phoenix group by queries returning non-distinct group by results

Explorer

I'm still puzzled by this. How can an explain plan with a distinct on "security" return the same "security" multiple times.

PLAN
CLIENT 20-CHUNK PARALLEL 20-WAY POINT LOOKUP ON 675 KEYS OVER ASSETS SERVER FILTER BY FIRST KEY ONLY SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [SECURITY]CLIENT MERGE SORT
Highlighted

Re: Phoenix group by queries returning non-distinct group by results

Explorer

Has anyone seen this? Is Phoenix production ready?

Don't have an account?
Coming from Hortonworks? Activate your account here