IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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

  • 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.
    

Problem conclusion

  • apar: iz40625
    module: engn_sqnr
    fixed >= v91 fpk8
    

Temporary fix

Comments

APAR Information

  • APAR number

    IZ40625

  • Reported component name

    DB2 EDE AIX

  • Reported component ID

    5724N7600

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-12-22

  • Closed date

    2009-12-03

  • Last modified date

    2009-12-03

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

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

    IZ40626 IZ40627

Modules/Macros

  • ENG_SQNR
    

Fix information

  • Fixed component name

    DB2 EDE AIX

  • Fixed component ID

    5724N7600

Applicable component levels

  • R910 PSN

       UP

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910"}]

Document Information

Modified date:
03 October 2021