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

With DISTINCT an aggregate order by is required to reference the aggregate arguments

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Won't Do
    • Icon: Major Major
    • None
    • 9.0.2
    • None
    • None
    • Hide

      This works in 8.11.3 but fails in 9.0.2

      select patientid, STRING_AGG(distinct FORMATTIMESTAMP(v.periodstart,'dd-MM-yyyy'),',' ORDER BY v.periodstart)
      from (
      select 1 as patientid, PARSETIMESTAMP('2016-01-01','yyyy-MM-dd') as periodstart UNION
      select 1 as patientid, PARSETIMESTAMP('2016-02-01','yyyy-MM-dd') as periodstart UNION
      select 1 as patientid, PARSETIMESTAMP('2016-02-01','yyyy-MM-dd') as periodstart UNION
      select 1 as patientid, PARSETIMESTAMP('2016-03-01','yyyy-MM-dd') as periodstart UNION
      select 1 as patientid, PARSETIMESTAMP('2016-04-01','yyyy-MM-dd') as periodstart UNION
      select 2 as patientid, PARSETIMESTAMP('2016-01-01','yyyy-MM-dd') as periodstart UNION
      select 2 as patientid, PARSETIMESTAMP('2016-01-05','yyyy-MM-dd') as periodstart
      ) v
      group by v.patientid

      Show
      This works in 8.11.3 but fails in 9.0.2 select patientid, STRING_AGG(distinct FORMATTIMESTAMP(v.periodstart,'dd-MM-yyyy'),',' ORDER BY v.periodstart) from ( select 1 as patientid, PARSETIMESTAMP('2016-01-01','yyyy-MM-dd') as periodstart UNION select 1 as patientid, PARSETIMESTAMP('2016-02-01','yyyy-MM-dd') as periodstart UNION select 1 as patientid, PARSETIMESTAMP('2016-02-01','yyyy-MM-dd') as periodstart UNION select 1 as patientid, PARSETIMESTAMP('2016-03-01','yyyy-MM-dd') as periodstart UNION select 1 as patientid, PARSETIMESTAMP('2016-04-01','yyyy-MM-dd') as periodstart UNION select 2 as patientid, PARSETIMESTAMP('2016-01-01','yyyy-MM-dd') as periodstart UNION select 2 as patientid, PARSETIMESTAMP('2016-01-05','yyyy-MM-dd') as periodstart ) v group by v.patientid

      Hi,

      I'm using the function STRING_AGG(distinct FORMATTIMESTAMP(somedate,'dd-MM-yyyy'),',' ORDER BY somedate) in a script that was made using 8.11.3. This used to work.

      In 9.0.2 I get an error with the following message: With DISTINCT an aggregate order by is required to reference the aggregate arguments

              rhn-engineering-shawkins Steven Hawkins
              gadeynebram Bram Gadeyne (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: