Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4433

Cannot insert from EXCEPT/INTERSECT when target table has more columns than the source

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Duplicate
    • 10.6.1.0
    • None
    • SQL
    • None

    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

      Attachments

        1. prn.diff
          8 kB
          Knut Anders Hatlen
        2. prn2.diff
          8 kB
          Knut Anders Hatlen
        3. prn3.diff
          11 kB
          Knut Anders Hatlen

        Issue Links

          Activity

            People

              knutanders Knut Anders Hatlen
              knutanders Knut Anders Hatlen
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: