[
https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12773029#action_12773029
]
Knut Anders Hatlen commented on DERBY-4433:
-------------------------------------------
IntersectOrExceptNode.preprocess() adds an ORDER BY clause and pushes it down
to the child nodes, since its easier to calculate the difference or the
intersection between two tables if they have the same ordering.
The ORDER BY clause is generated from the ResultColumnList in the
IntersectOrExceptNode. However, InsertNode.enhanceAndCheckForAutoincrement()
adds placeholders for unspecified columns in the target table, and it also
reorders the RCL to match that of the target table, so the generated ORDER BY
clause does not match the column lists in the child nodes.
As has been mentioned in other issues too (DERBY-4, DERBY-4413) it would be
better if a ProjectRestrictNode was inserted between the InsertNode and the
IntersectOrExceptNode, and the RCL of the PRN was modified instead of the
IntersectOrExceptNode directly.
> Cannot insert from EXCEPT/INTERSECT when target table has more columns than
> the source
> --------------------------------------------------------------------------------------
>
> Key: DERBY-4433
> URL: https://issues.apache.org/jira/browse/DERBY-4433
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
>
> If an INSERT statement takes the rows to insert from an EXCEPT query or an
> INTERSECT query, the statement fails with "Column position 'X' out of range"
> if the target table contains more columns than the result returned from
> EXCEPT or INTERSECT.
> Example:
> ij> create table t (x int, y int);
> 0 rows inserted/updated/deleted
> ij> insert into t(x) select x from t except select x from t;
> ERROR 42X77: Column position '2' is out of range for the query expression.
> ij> insert into t(x) select x from t intersect select x from t;
> ERROR 42X77: Column position '2' is out of range for the query expression.
> The corresponding UNION query works:
> ij> insert into t(x) select x from t union select x from t;
> 0 rows inserted/updated/deleted
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
