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)" <ji...@apache.org> on 2008/04/18 15:08:21 UTC
[jira] Commented: (DERBY-3631) UDF used with aggregate arguments
results in error 30000
[ https://issues.apache.org/jira/browse/DERBY-3631?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12590436#action_12590436 ]
Rick Hillegas commented on DERBY-3631:
--------------------------------------
Thanks for finding this bug, Donald. I have verified it. As a workaround, you can put the aggregates in a view and invoke your user function on the view. E.g.:
DROP FUNCTION MAXOF2;
DROP VIEW V;
DROP TABLE TEST;
CREATE FUNCTION MAXOF2(ONE DOUBLE, TWO DOUBLE) RETURNS DOUBLE
PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA
EXTERNAL NAME 'java.lang.Math.max';
CREATE TABLE Test( GroupCol INT, Value1 INT, Value2 INT );
INSERT INTO Test VALUES (1, 1, 5);
INSERT INTO Test VALUES (2, -7, 2);
INSERT INTO Test VALUES (2, 1, -5);
CREATE VIEW V ( GroupCol, a, b )
AS SELECT GroupCol, SUM(Value1), SUM(Value2) FROM Test GROUP BY GroupCol;
SELECT GroupCol, MAXOF2( a, b ) as MAXOF2 from v;
> UDF used with aggregate arguments results in error 30000
> --------------------------------------------------------
>
> Key: DERBY-3631
> URL: https://issues.apache.org/jira/browse/DERBY-3631
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.2.1
> Environment: Linux, Derby-10.3.2.1
> Reporter: Donald Munro
>
> UDF used with aggregate arguments results in error 30000: The SELECT list of a grouped query contains at least one invalid expression.
> CREATE FUNCTION MAXOF2(ONE DOUBLE, TWO DOUBLE) RETURNS DOUBLE
> PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA
> EXTERNAL NAME 'java.lang.Math.max'
> CREATE TABLE Test( GroupCol INT, Value1 INT, Value2 INT )
> INSERT INTO Test VALUES (1, 1, 5)
> INSERT INTO Test VALUES (2, -7, 2)
> INSERT INTO Test VALUES (2, 1, -5)
> Using a built in function works:
> SELECT GroupCol, MOD(SUM(Value1), SUM(Value2)) AS ModOf2 FROM Test GROUP BY GroupCol
> But using the UDF does not:
> Both
> SELECT GroupCol, MAXOF2(CAST(SUM(Value1) AS DOUBLE), CAST(SUM(Value2) AS DOUBLE)) AS MaxOf2 FROM Test GROUP BY GroupCol
> and
> SELECT GroupCol, MAXOF2(SUM(Value1), SUM(Value2)) AS MaxOf2 FROM Test GROUP BY GroupCol
> fail
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.