Derby
  1. Derby
  2. DERBY-3570

Implement DETERMINISTIC keyword for procedures and functions

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.5.1.1
    • Component/s: SQL
    • Labels:
      None

      Description

      We will need DETERMINISTIC functions when we implement generated columns (DERBY-481). The syntax and behavior of the DETERMINISTIC keyword are defined in the functional spec attached to DERBY-481.

        Issue Links

          Activity

          Hide
          Rick Hillegas added a comment -

          Thanks, Dag. I corrected the javadoc problem with subversion revision 701425.

          Show
          Rick Hillegas added a comment - Thanks, Dag. I corrected the javadoc problem with subversion revision 701425.
          Hide
          Dag H. Wanvik added a comment -

          Patch derby-3570-02-aa-grammar.diff looks good, found only this little Javadoc
          error:

          • <p>...
          • </p>
            *
          • </p>
            ____^
          • Also test that, by default, user-defined routines are created as NOT DETERMINISTIC.
          • </p>

          before test case test_001_determinism_of_stored_system_routines.

          Show
          Dag H. Wanvik added a comment - Patch derby-3570-02-aa-grammar.diff looks good, found only this little Javadoc error: <p>... </p> * </p> ____^ Also test that, by default, user-defined routines are created as NOT DETERMINISTIC. </p> before test case test_001_determinism_of_stored_system_routines.
          Hide
          Rick Hillegas added a comment -

          Committed derby-3570-02-aa-grammar.diff at subversion revision 701367.

          Show
          Rick Hillegas added a comment - Committed derby-3570-02-aa-grammar.diff at subversion revision 701367.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-3570-02-aa-grammar.diff. This patch adds the DETERMINISTIC clause to the CREATE FUNCTION/PROCEDURE statements. Touches the following files:

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

          Adds a new non-reserved keyword, DETERMINISTIC. Adds new clauses to the CREATE FUNCTION/PROCEDURE statements.

          M java/engine/org/apache/derby/catalog/types/RoutineAliasInfo.java

          Changes the toString() method to emit a DETERMINISTIC clause when the routine is deterministic. This, in turn, causes the dblook output to faithfully reproduce the DDL for deterministic routines.

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java

          Adds positive and negative tests for the new DDL.

          M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_5.java

          Verifies that the new clauses are recognized only after hard upgrade to 10.5.

          M java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql

          Adds routines with DETERMINISTIC and NOT DETERMINISTIC clauses to verify that dblook faithfully emits DDL for these routines.

          M java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net.out
          M java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net_territory.out
          M java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
          M java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
          M java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
          M java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out

          Canons accompanying the new ddl added to dblook_makeDB.sql.

          Show
          Rick Hillegas added a comment - Attaching derby-3570-02-aa-grammar.diff. This patch adds the DETERMINISTIC clause to the CREATE FUNCTION/PROCEDURE statements. Touches the following files: M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Adds a new non-reserved keyword, DETERMINISTIC. Adds new clauses to the CREATE FUNCTION/PROCEDURE statements. M java/engine/org/apache/derby/catalog/types/RoutineAliasInfo.java Changes the toString() method to emit a DETERMINISTIC clause when the routine is deterministic. This, in turn, causes the dblook output to faithfully reproduce the DDL for deterministic routines. M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java Adds positive and negative tests for the new DDL. M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_5.java Verifies that the new clauses are recognized only after hard upgrade to 10.5. M java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql Adds routines with DETERMINISTIC and NOT DETERMINISTIC clauses to verify that dblook faithfully emits DDL for these routines. M java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net.out M java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net_territory.out M java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out M java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out M java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out M java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out Canons accompanying the new ddl added to dblook_makeDB.sql.
          Hide
          Rick Hillegas added a comment -

          Committed derby-3570-01-ab-introduceDeterminismField.diff at subversion revision 699009. At this point I believe that Dan and I can work independently on this issue. That is, I can add the DETERMINISITIC keyword to the parser while Dan restructures the innards of RoleAliasInfo.

          Show
          Rick Hillegas added a comment - Committed derby-3570-01-ab-introduceDeterminismField.diff at subversion revision 699009. At this point I believe that Dan and I can work independently on this issue. That is, I can add the DETERMINISITIC keyword to the parser while Dan restructures the innards of RoleAliasInfo.
          Hide
          Rick Hillegas added a comment -

          Thanks for the feedback Knut and Dag. Attaching derby-3570-01-ab-introduceDeterminismField.diff. This changes the previous patch as follows:

          1) Fixes the comments on the sqlOptions field as Dag recommended.

          A couple other issues:

          2) It's true that the SQL modes in sqlOptions could be described by only 2 bits. I gave a couple other bits to that field to account for future expansion.

          3) I do intend to add upgrade tests for this work. They will be easier to add once I introduce some more language. The crude checks which I perform in GeneratedColumnTest right now can't be performed in the upgrade tests easily because I get a ClassCastException when I try to retrieve a RoutineAliasDescriptor out of a down-rev database.

          Show
          Rick Hillegas added a comment - Thanks for the feedback Knut and Dag. Attaching derby-3570-01-ab-introduceDeterminismField.diff. This changes the previous patch as follows: 1) Fixes the comments on the sqlOptions field as Dag recommended. A couple other issues: 2) It's true that the SQL modes in sqlOptions could be described by only 2 bits. I gave a couple other bits to that field to account for future expansion. 3) I do intend to add upgrade tests for this work. They will be easier to add once I introduce some more language. The crude checks which I perform in GeneratedColumnTest right now can't be performed in the upgrade tests easily because I get a ClassCastException when I try to retrieve a RoutineAliasDescriptor out of a down-rev database.
          Hide
          Dag H. Wanvik added a comment -

          In RoutineAliasInfo, you set aside 4 bits for sqlAllowed, but isn't that information encoded in 2 bits
          (int values 0--3)? Also, the legend says bits 0-4; I guess you mean 1-4 (or 0-3) since you mask with 0x10)?

          Show
          Dag H. Wanvik added a comment - In RoutineAliasInfo, you set aside 4 bits for sqlAllowed, but isn't that information encoded in 2 bits (int values 0--3)? Also, the legend says bits 0-4; I guess you mean 1-4 (or 0-3) since you mask with 0x10)?
          Hide
          Knut Anders Hatlen added a comment -

          I haven't tested the patch, but it looks good to me. Since it changes the stored format of the routines slightly, it may be a good idea to add a test case in the upgrade tests to verify that a routine created on Derby 10.4 works and is flagged as non-deterministic after an upgrade to Derby>=10.5.

          Show
          Knut Anders Hatlen added a comment - I haven't tested the patch, but it looks good to me. Since it changes the stored format of the routines slightly, it may be a good idea to add a test case in the upgrade tests to verify that a routine created on Derby 10.4 works and is flagged as non-deterministic after an upgrade to Derby>=10.5.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-3570-01-aa-introduceDeterminismField.diff. This allocates a bit in RoutineAliasInfo so that we can record whether a routine is deterministic. If this patch seems ok and is checked in, then the actual persistent representation of this information can be changed. I believe that Dan expressed an interest in reworking this (see the comments on the related DERBY-481 issue). Regression tests run cleanly for me. Touches the following files:

          M java/engine/org/apache/derby/catalog/types/RoutineAliasInfo.java

          Allocates a bit out of the sqlAllowed field in order to record whether a routine is deterministic and changes the constructors to allow callers to declare whether a routine is deterministic. To rework the internal bits of this class, just look for the field sqlOptions.

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

          Uses the new determinism-aware constructors of RoutineAliasInfo. All system procedures are created as non-deterministic. All system functions are created as deterministic except for RANDOM and RAND.

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

          All user-created routines are marked as the default right now, that is, as non-deterministic.

          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

          A small test which verifies that, by default, user-created routines are non-deterministic and that all system procedures are non-deterministic.

          Show
          Rick Hillegas added a comment - Attaching derby-3570-01-aa-introduceDeterminismField.diff. This allocates a bit in RoutineAliasInfo so that we can record whether a routine is deterministic. If this patch seems ok and is checked in, then the actual persistent representation of this information can be changed. I believe that Dan expressed an interest in reworking this (see the comments on the related DERBY-481 issue). Regression tests run cleanly for me. Touches the following files: M java/engine/org/apache/derby/catalog/types/RoutineAliasInfo.java Allocates a bit out of the sqlAllowed field in order to record whether a routine is deterministic and changes the constructors to allow callers to declare whether a routine is deterministic. To rework the internal bits of this class, just look for the field sqlOptions. M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Uses the new determinism-aware constructors of RoutineAliasInfo. All system procedures are created as non-deterministic. All system functions are created as deterministic except for RANDOM and RAND. M java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj All user-created routines are marked as the default right now, that is, as non-deterministic. A java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java A small test which verifies that, by default, user-created routines are non-deterministic and that all system procedures are non-deterministic.

            People

            • Assignee:
              Rick Hillegas
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development