INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10

Using the SQL-92 syntax for joining two tables with a text
column results in error message -874 "General exception error
has occurred in the optimizer." The text column has a BTS-index
and the query uses bts_contains() for a predicate.

If the query is re-written with SQL-89 syntax a result set is
returned.

Example with error message:
SELECT  t2.text
  FROM  bts_test1 t1
            LEFT OUTER JOIN bts_test2 t2 ON t2.id = t1.id
 WHERE bts_contains (t2.text, 'snow', score#REAL);

Example with result:
SELECT t2.text
  FROM bts_test1 t1,
           OUTER bts_test2 t2
 WHERE
           t2.id = t1.id AND
           bts_contains (t2.text, 'snow', score#REAL);

****************************************************************
* USERS AFFECTED:                                              *
* Users of 12.10.xC1 through 12.10.xC6.                        *
****************************************************************
* 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 12.10.xC7                      *
****************************************************************

If the query is re-written with Informix like SQL-89 syntax a
result set is returned.

not known / see Local fix

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

23.09.2015
28.06.2016
28.06.2016

12.10.xC7