INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10

Consider a simple table using a functional index in a definition
like this

create table "informix".kokos  (a serial not null ,b
varchar(40));

create function "informix".uppc (a varchar (40)) returning
varchar (40) with (not variant);
   return upper (a);
end function;
create index "informix".kokos_1 on "informix".kokos
("informix".uppc(b) ) using btree ;

A simple standalone query like:

select a,b,uppc (b) from kokos where uppc (b) like uppc
("Hqs%");

... will use the index, but the similar query in a stored
procedure


create procedure "informix".jojo1 (p_b varchar (40)) returning
integer, varchar (40), varchar(40);
   define l_a integer;
   define l_b varchar (40);
   define l_ub varchar (40);

   foreach select a,b,uppc(b) into l_a,l_b,l_ub from kokos where
uppc (b) like uppc (p_b)
      return l_a, l_b,l_ub with resume;
   end foreach
end procedure;
execute procedure jojo1 ("Hqs%");

... will not use the index, but can be forced by a directive.
And then the statistics show inaccurately that the index is
used, yet all rows in the table are scanned.

****************************************************************
* USERS AFFECTED:                                              *
* Users of Informix Server prior to 12.10.xC15 and 14.10.xC4.  *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Informix Server 12.10.xC15 (when available) or    *
* 14.10.xC4.                                                   *
****************************************************************

****************************************************************
* USERS AFFECTED:                                              *
* Users of Informix Server prior to 12.10.xC15 and 14.10.xC4.  *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Informix Server 12.10.xC15 (when available) or    *
* 14.10.xC4.                                                   *
****************************************************************

Fixed in Informix Server 12.10.xC15 and 14.10.xC4.

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

07.02.2020
10.12.2020
10.12.2020