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

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

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

Problem conclusion

• QUERY RESULTS WHILE USING INTERVAL IN A SUB QUERY DOES NOT GIVE
  CORRECT RESULTS.
  

Temporary 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);
  

Comments

APAR Information

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

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

  JR28195

Fix information

• Fixed component name

  DB2 UDB ESE WIN

• Fixed component ID

  5765F4101

Applicable component levels

• R820 PSY

     UP