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

          No work has yet been logged on this 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