Derby
  1. Derby
  2. DERBY-4513

Forbid NEXT VALUE FOR clause in certain contexts

    Details

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

      Description

      This is part of the work needed to implement ANSI/ISO sequences. The functional spec attached to DERBY-712 lists various situations in which the NEXT VALUE FOR clause is illegal. These include:

      • CASE expression
      • WHERE clause
      • ORDER BY clause
      • AGGREGATE expression
      • WINDOW function
      • DISTINCT select list

      In addition, I propose that we make it illegal for a statement to have more than one NEXT VALUE FOR clause on the same sequence generator. This is a tighter restriction than the ANSI/ISO standard calls for. The standard requires that if two columns in a row are populated by NEXT VALUE FOR clauses on the same sequence, then the values should be the same. I don't feel confident that I could track down all of the cases which could give rise to this situation--so I propose to limit the number of NEXT VALUE FOR clauses on a given sequence generator to just 1.

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Hi Rick,

          I'm fine with extra restrictions in the initial implementation, but I'm afraid I didn't understand exactly what limitation you're suggesting. Are you suggesting to limit the number of NEXT VALUE FOR clauses per row or per statement?

          Show
          Knut Anders Hatlen added a comment - Hi Rick, I'm fine with extra restrictions in the initial implementation, but I'm afraid I didn't understand exactly what limitation you're suggesting. Are you suggesting to limit the number of NEXT VALUE FOR clauses per row or per statement?
          Hide
          Rick Hillegas added a comment -

          Hi Knut. I'm suggesting that we allow one NEXT VALUE FOR clause per sequence per statement. Thanks.

          Show
          Rick Hillegas added a comment - Hi Knut. I'm suggesting that we allow one NEXT VALUE FOR clause per sequence per statement. Thanks.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for clarifying, Rick.

          So both of these (presumably SQL compliant) statements will be rejected?

          SELECT NEXT VALUE FOR S, NEXT VALUE FOR S FROM T – two clauses per row

          VALUES (NEXT VALUE FOR S), (NEXT VALUE FOR S) – two clauses in the statement, but only one per row

          The VALUES case is probably more useful than the SELECT case, especially as part of an INSERT statement. The restriction would basically prevent us from using a sequence generator in a multi-row insert. That's probably fine in the initial implementation, though, and anyone could scratch that itch later if they want to.

          Show
          Knut Anders Hatlen added a comment - Thanks for clarifying, Rick. So both of these (presumably SQL compliant) statements will be rejected? SELECT NEXT VALUE FOR S, NEXT VALUE FOR S FROM T – two clauses per row VALUES (NEXT VALUE FOR S), (NEXT VALUE FOR S) – two clauses in the statement, but only one per row The VALUES case is probably more useful than the SELECT case, especially as part of an INSERT statement. The restriction would basically prevent us from using a sequence generator in a multi-row insert. That's probably fine in the initial implementation, though, and anyone could scratch that itch later if they want to.
          Hide
          Rick Hillegas added a comment -

          Hi Knut,

          Yes, that's an accurate summary of the restriction I'm proposing. Note that there is an ungainly workaround for the insert case you mention. Instead of a values clause, you can use a subquery which selects from sysibm.sysdummy1:

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

          create table t( a int, b int );
          create sequence seq1;

          insert into t( a, b )
          select s.c, s.c
          from ( select next value for seq1 as c from sysibm.sysdummy1 ) as s;

          select * from t;

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Knut, Yes, that's an accurate summary of the restriction I'm proposing. Note that there is an ungainly workaround for the insert case you mention. Instead of a values clause, you can use a subquery which selects from sysibm.sysdummy1: connect 'jdbc:derby:memory:dummy;create=true'; create table t( a int, b int ); create sequence seq1; insert into t( a, b ) select s.c, s.c from ( select next value for seq1 as c from sysibm.sysdummy1 ) as s; select * from t; Thanks, -Rick
          Hide
          Rick Hillegas added a comment -

          Attaching derby-4513_01-aa-illegalContexts.diff. This patch prevents you from using a NEXT VALUE FOR clause in the following situations:

          o WHERE/HAVING/ON clauses

          o Aggregates and GROUP BY expressions

          o DISTINCT and ORDER BY expressions

          o CASE expressions

          o CHECK constraints and generation clauses

          The limitations imposed by this patch are more restrictive than what the SQL Standard allows:

          o Only one NEXT VALUE FOR expression is allowed per sequence per statement.

          o NEXT VALUE FOR is not allowed in any statement which has a DISTINCT or ORDER BY expression.

          When these limitations are checked in, I will create another JIRA to track the fact that Derby is more restrictive than the Standard allows. We can consider relaxing limitations which users find onerous.

          Touches the following files

          -------------

          M java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
          M java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java

          The compiler context now tracks which sequences are mentioned in the statement.

          -------------

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

          Statements which have DISTINCT and ORDER BY expressions are marked so that the bind() logic can reject NEXT VALUE FOR clauses.

          -------------

          M java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
          M java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
          M java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java
          M java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java
          M java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
          M java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
          M java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java

          Bind() logic to reject NEXT VALUE FOR in illegal situations.

          -------------

          M java/engine/org/apache/derby/loc/messages.xml
          M java/shared/org/apache/derby/shared/common/reference/SQLState.java

          New error messages.

          -------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java

          Regression tests.

          -------------

          Show
          Rick Hillegas added a comment - Attaching derby-4513_01-aa-illegalContexts.diff. This patch prevents you from using a NEXT VALUE FOR clause in the following situations: o WHERE/HAVING/ON clauses o Aggregates and GROUP BY expressions o DISTINCT and ORDER BY expressions o CASE expressions o CHECK constraints and generation clauses The limitations imposed by this patch are more restrictive than what the SQL Standard allows: o Only one NEXT VALUE FOR expression is allowed per sequence per statement. o NEXT VALUE FOR is not allowed in any statement which has a DISTINCT or ORDER BY expression. When these limitations are checked in, I will create another JIRA to track the fact that Derby is more restrictive than the Standard allows. We can consider relaxing limitations which users find onerous. Touches the following files ------------- M java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java M java/engine/org/apache/derby/impl/sql/compile/CompilerContextImpl.java The compiler context now tracks which sequences are mentioned in the statement. ------------- M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Statements which have DISTINCT and ORDER BY expressions are marked so that the bind() logic can reject NEXT VALUE FOR clauses. ------------- M java/engine/org/apache/derby/impl/sql/compile/SelectNode.java M java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java M java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java M java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java M java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java M java/engine/org/apache/derby/impl/sql/compile/JoinNode.java M java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java Bind() logic to reject NEXT VALUE FOR in illegal situations. ------------- M java/engine/org/apache/derby/loc/messages.xml M java/shared/org/apache/derby/shared/common/reference/SQLState.java New error messages. ------------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java Regression tests. -------------
          Hide
          Knut Anders Hatlen added a comment -

          I downloaded and applied the patch, but I still seem to be able to use NEXT VALUE FOR in WHERE clauses:

          ij version 10.6
          ij> connect 'jdbc:derby:seqdb;create=true';
          ij> create sequence s;
          0 rows inserted/updated/deleted
          ij> select * from sysibm.sysdummy1 where (next value for s) < 4;
          IBM&


          Y

          1 row selected

          What am I missing?

          Show
          Knut Anders Hatlen added a comment - I downloaded and applied the patch, but I still seem to be able to use NEXT VALUE FOR in WHERE clauses: ij version 10.6 ij> connect 'jdbc:derby:seqdb;create=true'; ij> create sequence s; 0 rows inserted/updated/deleted ij> select * from sysibm.sysdummy1 where (next value for s) < 4; IBM& Y 1 row selected What am I missing?
          Hide
          Rick Hillegas added a comment -

          Thanks for looking at the patch, Knut. I don't know what's going on. I did notice merge errors when I resynced this patch client with the trunk. I've brought the patch up-to-date with the head of the trunk and am attaching a merged revision: derby-4513_01-ab-illegalContexts.diff

          Here's what I see with this patch when I run your experiment:

          ij version 10.6
          ij> connect 'jdbc:derby:memory:dummy;create=true';
          ij> create sequence s;
          0 rows inserted/updated/deleted
          ij> select * from sysibm.sysdummy1 where (next value for s) < 4;
          ERROR 42XAH: A NEXT VALUE FOR expression may not appear in many contexts, including WHERE, ON, HAVING, ORDER BY, DISTINCT, CASE, GENERATION, and AGGREGATE clauses as well as WINDOW functions and CHECK constraints.

          Let me know if this still doesn't fix the problem. Thanks.

          Show
          Rick Hillegas added a comment - Thanks for looking at the patch, Knut. I don't know what's going on. I did notice merge errors when I resynced this patch client with the trunk. I've brought the patch up-to-date with the head of the trunk and am attaching a merged revision: derby-4513_01-ab-illegalContexts.diff Here's what I see with this patch when I run your experiment: ij version 10.6 ij> connect 'jdbc:derby:memory:dummy;create=true'; ij> create sequence s; 0 rows inserted/updated/deleted ij> select * from sysibm.sysdummy1 where (next value for s) < 4; ERROR 42XAH: A NEXT VALUE FOR expression may not appear in many contexts, including WHERE, ON, HAVING, ORDER BY, DISTINCT, CASE, GENERATION, and AGGREGATE clauses as well as WINDOW functions and CHECK constraints. Let me know if this still doesn't fix the problem. Thanks.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks! It works as expected now.

          Show
          Knut Anders Hatlen added a comment - Thanks! It works as expected now.
          Hide
          Rick Hillegas added a comment -

          Thanks, Knut. Committed derby-4513_01-ab-illegalContexts.diff at subversion revision 908627.

          Show
          Rick Hillegas added a comment - Thanks, Knut. Committed derby-4513_01-ab-illegalContexts.diff at subversion revision 908627.

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development