IC76048: WRONG NUMBER OF ROWS RETURNED WHEN FROM CLAUSE CONTAINS SUBQUERY

APAR status

• Closed as program error.

Error description

• You execute a query like the one that follows below. The
  subquery returns rows that do not satisfy the final filter, but
  the rows are returned by the entire query.
  
  SELECT *
  FROM
  (
    SELECT m.tab1_id,
    CASE
      WHEN m.flg = 'Y'
        THEN m.qty
      ELSE 0
      END tab1_qty,
    NVL(SUM(d.qty), 0) tab2_qty
    FROM tab1 m
    JOIN tab2 d
      ON d.tab1_id = m.tab1_id
    WHERE m.tab1_id in (1, 2)
    GROUP BY 1, 2
  ) x
  WHERE x.tab1_qty <> x.tab2_qty;
  
  Query returns rows where x.tab1_qty = x.tab2_qty.
  

Local fix

• Table tab2 has an index on column tab1_id. The query returned
  the correct results after that index was dropped.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * If a query has a filter on a combination of aggregate and    *
  * columns from the projection list of a view or collection     *
  * derived table, and the view/CDT also has a group by clause,  *
  * then the main query may return incorrect rows if any of the  *
  * columns in the filter maps to an expression instead of a     *
  * simple column reference.                                     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to IDS 11.50.xC9 or higher to resolve the problem.   *
  ****************************************************************
  

Problem conclusion

• The problem is fixed in IDS 11.50.xC9 and higher versions.
  

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

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSN

     UP

• RB15 PSY

     UP