Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-3777

ORDER BY DESC is ignored in STRING_AGG function when DISTINCT is also specified

    XMLWordPrintable

Details

    Description

      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:

      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
      

      the result is:

      orderByDesc orderByAsc distinctOrderByDesc distinctOrderByAsc
      c,c,b,a a,b,c,c a,b,c a,b,c

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            redfox999 Salvatore R.
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: