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

Trigger recompilation problem when trigger action has its table not qualified with a schema

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.3.1, 10.2.1.6, 10.3.1.4
    • Fix Version/s: 10.1.3.3, 10.2.2.1, 10.3.1.4
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Any

      Description

      Trigger recompilation problem when trigger action has its table not qualified with a schema.

      SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:

      C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
      ij version 10.3

      ij> connect 'jdbc:derby:wombat;create=true';

      ij> create table app.t1 (i int, j int);
      0 rows inserted/updated/deleted

      ij> insert into app.t1 values (1,10);
      1 row inserted/updated/deleted

      – notice trigger action's update statement did not qualify table t1 with a schema name
      ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
      0 rows inserted/updated/deleted

      ij> update app.t1 set i=i+1;
      1 row inserted/updated/deleted

      ij> select * from app.t1;
      I |J
      -----------------------
      2 |1

      1 row selected

      – this action invalidates the SPS and mark for recompilation
      ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
      0 rows inserted/updated/deleted
      ij> disconnect;

      ij> connect 'jdbc:derby:wombat' user 'user1';

      – recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
      ij> update app.t1 set i=i+1;
      ERROR 42Y07: Schema 'USER1' does not exist
      ij>

        Attachments

        1. derby2813-trunk-stat01.txt
          0.4 kB
          Yip Ng
        2. derby2813-trunk-diff01.txt
          9 kB
          Yip Ng
        3. derby2183-trunk-stat02.txt
          0.4 kB
          Yip Ng
        4. derby2183-trunk-diff02.txt
          10 kB
          Yip Ng
        5. derby2183-10.2-stat01.txt
          0.4 kB
          Yip Ng
        6. derby2183-10.2-diff01.txt
          10 kB
          Yip Ng
        7. derby2183-10.1-stat01.txt
          0.4 kB
          Yip Ng
        8. derby2183-10.1-diff01.txt
          10 kB
          Yip Ng

          Activity

            People

            • Assignee:
              yipng Yip Ng
              Reporter:
              yipng Yip Ng
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: