db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From derby-...@db.apache.org
Subject [jira] Commented: (DERBY-4) "order by" is not supported for "insert ... select"
Date Tue, 28 Sep 2004 07:39:32 GMT
The following comment has been added to this issue:

     Author: Jeff Lichtman
    Created: Tue, 28 Sep 2004 12:39 AM
I see what you want to do here: you are trying to number the rows in a table according to
an ordering.

Standard SQL doesn't allow the "order by" clause on an insert-select. Normally, this makes
sense because a table (AKA relation) is an unordered set of rows (AKA tuples). Normally, the
order that the rows are inserted would have no consequence that one should rely on. For example,
if you select the rows from the table you just inserted them into, there is no guarantee that
they would come back in the same order.

In this case, though, you want to rely on a side-effect of inserting (the generation of indentity
values) as the insert happens. This is a valid thing to want to do - however, it makes me
nervous to make a broad extension for such a specific purpose.

If the Virtual Table Interface (VTI) still existed (it was in Cloudscape 5.1 and earlier versions),
I would suggest using that as a workaround. VTIs allowed the user to write a Java class with
methods that returned ResultSets. One could easily hide the "order by" clause inside the VTI.

I don't think "group by" is a valid workaround. A select statement with a "group by" and no
aggregates in the select list is the same as a "select distinct." No ordering is guaranteed
- for example, a smart optimizer could avoid a sort if there were a unique index on a subset
of the result columns.

View this comment:

View the issue:

Here is an overview of the issue:
        Key: DERBY-4
    Summary: "order by" is not supported for "insert ... select"
       Type: New Feature

     Status: Unassigned
   Priority: Minor

    Project: Derby

   Reporter: Christian d'Heureuse

    Created: Mon, 27 Sep 2004 10:53 AM
    Updated: Tue, 28 Sep 2004 12:39 AM

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.

This message is automatically generated by JIRA.

If you think it was sent incorrectly contact one of the administrators:

If you want more information on JIRA, or have a bug to report see:

View raw message