Derby
  1. Derby
  2. DERBY-4803

Sequences do not work in INSERT/SELECT

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.6.1.0
    • Fix Version/s: 10.7.1.1
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Mac OS X, Derby Network Server 10.6.1.0
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached

      Description

      Using sequence in SELECT works fine whereas the same SELECT query used in INSERT/SELECT results in "The statement references the following sequence more than once" error. This happens even though the SELECT in question returns exactly 1 row of data.

      The Reference Manual states 1. " NEXT VALUE FOR expression may occur in the following places: SELECT statement: As part of the expression defining a returned column in a SELECT list" and 2. " NEXT VALUE expression may not appear in any of these situations: CASE expression, WHERE clause,
      ORDER BY clause, Aggregate expression, ROW_NUMBER function, DISTINCT select list".
      Nowhere a restriction on INSERT/SELECT is mentioned. Additionally, other databases (i.e. Oracle) support use of sequences in INSERT/SELECT.

      Therefore, I consider it a bug.

        Issue Links

          Activity

          Hide
          Rick Hillegas added a comment -

          The following script shows this behavior:

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

          create sequence sequence1;

          create table t1( a int );
          create table t2( a int, b int );

          insert into t1( a ) values ( 1 );

          insert into t2
          select next value for sequence1, a from t1;

          Show
          Rick Hillegas added a comment - The following script shows this behavior: connect 'jdbc:derby:memory:db;create=true'; create sequence sequence1; create table t1( a int ); create table t2( a int, b int ); insert into t1( a ) values ( 1 ); insert into t2 select next value for sequence1, a from t1;
          Hide
          Rick Hillegas added a comment -

          Attaching derby-4803-01-aa-simpleInsertSelect.diff. This fixes the bug for a simple insert/select case. The pre-existing test cases in SequenceTest pass cleanly. I will run the full regression test suite.

          The problem is that the NextSequenceNode is needlessly re-bound for INSERT...SELECT statements. On the second attempt to bind the statement, we discover that the sequence has already been seen and this triggers the overly conservative check for illegal conditions discussed in DERBY-4513. The fix is to short-circuit the second, needless binding of the NextSequenceNode.

          DK, could you post the INSERT statement which disclosed this bug? I would like to verify whether this patch fixes your problem or whether I have merely fixed a related test case. Thanks.

          Touches the following files:

          ------

          M java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java

          Short-circuit redundant bindings of NEXT VALUE FOR clauses.

          ------

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

          Test-case for this bug.

          Show
          Rick Hillegas added a comment - Attaching derby-4803-01-aa-simpleInsertSelect.diff. This fixes the bug for a simple insert/select case. The pre-existing test cases in SequenceTest pass cleanly. I will run the full regression test suite. The problem is that the NextSequenceNode is needlessly re-bound for INSERT...SELECT statements. On the second attempt to bind the statement, we discover that the sequence has already been seen and this triggers the overly conservative check for illegal conditions discussed in DERBY-4513 . The fix is to short-circuit the second, needless binding of the NextSequenceNode. DK, could you post the INSERT statement which disclosed this bug? I would like to verify whether this patch fixes your problem or whether I have merely fixed a related test case. Thanks. Touches the following files: ------ M java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java Short-circuit redundant bindings of NEXT VALUE FOR clauses. ------ M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java Test-case for this bug.
          Hide
          DK added a comment -

          Rick,

          that was quick!

          See attached test case. It fails in 10.6.1.0.

          Show
          DK added a comment - Rick, that was quick! See attached test case. It fails in 10.6.1.0.
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me. Committed derby-4803-01-aa-simpleInsertSelect.diff at subversion revision 999908.

          Hi DK,

          Your script now passes. I had to edit your script because it was trying to insert integers into varchar columns. Those problems must have been masked by this bug. Here is the modified script which now runs:

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

          CREATE SEQUENCE CART_TXN_ID_SEQ START WITH 1;
          CREATE TABLE TABLE_1
          (
          DOCNO VARCHAR(60) NOT NULL
          , OTHER_DOCNO VARCHAR(60)
          , CAN VARCHAR(7) NOT NULL
          , ICD VARCHAR(6)
          , FY VARCHAR(4)
          , SGL_DEBIT_AMT DECIMAL(12, 2)
          , FACTS_TP_CODE VARCHAR(10)
          , AUTHORITY_DOCNO VARCHAR(60)
          , ACTION_CODE VARCHAR(20)
          , CART_TXN_ID INT
          , CART_ID VARCHAR(50)
          , LAST_UPDATED_BY_USER_ID VARCHAR(20)
          , CREATED_DATE TIMESTAMP
          , CONSTRAINT TABLE_1 PRIMARY KEY
          (
          CART_TXN_ID
          )
          );

          CREATE TABLE TABLE_2
          (
          PROJECT_BUDGET CHAR(1)
          , ACS_CAN CHAR(7)
          , DIRECT_REIMB_FLAG CHAR(1)
          , INSTITUTE CHAR(25)
          , PROJECT_NBR CHAR(6)
          , CURRENT_IND CHAR(1)
          );

          insert into TABLE_2(project_budget, acs_can, direct_reimb_flag, institute, project_nbr, current_ind)
          values('P', '1234567', 'R', 'XYZ', '123456', 'C');

          INSERT INTO
          TABLE_1 (
          cart_id,
          can,
          docno,
          fy,
          icd,
          other_docno,
          facts_tp_code,
          authority_docno,
          SGL_DEBIT_AMT,
          action_code,
          cart_txn_id,
          last_updated_by_user_id,
          created_date
          ) SELECT
          'Abc',
          '1234567',
          'Y21',
          '2010',
          TRIM(acs.institute),
          'Y3123456',
          '123',
          'Y3123456',
          100,
          'Action',
          NEXT VALUE FOR CART_TXN_ID_SEQ,
          'Qwerty',
          CURRENT_TIMESTAMP
          FROM
          table_2 acs
          WHERE
          acs.acs_can = '1234567';

          select * from table_1;

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me. Committed derby-4803-01-aa-simpleInsertSelect.diff at subversion revision 999908. Hi DK, Your script now passes. I had to edit your script because it was trying to insert integers into varchar columns. Those problems must have been masked by this bug. Here is the modified script which now runs: connect 'jdbc:derby:memory:db;create=true'; CREATE SEQUENCE CART_TXN_ID_SEQ START WITH 1; CREATE TABLE TABLE_1 ( DOCNO VARCHAR(60) NOT NULL , OTHER_DOCNO VARCHAR(60) , CAN VARCHAR(7) NOT NULL , ICD VARCHAR(6) , FY VARCHAR(4) , SGL_DEBIT_AMT DECIMAL(12, 2) , FACTS_TP_CODE VARCHAR(10) , AUTHORITY_DOCNO VARCHAR(60) , ACTION_CODE VARCHAR(20) , CART_TXN_ID INT , CART_ID VARCHAR(50) , LAST_UPDATED_BY_USER_ID VARCHAR(20) , CREATED_DATE TIMESTAMP , CONSTRAINT TABLE_1 PRIMARY KEY ( CART_TXN_ID ) ); CREATE TABLE TABLE_2 ( PROJECT_BUDGET CHAR(1) , ACS_CAN CHAR(7) , DIRECT_REIMB_FLAG CHAR(1) , INSTITUTE CHAR(25) , PROJECT_NBR CHAR(6) , CURRENT_IND CHAR(1) ); insert into TABLE_2(project_budget, acs_can, direct_reimb_flag, institute, project_nbr, current_ind) values('P', '1234567', 'R', 'XYZ', '123456', 'C'); INSERT INTO TABLE_1 ( cart_id, can, docno, fy, icd, other_docno, facts_tp_code, authority_docno, SGL_DEBIT_AMT, action_code, cart_txn_id, last_updated_by_user_id, created_date ) SELECT 'Abc', '1234567', 'Y21', '2010', TRIM(acs.institute), 'Y3123456', '123', 'Y3123456', 100, 'Action', NEXT VALUE FOR CART_TXN_ID_SEQ, 'Qwerty', CURRENT_TIMESTAMP FROM table_2 acs WHERE acs.acs_can = '1234567'; select * from table_1;
          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development