IZ40627: POSSIBLE UNEXPECTED RESULTS IN SQL STATEMENT CONTAINING OR PREDICATE(S) WITH IN SUBQUERIES.

DB2 Version 9.5 Fix Pack 4  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• This problem could occur when all the following conditions are
  met:
  .
  1) The SQL statement has an OR predicate with subquery of the
  form
  .
     <col-1> IN (select <col-sq> from....) OR <other-subterm>
  .
     -OR-
     <col-1> NOT IN (select <col-sq> from....) OR <other-subterm>
  2) The statement also has an OR predicate with subquery of the
  form
     <col-2> IN (select <col-sq> from....) OR <other-subterm>
     -OR-
      <col-2> NOT IN (select <col-sq> from....) OR <other-subterm>
      Note that the OR predicate in 1) and 2) could be the same OR
  predicate or two different OR predicates
  3) The <col-sq> in 1) and 2) must be the same value of a
  constant, a host variable,
      a parameter marker, or a special register such as CURRENT
  DATE. In addition,
      <col-sq> could be a normal column in the user written SQL
  statement but is
      substituted by DB2 Query Rewrite to one of those
  aforementioned types,
      which could be observed in the Optimized Statement of an
  output of db2exfmt.
  4) There are join predicates in other part of the same SQL
  statement such that DB2 Optimizer
      could derive the following relationship
      <col-1> = <col-2> = <col-3>
      An example of such join predicates is
      <col-1> = <col-3> AND <col-2> = <col-3>
  5) When all the above conditions are met, DB2 incorrectly
  derives that
      <col-1> = <col-2> = <col-3> = <col-sq>
  In some certain choices of access plan where DB2 Optimizer
  chooses to apply the two join predicates
  <col-1> = <col-3> and <col-2> = <col-3> in the same join
  operator, the Optimizer may not apply one of
  the join predicates in 4). As a result, DB2 will produce
  possibly more rows in the result set.
  

Local fix

Problem summary

• Same as above.
  

Problem conclusion

• APAR IZ40627
  Fixed >= V95 fpk4
  

Temporary fix

• Same as above.
  

Comments

APAR Information

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

  IZ40625

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

  IC63303

Modules/Macros

• ENG_SQNO
  

Fix information

• Fixed component name

  DB2 EDE AIX

• Fixed component ID

  5724N7600

Applicable component levels

• R950 PSN

     UP