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] Issue Comment Edited: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT when target table has more columns than the source
Date Tue, 24 Nov 2009 22:57:39 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12782200#action_12782200
] 

Dag H. Wanvik edited comment on DERBY-4433 at 11/24/09 10:57 PM:
-----------------------------------------------------------------

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.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. 

      was (Author: dagw):
    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.


Mime
View raw message