db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4) "order by" is not supported for "insert ... select"
Date Tue, 22 May 2007 09:37:16 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12497780

Bernt M. Johnsen commented on DERBY-4:

Bryan wrote:
> 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. 

Actually, the SQL standard does not require that we increment by 1 but
by N*1 where N is an non-negative integer. See Ch 9.21 which says that
the next value calculated like this:

  "If there exists a non-negative integer N such that SMIN <= CBV + N
   * INC <= SMAX and the value (CBV + N * INC) has not already been
   returned in the current cycle, then let V1 be (CBV + N * INC)."

So any value N * INC between the current base value (CBV) and the
limit (SMAX (or SMIN if the generator is decreasing)) is legal and
holes may not be reclaimed unless the base value is altered
explicitely (that is for non-cyclic generators, which is what Derby

No well behaved application should depend on the increment actually to
be 1 every time (although it will be in most cases).

> "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.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message