db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4) "order by" is not supported for "insert ... select"
Date Sun, 20 May 2007 20:41:16 GMT

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

Bryan Pendleton updated DERBY-4:

    Attachment: samples.ij

Thanks to all for the continued feedback!

Attached is a second version of the proposed patch. Again, this is
not for commit, just to continue to stimulate the discussion. This
patch has two changes from the previous patch:
1) An error message is added to InsertNode's initializer to reject
the use of ORDER BY for INSERT ... VALUES statements
2) SingleChildResultSetNode is changed to override the
pushOrderByList() method and to pass the orderByList on to its
childResult. This is important when there is a NormalizeResultSetNode,
as occurs when the number of columns being SELECTed does not
match the number of columns being INSERTed. This situation arises
when using auto-generated columns.

Unfortunately, the original script from Christian is still not working
correctly with my patch. The ORDER BY list is properly passed to
the optimizer, and an OrderBy node is constructed to cause the
results to be generated in sorted order, but something goes wrong
and the final result is not properly sorted.

Examples *without* auto-generated columns seem to be working fine.

I'll continue working on this problem to figure out why the sort isn't working.

Also attached is 'samples.ij', which contains some SQL samples
that others might find interesting when experimenting with the patch,
and which I hope to someday turn into full-fledged test cases.

As I said above, none of this is for commit; I'm just trying to keep moving
the discussion and the implementation along and share the work
that I've done so far.

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