You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2009/11/03 12:45:03 UTC
[jira] Created: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12782217#action_12782217 ]
Dag H. Wanvik commented on DERBY-4433:
--------------------------------------
Right, this seems to be handled correctly in derby-4442-1a; that patch also works together with derby-4451d, good, running regressions for that combination now.
> 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12782135#action_12782135 ]
Knut Anders Hatlen commented on DERBY-4433:
-------------------------------------------
That's what I see too now. Don't know what has changed, or if I misread the test results.
Anyways, the 1a patch attached to DERBY-4442 (which is based on the prn3) does not cause any NPE in union.sql.
> 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.
[jira] Issue Comment Edited: (DERBY-4433) Cannot insert from
EXCEPT/INTERSECT when target table has more columns than the source
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12779709#action_12779709 ]
Knut Anders Hatlen commented on DERBY-4433:
-------------------------------------------
I think the root cause of failure (1) is DERBY-4448, which can also be seen on a clean trunk if the query is modified. With the prn.diff patch, RCL.forbidOverrides() is called on UnionNodes and RowResultSetNodes, whereas on a clean trunk it is called on all ResultSetNodes except UnionNodes and RowResultSetNodes, which is probably why the patch exposed the problem in these other queries.
> 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
>
>
> 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12773025#action_12773025 ]
Knut Anders Hatlen commented on DERBY-4433:
-------------------------------------------
This bug is only seen on branches where DERBY-4420 has been fixed. On branches without the fix for DERBY-4420, a NullPointerException is seen instead.
> 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12780339#action_12780339 ]
Dag H. Wanvik commented on DERBY-4433:
--------------------------------------
I am not sure when I'll be able to have a go at DERBY-4442, so I am fine with you finishing off this issue, or if you prefer, go for DERBY-4442, your call. :) If we can reuse some, all the better.
> 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12773211#action_12773211 ]
Knut Anders Hatlen commented on DERBY-4433:
-------------------------------------------
Before DERBY-1644, a PRN was inserted on top of SetOperatorNodes. Perhaps reintroducing parts of the old code would help. The case DERBY-1644 solved by removing the PRN, was a UnionNode that represented a multi-row VALUES clause. I'll see if inserting a PRN for all SetOperatorNodes, except UnionNodes whose tableConstructor() method returns true, solves this issue and at the same time doesn't break the DERBY-1644 cases. If that works, the approach could be extended to other node types as well, which may be helpful for DERBY-4 (both removing the need for OrderByColumn.findNewPos() and solving the problem with early evaluation of identity columns).
> 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.
[jira] Updated: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4433:
--------------------------------------
Attachment: prn2.diff
I'm not so sure anymore that failure (1) is an instance of DERBY-4448. The fix turned out to be simple, though. Just don't call forbidOverrides() on the top-level UnionNode if it's a table constructor. forbidOverrides() is called on each child RowResultSetNode in the table constructor, so the check is not needed on the UnionNode. The current code on trunk also skips the call to forbidOverrides() on UnionNodes that are table constructors (or actually, it skips it for all UnionNodes).
The attached prn2.diff includes this change and fixes failure (1). Failure (2) is still seen, though.
> 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12782129#action_12782129 ]
Dag H. Wanvik commented on DERBY-4433:
--------------------------------------
Tried prn3, but I got another error in the union script:
insert into insert_test (vc30) select vc10 from t1 union select c30 from t2;
ERROR XJ001: Java exception: ': java.lang.NullPointerException'
> 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12773565#action_12773565 ]
Dag H. Wanvik commented on DERBY-4433:
--------------------------------------
Looked briefly at the patch, I think this is a move in the right direction.
> 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
>
>
> 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12780488#action_12780488 ]
Knut Anders Hatlen commented on DERBY-4433:
-------------------------------------------
OK, in that case I'll see if I can sort out the NPE and get the patch into a committable state first. I have a hope that once it's in, DERBY-4442 could be solved by moving the code that generates the ProjectRestrictNode from SetOperatorNode to ResultSetNode.
> 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12773693#action_12773693 ]
Knut Anders Hatlen commented on DERBY-4433:
-------------------------------------------
Thanks Dag! Unfortunately, there were two failures in suites.All.
1) GeneratedColumnsTest:
> create table t_insert_3( a int, b int generated always as ( -a ) );
> insert into t_insert_3 values ( 2, default ), ( 3, default ), ( 4, default )
---> SQLSyntaxErrorException: You may not override the value of generated column 'B'.
2) LangScripts, union.sql:
> create table insert_test (i int, s smallint, d double precision, r real,
> c10 char(10), c30 char(30), vc10 varchar(10), vc30 varchar(30));
> insert into insert_test (s, i) values (2, 1) union values (4, 3);
---> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
> 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
>
>
> 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12779903#action_12779903 ]
Knut Anders Hatlen commented on DERBY-4433:
-------------------------------------------
I agree that this issue could be seen as part of DERBY-4442, so it might make sense to shift the effort to that bug. I'm cautiously optimistic, though, that the suggested approach in this issue could serve as a first step towards DERBY-4442.
If you want to get going on DERBY-4442, then I'd say just go ahead. I haven't looked much at this issue after the initial evaluation, and put it on hold and revisit it after DERBY-4442 (when there hopefully will be nothing more to do except writing a test case). If you can use some of the code I've posted here, feel free to recycle it over at that issue.
> 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
>
>
> 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.
[jira] Updated: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4433:
--------------------------------------
Attachment: prn3.diff
The root cause of NPE was that the code currently on trunk that reorders and enhances the RCL is only meant to be used for replacing the RCL in a node. It does not account for the extra level in the tree caused by inserting a ProjectRestrictNode.
This was easily solved by reviving more of the pre-DERBY-1644 code in SetOperatorNode, since that code used to insert a PRN and correctly adjusted the nesting levels of the RCs. The old code used the method ResultColumn.getExpressionType(), which does not exist anymore, but I think it should work fine to use ResultColumn.getType() instead.
This solved the NPE in union.sql. I'll start the regression tests to see if it has introduced any new problems.
(Updated patch attached as prn3.diff.)
> 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12779791#action_12779791 ]
Dag H. Wanvik commented on DERBY-4433:
--------------------------------------
Maybe we should work on DERBY-4442 instead, and most of these bugs seen with INSERT into column subsets would go away.. The source result set enhancement seems broken and I'd love to get rid of it..
> 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
>
>
> 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.
[jira] Closed: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen closed DERBY-4433.
-------------------------------------
Resolution: Duplicate
I'm closing this issue as a duplicate of DERBY-4442, since the fix for that issue will fix this issue too.
> 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.
[jira] Assigned: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen reassigned DERBY-4433:
-----------------------------------------
Assignee: Knut Anders Hatlen
> 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
>
> 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4433:
--------------------------------------
Attachment: prn.diff
The attached patch reintroduces the adding of a PRN between an InsertNode and a SetOperatorNode. In order to prevent DERBY-1644 from being reintroduced, the PRN is not added if the SetOperatorNode is a UnionNode that represents a table constructor. The patch appears to fix the problem reported in this issue, and it doesn't break the test cases added for DERBY-1644.
The patch is posted just for reference, it is not ready for commit. No tests have been run, except lang/autoincrement.sql.
> 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
>
>
> 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.
[jira] Issue Comment Edited: (DERBY-4433) Cannot insert from
EXCEPT/INTERSECT when target table has more columns than the source
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12779903#action_12779903 ]
Knut Anders Hatlen edited comment on DERBY-4433 at 11/19/09 9:54 AM:
---------------------------------------------------------------------
I agree that this issue could be seen as part of DERBY-4442, so it might make sense to shift the effort to that bug. I'm cautiously optimistic, though, that the suggested approach in this issue could serve as a first step towards DERBY-4442.
If you want to get going on DERBY-4442, then I'd say just go ahead. I haven't looked much at this issue after the initial evaluation, and could put it on hold and revisit it after DERBY-4442 (when there hopefully will be nothing more to do except writing a test case). If you can use some of the code I've posted here, feel free to recycle it over at that issue.
was (Author: knutanders):
I agree that this issue could be seen as part of DERBY-4442, so it might make sense to shift the effort to that bug. I'm cautiously optimistic, though, that the suggested approach in this issue could serve as a first step towards DERBY-4442.
If you want to get going on DERBY-4442, then I'd say just go ahead. I haven't looked much at this issue after the initial evaluation, and put it on hold and revisit it after DERBY-4442 (when there hopefully will be nothing more to do except writing a test case). If you can use some of the code I've posted here, feel free to recycle it over at that issue.
> 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
>
>
> 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.
[jira] Commented: (DERBY-4433) Cannot insert from EXCEPT/INTERSECT
when target table has more columns than the source
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12773183#action_12773183 ]
Dag H. Wanvik commented on DERBY-4433:
--------------------------------------
Using the exisiting scheme, I think I handled this issue as part of the revamped patch for DERBY-4, cf. the new method OrderByColumn.findNewPos in that patch.
> 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.