INFORMIX SERVER / 5725A3900 / B50 - IDS 11.50

If you use the Basic Text Search Datablade and your application
runs a query containing at least 2 joins written using the ANSI
syntax and calling the bts_contains(.., score # REAL) function,
the query fails with error -874 (General exception error has
occurred in the optimizer).
At the same time an assertion failure message is written into
the message log:

13:50:32  Assert Failed: Exception Caught. Type: MT_EX_OS,
Context: mem
13:50:32   Who: Session(130766, user1@hostname, 0,
70000013913f040)
		Thread(137157, sqlexec, 700000120674438, 43)
		File: mtex.c Line: 424
13:50:32   Action: Please notify IBM Informix Technical Support.
13:50:32  Raw hex dump of stack located in
/tmp/af.1badd218.rawstk

The stack of the failing thread looks similar to this:

afstack
afhandler
affail_interface
mt_ex_throw_sig@AF11_4
afsig_handler
sqslv_resolve
sqoptim
sqbind
sq_common_bind
sq_bind
sqmain
listen_verify
spawn_thread
startup

Example of the failing query:

   select tab1.*
   FROM tab1
       inner join tab2 on tab1.id=tab2.t1_id
       left outer join tab3 on tab2.id=tab3.t2_id
   where
     bts_contains(tab2.textcol, 'aaaaa', score # REAL);

****************************************************************
* USERS AFFECTED:                                              *
* Users of 11.70.xC1 through 11.70.xC8                         *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* An ANSI JOIN query with bts_contains() predicate involving   *
* SLV may return -874 error under the following conditions:    *
*   - the query is in ANSI OUTER JOIN format                   *
*   - a table containing a BTS index is used as subservient    *
* table                                                        *
*   - a query predicate with "bts_contains()" function is used *
* in WHERE clause                                              *
*   - the bts_contains() function uses a statement-level       *
* variable (SLV) as third argument, e.g. bts_contains(column,  *
* "string", score#REAL)                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Update to IBM Informix Server 11.70.xC9.                     *
****************************************************************

Rewrite the query using Informix join syntax, for example:

select tab1.*
FROM tab1,tab2,outer tab3
where tab1.id=tab2.t1_id
  and tab2.id=tab3.t2_id
  and bts_contains(tab2.textcol, 'aaaaa', score # REAL);

not known / see Local fix

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

18.01.2016
09.06.2017
09.06.2017

11.70.xC9