Derby
  1. Derby
  2. DERBY-3

Identity column not filled consecutively with "insert ... select distinct"

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Duplicate
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Derby Snapshot svnversion 46005
      or Cloudscape 10.0 GA
      Derby 10.2beta

      Description

      When an "insert ... select distinct" is used to fill a table with an identity column, gaps occur in the identity column. The caps correspond to the rows of the source table that have been suppressed with "distinct".

      Example:

      create table temp1 (
      s varchar(10));
      insert into temp1 values 'a','a','a','b','c','c','c','d';

      create table temp2 (
      i integer not null
      generated always as identity
      primary key,
      s varchar(10));

      insert into temp2 (s)
      select distinct s from temp1;
      select * from temp2;

      output:
      1 a
      4 b
      5 c
      8 d

        Issue Links

          Activity

          Gavin made changes -
          Workflow jira [ 37476 ] Default workflow, editable Closed status [ 12801239 ]
          Kathey Marsden made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Knut Anders Hatlen made changes -
          Status Reopened [ 4 ] Resolved [ 5 ]
          Resolution Duplicate [ 3 ]
          Knut Anders Hatlen made changes -
          Link This issue duplicates DERBY-4442 [ DERBY-4442 ]
          Dag H. Wanvik made changes -
          Link This issue relates to DERBY-4442 [ DERBY-4442 ]
          Kristian Waagan made changes -
          Resolution Invalid [ 6 ]
          Status Resolved [ 5 ] Reopened [ 4 ]
          Kristian Waagan made changes -
          Environment Derby Snapshot svnversion 46005
          or Cloudscape 10.0 GA
          Derby Snapshot svnversion 46005
          or Cloudscape 10.0 GA
          Derby 10.2beta
          Priority Major [ 3 ] Minor [ 4 ]
          Affects Version/s 10.2.0.0 [ 11187 ]
          Description When an "insert ... select distinct" is used to fill a table with an identity column, gaps occur in the identity column. The caps correspond to the rows of the source table that have been suppressed with "distinct".

          Example:

          create table temp1 (
             s varchar(10));
          insert into temp1 values 'a','a','a','b','c','c','c','d';

          create table temp2 (
             i integer not null
                generated always as identity
                primary key,
             s varchar(10));

          insert into temp2 (s)
             select distinct s from temp1;
          select * from temp2;

          output:
           1 a
           4 b
           5 c
           8 d
          When an "insert ... select distinct" is used to fill a table with an identity column, gaps occur in the identity column. The caps correspond to the rows of the source table that have been suppressed with "distinct".

          Example:

          create table temp1 (
             s varchar(10));
          insert into temp1 values 'a','a','a','b','c','c','c','d';

          create table temp2 (
             i integer not null
                generated always as identity
                primary key,
             s varchar(10));

          insert into temp2 (s)
             select distinct s from temp1;
          select * from temp2;

          output:
           1 a
           4 b
           5 c
           8 d
          Type Bug [ 1 ] Improvement [ 4 ]
          Kathey Marsden made changes -
          Resolution Invalid [ 6 ]
          Status Open [ 1 ] Resolved [ 5 ]
          Satheesh Bandaram made changes -
          Field Original Value New Value
          Component/s SQL [ 11408 ]
          Christian d'Heureuse created issue -

            People

            • Assignee:
              Unassigned
              Reporter:
              Christian d'Heureuse
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development