db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Donald Munro (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3631) UDF used with aggregate arguments results in error 30000
Date Fri, 18 Apr 2008 10:02:21 GMT

     [ https://issues.apache.org/jira/browse/DERBY-3631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Donald Munro updated DERBY-3631:
--------------------------------

    Description: 
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



  was:
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:
SELECT GroupCol, MAXOF2(SUM(Value1), SUM(Value2)) AS MaxOf2 FROM Test GROUP BY GroupCol




> 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.


Mime
View raw message