[ http://issues.apache.org/jira/browse/DERBY-3?page=comments#action_12360505 ]
Christian d'Heureuse commented on DERBY-3: ------------------------------------------ I used this technique within an SQL script to create consecutive numbers for some tables. I had to use temporary intermediate tables to circumvent this effect. I agree that it's not a real "bug", but it's an undesirable effect. Kathey, could you please change the issue type from "bug" to "improvement" and the status back to "open"? > Identity column not filled consecutively with "insert ... select distinct" > -------------------------------------------------------------------------- > > Key: DERBY-3 > URL: http://issues.apache.org/jira/browse/DERBY-3 > Project: Derby > Type: Bug > Components: SQL > Environment: Derby Snapshot svnversion 46005 > or Cloudscape 10.0 GA > Reporter: Christian d'Heureuse > > 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 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
