[jira] [Commented] (DERBY-6991) GROUP_CONCAT

["Rick Hillegas (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-6991?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16413118#comment-16413118 ] 

Rick Hillegas commented on DERBY-6991:
--------------------------------------

If you just need to implement GROUP_CONCAT for your own use, please check out the section on user-defined aggregates in the Derby Developer's Guide: http://db.apache.org/derby/docs/10.14/devguide/cdevspecialuda.html

If you are really interested in contributing code to the community, thanks! The remainder of this comment provides an overview of what that might entail.

DERBY-5466 would be a good model to follow. That was the issue which added SQL Standard statistical aggregates via Derby's user-defined aggregate mechanism. I would be happy to coach you through the process.

At a high level, the steps are:

1) Submit an individual contributor license agreement (ICLA). See https://www.apache.org/licenses/icla.pdf

2) Familiarize yourself with Derby's development process. See http://db.apache.org/derby/derby_comm.html#Contribute+Code+or+Documentation

3) Specify what the user experience will be.

4) Submit patches, following the pattern on DERBY-5466.

More about step (3) here:

GROUP_CONCAT is an aggregate which was popularized by MySQL and SQLite before the SQL Standard introduced the LISTAGG aggregate. The MySQL syntax can be found here: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat. The SQLite syntax can be found here: https://www.sqlite.org/lang_aggfunc.html#groupconcat. PostgreSQL offers similar functionality through its STRING_AGG aggregate, described here:
https://www.postgresql.org/docs/9.5/static/functions-aggregate.html

Derby, however, hews closely to the SQL Standard. The Derby committers will insist on the Standard syntax. Here is the full LISTAGG syntax extracted from part 2 of the 2016 Standard, section 10.9 (<aggregate function>):

{noformat}
<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
{noformat}

Most of this functionality cannot be implemented via Derby's user-defined aggregate mechanism. However, the following subset could be implemented via user-defined aggregates if we first add support for multi-arg UDAs (see DERBY-6992):

{noformat}
<listagg set function> ::= LISTAGG
  <left paren>
    <character value expression>
    <comma> <listagg separator>
    [ <listagg overflow clause> ]
  <right paren>
{noformat}

End-to-end, I estimate that this feature would involve a couple months of elapsed time. The following work must be done:

1) Implement DERBY-6992.

2) Implement the limited LISTAGG syntax sketched above.

3) Write tests.

4) Write upgrade code.

5) Write user documentation.

Other community members may be able to help you with these tasks.

I hope that I have not discouraged you. I do think that steps 2-5 can be handled by a newcomer and maybe step 1 as well. But it is important to set your expectations correctly. This is a mini-project involving a lot of community interaction.

What are your thoughts?

Thanks again!


> GROUP_CONCAT
> ------------
>
>                 Key: DERBY-6991
>                 URL: https://issues.apache.org/jira/browse/DERBY-6991
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.15.0.0
>            Reporter: The Alchemist
>            Priority: Minor
>
> I was wondering what it would take to get {{GROUP_CONCAT()}} support.
>  
> If someone points me in the right direction, I can send a patch.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)