db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: [jira] Commented: (DERBY-4) "order by" is not supported for "insert ... select"
Date Tue, 22 May 2007 01:44:55 GMT

Bryan Pendleton (JIRA) wrote:
>     [ https://issues.apache.org/jira/browse/DERBY-4?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12497655
> Bryan Pendleton commented on DERBY-4:
> -------------------------------------
> Mike asked: "The current implementation happens to return increasing keys, but do we
ever guarantee that, and should we?"
> I believe the answer is that we guarantee to obey the START WITH and 
> INCREMENT BY attributes of the generated column At:
> http://db.apache.org/derby/docs/dev/ref/rrefsqlj37836.html#rrefsqlj37836
> we say "For SMALLINT, INT, and BIGINT columns with identity attributes, Derby automatically
assigns increasing integer values to the column."
You are right, we do seem to already document the implementation - 
sounds like we won't ever be able to reclaim "holes" and still be 
backward compatible.  The increment guarantee is a little slippery as
we do document that the next key value may be an arbitrary amount above
the previous explicitly due to concurrency optimization allowing us to
"lose keys".

> Later, we also say: "And if you specify a negative number for the increment value, Derby
decrements the value with each insert. If this value is positive, Derby increments the value
with each insert. "
> It's hard to see how to interpret the "START WITH" and "INCREMENT BY"
> attributes of the generated column spec without providing such guarantees. 
> INCREMENT BY 1 needs to increment by 1.
> I agree with you that relaxing these guarantees could allow higher-performing
> implementations in the future.
> But I think that many applications are already depending on the current behavior of the
> START WITH and INCREMENT BY properties.
>>"order by" is not supported for "insert ... select"
>>                Key: DERBY-4
>>                URL: https://issues.apache.org/jira/browse/DERBY-4
>>            Project: Derby
>>         Issue Type: New Feature
>>         Components: SQL
>>           Reporter: Christian d'Heureuse
>>        Assigned To: Bryan Pendleton
>>           Priority: Minor
>>        Attachments: insertOrderBy.diff, insertOrderBy_v2.diff, samples.ij
>>When filling a table with "insert ... select ...", "order by" cannot be specified.
>>There is not method to copy a table sorted into another table (except using export/import).
This would be useful to optimize performance for big tables, or to create identity values
that are ascending (related to another column).
>>create table temp1 (
>>   s varchar(10));
>>insert into temp1 values 'x','a','c','b','a';
>>create table temp2 (
>>   i integer not null
>>      generated always as identity
>>      primary key,
>>   s varchar(10));
>>insert into temp2 (s)
>>   select s from temp1 order by s;
>>--> Error: "order by" is not allowed.
>>-- trying to use "group by" instead of "oder by":
>>insert into temp2 (s)
>>   select s from temp1 group by s;
>>select * from temp2;
>>--> "group by" did not sort the table.

View raw message