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

can't call a stored function with an aggregate argument without getting the following error: ERROR 42Y29

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.4.1.3
    • 10.4.2.0, 10.5.1.1
    • SQL
    • None
    • Patch Available
    • Regression

    Description

      hi,

      i cant execute the following statement with an aggregate (count) argument:

      SELECT checkCount(count) FROM SYS.SYSTABLES;

      .. without getting this error/stacktrace:

      ERROR 42Y29: The SELECT list of a non-grouped query contains at least one invalid expression. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
      java.sql.SQLSyntaxErrorException: The SELECT list of a non-grouped query contains at least one invalid expression. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
      at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
      at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
      at org.apache.derby.client.am.Statement.execute(Unknown Source)
      at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown Source)
      at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown Source)
      at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown Source)
      at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
      at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
      at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
      at org.apache.derby.impl.tools.ij.Main.main(Unknown Source)
      at org.apache.derby.tools.ij.main(Unknown Source)
      Caused by: org.apache.derby.client.am.SqlException: The SELECT list of a non-grouped query contains at least one invalid expression. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
      at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
      at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
      at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
      at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
      at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
      at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
      at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
      at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)
      at org.apache.derby.client.am.Statement.executeX(Unknown Source)
      ... 9 more

      here the code to create the function in derby:

      CREATE FUNCTION checkCount
      (count INTEGER)
      RETURNS INTEGER
      LANGUAGE JAVA PARAMETER STYLE JAVA
      NO SQL
      EXTERNAL NAME 'ExceptionOnZeroCount.checkCount';

      and here the code of the (quite simple) java method:

      public class ExceptionOnZeroCount {

      public static int checkCount(int count)
      throws SQLException

      { if (count == 0) throw new SQLException("No results found", "38777"); }

      return count;
      }
      }

      hope this will be fixed Good night!

      mamurdian

      Attachments

        1. derby-3649_diff.txt
          4 kB
          Katherine Marsden
        2. derby-3649_try1_diff.txt
          2 kB
          Katherine Marsden

        Issue Links

          Activity

            People

              kmarsden Katherine Marsden
              mamurdian Sebastian
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: