IBM Support

JR28198: UNEXPECTED QUERY RESULTS WHILE USING INTERVAL IN A SUB QUERY.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Following is an example of the table and the intervals :
    ----------------------------------------------------------------
    
    db2 => select * from t
    
    N
    -----------
              1
              2
              3
              4
              5
    
      5 record(s) selected.
    
    db2 => create function interval(i int) returns table(n int)
    language sql determi
    nistic no external action return with t (n) as (values 1 union
    all select n+1 fr
    om t where n<i ) select n from t
    DB20000I  The SQL command completed successfully.
    
    db2 => create function interval2() returns table(n int) language
    sql determinist
    ic no external action return with t (n) as (values 1 union all
    select n+1 from t
     where n<5 ) select n from t
    DB20000I  The SQL command completed
    
    
    
    When trying to run the following queries we see the difference
    between the 2 :
    ----------------------------------------------------------------
    
    db2 => select T.n from table(interval(5)) T where n in (select
    T2.n from  table(
    interval(T.n)) T2)
    
    N
    -----------
              1
    
      1 record(s) selected.
    
    db2 => select T.n from table(interval(5)) T where n in (select
    T2.n from  table(
    interval(T.n)) T2 order by T.n)
    
    N
    -----------
              1
              2
              3
              4
              5
    
      5 record(s) selected.
    ----------------------------------------------------------------
    ----------------------------------------------------------------
    
    As you can see above the only difference in the 2 queries above
    is the "order by" clause :
    db2 => select T.n from table(interval(5)) T where n in (select
    T2.n from  table(
    interval(T.n)) T2)
    
    db2 => select T.n from table(interval(5)) T where n in (select
    T2.n from  table(
    interval(T.n)) T2 order by T.n)
    
    The expected results were that both of them provide 5 COLUMNS as
    outputs
    while the prior will return it as the entry order and the latter
    will give it in a sequence due to the "order by"
    clause.
    

Local fix

  • A workaround is changing the reference to T.n in the subquery to
    the same constant used in the parent table.
    
    -- give incorrect result
    select T.n
    from table(interval(5)) T
    where n in (select T2.n from table(interval(T.n)) T2);
    
    -- workaround
    select T.n
    from table(interval(5)) T
    where n in (select T2.n from table(interval(5)) T2);
    

Problem summary

  • UNEXPECTED QUERY RESULTS WHILE USING INTERVAL IN A SUB QUERY.
    

Problem conclusion

  • Work around present and problem is fixed.
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR28198

  • Reported component name

    DB2 UDB ESE WIN

  • Reported component ID

    5765F4101

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-01-03

  • Closed date

    2008-05-12

  • Last modified date

    2008-05-12

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

    JR28195

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

Fix information

  • Fixed component name

    DB2 UDB ESE WIN

  • Fixed component ID

    5765F4101

Applicable component levels

  • R950 PSY

       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:
12 May 2008