IT01329: QUERY USING JOIN, 'GROUP BY' AND 'COUNT(*)' RETURNS BAD RESULTS WHEN DS_NONPDQ_QUERY_MEM SET TOO LOW

APAR status

• Closed as program error.

Error description

• If you run a simple query joining 2 tables and using count(*)
  and group by clauses and the DS_NONPDQ_QUERY_MEM parameter is
  set to either its default (128 KB in 11.70, 256 KB in 12.10) or
  some other low value (512, 1024 KB), the values returned by the
  count(*) function become incorrect after certain number of
  returned rows. The num of rows after which the returned value
  become corrupted depends on the DS_NONPDQ_QUERY_MEM value.
  
  
  An example of a query showing this problem:
  
  select t1.id2, count(*)::integer as cnt from tab1 t1, tab2 t2
  where t1.id2 = t2.id2 and t1.state_upoz = 'a' group by 1;
  
  The query above returns 12632 rows. With the default setting of
  DS_NONPDQ_QUERY_MEM (128KB) the first 1472 rows have the 'cnt'
  values correct, starting with line 1473 they become '1' for the
  rest of the resultset. When you change DS_NONPDQ_QUERY_MEM to
  1024, the boundary moves from line 1473 to 11777. With
  DS_NONPDQ_QUERY_MEM 2048 the problem disappear.
  The occurrence of this problem also seems to depend on the
  number of rows in the tables used in the join. Hence there is
  not any 'save' value of DS_NONPDQ_QUERY_MEM parameter which
  would prevent all possible occurrences of the issue.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of 11.70.xC3 through 11.70.xC8.                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A query with group by and count(*) operation may return      *
  * incorrect count information under the following conditions:  *
  *  - query is simple with one or two tables                    *
  *  - if two tables are involved, a nested-loop join is being   *
  * performed with the group-by column(s) on the outer table     *
  * only                                                         *
  *  - there are enough groups generated that the group-by       *
  * operation overflows to temp partition                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to IDS-11.70.xC9                                      *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IDS-11.70.xC9
  

Temporary fix

Comments

APAR Information

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

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

Fix information

• Fixed component name

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels

• RB70 PSN

     UP