IC95053: THE QUERY STATEMENT WITH A SUBQUERY PREDICATE MIGHT NOT RETURN ROWS AFTER ENABLING DB2_COMPATIBILITY_VECTOR=ORA

DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• The following are the necessary conditions that might cause the
  problem of "0 record(s) selected" to occur:
  
  - The DB2_COMPATIBILITY_VECTOR registry variable is set to ORA
  - A "X IN (select Y from <subquery>)" query predicate.
  - X is the CHAR type, and Y is the VARCHAR type.
  
  To verify that this problem has occurred in your query, generate
  the db2exfmt command and
  search if the optimized statement contains the following:
  
  - "(Q<num>.X >= $C3 )"
  - "(Q<num>.X <= $C2)"
  
  Also, check to see if the plan details contain:
  
  - "(ORA_VARCHAR(Q<num>.X) <= $C1 )"
  -  "(ORA_VARCHAR((Q<num>.X)>= $C0)".
  

Local fix

• Rewrite the IN predicate to the EXISTS predicate:
  
  select *
  from table1
  where pk_alansawyer in
     (select pk_christine
     from table2);
  
  to
  
  select *
  from table1
  where exists
     (select 1
     from table2 where pk_alansawyer = pk_christine);
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 version 10.1.0.3.                             *
  ****************************************************************
  

Problem conclusion

• The problem is first fixed in DB2 version 10.1.0.3.
  

Temporary fix

Comments

APAR Information

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

  IC91110

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

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RA10 PSN

     UP