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 "Donald Munro (JIRA)" <ji...@apache.org> on 2008/04/18 11:54:21 UTC
[jira] Created: (DERBY-3631) UDF used with aggregate arguments
results in error 30000
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:
SELECT GroupCol, MAXOF2(SUM(Value1), SUM(Value2)) AS MaxOf2 FROM Test GROUP BY GroupCol
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3631) UDF used with aggregate arguments
results in error 30000
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-3631) UDF used with aggregate arguments
results in error 30000
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3631?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12683488#action_12683488 ]
Dag H. Wanvik commented on DERBY-3631:
--------------------------------------
When backporting DERBY-4071 to the 10.4 branch, I noticed that a test case labelled 3631 (this issue) is present on trunk (svn 719015),
but not on 10.4. This issue is marked as a duplicate of DERBY-3649, which does have patches backported to 10.4.
Not sure if this is intentional, just though I'd mention it.
> 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
> Fix For: 10.3.3.0, 10.4.1.3, 10.5.0.0
>
>
> 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.
[jira] Updated: (DERBY-3631) UDF used with aggregate arguments
results in error 30000
Posted by "Donald Munro (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-3631) UDF used with aggregate arguments
results in error 30000
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3631?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12648885#action_12648885 ]
Kathey Marsden commented on DERBY-3631:
---------------------------------------
This appears to be fixed with trunk and with 10.3.3.0. I'll try to identify what fixed it.
> 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.
[jira] Resolved: (DERBY-3631) UDF used with aggregate arguments
results in error 30000
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden resolved DERBY-3631.
-----------------------------------
Resolution: Duplicate
Fix Version/s: 10.3.3.0
10.4.1.3
10.5.0.0
Resolving as a duplicate of DERBY-3649.
> 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
> Fix For: 10.5.0.0, 10.4.1.3, 10.3.3.0
>
>
> 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.