IT43466: A STORED PROCEDURE IN A VIEW'S QUERY WHERE-CLAUSE FILTER IS RE-OPTIMIZED FOR EACH ROW IN RESULT SET

APAR status

• Closed as program error.

Error description

• In this scenario, it has been observed that the query take three
  to four times longer in 12.10.FC15
  than in previous versions.
  
  The scenario is more like this:
  
  create view view1 as
    select y from tab1 x0 group by x0.y having (count(*) > 1)
  union
    select y from tab2 x1 group by x1.y having (count(*) > 1)
  union
    select y from (tab1 x2 join z x3 on equal(x2.y,x3.y));
  
  create view view2 as
    select xo.y from view1 x0 where (func1(x0.y)= -2);
  
  create function func1(x lvarchar(320))
  returns smallint;
  ...
      execute procedure func2(x = x) into y;
      return y
  end function;
  
  create procedure func2(y2 varchar(192) default null, y1
  varchar(64) default null, y lvarchar(512)
  returns  char(36) as l1, smallint as l2;
    select max(), min(), ... into p1, p2, ... from tab1 left join
  tab ... where tab1.y = y;
    ...
    select max(), min() ... into p3,p4, ... from tab2 left join
  tab3 ... where tab2.y = y;
    ...
  end procedure;
  
  and the query is
  
    select y from view2;
  
  The scan associated with the call to func1 takes much longer
  than previous versions because of the constant re-optimization.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of Informix Server prior to 12.10.xC16.                *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Informix Server 12.10.xC16.                       *
  ****************************************************************
  

Problem conclusion

• Fixed in Informix Server 12.10.xC16.
  

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