Support Questions
Find answers, ask questions, and share your expertise

Phoenix group by queries returning non-distinct group by results

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

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

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

Explorer

Has anyone seen this? Is Phoenix production ready?