[
https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12780493#action_12780493
]
Knut Anders Hatlen commented on DERBY-4433:
-------------------------------------------
The following is a trimmed down repro for the NPE (which happens during code
generation):
CREATE TABLE T (I INT, S SMALLINT);
INSERT INTO T (S, I) VALUES (1, 2) UNION VALUES (2, 3);
Without the patch, the InsertNode will put a NormalizeResultSetNode on top of
the UnionNode. The NormalizeRSN is there because the integers that are inserted
into the S column need to be converted to smallints. The RCL of the
NormalizeRSN contains ResultColumns that via VirtualColumnNodes point to the
ResultColumns in the UnionNode.
With the patch, a ProjectRestrictNode is inserted between the
NormalizeResultSetNode and the UnionNode, to allow for reordering of the
columns without touching the UnionNode's RCL. The ResultColumns of the
NormalizeRSN now point to the RCs of the PRN, via VirtualCNs, as expected.
However, the PRN's RCs don't point to the UnionNode's RC, as I think they
should have done. And it looks like the UnionNode has the exact same
ResultColumns as the NormalizeRSN, which also sounds wrong.
> 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
> Assignee: Knut Anders Hatlen
> Attachments: prn.diff, prn2.diff
>
>
> 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.
