IT16112: A CORRELATED SCALAR SUBQUERY IN AN UPDATE STATEMENT MAY NOT CORRECTLY RETURN SQL0811N

DB2 Version 11.1 Mod1 Fix Pack1 iFix001 for Linux, UNIX, and Windows
DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 4 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• A correlated scalar subquery in an UPDATE statement may not
  correctly return SQL0811N.
  
     SQL0811N   The result of a scalar fullselect, SELECT INTO
  statement, or VALUES INTO statement is more than one row.
  
  Assuming 2 rows in table B will match the same row of A:
  
  Case 1)	Expects success but SQL0811N is returned
     UPDATE TEST.A A SET COLUMN_B = (SELECT COLUMN_B FROM TEST.B B
  WHERE
    A.COLUMN_A=B.COLUMN_A FETCH FIRST 1 ROWS ONLY) ;
  
  Case 2) Expects SQL0811N but it succeeds
    UPDATE TEST.A_COL A SET COLUMN_B = (SELECT COLUMN_B FROM
  TEST.B_COL B WHERE
    A.COLUMN_A=B.COLUMN_A ORDER BY COLUMN_A FETCH FIRST 2 ROWS
  ONLY) ;
  

Local fix

• Workaround:
  db2set -im DB2COMPOPT=NO_UPDATE_COMP
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 11.1 Fix Pack 1                               *
  ****************************************************************
  

Problem conclusion

• Problem fixed in DB2 11.1 FP1
  

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:

  IT18022

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels