IT33537: UNEXPECTED INTERNAL ERROR 172 RUNNING MERGE STATEMENT USING SUBQUERIES

APAR status

• Closed as program error.

Error description

• A MERGE statement involving multiple subqueries may fail with
  error 959 / 172 when a join condition exists that connects the
  two subqueries.
  
  An example statement:
  
  MERGE INTO target t USING
  ( SELECT mnum, cref FROM source ) x
  ON t.num = x.mnum
  WHEN MATCHED THEN UPDATE
    SET code =
    (SELECT code FROM ref WHERE cref = x.cref);
  
  An Assert Failure is generated which includes a thread stack
  similar to:
  
  ld_newvals_in2tmpbuf
  subqcache_find
  chkrmk
  exsubq
  ev_cb
  new_eval
  eval_projection_list
  dodmlrow
  process_upsert
  upsert_next
  doupsert
  excommand
  sq_execute
  sqmain
  

Local fix

• a workaround can be using a view for the target table:
  
  create view vt as (select num, code from target);
  merge into vt t using
  ...
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of Informix Server prior to 12.10.xC15 and 14.10.xC5.  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Informix Server 12.10.xC15 (when available) or    *
  * 14.10.xC5.                                                   *
  ****************************************************************
  

Problem conclusion

• Fixed in Informix Server 12.10.xC15 and 14.10.xC5.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels