[DERBY-3] Identity column not filled consecutively with "insert ... select distinct"

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Duplicate
• Affects Version/s: 10.2.1.6
• Component/s: SQL
• Environment: Derby Snapshot svnversion 46005; or Cloudscape 10.0 GA; Derby 10.2beta
• Assignee: Unassigned
• Reporter: Christian d'Heureuse
• Created: 27/Sep/04 17:32
• Updated: 21/Jan/11 17:48
• Resolved: 30/Nov/09 13:49

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

• duplicates: DERBY-4442 Evaluation of default value and identity in an INSERT result set evaluated too early.
• relates to: DERBY-4442 Evaluation of default value and identity in an INSERT result set evaluated too early.