IBM Support

JR27383: DB2AGENT CRASH (SIGTRAP) ON INNER JOIN AND OUTER JOIN QUERY WITH MULTIPLE IN CLAUSES (CORRELATED SUBQUERY, OR PREDICATE)

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Signal #5 (SIGTRAP) error happens at sqlnr_reorder_IJ_qgm() in
    db2agent when a query below is issued.  In this case, you see
    this signal in the db2agent trap file as follows.
    
    Signal #5 (SIGTRAP): si_code is 8 (SI_UNDEFINED:siginfo_t
    contains partial information.) si_pid is 0, si_uid is 0,
    si_value is 00000000
    
    
      The following stack trace is the eye-catcher for this APAR.
    
    0x0900000009260260 pp__17sqlnq_qunqncchildFv + 0xC
    0x0900000009276F24
    sqlnr_reorder_IJ_qgm__FP9sqlnq_qunP9sqlnq_oprPP13sqlnq_deplistPi
    P3loc + 0x1F60
    0x0900000009115F2C sqlnr_qrwprep_phase2__FP3locPi + 0xA48
    0x090000000908C2B8 sqlnr_exe__FP9sqlnq_qur + 0x10A0
    0x090000000DCC3078
    sqlnn_cmpl__FP20sqle_agent_privatecbP11sqlrrstrings17sqlnn_compi
    leModeT3P14sqlrr_cmpl_enviT7PP9sqlnq_qur + 0x5724
    0x090000000DCBD8D0
    sqlnn_cmpl__FP20sqle_agent_privatecbP11sqlrrstrings17sqlnn_compi
    leModeT3P14sqlrr_cmpl_env + 0x64
    0x090000000B3C6BBC
    sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P16sqlra
    _cached_varPiPUl + 0x14CC
    0x090000000B420F00
    sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUs
    UiPUcT5T4UcP14sqlra_cmpl_enviPiT11_T12_N311_T12_P1
    4SQLP_LOCK_INFOPP16sqlra_cached_varT12_PUlb + 0xACC
    0x090000000B3BFA60 sqlra_get_var__FP8sqlrr_cbiPiT2bT3 + 0xE70
    0x090000000B9CCEE0
    sqlrr_prepare__FP14db2UCinterfaceP13UCprepareInfo + 0x1F8
    
    
    ----------------------------------------
    A sample query of reproduction
    
    -- Table and Primary key definition
    create table t1 (pk int not null primary key, c12 int);
    create table t2 (c21 int, c22 int);
    create table t3 (c31 int, c32 int);
    create table t4 (c41 int, c42 int);
    
    -- The simplest repro query found so far
    SELECT  1
    FROM T2 LEFT OUTER JOIN T3 ON c21 = c31
            INNER JOIN T1 ON c22 = pk AND c12 <> 1
    WHERE
         c22 IN (SELECT f1.pk
                 FROM T1 f1 LEFT OUTER JOIN T1 f2 ON f1.c12 = f2.pk
                 WHERE EXISTS (SELECT * FROM T4 WHERE c41 = f2.pk))
         AND
         c22 IN (SELECT f1.pk
                 FROM T1 f1 LEFT OUTER JOIN T1 f2 ON f1.c12 = f2.pk
                 WHERE f1.pk = 2 OR f2.pk = 2)
    ;
    
    
    ----------------------------------------
    Conditions to hit the trap
    
    1. T1 must have primary key. Call that column pk.
    2. Query outer block should have outer join and inner join at
    the same time.
    3. The inner join has join pred "pk=c22", while c22 are involved
    in two (or more) IN pred. Call IN pred subquery  INSQ1 and
    INSQ2. The inner join should have a local pred, "c12 <> 1". This
    local pred can not be equalty pred.
    4. Both INSQ1 and INSQ2 should have outer join and output pk.
    5. INSQ1 has correlated subquery, i.e. EXISTS pred in the query.
    6. INSQ2 has OR pred whose subterms involve columns from both
    row preserving side and null producing side. For example, f1.pk
    = 2 OR f2.pk = 2.
    

Local fix

  • Replace "c22 IN " with "pk IN ". The above sample query can be
    rewriten to:
    
    SELECT  1
    FROM T2 LEFT OUTER JOIN T3 ON c21 = c31
            INNER JOIN T1 ON c22 = pk AND c12 <> 1
    WHERE
         pk IN (SELECT f1.pk
                 FROM T1 f1 LEFT OUTER JOIN T1 f2 ON f1.c12 = f2.pk
                 WHERE EXISTS (SELECT * FROM T4 WHERE c41 = f2.pk))
         AND
         pk IN (SELECT f1.pk
                 FROM T1 f1 LEFT OUTER JOIN T1 f2 ON f1.c12 = f2.pk
                 WHERE f1.pk = 2 OR f2.pk = 2)
    ;
    

Problem summary

  • Instance crash happens on a query sql which has the outer join
    and inner join with a join predicate "primary key=<column>" at
    the same time, and of which table has primary key, two IN
    predicates following outer JOIN.  Workarond of this issue is to
    use a primary key in two IN predicates.
    

Problem conclusion

  • Problem was the first fixed in Version 9.1 FixPak 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR27383

  • Reported component name

    DB2 UDB EXE WIN

  • Reported component ID

    5724E4901

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2007-09-05

  • Closed date

    2008-06-27

  • Last modified date

    2008-06-27

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

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

    JR28475

Fix information

  • Fixed component name

    DB2 UDB EXE WIN

  • Fixed component ID

    5724E4901

Applicable component levels

  • R910 PSN

       UP

[{"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","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
12 October 2021