My interest in warming this up again is
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
(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  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
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
 See discussion on
Added pushOrderByList, InsertNode uses analogue to pattern used by CursorNode.
Removed a Sanity assert for a case and added a comment for when the
scenario can happen.
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.
Add logic for OrderBy.
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.
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
Code cleanups only.