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

ArrayIndexOutOfBoundsException when calling function from trigger

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.1, 10.10.1.1
    • Fix Version/s: 10.10.2.0, 10.11.1.1
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Seen in production

      Description

      I'm having problems will calling functions from within a trigger.
      The problem seems to be with the outer function call (FLOOR() in this case, but it also happens with other functions). It works fine in the SELECT statement, but when used in the trigger it throws a ArrayIndexOutOfBoundsException.
      Remove the FLOOR() part from the trigger and it works fine.

      -- create source table and some data
      CREATE TABLE foo (name VARCHAR(20), val DOUBLE);
      INSERT INTO foo (name, val) VALUES ('A', 10), ('A', 20), ('B', 30), ('C', 40);
      
      -- calling the function works fine here
      SELECT name, FLOOR(AVG(LOG10(val))), COUNT(*)
      FROM foo
      GROUP BY name;
      
      -- create target table for trigger
      CREATE TABLE summary (name VARCHAR(20), aver DOUBLE, size INT);
      
      -- create the trigger
      CREATE TRIGGER trg_foo AFTER INSERT ON foo
      REFERENCING NEW TABLE AS changed FOR EACH STATEMENT MODE DB2SQL
      INSERT INTO summary (name, aver, size)
      SELECT name, FLOOR(AVG(LOG10(val))), COUNT(*)
      FROM changed
      GROUP BY name;
      
      -- insert rows to cause trigger to fire
      INSERT INTO foo (name, val) VALUES ('A', 10), ('A', 20), ('B', 30), ('C', 40);
      
      SELECT * FROM foo;
      SELECT * FROM summary;
      

        Attachments

        1. d6443-1a.diff
          6 kB
          Knut Anders Hatlen

          Activity

            People

            • Assignee:
              knutanders Knut Anders Hatlen
              Reporter:
              tdudgeon Tim Dudgeon
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: