IBM Support

JR25954: WRONG RESULTS FROM OUTER JOIN OR MERGE WHEN ON CLAUSE CONTAINS ONLY CORRELATED COLUMNS; CONSTANTS; HOST OR REGISTER VARIABLES

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • The ON clause predicate is incorrectly applied as a
    filtering predicate in OUTER JOIN or MERGE operations when the
    predicate is composed of only one of the following references:
    
    - correlated columns
    - constants
    - host variables or special registers
    
    
    The following examples show queries that can be affected by this
    problem:
    
     select distinct 1 from (values 1) as dummy(c1) left join t on
     2=1 ;
    
     select * from s, lateral(select * from r left join t on s.c1=2)
     ;
    
     select case when exists (select 1 from (values 1) as dummy(c1)
     left join t on s.c1=1) then 'correct' else 'wrong' end from s ;
    
     select case when exists (select 1 from (values 1) as dummy(c1)
     left join t on s.c1=s.c2) then 'correct' else 'wrong' end from
     s ;
    

Local fix

Problem summary

  • Users affected: Users of DB2 LUW version 8
    Problem Description:
    The ON clause predicate is incorrectly applied as a
    filtering predicate in OUTER JOIN or MERGE operations when the
    predicate is composed of only one of the following references:
    
    - correlated columns
    - constants
    - host variables or special registers
    
    The following examples show queries that can be affected by this
    problem:
    
     select distinct 1 from (values 1) as dummy(c1) left join t on
     2=1 ;
    
     select * from s, lateral(select * from r left join t on s.c1=2)
     ;
    
     select case when exists (select 1 from (values 1) as dummy(c1)
     left join t on s.c1=1) then 'correct' else 'wrong' end from s ;
    
     select case when exists (select 1 from (values 1) as dummy(c1)
     left join t on s.c1=s.c2) then 'correct' else 'wrong' end from
     s ;
    

Problem conclusion

  • DEFECT=375752 CSD>c070305 MODULE=engn_sqnr
    First fixed in DB2 UDB Version 8, Fixpak 15
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR25954

  • Reported component name

    DB2 UDB EXE WIN

  • Reported component ID

    5724E4901

  • Reported release

    820

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2007-02-16

  • Closed date

    2007-08-16

  • Last modified date

    2007-08-16

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

    JR25882

  • 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

  • R820 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"820","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 August 2007