You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Kristian Waagan (JIRA)" <de...@db.apache.org> on 2006/01/23 14:42:22 UTC
[jira] Updated: (DERBY-3) Identity column not filled consecutively
with "insert ... select distinct"
[ http://issues.apache.org/jira/browse/DERBY-3?page=all ]
Kristian Waagan updated DERBY-3:
--------------------------------
type: Improvement (was: Bug)
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
was:
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
Version: 10.2.0.0
Environment:
Derby Snapshot svnversion 46005
or Cloudscape 10.0 GA
Derby 10.2beta
was:
Derby Snapshot svnversion 46005
or Cloudscape 10.0 GA
Priority: Minor (was: Major)
Ran test script on Derby 10.2beta. The behavior is still the same, you do not get consequtive ids for the given query (with "insert ... select distinct").
Recategorized issue, as the current behavior is not a bug.
> Identity column not filled consecutively with "insert ... select distinct"
> --------------------------------------------------------------------------
>
> Key: DERBY-3
> URL: http://issues.apache.org/jira/browse/DERBY-3
> Project: Derby
> Type: Improvement
> Components: SQL
> Versions: 10.2.0.0
> Environment: Derby Snapshot svnversion 46005
> or Cloudscape 10.0 GA
> Derby 10.2beta
> Reporter: Christian d'Heureuse
> Priority: Minor
>
> 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