| Key: |
DERBY-3631
|
| Type: |
Bug
|
| Status: |
Resolved
|
| Resolution: |
Duplicate
|
| Priority: |
Major
|
| Assignee: |
Unassigned
|
| Reporter: |
Donald Munro
|
| Votes: |
0
|
| Watchers: |
1
|
|
If you were logged in you would be able to see more operations.
|
|
|
|
Environment:
|
Linux, Derby-10.3.2.1
|
|
Issue Links:
|
Duplicate
|
|
This issue duplicates:
|
|
DERBY-3649
can't call a stored function with an aggregate argument without getting the following error: ERROR 42Y29
|
|
|
|
|
|
|
|
| Urgency: |
Normal
|
| Resolution Date: |
19/Nov/08 05:31 PM
|
|
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
|
|
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
|
Show » |
|
