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] Commented: (DERBY-4) "order by" is not supported for "insert ... select"
Date Mon, 21 May 2007 23:45:16 GMT

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

Bryan Pendleton commented on DERBY-4:
-------------------------------------

Hi Army, thanks for the question! 

No, there was no technical reason for the restriction.

I think that ORDER BY clauses for VALUES expressions need 
to order by a column position. That is:
  VALUES 'b17', 'b1', 'b52' ORDER BY 1
makes sense, as does
  VALUES ('ham', 50), ('eggs', 100), ('spam', 20) ORDER BY 2
but
  VALUES 'x', 'a', 'm' ORDER BY c1
does not make sense, since columns in VALUES expressions
don't have names.

I'll re-visit the VALUES restriction and see how to make it work.

Meanwhile, I discovered a major problem in my previous patch; I had
inserted the bind processing for order by columns into the
InsertNode.bind() method in the wrong place, so it was binding the
columns incorrectly in the case(s) where NormalizeResultSetNode
instances were generated.

I'm working on a 3rd version of the patch, to incorporate all this
new knowledge :)


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


Mime
View raw message