INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10

When you run an SQL query against a table which has undergone
in-place alters you may observe wrong results when a "skip scan"
(multi-index) path is choosen by the optimizer and all pages of
the table are of the current version.

The example is a query like

select {+MULTI_INDEX(tab1 idx1)}
    count(*) from tab1
where
    c2 = 'text to fill space'
    and c3 between '01.01.1970' and '31.12.9999';

resulting in a query plan

QUERY: (OPTIMIZATION TIMESTAMP: 04-26-2018 14:02:20)
------
select {+MULTI_INDEX(tab1 idx1)}
        count(*) from tab1
where
        c2 = 'text to fill space'
        and c3 between '01.01.1970' and '31.12.9999'

DIRECTIVES FOLLOWED:
MULTI_INDEX ( tab1 idx1 )
DIRECTIVES NOT FOLLOWED:

Estimated Cost: 6
Estimated # of Rows Returned: 1

  1) usera.tab1: INDEX PATH (SKIP SCAN)

        Filters: usera.tab1.c2 = 'text to fill space'

    (1) Index Name: usera.idx1
        Index Keys: c3   (Serial, fragments: ALL)
        Lower Index Filter: usera.tab1.c3 >= 01.01.1970
        Upper Index Filter: usera.tab1.c3

****************************************************************
* USERS AFFECTED:                                              *
* Users of IDS versions older than 12.10.xC12.                 *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* Wrong results possible with skip scan (multi-index) query    *
* path on in-place altered tables.                             *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

26.12.2018
04.10.2019
04.10.2019

12.10.xC12