Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-3

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

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: 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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                chdh@inventec.ch Christian d'Heureuse
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: