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).
Example:
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.
