Informix - Problem description
Problem IT13219 | Status: Closed |
QUERY USING 2 ANSI JOINS AND THE BTS_CONTAINS() FUNCTION WITH 'SCORE' ARGUMENT FAILS WITH ERROR 874 AND AF IN SQSLV_RESOLVE() | |
product: | |
INFORMIX SERVER / 5725A3900 / B50 - IDS 11.50 | |
Problem description: | |
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); | |
Problem Summary: | |
**************************************************************** * 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. * **************************************************************** | |
Local Fix: | |
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); | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 18.01.2016 09.06.2017 09.06.2017 |
Problem solved at the following versions (IBM BugInfos) | |
11.70.xC9 | |
Problem solved according to the fixlist(s) of the following version(s) |