IT17264: SELECT FIRST N WITH ORDER BY RETURNS THE WRONG NUMBER OF ROWS IFTHE QUERY PLAN USES INDEX-SELF JOIN

APAR status

• Closed as program error.

Error description

• A SELECT FIRST N query with ORDER BY will return either 0 or the
  wrong number of rows if the query plan uses index-self join on
  index fragmented by range.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of 12.10.xC1 through 12.10.xC7.                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A query may produce incorrect results under the following    *
  * conditions:                                                  *
  *   - Query on a single table, with an order by clause with    *
  * descending order on a column that leads an index (ascending  *
  * order on lead key)                                           *
  *   - the index is a composite index with at least two index   *
  * keys                                                         *
  *   - the first index key column has a small number of         *
  * distinct values, and update statistics medium or high has    *
  * been run on the leading key column                           *
  *   - there exists a predicate on the 2nd index key column     *
  * which selects a small fraction of the table                  *
  *   - index self-join is being used to access the table, with  *
  * the index being traversed in reverse order                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to IBM Informix Server 12.10.xC8                      *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IBM Informix Server 12.10.xC8
  

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

• RC10 PSN

     UP