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 Sat, 19 May 2007 08:13:16 GMT

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

Bernt M. Johnsen commented on DERBY-4:
--------------------------------------

Hi Bryan. I see your point and the need wrt. the generated columns. I have tried to dive into
the SQL 2003 standard to see if this extension violates any SQL semantics, and I admit I can't
find any (although the construct is a bit "un-SQL-like", but so are many constructs other
SQL databases ;-) ). But it will restrain us from some optimizations in the future, e.g. if
the result from the subquery is very large, it could be partitioned and the partitions inserted
in parallell into the base table (maybe far fetched?). 

I see from the patch that you extend the insert statement and not generally the subquery.
That is good. Is it an idea also to restrict the use of the order by clause in the insert
statement to base tables with generated columns? 

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


Mime
View raw message