Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-3631

UDF used with aggregate arguments results in error 30000

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.3.2.1
    • Fix Version/s: 10.3.3.0, 10.4.1.3, 10.5.1.1
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Linux, Derby-10.3.2.1
    • Urgency:
      Normal

      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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                donaldmunro Donald Munro
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: