[DERBY-4433] Cannot insert from EXCEPT/INTERSECT when target table has more columns than the source

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 10.6.1.0
• Component/s: SQL
• Assignee: Knut Anders Hatlen
• Reporter: Knut Anders Hatlen
• Created: 03/Nov/09 11:44
• Updated: 25/Nov/09 12:55
• Resolved: 25/Nov/09 12:55

Description

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 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 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 select x from t union select x from t;
0 rows inserted/updated/deleted

Issue Links

• duplicates: DERBY-4442 Evaluation of default value and identity in an INSERT result set evaluated too early.
• relates to: DERBY-4420 NullPointerException with INSERT INTO ... from EXCEPT/INTERSECT
• relates to: DERBY-4442 Evaluation of default value and identity in an INSERT result set evaluated too early.