[IMPALA-5545] Add LISTAGG() fuction

• Type: New Feature
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Component/s: Backend, Frontend
• Labels: ansi-sql ramp-up sql-language
• Epic Color: ghx-label-4
• Assignee: Unassigned
• Reporter: Greg Rahn
• Created: 20/Jun/17 18:22
• Updated: 25/Aug/22 02:06

Description

SQL:2016 added LISTAGG() as a standard function for aggregating records into a string list. This would introduce a superset of functionality that GROUP_CONCAT() implements today.

<listagg set function> ::=
  LISTAGG <left paren> [ <set quantifier> ] <character value expression>
      <comma> <listagg separator> [ <listagg overflow clause> ] <right paren>
      <within group specification>
<listagg separator> ::=
  <character string literal>
<listagg overflow clause> ::=
  ON OVERFLOW <overflow behavior>
<overflow behavior> ::=
    ERROR
  | TRUNCATE [ <listagg truncation filler> ] <listagg count indication>
<listagg truncation filler> ::=
  <character string literal>
<listagg count indication> ::=
    WITH COUNT>
  | WITHOUT COUNT>
<array aggregate function> ::=
  ARRAY_AGG
<left paren> <value expression> [ ORDER BY <sort specification list> ] <right paren>
<row pattern count function> ::=
  COUNT <left paren> <row pattern variable name> <period> <asterisk> <right paren>

References:

• http://modern-sql.com/feature/listagg
• http://docs.oracle.com/database/122/SQLRF/LISTAGG.htm
• https://oracle-base.com/articles/12c/listagg-function-enhancements-12cr2

Issue Links

• relates to: IMPALA-5083 add ORDER BY clause to GROUP_CONCAT()