IBM Support

JR30820: DB2 INSTANCE MAY ABEND WITH A QUERY CONTAINING TAUTOLOGY PREDICATES ORED WITH CORRELATED EXISTENTIAL

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • An SQL statement that contains a tautology or true predicate
    that is ORed with an existential predicate may cause the DB2
    instance to abend.
    For such an abend to occur, the following conditions must be
    satisfied:
    
    1) The query contains a true local predicate (TLP)
    2) The TLP is ORed with a correlated existential predicate (EP)
    3) The fullselect of the EP refers to a common table expression
       (CTE)
    4) The CTE refers to a table that is used as a source of
       correlation with 2 or more (not necessarily distinct) tables
    
    An example scenario follows:
    
    -- DDL
    CREATE TABLE T1 (C1 INT);
    CREATE TABLE T2 LIKE T1;
    CREATE TABLE T3 LIKE T1;
    CREATE TABLE T4 LIKE T1;
    
    -- Query
    WITH
    CTE (C1, C2) AS
    ( SELECT  (SELECT C1 FROM T1 WHERE T3.C1 = T1.C1),
              (SELECT C1 FROM T4 WHERE T3.C1 = T4.C1)
      FROM    T3
    )
    SELECT  C1
    FROM    T2
    WHERE   (EXISTS (SELECT C1 FROM CTE C WHERE C.C1 = T2.C1)
             OR 1 = 1
            )
    ;
    
    The stack trace from the abend may look something like:
    
    UDT_colequiv_exp
    sqlnr_build_equiv
    sqlnr_theorem_prover
    sqlnr_qrwprep_phase2
    sqlnr_exe
    sqlnn_cmpl
    sqlnn_cmpl
    ...
    

Local fix

  • If semantics permit, rewrite the problematic query to an
    equivalent form that does not have the 4 characteristics
    described above.
    Lowering the value of the CURRENT QUERY OPTIMIZATION special
    register to a value less than 5 will also circumvent the issue.
    

Problem summary

  • Users Affected: ALL
    
    Problem Description:
    
    An SQL statement that contains a tautology or true predicate
    that is ORed with an existential predicate may cause the DB2
    instance to abend.
    For such an abend to occur, the following conditions must be
    satisfied:
    
    1) The query contains a true local predicate (TLP)
    2) The TLP is ORed with a correlated existential predicate (EP)
    3) The fullselect of the EP refers to a common table expression
       (CTE)
    4) The CTE refers to a table that is used as a source of
       correlation with 2 or more (not necessarily distinct) tables
    
    An example scenario follows:
    
    -- DDL
    CREATE TABLE T1 (C1 INT);
    CREATE TABLE T2 LIKE T1;
    CREATE TABLE T3 LIKE T1;
    CREATE TABLE T4 LIKE T1;
    
    -- Query
    WITH
    CTE (C1, C2) AS
    ( SELECT  (SELECT C1 FROM T1 WHERE T3.C1 = T1.C1),
              (SELECT C1 FROM T4 WHERE T3.C1 = T4.C1)
      FROM    T3
    )
    SELECT  C1
    FROM    T2
    WHERE   (EXISTS (SELECT C1 FROM CTE C WHERE C.C1 = T2.C1)
             OR 1 = 1
            )
    ;
    
    The stack trace from the abend may look something like:
    
    UDT_colequiv_exp
    sqlnr_build_equiv
    sqlnr_theorem_prover
    sqlnr_qrwprep_phase2
    sqlnr_exe
    sqlnn_cmpl
    sqlnn_cmpl
    ...
    Problem Summary:  See Problem Description.
    

Problem conclusion

  • irst fixed in DB2 UDB Version 9.5, FixPak 4 (build s090429)
    

Temporary fix

  • see LOCAL FIX
    

Comments

APAR Information

  • APAR number

    JR30820

  • Reported component name

    DB2 UDB EXE WIN

  • Reported component ID

    5724E4901

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-10-20

  • Closed date

    2009-06-15

  • Last modified date

    2009-06-15

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

    JR30818

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

Fix information

  • Fixed component name

    DB2 UDB EXE WIN

  • Fixed component ID

    5724E4901

Applicable component levels

  • R810 PSN

       UP

  • R820 PSN

       UP

  • R910 PSN

       UP

  • R950 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2009