db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4) "order by" is not supported for "insert ... select"
Date Wed, 28 Oct 2009 19:24:59 GMT

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

Dag H. Wanvik updated DERBY-4:

    Attachment: derby-4_dhw.stat

My interest in warming this up again is DERBY-4397.
I plan to use a modified version of this patch as part of that work.

This patch is thus just a baseline of my work on this "subproblem" of
INSERT combined with ORDER BY and is not intended for commit. I just
post it here for the record. It passes regression tests, and besides
whats discussed below it seems to work for what I have thrown at it.

This patch builds on Bryan's latest patch proposal for DERBY-4
(thanks!), and adds a JUnit test. It does *not* solve the problem
("early" evaluation of identity columns, i.e. they are evaluated
before the ordering) which Bryan observed. I think the correct
solution [1] is to defer all default assignments (identity, plain
DEFAULT, generated columns) till after the result set is ready, as is
currently done for generated columns. If a target column list is given
in the INSERT, the result set would then be "narrow" (i.e. not
containing unspecified columns) until after the ordering. A top PRN
would be used to inject the missing columns with their (default)
values. This would solve DERBY-4.

Meanwhile, in preparation for that we should forbid ORDER BY on a
VALUES clause if it contains DEFAULT values (as a minimum, since
logically they have no value until after ordering; and physically, in
the case of generated columns, they are null and can't be used for
sorting), and probably also forbid ORDER BY on VALUES at all, as per
the standard (see explanation on why in DERBY-4413). As it stands,
this patch does the latter.

Also, DEFAULT should be allowed only in simple VALUES clause, see
DERBY-4426, ensuring it could never be the subject of sorting (e.g. in

[1] See discussion on DERBY-4413.

Patch details:

M       java/engine/org/apache/derby/impl/sql/compile/SingleChildResultSetNode.java

Added pushOrderByList, InsertNode uses analogue to pattern used by CursorNode.

M       java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java

Added bindExpressions.

M       java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java

Removed a Sanity assert for a case and added a comment for when the
scenario can happen.

M       java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
M       java/engine/org/apache/derby/impl/sql/compile/OrderByList.java
M       java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
M       java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java

Added colMap to account for column subset/reordering which happens as
a result of enhanceRCLForInsert called from InsertNode. Note that code
was written prior to our realizing that a simple VALUES clause could
not be followed by ORDER BY, so some code can probably be removed now
if that decision stands.

M       java/engine/org/apache/derby/impl/sql/compile/InsertNode.java

Add logic for OrderBy.

M       java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj

Special syntax allowance for ORDER BY in INSERT. Will be
moved/generalized to subqueries for DERBY-4397. Added a check that
ORDER BY can not be applied to a simple VALUES clause.

A       java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByInSubqueries.java

Also contains tests for VALUES .. ORDER BY, but commented out.  If the
check in sqlgrammar is removed these tests can be re-enabled and do

M       java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
M       java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
M       java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
M       java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
M       java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java

Code cleanups only.

> "order by" is not supported for "insert ... select"
> ---------------------------------------------------
>                 Key: DERBY-4
>                 URL: https://issues.apache.org/jira/browse/DERBY-4
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Christian d'Heureuse
>            Priority: Minor
>         Attachments: derby-4_dhw.diff, derby-4_dhw.stat, insertOrderBy.diff, insertOrderBy_v2.diff,
insertOrderBy_v3.diff, samples.ij, 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