You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <de...@db.apache.org> on 2005/11/02 17:07:55 UTC

[jira] Created: (DERBY-672) Re-enable user defined aggregates

Re-enable user defined aggregates
---------------------------------

         Key: DERBY-672
         URL: http://issues.apache.org/jira/browse/DERBY-672
     Project: Derby
        Type: New Feature
    Reporter: Rick Hillegas


Nicolas Dufour in an email thread titled "functions and list" started on November 2, 2005 requests the ability to create user defined aggregates.

This functionality used to be in Cloudscape. It was disabled presumably because it was considered non-standard. However, most of the machinery needed for this feature is still in the code. We should re-enable user defined aggregates after we agree on acceptable syntax.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

[jira] Commented: (DERBY-672) Re-enable user defined aggregates

Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-672?page=comments#action_12457874 ] 
            
Rick Hillegas commented on DERBY-672:
-------------------------------------

I do not see ANSI syntax for this feature. Here is the syntax used by some other databases.

----------------------------------------

Cloudscape 3.5:

CREATE AGGREGATE AggregateName FOR
    { JavaClassName | ClassAlias }

The aliased class has to be an implementation of a Cloudscape interface (COM.cloudscape.aggregates.AggregateDefinition). That, in turn, means that the class has to implement a couple methods:

accumulate() -- accumulator for non-grouped results
getResult()     -- the final result of the accumulation
merge()          -- accumulator for grouped results

---------------------------------------

Postgres 8.0.0:

CREATE AGGREGATE name (
    BASETYPE = input_data_type,
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , FINALFUNC = ffunc ]
    [ , INITCOND = initial_condition ]
)

Here

SFUNC is the name of an accumulator function
FFUNC is the name of a function which returns the accumulated result

-----------------------------------------------------------

IBM Informix:

CREATE AGGREGATE average
   WITH (
      INIT = initFunction,
      ITER = iterationFunction,
      COMBINE = combinationFunction,
      FINAL = resultFunction
      )

where

initFunction is a function initializing the accumulator
iterationFunction a counting function
combinationFunction an accumulator function
resultFunction a function which returns the accumulated result


> Re-enable user defined aggregates
> ---------------------------------
>
>                 Key: DERBY-672
>                 URL: http://issues.apache.org/jira/browse/DERBY-672
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>            Reporter: Rick Hillegas
>
> Nicolas Dufour in an email thread titled "functions and list" started on November 2, 2005 requests the ability to create user defined aggregates.
> This functionality used to be in Cloudscape. It was disabled presumably because it was considered non-standard. However, most of the machinery needed for this feature is still in the code. We should re-enable user defined aggregates after we agree on acceptable syntax.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[jira] Issue Comment Edited: (DERBY-672) Re-enable user defined aggregates

Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/DERBY-672?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12457874#action_12457874 ] 

Rick Hillegas edited comment on DERBY-672 at 7/23/10 2:42 PM:
--------------------------------------------------------------

I do not see ANSI syntax for this feature. Here is the syntax used by some other databases.

----------------------------------------

Cloudscape 3.5:

CREATE AGGREGATE AggregateName FOR
    { JavaClassName | ClassAlias }

The aliased class has to be an implementation of a Cloudscape interface (COM.cloudscape.aggregates.AggregateDefinition). That, in turn, means that the class has to implement a couple methods:

accumulate() -- accumulator for non-grouped results
getResult()     -- the final result of the accumulation
merge()          -- accumulator for grouped results

---------------------------------------

Postgres 8.0.0:

CREATE AGGREGATE name (
    BASETYPE = input_data_type,
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , FINALFUNC = ffunc ]
    [ , INITCOND = initial_condition ]
)

Here

SFUNC is the name of an accumulator function
FFUNC is the name of a function which returns the accumulated result

-----------------------------------------------------------

IBM Informix:

CREATE AGGREGATE average
   WITH (
      INIT = initFunction,
      ITER = iterationFunction,
      COMBINE = combinationFunction,
      FINAL = resultFunction
      )

where

initFunction is a function initializing the accumulator
iterationFunction a counting function
combinationFunction an accumulator function
resultFunction a function which returns the accumulated result


-----------------------------------------------------------

Oracle:

In Oracle, you define a UDT which implements an aggregator interface. Then you bind that UDT to an aggregate name. See http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28425/aggr_functions.htm

CREATE TYPE MyAggregatorRoutines(
   STATIC FUNCTION ODCIAggregateInitialize( ... ) ...,
   MEMBER FUNCTION ODCIAggregateIterate(...) ... ,
   MEMBER FUNCTION ODCIAggregateMerge(...) ...,
   MEMBER FUNCTION ODCIAggregateTerminate(...)
);
CREATE TYPE BODY MyAggregatorRoutines IS 
...
END;

CREATE FUNCTION MyAggregate(x SomeDataType) RETURN SomeDataType 
AGGREGATE USING MyAggregateRoutines;







      was (Author: rhillegas):
    I do not see ANSI syntax for this feature. Here is the syntax used by some other databases.

----------------------------------------

Cloudscape 3.5:

CREATE AGGREGATE AggregateName FOR
    { JavaClassName | ClassAlias }

The aliased class has to be an implementation of a Cloudscape interface (COM.cloudscape.aggregates.AggregateDefinition). That, in turn, means that the class has to implement a couple methods:

accumulate() -- accumulator for non-grouped results
getResult()     -- the final result of the accumulation
merge()          -- accumulator for grouped results

---------------------------------------

Postgres 8.0.0:

CREATE AGGREGATE name (
    BASETYPE = input_data_type,
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , FINALFUNC = ffunc ]
    [ , INITCOND = initial_condition ]
)

Here

SFUNC is the name of an accumulator function
FFUNC is the name of a function which returns the accumulated result

-----------------------------------------------------------

IBM Informix:

CREATE AGGREGATE average
   WITH (
      INIT = initFunction,
      ITER = iterationFunction,
      COMBINE = combinationFunction,
      FINAL = resultFunction
      )

where

initFunction is a function initializing the accumulator
iterationFunction a counting function
combinationFunction an accumulator function
resultFunction a function which returns the accumulated result

  
> Re-enable user defined aggregates
> ---------------------------------
>
>                 Key: DERBY-672
>                 URL: https://issues.apache.org/jira/browse/DERBY-672
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Rick Hillegas
>
> Nicolas Dufour in an email thread titled "functions and list" started on November 2, 2005 requests the ability to create user defined aggregates.
> This functionality used to be in Cloudscape. It was disabled presumably because it was considered non-standard. However, most of the machinery needed for this feature is still in the code. We should re-enable user defined aggregates after we agree on acceptable syntax.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[jira] Updated: (DERBY-672) Re-enable user defined aggregates

Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-672?page=all ]

Mike Matrigali updated DERBY-672:
---------------------------------

    Component: SQL

> Re-enable user defined aggregates
> ---------------------------------
>
>          Key: DERBY-672
>          URL: http://issues.apache.org/jira/browse/DERBY-672
>      Project: Derby
>         Type: New Feature
>   Components: SQL
>     Reporter: Rick Hillegas

>
> Nicolas Dufour in an email thread titled "functions and list" started on November 2, 2005 requests the ability to create user defined aggregates.
> This functionality used to be in Cloudscape. It was disabled presumably because it was considered non-standard. However, most of the machinery needed for this feature is still in the code. We should re-enable user defined aggregates after we agree on acceptable syntax.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira