[teiid-issues] [JBoss JIRA] (TEIID-3777) ORDER BY DESC is ignored in STRING_AGG function when DISTINCT is also specified

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-3777?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-3777.
-----------------------------------
    Resolution: Done


Add a similar validation as postgresql to prevent order bys on aggregates with distinct to reference values other than the aggregate parameters.  And updated the initialization logic to consider the ordering first.

Thanks for catching this.


> ORDER BY DESC is ignored in STRING_AGG function when DISTINCT is also specified
> -------------------------------------------------------------------------------
>
>                 Key: TEIID-3777
>                 URL: https://issues.jboss.org/browse/TEIID-3777
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>            Reporter: Salvatore R
>            Assignee: Steven Hawkins
>            Priority: Critical
>             Fix For: 8.11.5, 8.12.1, 8.13
>
>
> When both DISTINCT and ORDER BY clause are specified in a STRING_AGG function, the result of the aggregate function is not correctly sorted.
> For example, running the following query:
> {code:sql}
> select 
> 	string_agg(col1, ',' ORDER BY col1 DESC) as orderByDesc, 
> 	string_agg(col1, ',' ORDER BY col1 ASC) as orderByAsc, 
> 	string_agg(DISTINCT col1, ',' ORDER BY col1 DESC) as distinctOrderByDesc, 
> 	string_agg(DISTINCT col1, ',' ORDER BY col1 ASC) as distinctOrderByAsc 
> from (
> 	SELECT 'b' as col1
> 	UNION ALL
> 	SELECT 'c' as col1
> 	UNION ALL
> 	SELECT 'a' as col1
>         UNION ALL
> 	SELECT 'c' as col1
> ) x
> {code}
> the result is:
> ||orderByDesc||orderByAsc||distinctOrderByDesc||distinctOrderByAsc||
> |c,c,b,a|a,b,c,c|a,b,c|a,b,c|



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)