Derby
  1. Derby
  2. DERBY-534

Support use of the WHEN clause in CREATE TRIGGER statements

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.11.0.0
    • Component/s: SQL
    • Urgency:
      Low

      Description

      Support use of the WHEN clause in CREATE TRIGGER statements. The clause is described in the SQL standard (2003) in chapter "11.39 <trigger definition>" under "<triggered action>".

      There are traces in the code that suggests some work has been done on this earlier. If anyone knows something about this, please add a comment to this issue.

      1. derby-534-15-a-more-tests.diff
        8 kB
        Knut Anders Hatlen
      2. derby-534-14-a-remove-formatable-upgrade.diff
        12 kB
        Knut Anders Hatlen
      3. WhenClause.html
        9 kB
        Knut Anders Hatlen
      4. derby-534-13-a-dblook.diff
        26 kB
        Knut Anders Hatlen
      5. derby-534-12-a-subquery-npe.diff
        5 kB
        Knut Anders Hatlen
      6. derby-534-11-a-more-tests.diff
        9 kB
        Knut Anders Hatlen
      7. derby-534-10-a-get-sps.diff
        11 kB
        Knut Anders Hatlen
      8. derby-534-09-b-drop-column-deps.diff
        16 kB
        Knut Anders Hatlen
      9. derby-534-09-a-drop-column-deps.diff
        16 kB
        Knut Anders Hatlen
      10. derby-534-08-a-test-invalidation.diff
        3 kB
        Knut Anders Hatlen
      11. WhenClause.html
        8 kB
        Knut Anders Hatlen
      12. derby-534-07-a-more-restrictions.diff
        14 kB
        Knut Anders Hatlen
      13. derby-534-06-a-temptables.diff
        3 kB
        Knut Anders Hatlen
      14. derby-534-05-a-generated-cols.diff
        8 kB
        Knut Anders Hatlen
      15. WhenClause.html
        7 kB
        Knut Anders Hatlen
      16. derby-534-04-a-referencing.diff
        71 kB
        Knut Anders Hatlen
      17. derby-534-03-a-npe-testcase.diff
        2 kB
        Knut Anders Hatlen
      18. derby-534-02-a-refactor.diff
        5 kB
        Knut Anders Hatlen
      19. derby-534-01-a-syntax.diff
        20 kB
        Knut Anders Hatlen
      20. WhenClause.html
        6 kB
        Knut Anders Hatlen
      21. parser.diff
        3 kB
        Knut Anders Hatlen
      22. WhenClause.html
        4 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          nullone added a comment -

          Please somebody look at this issue. It has been there for almost 7 years.

          I really need to filter target rows.

          This will make Derby much more powerful and user friendly.

          Thanks in advance.

          Show
          nullone added a comment - Please somebody look at this issue. It has been there for almost 7 years. I really need to filter target rows. This will make Derby much more powerful and user friendly. Thanks in advance.
          Hide
          Dag H. Wanvik added a comment -

          Thanks for your interest, nullone. We are always looking for volunteers, Derby being open source. Apart from voting for the issues, we are always welcoming offers to help us move the product forward.
          If you were interested, I'm sure, you would get some help in the derby-dev mailing list from the regulars. If not, you'll have to wait for a dev to take an interest in it I am sure it can be a nice feature to have.

          Show
          Dag H. Wanvik added a comment - Thanks for your interest, nullone. We are always looking for volunteers, Derby being open source. Apart from voting for the issues, we are always welcoming offers to help us move the product forward. If you were interested, I'm sure, you would get some help in the derby-dev mailing list from the regulars. If not, you'll have to wait for a dev to take an interest in it I am sure it can be a nice feature to have.
          Hide
          Knut Anders Hatlen added a comment -

          Attaching a first crack at a functional specification for this feature.

          Show
          Knut Anders Hatlen added a comment - Attaching a first crack at a functional specification for this feature.
          Hide
          Knut Anders Hatlen added a comment -

          Kristian mentioned in the issue description that there are traces of support for the WHEN clause in the code. I see that there is some code for it in CreateTriggerNode for it at least.

          I experimented with the attached patch, parser.diff, that makes the parser accept the WHEN clause and pass the information on to CreateTriggerNode.

          As can be seen from the ij transcript below, this actually creates an SPS for the WHEN clause in SYS.SYSSTATEMENTS:

          ij version 10.11
          ij> connect 'jdbc:derby:memory:db;create=true';
          ij> create table t1(x int);
          0 rows inserted/updated/deleted
          ij> create table t2(x int);
          0 rows inserted/updated/deleted
          ij> create trigger tr1 after insert on t1 referencing new as new for each row when (true) insert into t2 values 1;
          0 rows inserted/updated/deleted
          ij> create trigger tr2 after insert on t1 referencing new as new for each row when (false) insert into t2 values 2;
          0 rows inserted/updated/deleted
          ij> select * from sys.sysstatements where stmtname like 'TRIGGER%';
          STMTID                              |STMTNAME                                                                                                                        |SCHEMAID                            |&|VALID|TEXT                                                                                                                            |LASTCOMPILED                 |COMPILATIONSCHEMAID                 |USINGTEXT                                                                                                                       
          -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          070a00b0-0141-0866-ce24-000018892889|TRIGGERACTN_6ede40af-0141-0866-ce24-000018892889_ed8940a7-0141-0866-ce24-000018892889                                           |80000000-00d2-b38f-4cda-000a0a412c00|T|false|insert into t2 values 1                                                                                                         |2013-09-10 17:04:22.812      |80000000-00d2-b38f-4cda-000a0a412c00|NULL                                                                                                                            
          89e800c0-0141-0866-ce24-000018892889|TRIGGERACTN_71b840bf-0141-0866-ce24-000018892889_ed8940a7-0141-0866-ce24-000018892889                                           |80000000-00d2-b38f-4cda-000a0a412c00|T|true |insert into t2 values 2                                                                                                         |2013-09-10 17:04:23.827      |80000000-00d2-b38f-4cda-000a0a412c00|NULL                                                                                                                            
          8f3600b1-0141-0866-ce24-000018892889|TRIGGERWHEN_6ede40af-0141-0866-ce24-000018892889_ed8940a7-0141-0866-ce24-000018892889                                           |80000000-00d2-b38f-4cda-000a0a412c00|T|false|VALUES true                                                                                                                     |2013-09-10 17:04:22.798      |80000000-00d2-b38f-4cda-000a0a412c00|NULL                                                                                                                            
          921800c1-0141-0866-ce24-000018892889|TRIGGERWHEN_71b840bf-0141-0866-ce24-000018892889_ed8940a7-0141-0866-ce24-000018892889                                           |80000000-00d2-b38f-4cda-000a0a412c00|T|true |VALUES false                                                                                                                    |2013-09-10 17:04:23.816      |80000000-00d2-b38f-4cda-000a0a412c00|NULL                                                                                                                            
          
          4 rows selected
          

          Unfortunately, it doesn't seem to be used at trigger execution time, and both triggers get executed even if only one of them has a WHEN clause that evaluates to TRUE:

          ij> insert into t1 values 1;
          1 row inserted/updated/deleted
          ij> select * from t2;
          X          
          -----------
          1          
          2          
          
          2 rows selected
          

          Also, the current code doesn't seem to like references to transition variables:

          ij> create trigger tr3 after insert on t1 referencing new as new for each row when (new.x > 40) insert into t2 values 3;
          ERROR 42X04: Column 'NEW.X' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'NEW.X' is not a column in the target table. (errorCode = 30000)
          
          Show
          Knut Anders Hatlen added a comment - Kristian mentioned in the issue description that there are traces of support for the WHEN clause in the code. I see that there is some code for it in CreateTriggerNode for it at least. I experimented with the attached patch, parser.diff, that makes the parser accept the WHEN clause and pass the information on to CreateTriggerNode. As can be seen from the ij transcript below, this actually creates an SPS for the WHEN clause in SYS.SYSSTATEMENTS: ij version 10.11 ij> connect 'jdbc:derby:memory:db;create=true'; ij> create table t1(x int); 0 rows inserted/updated/deleted ij> create table t2(x int); 0 rows inserted/updated/deleted ij> create trigger tr1 after insert on t1 referencing new as new for each row when (true) insert into t2 values 1; 0 rows inserted/updated/deleted ij> create trigger tr2 after insert on t1 referencing new as new for each row when (false) insert into t2 values 2; 0 rows inserted/updated/deleted ij> select * from sys.sysstatements where stmtname like 'TRIGGER%'; STMTID |STMTNAME |SCHEMAID |&|VALID|TEXT |LASTCOMPILED |COMPILATIONSCHEMAID |USINGTEXT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 070a00b0-0141-0866-ce24-000018892889|TRIGGERACTN_6ede40af-0141-0866-ce24-000018892889_ed8940a7-0141-0866-ce24-000018892889 |80000000-00d2-b38f-4cda-000a0a412c00|T|false|insert into t2 values 1 |2013-09-10 17:04:22.812 |80000000-00d2-b38f-4cda-000a0a412c00|NULL 89e800c0-0141-0866-ce24-000018892889|TRIGGERACTN_71b840bf-0141-0866-ce24-000018892889_ed8940a7-0141-0866-ce24-000018892889 |80000000-00d2-b38f-4cda-000a0a412c00|T|true |insert into t2 values 2 |2013-09-10 17:04:23.827 |80000000-00d2-b38f-4cda-000a0a412c00|NULL 8f3600b1-0141-0866-ce24-000018892889|TRIGGERWHEN_6ede40af-0141-0866-ce24-000018892889_ed8940a7-0141-0866-ce24-000018892889 |80000000-00d2-b38f-4cda-000a0a412c00|T|false|VALUES true |2013-09-10 17:04:22.798 |80000000-00d2-b38f-4cda-000a0a412c00|NULL 921800c1-0141-0866-ce24-000018892889|TRIGGERWHEN_71b840bf-0141-0866-ce24-000018892889_ed8940a7-0141-0866-ce24-000018892889 |80000000-00d2-b38f-4cda-000a0a412c00|T|true |VALUES false |2013-09-10 17:04:23.816 |80000000-00d2-b38f-4cda-000a0a412c00|NULL 4 rows selected Unfortunately, it doesn't seem to be used at trigger execution time, and both triggers get executed even if only one of them has a WHEN clause that evaluates to TRUE: ij> insert into t1 values 1; 1 row inserted/updated/deleted ij> select * from t2; X ----------- 1 2 2 rows selected Also, the current code doesn't seem to like references to transition variables: ij> create trigger tr3 after insert on t1 referencing new as new for each row when (new.x > 40) insert into t2 values 3; ERROR 42X04: Column 'NEW.X' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'NEW.X' is not a column in the target table. (errorCode = 30000)
          Hide
          Knut Anders Hatlen added a comment -

          Uploading a revised functional specification that adds more details about upgrade, privilege handling and dependency tracking.

          Show
          Knut Anders Hatlen added a comment - Uploading a revised functional specification that adds more details about upgrade, privilege handling and dependency tracking.
          Hide
          Knut Anders Hatlen added a comment -

          Attaching derby-534-01-a-syntax.diff, which is a first increment towards support for the WHEN clause.

          The patch adds the WHEN clause syntax to sqlgrammar.jj and wires it together with the existing machinery. The existing machinery contains logic for storing the WHEN clause in the system tables, and only some minor adjustments were needed to get that part working. It did not include any execution logic, so the patch had to add that.

          The most substantial changes are in GenericTriggerExecutor and its two subclasses: RowTriggerExecutor and StatementTriggerExecutor. This is where the WHEN clause and the trigger action are executed.

          The patch also adds some basic test cases for things that currently work.

          What doesn't work yet:

          • There's no dependency tracking
          • Upgrade logic is missing
          • WHEN clause cannot reference transition variables (cannot have a WHEN clause such as WHEN (NEW.X > 5))
          • Probably a lot more

          All the regression tests passed with the patch.

          Show
          Knut Anders Hatlen added a comment - Attaching derby-534-01-a-syntax.diff, which is a first increment towards support for the WHEN clause. The patch adds the WHEN clause syntax to sqlgrammar.jj and wires it together with the existing machinery. The existing machinery contains logic for storing the WHEN clause in the system tables, and only some minor adjustments were needed to get that part working. It did not include any execution logic, so the patch had to add that. The most substantial changes are in GenericTriggerExecutor and its two subclasses: RowTriggerExecutor and StatementTriggerExecutor. This is where the WHEN clause and the trigger action are executed. The patch also adds some basic test cases for things that currently work. What doesn't work yet: There's no dependency tracking Upgrade logic is missing WHEN clause cannot reference transition variables (cannot have a WHEN clause such as WHEN (NEW.X > 5)) Probably a lot more All the regression tests passed with the patch.
          Hide
          ASF subversion and git services added a comment -

          Commit 1523965 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1523965 ]

          DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

          Add the WHEN clause syntax to the grammar and wire it together with the
          existing partial code for the WHEN clause.

          Make RowTriggerExecutor and StatementTriggerExecutor execute the WHEN
          clause and use the result to decide whether the trigger action should
          be executed.

          Add some basic positive tests for the currently supported subset of the
          functionality.

          Show
          ASF subversion and git services added a comment - Commit 1523965 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1523965 ] DERBY-534 : Support use of the WHEN clause in CREATE TRIGGER statements Add the WHEN clause syntax to the grammar and wire it together with the existing partial code for the WHEN clause. Make RowTriggerExecutor and StatementTriggerExecutor execute the WHEN clause and use the result to decide whether the trigger action should be executed. Add some basic positive tests for the currently supported subset of the functionality.
          Hide
          Knut Anders Hatlen added a comment -

          Another thing that doesn't work yet, is recursive triggers with a WHEN clause. They seem to always run into an assert failure (or NPE in non-debug builds). I found a way to reproduce that problem without a WHEN clause too, so it doesn't appear to be specific to the WHEN clause implementation. It's probably going to be more likely to run into it with a WHEN clause, though.

          Show
          Knut Anders Hatlen added a comment - Another thing that doesn't work yet, is recursive triggers with a WHEN clause. They seem to always run into an assert failure (or NPE in non-debug builds). I found a way to reproduce that problem without a WHEN clause too, so it doesn't appear to be specific to the WHEN clause implementation. It's probably going to be more likely to run into it with a WHEN clause, though.
          Hide
          Knut Anders Hatlen added a comment -

          Attaching derby-534-02-a-refactor.diff which performs a small refactoring of the trigger execution logic. It moves some common logic from StatementTriggerExecutor and RowTriggerExecutor to GenericTriggerExecutor so that it is not duplicated.

          The refactoring also fixes two problems with the original logic:

          • the before and after result sets were left open if the WHEN clause of a statement trigger returned false
          • a row trigger that fired multiple times in a single statement would create a new PreparedStatement for the WHEN clause each time instead of reusing it

          All the regression tests ran cleanly.

          Show
          Knut Anders Hatlen added a comment - Attaching derby-534-02-a-refactor.diff which performs a small refactoring of the trigger execution logic. It moves some common logic from StatementTriggerExecutor and RowTriggerExecutor to GenericTriggerExecutor so that it is not duplicated. The refactoring also fixes two problems with the original logic: the before and after result sets were left open if the WHEN clause of a statement trigger returned false a row trigger that fired multiple times in a single statement would create a new PreparedStatement for the WHEN clause each time instead of reusing it All the regression tests ran cleanly.
          Hide
          ASF subversion and git services added a comment -

          Commit 1524645 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1524645 ]

          DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

          Move common logic for executing WHEN clause and trigger action to the
          base class GenericTriggerExecutor. In addition to reducing code
          duplication, the change makes row triggers reuse the prepared
          statement for the WHEN clause (same as it already does for the trigger
          action), and it makes statement triggers not leave the before and
          after result sets open if the WHEN clause evaluates to false.

          Show
          ASF subversion and git services added a comment - Commit 1524645 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1524645 ] DERBY-534 : Support use of the WHEN clause in CREATE TRIGGER statements Move common logic for executing WHEN clause and trigger action to the base class GenericTriggerExecutor. In addition to reducing code duplication, the change makes row triggers reuse the prepared statement for the WHEN clause (same as it already does for the trigger action), and it makes statement triggers not leave the before and after result sets open if the WHEN clause evaluates to false.
          Hide
          Knut Anders Hatlen added a comment -

          I came across a problem with a NullPointerException being thrown when executing the triggered SQL statement. This happens for row triggers if the WHEN clause contains a subquery.

          derby-534-03-a-npe-testcase.diff adds a test case to TriggerWhenClauseTest that reproduces the NPE. The test case is disabled for now. I'll look into it later.

          Show
          Knut Anders Hatlen added a comment - I came across a problem with a NullPointerException being thrown when executing the triggered SQL statement. This happens for row triggers if the WHEN clause contains a subquery. derby-534-03-a-npe-testcase.diff adds a test case to TriggerWhenClauseTest that reproduces the NPE. The test case is disabled for now. I'll look into it later.
          Hide
          ASF subversion and git services added a comment -

          Commit 1525819 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1525819 ]

          DERBY-534: Add a disabled test case for NPE with subquery in WHEN clause

          Show
          ASF subversion and git services added a comment - Commit 1525819 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1525819 ] DERBY-534 : Add a disabled test case for NPE with subquery in WHEN clause
          Hide
          Knut Anders Hatlen added a comment -

          derby-534-04-a-referencing.diff makes it possible to create triggers whose WHEN clause references the transition tables and transition variables defined in the REFERENCING clause.

          Unfortunately, for this to work, the SYSTRIGGERS table needed an extra column to hold the text of the original WHEN clause, so some upgrade logic is needed. And since we haven't added columns to existing system tables before, some new mechanisms were needed.

          Additionally, that extra column had to be reflected in TriggerDescriptor, which is a Formatable, so some version handling was needed in its serialization logic. (Although I haven't found any scenario where it's stored in the database with one version and read with another version. I think it's only stored as part of a stored prepared statement, which will be discarded on version change anyway. So we might be able to get rid of that piece of logic later.)

          With a little bit of refactoring, I was able to reuse the existing processing of transition tables/variables in triggered SQL statements for WHEN clauses. This logic is driven from CreateTriggerNode.bindReferencesClause(), and it essentially replaces references to the transition tables/variables with internal SQL syntax for VTIs and Java method calls.

          Patch details:

          java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java
          java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java
          java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
          java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor_v10_10.java

          • Add a new field to TriggerDescriptor for the WHEN clause text, update Formatable logic, and provide a new class using the old Formatable logic for compatibility.

          java/engine/org/apache/derby/iapi/sql/compile/Parser.java
          java/engine/org/apache/derby/impl/sql/compile/ParserImpl.java
          java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java

          • Add a method for parsing an SQL fragment that contains only a search condition. Used by CreateTriggerNode to re-parse the WHEN clause after references to transition tables or transition variables have been rewritten.

          java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj

          • Add a method for parsing a stand-alone search condition to help implementing the new method added to the Parser interface.
          • Pass original WHEN clause text as argument to CreateTriggerNode (will eventually end up in the new column in SYSTRIGGERS).
          • Raise an exception if the WHEN clause is used in a soft-upgraded database.

          java/engine/org/apache/derby/iapi/sql/dictionary/CatalogRowFactory.java
          java/engine/org/apache/derby/impl/sql/catalog/SYSTRIGGERSRowFactory.java

          • Add code for the new column in SYSTRIGGERS.
          • Add logic to expose different shape depending on soft and full upgrade.

          java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java

          • Remove unused code for fetching all trigger descriptors.
          • Change code that creates or upgrades catalogues, so that it uses the new CatalogRowFactory method that exposes what shape the table should have in a fully upgraded database (otherwise, since the database is still in soft upgrade when the upgrade code is running, it would just upgrade the table to its old shape).

          java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java

          • Add code that drives upgrade of the SYSTRIGGERS table if needed.

          java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.java
          java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java
          java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java

          • Pass the WHEN clause text through the layers. Mainly new parameters in existing methods.

          java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java

          • Refactor code to perform the same transformations on the WHEN clause text as it currently performs on the triggered SQL statement text.

          java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java

          • Add basic test cases for WHEN clauses that use transition tables and transition variables.

          java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java
          java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeRun.java

          • Test that the WHEN clause cannot be used in a soft-upgraded database, and that it can be used in a fully upgraded database.

          java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
          java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
          java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
          java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
          java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java

          Update tests to expect the extra column in SYSTRIGGERS.

          Show
          Knut Anders Hatlen added a comment - derby-534-04-a-referencing.diff makes it possible to create triggers whose WHEN clause references the transition tables and transition variables defined in the REFERENCING clause. Unfortunately, for this to work, the SYSTRIGGERS table needed an extra column to hold the text of the original WHEN clause, so some upgrade logic is needed. And since we haven't added columns to existing system tables before, some new mechanisms were needed. Additionally, that extra column had to be reflected in TriggerDescriptor, which is a Formatable, so some version handling was needed in its serialization logic. (Although I haven't found any scenario where it's stored in the database with one version and read with another version. I think it's only stored as part of a stored prepared statement, which will be discarded on version change anyway. So we might be able to get rid of that piece of logic later.) With a little bit of refactoring, I was able to reuse the existing processing of transition tables/variables in triggered SQL statements for WHEN clauses. This logic is driven from CreateTriggerNode.bindReferencesClause(), and it essentially replaces references to the transition tables/variables with internal SQL syntax for VTIs and Java method calls. Patch details: java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor_v10_10.java Add a new field to TriggerDescriptor for the WHEN clause text, update Formatable logic, and provide a new class using the old Formatable logic for compatibility. java/engine/org/apache/derby/iapi/sql/compile/Parser.java java/engine/org/apache/derby/impl/sql/compile/ParserImpl.java java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java Add a method for parsing an SQL fragment that contains only a search condition. Used by CreateTriggerNode to re-parse the WHEN clause after references to transition tables or transition variables have been rewritten. java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Add a method for parsing a stand-alone search condition to help implementing the new method added to the Parser interface. Pass original WHEN clause text as argument to CreateTriggerNode (will eventually end up in the new column in SYSTRIGGERS). Raise an exception if the WHEN clause is used in a soft-upgraded database. java/engine/org/apache/derby/iapi/sql/dictionary/CatalogRowFactory.java java/engine/org/apache/derby/impl/sql/catalog/SYSTRIGGERSRowFactory.java Add code for the new column in SYSTRIGGERS. Add logic to expose different shape depending on soft and full upgrade. java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Remove unused code for fetching all trigger descriptors. Change code that creates or upgrades catalogues, so that it uses the new CatalogRowFactory method that exposes what shape the table should have in a fully upgraded database (otherwise, since the database is still in soft upgrade when the upgrade code is running, it would just upgrade the table to its old shape). java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java Add code that drives upgrade of the SYSTRIGGERS table if needed. java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.java java/engine/org/apache/derby/impl/sql/execute/CreateTriggerConstantAction.java java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java Pass the WHEN clause text through the layers. Mainly new parameters in existing methods. java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java Refactor code to perform the same transformations on the WHEN clause text as it currently performs on the triggered SQL statement text. java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java Add basic test cases for WHEN clauses that use transition tables and transition variables. java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeRun.java Test that the WHEN clause cannot be used in a soft-upgraded database, and that it can be used in a fully upgraded database. java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java Update tests to expect the extra column in SYSTRIGGERS.
          Hide
          ASF subversion and git services added a comment -

          Commit 1526831 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1526831 ]

          DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

          Allow references to transition variables and transition tables in the
          WHEN clause. To support this, a new column WHENCLAUSETEXT is added to
          the SYS.SYSTRIGGERS table, and a corresponding field is added to the
          TriggerDescriptor class.

          The logic that transforms triggered SQL statements to internal syntax
          for accessing the transition variables and transition tables (via Java
          method calls and VTIs) is reused on the WHEN clause text so that the
          same transformation happens there.

          Upgrade logic is added so that the new column in SYS.SYSTRIGGERS will
          be created when a database is upgraded from an older version. The WHEN
          clause is now disabled in the parser when running in soft upgrade
          mode. An upgrade test case checks that the WHEN clause can only be
          used in a hard-upgraded database, and that a reasonable error is
          raised otherwise.

          Show
          ASF subversion and git services added a comment - Commit 1526831 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1526831 ] DERBY-534 : Support use of the WHEN clause in CREATE TRIGGER statements Allow references to transition variables and transition tables in the WHEN clause. To support this, a new column WHENCLAUSETEXT is added to the SYS.SYSTRIGGERS table, and a corresponding field is added to the TriggerDescriptor class. The logic that transforms triggered SQL statements to internal syntax for accessing the transition variables and transition tables (via Java method calls and VTIs) is reused on the WHEN clause text so that the same transformation happens there. Upgrade logic is added so that the new column in SYS.SYSTRIGGERS will be created when a database is upgraded from an older version. The WHEN clause is now disabled in the parser when running in soft upgrade mode. An upgrade test case checks that the WHEN clause can only be used in a hard-upgraded database, and that a reasonable error is raised otherwise.
          Hide
          Knut Anders Hatlen added a comment - - edited

          Uploading a new revision of the functional specification. It now mentions the new column in the SYSTRIGGERS table, and also some more errors that should be handled: The WHEN clause of a before trigger should not reference generated columns in its NEW transition variable (see DERBY-3948). WHEN clauses should not reference tables in the SESSION schema.

          [comment edited: clarified that the restriction on generated columns only applies to the NEW transition variable of BEFORE triggers]

          Show
          Knut Anders Hatlen added a comment - - edited Uploading a new revision of the functional specification. It now mentions the new column in the SYSTRIGGERS table, and also some more errors that should be handled: The WHEN clause of a before trigger should not reference generated columns in its NEW transition variable (see DERBY-3948 ). WHEN clauses should not reference tables in the SESSION schema. [comment edited: clarified that the restriction on generated columns only applies to the NEW transition variable of BEFORE triggers]
          Hide
          Knut Anders Hatlen added a comment -

          derby-534-05-a-generated-cols.diff makes CreateTriggerNode raise an error if it finds that the WHEN clause of a BEFORE trigger contains a reference to a generated column in the NEW transition variable. It implements the check by reusing the code added in DERBY-3948. The patch also adds tests.

          All regression tests ran cleanly.

          Show
          Knut Anders Hatlen added a comment - derby-534-05-a-generated-cols.diff makes CreateTriggerNode raise an error if it finds that the WHEN clause of a BEFORE trigger contains a reference to a generated column in the NEW transition variable. It implements the check by reusing the code added in DERBY-3948 . The patch also adds tests. All regression tests ran cleanly.
          Hide
          ASF subversion and git services added a comment -

          Commit 1527489 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1527489 ]

          DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

          Reject references to generated columns in the NEW transition variables
          of BEFORE triggers, as required by the SQL standard. See also
          DERBY-3948.

          Show
          ASF subversion and git services added a comment - Commit 1527489 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1527489 ] DERBY-534 : Support use of the WHEN clause in CREATE TRIGGER statements Reject references to generated columns in the NEW transition variables of BEFORE triggers, as required by the SQL standard. See also DERBY-3948 .
          Hide
          Knut Anders Hatlen added a comment -

          Attaching derby-534-06-a-temptables.diff, which implements the restriction that a WHEN clause cannot reference tables in the SESSION schema.

          All regression tests passed with the patch.

          Show
          Knut Anders Hatlen added a comment - Attaching derby-534-06-a-temptables.diff, which implements the restriction that a WHEN clause cannot reference tables in the SESSION schema. All regression tests passed with the patch.
          Hide
          ASF subversion and git services added a comment -

          Commit 1527993 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1527993 ]

          DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

          Disallow references to tables in the SESSION schema in the WHEN clause.

          Show
          ASF subversion and git services added a comment - Commit 1527993 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1527993 ] DERBY-534 : Support use of the WHEN clause in CREATE TRIGGER statements Disallow references to tables in the SESSION schema in the WHEN clause.
          Hide
          Knut Anders Hatlen added a comment -

          Attaching a new revision of the functional specification, which adds some more restrictions on what's allowed in a WHEN clause. These are restrictions that already exist for the WHERE clause or for the triggered SQL statement.

          The patch derby-534-07-a-more-restrictions.diff implements the restrictions by adding checks that verify that the WHEN clause does not contain dynamic parameters, and that its expression evaluates to a BOOLEAN.

          Show
          Knut Anders Hatlen added a comment - Attaching a new revision of the functional specification, which adds some more restrictions on what's allowed in a WHEN clause. These are restrictions that already exist for the WHERE clause or for the triggered SQL statement. The patch derby-534-07-a-more-restrictions.diff implements the restrictions by adding checks that verify that the WHEN clause does not contain dynamic parameters, and that its expression evaluates to a BOOLEAN.
          Hide
          ASF subversion and git services added a comment -

          Commit 1528401 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1528401 ]

          DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

          Forbid CREATE TRIGGER statements whose WHEN clause contains a
          parameter marker or returns a non-BOOLEAN value.

          Show
          ASF subversion and git services added a comment - Commit 1528401 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1528401 ] DERBY-534 : Support use of the WHEN clause in CREATE TRIGGER statements Forbid CREATE TRIGGER statements whose WHEN clause contains a parameter marker or returns a non-BOOLEAN value.
          Hide
          Knut Anders Hatlen added a comment -

          The attached patch, derby-534-08-a-test-invalidation.diff, adds a test case that verifies that the WHEN clause SPS is invalidated and recompiled if one of its dependencies requests a recompilation.

          Show
          Knut Anders Hatlen added a comment - The attached patch, derby-534-08-a-test-invalidation.diff, adds a test case that verifies that the WHEN clause SPS is invalidated and recompiled if one of its dependencies requests a recompilation.
          Hide
          ASF subversion and git services added a comment -

          Commit 1529145 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1529145 ]

          DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

          Add a test case to verify that the WHEN clause SPS is invalidated and
          recompiled if one of its dependencies requests a recompilation.

          Show
          ASF subversion and git services added a comment - Commit 1529145 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1529145 ] DERBY-534 : Support use of the WHEN clause in CREATE TRIGGER statements Add a test case to verify that the WHEN clause SPS is invalidated and recompiled if one of its dependencies requests a recompilation.
          Hide
          Knut Anders Hatlen added a comment -

          derby-534-09-a-drop-column-deps.diff adds tests that verify dependencies are tracked so that dropping an object referenced in a WHEN clause won't silently succeed.

          For columns referenced in NEW/OLD transition variables and transition tables, the dependency tracking seemed to be working already.

          For other columns referenced in the WHEN clause (via sub-queries) the dependencies were not tracked. The patch fixes this by generalizing the existing AlterTableConstantNode.columnDroppedAndTriggerDependencies() that detects these dependencies in the triggered SQL statement, and applying that method on the WHEN clause too.

          Dependencies are not tracked for other objects than columns. This is because of DERBY-2041, which also affects triggers without a WHEN clause.

          I'm running regression tests on the patch now.

          Show
          Knut Anders Hatlen added a comment - derby-534-09-a-drop-column-deps.diff adds tests that verify dependencies are tracked so that dropping an object referenced in a WHEN clause won't silently succeed. For columns referenced in NEW/OLD transition variables and transition tables, the dependency tracking seemed to be working already. For other columns referenced in the WHEN clause (via sub-queries) the dependencies were not tracked. The patch fixes this by generalizing the existing AlterTableConstantNode.columnDroppedAndTriggerDependencies() that detects these dependencies in the triggered SQL statement, and applying that method on the WHEN clause too. Dependencies are not tracked for other objects than columns. This is because of DERBY-2041 , which also affects triggers without a WHEN clause. I'm running regression tests on the patch now.
          Hide
          Knut Anders Hatlen added a comment -

          There was a failure in the upgrade tests with the patch, in the test case Changes10_7.testAlterTableDropColumnAndTriggerAction. The reason was that the patch factored out the call that retrieved the trigger action SPS from columnDroppedAndTriggerDependencies(), but in databases soft-upgraded from 10.6 and earlier the call had to be inside the try/catch block in columnDroppedAndTriggerDependencies() to work around missing dependency tracking in those versions. Since it was no longer inside the try/catch block, an expected error came out with the wrong SQLState because it hadn't been caught and replaced with an exception with the correct SQLState.

          The derby-534-09-b-drop-column-deps.diff patch changes this back so that getActionSPS() is called at the same place as before. Instead of passing the SPS as an argument to the columnDroppedAndTriggerDependencies() method, the method now needs to check how it's called and determine whether it should call getActionSPS() or getWhenClauseSPS().

          All regression tests passed with the revised patch.

          Show
          Knut Anders Hatlen added a comment - There was a failure in the upgrade tests with the patch, in the test case Changes10_7.testAlterTableDropColumnAndTriggerAction. The reason was that the patch factored out the call that retrieved the trigger action SPS from columnDroppedAndTriggerDependencies(), but in databases soft-upgraded from 10.6 and earlier the call had to be inside the try/catch block in columnDroppedAndTriggerDependencies() to work around missing dependency tracking in those versions. Since it was no longer inside the try/catch block, an expected error came out with the wrong SQLState because it hadn't been caught and replaced with an exception with the correct SQLState. The derby-534-09-b-drop-column-deps.diff patch changes this back so that getActionSPS() is called at the same place as before. Instead of passing the SPS as an argument to the columnDroppedAndTriggerDependencies() method, the method now needs to check how it's called and determine whether it should call getActionSPS() or getWhenClauseSPS(). All regression tests passed with the revised patch.
          Hide
          ASF subversion and git services added a comment -

          Commit 1530887 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1530887 ]

          DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

          Reuse code for dependency checking of the triggered SQL statement for
          checking dependencies in the WHEN clause.

          Add test to verify that attempts to drop columns referenced in the WHEN
          clause detect that the trigger is dependent on the columns.

          Show
          ASF subversion and git services added a comment - Commit 1530887 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1530887 ] DERBY-534 : Support use of the WHEN clause in CREATE TRIGGER statements Reuse code for dependency checking of the triggered SQL statement for checking dependencies in the WHEN clause. Add test to verify that attempts to drop columns referenced in the WHEN clause detect that the trigger is dependent on the columns.
          Hide
          Knut Anders Hatlen added a comment -

          The old, incomplete code for the WHEN clause, which predated this issue, has diverged from the rest of the trigger implementation over time. In particular, I noticed that TriggerDescriptor.getActionSPS() contained fixes for DERBY-4874 and an old bug from the Cloudscape bug tracker (4821), but none of those fixes were in TriggerDescriptor.getWhenClauseSPS().

          The attached patch, derby-534-10-a-get-sps.diff, moves most of the code out of getActionSPS() and into a helper method called getSPS(). Both getActionSPS() and getWhenClauseSPS() now call that helper method, so that they behave similarly and the two aforementioned bugs are also fixed for the WHEN clause.

          The patch adds two test cases to TriggerWhenClauseTest:

          • testDerby4874() reproduces symptoms similar to DERBY-4874 by changing the data type of a column referenced from a WHEN clause. The test case fails with a truncation error without the fix.
          • testCloudscapeBug4821() shows a problem with system table locks being held in the parent transaction after recompilation of a WHEN clause that had been invalidated. The test case fails with a lock timeout without the fix.

          All the regression tests passed with the patch.

          Show
          Knut Anders Hatlen added a comment - The old, incomplete code for the WHEN clause, which predated this issue, has diverged from the rest of the trigger implementation over time. In particular, I noticed that TriggerDescriptor.getActionSPS() contained fixes for DERBY-4874 and an old bug from the Cloudscape bug tracker (4821), but none of those fixes were in TriggerDescriptor.getWhenClauseSPS(). The attached patch, derby-534-10-a-get-sps.diff, moves most of the code out of getActionSPS() and into a helper method called getSPS(). Both getActionSPS() and getWhenClauseSPS() now call that helper method, so that they behave similarly and the two aforementioned bugs are also fixed for the WHEN clause. The patch adds two test cases to TriggerWhenClauseTest: testDerby4874() reproduces symptoms similar to DERBY-4874 by changing the data type of a column referenced from a WHEN clause. The test case fails with a truncation error without the fix. testCloudscapeBug4821() shows a problem with system table locks being held in the parent transaction after recompilation of a WHEN clause that had been invalidated. The test case fails with a lock timeout without the fix. All the regression tests passed with the patch.
          Hide
          ASF subversion and git services added a comment -

          Commit 1531226 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1531226 ]

          DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

          Make the code in TriggerDescriptor.getActionSPS() reusable for
          TriggerDescriptor.getWhenClauseSPS() so that the fixes for DERBY-4874
          and Cloudscape bug 4821 also get applied to the WHEN clause.

          Show
          ASF subversion and git services added a comment - Commit 1531226 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1531226 ] DERBY-534 : Support use of the WHEN clause in CREATE TRIGGER statements Make the code in TriggerDescriptor.getActionSPS() reusable for TriggerDescriptor.getWhenClauseSPS() so that the fixes for DERBY-4874 and Cloudscape bug 4821 also get applied to the WHEN clause.
          Hide
          Knut Anders Hatlen added a comment -

          The attached patch, derby-534-11-a-more-tests.diff, adds tests that verify that the WHEN clause operates with the privileges of the user that created the trigger, and that exceptions thrown in the WHEN clause are handled gracefully. No problems were found by these tests.

          Show
          Knut Anders Hatlen added a comment - The attached patch, derby-534-11-a-more-tests.diff, adds tests that verify that the WHEN clause operates with the privileges of the user that created the trigger, and that exceptions thrown in the WHEN clause are handled gracefully. No problems were found by these tests.
          Hide
          ASF subversion and git services added a comment -

          Commit 1531279 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1531279 ]

          DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

          Add tests to verify that the when clause operates with the privileges
          of the user that created the trigger, and that exceptions thrown in
          the WHEN clause are handled gracefully.

          Show
          ASF subversion and git services added a comment - Commit 1531279 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1531279 ] DERBY-534 : Support use of the WHEN clause in CREATE TRIGGER statements Add tests to verify that the when clause operates with the privileges of the user that created the trigger, and that exceptions thrown in the WHEN clause are handled gracefully.
          Hide
          Knut Anders Hatlen added a comment -

          Attaching derby-534-12-a-subquery-npe.diff which fixes the NPE exposed by the 03 patch.

          The NPE happened in GenericStatementContext.setTopResultSet() while copying materialized subqueries over to the new subquery tracking array in a for loop. As far as I can tell, the intention of the for loop is to copy every non-null element in the materializedSubqueries array to the new subquery tracking array. However, instead of checking that the element in materializedSubqueries is non-null, it checks that the element at the given index position on the old subquery tracking array is non-null. The NPE is thrown because the old subquery tracking array is null.

          This is old code, so it may be possible to construct a test case that makes it fail even without a WHEN clause. I haven't been able to do that so far, though. According to the test coverage reports, the line that fails with an NPE is only partially covered: https://builds.apache.org/job/Derby-JaCoCo/lastSuccessfulBuild/artifact/coverage-report/org.apache.derby.impl.sql.conn/GenericStatementContext.java.html#L356

          The patch enables the previously disabled test case added by the 03 patch. It also adds some more test cases with scalar subqueries in the WHEN clause.

          All regression tests ran cleanly with the patch.

          Show
          Knut Anders Hatlen added a comment - Attaching derby-534-12-a-subquery-npe.diff which fixes the NPE exposed by the 03 patch. The NPE happened in GenericStatementContext.setTopResultSet() while copying materialized subqueries over to the new subquery tracking array in a for loop. As far as I can tell, the intention of the for loop is to copy every non-null element in the materializedSubqueries array to the new subquery tracking array. However, instead of checking that the element in materializedSubqueries is non-null, it checks that the element at the given index position on the old subquery tracking array is non-null. The NPE is thrown because the old subquery tracking array is null. This is old code, so it may be possible to construct a test case that makes it fail even without a WHEN clause. I haven't been able to do that so far, though. According to the test coverage reports, the line that fails with an NPE is only partially covered: https://builds.apache.org/job/Derby-JaCoCo/lastSuccessfulBuild/artifact/coverage-report/org.apache.derby.impl.sql.conn/GenericStatementContext.java.html#L356 The patch enables the previously disabled test case added by the 03 patch. It also adds some more test cases with scalar subqueries in the WHEN clause. All regression tests ran cleanly with the patch.
          Hide
          ASF subversion and git services added a comment -

          Commit 1532666 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1532666 ]

          DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

          Fix incorrect null check when merging subqueryTrackingArray and
          materializedSubqueries in GenericStatementContext.setTopResultSet().
          Used to cause NullPointerException in some cases when a WHEN clause
          contained a subquery.

          Add more tests for scalar subqueries in WHEN clauses.

          Show
          ASF subversion and git services added a comment - Commit 1532666 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1532666 ] DERBY-534 : Support use of the WHEN clause in CREATE TRIGGER statements Fix incorrect null check when merging subqueryTrackingArray and materializedSubqueries in GenericStatementContext.setTopResultSet(). Used to cause NullPointerException in some cases when a WHEN clause contained a subquery. Add more tests for scalar subqueries in WHEN clauses.
          Hide
          Knut Anders Hatlen added a comment -

          Attaching derby-534-13-a-dblook.diff which makes dblook show the WHEN clause for triggers that have one.

          It changes DB_Trigger.doTriggers() so that it looks for the WHENCLAUSETEXT column when it scans SYS.SYSTRIGGERS.

          Since the WHENCLAUSETEXT column is not present in soft-upgraded databases, dblook should not look for that column unless the data dictionary has been upgraded to 10.11. Instead of executing different queries against SYS.SYSTRIGGERS depending on whether or not the WHENCLAUSETEXT column is present, I changed the SELECT query to always fetch all column from SYS.SYSTRIGGERS (using SELECT *). I also changed the calls to ResultSet getter methods so that they referenced the columns by name instead of index, which makes it easier to understand them if you don't remember the order of the columns in the SYS.SYSTRIGGERS table.

          For formatting of the WHEN clause I reused the logic that formats the triggered SQL statement. This means the WHEN clause inherits any bugs in that code, such as DERBY-6384.

          The patch adds triggers with WHEN clauses to dblook_test and updates the test canons accordingly.

          All regression tests ran cleanly with the patch.

          Show
          Knut Anders Hatlen added a comment - Attaching derby-534-13-a-dblook.diff which makes dblook show the WHEN clause for triggers that have one. It changes DB_Trigger.doTriggers() so that it looks for the WHENCLAUSETEXT column when it scans SYS.SYSTRIGGERS. Since the WHENCLAUSETEXT column is not present in soft-upgraded databases, dblook should not look for that column unless the data dictionary has been upgraded to 10.11. Instead of executing different queries against SYS.SYSTRIGGERS depending on whether or not the WHENCLAUSETEXT column is present, I changed the SELECT query to always fetch all column from SYS.SYSTRIGGERS (using SELECT * ). I also changed the calls to ResultSet getter methods so that they referenced the columns by name instead of index, which makes it easier to understand them if you don't remember the order of the columns in the SYS.SYSTRIGGERS table. For formatting of the WHEN clause I reused the logic that formats the triggered SQL statement. This means the WHEN clause inherits any bugs in that code, such as DERBY-6384 . The patch adds triggers with WHEN clauses to dblook_test and updates the test canons accordingly. All regression tests ran cleanly with the patch.
          Hide
          ASF subversion and git services added a comment -

          Commit 1534988 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1534988 ]

          DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

          Add dblook support for triggers with a WHEN clause.

          Show
          ASF subversion and git services added a comment - Commit 1534988 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1534988 ] DERBY-534 : Support use of the WHEN clause in CREATE TRIGGER statements Add dblook support for triggers with a WHEN clause.
          Hide
          Knut Anders Hatlen added a comment -

          The patch with dblook support has been committed, and that was the last piece of missing functionality, so I think the work on this issue is more or less done. I'm still looking into whether the upgrade logic added to the TriggerDescriptor class is really necessary (I'm currently leaning towards that it's not needed, and will probably post a patch that removes it). There are also some known bugs that are tracked in separate JIRA issues, but none of them are specific to the WHEN clause (that is, they can also be reproduced without a WHEN clause).

          I'm attaching an updated functional specification where some more information has been added to the SQL Standard and Documentation sections.

          I've logged DERBY-6390 for documentation.

          Show
          Knut Anders Hatlen added a comment - The patch with dblook support has been committed, and that was the last piece of missing functionality, so I think the work on this issue is more or less done. I'm still looking into whether the upgrade logic added to the TriggerDescriptor class is really necessary (I'm currently leaning towards that it's not needed, and will probably post a patch that removes it). There are also some known bugs that are tracked in separate JIRA issues, but none of them are specific to the WHEN clause (that is, they can also be reproduced without a WHEN clause). I'm attaching an updated functional specification where some more information has been added to the SQL Standard and Documentation sections. I've logged DERBY-6390 for documentation.
          Hide
          Knut Anders Hatlen added a comment -

          I've convinced myself that TriggerDescriptor instances are only written to disk as part of TriggerInfo instances in stored prepared statements. Since Derby always clears all SPSs on version change, one version of Derby will never try to read an instance written by another version of Derby. (TriggerInfo even has a comment that says "since this is something that is used in stored prepared statements, it is ok to change it if you make sure that stored prepared statements are invalidated across releases.")

          derby-534-14-a-remove-formatable-upgrade.diff removes the code that makes TriggerInfo and TriggerDescriptor instances compatible across versions.

          Note that the patch only changes the upgrade logic for trigger descriptors written using the Formatable interface. The logic that adds a column to the SYS.SYSTRIGGERS table on upgrade is not touched by the patch.

          All regression tests, including all upgrade tests, ran cleanly with the patch.

          Show
          Knut Anders Hatlen added a comment - I've convinced myself that TriggerDescriptor instances are only written to disk as part of TriggerInfo instances in stored prepared statements. Since Derby always clears all SPSs on version change, one version of Derby will never try to read an instance written by another version of Derby. (TriggerInfo even has a comment that says "since this is something that is used in stored prepared statements, it is ok to change it if you make sure that stored prepared statements are invalidated across releases.") derby-534-14-a-remove-formatable-upgrade.diff removes the code that makes TriggerInfo and TriggerDescriptor instances compatible across versions. Note that the patch only changes the upgrade logic for trigger descriptors written using the Formatable interface. The logic that adds a column to the SYS.SYSTRIGGERS table on upgrade is not touched by the patch. All regression tests, including all upgrade tests, ran cleanly with the patch.
          Hide
          ASF subversion and git services added a comment -

          Commit 1535654 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1535654 ]

          DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

          Remove logic in the readExternal() and writeExternal() methods of
          TriggerInfo and TriggerDescriptor that was originally put there for
          compatibility between different Derby versions. Since these objects
          are only persisted as part of a stored prepared statement, and Derby
          always clears all stored prepared statements on version change, there
          is no requirement that TriggerInfo and TriggerDescriptor instances
          written by one Derby version must be possible to read by other Derby
          versions.

          Show
          ASF subversion and git services added a comment - Commit 1535654 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1535654 ] DERBY-534 : Support use of the WHEN clause in CREATE TRIGGER statements Remove logic in the readExternal() and writeExternal() methods of TriggerInfo and TriggerDescriptor that was originally put there for compatibility between different Derby versions. Since these objects are only persisted as part of a stored prepared statement, and Derby always clears all stored prepared statements on version change, there is no requirement that TriggerInfo and TriggerDescriptor instances written by one Derby version must be possible to read by other Derby versions.
          Hide
          Rick Hillegas added a comment -

          I have written some tests of the WHEN clause and it seems to behave as specified. I have verified that the WHEN clause can not be used if you soft-upgrade to 10.11. Additional tests are indicated in the script attached at the end of this comment.

          The WHEN clause widens the weird behavior of the DROP FUNCTION command. That behavior is neither CASCADE nor RESTRICT. Functions can be mentioned in a triggered action. If you drop the function, you'll get a confusing error the next time you execute a statement which fires the trigger. The message complains about a missing function. Now this weird error message can arise because functions can be mentioned in WHEN clauses too. I think it's fine that the weird behavior is consistent regardless of where the function is mentioned in the trigger definition. The real pity is that DROP FUNCTION is neither CASCADE nor RESTRICT.

          connect 'jdbc:derby:memory:db;create=true';

          create table t1( a int, b generated always as ( -a ) );
          create table t2( a int );

          create procedure dumpStack()
          language java parameter style java no sql
          external name 'java.lang.Thread.dumpStack';

          create function squareRoot( val double ) returns double
          language java parameter style java no sql
          external name 'java.lang.Math.sqrt';

          create derby aggregate mode_05 for int
          external name 'org.apache.derbyTesting.functionTests.tests.lang.ModeAggregate';

          – fails as expected because statement triggers can't reference columns
          create trigger t1_trig
          after insert on t1
          referencing new as new
          when ( squareRoot( new.a ) < 3 )
          insert into t2( a ) values ( 0 );

          – fails because aggregates not allowed in WHEN clauses
          create trigger t1_trig
          after insert on t1
          referencing new as new
          for each row
          when ( max( new.a ) between 0 and 3 )
          insert into t2( a ) values ( new.a );
          create trigger t1_trig
          after insert on t1
          referencing new as new
          for each row
          when ( mode_05( new.a ) between 0 and 3 )
          insert into t2( a ) values ( new.a );

          – fails because of reference to generated column
          create trigger t1_trig
          no cascade before insert on t1
          referencing new as new
          for each row
          when ( new.a > new.b )
          call dumpStack();

          create trigger t1_trig100
          after insert on t1
          referencing new as new
          for each row
          when ( new.a in ( values ( 1 ), ( 10 ) ) )
          insert into t2( a ) values ( new.a );

          create trigger t1_trig101
          after insert on t1
          referencing new as new
          for each row
          when ( new.a in ( select a from t2 ) )
          insert into t2( a ) values ( new.a );

          create trigger t1_trig102
          after insert on t1
          referencing new as new
          for each row
          when ( new.a in ( select a from t1 ) )
          insert into t2( a ) values ( new.a );

          create trigger t1_trig104
          no cascade before update on t1
          referencing old as old new as new
          for each row
          when ( new.a in ( select a from t1 where a = new.a ) )
          call dumpStack();

          create trigger t1_square_root
          after insert on t1
          referencing new as new
          for each row
          when ( squareRoot( new.a ) between 0 and 3 )
          insert into t2( a ) values ( new.a );

          insert into t1( a ) values ( 1 ), ( 10 );

          select * from t1;
          select * from t2;

          – dumps one stack trace as expected
          update t1 set a = 10 * a;

          select triggername from sys.systriggers trig, sys.systables tab
          where tab.tablename = 'T1' and tab.tableid = trig.tableid;

          drop function squareRoot;

          select triggername from sys.systriggers trig, sys.systables tab
          where tab.tablename = 'T1' and tab.tableid = trig.tableid;

          insert into t1( a ) values ( 2 );

          Show
          Rick Hillegas added a comment - I have written some tests of the WHEN clause and it seems to behave as specified. I have verified that the WHEN clause can not be used if you soft-upgrade to 10.11. Additional tests are indicated in the script attached at the end of this comment. The WHEN clause widens the weird behavior of the DROP FUNCTION command. That behavior is neither CASCADE nor RESTRICT. Functions can be mentioned in a triggered action. If you drop the function, you'll get a confusing error the next time you execute a statement which fires the trigger. The message complains about a missing function. Now this weird error message can arise because functions can be mentioned in WHEN clauses too. I think it's fine that the weird behavior is consistent regardless of where the function is mentioned in the trigger definition. The real pity is that DROP FUNCTION is neither CASCADE nor RESTRICT. connect 'jdbc:derby:memory:db;create=true'; create table t1( a int, b generated always as ( -a ) ); create table t2( a int ); create procedure dumpStack() language java parameter style java no sql external name 'java.lang.Thread.dumpStack'; create function squareRoot( val double ) returns double language java parameter style java no sql external name 'java.lang.Math.sqrt'; create derby aggregate mode_05 for int external name 'org.apache.derbyTesting.functionTests.tests.lang.ModeAggregate'; – fails as expected because statement triggers can't reference columns create trigger t1_trig after insert on t1 referencing new as new when ( squareRoot( new.a ) < 3 ) insert into t2( a ) values ( 0 ); – fails because aggregates not allowed in WHEN clauses create trigger t1_trig after insert on t1 referencing new as new for each row when ( max( new.a ) between 0 and 3 ) insert into t2( a ) values ( new.a ); create trigger t1_trig after insert on t1 referencing new as new for each row when ( mode_05( new.a ) between 0 and 3 ) insert into t2( a ) values ( new.a ); – fails because of reference to generated column create trigger t1_trig no cascade before insert on t1 referencing new as new for each row when ( new.a > new.b ) call dumpStack(); create trigger t1_trig100 after insert on t1 referencing new as new for each row when ( new.a in ( values ( 1 ), ( 10 ) ) ) insert into t2( a ) values ( new.a ); create trigger t1_trig101 after insert on t1 referencing new as new for each row when ( new.a in ( select a from t2 ) ) insert into t2( a ) values ( new.a ); create trigger t1_trig102 after insert on t1 referencing new as new for each row when ( new.a in ( select a from t1 ) ) insert into t2( a ) values ( new.a ); create trigger t1_trig104 no cascade before update on t1 referencing old as old new as new for each row when ( new.a in ( select a from t1 where a = new.a ) ) call dumpStack(); create trigger t1_square_root after insert on t1 referencing new as new for each row when ( squareRoot( new.a ) between 0 and 3 ) insert into t2( a ) values ( new.a ); insert into t1( a ) values ( 1 ), ( 10 ); select * from t1; select * from t2; – dumps one stack trace as expected update t1 set a = 10 * a; select triggername from sys.systriggers trig, sys.systables tab where tab.tablename = 'T1' and tab.tableid = trig.tableid; drop function squareRoot; select triggername from sys.systriggers trig, sys.systables tab where tab.tablename = 'T1' and tab.tableid = trig.tableid; insert into t1( a ) values ( 2 );
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for testing the new syntax, Rick.

          I think the weird behaviour you're seeing with DROP FUNCTION also affects other DROP statements. For example, a similar problem with DROP TABLE is recorded in DERBY-2041. Hopefully, a fix for that issue will also handle the DROP FUNCTION case. It should be fixed, but since it is a general trigger problem, I don't intend to fix it as part of this JIRA issue.

          Note also that the weird behaviour is actually documented behaviour up to version 10.10. CREATE TRIGGER statement says:

          The triggered-SQL-statement can reference database objects other than the table upon which the trigger is declared. If any of these database objects is dropped, the trigger is invalidated. If the trigger cannot be successfully recompiled upon the next execution, the invocation throws an exception and the statement that caused it to fire will be rolled back.

          DERBY-6390 removed that paragraph from the docs on trunk so that application writers aren't led to believe that the buggy behaviour is the correct one. (It was also removed because it's not always true. For example, DROP COLUMN CASCADE does cause dependent triggers to be dropped. DROP COLUMN's handling of dependencies is a hack, though. It rebinds the trigger actions of the triggers it believe might be dependent, and cascades if the rebinding fails with one of four specific SQLStates. I hope DERBY-2041 will be fixed by making use of the dependency system in a more direct way, similar to what we do for VIEWs.)

          Show
          Knut Anders Hatlen added a comment - Thanks for testing the new syntax, Rick. I think the weird behaviour you're seeing with DROP FUNCTION also affects other DROP statements. For example, a similar problem with DROP TABLE is recorded in DERBY-2041 . Hopefully, a fix for that issue will also handle the DROP FUNCTION case. It should be fixed, but since it is a general trigger problem, I don't intend to fix it as part of this JIRA issue. Note also that the weird behaviour is actually documented behaviour up to version 10.10. CREATE TRIGGER statement says: The triggered-SQL-statement can reference database objects other than the table upon which the trigger is declared. If any of these database objects is dropped, the trigger is invalidated. If the trigger cannot be successfully recompiled upon the next execution, the invocation throws an exception and the statement that caused it to fire will be rolled back. DERBY-6390 removed that paragraph from the docs on trunk so that application writers aren't led to believe that the buggy behaviour is the correct one. (It was also removed because it's not always true. For example, DROP COLUMN CASCADE does cause dependent triggers to be dropped. DROP COLUMN's handling of dependencies is a hack, though. It rebinds the trigger actions of the triggers it believe might be dependent, and cascades if the rebinding fails with one of four specific SQLStates. I hope DERBY-2041 will be fixed by making use of the dependency system in a more direct way, similar to what we do for VIEWs.)
          Hide
          Knut Anders Hatlen added a comment -

          Attaching derby-534-15-a-more-tests.diff which adds more tests:

          • Test that the SQL text in a WHEN clause can be longer than the maximum length of a LONG VARCHAR
          • Test that a function declared as READ SQL DATA can be invoked from a WHEN clause
          • Test that it is not possible to create a function that is declared as MODIFIES SQL DATA (If it had been possible, the SQL standard says it should not be possible to invoke that function from a WHEN clause. This test case serves as a reminder to add that restriction if we ever add support for functions that modify SQL data.)
          • Test that it is not possible to call procedures from a WHEN clause
          • Test that aggregates (both built-in and user-defined) can be used in a WHEN clause
          Show
          Knut Anders Hatlen added a comment - Attaching derby-534-15-a-more-tests.diff which adds more tests: Test that the SQL text in a WHEN clause can be longer than the maximum length of a LONG VARCHAR Test that a function declared as READ SQL DATA can be invoked from a WHEN clause Test that it is not possible to create a function that is declared as MODIFIES SQL DATA (If it had been possible, the SQL standard says it should not be possible to invoke that function from a WHEN clause. This test case serves as a reminder to add that restriction if we ever add support for functions that modify SQL data.) Test that it is not possible to call procedures from a WHEN clause Test that aggregates (both built-in and user-defined) can be used in a WHEN clause
          Hide
          ASF subversion and git services added a comment -

          Commit 1540690 from Knut Anders Hatlen in branch 'code/trunk'
          [ https://svn.apache.org/r1540690 ]

          DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

          Add more tests.

          Show
          ASF subversion and git services added a comment - Commit 1540690 from Knut Anders Hatlen in branch 'code/trunk' [ https://svn.apache.org/r1540690 ] DERBY-534 : Support use of the WHEN clause in CREATE TRIGGER statements Add more tests.
          Hide
          Knut Anders Hatlen added a comment -

          No more work is planned as part of this issue, so I'm marking it as resolved. If problems are found, please file separate JIRA issues to track them.

          Show
          Knut Anders Hatlen added a comment - No more work is planned as part of this issue, so I'm marking it as resolved. If problems are found, please file separate JIRA issues to track them.

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Kristian Waagan
            • Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development