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 de...@db.apache.org on 2004/09/27 19:32:32 UTC
[jira] Created: (DERBY-3) Identity column not filled consecutively with "insert ... select distinct"
Message:
A new issue has been created in JIRA.
---------------------------------------------------------------------
View the issue:
http://issues.apache.org/jira/browse/DERBY-3
Here is an overview of the issue:
---------------------------------------------------------------------
Key: DERBY-3
Summary: Identity column not filled consecutively with "insert ... select distinct"
Type: Bug
Status: Unassigned
Priority: Major
Project: Derby
Assignee:
Reporter: Christian d'Heureuse
Created: Mon, 27 Sep 2004 10:32 AM
Updated: Mon, 27 Sep 2004 10:32 AM
Environment: Derby Snapshot svnversion 46005
or Cloudscape 10.0 GA
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
---------------------------------------------------------------------
JIRA INFORMATION:
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
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-3) Identity column not filled consecutively with "insert ... select distinct"
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-3?page=comments#action_12360491 ]
Daniel John Debrunner commented on DERBY-3:
-------------------------------------------
While I agree it's not a bug, I could see this as an improvement. It would be more natural to have consective values in this case.
> 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
[jira] Resolved: (DERBY-3) Identity column not filled consecutively with "insert ... select distinct"
Posted by "Kathey Marsden (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-3?page=all ]
Kathey Marsden resolved DERBY-3:
--------------------------------
Resolution: Invalid
Per Satheesh's comment in May this does not appear to be 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: 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
[jira] Reopened: (DERBY-3) Identity column not filled consecutively
with "insert ... select distinct"
Posted by "Kristian Waagan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-3?page=all ]
Kristian Waagan reopened DERBY-3:
---------------------------------
> 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
[jira] Commented: (DERBY-3) Identity column not filled consecutively with "insert ... select distinct"
Posted by "Christian d'Heureuse (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Updated: (DERBY-3) Identity column not filled consecutively with "insert ... select distinct"
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-3?page=all ]
Satheesh Bandaram updated DERBY-3:
----------------------------------
Component: SQL
While it may be desirable not to have gaps in identity values, the current implementation does match required uniqueness.
> 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
[jira] Updated: (DERBY-3) Identity column not filled consecutively
with "insert ... select distinct"
Posted by "Kristian Waagan (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Commented: (DERBY-3) Identity column not filled
consecutively with "insert ... select distinct"
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12777535#action_12777535 ]
Dag H. Wanvik commented on DERBY-3:
-----------------------------------
I agree with that analysis, Knut.
> Identity column not filled consecutively with "insert ... select distinct"
> --------------------------------------------------------------------------
>
> Key: DERBY-3
> URL: https://issues.apache.org/jira/browse/DERBY-3
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.2.1.6
> 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.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3) Identity column not filled
consecutively with "insert ... select distinct"
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12712767#action_12712767 ]
Knut Anders Hatlen commented on DERBY-3:
----------------------------------------
Just to add another data point, the identity values are consecutive if you put another query around the select distinct:
ij> insert into temp2 (s) select * from (select distinct s from temp1) t;
4 rows inserted/updated/deleted
ij> select * from temp2;
I |S
----------------------
1 |d
2 |b
3 |c
4 |a
4 rows selected
For some reason, the ordering is different too.
> Identity column not filled consecutively with "insert ... select distinct"
> --------------------------------------------------------------------------
>
> Key: DERBY-3
> URL: https://issues.apache.org/jira/browse/DERBY-3
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.2.1.6
> 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.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3) Identity column not filled
consecutively with "insert ... select distinct"
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12774408#action_12774408 ]
Knut Anders Hatlen commented on DERBY-3:
----------------------------------------
The gaps are probably caused by the early evaluation of identity columns mentioned in Dag's comment on DERBY-4, dated 28/Oct/09. A fix for DERBY-4 will likely fix this issue too.
> Identity column not filled consecutively with "insert ... select distinct"
> --------------------------------------------------------------------------
>
> Key: DERBY-3
> URL: https://issues.apache.org/jira/browse/DERBY-3
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.2.1.6
> 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.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-3) Identity column not filled consecutively
with "insert ... select distinct"
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen resolved DERBY-3.
------------------------------------
Resolution: Duplicate
This issue has now been fixed by the changes in DERBY-4442, so I'm marking it as a duplicate of that issue and resolving it.
> Identity column not filled consecutively with "insert ... select distinct"
> --------------------------------------------------------------------------
>
> Key: DERBY-3
> URL: https://issues.apache.org/jira/browse/DERBY-3
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.2.1.6
> 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.
-
You can reply to this email to add a comment to the issue online.