[
https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12782200#action_12782200
]
Dag H. Wanvik commented on DERBY-4433:
--------------------------------------
Adding the following line to SetOperatorNode#enhanceRCLForInsert just before
the call to generateProjectRestrictForInsert made the above case work:
resultColumns.resetVirtualColumnIds();
Cf. the following lines in SetOperatorNode#enhanceRCLForInsert:
:
ResultColumnList newResultCols = getRCLForInsert(target, colMap);
resultColumns.treePrint();
[ resultColumns.resetVirtualColumnIds(); ]
return generateProjectRestrictForInsert(target, colMap);
:
A priori in the example, there is one column in the source result set, so that
column number has virtualColumnNumber 1.
However, getRCLForInsert reuses the column object for the new ResultColumnList,
so the virtualColumnNumber gets set to 8 when enhancing. But in the original
source result set this is wrong, so this line in
generateProjectRestrictForInsert returns a null:
oldResultColumn = resultColumns.getResultColumn(colMap[index]+1);
since the virtualColumnNumber does not match the columns position, cf the logic
in ResultColumnList#getResultColumn.
Resetting the viirtual column ids bring it back in shape, but it is not the
right solution, since the numbers would then be wacky for the PRN's RCL. I
think a copy of the RC should be taken instead, cf. your observation above that
the tree looks weird.
> 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, prn3.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.
